14 Replies Latest reply: May 10, 2013 3:05 PM by 759978 RSS

    CBO horribly slow with SYS views such as all_constraints/all_cons_columns

    759978
      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!
        • 2. Re: CBO horribly slow with SYS views such as all_constraints/all_cons_columns
          759978
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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.