Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Big Difference in Select and Create Table As Select Traces

Big Difference in Select and Create Table As Select Traces

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Thu, 4 Jan 2007 15:56:18 -0800
Message-ID: <7F24308CD176594B8F14969D10C02C6C011B124F@exch-mail2.win.slac.stanford.edu>


Our Peopesoft folks are going through the upgrade process. One of the updates takes forever, one of the things I tried was to materialize the subquery in the update. I was not able to do so.

Here is the subquery being run as a query

SELECT DISTINCT J1.EMPLID

                FROM PS_JOB J1 ,PS_SAL_RATECD_TBL S1, PS_COMPENSATION
                WHERE J1.EMPLID = PS_COMPENSATION.EMPLID AND
                J1.EMPL_RCD = PS_COMPENSATION.EMPL_RCD AND
                J1.EFFDT = PS_COMPENSATION.EFFDT AND
                J1.EFFSEQ = PS_COMPENSATION.EFFSEQ AND
                S1.SETID = J1.SETID_SALARY AND
                S1.SAL_ADMIN_PLAN = J1.SAL_ADMIN_PLAN
                AND S1.GRADE = J1.GRADE
                AND S1.STEP = J1.STEP AND
                S1.EFFDT = ( SELECT MAX(S2.EFFDT)
                        FROM PS_SAL_RATECD_TBL S2
                        WHERE S2.SETID = J1.SETID_SALARY AND
                        S2.SAL_ADMIN_PLAN = J1.SAL_ADMIN_PLAN AND
                        S2.GRADE = J1.GRADE AND
                        S2.STEP = J1.STEP AND
                        S2.EFFDT <= J1.EFFDT) AND
                        S1.COMP_RATECD = PS_COMPENSATION.COMP_RATECD

call     count       cpu    elapsed       disk      query    current        rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.20 0.18 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 63 7.74 10.17 8932 3189 0 923
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 65 7.94 10.36 8932 3189 0 923

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48 (ORACLE)

Rows Row Source Operation

-------  ---------------------------------------------------

    923 HASH UNIQUE (cr=3189 pr=8932 pw=8932 time=10166810 us)    7691 FILTER (cr=3189 pr=8932 pw=8932 time=10847080 us)   72798 HASH GROUP BY (cr=3189 pr=8932 pw=8932 time=10085197 us)

 415120     HASH JOIN  (cr=3189 pr=7882 pw=7882 time=8460323 us)
 415825      HASH JOIN  (cr=2972 pr=644 pw=644 time=1410302 us)
  42651       HASH JOIN  (cr=2934 pr=0 pw=0 time=181414 us)
   2759        TABLE ACCESS FULL PS_SAL_RATECD_TBL (cr=38 pr=0 pw=0 time=181 us)
  31863        TABLE ACCESS FULL PS_JOB (cr=2896 pr=0 pw=0 time=95775 us)
   2759       TABLE ACCESS FULL PS_SAL_RATECD_TBL (cr=38 pr=0 pw=0 time=188 us)
  31641      INDEX FAST FULL SCAN PS_COMPENSATION (cr=217 pr=0 pw=0 time=187 us)(object id 136687
)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
    923 HASH (UNIQUE)
   7691 FILTER
  72798     HASH (GROUP BY)
 415120      HASH JOIN
 415825       HASH JOIN
  42651        HASH JOIN
   2759         TABLE ACCESS   MODE: ANALYZED (FULL) OF
                    'PS_SAL_RATECD_TBL' (TABLE)
  31863         TABLE ACCESS   MODE: ANALYZED (FULL) OF 'PS_JOB'
                    (TABLE)
   2759        TABLE ACCESS   MODE: ANALYZED (FULL) OF
                   'PS_SAL_RATECD_TBL' (TABLE)
  31641       INDEX   MODE: ANALYZED (FAST FULL SCAN) OF
                  'PS_COMPENSATION' (INDEX (UNIQUE))


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      63        0.00          0.00
  direct path write temp                       1196        0.02          3.25
  direct path read temp                        1196        0.01          0.33
  SQL*Net message from client                    63        0.00          0.04
********************************************************************************

alter session set events '10046 trace name off'

Here's what happens when I try to materialize it and aborted that attempt

create table ps_compensation2 nologging as SELECT DISTINCT J1.EMPLID

                FROM PS_JOB J1 ,PS_SAL_RATECD_TBL S1, PS_COMPENSATION
                WHERE J1.EMPLID = PS_COMPENSATION.EMPLID AND
                J1.EMPL_RCD = PS_COMPENSATION.EMPL_RCD AND
                J1.EFFDT = PS_COMPENSATION.EFFDT AND
                J1.EFFSEQ = PS_COMPENSATION.EFFSEQ AND
                S1.SETID = J1.SETID_SALARY AND
                S1.SAL_ADMIN_PLAN = J1.SAL_ADMIN_PLAN
                AND S1.GRADE = J1.GRADE
                AND S1.STEP = J1.STEP AND
                S1.EFFDT = ( SELECT MAX(S2.EFFDT)
                        FROM PS_SAL_RATECD_TBL S2
                        WHERE S2.SETID = J1.SETID_SALARY AND
                        S2.SAL_ADMIN_PLAN = J1.SAL_ADMIN_PLAN AND
                        S2.GRADE = J1.GRADE AND
                        S2.STEP = J1.STEP AND
                        S2.EFFDT <= J1.EFFDT) AND
                        S1.COMP_RATECD = PS_COMPENSATION.COMP_RATECD

call     count       cpu    elapsed       disk      query    current        rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.10 0 0 0 0 Execute 1 257.30 574.49 97535 238 17 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 257.38 574.60 97535 238 17 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48 (ORACLE)

Rows Execution Plan

-------  ---------------------------------------------------
      0  CREATE TABLE STATEMENT   MODE: ALL_ROWS
      0   LOAD AS SELECT OF 'PS_COMPENSATION2'
      0    HASH (UNIQUE)
      0     NESTED LOOPS
      0      NESTED LOOPS
      0       HASH JOIN
      0        VIEW OF 'VW_SQ_1' (VIEW)
      0         HASH (GROUP BY)
      0          MERGE JOIN
      0           SORT (JOIN)
      0            INDEX   MODE: ANALYZED (FAST FULL SCAN) OF
                       'PS_SAL_RATECD_TBL' (INDEX (UNIQUE))
      0           SORT (JOIN)
      0            INDEX   MODE: ANALYZED (FAST FULL SCAN) OF
                       'PSDJOB' (INDEX)
      0        TABLE ACCESS   MODE: ANALYZED (FULL) OF 'PS_JOB'
                   (TABLE)
      0       INDEX   MODE: ANALYZED (RANGE SCAN) OF
                  'PS_SAL_RATECD_TBL' (INDEX (UNIQUE))
      0      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'PS_COMPENSATION'
                 (INDEX (UNIQUE))


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                    4        0.00          0.00
  direct path write temp                      55978        0.13        190.68
  direct path read temp                       13934        0.16        173.02
  log file sync                                   1        0.00          0.00
  SQL*Net break/reset to client                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01
********************************************************************************

Note the different query path. Also the great rise in direct path * waits. Has anyone else seen anything like this? Any ideas?

This is 10.2.0.2.0

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 04 2007 - 17:56:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US