How to speed up this

From: astalavista <nobody_at_nowhere.com>
Date: Sat, 15 Nov 2008 13:46:22 +0100
Message-ID: <491ec49e$0$20800$426a74cc@news.free.fr>

Hi,

Have you any hint to speed up the query below ?

(oracle 9.2.0.6)

Thanks in advance

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 581 0.45 0.43 0 0 0 0
Execute 581 1.65 1.61 0 0 0 0
Fetch 581 20.73 20.86 0 913913 0 581
------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1743 22.84 22.90 0 913913 0 581

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 157 (recursive depth: 3)

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1573 r=0 w=0 time=31109 us)
      0   VIEW  (cr=1573 r=0 w=0 time=31101 us)
      0    MINUS  (cr=1573 r=0 w=0 time=31099 us)
      0     SORT UNIQUE (cr=1543 r=0 w=0 time=30469 us)
      0      UNION-ALL  (cr=1543 r=0 w=0 time=30455 us)
      0       MINUS  (cr=1530 r=0 w=0 time=12328 us)
     21        SORT UNIQUE (cr=1215 r=0 w=0 time=9360 us)
     21         UNION-ALL  (cr=1215 r=0 w=0 time=9257 us)
      0          MINUS  (cr=765 r=0 w=0 time=6264 us)
     28           SORT UNIQUE (cr=450 r=0 w=0 time=3333 us)
     28            FILTER  (cr=450 r=0 w=0 time=3094 us)
     53             NESTED LOOPS OUTER (cr=439 r=0 w=0 time=2644 us)
     53              NESTED LOOPS OUTER (cr=439 r=0 w=0 time=2385 us)
     53               NESTED LOOPS OUTER (cr=439 r=0 w=0 time=2057 us)
     53                NESTED LOOPS OUTER (cr=384 r=0 w=0 time=1340 us)
     53                 NESTED LOOPS  (cr=66 r=0 w=0 time=585 us)
      1                  NESTED LOOPS  (cr=6 r=0 w=0 time=119 us)
      1                   TABLE ACCESS BY INDEX ROWID USER$ (cr=3 r=0 w=0 
time=53 us)
      1                    INDEX UNIQUE SCAN I_USER1 (cr=2 r=0 w=0 time=34 
us)(object id 44)
      1                   TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 
time=49 us)
      1                    INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=31 
us)(object id 37)
     53                  TABLE ACCESS CLUSTER COL$ (cr=60 r=0 w=0 time=389 
us)
      1                   INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=10 
us)(object id 3)
      0                 TABLE ACCESS CLUSTER COLTYPE$ (cr=318 r=0 w=0 
time=593 us)
     53                INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=55 r=0 w=0 
time=460 us)(object id 221)
      0               TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 
time=118 us)
      0                INDEX RANGE SCAN I_OBJ3 (cr=0 r=0 w=0 time=39 
us)(object id 38)
      0              TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=113 us)
      0               INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=37 
us)(object id 11)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS CLUSTER TAB$ (cr=7 r=0 w=0 time=33 us)
      1              INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=10 
us)(object id 3)
      0             NESTED LOOPS  (cr=4 r=0 w=0 time=45 us)
      2              FIXED TABLE FULL X$KZSRO (cr=0 r=0 w=0 time=8 us)
      0              INDEX RANGE SCAN I_OBJAUTH2 (cr=4 r=0 w=0 time=27 
us)(object id 109)
      1             FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=41 us)
     36           SORT UNIQUE (cr=315 r=0 w=0 time=2865 us)
     36            FILTER  (cr=315 r=0 w=0 time=2621 us)
     59             NESTED LOOPS OUTER (cr=307 r=0 w=0 time=2218 us)
     59              NESTED LOOPS OUTER (cr=307 r=0 w=0 time=1937 us)
     59               NESTED LOOPS OUTER (cr=307 r=0 w=0 time=1569 us)
     59                NESTED LOOPS OUTER (cr=246 r=0 w=0 time=1056 us)
     59                 NESTED LOOPS  (cr=69 r=0 w=0 time=519 us)
      1                  NESTED LOOPS  (cr=6 r=0 w=0 time=62 us)
      1                   TABLE ACCESS BY INDEX ROWID USER$ (cr=3 r=0 w=0 
time=24 us)
      1                    INDEX UNIQUE SCAN I_USER1 (cr=2 r=0 w=0 time=12 
us)(object id 44)
      1                   TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 
time=28 us)
      1                    INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=15 
us)(object id 37)
     59                  TABLE ACCESS CLUSTER COL$ (cr=63 r=0 w=0 time=368 
us)
      1                   INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=9 
us)(object id 3)
      0                 TABLE ACCESS CLUSTER COLTYPE$ (cr=177 r=0 w=0 
time=373 us)
      0                INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=61 r=0 w=0 
time=297 us)(object id 221)
      0               TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 
time=133 us)
      0                INDEX RANGE SCAN I_OBJ3 (cr=0 r=0 w=0 time=39 
us)(object id 38)
      0              TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=123 us)
      0               INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=39 
us)(object id 11)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS BY INDEX ROWID OBJ$
      0              INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0             TABLE ACCESS CLUSTER TAB$ (cr=4 r=0 w=0 time=23 us)
      1              INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=7 
us)(object id 3)
      0             NESTED LOOPS  (cr=4 r=0 w=0 time=35 us)
      2              FIXED TABLE FULL X$KZSRO (cr=0 r=0 w=0 time=5 us)
      0              INDEX RANGE SCAN I_OBJAUTH2 (cr=4 r=0 w=0 time=18 
us)(object id 109)
      1             FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=26 us)
     21          FILTER  (cr=450 r=0 w=0 time=2864 us)
     53           NESTED LOOPS OUTER (cr=439 r=0 w=0 time=2472 us)
     53            NESTED LOOPS OUTER (cr=439 r=0 w=0 time=2212 us)
     53             NESTED LOOPS OUTER (cr=439 r=0 w=0 time=1899 us)
     53              NESTED LOOPS OUTER (cr=384 r=0 w=0 time=1204 us)
     53               NESTED LOOPS  (cr=66 r=0 w=0 time=476 us)
      1                NESTED LOOPS  (cr=6 r=0 w=0 time=57 us)
      1                 TABLE ACCESS BY INDEX ROWID USER$ (cr=3 r=0 w=0 
time=22 us)
      1                  INDEX UNIQUE SCAN I_USER1 (cr=2 r=0 w=0 time=12 
us)(object id 44)
      1                 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 
time=25 us)
      1                  INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=13 
us)(object id 37)
     53                TABLE ACCESS CLUSTER COL$ (cr=60 r=0 w=0 time=338 us)
      1                 INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=8 
us)(object id 3)
      0               TABLE ACCESS CLUSTER COLTYPE$ (cr=318 r=0 w=0 time=567 
us)
     53              INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=55 r=0 w=0 
time=425 us)(object id 221)
      0             TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 time=122 
us)
      0              INDEX RANGE SCAN I_OBJ3 (cr=0 r=0 w=0 time=44 
us)(object id 38)
      0            TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=119 us)
      0             INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=38 
us)(object id 11)
      0           TABLE ACCESS BY INDEX ROWID OBJ$
      0            INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0           TABLE ACCESS BY INDEX ROWID OBJ$
      0            INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0           TABLE ACCESS BY INDEX ROWID OBJ$
      0            INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0           TABLE ACCESS BY INDEX ROWID OBJ$
      0            INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0           TABLE ACCESS BY INDEX ROWID OBJ$
      0            INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0           TABLE ACCESS CLUSTER TAB$ (cr=7 r=0 w=0 time=29 us)
      1            INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=8 us)(object 
id 3)
      0           NESTED LOOPS  (cr=4 r=0 w=0 time=36 us)
      2            FIXED TABLE FULL X$KZSRO (cr=0 r=0 w=0 time=6 us)
      0            INDEX RANGE SCAN I_OBJAUTH2 (cr=4 r=0 w=0 time=20 
us)(object id 109)
      1           FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=26 us)
     21        SORT UNIQUE (cr=315 r=0 w=0 time=2905 us)
     21         FILTER  (cr=315 r=0 w=0 time=2745 us)
     59          NESTED LOOPS OUTER (cr=307 r=0 w=0 time=2356 us)
     59           NESTED LOOPS OUTER (cr=307 r=0 w=0 time=2064 us)
     59            NESTED LOOPS OUTER (cr=307 r=0 w=0 time=1689 us)
     59             NESTED LOOPS OUTER (cr=246 r=0 w=0 time=1136 us)
     59              NESTED LOOPS  (cr=69 r=0 w=0 time=528 us)
      1               NESTED LOOPS  (cr=6 r=0 w=0 time=66 us)
      1                TABLE ACCESS BY INDEX ROWID USER$ (cr=3 r=0 w=0 
time=25 us)
      1                 INDEX UNIQUE SCAN I_USER1 (cr=2 r=0 w=0 time=14 
us)(object id 44)
      1                TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 
time=28 us)
      1                 INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=16 
us)(object id 37)
     59               TABLE ACCESS CLUSTER COL$ (cr=63 r=0 w=0 time=380 us)
      1                INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=8 
us)(object id 3)
      0              TABLE ACCESS CLUSTER COLTYPE$ (cr=177 r=0 w=0 time=382 
us)
      0             INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=61 r=0 w=0 
time=317 us)(object id 221)
      0            TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 time=136 
us)
      0             INDEX RANGE SCAN I_OBJ3 (cr=0 r=0 w=0 time=47 us)(object 
id 38)
      0           TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=132 us)
      0            INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=43 
us)(object id 11)
      0          TABLE ACCESS BY INDEX ROWID OBJ$
      0           INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0          TABLE ACCESS BY INDEX ROWID OBJ$
      0           INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0          TABLE ACCESS BY INDEX ROWID OBJ$
      0           INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0          TABLE ACCESS BY INDEX ROWID OBJ$
      0           INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0          TABLE ACCESS BY INDEX ROWID OBJ$
      0           INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0          TABLE ACCESS CLUSTER TAB$ (cr=4 r=0 w=0 time=21 us)
      1           INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=7 us)(object 
id 3)
      0          NESTED LOOPS  (cr=4 r=0 w=0 time=36 us)
      2           FIXED TABLE FULL X$KZSRO (cr=0 r=0 w=0 time=7 us)
      0           INDEX RANGE SCAN I_OBJAUTH2 (cr=4 r=0 w=0 time=20 
us)(object id 109)
      1          FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=26 us)
      0       FILTER  (cr=13 r=0 w=0 time=18100 us)
      0        NESTED LOOPS OUTER (cr=13 r=0 w=0 time=18098 us)
      0         NESTED LOOPS OUTER (cr=13 r=0 w=0 time=18095 us)
      0          NESTED LOOPS OUTER (cr=13 r=0 w=0 time=18093 us)
      0           NESTED LOOPS OUTER (cr=13 r=0 w=0 time=18091 us)
      0            NESTED LOOPS  (cr=13 r=0 w=0 time=18089 us)
      1             NESTED LOOPS  (cr=6 r=0 w=0 time=84 us)
      1              TABLE ACCESS BY INDEX ROWID USER$ (cr=3 r=0 w=0 time=33 
us)
      1               INDEX UNIQUE SCAN I_USER1 (cr=2 r=0 w=0 time=20 
us)(object id 44)
      1              TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 time=39 
us)
      1               INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=23 
us)(object id 37)
      0             TABLE ACCESS CLUSTER COL$ (cr=7 r=0 w=0 time=17997 us)
      1              INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=11 
us)(object id 3)
      0            TABLE ACCESS CLUSTER COLTYPE$
      0           INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (object id 221)
      0          TABLE ACCESS BY INDEX ROWID OBJ$
      0           INDEX RANGE SCAN I_OBJ3 (object id 38)
      0         TABLE ACCESS CLUSTER USER$
      0          INDEX UNIQUE SCAN I_USER# (object id 11)
      0        TABLE ACCESS BY INDEX ROWID OBJ$
      0         INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0        TABLE ACCESS BY INDEX ROWID OBJ$
      0         INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0        TABLE ACCESS BY INDEX ROWID OBJ$
      0         INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0        TABLE ACCESS BY INDEX ROWID OBJ$
      0         INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0        TABLE ACCESS BY INDEX ROWID OBJ$
      0         INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0        TABLE ACCESS CLUSTER TAB$
      0         INDEX UNIQUE SCAN I_OBJ# (object id 3)
      0        NESTED LOOPS
      0         FIXED TABLE FULL X$KZSRO
      0         INDEX RANGE SCAN I_OBJAUTH2 (object id 109)
      0        FIXED TABLE FULL X$KZSPR
      2     SORT UNIQUE (cr=30 r=0 w=0 time=624 us)
      2      FILTER  (cr=30 r=0 w=0 time=565 us)
      2       NESTED LOOPS OUTER (cr=22 r=0 w=0 time=316 us)
      2        NESTED LOOPS OUTER (cr=22 r=0 w=0 time=298 us)
      2         NESTED LOOPS OUTER (cr=22 r=0 w=0 time=277 us)
      2          NESTED LOOPS OUTER (cr=18 r=0 w=0 time=241 us)
      2           NESTED LOOPS  (cr=12 r=0 w=0 time=212 us)
      1            NESTED LOOPS  (cr=6 r=0 w=0 time=98 us)
      1             TABLE ACCESS BY INDEX ROWID USER$ (cr=3 r=0 w=0 time=38 
us)
      1              INDEX UNIQUE SCAN I_USER1 (cr=2 r=0 w=0 time=24 
us)(object id 44)
      1             TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 time=38 
us)
      1              INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=23 
us)(object id 37)
      2            TABLE ACCESS CLUSTER COL$ (cr=6 r=0 w=0 time=104 us)
      1             INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=10 
us)(object id 3)
      0           TABLE ACCESS CLUSTER COLTYPE$ (cr=6 r=0 w=0 time=19 us)
      0          INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=4 r=0 w=0 time=21 
us)(object id 221)
      0         TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 time=5 us)
      0          INDEX RANGE SCAN I_OBJ3 (cr=0 r=0 w=0 time=1 us)(object id 
38)
      0        TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=5 us)
      0         INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=1 us)(object id 
11)
      0       TABLE ACCESS BY INDEX ROWID OBJ$
      0        INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0       TABLE ACCESS BY INDEX ROWID OBJ$
      0        INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0       TABLE ACCESS BY INDEX ROWID OBJ$
      0        INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0       TABLE ACCESS BY INDEX ROWID OBJ$
      0        INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0       TABLE ACCESS BY INDEX ROWID OBJ$
      0        INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0       TABLE ACCESS CLUSTER TAB$ (cr=4 r=0 w=0 time=26 us)
      1        INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=7 us)(object id 
3)
      0       NESTED LOOPS  (cr=4 r=0 w=0 time=38 us)
      2        FIXED TABLE FULL X$KZSRO (cr=0 r=0 w=0 time=8 us)
      0        INDEX RANGE SCAN I_OBJAUTH2 (cr=4 r=0 w=0 time=21 us)(object 
id 109)
      1       FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=37 us)

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

SELECT COUNT (*) FROM (SELECT column_name

|| :"SYS_B_00"

|| data_type

|| :"SYS_B_01"

|| data_length

|| :"SYS_B_02"
FROM all_tab_columns

WHERE owner = :"SYS_B_03"

AND table_name = :"SYS_B_04"

AND data_type LIKE :"SYS_B_05"

MINUS SELECT column_name

|| :"SYS_B_06"

|| data_type

|| :"SYS_B_07"

|| data_length

|| :"SYS_B_08"
FROM all_tab_columns

WHERE owner = :"SYS_B_09"

AND table_name = :"SYS_B_10"

AND data_type LIKE :"SYS_B_11"

UNION SELECT column_name || :"SYS_B_12" || data_type

FROM all_tab_columns

WHERE owner = :"SYS_B_13"

AND table_name = :"SYS_B_14"

AND data_type LIKE :"SYS_B_15"

MINUS SELECT column_name || :"SYS_B_16" || data_type

FROM all_tab_columns

WHERE owner = :"SYS_B_17"

AND table_name = :"SYS_B_18"

AND data_type LIKE :"SYS_B_19"

UNION SELECT column_name

|| :"SYS_B_20"

|| column_name

|| :"SYS_B_21"

|| column_name

|| :"SYS_B_22"

|| column_name

|| :"SYS_B_23"
FROM all_tab_columns

WHERE owner = :"SYS_B_24"

AND table_name = :"SYS_B_25"

AND data_type LIKE :"SYS_B_26"

AND column_name NOT IN (:"SYS_B_27", :"SYS_B_28")

AND pck_si_don.fct_donner_info_item_2 (:"SYS_B_29",

:"SYS_B_30", column_name,

:"SYS_B_31" ) = :"SYS_B_32" MINUS SELECT column_name

|| :"SYS_B_33"

|| column_name

|| :"SYS_B_34"

|| column_name

|| :"SYS_B_35"

|| column_name

|| :"SYS_B_36"
FROM all_tab_columns

WHERE owner = :"SYS_B_37"

AND table_name = :"SYS_B_38"

AND data_type LIKE :"SYS_B_39"

AND data_length = :"SYS_B_40"

AND column_name LIKE :"SYS_B_41"

AND column_name NOT IN (:"SYS_B_42", :"SYS_B_43")) Received on Sat Nov 15 2008 - 06:46:22 CST

This message: [ Message body ]
Next message: hpuxrac: "Re: anyone have a decent purge script for 11g diag directories"
Previous message: jan.benjamins_at_gmail.com: "Re: anyone have a decent purge script for 11g diag directories"
Next in thread: Mladen Gogala: "Re: How to speed up this"
Reply: Mladen Gogala: "Re: How to speed up this"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message