1 2 Previous Next 19 Replies Latest reply: Nov 16, 2012 12:15 AM by LiuMaclean(刘相兵) RSS

    solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长

    973203
      同一条SQL语句在单机下执行速只要1秒,而在RAC环境下要3-4秒,做了跟踪发现在SQL硬解析CPU要花3-4秒
      RAC下己做了应用隔离



      SQL语句:select a.*, b.goodsname,
      b.goodstype,
      b.goodsno from mpcs_nm_mrp_exec_need a,mpcs_mep_pln_innord_v b
      where a.innordid = b.innordid(+) and a.mrpexecid=6585;


      已用时间: 00: 00: 04.10

      执行计划
      ----------------------------------------------------------
      Plan hash value: 3689069394

      ----------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ----------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 74 | 49506 | 64 (0)| 00:00:01 |
      | 1 | NESTED LOOPS OUTER | | 74 | 49506 | 64 (0)| 00:00:01 |
      | 2 | TABLE ACCESS BY INDEX ROWID | MPCS_NM_MRP_EXEC_NEED | 12 | 1668 | 4 (0)| 00:0
      |* 3 | INDEX RANGE SCAN | MPCS_NM_MRP_EXEC_NEED_IDX1 | 12 | | 1 (0)| 00:00:01 |
      | 4 | VIEW PUSHED PREDICATE | MPCS_MEP_PLN_INNORD_V | 1 | 530 | 5 (0)| 00:00:01
      | 5 | NESTED LOOPS OUTER | | 6 | 1398 | 5 (0)| 00:00:01 |
      | 6 | NESTED LOOPS OUTER | | 1 | 216 | 3 (0)| 00:00:01 |
      | 7 | NESTED LOOPS OUTER | | 1 | 204 | 3 (0)| 00:00:01 |
      | 8 | NESTED LOOPS OUTER | | 1 | 132 | 2 (0)| 00:00:01 |
      | 9 | NESTED LOOPS OUTER | | 1 | 127 | 2 (0)| 00:00:01 |
      | 10 | NESTED LOOPS OUTER | | 1 | 122 | 2 (0)| 00:00:01 |
      | 11 | NESTED LOOPS OUTER | | 1 | 117 | 2 (0)| 00:00:01 |
      | 12 | NESTED LOOPS OUTER | | 1 | 113 | 2 (0)| 00:00:01 |
      | 13 | NESTED LOOPS OUTER | | 1 | 100 | 2 (0)| 00:00:01 |
      | 14 | NESTED LOOPS OUTER | | 1 | 95 | 2 (0)| 00:00:01 |
      | 15 | TABLE ACCESS BY INDEX ROWID| MPCS_MEP_PLN_INNORD | 1 | 82 | 2 (0)| 00:0
      |* 16 | INDEX UNIQUE SCAN | MPCS_MEP_PLN_INNORD_PK | 1 | | 1 (0)| 00:00:01
      |* 17 | INDEX UNIQUE SCAN | MPCS_MB_PRODLINE_DEF_PK | 1 | 13 | 0 (0)| 00:00:01 |
      |* 18 | INDEX UNIQUE SCAN | PUB_COMPANY_PK | 3201 | 16005 | 0 (0)| 00:00:01 |
      |* 19 | INDEX UNIQUE SCAN | MPCS_MB_PROD_TRACE_DOC_PK | 1 | 13 | 0 (0)| 00:00:01 |
      |* 20 | INDEX UNIQUE SCAN | PUB_CUSTOMER_PK | 311 | 1244 | 0 (0)| 00:00:01 |
      |* 21 | INDEX UNIQUE SCAN | PUB_GOODSDETAIL_PK | 105K| 513K| 0 (0)| 00:00:01 |
      |* 22 | INDEX UNIQUE SCAN | EMPLOYEEID_PK | 3547 | 17735 | 0 (0)| 00:00:01 |
      |* 23 | INDEX UNIQUE SCAN | EMPLOYEEID_PK | 3547 | 17735 | 0 (0)| 00:00:01 |
      | 24 | TABLE ACCESS BY INDEX ROWID | PUB_GOODS | 103K| 7265K| 1 (0)| 00:00:01 |
      |* 25 | INDEX UNIQUE SCAN | PUB_GOODS_PK | 1 | | 0 (0)| 00:00:01 |
      |* 26 | INDEX UNIQUE SCAN | MPCS_CHK_DOC_STATUS_IDX1 | 1 | 12 | 0 (0)| 00:00:01 |
      | 27 | VIEW PUSHED PREDICATE | | 1 | 17 | 2 (0)| 00:00:01 |
      | 28 | NESTED LOOPS OUTER | | 7 | 168 | 2 (0)| 00:00:01 |
      | 29 | NESTED LOOPS | | 7 | 133 | 2 (0)| 00:00:01 |
      |* 30 | INDEX UNIQUE SCAN | BMS_BOM_PK | 1 | 10 | 1 (0)| 00:00:01 |
      |* 31 | INDEX RANGE SCAN | BMS_PR_BOM_DTL_IDX2 | 7 | 63 | 1 (0)| 00:00:01 |
      |* 32 | INDEX UNIQUE SCAN | PUB_GOODS_PK | 1 | 5 | 0 (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      3 - access("A"."MRPEXECID"=6585)
      16 - access("A"."INNORDID"="A"."INNORDID")
      17 - access("A"."PRODLINEID"="I"."PRODLINEID"(+))
      18 - access("A"."DEPTID"="H"."COMPANYID"(+))
      19 - access("A"."TRACEID"="G"."TRACEID"(+))
      20 - access("A"."CUSTOMID"="F"."CUSTOMID"(+))
      21 - access("A"."GOODSDTLID"="E"."GOODSDTLID"(+))
      22 - access("A"."INPUTMANID"="D"."EMPLOYEEID"(+))
      23 - access("A"."APPRMANID"="C"."EMPLOYEEID"(+))
      25 - access("A"."GOODSID"="B"."GOODSID"(+))
      26 - access("CHK"."SOURCEID"(+)="A"."INNORDID" AND "CHK"."SOURCETABLE"(+)=24 AND "CHK"."CHKTYPEID"
      filter("CHK"."SOURCEID"(+)="A"."INNORDID")
      30 - access("J"."BOMID"="A"."BOMID")
      31 - access("K"."BOMID"="A"."BOMID")
      32 - access("K"."MGOODSID"="L"."GOODSID"(+))


      统计信息
      ----------------------------------------------------------
      298 recursive calls
      0 db block gets
      40 consistent gets
      0 physical reads
      0 redo size
      1041 bytes sent via SQL*Net to client
      230 bytes received via SQL*Net from client
      1 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      0 rows processed





      、************用Rule规则

      select /*+Rule */ a.*,     b.goodsname,
      b.goodstype,
      b.goodsno from mpcs_nm_mrp_exec_need a,mpcs_mep_pln_innord_v b
      where a.innordid = b.innordid(+) and a.mrpexecid=6588


      已用时间: 00: 00: 02.37

      执行计划
      ----------------------------------------------------------
      Plan hash value: 2377382721

      -------------------------------------------------------------------------
      | Id | Operation | Name |
      -------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | |
      | 1 | MERGE JOIN OUTER | |
      | 2 | SORT JOIN | |
      | 3 | TABLE ACCESS BY INDEX ROWID | MPCS_NM_MRP_EXEC_NEED |
      |* 4 | INDEX RANGE SCAN | MPCS_NM_MRP_EXEC_NEED_IDX1 |
      |* 5 | SORT JOIN | |
      | 6 | VIEW | MPCS_MEP_PLN_INNORD_V |
      | 7 | MERGE JOIN OUTER | |
      | 8 | SORT JOIN | |
      | 9 | NESTED LOOPS OUTER | |
      | 10 | NESTED LOOPS OUTER | |
      | 11 | NESTED LOOPS OUTER | |
      | 12 | NESTED LOOPS OUTER | |
      | 13 | NESTED LOOPS OUTER | |
      | 14 | NESTED LOOPS OUTER | |
      | 15 | NESTED LOOPS OUTER | |
      | 16 | NESTED LOOPS OUTER | |
      | 17 | NESTED LOOPS OUTER | |
      | 18 | TABLE ACCESS FULL | MPCS_MEP_PLN_INNORD |
      |* 19 | INDEX UNIQUE SCAN | MPCS_MB_PRODLINE_DEF_PK |
      |* 20 | INDEX UNIQUE SCAN | PUB_COMPANY_PK |
      |* 21 | INDEX UNIQUE SCAN | MPCS_MB_PROD_TRACE_DOC_PK |
      |* 22 | INDEX UNIQUE SCAN | PUB_CUSTOMER_PK |
      |* 23 | INDEX UNIQUE SCAN | PUB_GOODSDETAIL_PK |
      |* 24 | INDEX UNIQUE SCAN | EMPLOYEEID_PK |
      |* 25 | INDEX UNIQUE SCAN | EMPLOYEEID_PK |
      | 26 | TABLE ACCESS BY INDEX ROWID| PUB_GOODS |
      |* 27 | INDEX UNIQUE SCAN | PUB_GOODS_PK |
      |* 28 | INDEX UNIQUE SCAN | MPCS_CHK_DOC_STATUS_IDX1 |
      |* 29 | SORT JOIN | |
      | 30 | VIEW | |
      | 31 | NESTED LOOPS | |
      | 32 | NESTED LOOPS OUTER | |
      | 33 | TABLE ACCESS FULL | BMS_PR_BOM_DTL |
      |* 34 | INDEX UNIQUE SCAN | PUB_GOODS_PK |
      |* 35 | INDEX UNIQUE SCAN | BMS_BOM_PK |
      -------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      4 - access("A"."MRPEXECID"=6588)
      5 - access("A"."INNORDID"="B"."INNORDID"(+))
      filter("A"."INNORDID"="B"."INNORDID"(+))
      19 - access("A"."PRODLINEID"="I"."PRODLINEID"(+))
      20 - access("A"."DEPTID"="H"."COMPANYID"(+))
      21 - access("A"."TRACEID"="G"."TRACEID"(+))
      22 - access("A"."CUSTOMID"="F"."CUSTOMID"(+))
      23 - access("A"."GOODSDTLID"="E"."GOODSDTLID"(+))
      24 - access("A"."INPUTMANID"="D"."EMPLOYEEID"(+))
      25 - access("A"."APPRMANID"="C"."EMPLOYEEID"(+))
      27 - access("A"."GOODSID"="B"."GOODSID"(+))
      28 - access("CHK"."SOURCEID"(+)="A"."INNORDID" AND
      "CHK"."SOURCETABLE"(+)=24 AND "CHK"."CHKTYPEID"(+)=427)
      29 - access("A"."BOMID"="M"."BOMID"(+))
      filter("A"."BOMID"="M"."BOMID"(+))
      34 - access("K"."MGOODSID"="L"."GOODSID"(+))
      35 - access("J"."BOMID"="K"."BOMID")

      Note
      -----
      - rule based optimizer used (consider using cbo)


      统计信息
      ----------------------------------------------------------
      8 recursive calls
      0 db block gets
      72237 consistent gets
      1 physical reads
      0 redo size
      1692 bytes sent via SQL*Net to client
      237 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      4 sorts (memory)
      0 sorts (disk)
      4 rows processed
      速度变快了一倍。但在单机环境下同样用Rule规则,只要用0.03秒


      上述发现 recursive calls 高 导致CPU硬解析耗时很大,麻烦指点一下



      单机和RAC的执行计划和执行路径是一样的
        • 1. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
          973203
          TKPROF: Release 10.2.0.3.0 - Production on Thu Nov 8 14:38:09 2012

          Copyright (c) 1982, 2005, Oracle. All rights reserved.

          Trace file: orcl2_ora_398.trc
          Sort options: default

          ********************************************************************************
          count = number of times OCI procedure was executed
          cpu = cpu time in seconds executing
          elapsed = elapsed time in seconds executing
          disk = number of physical reads of buffers from disk
          query = number of buffers gotten for consistent read
          current = number of buffers gotten in current mode (usually for update)
          rows = number of rows processed by the fetch or execute call
          ********************************************************************************

          select text
          from
          view$ where rowid=:1


          call count cpu elapsed disk query current rows
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          Parse 2 0.00 0.00 0 0 0 0
          Execute 2 0.00 0.00 0 0 0 0
          Fetch 2 0.00 0.00 0 4 0 2
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          total 6 0.00 0.00 0 4 0 2

          Misses in library cache during parse: 0
          Optimizer mode: CHOOSE
          Parsing user id: SYS (recursive depth: 1)

          Rows Row Source Operation
          ------- ---------------------------------------------------
          1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=85 us)


          Elapsed times include waiting on following events:
          Event waited on Times Max. Wait Total Waited
          ---------------------------------------- Waited ---------- ------------
          SQL*Net message to client 1 0.00 0.00
          SQL*Net message from client 1 0.00 0.00
          ********************************************************************************

          select condition
          from
          cdef$ where rowid=:1


          call count cpu elapsed disk query current rows
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          Parse 12 0.00 0.00 0 0 0 0
          Execute 12 0.00 0.00 0 0 0 0
          Fetch 12 0.00 0.00 0 24 0 12
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          total 36 0.00 0.00 0 24 0 12

          Misses in library cache during parse: 0
          Optimizer mode: CHOOSE
          Parsing user id: SYS (recursive depth: 1)

          Rows Row Source Operation
          ------- ---------------------------------------------------
          1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=38 us)

          ********************************************************************************

          select u.name, o.name, a.interface_version#, o.obj#
          from
          association$ a, user$ u, obj$ o where a.obj# = :1
          and a.property = :2
          and a.statstype# = o.obj# and
          u.user# = o.owner#


          call count cpu elapsed disk query current rows
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          Parse 24 0.00 0.00 0 0 0 0
          Execute 24 0.01 0.00 0 0 0 0
          Fetch 24 0.00 0.00 0 24 0 0
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          total 72 0.01 0.00 0 24 0 0

          Misses in library cache during parse: 0
          Optimizer mode: CHOOSE
          Parsing user id: SYS (recursive depth: 1)

          Rows Row Source Operation
          ------- ---------------------------------------------------
          0 NESTED LOOPS (cr=1 pr=0 pw=0 time=96 us)
          0 NESTED LOOPS (cr=1 pr=0 pw=0 time=87 us)
          0 TABLE ACCESS BY INDEX ROWID ASSOCIATION$ (cr=1 pr=0 pw=0 time=85 us)
          0 INDEX RANGE SCAN ASSOC1 (cr=1 pr=0 pw=0 time=72 us)(object id 387)
          0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)
          0 INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)(object id 36)
          0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)
          0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)(object id 11)

          ********************************************************************************

          select a.default_cpu_cost, a.default_io_cost
          from
          association$ a where a.obj# = :1
          and a.property = :2


          call count cpu elapsed disk query current rows
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          Parse 24 0.00 0.00 0 0 0 0
          Execute 24 0.00 0.00 0 0 0 0
          Fetch 24 0.00 0.00 0 24 0 0
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          total 72 0.00 0.00 0 24 0 0

          Misses in library cache during parse: 0
          Optimizer mode: CHOOSE
          Parsing user id: SYS (recursive depth: 1)

          Rows Row Source Operation
          ------- ---------------------------------------------------
          0 TABLE ACCESS BY INDEX ROWID ASSOCIATION$ (cr=1 pr=0 pw=0 time=32 us)
          0 INDEX RANGE SCAN ASSOC1 (cr=1 pr=0 pw=0 time=26 us)(object id 387)

          ********************************************************************************

          select a.*, b.goodsname,
          b.goodstype,
          b.goodsno from mpcs_nm_mrp_exec_need a,mpcs_mep_pln_innord_v b
          where a.innordid = b.innordid(+) and a.mrpexecid=5230

          call count cpu elapsed disk query current rows
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          Parse 1 3.96 3.88 0 0 0 0
          Execute 1 0.00 0.00 0 0 0 0
          Fetch 2 0.02 0.01 1 48 0 2
          ------- ------ -------- ---------- ---------- ---------- ---------- ----------
          total 4 3.98 3.90 1 48 0 2

          Misses in library cache during parse: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 84

          Rows Row Source Operation
          ------- ---------------------------------------------------
          2 NESTED LOOPS OUTER (cr=48 pr=1 pw=0 time=13647 us)
          2 TABLE ACCESS BY INDEX ROWID MPCS_NM_MRP_EXEC_NEED (cr=5 pr=1 pw=0 time=13203 us)
          2 INDEX RANGE SCAN MPCS_NM_MRP_EXEC_NEED_IDX1 (cr=3 pr=0 pw=0 time=119 us)(object id 334499)
          2 VIEW PUSHED PREDICATE MPCS_MEP_PLN_INNORD_V (cr=43 pr=0 pw=0 time=994 us)
          2 NESTED LOOPS OUTER (cr=43 pr=0 pw=0 time=980 us)
          2 NESTED LOOPS OUTER (cr=30 pr=0 pw=0 time=739 us)
          2 NESTED LOOPS OUTER (cr=26 pr=0 pw=0 time=653 us)
          2 NESTED LOOPS OUTER (cr=20 pr=0 pw=0 time=530 us)
          2 NESTED LOOPS OUTER (cr=20 pr=0 pw=0 time=510 us)
          2 NESTED LOOPS OUTER (cr=16 pr=0 pw=0 time=440 us)
          2 NESTED LOOPS OUTER (cr=12 pr=0 pw=0 time=363 us)
          2 NESTED LOOPS OUTER (cr=10 pr=0 pw=0 time=307 us)
          2 NESTED LOOPS OUTER (cr=10 pr=0 pw=0 time=284 us)
          2 NESTED LOOPS OUTER (cr=6 pr=0 pw=0 time=204 us)
          2 TABLE ACCESS BY INDEX ROWID MPCS_MEP_PLN_INNORD (cr=6 pr=0 pw=0 time=179 us)
          2 INDEX UNIQUE SCAN MPCS_MEP_PLN_INNORD_PK (cr=4 pr=0 pw=0 time=89 us)(object id 332817)
          0 INDEX UNIQUE SCAN MPCS_MB_PRODLINE_DEF_PK (cr=0 pr=0 pw=0 time=9 us)(object id 332036)
          2 INDEX UNIQUE SCAN PUB_COMPANY_PK (cr=4 pr=0 pw=0 time=57 us)(object id 336097)
          0 INDEX UNIQUE SCAN MPCS_MB_PROD_TRACE_DOC_PK (cr=0 pr=0 pw=0 time=8 us)(object id 332044)
          2 INDEX UNIQUE SCAN PUB_CUSTOMER_PK (cr=2 pr=0 pw=0 time=41 us)(object id 336135)
          2 INDEX UNIQUE SCAN PUB_GOODSDETAIL_PK (cr=4 pr=0 pw=0 time=58 us)(object id 336402)
          2 INDEX UNIQUE SCAN EMPLOYEEID_PK (cr=4 pr=0 pw=0 time=55 us)(object id 336223)
          0 INDEX UNIQUE SCAN EMPLOYEEID_PK (cr=0 pr=0 pw=0 time=7 us)(object id 336223)
          2 TABLE ACCESS BY INDEX ROWID PUB_GOODS (cr=6 pr=0 pw=0 time=98 us)
          2 INDEX UNIQUE SCAN PUB_GOODS_PK (cr=4 pr=0 pw=0 time=63 us)(object id 336287)
          2 INDEX UNIQUE SCAN MPCS_CHK_DOC_STATUS_IDX1 (cr=4 pr=0 pw=0 time=69 us)(object id 330624)
          2 VIEW PUSHED PREDICATE (cr=13 pr=0 pw=0 time=261 us)
          2 NESTED LOOPS OUTER (cr=13 pr=0 pw=0 time=249 us)
          2 NESTED LOOPS (cr=9 pr=0 pw=0 time=218 us)
          2 INDEX UNIQUE SCAN BMS_BOM_PK (cr=4 pr=0 pw=0 time=69 us)(object id 328993)
          2 INDEX RANGE SCAN BMS_PR_BOM_DTL_IDX2 (cr=5 pr=0 pw=0 time=132 us)(object id 329006)
          2 INDEX UNIQUE SCAN PUB_GOODS_PK (cr=4 pr=0 pw=0 time=55 us)(object id 336287)


          Elapsed times include waiting on following events:
          Event waited on Times Max. Wait Total Waited
          ---------------------------------------- Waited ---------- ------------
          library cache lock 23 0.00 0.01
          SQL*Net message to client 2 0.00 0.00
          gc cr grant 2-way 1 0.00 0.00
          db file sequential read 1 0.00 0.00
          SQL*Net message from client 2 0.01 0.01
          ********************************************************************************
          • 2. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
            LiuMaclean(刘相兵)
            请把 原始的10046 trace发给我

            有问题请去OTN中文论坛开个帖子 我会回复 地址:http://www.otncn.org
            如果需要发送附件,可以直接发邮件到 liu.maclean@gmail.com
            • 3. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
              LiuMaclean(刘相兵)
              Good One
              EXPLAIN PLAN SET STATEMENT_ID='PLUS220342' FOR select a.*,b.goodsname,b.goodstype,b.goodsno from mpcs_nm_mrp_exec_need a
              left join mpcs_mep_pln_innord_v b on a.innordid=b.innordid where
              a.mrpexecid=5230
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.93       0.92          0          0          0           0
              Execute      1      0.02       0.00          0          0          0           0
              Fetch        0      0.00       0.00          0          0          0           0
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        2      0.95       0.93          0          0          0           0
              
              Misses in library cache during parse: 1
              Optimizer mode: ALL_ROWS
              Parsing user id: 84  
              
              Rows     Row Source Operation
              -------  ---------------------------------------------------
                    0  NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0   TABLE ACCESS BY INDEX ROWID MPCS_NM_MRP_EXEC_NEED (cr=0 pr=0 pw=0 time=0 us)
                    0    INDEX RANGE SCAN MPCS_NM_MRP_EXEC_NEED_IDX1 (cr=0 pr=0 pw=0 time=0 us)(object id 150777)
                    0   VIEW PUSHED PREDICATE  MPCS_MEP_PLN_INNORD_V (cr=0 pr=0 pw=0 time=0 us)
                    0    NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0     NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0      NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0       NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0        NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0         NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0          NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0           NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0            NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0             NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0              TABLE ACCESS BY INDEX ROWID MPCS_MEP_PLN_INNORD (cr=0 pr=0 pw=0 time=0 us)
                    0               INDEX UNIQUE SCAN MPCS_MEP_PLN_INNORD_PK (cr=0 pr=0 pw=0 time=0 us)(object id 150346)
                    0              INDEX UNIQUE SCAN MPCS_MB_PRODLINE_DEF_PK (cr=0 pr=0 pw=0 time=0 us)(object id 150075)
                    0             INDEX UNIQUE SCAN PUB_COMPANY_PK (cr=0 pr=0 pw=0 time=0 us)(object id 151745)
                    0            INDEX UNIQUE SCAN MPCS_MB_PROD_TRACE_DOC_PK (cr=0 pr=0 pw=0 time=0 us)(object id 150083)
                    0           INDEX UNIQUE SCAN PUB_CUSTOMER_PK (cr=0 pr=0 pw=0 time=0 us)(object id 151763)
                    0          INDEX UNIQUE SCAN PUB_GOODSDETAIL_PK (cr=0 pr=0 pw=0 time=0 us)(object id 151860)
                    0         INDEX UNIQUE SCAN EMPLOYEEID_PK (cr=0 pr=0 pw=0 time=0 us)(object id 151811)
                    0        INDEX UNIQUE SCAN EMPLOYEEID_PK (cr=0 pr=0 pw=0 time=0 us)(object id 151811)
                    0       TABLE ACCESS BY INDEX ROWID PUB_GOODS (cr=0 pr=0 pw=0 time=0 us)
                    0        INDEX UNIQUE SCAN PUB_GOODS_PK (cr=0 pr=0 pw=0 time=0 us)(object id 151835)
                    0      INDEX UNIQUE SCAN MPCS_CHK_DOC_STATUS_IDX1 (cr=0 pr=0 pw=0 time=0 us)(object id 148675)
                    0     VIEW PUSHED PREDICATE  (cr=0 pr=0 pw=0 time=0 us)
                    0      NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
                    0        INDEX UNIQUE SCAN BMS_BOM_PK (cr=0 pr=0 pw=0 time=0 us)(object id 147071)
                    0        INDEX RANGE SCAN BMS_PR_BOM_DTL_IDX2 (cr=0 pr=0 pw=0 time=0 us)(object id 147084)
                    0       INDEX UNIQUE SCAN PUB_GOODS_PK (cr=0 pr=0 pw=0 time=0 us)(object id 151835)
              
              
              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                SQL*Net message to client                       1        0.00          0.00
                SQL*Net message from client                     1        0.00          0.00
              Bad One

              EXPLAIN PLAN SET STATEMENT_ID='PLUS2636762' FOR  select a.*,     b.goodsname,
                      b.goodstype,
                      b.goodsno from mpcs_nm_mrp_exec_need a,mpcs_mep_pln_innord_v b
               where a.innordid = b.innordid(+)  and a.mrpexecid=5230
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      3.98       3.89          0          0          0           0
              Execute      1      0.03       0.04          0          0          0           0
              Fetch        0      0.00       0.00          0          0          0           0
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        2      4.01       3.93          0          0          0           0
              
              Misses in library cache during parse: 1
              Optimizer mode: ALL_ROWS
              Parsing user id: 84  
              
              Rows     Row Source Operation
              -------  ---------------------------------------------------
                    0  NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0   TABLE ACCESS BY INDEX ROWID MPCS_NM_MRP_EXEC_NEED (cr=0 pr=0 pw=0 time=0 us)
                    0    INDEX RANGE SCAN MPCS_NM_MRP_EXEC_NEED_IDX1 (cr=0 pr=0 pw=0 time=0 us)(object id 334499)
                    0   VIEW PUSHED PREDICATE  MPCS_MEP_PLN_INNORD_V (cr=0 pr=0 pw=0 time=0 us)
                    0    NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0     NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0      NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0       NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0        NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0         NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0          NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0           NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0            NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0             NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0              TABLE ACCESS BY INDEX ROWID MPCS_MEP_PLN_INNORD (cr=0 pr=0 pw=0 time=0 us)
                    0               INDEX UNIQUE SCAN MPCS_MEP_PLN_INNORD_PK (cr=0 pr=0 pw=0 time=0 us)(object id 332817)
                    0              INDEX UNIQUE SCAN MPCS_MB_PRODLINE_DEF_PK (cr=0 pr=0 pw=0 time=0 us)(object id 332036)
                    0             INDEX UNIQUE SCAN PUB_COMPANY_PK (cr=0 pr=0 pw=0 time=0 us)(object id 336097)
                    0            INDEX UNIQUE SCAN MPCS_MB_PROD_TRACE_DOC_PK (cr=0 pr=0 pw=0 time=0 us)(object id 332044)
                    0           INDEX UNIQUE SCAN PUB_CUSTOMER_PK (cr=0 pr=0 pw=0 time=0 us)(object id 336135)
                    0          INDEX UNIQUE SCAN PUB_GOODSDETAIL_PK (cr=0 pr=0 pw=0 time=0 us)(object id 336402)
                    0         INDEX UNIQUE SCAN EMPLOYEEID_PK (cr=0 pr=0 pw=0 time=0 us)(object id 336223)
                    0        INDEX UNIQUE SCAN EMPLOYEEID_PK (cr=0 pr=0 pw=0 time=0 us)(object id 336223)
                    0       TABLE ACCESS BY INDEX ROWID PUB_GOODS (cr=0 pr=0 pw=0 time=0 us)
                    0        INDEX UNIQUE SCAN PUB_GOODS_PK (cr=0 pr=0 pw=0 time=0 us)(object id 336287)
                    0      INDEX UNIQUE SCAN MPCS_CHK_DOC_STATUS_IDX1 (cr=0 pr=0 pw=0 time=0 us)(object id 330624)
                    0     VIEW PUSHED PREDICATE  (cr=0 pr=0 pw=0 time=0 us)
                    0      NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
                    0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
                    0        INDEX UNIQUE SCAN BMS_BOM_PK (cr=0 pr=0 pw=0 time=0 us)(object id 328993)
                    0        INDEX RANGE SCAN BMS_PR_BOM_DTL_IDX2 (cr=0 pr=0 pw=0 time=0 us)(object id 329006)
                    0       INDEX UNIQUE SCAN PUB_GOODS_PK (cr=0 pr=0 pw=0 time=0 us)(object id 336287)
              
              
              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                SQL*Net message to client                       1        0.00          0.00
                SQL*Net message from client                     1        0.00          0.00
              可以看到 在BAD ONE中 确实PARSE消耗了更多的CPU

              优化器模式均为 Optimizer mode: ALL_ROWS


              但是 GOOD ONE的 版本是 10.2.0.2.0 , 而BAD ONE是 10.2.0.3


              请给出以下 查询在2个库 的 结果:
               col name for a40
               col value for a30
              
               set linesize 200 pagesize 1400
               select name,value from v$system_parameter where ISDEFAULT='FALSE';
              • 4. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                973203
                good:(单机)
                SQL> select name,value from v$system_parameter where isdefault='FALSE';

                NAME VALUE
                ---------------------------------------- ------------------------------
                processes 150
                nls_territory CHINA
                nls_date_language
                sga_target 532676608
                control_files /opt/app/oracle/oradata/orcl/c
                ontrol01.ctl, /opt/app/oracle/
                oradata/orcl/control02.ctl, /o
                pt/app/oracle/oradata/orcl/con
                trol03.ctl

                db_block_size 8192
                compatible 10.2.0.2.0
                db_file_multiblock_read_count 16
                db_recovery_file_dest /opt/app/oracle/flash_recovery
                _area                                                                                                                                                         

                db_recovery_file_dest_size 2147483648
                undo_management AUTO
                undo_tablespace UNDOTBS1
                remote_login_passwordfile EXCLUSIVE
                db_domain
                dispatchers (PROTOCOL=TCP) (SERVICE=orclXD
                B)

                job_queue_processes 10
                background_dump_dest /opt/app/oracle/admin/orcl/bdu
                mp

                user_dump_dest /opt/app/oracle/admin/orcl/udu
                mp

                core_dump_dest /opt/app/oracle/admin/orcl/cdu
                mp

                audit_file_dest /opt/app/oracle/admin/orcl/adu
                mp

                db_name orcl
                open_cursors 300
                pga_aggregate_target 176160768

                23 rows selected.

                SQL> spool off


                bad(RAC):
                SQL> /

                NAME VALUE
                ---------------------------------------- ------------------------------
                processes 150
                sga_max_size 8589934592
                spfile +DATA/orcl/spfileorcl.ora                                                                                                                                     
                sga_target 5368709120
                control_files +DATA/orcl/controlfile/current                                                                                                                                
                .260.688884829

                db_block_size 8192
                db_writer_processes 20
                compatible 10.2.0.3.0
                log_archive_dest_1 LOCATION=+DATA/orcl/
                log_archive_format %t_%s_%r.dbf
                db_file_multiblock_read_count 16
                cluster_database TRUE
                cluster_database_instances 2
                db_create_file_dest +DATA                                                                                                                                                         
                db_create_online_log_dest_1 +DATA                                                                                                                                                         
                db_recovery_file_dest +DATA                                                                                                                                                         
                db_recovery_file_dest_size 21474836480
                thread 2
                instance_number 2
                undo_management AUTO
                undo_tablespace UNDOTBS2
                remote_login_passwordfile EXCLUSIVE
                db_domain
                dispatchers (PROTOCOL=TCP) (SERVICE=orclXD
                B)

                local_listener (ADDRESS = (PROTOCOL=TCP)(HOST
                =192.168.1.59)(PORT=1521))

                remote_listener LISTENERS_ORCL
                job_queue_processes 10
                cursor_sharing FORCE
                background_dump_dest /oracle/app/oracle/admin/orcl/
                bdump

                user_dump_dest /oracle/app/oracle/admin/orcl/
                udump

                core_dump_dest /oracle/app/oracle/admin/orcl/
                cdump

                audit_file_dest /oracle/app/oracle/admin/orcl/
                adump

                db_name orcl
                open_cursors 300
                pga_aggregate_target 1684013056

                已选择35行。

                已用时间: 00: 00: 00.17
                SQL> spool off;
                • 5. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                  LiuMaclean(刘相兵)
                  在BAD ONE上 执行

                  alter session set optimizer_features_enable='10.2.0.2';

                  在同一个session中再次做 10046 和 explain plan for 你的SQL,并发给我
                  • 6. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                    973203
                    你好,附件己发给你呢,速度还是一样的
                    • 7. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                      LiuMaclean(刘相兵)
                      把 相关表的统计信息删除掉 再试一次
                      • 8. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                        973203
                        删除统计信息后的数据己发给你,比之前更慢
                        • 10. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                          LiuMaclean(刘相兵)
                          怀疑是BUG

                          请参考:

                          Bug 3537086 High CPU usage during parse for bitmap plans
                          Bug 4900129 High CPU consumption during parse for unused CHECK constraints
                          How to Reduce Parse Time Cpu [ID 156262.1]
                          Bug 5131645 10g hard parse CPU regression [ appopd->qksfrochild ]


                          如果可以的话 对 该SQL 做一个 sql health check

                          请使用 sql health check 脚本 分析该SQL 然后上传HTML
                          http://www.oracledatabase12g.com/archives/sql-tuning-health-check-script.html
                          • 11. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                            973203
                            你好,那个html我己发给你
                            • 12. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                              LiuMaclean(刘相兵)
                              MAT_VIEW     REWRITE_ENABLED     There are 2 materialized views with rewrite enabled.     A large number of materialized views could affect parsing time since CBO would have to evaluate each during a hard-parse.

                              新发现是 似乎你这里用到了 物化视图

                              histogram.     A Height-balanced histogram with no popular values is not helpful nor desired. Consider dropping this histogram by collecting new CBO statistics while using METHOD_OPT with SIZE 1.
                              11     TABLE     TSDZM2.PUB_CUSTOMER     Table has 3 index(es) with DOP different than its table.     Table has a degree of parallelism of "DEFAULT".
                              Review index properties and fix degree of parallelism of table and/or its index(es).


                              似乎 TSDZM2.PUB_CUSTOMER 表用到了 parallel degree
                              • 13. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                                LiuMaclean(刘相兵)
                                SQL> alter session set query_rewrite_enabled=false;



                                再试试, 把涉及到的 物化视图DDl列出来
                                • 14. Re: solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
                                  973203
                                  你好,有两个物化视图是ORACLE示例用户SH创建的。
                                  CREATE MATERIALIZED VIEW SH.CAL_MONTH_SALES_MV
                                  ON PREBUILT TABLE
                                  REFRESH FORCE ON DEMAND
                                  ENABLE QUERY REWRITE
                                  AS
                                  SELECT t.calendar_month_desc
                                  , sum(s.amount_sold) AS dollars
                                  FROM sales s
                                  , times t
                                  WHERE s.time_id = t.time_id
                                  GROUP BY t.calendar_month_desc


                                  CREATE MATERIALIZED VIEW SH.FWEEK_PSCAT_SALES_MV
                                  ON PREBUILT TABLE
                                  REFRESH FORCE ON DEMAND
                                  ENABLE QUERY REWRITE
                                  AS
                                  SELECT t.week_ending_day
                                  , p.prod_subcategory
                                  , sum(s.amount_sold) AS dollars
                                  , s.channel_id
                                  , s.promo_id
                                  FROM sales s
                                  , times t
                                  , products p
                                  WHERE s.time_id = t.time_id
                                  AND s.prod_id = p.prod_id
                                  GROUP BY t.week_ending_day
                                  , p.prod_subcategory
                                  , s.channel_id
                                  , s.promo_id

                                  我把这两个物化视图删了,也是一样的,PUB_CUSTOMER我也取消了,速度还是差不多,少了0.1秒
                                  1 2 Previous Next