Skip to Main Content
  • Questions

  • Query on system catalog is very slow

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tiberiu.

Asked: February 01, 2016 - 3:14 pm UTC

Last updated: October 13, 2018 - 4:22 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi

I have a query in my application:
SELECT NULL,
p.owner,
p.table_name,
pc.column_name,
NULL,
f.owner,
f.table_name,
fc.column_name,
fc.position,
NULL,
DECODE( f.delete_rule, 'CASCADE', 0, 'SET NULL', 2, 1 ),
f.constraint_name,
p.constraint_name,
DECODE( f.deferrable, 'DEFERRABLE', 5, 'NOT DEFERRABLE', 7, 'DEFERRED', 6 )
FROM all_cons_columns pc,
all_constraints p,
all_cons_columns fc,
all_constraints f
WHERE 1 = 1
AND f.table_name = 'my_table'
AND f.owner = 'my_schema'
AND f.constraint_type = 'R'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
AND p.constraint_type = 'P'
AND pc.owner = p.owner
AND pc.constraint_name = p.constraint_name
AND pc.table_name = p.table_name
AND fc.owner = f.owner
AND fc.constraint_name = f.constraint_name
AND fc.table_name = f.table_name
AND fc.position = pc.position;

until now I ran Oracle 11SE and everythign was ok.
Recently I installed 12c (Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production), and now this query takes about 2 minutes to run. I tried several solutions found on the web to enhance database performance but nothing helped.
Any hint would be very helpful.

Thank you in advance.

and Connor said...

I managed to replicate this on my db. Most of the time was in fact spent *parsing* the query, not actually running it. So I generated an outline to lock in the plan (which you use to create a baseline if you are using bind variables - see http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf ).

But since it looks like you are using literals for the owner/table, that is, every SQL is different, then a quick fix is to revert to having the outline hardcoded in the SQL.

It doesnt *look* nice but it works


SQL>
SQL> SELECT
  2    /*+
  3        BEGIN_OUTLINE_DATA
  4        PARTIAL_JOIN(@"SEL$B1BD3C93" "OBJAUTH$"@"SEL$53")
  5        USE_NL(@"SEL$B1BD3C93" "OBJAUTH$"@"SEL$53")
  6        LEADING(@"SEL$B1BD3C93" "X$KZSRO"@"SEL$54" "OBJAUTH$"@"SEL$53")
  7        INDEX(@"SEL$B1BD3C93" "OBJAUTH$"@"SEL$53" ("OBJAUTH$"."GRANTEE#" "OBJAUTH$"."OBJ#" "OBJAUTH$"."COL#"))
  8        FULL(@"SEL$B1BD3C93" "X$KZSRO"@"SEL$54")
  9        FULL(@"SEL$141D79C4" "X$KZSPR"@"SEL$57")
 10        FULL(@"SEL$50" "UE"@"SEL$50")
 11        FULL(@"SEL$51" "UE"@"SEL$51")
 12        PARTIAL_JOIN(@"SEL$52" "O2"@"SEL$52")
 13        USE_NL(@"SEL$52" "O2"@"SEL$52")
 14        LEADING(@"SEL$52" "U2"@"SEL$52" "O2"@"SEL$52")
 15        INDEX(@"SEL$52" "O2"@"SEL$52" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
 16        INDEX_SS(@"SEL$52" "U2"@"SEL$52" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
 17        PARTIAL_JOIN(@"SEL$5FA86079" "OBJAUTH$"@"SEL$35")
 18        USE_NL(@"SEL$5FA86079" "OBJAUTH$"@"SEL$35")
 19        LEADING(@"SEL$5FA86079" "X$KZSRO"@"SEL$36" "OBJAUTH$"@"SEL$35")
 20        INDEX(@"SEL$5FA86079" "OBJAUTH$"@"SEL$35" ("OBJAUTH$"."GRANTEE#" "OBJAUTH$"."OBJ#" "OBJAUTH$"."COL#"))
 21        FULL(@"SEL$5FA86079" "X$KZSRO"@"SEL$36")
 22        FULL(@"SEL$04C44AC2" "X$KZSPR"@"SEL$39")
 23        FULL(@"SEL$32" "UE"@"SEL$32")
 24        FULL(@"SEL$33" "UE"@"SEL$33")
 25        PARTIAL_JOIN(@"SEL$34" "O2"@"SEL$34")
 26        USE_NL(@"SEL$34" "O2"@"SEL$34")
 27        LEADING(@"SEL$34" "U2"@"SEL$34" "O2"@"SEL$34")
 28        INDEX(@"SEL$34" "O2"@"SEL$34" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
 29        INDEX_SS(@"SEL$34" "U2"@"SEL$34" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
 30        PARTIAL_JOIN(@"SEL$454F554E" "OBJAUTH$"@"SEL$25")
 31        USE_NL(@"SEL$454F554E" "OBJAUTH$"@"SEL$25")
 32        LEADING(@"SEL$454F554E" "X$KZSRO"@"SEL$26" "OBJAUTH$"@"SEL$25")
 33        INDEX(@"SEL$454F554E" "OBJAUTH$"@"SEL$25" ("OBJAUTH$"."GRANTEE#" "OBJAUTH$"."OBJ#" "OBJAUTH$"."COL#"))
 34        FULL(@"SEL$454F554E" "X$KZSRO"@"SEL$26")
 35        FULL(@"SEL$86413303" "X$KZSPR"@"SEL$29")
 36        FULL(@"SEL$22" "UE"@"SEL$22")
 37        FULL(@"SEL$23" "UE"@"SEL$23")
 38        PARTIAL_JOIN(@"SEL$24" "O2"@"SEL$24")
 39        USE_NL(@"SEL$24" "O2"@"SEL$24")
 40        LEADING(@"SEL$24" "U2"@"SEL$24" "O2"@"SEL$24")
 41        INDEX(@"SEL$24" "O2"@"SEL$24" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
 42        INDEX_SS(@"SEL$24" "U2"@"SEL$24" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
 43        PARTIAL_JOIN(@"SEL$6444526D" "OBJAUTH$"@"SEL$7")
 44        USE_NL(@"SEL$6444526D" "OBJAUTH$"@"SEL$7")
 45        LEADING(@"SEL$6444526D" "X$KZSRO"@"SEL$8" "OBJAUTH$"@"SEL$7")
 46        INDEX(@"SEL$6444526D" "OBJAUTH$"@"SEL$7" ("OBJAUTH$"."GRANTEE#" "OBJAUTH$"."OBJ#" "OBJAUTH$"."COL#"))
 47        FULL(@"SEL$6444526D" "X$KZSRO"@"SEL$8")
 48        FULL(@"SEL$96467BBE" "X$KZSPR"@"SEL$11")
 49        FULL(@"SEL$4" "UE"@"SEL$4")
 50        FULL(@"SEL$5" "UE"@"SEL$5")
 51        PARTIAL_JOIN(@"SEL$6" "O2"@"SEL$6")
 52        USE_NL(@"SEL$6" "O2"@"SEL$6")
 53        LEADING(@"SEL$6" "U2"@"SEL$6" "O2"@"SEL$6")
 54        INDEX(@"SEL$6" "O2"@"SEL$6" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
 55        INDEX_SS(@"SEL$6" "U2"@"SEL$6" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
 56        FULL(@"SEL$18" "UE"@"SEL$18")
 57        FULL(@"SEL$19" "UE"@"SEL$19")
 58        PARTIAL_JOIN(@"SEL$20" "O2"@"SEL$20")
 59        USE_NL(@"SEL$20" "O2"@"SEL$20")
 60        LEADING(@"SEL$20" "U2"@"SEL$20" "O2"@"SEL$20")
 61        INDEX(@"SEL$20" "O2"@"SEL$20" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
 62        INDEX_SS(@"SEL$20" "U2"@"SEL$20" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
 63        FULL(@"SEL$46" "UE"@"SEL$46")
 64        FULL(@"SEL$47" "UE"@"SEL$47")
 65        PARTIAL_JOIN(@"SEL$48" "O2"@"SEL$48")
 66        USE_NL(@"SEL$48" "O2"@"SEL$48")
 67        LEADING(@"SEL$48" "U2"@"SEL$48" "O2"@"SEL$48")
 68        INDEX(@"SEL$48" "O2"@"SEL$48" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
 69        INDEX_SS(@"SEL$48" "U2"@"SEL$48" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
 70        PQ_FILTER(@"SEL$B34693C7" SERIAL)
 71        USE_NL(@"SEL$B34693C7" "U"@"SEL$45")
 72        LEADING(@"SEL$B34693C7" "O"@"SEL$45" "U"@"SEL$45")
 73        INDEX(@"SEL$B34693C7" "U"@"SEL$45" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
 74        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B34693C7" "O"@"SEL$45")
 75        INDEX_RS_ASC(@"SEL$B34693C7" "O"@"SEL$45" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
 76        PQ_FILTER(@"SEL$6A03F9A0" SERIAL)
 77        USE_NL(@"SEL$6A03F9A0" "U"@"SEL$17")
 78        LEADING(@"SEL$6A03F9A0" "O"@"SEL$17" "U"@"SEL$17")
 79        INDEX(@"SEL$6A03F9A0" "U"@"SEL$17" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
 80        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$6A03F9A0" "O"@"SEL$17")
 81        INDEX_RS_ASC(@"SEL$6A03F9A0" "O"@"SEL$17" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
 82        PQ_FILTER(@"SEL$3B7221D9" SERIAL)
 83        USE_NL(@"SEL$3B7221D9" "RO"@"SEL$14")
 84        USE_NL(@"SEL$3B7221D9" "RO"@"SEL$42")
 85        USE_NL(@"SEL$3B7221D9" "U"@"SEL$3")
 86        USE_NL(@"SEL$3B7221D9" "U"@"SEL$2")
 87        USE_NL(@"SEL$3B7221D9" "AC"@"SEL$2")
 88        USE_NL(@"SEL$3B7221D9" "COL"@"SEL$2")
 89        USE_NL(@"SEL$3B7221D9" "O"@"SEL$3")
 90        USE_NL(@"SEL$3B7221D9" "CC"@"SEL$2")
 91        USE_NL(@"SEL$3B7221D9" "CD"@"SEL$2")
 92        USE_HASH(@"SEL$3B7221D9" "C"@"SEL$2")
 93        USE_NL(@"SEL$3B7221D9" "U"@"SEL$21")
 94        USE_NL(@"SEL$3B7221D9" "UI"@"SEL$14")
 95        USE_NL(@"SEL$3B7221D9" "U"@"SEL$16")
 96        USE_NL(@"SEL$3B7221D9" "U"@"SEL$15")
 97        USE_NL(@"SEL$3B7221D9" "OI"@"SEL$14")
 98        USE_NL(@"SEL$3B7221D9" "RC"@"SEL$14")
 99        USE_NL(@"SEL$3B7221D9" "O"@"SEL$21")
100        USE_NL(@"SEL$3B7221D9" "C"@"SEL$14")
101        USE_HASH(@"SEL$3B7221D9" "OC"@"SEL$14")
102        USE_NL(@"SEL$3B7221D9" "U"@"SEL$31")
103        USE_NL(@"SEL$3B7221D9" "AC"@"SEL$30")
104        USE_NL(@"SEL$3B7221D9" "COL"@"SEL$30")
105        USE_NL(@"SEL$3B7221D9" "O"@"SEL$31")
106        USE_NL(@"SEL$3B7221D9" "CC"@"SEL$30")
107        USE_NL(@"SEL$3B7221D9" "CD"@"SEL$30")
108        USE_NL(@"SEL$3B7221D9" "C"@"SEL$30")
109        USE_NL(@"SEL$3B7221D9" "UI"@"SEL$42")
110        USE_NL(@"SEL$3B7221D9" "U"@"SEL$44")
111        USE_NL(@"SEL$3B7221D9" "U"@"SEL$49")
112        USE_NL(@"SEL$3B7221D9" "RC"@"SEL$42")
113        USE_NL(@"SEL$3B7221D9" "OI"@"SEL$42")
114        USE_NL(@"SEL$3B7221D9" "O"@"SEL$49")
115        USE_HASH(@"SEL$3B7221D9" "U"@"SEL$43")
116        NLJ_BATCHING(@"SEL$3B7221D9" "OC"@"SEL$42")
117        USE_NL(@"SEL$3B7221D9" "OC"@"SEL$42")
118        USE_NL(@"SEL$3B7221D9" "C"@"SEL$42")
119        LEADING(@"SEL$3B7221D9" "U"@"SEL$30" "C"@"SEL$42" "OC"@"SEL$42" "U"@"SEL$43" "O"@"SEL$49" "OI"@"SEL$42" "RC"@"SEL$42"
120                "U"@"SEL$49" "U"@"SEL$44" "UI"@"SEL$42" "C"@"SEL$30" "CD"@"SEL$30" "CC"@"SEL$30" "O"@"SEL$31" "COL"@"SEL$30" "AC"@"SEL$30
"
121                "U"@"SEL$31" "OC"@"SEL$14" "C"@"SEL$14" "O"@"SEL$21" "RC"@"SEL$14" "OI"@"SEL$14" "U"@"SEL$15" "U"@"SEL$16" "UI"@"SEL$14"
122                "U"@"SEL$21" "C"@"SEL$2" "CD"@"SEL$2" "CC"@"SEL$2" "O"@"SEL$3" "COL"@"SEL$2" "AC"@"SEL$2" "U"@"SEL$2" "U"@"SEL$3"
123                "RO"@"SEL$42" "RO"@"SEL$14")
124        NO_ACCESS(@"SEL$3B7221D9" "RO"@"SEL$14")
125        NO_ACCESS(@"SEL$3B7221D9" "RO"@"SEL$42")
126        INDEX(@"SEL$3B7221D9" "U"@"SEL$3" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
127        INDEX_RS_ASC(@"SEL$3B7221D9" "U"@"SEL$2" ("USER$"."NAME"))
128        CLUSTER(@"SEL$3B7221D9" "AC"@"SEL$2")
129        INDEX_RS_ASC(@"SEL$3B7221D9" "COL"@"SEL$2" ("COL$"."OBJ#" "COL$"."INTCOL#"))
130        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3B7221D9" "O"@"SEL$3")
131        INDEX_RS_ASC(@"SEL$3B7221D9" "O"@"SEL$3" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
132        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3B7221D9" "CC"@"SEL$2")
133        INDEX_RS_ASC(@"SEL$3B7221D9" "CC"@"SEL$2" ("CCOL$"."CON#" "CCOL$"."COL#"))
134        INDEX_RS_ASC(@"SEL$3B7221D9" "CD"@"SEL$2" ("CDEF$"."CON#"))
135        FULL(@"SEL$3B7221D9" "C"@"SEL$2")
136        INDEX(@"SEL$3B7221D9" "U"@"SEL$21" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
137        INDEX(@"SEL$3B7221D9" "UI"@"SEL$14" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
138        INDEX(@"SEL$3B7221D9" "U"@"SEL$16" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
139        INDEX(@"SEL$3B7221D9" "U"@"SEL$15" "I_USER#")
140        INDEX(@"SEL$3B7221D9" "OI"@"SEL$14" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
141        INDEX_RS_ASC(@"SEL$3B7221D9" "RC"@"SEL$14" ("CON$"."CON#"))
142        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3B7221D9" "O"@"SEL$21")
143        INDEX_RS_ASC(@"SEL$3B7221D9" "O"@"SEL$21" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
144        INDEX_RS_ASC(@"SEL$3B7221D9" "C"@"SEL$14" ("CDEF$"."CON#"))
145        FULL(@"SEL$3B7221D9" "OC"@"SEL$14")
146        INDEX(@"SEL$3B7221D9" "U"@"SEL$31" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
147        CLUSTER(@"SEL$3B7221D9" "AC"@"SEL$30")
148        INDEX_RS_ASC(@"SEL$3B7221D9" "COL"@"SEL$30" ("COL$"."OBJ#" "COL$"."INTCOL#"))
149        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3B7221D9" "O"@"SEL$31")
150        INDEX_RS_ASC(@"SEL$3B7221D9" "O"@"SEL$31" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
151        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3B7221D9" "CC"@"SEL$30")
152        INDEX_RS_ASC(@"SEL$3B7221D9" "CC"@"SEL$30" ("CCOL$"."CON#" "CCOL$"."COL#"))
153        INDEX_RS_ASC(@"SEL$3B7221D9" "CD"@"SEL$30" ("CDEF$"."CON#"))
154        INDEX_RS_ASC(@"SEL$3B7221D9" "C"@"SEL$30" ("CON$"."OWNER#" "CON$"."NAME"))
155        INDEX(@"SEL$3B7221D9" "UI"@"SEL$42" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
156        INDEX(@"SEL$3B7221D9" "U"@"SEL$44" "I_USER#")
157        INDEX(@"SEL$3B7221D9" "U"@"SEL$49" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
158        INDEX_RS_ASC(@"SEL$3B7221D9" "RC"@"SEL$42" ("CON$"."CON#"))
159        INDEX(@"SEL$3B7221D9" "OI"@"SEL$42" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
160        BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3B7221D9" "O"@"SEL$49")
161        INDEX_RS_ASC(@"SEL$3B7221D9" "O"@"SEL$49" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
162        FULL(@"SEL$3B7221D9" "U"@"SEL$43")
163        INDEX(@"SEL$3B7221D9" "OC"@"SEL$42" ("CON$"."CON#"))
164        FULL(@"SEL$3B7221D9" "C"@"SEL$42")
165        INDEX_RS_ASC(@"SEL$3B7221D9" "U"@"SEL$30" ("USER$"."NAME"))
166        OUTLINE(@"SEL$49")
167        OUTLINE(@"SEL$44")
168        OUTLINE(@"SEL$43")
169        OUTLINE(@"SEL$42")
170        OUTLINE(@"SEL$21")
171        OUTLINE(@"SEL$16")
172        OUTLINE(@"SEL$15")
173        OUTLINE(@"SEL$14")
174        MERGE(@"SEL$49")
175        MERGE(@"SEL$44")
176        MERGE(@"SEL$43")
177        OUTLINE(@"SEL$DC79DD25")
178        OUTLINE(@"SEL$41")
179        MERGE(@"SEL$21")
180        MERGE(@"SEL$16")
181        MERGE(@"SEL$15")
182        OUTLINE(@"SEL$F1BBD541")
183        OUTLINE(@"SEL$13")
184        MERGE(@"SEL$DC79DD25")
185        OUTLINE(@"SEL$958BE710")
186        OUTLINE(@"SEL$40")
187        OUTLINE(@"SEL$31")
188        OUTLINE(@"SEL$30")
189        OUTLINE(@"SEL$3")
190        OUTLINE(@"SEL$2")
191        MERGE(@"SEL$F1BBD541")
192        OUTLINE(@"SEL$FFC60183")
193        OUTLINE(@"SEL$12")
194        MERGE(@"SEL$958BE710")
195        OUTLINE(@"SEL$E1ADD9E5")
196        MERGE(@"SEL$31")
197        OUTLINE(@"SEL$DCCAED8A")
198        MERGE(@"SEL$3")
199        OUTLINE(@"SEL$335DD26A")
200        MERGE(@"SEL$FFC60183")
201        OUTLINE(@"SEL$2234924E")
202        OUTLINE(@"SEL$1")
203        OUTLINE(@"SEL$11")
204        OUTLINE(@"SEL$10")
205        OUTLINE(@"SEL$29")
206        OUTLINE(@"SEL$28")
207        OUTLINE(@"SEL$39")
208        OUTLINE(@"SEL$38")
209        OUTLINE(@"SEL$57")
210        OUTLINE(@"SEL$56")
211        MERGE(@"SEL$E1ADD9E5")
212        MERGE(@"SEL$DCCAED8A")
213        MERGE(@"SEL$335DD26A")
214        MERGE(@"SEL$2234924E")
215        OUTLINE(@"SEL$AF1E34E1")
216        OUTLINE(@"SEL$45")
217        OUTER_JOIN_TO_INNER(@"SEL$AF1E34E1" "RC"@"SEL$42")
218        OUTLINE(@"SEL$3B7221D9")
219        OUTLINE(@"SEL$17")
220        MERGE(@"SEL$11")
221        OUTLINE(@"SEL$285A8194")
222        OUTLINE(@"SEL$9")
223        OUTLINE(@"SEL$8")
224        OUTLINE(@"SEL$7")
225        MERGE(@"SEL$29")
226        OUTLINE(@"SEL$6BD15B31")
227        OUTLINE(@"SEL$27")
228        OUTLINE(@"SEL$26")
229        OUTLINE(@"SEL$25")
230        MERGE(@"SEL$39")
231        OUTLINE(@"SEL$A6FE72D9")
232        OUTLINE(@"SEL$37")
233        OUTLINE(@"SEL$36")
234        OUTLINE(@"SEL$35")
235        MERGE(@"SEL$57")
236        OUTLINE(@"SEL$931DC35E")
237        OUTLINE(@"SEL$55")
238        OUTLINE(@"SEL$54")
239        OUTLINE(@"SEL$53")
240        OUTER_JOIN_TO_INNER(@"SEL$AF1E34E1" "RC"@"SEL$42")
241        OUTLINE_LEAF(@"SEL$3B7221D9")
242        PUSH_PRED(@"SEL$3B7221D9" "RO"@"SEL$42" 26)
243        OUTLINE_LEAF(@"SEL$B34693C7")
244        OUTLINE_LEAF(@"SEL$48")
245        OUTLINE_LEAF(@"SEL$47")
246        OUTLINE_LEAF(@"SEL$46")
247        PUSH_PRED(@"SEL$3B7221D9" "RO"@"SEL$14" 54)
248        OUTLINE_LEAF(@"SEL$6A03F9A0")
249        OUTLINE_LEAF(@"SEL$20")
250        OUTLINE_LEAF(@"SEL$19")
251        OUTLINE_LEAF(@"SEL$18")
252        OUTLINE_LEAF(@"SEL$6")
253        OUTLINE_LEAF(@"SEL$5")
254        OUTLINE_LEAF(@"SEL$4")
255        MERGE(@"SEL$285A8194")
256        OUTLINE_LEAF(@"SEL$96467BBE")
257        UNNEST(@"SEL$8")
258        OUTLINE_LEAF(@"SEL$6444526D")
259        OUTLINE_LEAF(@"SEL$24")
260        OUTLINE_LEAF(@"SEL$23")
261        OUTLINE_LEAF(@"SEL$22")
262        MERGE(@"SEL$6BD15B31")
263        OUTLINE_LEAF(@"SEL$86413303")
264        UNNEST(@"SEL$26")
265        OUTLINE_LEAF(@"SEL$454F554E")
266        OUTLINE_LEAF(@"SEL$34")
267        OUTLINE_LEAF(@"SEL$33")
268        OUTLINE_LEAF(@"SEL$32")
269        MERGE(@"SEL$A6FE72D9")
270        OUTLINE_LEAF(@"SEL$04C44AC2")
271        UNNEST(@"SEL$36")
272        OUTLINE_LEAF(@"SEL$5FA86079")
273        OUTLINE_LEAF(@"SEL$52")
274        OUTLINE_LEAF(@"SEL$51")
275        OUTLINE_LEAF(@"SEL$50")
276        MERGE(@"SEL$931DC35E")
277        OUTLINE_LEAF(@"SEL$141D79C4")
278        UNNEST(@"SEL$54")
279        OUTLINE_LEAF(@"SEL$B1BD3C93")
280        ALL_ROWS
281        DB_VERSION('12.1.0.2')
282        OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
283        IGNORE_OPTIM_EMBEDDED_HINTS
284        END_OUTLINE_DATA
285    */
286  NULL,
287  p.owner,
288  p.table_name,
289  pc.column_name,
290  NULL,
291  f.owner,
292  f.table_name,
293  fc.column_name,
294  fc.position,
295  NULL,
296  DECODE( f.delete_rule, 'CASCADE', 0, 'SET NULL', 2, 1 ),
297  f.constraint_name,
298  p.constraint_name,
299  DECODE( f.deferrable, 'DEFERRABLE', 5, 'NOT DEFERRABLE', 7, 'DEFERRED', 6 )
300  FROM
301    all_cons_columns pc,
302    all_constraints p,
303    all_cons_columns fc,
304    all_constraints f
305  WHERE 1 = 1
306  AND f.table_name = 'CHD'
307  AND f.owner = 'MCDONAC'
308  AND f.constraint_type = 'R'
309  AND p.owner = f.r_owner
310  AND p.constraint_name = f.r_constraint_name
311  AND p.constraint_type = 'P'
312  AND pc.owner = p.owner
313  AND pc.constraint_name = p.constraint_name
314  AND pc.table_name = p.table_name
315  AND fc.owner = f.owner
316  AND fc.constraint_name = f.constraint_name
317  AND fc.table_name = f.table_name
318  AND fc.position = pc.position;

[snip]

Elapsed: 00:00:00.14


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How

A reader, October 12, 2018 - 6:00 pm UTC

And how did you do step by step to generate the outline hardcoded ?
Connor McDonald
October 13, 2018 - 4:22 am UTC

Sorry, I should have added that.

SQL> explain plan for select * from scott.emp;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'+OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EMP"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

22 rows selected.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.