Comments in-line.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
March 2004 Hotsos Symposium - The Burden of Proof
Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message
news:404714d3$1$26342$afc38c87_at_news.optusnet.com.au...
>
> Of course, I wanna see a full 10046 of that 9i SQL...
> :)
No you don't !
But here's a full-blooded execution plan.
Most of the volume is generated by the recursive
creation of the temporary table that does the
select from DBA_objects - for the plan, I changed
from all_objects to eliminate loads of references
to x$kzsro.
0 SELECT STATEMENT (choose)
2 0 1 TEMP TABLE TRANSFORMATION
1 2 1 RECURSIVE EXECUTION SYS_LE_2_0
INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */
INTO "SYS"."SYS_TEMP_0FD9D661A_479C0E" SELECT /*+ */
ROWNUM FROM ( (SELECT "U"."NAME"
"OWNER","SYS_ALIAS_1"."NAME"
"OBJECT_NAME","SYS_ALIAS_1"."SUBNAME"
"SUBOBJECT_NAME","SYS_ALIAS_1"."OBJ#"
"OBJECT_ID","SYS_ALIAS_1"."DATAOBJ#"
"DATA_OBJECT_ID",DECODE("SYS_ALIAS_1"."TYPE#",0,'NEXT
OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,
'PROCEDURE',8,'FUNCTION'
,9,'PACKAGE',11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
SOURCE',29,'JAVA
CLASS',30,'JAVA
RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX
SUBPARTITION',40,'LOB PARTITION',41,'LOB
SUBPARTITION',42,'MATERIALIZED
VIEW',43,'DIMENSION',44,'CONTEXT',46,'RULE
SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML
SCHEMA',56,'JAVA DATA',57,'SECURITY
PROFILE',59,'RULE',62,'EVALUATION
CONTEXT','UNDEFINED') "OBJECT_TYPE","SYS_ALIAS_1"."CTIME"
"CREATED","SYS_ALIAS_1"."MTIME"
"LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_1"."STIME",'YYYY-MM-DD:HH24:MI:SS')
"TIMESTAMP",DECODE("SYS_ALIAS_1"."STATUS",0,'N/A',1,'VALID','INVALID')
"STATUS",DECODE(BITAND("SYS_ALIAS_1"."FLAGS",2),0,'N',2,'Y','N')
"TEMPORARY",DECODE(BITAND("SYS_ALIAS_1"."FLAGS",4),0,'N',4,'Y','N')
"GENERATED",DECODE(BITAND("SYS_ALIAS_1"."FLAGS",16),0,'N',16,'Y','N')
"SECONDARY" FROM "SYS"."OBJ$"
"SYS_ALIAS_1","SYS"."USER$" "U" WHERE
("SYS_ALIAS_1"."TYPE#"<>1 AND "SYS_ALIAS_1"."TYPE#"<>10 OR
"SYS_ALIAS_1"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_1"."OBJ#"
AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR
"I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
"SYS_ALIAS_1"."OWNER#"="U"."USER#" AND
"SYS_ALIAS_1"."LINKNAME" IS NULL AND
"SYS_ALIAS_1"."NAME"<>'_default_auditing_options_' AND
"SYS_ALIAS_1"."NAME"<>'_NEXT_OBJECT') UNION ALL
(SELECT "U"."NAME" "OWNER","L"."NAME"
"OBJECT_NAME",NULL
"SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL)
"DATA_OBJECT_ID",'DATABASE LINK'
"OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL)
"LAST_DDL_TIME",NULL "TIMESTAMP",'VALID'
"STATUS",'N' "TEMPORARY",'N' "GENERATED",'N' "SECONDARY"
FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE
"L"."OWNER#"="U"."USER#")) "DBA_OBJECTS" WHERE
ROWNUM<=36
0 INSERT STATEMENT (choose)
1 0 1 LOAD AS SELECT
2 1 1 COUNT (stopkey) Filter (ROWNUM<=36)
3 2 1 2 VIEW SYS
4 3 1 UNION-ALL
5 4 1 FILTER Filter
("SYS_ALIAS_1"."TYPE#"<>1 AND
"SYS_ALIAS_1"."TYPE#"<>10 OR
"SYS_ALIAS_1"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE
"I"."OBJ#"=:B1 AND ("I"."TYPE#"=1
OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9))=1)
6 5 1 3 TABLE ACCESS SYS OBJ$ (by index
rowid)
7 6 1 NESTED LOOPS
8 7 1 4 TABLE ACCESS SYS USER$ (full)
9 7 2 INDEX UNIQUE SYS I_OBJ2 (range
scan) (Columns 2) Access
("SYS_ALIAS_1"."OWNER#"="U"."USER#" AND "SYS_ALIAS_1"."LINKNAME" IS NULL)
Filter
("SYS_ALIAS_1"."LINKNAME" IS
NULL AND "SYS_ALIAS_1"."NAME"<>'_NEXT_OBJECT' AND
"SYS_ALIAS_1"."NAME"<>'_default_auditing_options_')
10 5 2 5 TABLE ACCESS SYS IND$ (by index
rowid) Filter
("I"."TYPE#"=1 OR "I"."TYPE#"=2
OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7
OR "I"."TYPE#"=9)
11 10 1 INDEX UNIQUE SYS I_IND1 (unique
scan) (Columns 1) Access ("I"."OBJ#"=:B1)
12 4 2 NESTED LOOPS
13 12 1 7 TABLE ACCESS SYS USER$ (full)
14 12 2 INDEX NON-UNIQUE SYS I_LINK1 (range
scan) (Columns 1) Access
("L"."OWNER#"="U"."USER#")
3 2 1 6 VIEW SYS Filter ("from$_subquery$_006"."CT">1 AND
"from$_subquery$_006"."OLDEST">"from$_subquery$_006"."MIDDLE")
4 3 1 WINDOW (sort)
5 4 1 FILTER Filter
("AGE1"."AGE"*"AGE2"."AGE"*"AGE3"."AGE"= (SELECT /*+
*/ MAX("AGE_LIST"."AGE") FROM (SELECT /*+
CACHE_TEMP_TABLE("T1") */ "T1"."C0" "AGE" FROM
"SYS"."SYS_TEMP_0FD9D661A_479C0E" "T1")
"AGE_LIST"))
6 5 1 NESTED LOOPS
7 6 1 NESTED LOOPS
8 7 1 4 VIEW SYS
9 8 1 14 TABLE ACCESS SYS
SYS_TEMP_0FD9D661A_479C0E (full)
10 7 2 3 VIEW SYS Filter
("AGE3"."AGE">="AGE2"."AGE")
11 10 1 14 TABLE ACCESS SYS
SYS_TEMP_0FD9D661A_479C0E (full)
12 6 2 2 VIEW SYS Filter
("AGE2"."AGE">="AGE1"."AGE")
13 12 1 14 TABLE ACCESS SYS
SYS_TEMP_0FD9D661A_479C0E (full)
14 5 2 SORT (aggregate)
15 14 1 5 VIEW SYS
16 15 1 14 TABLE ACCESS SYS
SYS_TEMP_0FD9D661A_479C0E (full)
Alternatively, used utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------
----------------------------------------------------------------------------
----------------------
| Id | Operation | Name |
Rows | Bytes | Cost |
----------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | |
| | |
| 2 | TEMP TABLE TRANSFORMATION | |
| | |
| 1 | RECURSIVE EXECUTION | SYS_LE_2_0 |
| | |
| 0 | INSERT STATEMENT | |
| | |
| 1 | LOAD AS SELECT | |
| | |
|* 2 | COUNT STOPKEY | |
| | |
| 3 | VIEW | |
| | |
| 4 | UNION-ALL | |
| | |
|* 5 | FILTER | |
| | |
| 6 | TABLE ACCESS BY INDEX ROWID| OBJ$ |
| | |
| 7 | NESTED LOOPS | |
| | |
| 8 | TABLE ACCESS FULL | USER$ |
| | |
|* 9 | INDEX RANGE SCAN | I_OBJ2 |
| | |
|* 10 | TABLE ACCESS BY INDEX ROWID| IND$ |
| | |
|* 11 | INDEX UNIQUE SCAN | I_IND1 |
| | |
| 12 | NESTED LOOPS | |
| | |
| 13 | TABLE ACCESS FULL | USER$ |
| | |
|* 14 | INDEX RANGE SCAN | I_LINK1 |
| | |
|* 3 | VIEW | |
| | |
| 4 | WINDOW SORT | |
| | |
|* 5 | FILTER | |
| | |
| 6 | NESTED LOOPS | |
| | |
| 7 | NESTED LOOPS | |
| | |
| 8 | VIEW | |
| | |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661B_479C0E |
| | |
|* 10 | VIEW | |
| | |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661B_479C0E |
| | |
|* 12 | VIEW | |
| | |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661B_479C0E |
| | |
| 14 | SORT AGGREGATE | |
| | |
| 15 | VIEW | |
| | |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661B_479C0E |
| | |
----------------------------------------------------------------------------
----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=36)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------
5 - filter("SYS_ALIAS_1"."TYPE#"<>1 AND "SYS_ALIAS_1"."TYPE#"<>10 OR
"SYS_ALIAS_1"."TYPE#"=1
AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND
("I"."TYPE#"=1 OR
"I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7
OR "I"."TYPE#"=9))=1)
9 - access("SYS_ALIAS_1"."OWNER#"="U"."USER#" AND
"SYS_ALIAS_1"."LINKNAME" IS NULL)
filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND
"SYS_ALIAS_1"."NAME"<>'_NEXT_OBJECT' AND "SY
S_ALIAS_1"."NAME"<>'_default_auditing_options_')
10 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR
"I"."TYPE#"=4 OR "I"."TYPE#"=6
OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
11 - access("I"."OBJ#"=:B1)
14 - access("L"."OWNER#"="U"."USER#")
3 - filter("from$_subquery$_006"."CT">1 AND
"from$_subquery$_006"."OLDEST">"from$_subquery$_
006"."MIDDLE")
5 - filter("AGE1"."AGE"*"AGE2"."AGE"*"AGE3"."AGE"= (SELECT /*+ */
MAX("AGE_LIST"."AGE") FROM
(SELECT /*+ CACHE_TEMP_TABLE("T1") */ "T1"."C0" "AGE" FROM
"SYS"."SYS_TEMP_0FD9
D661B_479C0E" "T1") "AGE_LIST"))
10 - filter("AGE3"."AGE">="AGE2"."AGE")
12 - filter("AGE2"."AGE">="AGE1"."AGE")
Note: rule based optimization
You'll see that utlxpls.sql reports RULE BASED (I was actually
on CHOOSE). When I switched to cost based, Oracle assumed that the
generated temporary table had the standard 8,168 rows in
it and changed the latter part of the execution path to sort/merge
rather than nested loop full tablescan.
When I enabled dynamic sampling at level 2 for the session,
Oracle then got a better idea of cardinality, and changed the
plan again.
Any which way, it's not really designed to be efficient -
only to be informative.
>
> --
> Cheers
> Nuno Souto
> wizofoz2k_at_yahoo.com.au.nospam
>
Received on Thu Mar 04 2004 - 06:20:14 CST
