This discussion is archived
14 Replies Latest reply: May 10, 2013 1:05 PM by 759978 RSS

CBO horribly slow with SYS views such as all_constraints/all_cons_columns

759978 Newbie
Currently Being Moderated
I have a problem with an Oracle 11g Release 11.2.0.1.0 database, where a query such as:
SELECT fields
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = '&tab'
AND ac.constraint_type = 'R'
AND ac.owner = '&own'
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner
AND ac.r_constraint_name = rem.constraint_name
AND loc.position=rem.position
ORDER BY constraint_name, loc_pos;

Takes a very very long time, but adding the /*+ RULE */ hint makes it execute quite fast.

When I ran:
SQL> exec DBMS_STATS.DELETE_DICTIONARY_STATS();

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS();

PL/SQL procedure successfully completed.

Then afterwards, the query was fast (without the /*+ RULE */ hint).

Gathering the stats again:
SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS();

Slowed it back down.

I can't imagine that best practice is to DELETE_DICTIONARY_STATS()? Can anyone explain what is going on and advise?

Many thanks for your time!
  • 1. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    VishalP-Oracle Pro
    Currently Being Moderated
    Can you paste the explain plan?
  • 2. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    759978 Newbie
    Currently Being Moderated
    h1.
    Certainly, with CBO:
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1809780121

    -------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 730 | 41 (3)| 00:00:01 |
    | 1 | SORT ORDER BY | | 1 | 730 | 41 (3)| 00:00:01 |
    |* 2 | FILTER | | | | | |
    | 3 | NESTED LOOPS OUTER | | 1 | 730 | 40 (0)| 00:00:01 |
    | 4 | NESTED LOOPS | | 1 | 728 | 38 (0)| 00:00:01 |
    | 5 | NESTED LOOPS | | 1 | 706 | 37 (0)| 00:00:01 |
    | 6 | NESTED LOOPS OUTER | | 1 | 673 | 36 (0)| 00:00:01 |
    | 7 | NESTED LOOPS OUTER | | 1 | 669 | 35 (0)| 00:00:01 |
    | 8 | NESTED LOOPS | | 1 | 664 | 34 (0)| 00:00:01 |
    | 9 | NESTED LOOPS | | 1 | 642 | 33 (0)| 00:00:01 |
    | 10 | NESTED LOOPS OUTER | | 1 | 630 | 32 (0)| 00:00:01 |
    | 11 | NESTED LOOPS | | 1 | 588 | 31 (0)| 00:00:01 |
    | 12 | NESTED LOOPS | | 1 | 568 | 30 (0)| 00:00:01 |
    | 13 | NESTED LOOPS | | 1 | 552 | 29 (0)| 00:00:01 |
    | 14 | NESTED LOOPS | | 1 | 530 | 28 (0)| 00:00:01 |
    | 15 | NESTED LOOPS OUTER | | 1 | 500 | 27 (0)| 00:00:01 |
    | 16 | NESTED LOOPS | | 1 | 458 | 26 (0)| 00:00:01 |
    | 17 | NESTED LOOPS | | 1 | 438 | 25 (0)| 00:00:01 |
    | 18 | NESTED LOOPS | | 1 | 422 | 24 (0)| 00:00:01 |
    | 19 | NESTED LOOPS | | 1 | 409 | 23 (0)| 00:00:01 |
    | 20 | NESTED LOOPS | | 1 | 386 | 22 (0)| 00:00:01 |
    | 21 | NESTED LOOPS OUTER | | 1 | 376 | 21 (0)| 00:00:01 |
    | 22 | NESTED LOOPS | | 1 | 244 | 20 (0)| 00:00:01 |
    | 23 | NESTED LOOPS | | 1 | 221 | 19 (0)| 00:00:01 |
    | 24 | NESTED LOOPS | | 1 | 201 | 18 (0)| 00:00:01 |
    | 25 | NESTED LOOPS | | 4 | 712 | 17 (0)| 00:00:01 |
    | 26 | NESTED LOOPS | | 4 | 660 | 16 (0)| 00:00:01 |
    | 27 | NESTED LOOPS | | 1 | 142 | 9 (0)| 00:00:01 |
    | 28 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 10 | 1 (0)| 00:00:01 |
    |* 29 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 (0)| 00:00:01 |
    |* 30 | TABLE ACCESS FULL | USER$ | 1 | 132 | 8 (0)| 00:00:01 |
    | 31 | TABLE ACCESS BY INDEX ROWID | CON$ | 401 | 9223 | 7 (0)| 00:00:01 |
    |* 32 | INDEX RANGE SCAN | I_CON1 | 401 | | 1 (0)| 00:00:01 |
    |* 33 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 13 | 1 (0)| 00:00:01 |
    |* 34 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
    | 35 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 23 | 1 (0)| 00:00:01 |
    |* 36 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
    |* 37 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 20 | 1 (0)| 00:00:01 |
    |* 38 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
    | 39 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 23 | 1 (0)| 00:00:01 |
    |* 40 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 1 (0)| 00:00:01 |
    | 41 | TABLE ACCESS CLUSTER | USER$ | 1 | 132 | 1 (0)| 00:00:01 |
    |* 42 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
    | 43 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 10 | 1 (0)| 00:00:01 |
    |* 44 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 (0)| 00:00:01 |
    | 45 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 23 | 1 (0)| 00:00:01 |
    |* 46 | INDEX UNIQUE SCAN | I_CON1 | 1 | | 1 (0)| 00:00:01 |
    |* 47 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 13 | 1 (0)| 00:00:01 |
    |* 48 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | 1 (0)| 00:00:01 |
    |* 49 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 16 | 1 (0)| 00:00:01 |
    |* 50 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
    | 51 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 20 | 1 (0)| 00:00:01 |
    |* 52 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
    | 53 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 42 | 1 (0)| 00:00:01 |
    |* 54 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
    | 55 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 30 | 1 (0)| 00:00:01 |
    |* 56 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
    |* 57 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 |
    |* 58 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 16 | 1 (0)| 00:00:01 |
    |* 59 | INDEX RANGE SCAN | I_CCOL2 | 1 | | 1 (0)| 00:00:01 |
    | 60 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 20 | 1 (0)| 00:00:01 |
    |* 61 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
    | 62 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 42 | 1 (0)| 00:00:01 |
    |* 63 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 1 (0)| 00:00:01 |
    |* 64 | INDEX RANGE SCAN | I_OBJ1 | 1 | 12 | 1 (0)| 00:00:01 |
    |* 65 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 |
    |* 66 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
    |* 67 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 |
    |* 68 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 33 | 1 (0)| 00:00:01 |
    |* 69 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
    |* 70 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 |
    | 71 | VIEW PUSHED PREDICATE | CURRENTEDITION_OBJ | 1 | 2 | 2 (0)| 00:00:01 |
    |* 72 | FILTER | | | | | |
    | 73 | NESTED LOOPS | | 1 | 34 | 2 (0)| 00:00:01 |
    |* 74 | INDEX RANGE SCAN | I_OBJ1 | 1 | 12 | 1 (0)| 00:00:01 |
    |* 75 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 |
    | 76 | NESTED LOOPS | | 1 | 30 | 2 (0)| 00:00:01 |
    |* 77 | INDEX RANGE SCAN | I_OBJ4 | 1 | 10 | 1 (0)| 00:00:01 |
    |* 78 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
    | 79 | NESTED LOOPS | | 1 | 22 | 1 (0)| 00:00:01 |
    |* 80 | INDEX RANGE SCAN | I_OBJAUTH1 | 3 | 27 | 1 (0)| 00:00:01 |
    |* 81 | FIXED TABLE FULL | X$KZSRO | 1 | 13 | 0 (0)| 00:00:01 |
    |* 82 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |
    | 83 | NESTED LOOPS | | 1 | 22 | 1 (0)| 00:00:01 |
    |* 84 | INDEX RANGE SCAN | I_OBJAUTH1 | 3 | 27 | 1 (0)| 00:00:01 |
    |* 85 | FIXED TABLE FULL | X$KZSRO | 1 | 13 | 0 (0)| 00:00:01 |
    |* 86 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |
    | 87 | NESTED LOOPS | | 1 | 22 | 1 (0)| 00:00:01 |
    |* 88 | INDEX RANGE SCAN | I_OBJAUTH1 | 3 | 27 | 1 (0)| 00:00:01 |
    |* 89 | FIXED TABLE FULL | X$KZSRO | 1 | 13 | 0 (0)| 00:00:01 |
    |* 90 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |
    | 91 | NESTED LOOPS | | 1 | 30 | 2 (0)| 00:00:01 |
    |* 92 | INDEX RANGE SCAN | I_OBJ4 | 1 | 10 | 1 (0)| 00:00:01 |
    |* 93 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
    | 94 | NESTED LOOPS | | 1 | 30 | 2 (0)| 00:00:01 |
    |* 95 | INDEX RANGE SCAN | I_OBJ4 | 1 | 10 | 1 (0)| 00:00:01 |
    |* 96 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
    | 97 | NESTED LOOPS | | 1 | 30 | 2 (0)| 00:00:01 |
    |* 98 | INDEX RANGE SCAN | I_OBJ4 | 1 | 10 | 1 (0)| 00:00:01 |
    |* 99 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------------------------

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

    2 - filter(("O"."SPARE3"=USERENV('SCHEMAID') OR EXISTS (SELECT 0 FROM "SYS"."OBJAUTH$" "OBJAUTH$",SYS."X$KZSRO"
    "X$KZSRO" WHERE "GRANTEE#"="KZSROROL" AND "OBJ#"=:B1) OR EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE
    "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR
    (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))) AND ("C"."OWNER#"=USERENV('SCHEMAID') OR EXISTS (SELECT 0 FROM
    "SYS"."OBJAUTH$" "OBJAUTH$",SYS."X$KZSRO" "X$KZSRO" WHERE "GRANTEE#"="KZSROROL" AND "OBJ#"=:B2) OR EXISTS (SELECT 0
    FROM SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR
    (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))) AND ("C"."OWNER#"=USERENV('SCHEMAID') OR EXISTS
    (SELECT 0 FROM "SYS"."OBJAUTH$" "OBJAUTH$",SYS."X$KZSRO" "X$KZSRO" WHERE "GRANTEE#"="KZSROROL" AND "OBJ#"=:B3) OR
    EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR
    (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))) AND ("O"."TYPE#"<>4 AND
    "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND
    "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
    BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR
    "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR
    "O"."TYPE#"=87) AND ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "U"."TYPE#"=2 AND
    "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$"
    "U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B4 AND "U2"."TYPE#"=2 AND "O2"."OWNER#"="U2"."USER#"
    AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
    "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
    "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
    BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR
    "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR
    "O"."TYPE#"=87) AND ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "U"."TYPE#"=2 AND
    "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$"
    "U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B5 AND "U2"."TYPE#"=2 AND "O2"."OWNER#"="U2"."USER#"
    AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
    "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
    "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
    BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR
    "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR
    "O"."TYPE#"=87) AND ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "U"."TYPE#"=2 AND
    "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$"
    "U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B6 AND "U2"."TYPE#"=2 AND "O2"."OWNER#"="U2"."USER#"
    AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
    29 - access("U"."NAME"='MISC')
    30 - filter(DECODE("U"."TYPE#",2,SUBSTR("U"."EXT_USERNAME",0,30),"U"."NAME")='MISC' AND
    "U"."NAME"=DECODE("U"."TYPE#",2,SUBSTR("U"."EXT_USERNAME",0,30),"U"."NAME"))
    32 - access("C"."OWNER#"="U"."USER#")
    33 - filter("CD"."TYPE#"<>12 AND ("CD"."TYPE#"<14 OR "CD"."TYPE#">17))
    34 - access("C"."CON#"="CD"."CON#")
    36 - access("OC"."OWNER#"="U"."USER#" AND "OC"."NAME"="C"."NAME")
    37 - filter("C"."RCON#" IS NOT NULL AND DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C',8,'H',9,'F',10,'
    F',11,'F',13,'F','?')='R' AND "C"."TYPE#"<>12 AND "C"."TYPE#"<>8 AND ("C"."TYPE#"<14 OR "C"."TYPE#">17))
    38 - access("OC"."CON#"="C"."CON#")
    40 - access("C"."RCON#"="RC"."CON#")
    42 - access("RC"."OWNER#"="U"."USER#"(+))
    44 - access("U"."NAME"=NVL2(ROWID,DECODE("U"."TYPE#",2,SUBSTR("U"."EXT_USERNAME",0,30),"U"."NAME"),NULL))
    46 - access("C"."OWNER#"="U"."USER#" AND "RC"."NAME"="C"."NAME")
    47 - filter("CD"."TYPE#"<>12 AND ("CD"."TYPE#"<14 OR "CD"."TYPE#">17))
    48 - access("C"."CON#"="CD"."CON#")
    49 - filter("CC"."POS#" IS NOT NULL)
    50 - access("CD"."CON#"="CC"."CON#")
    52 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
    54 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
    56 - access("CC"."OBJ#"="O"."OBJ#")
    57 - access("O"."OWNER#"="U"."USER#")
    58 - filter("CC"."POS#" IS NOT NULL AND "CC"."POS#"="CC"."POS#")
    59 - access("CD"."CON#"="CC"."CON#")
    61 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#")
    63 - access("COL"."OBJ#"="AC"."OBJ#"(+) AND "COL"."INTCOL#"="AC"."INTCOL#"(+))
    64 - access("CC"."OBJ#"="O"."OBJ#")
    65 - access("O"."OWNER#"="U"."USER#")
    66 - access("C"."ENABLED"="OI"."OBJ#"(+))
    67 - access("OI"."OBJ#"="UI"."USER#"(+))
    68 - filter("O"."NAME"='STORE')
    69 - access("C"."OBJ#"="O"."OBJ#")
    70 - access("O"."OWNER#"="U"."USER#")
    72 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
    "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22
    AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
    "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
    "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND ("U"."TYPE#"<>2 AND
    SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "U"."TYPE#"=2 AND
    "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$"
    "U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "O2"."OWNER#"="U2"."USER#"
    AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
    74 - access("O"."OBJ#"="C"."ROBJ#")
    75 - access("O"."OWNER#"="U"."USER#")
    77 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
    78 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND
    "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
    filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
    80 - access("OBJ#"=:B1)
    81 - filter("GRANTEE#"="KZSROROL")
    82 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR
    (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))
    84 - access("OBJ#"=:B1)
    85 - filter("GRANTEE#"="KZSROROL")
    86 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR
    (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))
    88 - access("OBJ#"=:B1)
    89 - filter("GRANTEE#"="KZSROROL")
    90 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR
    (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))
    92 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
    93 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND
    "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
    filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
    95 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
    96 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND
    "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
    filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
    98 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
    99 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND
    "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
    filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  • 3. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    Alvaro Pro
    Currently Being Moderated
    Hi,

    It's quite possible you're suffering from degradation of performance on your fixed x$ tables.

    As you can see in the explain plan those tables are also touched by your query.

    Can you please collect stats on them and then post back stating if the performance became acceptable?

    Run the following:
    EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
    I recommend you run this while the database has a load running so the optimizer can figure out optimal plans.

    More to read:
    Data Dictionary View Queries are Slow - Potential Solutions [ID 222671.1]
    How to Gather Statistics on SYS Objects and 'Fixed' Objects? [ID 457926.1]
    GATHER_FIXED_OBJECTS_STATS Considerations [ID 798257.1]
  • 4. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    759978 Newbie
    Currently Being Moderated
    Thank you for the advice. It hits this error:

    SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ;
    BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ; END;

    *
    ERROR at line 1:
    ORA-20011: Approximate NDV failed: ORA-00439: feature not enabled: Data Guard Broker
    ORA-06512: at "SYS.DBMS_STATS", line 20508
    ORA-06512: at "SYS.DBMS_STATS", line 20951
    ORA-06512: at "SYS.DBMS_STATS", line 21498
    ORA-06512: at line 1

    I'll admit I don't know what this error refers to, but I suspect that FIXED OBJECTS stats have never been properly gathered and that is the root of my problem. Anyone help with Approximate NDV failed: ORA-00439: feature not enabled: Data Guard Broker?
  • 5. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    Alvaro Pro
    Currently Being Moderated
    Heh, you're in luck. It looks like you're hitting Bug 9753479 : GATHER_FIXED_OBJECTS_STATS FAILS WITH ORA-20011 AND ORA-00439 which is specific to your version 11.2.0.1

    There is no workaround, but if it's acceptable and you're not in production, I would bounce the database and try it again.
  • 6. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    759978 Newbie
    Currently Being Moderated
    Alvaro,
    Thanks for finding that (though with no workaround I'm somewhat stuck, I guess). The problem exists with both the production and test databases, so I did bounce the test instance and tried GATHER_FIXED_OBJECTS_STATS again, which produced the same error.
  • 7. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    kent b wrote:
    Alvaro,
    Thanks for finding that (though with no workaround I'm somewhat stuck, I guess). The problem exists with both the production and test databases, so I did bounce the test instance and tried GATHER_FIXED_OBJECTS_STATS again, which produced the same error.
    What type of workaround are you looking for ?

    In this case, you MIGHT find that limiting the actions of the optimizer with a /*+ no_merge (ac) */ hint is enough to produce a plan that (a) is cost based and (b) operates efficiently enough - there are always risks with joining multiple dictionary views to produce the reports you like, so you may as well assume that you can't always write the simple and obvious SQL and get it to work efficiently.

    If the solution really is to gather some stats on the X$ objects, then read down the execution plan, check which x$ appear, and collect stats on them individually using dbms_stats.gather_table_stats() -- see: http://jonathanlewis.wordpress.com/2013/05/02/vlock/

    Regards
    Jonathan Lewis
  • 8. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    VishalP-Oracle Pro
    Currently Being Moderated
    This is discussed in "ORA-20011: Approximate NDV Failed: ORA-16532 During Statistics Gathering [ID 1455548.1]"

    this happens if you have dataguard broker enabled while dataguard broker configuration doesnt exist.
  • 9. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    Alvaro Pro
    Currently Being Moderated
    That is an excellent suggestion by Jonathan Leiws, try running it on:

    X$KZSRO
    X$KZSPR

    and let us know if it fixed the issue in that particular query.

    Keep in mind that would be a workaround for your present query, the GATHER_FIXED_OBJECTS_STATS would collect on all x$ tables.

    That is why I said there is no workaround, as in to achieve the same effect you would with the GATHER_FIXED_OBJECTS_STATS .
  • 10. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    759978 Newbie
    Currently Being Moderated
    It was an excellent suggestion, and I tried running it on those tables, but it didn't speed anything up and rule based is still much faster than CBO.
  • 11. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    Alvaro Pro
    Currently Being Moderated
    Sorry to hear that.

    Well, I would suggest you try Dynamic Sampling with the SYS tables since you are unable to collect the CBO:

    DELETE_DICTIONARY_STATS() -- to delete the present CBO stats, leaving the tables unalyzed.

    Check your OPTIMIZER_DYNAMIC_SAMPLING parameter, should be 2 or higher.

    That will cause the optimizer to sample the tables at runtime, thus generating some overhead, but possibly better plans.

    Not ideal, but at least it's something you can do, if it speeds up DD queries.
  • 12. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    759978 Newbie
    Currently Being Moderated
    Interesting to know that is available, thanks.

    Can you enlighten me: would DELETE_DICTIONARY_STATS() delete all CBO stats or only those regarding the SYS tables/views?

    For normal "user" data, CBO is still working I would suspect, given that only GATHER_FIXED_OBJECTS_STATS crashes.

    What I don't want to do is DELETE_DICTIONARY_STATS() if it causes overhead for normal user table data queries that would currently be running fine, if I'm making sense. The only performance issues I'm readily aware of are those involving these SYS objects.

    Would DELETE_DICTIONARY_STATS() cause some extra overhead for all queries?
  • 13. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    Alvaro Pro
    Currently Being Moderated
    No, this procedure deletes statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas). So, be careful with it, try it out at development first,see if the added overhead of dynamic sampling will be acceptable compared to query execution time.
  • 14. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
    759978 Newbie
    Currently Being Moderated
    Ok, sounds good, thanks!

Legend

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