This discussion is archived
2 Replies Latest reply: Nov 21, 2012 11:09 PM by Nikolay Savvinov RSS

performance tuning - anlayze db time difference of 2 queries

spur230 Newbie
Currently Being Moderated
I'm using Oracle 11.2.0.3. I wanted to understand why elasped time between following two queries is so diffent.

Both the query is expected to return same result . In query 1, I am using IN clause whereas for query 2 I am joining.

Query 1 takes 13.74 sec
Query 2 takes 0.9 sec

Experts please take a look into the query and suggest why query 1 is taking significantly long time .

Here is the snippet of tkprof report of the two queries.

Query 1
SELECT segment_name
FROM dba_segments ds
WHERE segment_name IN
  (SELECT index_name
  FROM dba_indexes
  WHERE table_name IN
    (
    SELECT SEGMENT_NAME
    FROM dba_segments
    WHERE tablespace_name = 'CRDDATA_03'
    AND segment_type      = 'TABLE'
    AND owner             = 'CRDMAIN'
    AND segment_name LIKE 'H%'
    )
  )
AND ds.tablespace_name <> 'CRDDATA_03'
AND ds.segment_type     = 'INDEX'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.77       0.77          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     12.96      12.97          0    2901529          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     13.73      13.74          0    2901529          0          20

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        20         20         20  FILTER  (cr=2901529 pr=0 pw=0 time=4813011 us)
      3797       3797       3797   VIEW  SYS_DBA_SEGS (cr=11739 pr=0 pw=0 time=57054 us cost=723 size=34524 card=822)
      3797       3797       3797    UNION-ALL  (cr=11739 pr=0 pw=0 time=55281 us)
      3797       3797       3797     NESTED LOOPS  (cr=1877 pr=0 pw=0 time=53508 us cost=634 size=92568 card=798)
      3797       3797       3797      HASH JOIN RIGHT OUTER (cr=1872 pr=0 pw=0 time=40841 us cost=633 size=85456 card=784)
       183        183        183       INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=116 us cost=1 size=732 card=183)(object id 47)
      3797       3797       3797       HASH JOIN  (cr=1871 pr=0 pw=0 time=36553 us cost=632 size=82320 card=784)
      3797       3797       3797        HASH JOIN  (cr=1541 pr=0 pw=0 time=17769 us cost=565 size=52528 card=784)
      3968       3968       3968         VIEW  SYS_OBJECTS (cr=1232 pr=0 pw=0 time=9819 us cost=463 size=140245 card=4007)
      3968       3968       3968          UNION-ALL  (cr=1232 pr=0 pw=0 time=9176 us)
         0          0          0           TABLE ACCESS FULL TAB$ (cr=615 pr=0 pw=0 time=4003 us cost=230 size=704 card=32)
         0          0          0           FILTER  (cr=0 pr=0 pw=0 time=2 us)
         0          0          0            TABLE ACCESS FULL TABPART$ (cr=0 pr=0 pw=0 time=0 us cost=4 size=5440 card=320)
         0          0          0           FILTER  (cr=0 pr=0 pw=0 time=1 us)
         0          0          0            TABLE ACCESS FULL CLU$ (cr=0 pr=0 pw=0 time=0 us cost=230 size=140 card=10)
      3968       3968       3968           TABLE ACCESS FULL IND$ (cr=615 pr=0 pw=0 time=3098 us cost=230 size=83328 card=3968)
         0          0          0           FILTER  (cr=0 pr=0 pw=0 time=1 us)
         0          0          0            TABLE ACCESS FULL INDPART$ (cr=0 pr=0 pw=0 time=0 us cost=4 size=6273 card=369)
         0          0          0           FILTER  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0            TABLE ACCESS FULL LOB$ (cr=0 pr=0 pw=0 time=0 us cost=226 size=18216 card=828)
         0          0          0           FILTER  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0            TABLE ACCESS FULL TABSUBPART$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=448 card=32)
         0          0          0           FILTER  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0            TABLE ACCESS FULL INDSUBPART$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=52 card=1)
         0          0          0           TABLE ACCESS FULL LOBFRAG$ (cr=2 pr=0 pw=0 time=16 us cost=2 size=19 card=1)
      8652       8652       8652         MERGE JOIN  (cr=309 pr=0 pw=0 time=6972 us cost=102 size=278912 card=8716)
        61         61         61          TABLE ACCESS CLUSTER TS$ (cr=74 pr=0 pw=0 time=501 us cost=13 size=1037 card=61)
        62         62         62           INDEX FULL SCAN I_TS# (cr=1 pr=0 pw=0 time=68 us cost=1 size=0 card=1)(object id 7)
      8652       8652       8652          SORT JOIN (cr=235 pr=0 pw=0 time=5896 us cost=89 size=130740 card=8716)
      8742       8742       8742           TABLE ACCESS FULL SEG$ (cr=235 pr=0 pw=0 time=2938 us cost=88 size=130740 card=8716)
     67814      67814      67814        INDEX FULL SCAN I_OBJ2 (cr=330 pr=0 pw=0 time=17413 us cost=66 size=2575944 card=67788)(object id 37)
      3797       3797       3797      INDEX UNIQUE SCAN I_FILE2 (cr=5 pr=0 pw=0 time=9383 us cost=1 size=7 card=1)(object id 44)
         0          0          0     NESTED LOOPS  (cr=31 pr=0 pw=0 time=156 us cost=9 size=80 card=1)
         0          0          0      NESTED LOOPS OUTER (cr=31 pr=0 pw=0 time=153 us cost=8 size=63 card=1)
         0          0          0       NESTED LOOPS  (cr=31 pr=0 pw=0 time=152 us cost=7 size=59 card=1)
         0          0          0        NESTED LOOPS  (cr=31 pr=0 pw=0 time=152 us cost=6 size=52 card=1)
        19         19         19         TABLE ACCESS FULL UNDO$ (cr=2 pr=0 pw=0 time=42 us cost=2 size=646 card=19)
         0          0          0         TABLE ACCESS CLUSTER SEG$ (cr=29 pr=0 pw=0 time=110 us cost=1 size=18 card=1)
        19         19         19          INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=10 pr=0 pw=0 time=32 us cost=1 size=0 card=1)(object id 9)
         0          0          0        INDEX UNIQUE SCAN I_FILE2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=7 card=1)(object id 44)
         0          0          0       INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)
         0          0          0      TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)
         0          0          0       INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 7)
         0          0          0     HASH JOIN OUTER (cr=9831 pr=0 pw=0 time=19011 us cost=80 size=1150 card=23)
         0          0          0      NESTED LOOPS  (cr=9831 pr=0 pw=0 time=18957 us cost=79 size=1058 card=23)
         0          0          0       NESTED LOOPS  (cr=9831 pr=0 pw=0 time=18956 us cost=74 size=667 card=23)
       180        180        180        TABLE ACCESS FULL FILE$ (cr=2 pr=0 pw=0 time=197 us cost=2 size=1980 card=180)
         0          0          0        TABLE ACCESS CLUSTER SEG$ (cr=9829 pr=0 pw=0 time=18765 us cost=1 size=18 card=1)
      8972       8972       8972         INDEX RANGE SCAN I_FILE#_BLOCK# (cr=120 pr=0 pw=0 time=2506 us cost=1 size=0 card=1)(object id 9)
         0          0          0       TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)
         0          0          0        INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 7)
         0          0          0      INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=732 card=183)(object id 47)
        20         20         20   NESTED LOOPS OUTER (cr=2889790 pr=0 pw=0 time=12874737 us cost=153 size=180 card=1)
        20         20         20    NESTED LOOPS OUTER (cr=2889720 pr=0 pw=0 time=12871493 us cost=152 size=168 card=1)
        20         20         20     NESTED LOOPS  (cr=2889720 pr=0 pw=0 time=12869790 us cost=151 size=164 card=1)
        20         20         20      NESTED LOOPS OUTER (cr=2889700 pr=0 pw=0 time=12867614 us cost=150 size=160 card=1)
        20         20         20       HASH JOIN  (cr=2889652 pr=0 pw=0 time=12865548 us cost=149 size=157 card=1)
      3796       3796       3796        NESTED LOOPS  (cr=94920 pr=0 pw=0 time=535174 us)
      3796       3796       3796         NESTED LOOPS  (cr=91125 pr=0 pw=0 time=523374 us cost=58 size=230 card=2)
      3796       3796       3796          NESTED LOOPS OUTER (cr=85020 pr=0 pw=0 time=504797 us cost=57 size=162 card=2)
      3796       3796       3796           NESTED LOOPS  (cr=81223 pr=0 pw=0 time=492180 us cost=56 size=144 card=2)
      3796       3796       3796            NESTED LOOPS  (cr=77431 pr=0 pw=0 time=475114 us cost=55 size=136 card=2)
      3805       3805       3805             TABLE ACCESS BY INDEX ROWID OBJ$ (cr=67498 pr=0 pw=0 time=445532 us cost=54 size=74 card=2)
      3809       3809       3809              INDEX SKIP SCAN I_OBJ2 (cr=63694 pr=0 pw=0 time=423136 us cost=54 size=0 card=2)(object id 37)
      3796       3796       3796             TABLE ACCESS BY INDEX ROWID IND$ (cr=9933 pr=0 pw=0 time=25031 us cost=1 size=31 card=1)
      3796       3796       3796              INDEX UNIQUE SCAN I_IND1 (cr=6123 pr=0 pw=0 time=14412 us cost=1 size=0 card=1)(object id 41)
      3796       3796       3796            INDEX RANGE SCAN I_USER2 (cr=3792 pr=0 pw=0 time=12980 us cost=1 size=4 card=1)(object id 47)
         0          0          0           INDEX RANGE SCAN I_OBJ1 (cr=3797 pr=0 pw=0 time=7552 us cost=1 size=9 card=1)(object id 36)
      3796       3796       3796          INDEX RANGE SCAN I_OBJ1 (cr=6105 pr=0 pw=0 time=13604 us cost=1 size=0 card=1)(object id 36)
      3796       3796       3796         TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3795 pr=0 pw=0 time=6954 us cost=1 size=34 card=1)
     37802      37802      37802        VIEW  SYS_DBA_SEGS (cr=2794732 pr=0 pw=0 time=7279972 us cost=91 size=126 card=3)
     37802      37802      37802         UNION-ALL  (cr=2794732 pr=0 pw=0 time=7268657 us)
     37802      37802      37802          NESTED LOOPS  (cr=2183663 pr=0 pw=0 time=7232396 us cost=70 size=107 card=1)
     37802      37802      37802           NESTED LOOPS  (cr=2179868 pr=0 pw=0 time=7167437 us cost=69 size=100 card=1)
    351276     351276     351276            NESTED LOOPS  (cr=2074083 pr=0 pw=0 time=11505903 us cost=68 size=85 card=1)
    351276     351276     351276             FILTER  (cr=113700 pr=0 pw=0 time=3467945 us)
   1720168    1720168    1720168              HASH JOIN RIGHT OUTER (cr=113700 pr=0 pw=0 time=3581413 us cost=62 size=140 card=2)
    693936     693936     693936               TABLE ACCESS FULL USER$ (cr=34128 pr=0 pw=0 time=272070 us cost=4 size=2745 card=183)
   1720168    1720168    1720168               NESTED LOOPS  (cr=79572 pr=0 pw=0 time=2002140 us cost=58 size=15455 card=281)
      3792       3792       3792                TABLE ACCESS BY INDEX ROWID TS$ (cr=7584 pr=0 pw=0 time=26049 us cost=1 size=17 card=1)
      3792       3792       3792                 INDEX UNIQUE SCAN I_TS1 (cr=3792 pr=0 pw=0 time=14353 us cost=1 size=0 card=1)(object id 45)
   1720168    1720168    1720168                INDEX SKIP SCAN I_OBJ2 (cr=71988 pr=0 pw=0 time=1673084 us cost=57 size=10678 card=281)(object id 37)
    351276     351276     351276             VIEW  SYS_OBJECTS (cr=1960383 pr=0 pw=0 time=6697277 us cost=3 size=15 card=1)
    351276     351276     351276              UNION ALL PUSHED PREDICATE  (cr=1960383 pr=0 pw=0 time=6390778 us)
    351276     351276     351276               TABLE ACCESS CLUSTER TAB$ (cr=1204938 pr=0 pw=0 time=2438439 us cost=1 size=22 card=1)
    351276     351276     351276                INDEX UNIQUE SCAN I_OBJ# (cr=547671 pr=0 pw=0 time=701689 us cost=1 size=0 card=1)(object id 3)
         0          0          0               FILTER  (cr=0 pr=0 pw=0 time=137747 us)
         0          0          0                TABLE ACCESS BY INDEX ROWID TABPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)
         0          0          0                 INDEX UNIQUE SCAN I_TABPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 572)
         0          0          0               FILTER  (cr=0 pr=0 pw=0 time=82101 us)
         0          0          0                TABLE ACCESS CLUSTER CLU$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=14 card=1)
         0          0          0                 INDEX UNIQUE SCAN I_OBJ# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)
         0          0          0               TABLE ACCESS BY INDEX ROWID IND$ (cr=404189 pr=0 pw=0 time=642720 us cost=1 size=21 card=1)
         0          0          0                INDEX UNIQUE SCAN I_IND1 (cr=404189 pr=0 pw=0 time=485330 us cost=1 size=0 card=1)(object id 41)
         0          0          0               FILTER  (cr=0 pr=0 pw=0 time=97190 us)
         0          0          0                TABLE ACCESS BY INDEX ROWID INDPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)
         0          0          0                 INDEX UNIQUE SCAN I_INDPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 577)
         0          0          0               FILTER  (cr=0 pr=0 pw=0 time=82253 us)
         0          0          0                TABLE ACCESS BY INDEX ROWID LOB$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=1)
         0          0          0                 INDEX UNIQUE SCAN I_LOB2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 82)
         0          0          0               FILTER  (cr=0 pr=0 pw=0 time=83484 us)
         0          0          0                TABLE ACCESS BY INDEX ROWID TABSUBPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=14 card=1)
         0          0          0                 INDEX UNIQUE SCAN I_TABSUBPART$_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 584)
         0          0          0               FILTER  (cr=0 pr=0 pw=0 time=82854 us)
         0          0          0                TABLE ACCESS BY INDEX ROWID INDSUBPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=52 card=1)
         0          0          0                 INDEX UNIQUE SCAN I_INDSUBPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 589)
         0          0          0               TABLE ACCESS BY INDEX ROWID LOBFRAG$ (cr=351256 pr=0 pw=0 time=494716 us cost=1 size=19 card=1)
         0          0          0                INDEX UNIQUE SCAN I_LOBFRAG$_FRAGOBJ$ (cr=351256 pr=0 pw=0 time=343878 us cost=1 size=0 card=1)(object id 603)
     37802      37802      37802            TABLE ACCESS CLUSTER SEG$ (cr=105785 pr=0 pw=0 time=708449 us cost=1 size=15 card=1)
     37802      37802      37802             INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=3800 pr=0 pw=0 time=345966 us cost=1 size=0 card=1)(object id 9)
     37802      37802      37802           INDEX UNIQUE SCAN I_FILE2 (cr=3795 pr=0 pw=0 time=42849 us cost=1 size=7 card=1)(object id 44)
         0          0          0          FILTER  (cr=11316 pr=0 pw=0 time=51686 us)
         0          0          0           NESTED LOOPS OUTER (cr=11316 pr=0 pw=0 time=49751 us cost=5 size=91 card=1)
         0          0          0            NESTED LOOPS  (cr=11316 pr=0 pw=0 time=46300 us cost=4 size=76 card=1)
         0          0          0             NESTED LOOPS  (cr=11316 pr=0 pw=0 time=44112 us cost=3 size=58 card=1)
         0          0          0              NESTED LOOPS  (cr=11316 pr=0 pw=0 time=42368 us cost=2 size=51 card=1)
      3772       3772       3772               TABLE ACCESS BY INDEX ROWID TS$ (cr=7544 pr=0 pw=0 time=23357 us cost=1 size=17 card=1)
      3772       3772       3772                INDEX UNIQUE SCAN I_TS1 (cr=3772 pr=0 pw=0 time=13161 us cost=1 size=0 card=1)(object id 45)
         0          0          0               TABLE ACCESS BY INDEX ROWID UNDO$ (cr=3772 pr=0 pw=0 time=15986 us cost=1 size=34 card=1)
         0          0          0                INDEX RANGE SCAN I_UNDO2 (cr=3772 pr=0 pw=0 time=13965 us cost=1 size=0 card=1)(object id 35)
         0          0          0              INDEX UNIQUE SCAN I_FILE2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=7 card=1)(object id 44)
         0          0          0             TABLE ACCESS CLUSTER SEG$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=18 card=1)
         0          0          0              INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 9)
         0          0          0            TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=15 card=1)
         0          0          0             INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 11)
         0          0          0          NESTED LOOPS  (cr=599753 pr=0 pw=0 time=1028191 us)
         0          0          0           NESTED LOOPS  (cr=599753 pr=0 pw=0 time=1025997 us cost=16 size=61 card=1)
         0          0          0            FILTER  (cr=599753 pr=0 pw=0 time=1024264 us)
         0          0          0             NESTED LOOPS OUTER (cr=599753 pr=0 pw=0 time=1022270 us cost=15 size=50 card=1)
         0          0          0              NESTED LOOPS  (cr=599753 pr=0 pw=0 time=1020246 us cost=14 size=35 card=1)
      3772       3772       3772               TABLE ACCESS BY INDEX ROWID TS$ (cr=7544 pr=0 pw=0 time=11764 us cost=1 size=17 card=1)
      3772       3772       3772                INDEX UNIQUE SCAN I_TS1 (cr=3772 pr=0 pw=0 time=6214 us cost=1 size=0 card=1)(object id 45)
         0          0          0               TABLE ACCESS CLUSTER SEG$ (cr=592209 pr=0 pw=0 time=1005127 us cost=13 size=18 card=1)
    347024     347024     347024                INDEX RANGE SCAN I_FILE#_BLOCK# (cr=3777 pr=0 pw=0 time=103850 us cost=1 size=0 card=1)(object id 9)
         0          0          0              TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=15 card=1)
         0          0          0               INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 11)
         0          0          0            INDEX UNIQUE SCAN I_FILE2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 44)
         0          0          0           TABLE ACCESS BY INDEX ROWID FILE$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=11 card=1)
        20         20         20       TABLE ACCESS CLUSTER TS$ (cr=48 pr=0 pw=0 time=170 us cost=1 size=3 card=1)
        20         20         20        INDEX UNIQUE SCAN I_TS# (cr=20 pr=0 pw=0 time=52 us cost=1 size=0 card=1)(object id 7)
        20         20         20      INDEX RANGE SCAN I_USER2 (cr=20 pr=0 pw=0 time=75 us cost=1 size=4 card=1)(object id 47)
         0          0          0     INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=22 us cost=1 size=4 card=1)(object id 47)
        20         20         20    TABLE ACCESS CLUSTER SEG$ (cr=70 pr=0 pw=0 time=194 us cost=1 size=12 card=1)
        20         20         20     INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=31 pr=0 pw=0 time=77 us cost=1 size=0 card=1)(object id 9)

********************************************************************************
Thanks for your time.
  • 1. Re: performance tuning - anlayze db time difference of 2 queries
    spur230 Newbie
    Currently Being Moderated
    Herer is tkprof snnipet for query 2:

    Query 2
    SELECT    SUM (bytes) / 1024 / 1024 / 1024 GB
      FROM   dba_segments ds , (SELECT   index_name
                   FROM   dba_indexes 
                  WHERE   table_name IN (SELECT   SEGMENT_NAME
                                FROM   dba_segments
                               WHERE       tablespace_name = 'CRDDATA_03'
                                       AND segment_type = 'TABLE'
                                          AND owner = 'CRDMAIN'
                                       and segment_name like 'H%' ) 
                                       )                                    b
                                where        b.index_name =ds.segment_name
             AND ds.tablespace_name <> 'CRDDATA_03'
             AND ds.segment_type = 'INDEX'
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.74       0.75          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.14       0.14          0      28490          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.89       0.90          0      28490          0           1
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 66  
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT AGGREGATE (cr=28490 pr=0 pw=0 time=149529 us)
            20         20         20   NESTED LOOPS OUTER (cr=28490 pr=0 pw=0 time=128860 us cost=748 size=339 card=1)
            20         20         20    NESTED LOOPS OUTER (cr=28490 pr=0 pw=0 time=128835 us cost=747 size=335 card=1)
            20         20         20     NESTED LOOPS  (cr=28482 pr=0 pw=0 time=128789 us cost=746 size=326 card=1)
            20         20         20      HASH JOIN  (cr=28479 pr=0 pw=0 time=128685 us cost=745 size=322 card=1)
            21         21         21       NESTED LOOPS  (cr=4209 pr=0 pw=0 time=121212 us)
            21         21         21        NESTED LOOPS  (cr=4201 pr=0 pw=0 time=121146 us cost=163 size=163 card=1)
            21         21         21         NESTED LOOPS OUTER (cr=4184 pr=0 pw=0 time=120898 us cost=162 size=126 card=1)
            21         21         21          NESTED LOOPS OUTER (cr=4117 pr=0 pw=0 time=120608 us cost=161 size=114 card=1)
            21         21         21           NESTED LOOPS  (cr=4078 pr=0 pw=0 time=120477 us cost=160 size=111 card=1)
            20         20         20            NESTED LOOPS  (cr=4035 pr=0 pw=0 time=35852 us cost=159 size=160 card=2)
            20         20         20             NESTED LOOPS  (cr=4032 pr=0 pw=0 time=35730 us cost=158 size=152 card=2)
            10         10         10              VIEW  VW_NSO_1 (cr=754 pr=0 pw=0 time=5196 us cost=91 size=126 card=3)
            10         10         10               HASH UNIQUE (cr=754 pr=0 pw=0 time=5195 us)
            10         10         10                VIEW  SYS_DBA_SEGS (cr=754 pr=0 pw=0 time=3428 us cost=91 size=126 card=3)
            10         10         10                 UNION-ALL  (cr=754 pr=0 pw=0 time=3419 us)
            10         10         10                  NESTED LOOPS  (cr=591 pr=0 pw=0 time=3415 us cost=70 size=143 card=1)
            10         10         10                   NESTED LOOPS  (cr=587 pr=0 pw=0 time=3347 us cost=69 size=136 card=1)
            93         93         93                    NESTED LOOPS  (cr=551 pr=0 pw=0 time=6381 us cost=68 size=121 card=1)
            93         93         93                     FILTER  (cr=30 pr=0 pw=0 time=2024 us)
           454        454        454                      HASH JOIN RIGHT OUTER (cr=30 pr=0 pw=0 time=1992 us cost=62 size=140 card=2)
           183        183        183                       TABLE ACCESS FULL USER$ (cr=9 pr=0 pw=0 time=158 us cost=4 size=2745 card=183)
           454        454        454                       NESTED LOOPS  (cr=21 pr=0 pw=0 time=763 us cost=58 size=15455 card=281)
             1          1          1                        TABLE ACCESS BY INDEX ROWID TS$ (cr=2 pr=0 pw=0 time=25 us cost=1 size=17 card=1)
             1          1          1                         INDEX UNIQUE SCAN I_TS1 (cr=1 pr=0 pw=0 time=15 us cost=1 size=0 card=1)(object id 45)
           454        454        454                        INDEX SKIP SCAN I_OBJ2 (cr=19 pr=0 pw=0 time=736 us cost=57 size=10678 card=281)(object id 37)
            93         93         93                     VIEW  SYS_OBJECTS (cr=521 pr=0 pw=0 time=1966 us cost=3 size=51 card=1)
            93         93         93                      UNION ALL PUSHED PREDICATE  (cr=521 pr=0 pw=0 time=1879 us)
            93         93         93                       TABLE ACCESS CLUSTER TAB$ (cr=318 pr=0 pw=0 time=805 us cost=1 size=22 card=1)
            93         93         93                        INDEX UNIQUE SCAN I_OBJ# (cr=144 pr=0 pw=0 time=212 us cost=1 size=0 card=1)(object id 3)
             0          0          0                       FILTER  (cr=0 pr=0 pw=0 time=37 us)
             0          0          0                        TABLE ACCESS BY INDEX ROWID TABPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)
             0          0          0                         INDEX UNIQUE SCAN I_TABPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 572)
             0          0          0                       FILTER  (cr=0 pr=0 pw=0 time=29 us)
             0          0          0                        TABLE ACCESS CLUSTER CLU$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=14 card=1)
             0          0          0                         INDEX UNIQUE SCAN I_OBJ# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)
             0          0          0                       TABLE ACCESS BY INDEX ROWID IND$ (cr=110 pr=0 pw=0 time=198 us cost=1 size=21 card=1)
             0          0          0                        INDEX UNIQUE SCAN I_IND1 (cr=110 pr=0 pw=0 time=156 us cost=1 size=0 card=1)(object id 41)
             0          0          0                       FILTER  (cr=0 pr=0 pw=0 time=33 us)
             0          0          0                        TABLE ACCESS BY INDEX ROWID INDPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)
             0          0          0                         INDEX UNIQUE SCAN I_INDPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 577)
             0          0          0                       FILTER  (cr=0 pr=0 pw=0 time=17 us)
             0          0          0                        TABLE ACCESS BY INDEX ROWID LOB$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=1)
             0          0          0                         INDEX UNIQUE SCAN I_LOB2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 82)
             0          0          0                       FILTER  (cr=0 pr=0 pw=0 time=15 us)
             0          0          0                        TABLE ACCESS BY INDEX ROWID TABSUBPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=14 card=1)
             0          0          0                         INDEX UNIQUE SCAN I_TABSUBPART$_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 584)
             0          0          0                       FILTER  (cr=0 pr=0 pw=0 time=32 us)
             0          0          0                        TABLE ACCESS BY INDEX ROWID INDSUBPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=52 card=1)
             0          0          0                         INDEX UNIQUE SCAN I_INDSUBPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 589)
             0          0          0                       TABLE ACCESS BY INDEX ROWID LOBFRAG$ (cr=93 pr=0 pw=0 time=139 us cost=1 size=19 card=1)
             0          0          0                        INDEX UNIQUE SCAN I_LOBFRAG$_FRAGOBJ$ (cr=93 pr=0 pw=0 time=98 us cost=1 size=0 card=1)(object id 603)
            10         10         10                    TABLE ACCESS CLUSTER SEG$ (cr=36 pr=0 pw=0 time=265 us cost=1 size=15 card=1)
            10         10         10                     INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=9 pr=0 pw=0 time=123 us cost=1 size=0 card=1)(object id 9)
            10         10         10                   INDEX UNIQUE SCAN I_FILE2 (cr=4 pr=0 pw=0 time=38 us cost=1 size=7 card=1)(object id 44)
             0          0          0                  FILTER  (cr=3 pr=0 pw=0 time=26 us)
             0          0          0                   NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=26 us cost=5 size=91 card=1)
             0          0          0                    NESTED LOOPS  (cr=3 pr=0 pw=0 time=26 us cost=4 size=76 card=1)
             0          0          0                     NESTED LOOPS  (cr=3 pr=0 pw=0 time=23 us cost=3 size=58 card=1)
             0          0          0                      NESTED LOOPS  (cr=3 pr=0 pw=0 time=23 us cost=2 size=51 card=1)
             1          1          1                       TABLE ACCESS BY INDEX ROWID TS$ (cr=2 pr=0 pw=0 time=5 us cost=1 size=17 card=1)
             1          1          1                        INDEX UNIQUE SCAN I_TS1 (cr=1 pr=0 pw=0 time=3 us cost=1 size=0 card=1)(object id 45)
             0          0          0                       TABLE ACCESS BY INDEX ROWID UNDO$ (cr=1 pr=0 pw=0 time=17 us cost=1 size=34 card=1)
             0          0          0                        INDEX RANGE SCAN I_UNDO2 (cr=1 pr=0 pw=0 time=16 us cost=1 size=0 card=1)(object id 35)
             0          0          0                      INDEX UNIQUE SCAN I_FILE2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=7 card=1)(object id 44)
             0          0          0                     TABLE ACCESS CLUSTER SEG$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=18 card=1)
             0          0          0                      INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 9)
             0          0          0                    TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=15 card=1)
             0          0          0                     INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 11)
             0          0          0                  NESTED LOOPS  (cr=160 pr=0 pw=0 time=445 us)
             0          0          0                   NESTED LOOPS  (cr=160 pr=0 pw=0 time=445 us cost=16 size=61 card=1)
             0          0          0                    FILTER  (cr=160 pr=0 pw=0 time=445 us)
             0          0          0                     NESTED LOOPS OUTER (cr=160 pr=0 pw=0 time=444 us cost=15 size=50 card=1)
             0          0          0                      NESTED LOOPS  (cr=160 pr=0 pw=0 time=443 us cost=14 size=35 card=1)
             1          1          1                       TABLE ACCESS BY INDEX ROWID TS$ (cr=2 pr=0 pw=0 time=4 us cost=1 size=17 card=1)
             1          1          1                        INDEX UNIQUE SCAN I_TS1 (cr=1 pr=0 pw=0 time=2 us cost=1 size=0 card=1)(object id 45)
             0          0          0                       TABLE ACCESS CLUSTER SEG$ (cr=158 pr=0 pw=0 time=439 us cost=13 size=18 card=1)
            92         92         92                        INDEX RANGE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=5 us cost=1 size=0 card=1)(object id 9)
             0          0          0                      TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=15 card=1)
             0          0          0                       INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 11)
             0          0          0                    INDEX UNIQUE SCAN I_FILE2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 44)
             0          0          0                   TABLE ACCESS BY INDEX ROWID FILE$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=11 card=1)
            20         20         20              INDEX FULL SCAN I_OBJ2 (cr=3278 pr=0 pw=0 time=49322 us cost=66 size=68 card=2)(object id 37)
            20         20         20             INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=72 us cost=1 size=4 card=1)(object id 47)
            21         21         21            TABLE ACCESS CLUSTER IND$ (cr=43 pr=0 pw=0 time=149 us cost=1 size=31 card=1)
            10         10         10             INDEX UNIQUE SCAN I_OBJ# (cr=22 pr=0 pw=0 time=56 us cost=1 size=0 card=1)(object id 3)
            21         21         21           TABLE ACCESS CLUSTER TS$ (cr=39 pr=0 pw=0 time=122 us cost=1 size=3 card=1)
            21         21         21            INDEX UNIQUE SCAN I_TS# (cr=4 pr=0 pw=0 time=28 us cost=1 size=0 card=1)(object id 7)
            21         21         21          TABLE ACCESS CLUSTER SEG$ (cr=67 pr=0 pw=0 time=195 us cost=1 size=12 card=1)
            21         21         21           INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=22 pr=0 pw=0 time=66 us cost=1 size=0 card=1)(object id 9)
            21         21         21         INDEX RANGE SCAN I_OBJ1 (cr=17 pr=0 pw=0 time=85 us cost=1 size=0 card=1)(object id 36)
            21         21         21        TABLE ACCESS BY INDEX ROWID OBJ$ (cr=8 pr=0 pw=0 time=46 us cost=1 size=37 card=1)
          3797       3797       3797       VIEW  SYS_DBA_SEGS (cr=24270 pr=0 pw=0 time=70518 us cost=582 size=6519 card=41)
          3797       3797       3797        UNION-ALL  (cr=24270 pr=0 pw=0 time=70136 us)
          3797       3797       3797         HASH JOIN OUTER (cr=14408 pr=0 pw=0 time=67857 us cost=493 size=2686 card=17)
          3797       3797       3797          NESTED LOOPS  (cr=14407 pr=0 pw=0 time=71464 us)
          3797       3797       3797           NESTED LOOPS  (cr=14180 pr=0 pw=0 time=65767 us cost=491 size=2618 card=17)
          3797       3797       3797            NESTED LOOPS  (cr=13486 pr=0 pw=0 time=55890 us cost=484 size=1938 card=17)
          3819       3819       3819             NESTED LOOPS  (cr=8368 pr=0 pw=0 time=35953 us cost=481 size=1564 card=17)
          3819       3819       3819              NESTED LOOPS  (cr=8364 pr=0 pw=0 time=30094 us cost=480 size=1445 card=17)
          3968       3968       3968               VIEW  SYS_OBJECTS (cr=1232 pr=0 pw=0 time=11579 us cost=463 size=5046 card=87)
          3968       3968       3968                UNION-ALL  (cr=1232 pr=0 pw=0 time=10426 us)
             0          0          0                 TABLE ACCESS FULL TAB$ (cr=615 pr=0 pw=0 time=3202 us cost=230 size=704 card=32)
             0          0          0                 FILTER  (cr=0 pr=0 pw=0 time=2 us)
             0          0          0                  TABLE ACCESS FULL TABPART$ (cr=0 pr=0 pw=0 time=0 us cost=4 size=5440 card=320)
             0          0          0                 FILTER  (cr=0 pr=0 pw=0 time=1 us)
             0          0          0                  TABLE ACCESS FULL CLU$ (cr=0 pr=0 pw=0 time=0 us cost=230 size=140 card=10)
          3968       3968       3968                 TABLE ACCESS FULL IND$ (cr=615 pr=0 pw=0 time=4886 us cost=230 size=1008 card=48)
             0          0          0                 FILTER  (cr=0 pr=0 pw=0 time=1 us)
             0          0          0                  TABLE ACCESS FULL INDPART$ (cr=0 pr=0 pw=0 time=0 us cost=4 size=6273 card=369)
             0          0          0                 FILTER  (cr=0 pr=0 pw=0 time=1 us)
             0          0          0                  TABLE ACCESS FULL LOB$ (cr=0 pr=0 pw=0 time=0 us cost=226 size=18216 card=828)
             0          0          0                 FILTER  (cr=0 pr=0 pw=0 time=1 us)
             0          0          0                  TABLE ACCESS FULL TABSUBPART$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=448 card=32)
             0          0          0                 FILTER  (cr=0 pr=0 pw=0 time=0 us)
             0          0          0                  TABLE ACCESS FULL INDSUBPART$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=52 card=1)
             0          0          0                 TABLE ACCESS FULL LOBFRAG$ (cr=2 pr=0 pw=0 time=18 us cost=2 size=19 card=1)
          3819       3819       3819               TABLE ACCESS CLUSTER SEG$ (cr=7132 pr=0 pw=0 time=20304 us cost=1 size=27 card=1)
          3819       3819       3819                INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2842 pr=0 pw=0 time=7117 us cost=1 size=0 card=1)(object id 9)
          3819       3819       3819              INDEX UNIQUE SCAN I_FILE2 (cr=4 pr=0 pw=0 time=3525 us cost=1 size=7 card=1)(object id 44)
          3797       3797       3797             TABLE ACCESS CLUSTER TS$ (cr=5118 pr=0 pw=0 time=14175 us cost=1 size=22 card=1)
          3819       3819       3819              INDEX UNIQUE SCAN I_TS# (cr=4 pr=0 pw=0 time=2601 us cost=1 size=0 card=1)(object id 7)
          3797       3797       3797            INDEX RANGE SCAN I_OBJ1 (cr=694 pr=0 pw=0 time=7186 us cost=1 size=0 card=1)(object id 36)
          3797       3797       3797           TABLE ACCESS BY INDEX ROWID OBJ$ (cr=227 pr=0 pw=0 time=3397 us cost=1 size=40 card=1)
           183        183        183          INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=96 us cost=1 size=732 card=183)(object id 47)
             0          0          0         NESTED LOOPS  (cr=31 pr=0 pw=0 time=147 us cost=9 size=100 card=1)
             0          0          0          NESTED LOOPS OUTER (cr=31 pr=0 pw=0 time=145 us cost=8 size=78 card=1)
             0          0          0           NESTED LOOPS  (cr=31 pr=0 pw=0 time=144 us cost=7 size=74 card=1)
             0          0          0            NESTED LOOPS  (cr=31 pr=0 pw=0 time=143 us cost=6 size=67 card=1)
            19         19         19             TABLE ACCESS FULL UNDO$ (cr=2 pr=0 pw=0 time=47 us cost=2 size=703 card=19)
             0          0          0             TABLE ACCESS CLUSTER SEG$ (cr=29 pr=0 pw=0 time=99 us cost=1 size=30 card=1)
            19         19         19              INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=10 pr=0 pw=0 time=28 us cost=1 size=0 card=1)(object id 9)
             0          0          0            INDEX UNIQUE SCAN I_FILE2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=7 card=1)(object id 44)
             0          0          0           INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)
             0          0          0          TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=1)
             0          0          0           INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 7)
             0          0          0         HASH JOIN OUTER (cr=9831 pr=0 pw=0 time=18781 us cost=80 size=1679 card=23)
             0          0          0          NESTED LOOPS  (cr=9831 pr=0 pw=0 time=18739 us cost=79 size=1587 card=23)
             0          0          0           NESTED LOOPS  (cr=9831 pr=0 pw=0 time=18738 us cost=74 size=1081 card=23)
           180        180        180            TABLE ACCESS FULL FILE$ (cr=2 pr=0 pw=0 time=106 us cost=2 size=1980 card=180)
             0          0          0            TABLE ACCESS CLUSTER SEG$ (cr=9829 pr=0 pw=0 time=18518 us cost=1 size=36 card=1)
          8972       8972       8972             INDEX RANGE SCAN I_FILE#_BLOCK# (cr=120 pr=0 pw=0 time=2630 us cost=1 size=0 card=1)(object id 9)
             0          0          0           TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=1)
             0          0          0            INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 7)
             0          0          0          INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=732 card=183)(object id 47)
            20         20         20      INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=32 us cost=1 size=4 card=1)(object id 47)
             0          0          0     INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=22 us cost=1 size=9 card=1)(object id 36)
             0          0          0    INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=5 us cost=1 size=4 card=1)(object id 47)
    
    
    
    
    ********************************************************************************
  • 2. Re: performance tuning - anlayze db time difference of 2 queries
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,
    spur230 wrote:
    I'm using Oracle 11.2.0.3. I wanted to understand why elasped time between following two queries is so diffent.
    well first of all the two queries select different things. But from the performance perspective the most important difference is that the level of subquery nesting. The 1st query is using an IN subquery where the 2nd one is using a join. You can see that in the plan -- the very first operation in 1st query's plan is FILTER, meaning that results from one rowsource are kept or discarded depending on results from the other rowsource.

    Just rewrite the 1st query as a join (using the 2nd query as example) and you should see performance improvements.

    Best regards,
    Nikolay

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points