Skip to content

Commit ed1186d

Browse files
nigelbaylissnigelbayliss
committed
adv
1 parent e7f0c92 commit ed1186d

File tree

12 files changed

+107
-276
lines changed

12 files changed

+107
-276
lines changed

‎optimizer/execution_plans/part0/eg1.lst

Lines changed: 0 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,25 +1,9 @@
11
SQL> @eg1
2-
SQL> column ename format a20
3-
SQL> column rname format a20
4-
SQL>
5-
SQL> select e.ename,r.rname
6-
2 from employees e
7-
3 join roles r on (r.id = e.role_id)
8-
4 join departments d on (d.id = e.dept_id)
9-
5 where e.staffno <= 10
10-
6 and d.dname in ('Department Name 1','Department Name 2');
112

123
ENAME RNAME
134
-------------------- --------------------
145
Employee Name 1 Role Name 1
156

16-
SQL>
17-
SQL> @advanced
18-
SQL> set linesize 220 tab off pagesize 1000 trims on
19-
SQL> column plan_table_output format a120
20-
SQL>
21-
SQL> SELECT *
22-
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ADVANCED'));
237

248
PLAN_TABLE_OUTPUT
259
------------------------------------------------------------------------------------------------------------------------

‎optimizer/execution_plans/part0/eg1.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,4 +8,4 @@ join departments d on (d.id = e.dept_id)
88
where e.staffno <= 10
99
and d.dname in ('Department Name 1','Department Name 2');
1010

11-
@advanced
11+
@plan

‎optimizer/execution_plans/part0/eg2.lst

Lines changed: 2 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,26 +1,9 @@
11
SQL> @eg2
2-
SQL> column ename format a20
3-
SQL> column rname format a20
4-
SQL>
5-
SQL> select /*+ gather_plan_statistics */
6-
2 e.ename,r.rname
7-
3 from employees e
8-
4 join roles r on (r.id = e.role_id)
9-
5 join departments d on (d.id = e.dept_id)
10-
6 where e.staffno <= 10
11-
7 and d.dname in ('Department Name 1','Department Name 2');
122

133
ENAME RNAME
144
-------------------- --------------------
155
Employee Name 1 Role Name 1
166

17-
SQL>
18-
SQL> @stats
19-
SQL> set linesize 210 tab off pagesize 1000 trims on
20-
SQL> column plan_table_output format a200
21-
SQL>
22-
SQL> SELECT *
23-
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ADVANCED'));
247

258
PLAN_TABLE_OUTPUT
269
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
@@ -37,8 +20,8 @@ Plan hash value: 2341252972
3720
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
3821
--------------------------------------------------------------------------------------------------------------------------------------------------------
3922
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 28 | | | |
40-
|* 1 | HASH JOIN | | 1 | 3 | 192 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 28 | 1448K| 1448K| 830K (0)|
41-
|* 2 | HASH JOIN | | 1 | 3 | 147 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 17 | 2545K| 2545K| 930K (0)|
23+
|* 1 | HASH JOIN | | 1 | 3 | 192 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 28 | 1448K| 1448K| 535K (0)|
24+
|* 2 | HASH JOIN | | 1 | 3 | 147 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 17 | 2545K| 2545K| 804K (0)|
4225
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 2 | 42 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | | | |
4326
|* 4 | TABLE ACCESS FULL| EMPLOYEES | 1 | 10 | 280 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 7 | | | |
4427
| 5 | TABLE ACCESS FULL | ROLES | 1 | 20 | 300 | 2 (0)| 00:00:01 | 20 |00:00:00.01 | 8 | | | |

‎optimizer/execution_plans/part0/eg3.lst

Lines changed: 14 additions & 60 deletions
Original file line numberDiff line numberDiff line change
@@ -1,55 +1,12 @@
11
SQL> @eg3
2-
SQL> column ename format a20
3-
SQL> column rname format a20
4-
SQL>
5-
SQL> --
6-
SQL> -- Use this to capture a SQL_ID
7-
SQL> --
8-
SQL> var sqlid varchar2(100)
9-
SQL>
10-
SQL> --
11-
SQL> -- Long running queries will be monitored automatically
12-
SQL> -- so the hint won't always be required. In this case,
13-
SQL> -- it's over very quickly so I've added the hint.
14-
SQL> --
15-
SQL> select /*+ MONITOR */
16-
2 e.ename,r.rname
17-
3 from employees e
18-
4 join roles r on (r.id = e.role_id)
19-
5 join departments d on (d.id = e.dept_id)
20-
6 where e.staffno <= 10
21-
7 and d.dname in ('Department Name 1','Department Name 2');
222

233
ENAME RNAME
244
-------------------- --------------------
255
Employee Name 1 Role Name 1
266

27-
SQL>
28-
SQL> --
29-
SQL> -- Get the SQL_ID of the last SQL statement we ran
30-
SQL> --
31-
SQL> begin
32-
2 select prev_sql_id
33-
3 into :sqlid
34-
4 from v$session
35-
5 where sid=userenv('sid')
36-
6 and username is not null
37-
7 and prev_hash_value <> 0
38-
8 and rownum<2;
39-
9 end;
40-
10 /
417

428
PL/SQL procedure successfully completed.
439

44-
SQL>
45-
SQL> --
46-
SQL> -- Text first, then HTML
47-
SQL> --
48-
SQL> set long 100000 pagesize 0 linesize 250 tab off trims on
49-
SQL> column report format a230
50-
SQL>
51-
SQL> select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id=>:sqlid, report_level=>'all', type=>'text') report
52-
2 from dual;
5310
SQL Monitoring Report
5411

5512
SQL Text
@@ -60,40 +17,37 @@ Global Information
6017
------------------------------
6118
Status : DONE (ALL ROWS)
6219
Instance ID : 1
63-
Session : ADHOC (63:23029)
20+
Session : ADHOC (44:61676)
6421
SQL ID : an05rsj1up1k5
65-
SQL Execution ID : 16777226
66-
Execution Started : 03/09/2018 14:23:14
67-
First Refresh Time : 03/09/2018 14:23:14
68-
Last Refresh Time : 03/09/2018 14:23:14
69-
Duration : .002638s
22+
SQL Execution ID : 16777228
23+
Execution Started : 03/16/2018 13:45:05
24+
First Refresh Time : 03/16/2018 13:45:05
25+
Last Refresh Time : 03/16/2018 13:45:05
26+
Duration : .001969s
7027
Module/Action : SQL*Plus/-
7128
Service : SYS$USERS
7229
Program : sqlplus@prod12c (TNS V1-V3)
7330
Fetch Calls : 2
7431

7532
Global Stats
76-
=================================================
77-
| Elapsed | Cpu | Other | Fetch | Buffer |
78-
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
79-
=================================================
80-
| 0.00 | 0.00 | 0.00 | 2 | 28 |
81-
=================================================
33+
=======================================
34+
| Elapsed | Other | Fetch | Buffer |
35+
| Time(s) | Waits(s) | Calls | Gets |
36+
=======================================
37+
| 0.00 | 0.00 | 2 | 28 |
38+
=======================================
8239

8340
SQL Plan Monitoring Details (Plan Hash Value=2341252972)
8441
==========================================================================================================================================
8542
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
8643
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
8744
==========================================================================================================================================
8845
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | . | | |
89-
| 1 | HASH JOIN | | 3 | 6 | 1 | +0 | 1 | 1 | 811KB | | |
90-
| 2 | HASH JOIN | | 3 | 4 | 1 | +0 | 1 | 2 | 898KB | | |
46+
| 1 | HASH JOIN | | 3 | 6 | 1 | +0 | 1 | 1 | 523KB | | |
47+
| 2 | HASH JOIN | | 3 | 4 | 1 | +0 | 1 | 2 | 788KB | | |
9148
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 2 | 1 | +0 | 1 | 2 | . | | |
9249
| 4 | TABLE ACCESS FULL | EMPLOYEES | 10 | 2 | 1 | +0 | 1 | 10 | . | | |
9350
| 5 | TABLE ACCESS FULL | ROLES | 20 | 2 | 1 | +0 | 1 | 20 | . | | |
9451
==========================================================================================================================================
9552

9653

97-
SQL>
98-
SQL> set termout off feedback off
99-
SQL> spool monitor_output.html

‎optimizer/execution_plans/part0/eg4.lst

Lines changed: 7 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -1,44 +1,11 @@
11
SQL> @eg4
2-
SQL> column ename format a20
3-
SQL> column rname format a20
4-
SQL>
5-
SQL> var sqlid varchar2(100)
6-
SQL>
7-
SQL> select /* MY_TEST_QUERY */
8-
2 e.ename,r.rname
9-
3 from employees e
10-
4 join roles r on (r.id = e.role_id)
11-
5 join departments d on (d.id = e.dept_id)
12-
6 where e.staffno <= 10
13-
7 and d.dname in ('Department Name 1','Department Name 2');
142
Employee Name 1 Role Name 1
15-
SQL>
16-
SQL> --
17-
SQL> -- In this example, let's search for the query
18-
SQL> -- in V$SQL. We could do this in another session
19-
SQL> -- while the query is executing.
20-
SQL> --
21-
SQL> begin
22-
2 select sql_id
23-
3 into :sqlid
24-
4 from v$sql
25-
5 where sql_text like '%MY_TEST_QUERY%'
26-
6 and sql_text not like '%v$sql%'
27-
7 and rownum<2;
28-
8 end;
29-
9 /
30-
SQL>
31-
SQL> set long 100000 pagesize 0 linesize 250 tab off trims on
32-
SQL> column plan_table_output format a230
33-
SQL>
34-
SQL> SELECT *
35-
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>:sqlid,FORMAT=>'ADVANCED'));
36-
SQL_ID 1j5mhvb79631d, child number 0
3+
SQL_ID fzfk7qqax6chk, child number 0
374
-------------------------------------
38-
select /* MY_TEST_QUERY */ /*+ MONITOR */ e.ename,r.rname from
39-
employees e join roles r on (r.id = e.role_id) join
40-
departments d on (d.id = e.dept_id) where e.staffno <= 10 and
41-
d.dname in ('Department Name 1','Department Name 2')
5+
select /* MY_TEST_QUERY */ e.ename,r.rname from employees e
6+
join roles r on (r.id = e.role_id) join departments d on
7+
(d.id = e.dept_id) where e.staffno <= 10 and d.dname in
8+
('Department Name 1','Department Name 2')
429

4310
Plan hash value: 2341252972
4411

@@ -106,5 +73,6 @@ Column Projection Information (identified by operation id):
10673
"E"."ROLE_ID"[NUMBER,22]
10774
5 - (rowset=256) "R"."ID"[NUMBER,22], "R"."RNAME"[VARCHAR2,100]
10875

109-
SQL>
76+
SQL> spoll off
77+
SP2-0042: unknown command "spoll off" - rest of line ignored.
11078
SQL> spool off

‎optimizer/execution_plans/part0/eg4.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -30,5 +30,5 @@ set long 100000 pagesize 0 linesize 250 tab off trims on
3030
column plan_table_output format a230
3131

3232
SELECT *
33-
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>:sqlid,FORMAT=>'ADVANCED'));
33+
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>:sqlid,FORMAT=>'ALL +OUTLINE'));
3434

‎optimizer/execution_plans/part0/eg5.lst

Lines changed: 14 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -1,45 +1,5 @@
11
SQL> @eg5
2-
SQL> column ename format a20
3-
SQL> column rname format a20
4-
SQL>
5-
SQL> var sqlid varchar2(100)
6-
SQL>
7-
SQL> select /* MY_TEST_QUERY */ /*+ MONITOR */
8-
2 e.ename,r.rname
9-
3 from employees e
10-
4 join roles r on (r.id = e.role_id)
11-
5 join departments d on (d.id = e.dept_id)
12-
6 where e.staffno <= 10
13-
7 and d.dname in ('Department Name 1','Department Name 2');
142
Employee Name 1 Role Name 1
15-
SQL>
16-
SQL> --
17-
SQL> -- In this example, let's search for the query
18-
SQL> -- in V$SQL. We could do this in another session
19-
SQL> -- while the query is executing.
20-
SQL> --
21-
SQL> begin
22-
2 select sql_id
23-
3 into :sqlid
24-
4 from v$sql
25-
5 where sql_text like '%MY_TEST_QUERY%'
26-
6 and sql_text not like '%v$sql%'
27-
7 and rownum<2;
28-
8 end;
29-
9 /
30-
SQL>
31-
SQL> --
32-
SQL> -- Generate the SQL Monitor Report
33-
SQL> --
34-
SQL> -- If the test query was long-running, we could run this
35-
SQL> -- in a seperate SQL Plus session and watch the query's
36-
SQL> -- progress.
37-
SQL> --
38-
SQL> set long 100000 pagesize 0 linesize 250 tab off trims on
39-
SQL> column report format a230
40-
SQL>
41-
SQL> select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id=>:sqlid, report_level=>'all', type=>'text') report
42-
2 from dual;
433
SQL Monitoring Report
444

455
SQL Text
@@ -50,38 +10,37 @@ Global Information
5010
------------------------------
5111
Status : DONE (ALL ROWS)
5212
Instance ID : 1
53-
Session : ADHOC (63:23029)
13+
Session : ADHOC (44:61676)
5414
SQL ID : 1j5mhvb79631d
55-
SQL Execution ID : 16777219
56-
Execution Started : 03/09/2018 14:23:27
57-
First Refresh Time : 03/09/2018 14:23:27
58-
Last Refresh Time : 03/09/2018 14:23:27
59-
Duration : .002311s
15+
SQL Execution ID : 16777220
16+
Execution Started : 03/16/2018 13:45:23
17+
First Refresh Time : 03/16/2018 13:45:23
18+
Last Refresh Time : 03/16/2018 13:45:23
19+
Duration : .000591s
6020
Module/Action : SQL*Plus/-
6121
Service : SYS$USERS
6222
Program : sqlplus@prod12c (TNS V1-V3)
6323
Fetch Calls : 2
6424

6525
Global Stats
66-
=================================================
67-
| Elapsed | Cpu | Other | Fetch | Buffer |
68-
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
69-
=================================================
70-
| 0.00 | 0.00 | 0.00 | 2 | 28 |
71-
=================================================
26+
=======================================
27+
| Elapsed | Other | Fetch | Buffer |
28+
| Time(s) | Waits(s) | Calls | Gets |
29+
=======================================
30+
| 0.00 | 0.00 | 2 | 28 |
31+
=======================================
7232

7333
SQL Plan Monitoring Details (Plan Hash Value=2341252972)
7434
==========================================================================================================================================
7535
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
7636
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
7737
==========================================================================================================================================
7838
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | . | | |
79-
| 1 | HASH JOIN | | 3 | 6 | 1 | +0 | 1 | 1 | 840KB | | |
80-
| 2 | HASH JOIN | | 3 | 4 | 1 | +0 | 1 | 2 | 930KB | | |
39+
| 1 | HASH JOIN | | 3 | 6 | 1 | +0 | 1 | 1 | 545KB | | |
40+
| 2 | HASH JOIN | | 3 | 4 | 1 | +0 | 1 | 2 | 837KB | | |
8141
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 2 | 1 | +0 | 1 | 2 | . | | |
8242
| 4 | TABLE ACCESS FULL | EMPLOYEES | 10 | 2 | 1 | +0 | 1 | 10 | . | | |
8343
| 5 | TABLE ACCESS FULL | ROLES | 20 | 2 | 1 | +0 | 1 | 20 | . | | |
8444
==========================================================================================================================================
8545

86-
SQL>
8746
SQL> spool off

‎optimizer/execution_plans/part0/eg6.lst

Lines changed: 0 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,22 +1,5 @@
11
SQL> @eg6
2-
SQL> column ename format a20
3-
SQL> column rname format a20
4-
SQL>
5-
SQL> select /*+ PARALLEL (e 2) */
6-
2 e.ename,r.rname
7-
3 from employees e
8-
4 join roles r on (r.id = e.role_id)
9-
5 join departments d on (d.id = e.dept_id)
10-
6 where e.staffno <= 10
11-
7 and d.dname in ('Department Name 1','Department Name 2');
122
Employee Name 1 Role Name 1
13-
SQL>
14-
SQL> @advancedp
15-
SQL> set linesize 220 tab off pagesize 1000 trims on
16-
SQL> column plan_table_output format a120
17-
SQL>
18-
SQL> SELECT *
19-
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ADVANCED PARALLEL'));
203

214
PLAN_TABLE_OUTPUT
225
------------------------------------------------------------------------------------------------------------------------

‎optimizer/execution_plans/part0/eg6.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,4 +9,4 @@ join departments d on (d.id = e.dept_id)
99
where e.staffno <= 10
1010
and d.dname in ('Department Name 1','Department Name 2');
1111

12-
@advancedp
12+
@planp

0 commit comments

Comments
 (0)