Skip to content

Commit c317b0b

Browse files
nigelbaylissnigelbayliss
committed
xplan
1 parent d090cd5 commit c317b0b

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

52 files changed

+198752
-0
lines changed

‎optimizer/xplan/README.md

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
<h2>Scripts for playing with SQL execution plans</h2>
2+
3+
Some examples of how you can generate interesting SQL execution plans to help to unserstand how they work.
4+
5+
These scripts have been tested on Oracle Database 12c Release 2
6+
7+
DISCLAIMER:
8+
<br/>-- These scripts are provided for educational purposes only.
9+
<br/>-- They are NOT supported by Oracle World Wide Technical Support.
10+
<br/>-- The scripts have been tested and they appear to work as intended.
11+
<br/>-- You should always run scripts on a test instance.
12+

‎optimizer/xplan/adv.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
set linesize 200
2+
set tab off
3+
set pagesize 1000
4+
column plan_table_output format a180
5+
6+
SELECT *
7+
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALL +OUTLINE'));

‎optimizer/xplan/all.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
set linesize 180
2+
set trims on
3+
set tab off
4+
set pagesize 1000
5+
column plan_table_output format a150
6+
7+
SELECT *
8+
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALL'));

‎optimizer/xplan/basic.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
set linesize 200
2+
set tab off
3+
set pagesize 1000
4+
column plan_table_output format a180
5+
6+
SELECT *
7+
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'BASIC'));

‎optimizer/xplan/eg_trace.sql

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,32 @@
1+
--
2+
-- Generate an example trace file
3+
--
4+
alter system flush shared_pool;
5+
alter session set tracefile_identifier = 'EXAMPLE_TRACE';
6+
alter session set events = '10053 trace name context forever, level 1';
7+
select
8+
/*+
9+
BEGIN_OUTLINE_DATA
10+
IGNORE_OPTIM_EMBEDDED_HINTS
11+
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
12+
DB_VERSION('12.2.0.1')
13+
ALL_ROWS
14+
OUTLINE_LEAF(@"SEL$58A6D7F6")
15+
MERGE(@"SEL$1" >"SEL$2")
16+
OUTLINE(@"SEL$2")
17+
OUTLINE(@"SEL$1")
18+
FULL(@"SEL$58A6D7F6" "E"@"SEL$1")
19+
INDEX_RS_ASC(@"SEL$58A6D7F6" "T"@"SEL$1" ("TASKS"."EMP_ID"))
20+
LEADING(@"SEL$58A6D7F6" "E"@"SEL$1" "T"@"SEL$1")
21+
USE_NL(@"SEL$58A6D7F6" "T"@"SEL$1")
22+
END_OUTLINE_DATA
23+
*/
24+
e.ename as "Employee Name",
25+
t.tname as "Task Name"
26+
from employees e
27+
join tasks t on (t.emp_id = e.id)
28+
where e.etype <= 5;
29+
30+
alter session set events = '10053 trace name context off';
31+
32+
@@adv

‎optimizer/xplan/ind.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
create unique index emp_pk on employees(id);
2+
create unique index task_pk on tasks(id);
3+
create index task_emp_fk on tasks(emp_id);

‎optimizer/xplan/intro.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
--
2+
-- Intro example
3+
--
4+
select sum(a.id), sum(e.id)
5+
from table_10 a
6+
join table_100000 e on (a.id = e.id);
7+
8+
@@simple
9+
10+
select /*+ LEADING(@"SEL$58A6D7F6" "E"@"SEL$1" "A"@"SEL$1") */
11+
sum(a.id), sum(e.id)
12+
from table_10 a
13+
join table_100000 e on (a.id = e.id);
14+
15+
@@simple
16+
17+

‎optimizer/xplan/j1.lst

Lines changed: 134 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,134 @@
1+
SQL> @j1
2+
SQL> select sum(a.id), sum(b.id),
3+
2 sum(c.id), sum(d.id), sum(e.id)
4+
3 from table_10 a
5+
4 join table_100 b on (a.id = b.fkcol)
6+
5 join table_1000 c on (b.id = c.fkcol)
7+
6 join table_10000 d on (c.id = d.fkcol)
8+
7 join table_100000 e on (d.id = e.fkcol);
9+
10+
SUM(A.ID) SUM(B.ID) SUM(C.ID) SUM(D.ID) SUM(E.ID)
11+
---------- ---------- ---------- ---------- ----------
12+
450000 4500000 45000000 450000000 4500000000
13+
14+
Elapsed: 00:00:00.10
15+
SQL>
16+
SQL> @@typ
17+
SQL> set linesize 200
18+
SQL> set tab off
19+
SQL> set pagesize 1000
20+
SQL> column plan_table_output format a180
21+
SQL>
22+
SQL> SELECT *
23+
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
24+
25+
PLAN_TABLE_OUTPUT
26+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
27+
SQL_ID c8v6bqjzn42t2, child number 0
28+
-------------------------------------
29+
select sum(a.id), sum(b.id), sum(c.id), sum(d.id), sum(e.id)
30+
from table_10 a join table_100 b on (a.id =
31+
b.fkcol) join table_1000 c on (b.id = c.fkcol) join
32+
table_10000 d on (c.id = d.fkcol) join table_100000 e on (d.id
33+
= e.fkcol)
34+
35+
Plan hash value: 644569126
36+
37+
---------------------------------------------------------------------------------------
38+
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
39+
---------------------------------------------------------------------------------------
40+
| 0 | SELECT STATEMENT | | | | 97 (100)| |
41+
| 1 | SORT AGGREGATE | | 1 | 33 | | |
42+
|* 2 | HASH JOIN | | 100K| 3222K| 97 (4)| 00:00:01 |
43+
|* 3 | HASH JOIN | | 10000 | 234K| 18 (0)| 00:00:01 |
44+
|* 4 | HASH JOIN | | 1000 | 16000 | 9 (0)| 00:00:01 |
45+
|* 5 | HASH JOIN | | 100 | 900 | 6 (0)| 00:00:01 |
46+
| 6 | TABLE ACCESS FULL| TABLE_10 | 10 | 30 | 3 (0)| 00:00:01 |
47+
| 7 | TABLE ACCESS FULL| TABLE_100 | 100 | 600 | 3 (0)| 00:00:01 |
48+
| 8 | TABLE ACCESS FULL | TABLE_1000 | 1000 | 7000 | 3 (0)| 00:00:01 |
49+
| 9 | TABLE ACCESS FULL | TABLE_10000 | 10000 | 80000 | 9 (0)| 00:00:01 |
50+
| 10 | TABLE ACCESS FULL | TABLE_100000 | 100K| 878K| 78 (3)| 00:00:01 |
51+
---------------------------------------------------------------------------------------
52+
53+
Predicate Information (identified by operation id):
54+
---------------------------------------------------
55+
56+
2 - access("D"."ID"="E"."FKCOL")
57+
3 - access("C"."ID"="D"."FKCOL")
58+
4 - access("B"."ID"="C"."FKCOL")
59+
5 - access("A"."ID"="B"."FKCOL")
60+
61+
62+
34 rows selected.
63+
64+
Elapsed: 00:00:00.05
65+
SQL>
66+
SQL> pause Press <cr> to continue
67+
Press <cr> to continue
68+
69+
SQL>
70+
SQL> select /*+ gather_plan_statistics */
71+
2 sum(a.id), sum(b.id),
72+
3 sum(c.id), sum(d.id), sum(e.id)
73+
4 from table_10 a
74+
5 join table_100 b on (a.id = b.fkcol)
75+
6 join table_1000 c on (b.id = c.fkcol)
76+
7 join table_10000 d on (c.id = d.fkcol)
77+
8 join table_100000 e on (d.id = e.fkcol);
78+
79+
SUM(A.ID) SUM(B.ID) SUM(C.ID) SUM(D.ID) SUM(E.ID)
80+
---------- ---------- ---------- ---------- ----------
81+
450000 4500000 45000000 450000000 4500000000
82+
83+
Elapsed: 00:00:00.05
84+
SQL>
85+
SQL> @@sta
86+
SQL> set linesize 200
87+
SQL> set tab off
88+
SQL> set pagesize 1000
89+
SQL> column plan_table_output format a180
90+
SQL>
91+
SQL> SELECT *
92+
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST ALLSTATS -MEMSTATS'));
93+
94+
PLAN_TABLE_OUTPUT
95+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
96+
SQL_ID 3scm64ghham9c, child number 0
97+
-------------------------------------
98+
select /*+ gather_plan_statistics */ sum(a.id), sum(b.id),
99+
sum(c.id), sum(d.id), sum(e.id) from table_10 a join
100+
table_100 b on (a.id = b.fkcol) join table_1000 c on (b.id
101+
= c.fkcol) join table_10000 d on (c.id = d.fkcol) join
102+
table_100000 e on (d.id = e.fkcol)
103+
104+
Plan hash value: 644569126
105+
106+
---------------------------------------------------------------------------------------------------------------------------------
107+
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
108+
---------------------------------------------------------------------------------------------------------------------------------
109+
| 0 | SELECT STATEMENT | | 1 | | | 97 (100)| | 1 |00:00:00.04 | 353 |
110+
| 1 | SORT AGGREGATE | | 1 | 1 | 33 | | | 1 |00:00:00.04 | 353 |
111+
|* 2 | HASH JOIN | | 1 | 100K| 3222K| 97 (4)| 00:00:01 | 90000 |00:00:00.05 | 353 |
112+
|* 3 | HASH JOIN | | 1 | 10000 | 234K| 18 (0)| 00:00:01 | 9000 |00:00:00.01 | 74 |
113+
|* 4 | HASH JOIN | | 1 | 1000 | 16000 | 9 (0)| 00:00:01 | 900 |00:00:00.01 | 35 |
114+
|* 5 | HASH JOIN | | 1 | 100 | 900 | 6 (0)| 00:00:01 | 90 |00:00:00.01 | 21 |
115+
| 6 | TABLE ACCESS FULL| TABLE_10 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 9 |
116+
| 7 | TABLE ACCESS FULL| TABLE_100 | 1 | 100 | 600 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 9 |
117+
| 8 | TABLE ACCESS FULL | TABLE_1000 | 1 | 1000 | 7000 | 3 (0)| 00:00:01 | 1000 |00:00:00.01 | 11 |
118+
| 9 | TABLE ACCESS FULL | TABLE_10000 | 1 | 10000 | 80000 | 9 (0)| 00:00:01 | 10000 |00:00:00.01 | 36 |
119+
| 10 | TABLE ACCESS FULL | TABLE_100000 | 1 | 100K| 878K| 78 (3)| 00:00:01 | 100K|00:00:00.02 | 276 |
120+
---------------------------------------------------------------------------------------------------------------------------------
121+
122+
Predicate Information (identified by operation id):
123+
---------------------------------------------------
124+
125+
2 - access("D"."ID"="E"."FKCOL")
126+
3 - access("C"."ID"="D"."FKCOL")
127+
4 - access("B"."ID"="C"."FKCOL")
128+
5 - access("A"."ID"="B"."FKCOL")
129+
130+
131+
34 rows selected.
132+
133+
Elapsed: 00:00:00.03
134+
SQL> spool off

‎optimizer/xplan/j1.sql

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
select sum(a.id), sum(b.id),
2+
sum(c.id), sum(d.id), sum(e.id)
3+
from table_10 a
4+
join table_100 b on (a.id = b.fkcol)
5+
join table_1000 c on (b.id = c.fkcol)
6+
join table_10000 d on (c.id = d.fkcol)
7+
join table_100000 e on (d.id = e.fkcol);
8+
9+
@@typ
10+
11+
pause Press <cr> to continue
12+
13+
select /*+ gather_plan_statistics */
14+
sum(a.id), sum(b.id),
15+
sum(c.id), sum(d.id), sum(e.id)
16+
from table_10 a
17+
join table_100 b on (a.id = b.fkcol)
18+
join table_1000 c on (b.id = c.fkcol)
19+
join table_10000 d on (c.id = d.fkcol)
20+
join table_100000 e on (d.id = e.fkcol);
21+
22+
@@sta

0 commit comments

Comments
 (0)