【案例1】NC打开节点慢

左侧树形结构打开节点慢

目录

问题描述

问题排查

解决办法

---

问题描述

左侧树形结构打开节点最快240s以上。

问题排查

通过nmc检查线程信息。

• 重复执行如下sql

<record>
<sql>
SELECT * FROM pr_bd_catagoryctrl WHERE ( nvl ( dr, 0 ) = 0 ) and pk_corp = '1020' and
pk_brand = '10191010000000005P85' and
pk_psndoc in
( select pk_psndoc from bd_psndoc where pk_psnbasdoc in
 
( select pk_psndoc from sm_userandclerk where userid = '00011010000000008RO8' and
nvl( dr, 0 ) = 0 ) and nvl ( dr, 0 ) = 0 ) and nvl ( dr, 0 ) = 0
 
</sql>
         <数据库连接id>22485234</数据库连接id>
         <数据源>zhongheerp</数据源>
         <执行状态>正在执行</执行状态>
         <执行耗时>2359</执行耗时>
         <读取记录数>0</读取记录数>
</record>

SQL_ID  cb1gfgjajgq7k, child number 0
-------------------------------------
SELECT * FROM pr_bd_catagoryctrl WHERE ( nvl ( dr, 0 ) = 0 ) and
pk_corp = '1020' and pk_brand = '10201010000000007KS0' and pk_psndoc in
( select pk_psndoc from bd_psndoc where pk_psnbasdoc in ( select
pk_psndoc from sm_userandclerk where userid = '00011010000000008RO8'
and nvl ( dr, 0 ) = 0 ) and nvl ( dr, 0 ) = 0 ) and nvl ( dr, 0 ) = 0
 
Plan hash value: 489055095
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |       |       |  6680 (100)|          |
|*  1 |  HASH JOIN SEMI                 |                    |     3 |   468 |  6680   (1)| 00:01:21 |
|*  2 |   TABLE ACCESS FULL             | PR_BD_CATAGORYCTRL |    30 |  4020 |  6675   (1)| 00:01:21 |
|   3 |   VIEW                          | VW_NSO_1           |     2 |    44 |     5   (0)| 00:00:01 |
|   4 |    NESTED LOOPS                 |                    |       |       |            |          |
|   5 |     NESTED LOOPS                |                    |     2 |   176 |     5   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| SM_USERANDCLERK    |     1 |    44 |     2   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | I_SM_USERANDCLERK1 |     1 |       |     1   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN           | I_BD_PSNDO_BASDOC  |     2 |       |     1   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS BY INDEX ROWID | BD_PSNDOC          |     2 |    88 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PK_PSNDOC"="PK_PSNDOC")
   2 - filter((NVL("DR",0)=0 AND "PK_BRAND"='10201010000000007KS0' AND "PK_CORP"='1020'))
   6 - filter(NVL("DR",0)=0)
   7 - access("USERID"='00011010000000008RO8')
   8 - access("PK_PSNBASDOC"="PK_PSNDOC")
   9 - filter(NVL("DR",0)=0)

• 对应堆栈信息

java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
oracle.net.ns.Packet.receive(Packet.java:283)
oracle.net.ns.DataPacket.receive(DataPacket.java:103)
oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230)
oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
oracle.net.ns.NetInputStream.read(NetInputStream.java:85)
oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:123)
oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:79)
oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1122)
oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1099)
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288)
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203)
nc.jdbc.framework.crossdb.CrossDBPreparedStatement.executeQuery(CrossDBPreparedStatement.java:100)
nc.jdbc.framework.JdbcSession.executeQuery(JdbcSession.java:271)
nc.jdbc.framework.JdbcPersistenceManager.retrieveByClause(JdbcPersistenceManager.java:881)
nc.jdbc.framework.JdbcPersistenceManager.retrieveByClause(JdbcPersistenceManager.java:888)
nc.jdbc.framework.JdbcPersistenceManager.retrieveByClause(JdbcPersistenceManager.java:875)
nc.bs.pub.SuperDMO.queryByWhereClause(SuperDMO.java:148)
nc.impl.uif.pub.UifServiceImp.queryByCondition(UifServiceImp.java:797)
nc.gejb.uap.cmt.UIFactoryEJBEjbBean.queryByCondition(UIFactoryEJBEjbBean.java:79)
nc.gejb.uap.cmt.UIFactoryEJB_Local.queryByCondition(UIFactoryEJB_Local.java:214)
sun.reflect.GeneratedMethodAccessor278.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:592)
nc.bs.framework.ejb.AbstractEJBServiceHandler.invoke(AbstractEJBServiceHandler.java:56)
nc.bs.framework.ejb.AbstractEJBServiceHandler.invoke(AbstractEJBServiceHandler.java:34)
$Proxy34.queryByCondition(Unknown Source)
nc.bs.fdc.pub.extendedvo.FDCUifServiceImpl.queryByCondition(FDCUifServiceImpl.java:29)
sun.reflect.GeneratedMethodAccessor280.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:592)
nc.bs.framework.comn.serv.ServiceDispatcher.invokeBeanMethod(ServiceDispatcher.java:351)
nc.bs.framework.comn.serv.ServiceDispatcher.execCall(ServiceDispatcher.java:160)
nc.bs.framework.comn.serv.CommonServletDispatcher.doGet(CommonServletDispatcher.java:75)
nc.bs.framework.comn.serv.CommonServletDispatcher.doPost(CommonServletDispatcher.java:95)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
nc.bs.framework.server.LoggerServletFilter.doFilter(LoggerServletFilter.java:34)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:407)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
java.lang.Thread.run(Thread.java:595)

解决办法

select * from user_indexes where table_name=upper('pr_bd_catagoryctrl')
      
select * from user_ind_columns where table_name=upper('pr_bd_catagoryctrl')
      
select count(*) from pr_bd_catagoryctrl  where 
pk_brand = '10191010000000005P85' --1270726
     
--优化sql，增加索引。
create index ind_pk_brand on pr_bd_catagoryctrl (pk_brand)