Overview of Practices for Lesson 4
Practice 4-2: Extracting Execution Plans
10. Start the execution of your query from session 1 by pressing Enter. Note: Move to the next step without waiting
set echo on
set linesize 200 pagesize 1000
explain plan for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
select * from table(dbms_xplan.display);
8. Now, you want to monitor this execution plan that uses a hash join to compare it with the one generated in step 4. In addition, you want to make sure that you use the correct plan this time. So, in your session 1, start Autotrace, and be ready to execute the following query. Do not execute it yet because you need to start SQL Monitoring in your session 2:
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
Session 1:
---
SQL> set autotrace on SQL> @ep_execute
9. From your session 2, be ready to execute your SQL Monitoring command again. Do not execute it yet though.
Session 2:
---
SQL> @ep_monitor.sql
10. Start the execution of your query from session 1 by pressing Enter. Note: Move to the next step without waiting.
Session 1:
---
SQL> @ep_execute SQL> set echo on
Oracle University and InfoTech (Pvt.) Ltd use only
SQL>
SQL> set timing on SQL>
SQL> select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
---
set echo on
set timing on
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
11. From your session 2, start monitoring your query by pressing Enter. After the query is executed, enter “/” and go back to your SQL*Plus session as many times as necessary until session 1 is done with its execution. What do you observe?
You can see that the optimizer uses a hash join on top of two index fast full scans. Looking at the various reports, you can clearly see how the optimizer processes a hash join by reading the driving index in memory first. This operation is quick. Though you cannot see it run, it is already done the first time you can look at it. Then the probe is performed on the index again. This operation takes more time. Also, note that the cost information is provided in the execution plan.
Oracle University and InfoTech (Pvt.) Ltd use only
SQL Monitoring Report
SQL Text
--- select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1 ---
Execution Started : 06/18/2010 12:41:00 First Refresh Time : 06/18/2010 12:41:06 Last Refresh Time : 06/18/2010 12:41:08
SQL Plan Monitoring Details (Plan Hash Value=3253233075)
================================================================
Oracle University and InfoTech (Pvt.) Ltd use only
| 0 | SELECT STATEMENT | | |
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Global Information
Execution Started : 06/18/2010 12:41:00 First Refresh Time : 06/18/2010 12:41:06 Last Refresh Time : 06/18/2010 12:41:10 Duration : 12s
Module/Action : SQL*Plus/- Service : SYS$USERS
Oracle University and InfoTech (Pvt.) Ltd use only
Program : [email protected] (TNS
SQL Plan Monitoring Details (Plan Hash Value=3253233075)
================================================================
Oracle University and InfoTech (Pvt.) Ltd use only
---
SQL Monitoring Report
SQL Text
---
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Global Information
Execution Started : 06/18/2010 12:41:00 First Refresh Time : 06/18/2010 12:41:06 Last Refresh Time : 06/18/2010 12:41:14
SQL Plan Monitoring Details (Plan Hash Value=3253233075)
================================================================
Oracle University and InfoTech (Pvt.) Ltd use only
| 0 | SELECT STATEMENT | | |
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Global Information
Execution Started : 06/18/2010 12:41:00 First Refresh Time : 06/18/2010 12:41:06 Last Refresh Time : 06/18/2010 12:41:18 Duration : 20s
Module/Action : SQL*Plus/- Service : SYS$USERS
Oracle University and InfoTech (Pvt.) Ltd use only
Program : [email protected] (TNS
SQL Plan Monitoring Details (Plan Hash Value=3253233075)
================================================================
Oracle University and InfoTech (Pvt.) Ltd use only
---
SQL Monitoring Report
SQL Text
---
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Global Information
Execution Started : 06/18/2010 12:41:00 First Refresh Time : 06/18/2010 12:41:06 Last Refresh Time : 06/18/2010 12:41:24
SQL Plan Monitoring Details (Plan Hash Value=3253233075)
================================================================
Oracle University and InfoTech (Pvt.) Ltd use only
| 0 | SELECT STATEMENT | | |
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Global Information
Execution Started : 06/18/2010 12:41:00 First Refresh Time : 06/18/2010 12:41:06 Last Refresh Time : 06/18/2010 12:41:30 Duration : 31s
Module/Action : SQL*Plus/-
Oracle University and InfoTech (Pvt.) Ltd use only
Service : SYS$USERS
SQL Plan Monitoring Details (Plan Hash Value=3253233075)
================================================================
Oracle University and InfoTech (Pvt.) Ltd use only
---
SQL Monitoring Report
SQL Text
---
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Global Information
Execution Started : 06/18/2010 12:41:00 First Refresh Time : 06/18/2010 12:41:06 Last Refresh Time : 06/18/2010 12:41:32
SQL Plan Monitoring Details (Plan Hash Value=3253233075)
================================================================
Oracle University and InfoTech (Pvt.) Ltd use only
| 0 | SELECT STATEMENT | | |
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Global Information
Execution Started : 06/18/2010 12:41:00 First Refresh Time : 06/18/2010 12:41:06 Last Refresh Time : 06/18/2010 12:41:35 Duration : 35s
Module/Action : SQL*Plus/- Service : SYS$USERS
Oracle University and InfoTech (Pvt.) Ltd use only
Program : [email protected] (TNS
SQL Plan Monitoring Details (Plan Hash Value=3253233075)
================================================================
12. When your query is executed, what do you observe in your session 1?
Session 1 also reports the same execution plan as the one you observed in session 2.
Oracle University and InfoTech (Pvt.) Ltd use only
Session 1:
Predicate Information (identified by operation id):
---
2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1)
Oracle University and InfoTech (Pvt.) Ltd use only
0 physical reads
13. In session 1, disable Autotrace.
Session 1:
---
SQL> set autotrace off SQL>
14. From your session 1, how can you ensure that you gather all execution plan statistics for the following query without changing any session parameters? Implement your solution.
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
SQL> select /*+ gather_plan_statistics */ count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
COUNT(*) --- 400000000
Elapsed: 00:01:32.19 SQL>
15. From your session 1, retrieve all execution plans corresponding to all the queries you executed since the beginning of this lab. What is your conclusion?
a. The easiest way to find out all the plans is to look at the content of the SGA using the dbms_xplan.display_cursor function. First, you must determine the SQL_Ids
Oracle University and InfoTech (Pvt.) Ltd use only
used to represent your queries. You essentially have two queries, and one that has two children. You should now understand what happened at step 4. There was no cost information due to the use of the rule-based optimizer instead of the cost-based one.
Session 1:
---
SQL> @ep_retrieve_all_plans SQL> set echo on
SQL>
SQL> set linesize 200 pagesize 1000 SQL>
SQL> col sql_text format a50 SQL>
SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%from test t1, test t2%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
--- --- ---
dkz7v96ym42c6 3253233075 select count(*) from test t1, test t2 where t1.c=t
2.c and t1.c=1
dkz7v96ym42c6 1643938535 select count(*) from test t1, test t2 where t1.c=t
2.c and t1.c=1
8w580dd6ncgqw 3253233075 select /*+ gather_plan_statistics */
count(*) from
test t1, test t2 where t1.c=t2.c and t1.c=1
0w0va2d7hhtxa 3253233075 explain plan for select count(*) from test t1, tes
t t2 where t1.c=t2.c and t1.c=1
dd09kf5dnp1gt 903671040 select sql_id,plan_hash_value,sql_text from v$sql
where sql_text like '%from test t1, test t2%'
32fqwuk16uf23 3253233075 EXPLAIN PLAN SET STATEMENT_ID='PLUS2140495' FOR se
lect count(*) from test t1, test t2 where t1.c=t2.
c and t1.c=1
Oracle University and InfoTech (Pvt.) Ltd use only
6 rows selected. SQL_ID dkz7v96ym42c6, child number 0
---
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
Predicate Information (identified by operation id):
---
2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
SQL_ID dkz7v96ym42c6, child number 1 ---
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 1643938535
Oracle University and InfoTech (Pvt.) Ltd use only
---
Predicate Information (identified by operation id):
---
3 - access("T1"."C"=1)
4 - access("T1"."C"="T2"."C")
Note ---
- rule based optimizer used (consider using cbo)
49 rows selected. SQL_ID 8w580dd6ncgqw, child number 0
---
select /*+ gather_plan_statistics */ count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Oracle University and InfoTech (Pvt.) Ltd use only
| 1 | SORT AGGREGATE | | 1 | 1 | 6
Query Block Name / Object Alias (identified by operation id):
---
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("TEST"."C")) INDEX_FFS(@"SEL$1" "T2"@"SEL$1" ("TEST"."C")) LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1") USE_HASH(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA */
Predicate Information (identified by operation id):
---
2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
Column Projection Information (identified by operation id):
---
Oracle University and InfoTech (Pvt.) Ltd use only
1 - (#keys=0) COUNT(*)[22]
2 - (#keys=1)
3 - "T1"."C"[NUMBER,22]
4 - "T2"."C"[NUMBER,22]
55 rows selected.
Elapsed: 00:00:00.12 SQL>
---
set echo on
set linesize 200 pagesize 1000
col sql_text format a50
select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%from test t1, test t2%';
select * from
table(dbms_xplan.display_cursor('dkz7v96ym42c6',null,'TYPICAL'));
select * from
table(dbms_xplan.display_cursor('8w580dd6ncgqw',null,'ADVANCED ALLSTATS LAST'));
16. From session 1, try to retrieve your execution plans from the Automatic Workload Repository. What happens and why?
a. You can use the previously found SQL_Ids to search through the
DBA_HIST_SQLTEXT view. You should see that right now, none of your queries were stored in the AWR. Note: It is possible that a snapshot was taken during this practice.
If so, some or all of your queries are stored in the AWR.
Session 1:
---
SQL> @ep_retrieve_awr SQL> set echo on SQL>
SQL> set linesize 200 SQL>
SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext
Oracle University and InfoTech (Pvt.) Ltd use only
2 WHERE SQL_ID in ('dkz7v96ym42c6','8w580dd6ncgqw');
no rows selected
SQL>
Elapsed: 00:00:00.01 SQL>
---
set echo on
set linesize 200
SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID in ('dkz7v96ym42c6','8w580dd6ncgqw');
17. How can you ensure that you retrieve your queries from the Automatic Workload Repository? Implement your solution.
a. You must flush the SGA information to the AWR. You can use
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT for this purpose.
Session 1:
---
SQL> @ep_save_awr SQL> set echo on SQL>
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.68 SQL>
---
set echo on
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');
Oracle University and InfoTech (Pvt.) Ltd use only
18. Verify that your solution works.
SQL> set linesize 200 pagesize 1000 SQL>
SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM
3 TABLE (DBMS_XPLAN.DISPLAY_AWR('dkz7v96ym42c6'));
PLAN_TABLE_OUTPUT
--- SQL_ID dkz7v96ym42c6
---
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 1643938535
- rule based optimizer used (consider using cbo)
SQL_ID dkz7v96ym42c6 ---
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---
Oracle University and InfoTech (Pvt.) Ltd use only
| 0 | SELECT STATEMENT | | | | 2042
3 TABLE (DBMS_XPLAN.DISPLAY_AWR('8w580dd6ncgqw',null,null,'TYPICAL ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--- SQL_ID 8w580dd6ncgqw
---
select /*+ gather_plan_statistics */ count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Oracle University and InfoTech (Pvt.) Ltd use only
---
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level
23 rows selected.
Elapsed: 00:00:00.04 SQL>
---
set echo on
set linesize 200 pagesize 1000
SELECT PLAN_TABLE_OUTPUT FROM
TABLE (DBMS_XPLAN.DISPLAY_AWR('dkz7v96ym42c6'));
SELECT PLAN_TABLE_OUTPUT FROM
TABLE (DBMS_XPLAN.DISPLAY_AWR('8w580dd6ncgqw',null,null,'TYPICAL ALLSTATS LAST'));
19. Exit from both SQL*Plus sessions.
Session 1:
---
SQL> exit Disconnected …
$
Do not forget to exit from your session 2:
Session 2:
---
SQL> exit Disconnected …
$