Skip to content

Commit e7f0c92

Browse files
nigelbaylissnigelbayliss
committed
update plans
1 parent 3964bbb commit e7f0c92

File tree

17 files changed

+342
-883
lines changed

17 files changed

+342
-883
lines changed

‎optimizer/execution_plans/part0/EM_sqlmonitor_output.html

Lines changed: 30 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -11,26 +11,26 @@
1111
<script type="text/javascript">writeIframe();</script>
1212
<script id="fxtmodel" type="text/xml">
1313
<!--FXTMODEL-->
14-
<report db_version="12.2.0.1.0" elapsed_time="0.09" cpu_time="0.05" cpu_cores="1" hyperthread="N" timezone_offset="0" packs="2">
15-
<report_id><![CDATA[/orarep/sqlmonitor/main%3finst_id%3d1%26session_id%3d62%26session_serial%3d34124%26sql_exec_id%3d16777234%26sql_exec_start%3d03%3a09%3a2018%2010%3a37%3a32%26sql_id%3dfy25z2r5xpfq4]]></report_id>
16-
<sql_monitor_report version="4.0" sysdate="03/09/2018 10:37:32">
14+
<report db_version="12.2.0.1.0" elapsed_time="0.28" cpu_time="0.08" cpu_cores="1" hyperthread="N" timezone_offset="0" packs="2">
15+
<report_id><![CDATA[/orarep/sqlmonitor/main%3finst_id%3d1%26session_id%3d63%26session_serial%3d34039%26sql_exec_id%3d16777225%26sql_exec_start%3d03%3a09%3a2018%2014%3a00%3a09%26sql_id%3dan05rsj1up1k5]]></report_id>
16+
<sql_monitor_report version="4.0" sysdate="03/09/2018 14:00:09">
1717
<report_parameters>
18-
<sql_id>fy25z2r5xpfq4</sql_id>
19-
<sql_exec_id>16777234</sql_exec_id>
20-
<session_id>62</session_id>
21-
<session_serial>34124</session_serial>
22-
<sql_exec_start>03/09/2018 10:37:32</sql_exec_start>
18+
<sql_id>an05rsj1up1k5</sql_id>
19+
<sql_exec_id>16777225</sql_exec_id>
20+
<session_id>63</session_id>
21+
<session_serial>34039</session_serial>
22+
<sql_exec_start>03/09/2018 14:00:09</sql_exec_start>
2323
<bucket_count>2</bucket_count>
24-
<interval_start>03/09/2018 10:37:32</interval_start>
25-
<interval_end>03/09/2018 10:37:33</interval_end>
24+
<interval_start>03/09/2018 14:00:09</interval_start>
25+
<interval_end>03/09/2018 14:00:10</interval_end>
2626
</report_parameters>
27-
<target instance_id="1" session_id="62" session_serial="34124" sql_id="fy25z2r5xpfq4" sql_exec_start="03/09/2018 10:37:32" sql_exec_id="16777234" sql_plan_hash="2341252972" sql_full_plan_hash="1830508051" db_unique_name="orcl" db_platform_name="Linux x86 64-bit" report_host_name="prod12c">
27+
<target instance_id="1" session_id="63" session_serial="34039" sql_id="an05rsj1up1k5" sql_exec_start="03/09/2018 14:00:09" sql_exec_id="16777225" sql_plan_hash="2341252972" sql_full_plan_hash="1830508051" db_unique_name="orcl" db_platform_name="Linux x86 64-bit" report_host_name="prod12c">
2828
<user_id>134</user_id>
2929
<user>ADHOC</user>
3030
<program>sqlplus@prod12c (TNS V1-V3)</program>
3131
<module>SQL*Plus</module>
3232
<service>SYS$USERS</service>
33-
<sql_fulltext is_full="Y">select /*+ monitor */
33+
<sql_fulltext is_full="Y">select /*+ MONITOR */
3434
e.ename,r.rname
3535
from employees e
3636
join roles r on (r.id = e.role_id)
@@ -39,9 +39,9 @@
3939
and d.dname in ('Department Name 1','Department Name 2')</sql_fulltext>
4040
<status>DONE (ALL ROWS)</status>
4141
<refresh_count>5</refresh_count>
42-
<first_refresh_time>03/09/2018 10:37:32</first_refresh_time>
43-
<last_refresh_time>03/09/2018 10:37:32</last_refresh_time>
44-
<duration>.003999</duration>
42+
<first_refresh_time>03/09/2018 14:00:09</first_refresh_time>
43+
<last_refresh_time>03/09/2018 14:00:09</last_refresh_time>
44+
<duration>.001755</duration>
4545
<optimizer_env>
4646
<param name="active_instance_count">1</param>
4747
<param name="is_recur_flags">0</param>
@@ -59,8 +59,9 @@
5959
</optimizer_env>
6060
</target>
6161
<stats type="monitor">
62-
<stat name="elapsed_time">3999</stat>
63-
<stat name="cpu_time">3999</stat>
62+
<stat name="elapsed_time">1755</stat>
63+
<stat name="cpu_time">1000</stat>
64+
<stat name="other_wait_time">755</stat>
6465
<stat name="user_fetch_count">2</stat>
6566
<stat name="buffer_gets">28</stat>
6667
</stats>
@@ -152,8 +153,8 @@
152153
<plan_monitor max_io_reqs="0" max_io_bytes="0">
153154
<operation id="0" name="SELECT STATEMENT" depth="0" position="0" skp="0">
154155
<stats type="plan_monitor">
155-
<stat name="first_active">03/09/2018 10:37:32</stat>
156-
<stat name="last_active">03/09/2018 10:37:32</stat>
156+
<stat name="first_active">03/09/2018 14:00:09</stat>
157+
<stat name="last_active">03/09/2018 14:00:09</stat>
157158
<stat name="duration">1</stat>
158159
<stat name="from_most_recent">0</stat>
159160
<stat name="from_sql_exec_start">0</stat>
@@ -171,8 +172,8 @@
171172
<time>1</time>
172173
</optimizer>
173174
<stats type="plan_monitor">
174-
<stat name="first_active">03/09/2018 10:37:32</stat>
175-
<stat name="last_active">03/09/2018 10:37:32</stat>
175+
<stat name="first_active">03/09/2018 14:00:09</stat>
176+
<stat name="last_active">03/09/2018 14:00:09</stat>
176177
<stat name="duration">1</stat>
177178
<stat name="from_most_recent">0</stat>
178179
<stat name="from_sql_exec_start">0</stat>
@@ -211,8 +212,8 @@
211212
<time>1</time>
212213
</optimizer>
213214
<stats type="plan_monitor">
214-
<stat name="first_active">03/09/2018 10:37:32</stat>
215-
<stat name="last_active">03/09/2018 10:37:32</stat>
215+
<stat name="first_active">03/09/2018 14:00:09</stat>
216+
<stat name="last_active">03/09/2018 14:00:09</stat>
216217
<stat name="duration">1</stat>
217218
<stat name="from_most_recent">0</stat>
218219
<stat name="from_sql_exec_start">0</stat>
@@ -243,8 +244,8 @@
243244
<time>1</time>
244245
</optimizer>
245246
<stats type="plan_monitor">
246-
<stat name="first_active">03/09/2018 10:37:32</stat>
247-
<stat name="last_active">03/09/2018 10:37:32</stat>
247+
<stat name="first_active">03/09/2018 14:00:09</stat>
248+
<stat name="last_active">03/09/2018 14:00:09</stat>
248249
<stat name="duration">1</stat>
249250
<stat name="from_most_recent">0</stat>
250251
<stat name="from_sql_exec_start">0</stat>
@@ -280,8 +281,8 @@
280281
<time>1</time>
281282
</optimizer>
282283
<stats type="plan_monitor">
283-
<stat name="first_active">03/09/2018 10:37:32</stat>
284-
<stat name="last_active">03/09/2018 10:37:32</stat>
284+
<stat name="first_active">03/09/2018 14:00:09</stat>
285+
<stat name="last_active">03/09/2018 14:00:09</stat>
285286
<stat name="duration">1</stat>
286287
<stat name="from_most_recent">0</stat>
287288
<stat name="from_sql_exec_start">0</stat>
@@ -304,8 +305,8 @@
304305
<time>1</time>
305306
</optimizer>
306307
<stats type="plan_monitor">
307-
<stat name="first_active">03/09/2018 10:37:32</stat>
308-
<stat name="last_active">03/09/2018 10:37:32</stat>
308+
<stat name="first_active">03/09/2018 14:00:09</stat>
309+
<stat name="last_active">03/09/2018 14:00:09</stat>
309310
<stat name="duration">1</stat>
310311
<stat name="from_most_recent">0</stat>
311312
<stat name="from_sql_exec_start">0</stat>

‎optimizer/execution_plans/part0/README.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@ Spool files (.lst) are included so you can see the expected results.
66

77
To run the examples yourself, use a DBA account and start by running "ctables.sql". Note that this will drop some tables - so use a test database only!
88

9-
Next, run the examples: eg1.sql ... eg5.sql
9+
Next, run the examples: eg1.sql ... eg6.sql
1010

1111
An interactive report from Enterprise Manager us also included: EM_sqlmonitor_output.html
1212

‎optimizer/execution_plans/part0/advanced.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,4 +2,4 @@ set linesize 220 tab off pagesize 1000 trims on
22
column plan_table_output format a120
33

44
SELECT *
5-
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST ADVANCED'));
5+
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ADVANCED'));

‎optimizer/execution_plans/part0/advancedp.sql

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

‎optimizer/execution_plans/part0/eg1.lst

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@ SQL> set linesize 220 tab off pagesize 1000 trims on
1919
SQL> column plan_table_output format a120
2020
SQL>
2121
SQL> SELECT *
22-
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST ADVANCED'));
22+
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ADVANCED'));
2323

2424
PLAN_TABLE_OUTPUT
2525
------------------------------------------------------------------------------------------------------------------------

‎optimizer/execution_plans/part0/eg1.sql

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +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-
--
12-
-- Show advanced plan for the SQL statement above
13-
--
1411
@advanced

‎optimizer/execution_plans/part0/eg2.lst

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@ SQL> set linesize 210 tab off pagesize 1000 trims on
2020
SQL> column plan_table_output format a200
2121
SQL>
2222
SQL> SELECT *
23-
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST ALLSTATS ADVANCED'));
23+
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ADVANCED'));
2424

2525
PLAN_TABLE_OUTPUT
2626
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
@@ -37,7 +37,7 @@ Plan hash value: 2341252972
3737
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
3838
--------------------------------------------------------------------------------------------------------------------------------------------------------
3939
| 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| 839K (0)|
40+
|* 1 | HASH JOIN | | 1 | 3 | 192 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 28 | 1448K| 1448K| 830K (0)|
4141
|* 2 | HASH JOIN | | 1 | 3 | 147 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 17 | 2545K| 2545K| 930K (0)|
4242
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 2 | 42 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | | | |
4343
|* 4 | TABLE ACCESS FULL| EMPLOYEES | 1 | 10 | 280 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 7 | | | |

‎optimizer/execution_plans/part0/eg2.sql

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +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-
--
13-
-- Show advanced plan with runtime statistics
14-
--
1512
@stats

‎optimizer/execution_plans/part0/eg3.lst

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@ SQL> -- Long running queries will be monitored automatically
1212
SQL> -- so the hint won't always be required. In this case,
1313
SQL> -- it's over very quickly so I've added the hint.
1414
SQL> --
15-
SQL> select /*+ monitor */
15+
SQL> select /*+ MONITOR */
1616
2 e.ename,r.rname
1717
3 from employees e
1818
4 join roles r on (r.id = e.role_id)
@@ -54,39 +54,39 @@ SQL Monitoring Report
5454

5555
SQL Text
5656
------------------------------
57-
select /*+ monitor */ e.ename,r.rname from employees e join roles r on (r.id = e.role_id) join departments d on (d.id = e.dept_id) where e.staffno <= 10 and d.dname in ('Department Name 1','Department Name 2')
57+
select /*+ MONITOR */ e.ename,r.rname from employees e join roles r on (r.id = e.role_id) join departments d on (d.id = e.dept_id) where e.staffno <= 10 and d.dname in ('Department Name 1','Department Name 2')
5858

5959
Global Information
6060
------------------------------
6161
Status : DONE (ALL ROWS)
6262
Instance ID : 1
63-
Session : ADHOC (42:2052)
64-
SQL ID : fy25z2r5xpfq4
65-
SQL Execution ID : 16777235
66-
Execution Started : 03/09/2018 10:42:01
67-
First Refresh Time : 03/09/2018 10:42:01
68-
Last Refresh Time : 03/09/2018 10:42:01
69-
Duration : .002s
63+
Session : ADHOC (63:23029)
64+
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
7070
Module/Action : SQL*Plus/-
7171
Service : SYS$USERS
7272
Program : sqlplus@prod12c (TNS V1-V3)
7373
Fetch Calls : 2
7474

7575
Global Stats
76-
======================================
77-
| Elapsed | Cpu | Fetch | Buffer |
78-
| Time(s) | Time(s) | Calls | Gets |
79-
======================================
80-
| 0.00 | 0.00 | 2 | 28 |
81-
======================================
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+
=================================================
8282

8383
SQL Plan Monitoring Details (Plan Hash Value=2341252972)
8484
==========================================================================================================================================
8585
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
8686
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
8787
==========================================================================================================================================
8888
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | . | | |
89-
| 1 | HASH JOIN | | 3 | 6 | 1 | +0 | 1 | 1 | 809KB | | |
89+
| 1 | HASH JOIN | | 3 | 6 | 1 | +0 | 1 | 1 | 811KB | | |
9090
| 2 | HASH JOIN | | 3 | 4 | 1 | +0 | 1 | 2 | 898KB | | |
9191
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 2 | 1 | +0 | 1 | 2 | . | | |
9292
| 4 | TABLE ACCESS FULL | EMPLOYEES | 10 | 2 | 1 | +0 | 1 | 10 | . | | |

‎optimizer/execution_plans/part0/eg3.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ var sqlid varchar2(100)
1111
-- so the hint won't always be required. In this case,
1212
-- it's over very quickly so I've added the hint.
1313
--
14-
select /*+ monitor */
14+
select /*+ MONITOR */
1515
e.ename,r.rname
1616
from employees e
1717
join roles r on (r.id = e.role_id)
@@ -44,7 +44,7 @@ from dual;
4444

4545
set termout off feedback off
4646
spool monitor_output.html
47-
select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id=>:sqlid, report_level=>'all', type=>'html') report
47+
select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id=>:sqlid, report_level=>'all', type=>'active') report
4848
from dual;
4949
spool off
5050

0 commit comments

Comments
 (0)