This discussion is archived
5 Replies Latest reply: Dec 4, 2012 1:55 AM by user12155961 RSS

high parse time

user12155961 Newbie
Currently Being Moderated
hello,

we use a tool that execute some sql's against dictionary views, for example:

select uc_fk.constraint_name FK_NAME,uc_fk.owner FKTABLE_SCHEM,
ucc_fk.table_name FKTABLE_NAME,ucc_fk.column_name FKCOLUMN_NAME,
decode(uc_fk.deferrable, 'DEFERRABLE', 5 ,'NOT DEFERRABLE', 7 , 'DEFERRED',
6 ) DEFERRABILITY, decode(uc_fk.delete_rule, 'CASCADE', 0,'NO ACTION', 3)
DELETE_RULE,ucc_rf.table_name PKTABLE_NAME,ucc_rf.column_name PKCOLUMN_NAME
from
all_cons_columns ucc_fk,all_constraints uc_fk,all_cons_columns ucc_rf,
all_constraints uc_rf
where uc_fk.CONSTRAINT_NAME = ucc_fk.CONSTRAINT_NAME
and uc_fk.constraint_type='R' and uc_fk.r_constraint_name=
ucc_rf.CONSTRAINT_NAME and uc_rf.constraint_name = ucc_rf.constraint_name
and uc_rf.constraint_type = 'U' and uc_fk.owner = 'TEST' and
ucc_fk.owner = 'TEST' and uc_rf.owner = 'TEST' and ucc_rf.owner =
'TEST'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 *15.16* *18.51* 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.03 0 3112 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 15.19 18.55 0 3112 0 0

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

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 FILTER (cr=3112 pr=0 pw=0 time=32658 us)
0 0 0 NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32655 us cost=114 size=822 card=1)
0 0 0 NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32651 us cost=111 size=820 card=1)
0 0 0 NESTED LOOPS (cr=3112 pr=0 pw=0 time=32651 us cost=108 size=818 card=1)
0 0 0 NESTED LOOPS (cr=3112 pr=0 pw=0 time=32647 us cost=107 size=797 card=1)
0 0 0 NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32646 us cost=105 size=782 card=1)
0 0 0 NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32644 us cost=104 size=779 card=1)
0 0 0 NESTED LOOPS (cr=3112 pr=0 pw=0 time=32644 us cost=103 size=774 card=1)
0 0 0 NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32642 us cost=102 size=662 card=1)
0 0 0 NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32642 us cost=101 size=659 card=1)
0 0 0 NESTED LOOPS (cr=3112 pr=0 pw=0 time=32641 us cost=100 size=651 card=1)
181 181 181 HASH JOIN (cr=2888 pr=0 pw=0 time=33127 us cost=99 size=630 card=1)
15 15 15 NESTED LOOPS (cr=2834 pr=0 pw=0 time=51812 us cost=82 size=607 card=1)
15 15 15 NESTED LOOPS (cr=2831 pr=0 pw=0 time=51695 us cost=81 size=586 card=1)
15 15 15 NESTED LOOPS OUTER (cr=2817 pr=0 pw=0 time=51531 us cost=79 size=554 card=1)
15 15 15 NESTED LOOPS (cr=2778 pr=0 pw=0 time=51372 us cost=78 size=505 card=1)
15 15 15 NESTED LOOPS (cr=2747 pr=0 pw=0 time=51224 us cost=77 size=484 card=1)
15 15 15 NESTED LOOPS (cr=2726 pr=0 pw=0 time=51017 us cost=75 size=470 card=1)
15 15 15 NESTED LOOPS (cr=2723 pr=0 pw=0 time=50941 us cost=74 size=449 card=1)
15 15 15 NESTED LOOPS OUTER (cr=2709 pr=0 pw=0 time=50801 us cost=72 size=434 card=1)
15 15 15 NESTED LOOPS OUTER (cr=2706 pr=0 pw=0 time=50710 us cost=71 size=431 card=1)
15 15 15 NESTED LOOPS (cr=2698 pr=0 pw=0 time=50595 us cost=70 size=426 card=1)
15 15 15 NESTED LOOPS (cr=2695 pr=0 pw=0 time=50514 us cost=69 size=405 card=1)
15 15 15 NESTED LOOPS OUTER (cr=2683 pr=0 pw=0 time=50281 us cost=67 size=373 card=1)
15 15 15 NESTED LOOPS (cr=2648 pr=0 pw=0 time=50148 us cost=66 size=324 card=1)
15 15 15 NESTED LOOPS (cr=2616 pr=0 pw=0 time=49892 us cost=65 size=303 card=1)
15 15 15 NESTED LOOPS (cr=2594 pr=0 pw=0 time=49592 us cost=63 size=289 card=1)
176 176 176 NESTED LOOPS OUTER (cr=2414 pr=0 pw=0 time=26904 us cost=62 size=177 card=1)
176 176 176 NESTED LOOPS (cr=2411 pr=0 pw=0 time=25916 us cost=61 size=174 card=1)
176 176 176 NESTED LOOPS (cr=2129 pr=0 pw=0 time=24333 us cost=60 size=161 card=1)
176 176 176 NESTED LOOPS (cr=1944 pr=0 pw=0 time=23539 us cost=59 size=138 card=1)
176 176 176 NESTED LOOPS (cr=1587 pr=0 pw=0 time=22395 us cost=58 size=115 card=1)
176 176 176 NESTED LOOPS (cr=1305 pr=0 pw=0 time=18419 us cost=57 size=102 card=1)
513 513 513 NESTED LOOPS (cr=1020 pr=0 pw=0 time=22990 us cost=55 size=395 card=5)
513 513 513 NESTED LOOPS (cr=174 pr=0 pw=0 time=17622 us cost=50 size=280 card=5)
1 1 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=87 us cost=2 size=30 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=78 us cost=1 size=15 card=1)
1 1 1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=50 us cost=0 size=0 card=1)(object id 46)
1 1 1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=6 us cost=1 size=15 card=1)
1 1 1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=3 us cost=0 size=0 card=1)(object id 46)
513 513 513 TABLE ACCESS FULL CDEF$ (cr=170 pr=0 pw=0 time=17025 us cost=48 size=130 card=5)
513 513 513 TABLE ACCESS BY INDEX ROWID CON$ (cr=846 pr=0 pw=0 time=2907 us cost=1 size=23 card=1)
513 513 513 INDEX UNIQUE SCAN I_CON2 (cr=333 pr=0 pw=0 time=1549 us cost=0 size=0 card=1)(object id 52)
176 176 176 TABLE ACCESS BY INDEX ROWID CON$ (cr=285 pr=0 pw=0 time=1638 us cost=1 size=23 card=1)
176 176 176 INDEX UNIQUE SCAN I_CON1 (cr=109 pr=0 pw=0 time=971 us cost=0 size=0 card=1)(object id 51)
176 176 176 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=282 pr=0 pw=0 time=1185 us cost=1 size=13 card=1)
176 176 176 INDEX UNIQUE SCAN I_CDEF1 (cr=106 pr=0 pw=0 time=590 us cost=0 size=0 card=1)(object id 53)
176 176 176 TABLE ACCESS BY INDEX ROWID CON$ (cr=357 pr=0 pw=0 time=817 us cost=1 size=23 card=1)
176 176 176 INDEX UNIQUE SCAN I_CON2 (cr=181 pr=0 pw=0 time=473 us cost=0 size=0 card=1)(object id 52)
176 176 176 TABLE ACCESS BY INDEX ROWID CON$ (cr=185 pr=0 pw=0 time=654 us cost=1 size=23 card=1)
176 176 176 INDEX UNIQUE SCAN I_CON1 (cr=9 pr=0 pw=0 time=344 us cost=0 size=0 card=1)(object id 51)
176 176 176 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=282 pr=0 pw=0 time=792 us cost=1 size=13 card=1)
176 176 176 INDEX UNIQUE SCAN I_CDEF1 (cr=106 pr=0 pw=0 time=338 us cost=0 size=0 card=1)(object id 53)
176 176 176 INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=407 us cost=1 size=3 card=1)(object id 47)
15 15 15 TABLE ACCESS CLUSTER USER$ (cr=180 pr=0 pw=0 time=1048 us cost=1 size=112 card=1)
176 176 176 INDEX UNIQUE SCAN I_USER# (cr=4 pr=0 pw=0 time=252 us cost=0 size=0 card=1)(object id 11)
15 15 15 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=22 pr=0 pw=0 time=237 us cost=2 size=14 card=1)
15 15 15 INDEX RANGE SCAN I_CCOL1 (cr=16 pr=0 pw=0 time=192 us cost=1 size=0 card=1)(object id 57)
15 15 15 TABLE ACCESS BY INDEX ROWID COL$ (cr=32 pr=0 pw=0 time=131 us cost=1 size=21 card=1)
15 15 15 INDEX UNIQUE SCAN I_COL3 (cr=17 pr=0 pw=0 time=90 us cost=0 size=0 card=1)(object id 50)
0 0 0 TABLE ACCESS CLUSTER ATTRCOL$ (cr=35 pr=0 pw=0 time=153 us cost=1 size=49 card=1)
15 15 15 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=12 pr=0 pw=0 time=179 us cost=2 size=32 card=1)
15 15 15 INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=114 us cost=1 size=0 card=1)(object id 36)
15 15 15 INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=46 us cost=1 size=21 card=1)(object id 47)
15 15 15 INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=77 us cost=1 size=5 card=1)(object id 36)
15 15 15 INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=33 us cost=1 size=3 card=1)(object id 47)
15 15 15 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=14 pr=0 pw=0 time=102 us cost=2 size=15 card=1)
15 15 15 INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=57 us cost=1 size=0 card=1)(object id 36)
15 15 15 INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=41 us cost=1 size=21 card=1)(object id 47)
15 15 15 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=21 pr=0 pw=0 time=337 us cost=2 size=14 card=1)
15 15 15 INDEX RANGE SCAN I_CCOL1 (cr=16 pr=0 pw=0 time=75 us cost=1 size=0 card=1)(object id 57)
15 15 15 TABLE ACCESS BY INDEX ROWID COL$ (cr=31 pr=0 pw=0 time=152 us cost=1 size=21 card=1)
15 15 15 INDEX UNIQUE SCAN I_COL3 (cr=16 pr=0 pw=0 time=105 us cost=0 size=0 card=1)(object id 50)
0 0 0 TABLE ACCESS CLUSTER ATTRCOL$ (cr=39 pr=0 pw=0 time=113 us cost=1 size=49 card=1)
15 15 15 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=14 pr=0 pw=0 time=89 us cost=2 size=32 card=1)
15 15 15 INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=56 us cost=1 size=0 card=1)(object id 36)
15 15 15 INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=41 us cost=1 size=21 card=1)(object id 47)
10953 10953 10953 TABLE ACCESS FULL CON$ (cr=54 pr=0 pw=0 time=4276 us cost=16 size=250953 card=10911)
0 0 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=224 pr=0 pw=0 time=1101 us cost=1 size=21 card=1)
181 181 181 INDEX UNIQUE SCAN I_CDEF1 (cr=170 pr=0 pw=0 time=554 us cost=0 size=0 card=1)(object id 53)
0 0 0 TABLE ACCESS BY INDEX ROWID CON$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=8 card=1)
0 0 0 INDEX UNIQUE SCAN I_CON2 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 52)
0 0 0 INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 47)
0 0 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=112 card=1)
0 0 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 11)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=5 card=1)(object id 36)
0 0 0 INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 47)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36)
0 0 0 INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=21 card=1)(object id 47)
0 0 0 VIEW PUSHED PREDICATE CURRENTEDITION_OBJ (cr=0 pr=0 pw=0 time=0 us cost=3 size=2 card=1)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=3 size=33 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 36)
0 0 0 INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=21 card=1)(object id 47)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
0 0 0 VIEW PUSHED PREDICATE CURRENTEDITION_OBJ (cr=0 pr=0 pw=0 time=0 us cost=3 size=2 card=1)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=3 size=33 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 36)
0 0 0 INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=21 card=1)(object id 47)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)

parse time is very high, this is each time if the sql is executed, what can be done about this? Is this normal behaviour?

database is Oracle 11.2.0.2 (64bit) on SUSE Linux Enterprise Server 11 (s390x) VERSION = 11 PATCHLEVEL = 1
4GB ram, 1 cpu.

thanks.
  • 1. Re: high parse time
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user12155961 wrote:
    hello,

    we use a tool that execute some sql's against dictionary views, for example:
    select uc_fk.constraint_name FK_NAME,uc_fk.owner FKTABLE_SCHEM,
    ucc_fk.table_name FKTABLE_NAME,ucc_fk.column_name FKCOLUMN_NAME,
    decode(uc_fk.deferrable, 'DEFERRABLE', 5 ,'NOT DEFERRABLE', 7 , 'DEFERRED', 
    6 ) DEFERRABILITY, decode(uc_fk.delete_rule, 'CASCADE', 0,'NO ACTION', 3) 
    DELETE_RULE,ucc_rf.table_name PKTABLE_NAME,ucc_rf.column_name PKCOLUMN_NAME 
    from
    all_cons_columns ucc_fk,all_constraints uc_fk,all_cons_columns ucc_rf,
    all_constraints uc_rf 
    where uc_fk.CONSTRAINT_NAME = ucc_fk.CONSTRAINT_NAME 
    and uc_fk.constraint_type='R' and uc_fk.r_constraint_name=
    ucc_rf.CONSTRAINT_NAME and uc_rf.constraint_name = ucc_rf.constraint_name 
    and uc_rf.constraint_type = 'U' and uc_fk.owner = 'TEST' and 
    ucc_fk.owner = 'TEST' and uc_rf.owner = 'TEST' and ucc_rf.owner = 
    'TEST'
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1     *15.16*      *18.51*          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.03       0.03          0       3112          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3     15.19      18.55          0       3112          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 41  
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
    0          0          0  FILTER  (cr=3112 pr=0 pw=0 time=32658 us)
    0          0          0   NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32655 us cost=114 size=822 card=1)
    0          0          0    NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32651 us cost=111 size=820 card=1)
    0          0          0     NESTED LOOPS  (cr=3112 pr=0 pw=0 time=32651 us cost=108 size=818 card=1)
    0          0          0      NESTED LOOPS  (cr=3112 pr=0 pw=0 time=32647 us cost=107 size=797 card=1)
    0          0          0       NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32646 us cost=105 size=782 card=1)
    0          0          0        NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32644 us cost=104 size=779 card=1)
    0          0          0         NESTED LOOPS  (cr=3112 pr=0 pw=0 time=32644 us cost=103 size=774 card=1)
    0          0          0          NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32642 us cost=102 size=662 card=1)
    0          0          0           NESTED LOOPS OUTER (cr=3112 pr=0 pw=0 time=32642 us cost=101 size=659 card=1)
    0          0          0            NESTED LOOPS  (cr=3112 pr=0 pw=0 time=32641 us cost=100 size=651 card=1)
    181        181        181             HASH JOIN  (cr=2888 pr=0 pw=0 time=33127 us cost=99 size=630 card=1)
    15         15         15              NESTED LOOPS  (cr=2834 pr=0 pw=0 time=51812 us cost=82 size=607 card=1)
    15         15         15               NESTED LOOPS  (cr=2831 pr=0 pw=0 time=51695 us cost=81 size=586 card=1)
    15         15         15                NESTED LOOPS OUTER (cr=2817 pr=0 pw=0 time=51531 us cost=79 size=554 card=1)
    15         15         15                 NESTED LOOPS  (cr=2778 pr=0 pw=0 time=51372 us cost=78 size=505 card=1)
    15         15         15                  NESTED LOOPS  (cr=2747 pr=0 pw=0 time=51224 us cost=77 size=484 card=1)
    15         15         15                   NESTED LOOPS  (cr=2726 pr=0 pw=0 time=51017 us cost=75 size=470 card=1)
    15         15         15                    NESTED LOOPS  (cr=2723 pr=0 pw=0 time=50941 us cost=74 size=449 card=1)
    15         15         15                     NESTED LOOPS OUTER (cr=2709 pr=0 pw=0 time=50801 us cost=72 size=434 card=1)
    15         15         15                      NESTED LOOPS OUTER (cr=2706 pr=0 pw=0 time=50710 us cost=71 size=431 card=1)
    15         15         15                       NESTED LOOPS  (cr=2698 pr=0 pw=0 time=50595 us cost=70 size=426 card=1)
    15         15         15                        NESTED LOOPS  (cr=2695 pr=0 pw=0 time=50514 us cost=69 size=405 card=1)
    15         15         15                         NESTED LOOPS OUTER (cr=2683 pr=0 pw=0 time=50281 us cost=67 size=373 card=1)
    15         15         15                          NESTED LOOPS  (cr=2648 pr=0 pw=0 time=50148 us cost=66 size=324 card=1)
    15         15         15                           NESTED LOOPS  (cr=2616 pr=0 pw=0 time=49892 us cost=65 size=303 card=1)
    15         15         15                            NESTED LOOPS  (cr=2594 pr=0 pw=0 time=49592 us cost=63 size=289 card=1)
    176        176        176                             NESTED LOOPS OUTER (cr=2414 pr=0 pw=0 time=26904 us cost=62 size=177 card=1)
    176        176        176                              NESTED LOOPS  (cr=2411 pr=0 pw=0 time=25916 us cost=61 size=174 card=1)
    176        176        176                               NESTED LOOPS  (cr=2129 pr=0 pw=0 time=24333 us cost=60 size=161 card=1)
    176        176        176                                NESTED LOOPS  (cr=1944 pr=0 pw=0 time=23539 us cost=59 size=138 card=1)
    176        176        176                                 NESTED LOOPS  (cr=1587 pr=0 pw=0 time=22395 us cost=58 size=115 card=1)
    176        176        176                                  NESTED LOOPS  (cr=1305 pr=0 pw=0 time=18419 us cost=57 size=102 card=1)
    513        513        513                                   NESTED LOOPS  (cr=1020 pr=0 pw=0 time=22990 us cost=55 size=395 card=5)
    513        513        513                                    NESTED LOOPS  (cr=174 pr=0 pw=0 time=17622 us cost=50 size=280 card=5)
    1          1          1                                     NESTED LOOPS  (cr=4 pr=0 pw=0 time=87 us cost=2 size=30 card=1)
    1          1          1                                      TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=78 us cost=1 size=15 card=1)
    1          1          1                                       INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=50 us cost=0 size=0 card=1)(object id 46)
    1          1          1                                      TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=6 us cost=1 size=15 card=1)
    1          1          1                                       INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=3 us cost=0 size=0 card=1)(object id 46)
    513        513        513                                     TABLE ACCESS FULL CDEF$ (cr=170 pr=0 pw=0 time=17025 us cost=48 size=130 card=5)
    513        513        513                                    TABLE ACCESS BY INDEX ROWID CON$ (cr=846 pr=0 pw=0 time=2907 us cost=1 size=23 card=1)
    513        513        513                                     INDEX UNIQUE SCAN I_CON2 (cr=333 pr=0 pw=0 time=1549 us cost=0 size=0 card=1)(object id 52)
    176        176        176                                   TABLE ACCESS BY INDEX ROWID CON$ (cr=285 pr=0 pw=0 time=1638 us cost=1 size=23 card=1)
    176        176        176                                    INDEX UNIQUE SCAN I_CON1 (cr=109 pr=0 pw=0 time=971 us cost=0 size=0 card=1)(object id 51)
    176        176        176                                  TABLE ACCESS BY INDEX ROWID CDEF$ (cr=282 pr=0 pw=0 time=1185 us cost=1 size=13 card=1)
    176        176        176                                   INDEX UNIQUE SCAN I_CDEF1 (cr=106 pr=0 pw=0 time=590 us cost=0 size=0 card=1)(object id 53)
    176        176        176                                 TABLE ACCESS BY INDEX ROWID CON$ (cr=357 pr=0 pw=0 time=817 us cost=1 size=23 card=1)
    176        176        176                                  INDEX UNIQUE SCAN I_CON2 (cr=181 pr=0 pw=0 time=473 us cost=0 size=0 card=1)(object id 52)
    176        176        176                                TABLE ACCESS BY INDEX ROWID CON$ (cr=185 pr=0 pw=0 time=654 us cost=1 size=23 card=1)
    176        176        176                                 INDEX UNIQUE SCAN I_CON1 (cr=9 pr=0 pw=0 time=344 us cost=0 size=0 card=1)(object id 51)
    176        176        176                               TABLE ACCESS BY INDEX ROWID CDEF$ (cr=282 pr=0 pw=0 time=792 us cost=1 size=13 card=1)
    176        176        176                                INDEX UNIQUE SCAN I_CDEF1 (cr=106 pr=0 pw=0 time=338 us cost=0 size=0 card=1)(object id 53)
    176        176        176                              INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=407 us cost=1 size=3 card=1)(object id 47)
    15         15         15                             TABLE ACCESS CLUSTER USER$ (cr=180 pr=0 pw=0 time=1048 us cost=1 size=112 card=1)
    176        176        176                              INDEX UNIQUE SCAN I_USER# (cr=4 pr=0 pw=0 time=252 us cost=0 size=0 card=1)(object id 11)
    15         15         15                            TABLE ACCESS BY INDEX ROWID CCOL$ (cr=22 pr=0 pw=0 time=237 us cost=2 size=14 card=1)
    15         15         15                             INDEX RANGE SCAN I_CCOL1 (cr=16 pr=0 pw=0 time=192 us cost=1 size=0 card=1)(object id 57)
    15         15         15                           TABLE ACCESS BY INDEX ROWID COL$ (cr=32 pr=0 pw=0 time=131 us cost=1 size=21 card=1)
    15         15         15                            INDEX UNIQUE SCAN I_COL3 (cr=17 pr=0 pw=0 time=90 us cost=0 size=0 card=1)(object id 50)
    0          0          0                          TABLE ACCESS CLUSTER ATTRCOL$ (cr=35 pr=0 pw=0 time=153 us cost=1 size=49 card=1)
    15         15         15                         TABLE ACCESS BY INDEX ROWID OBJ$ (cr=12 pr=0 pw=0 time=179 us cost=2 size=32 card=1)
    15         15         15                          INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=114 us cost=1 size=0 card=1)(object id 36)
    15         15         15                        INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=46 us cost=1 size=21 card=1)(object id 47)
    15         15         15                       INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=77 us cost=1 size=5 card=1)(object id 36)
    15         15         15                      INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=33 us cost=1 size=3 card=1)(object id 47)
    15         15         15                     TABLE ACCESS BY INDEX ROWID OBJ$ (cr=14 pr=0 pw=0 time=102 us cost=2 size=15 card=1)
    15         15         15                      INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=57 us cost=1 size=0 card=1)(object id 36)
    15         15         15                    INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=41 us cost=1 size=21 card=1)(object id 47)
    15         15         15                   TABLE ACCESS BY INDEX ROWID CCOL$ (cr=21 pr=0 pw=0 time=337 us cost=2 size=14 card=1)
    15         15         15                    INDEX RANGE SCAN I_CCOL1 (cr=16 pr=0 pw=0 time=75 us cost=1 size=0 card=1)(object id 57)
    15         15         15                  TABLE ACCESS BY INDEX ROWID COL$ (cr=31 pr=0 pw=0 time=152 us cost=1 size=21 card=1)
    15         15         15                   INDEX UNIQUE SCAN I_COL3 (cr=16 pr=0 pw=0 time=105 us cost=0 size=0 card=1)(object id 50)
    0          0          0                 TABLE ACCESS CLUSTER ATTRCOL$ (cr=39 pr=0 pw=0 time=113 us cost=1 size=49 card=1)
    15         15         15                TABLE ACCESS BY INDEX ROWID OBJ$ (cr=14 pr=0 pw=0 time=89 us cost=2 size=32 card=1)
    15         15         15                 INDEX RANGE SCAN I_OBJ1 (cr=8 pr=0 pw=0 time=56 us cost=1 size=0 card=1)(object id 36)
    15         15         15               INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=41 us cost=1 size=21 card=1)(object id 47)
    10953      10953      10953              TABLE ACCESS FULL CON$ (cr=54 pr=0 pw=0 time=4276 us cost=16 size=250953 card=10911)
    0          0          0             TABLE ACCESS BY INDEX ROWID CDEF$ (cr=224 pr=0 pw=0 time=1101 us cost=1 size=21 card=1)
    181        181        181              INDEX UNIQUE SCAN I_CDEF1 (cr=170 pr=0 pw=0 time=554 us cost=0 size=0 card=1)(object id 53)
    0          0          0            TABLE ACCESS BY INDEX ROWID CON$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=8 card=1)
    0          0          0             INDEX UNIQUE SCAN I_CON2 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 52)
    0          0          0           INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 47)
    0          0          0          TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=112 card=1)
    0          0          0           INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 11)
    0          0          0         INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=5 card=1)(object id 36)
    0          0          0        INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 47)
    0          0          0       TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
    0          0          0        INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36)
    0          0          0      INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=21 card=1)(object id 47)
    0          0          0     VIEW PUSHED PREDICATE  _CURRENT_EDITION_OBJ (cr=0 pr=0 pw=0 time=0 us cost=3 size=2 card=1)
    0          0          0      FILTER  (cr=0 pr=0 pw=0 time=0 us)
    0          0          0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=3 size=33 card=1)
    0          0          0        INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 36)
    0          0          0        INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=21 card=1)(object id 47)
    0          0          0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
    0          0          0        INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
    0          0          0        INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
    0          0          0    VIEW PUSHED PREDICATE  _CURRENT_EDITION_OBJ (cr=0 pr=0 pw=0 time=0 us cost=3 size=2 card=1)
    0          0          0     FILTER  (cr=0 pr=0 pw=0 time=0 us)
    0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=3 size=33 card=1)
    0          0          0       INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 36)
    0          0          0       INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=21 card=1)(object id 47)
    0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
    0          0          0       INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
    0          0          0       INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
    0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
    0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
    0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
    0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
    0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
    0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
    0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
    0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
    0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
    0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
    0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
    0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
    0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
    0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
    0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
    0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
    0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
    0          0          0    INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
    0          0          0    INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
    0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
    0          0          0    INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
    0          0          0    INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
    0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
    0          0          0    INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
    0          0          0    INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
    0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
    0          0          0    INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
    0          0          0    INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)(object id 39)
    parse time is very high, this is each time if the sql is executed, what can be done about this? Is this normal behaviour?

    database is Oracle 11.2.0.2 (64bit) on SUSE Linux Enterprise Server 11 (s390x) VERSION = 11 PATCHLEVEL = 1
    4GB ram, 1 cpu.
    It looks like you're expecting your unique keys and foreign key constraints to be single column only. Presumably you're ignoring primary keys here because you have another query for them.

    You may simply have a problem to do with statistics out of date, so check when stats were last gathered on all the underlying tables. If it's not a stats problem then given the fairly large number of objects used in the resolution of the four views, you might consider rewriting the query as a join of two non-mergeable views - pseudo-code:
    select
            list of columns
    from
            (select /*+ no_merge */ ... query for unique key information owned by user test) uk,
            (select /*+ no_merge */ ... query for foreign key information owned by user test) fk
    where
            join between unique keys and foreign keys
    ;
    Regards
    Jonathan Lewis
  • 2. Re: high parse time
    Nikolay Savvinov Guru
    Currently Being Moderated
    hi,

    try looking at tkprof output with recursive queries included (sys='y' if I remember well). If the problem is high parse time, then the resulting plan is of little interest (Oracle spends most of the time producing the plan, not executing it), what is potentially more interesting here is plans and execution statistics for recursive queries against the data dictionary.

    Best regards,
    Nikolay
  • 3. Re: high parse time
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Nikolay Savvinov wrote:

    what is potentially more interesting here is plans and execution statistics for recursive queries against the data dictionary.
    Nikolay,

    Probably not in this case - the parse line shows zeros for buffer visits, and I think you'd see non--zero values echoed from the sys-recursive SQL if that were a significant contributor to the time.

    Regards
    Jonathan Lewis
  • 4. Re: high parse time
    Nikolay Savvinov Guru
    Currently Being Moderated
    hi Jonathan
    Jonathan Lewis wrote:
    Nikolay Savvinov wrote:

    what is potentially more interesting here is plans and execution statistics for recursive queries against the data dictionary.
    Nikolay,

    Probably not in this case - the parse line shows zeros for buffer visits, and I think you'd see non--zero values echoed from the sys-recursive SQL if that were a significant contributor to the time.
    oops -- missed that ... and the parse time probably cannot be explained by waits, either (because of the CPU time)... ok, then I'm out of ideas... probably one of the cases for "rocket scientists" :)

    Best regards,
    Nikolay
  • 5. Re: high parse time
    user12155961 Newbie
    Currently Being Moderated
    rewriting the query is not an option, it is a tool that is not ours ...

    statistics have run, also the sys-stats ...

    so I don't have an idea how to solve this problem ...

    but thx anyway!

Legend

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