/*
|| Oracle 11g SQL Plan Management Listing 1
||
|| Demonstrates Oracle 11g SQL Plan Management(SPM) basic techniques,
|| including:
|| - Enabling automatic capture of SQL Plan Baselines
|| - Displaying captured SQL Plan Baselines
|| - Capturing new (but "unaccepted") SQL Plan Baselines
|| - Displaying SQL Plan Management summary and detail metadata
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| SQL Plan Management features, and they should be carefully proofread
|| before executing them against any existing Oracle database(s) to avoid
|| potential damage!
*/
/*
|| Listing 1.1: A Candidate for SQL Plan Management
*/
-----
-- SPM Scenario 1.1:
-- Create a query that returns a better execution plan when the setting
-- for OPTIMIZER_MODE is ALL_ROWS vs. FIRST_ROWS
-----
SELECT /*SPM_1.1*/
S.cust_id
,C.cust_last_name
,S.prod_id
,P.prod_name
,S.amount_sold
,S.quantity_sold
FROM
sh.sales S
,sh.customers C
,sh.products P
WHERE S.cust_id = C.cust_id
AND S.prod_id = P.prod_id
AND S.prod_id BETWEEN 40 AND 60
AND S.cust_id BETWEEN 13000 AND 15000
;
/*
|| Listing 1.2: Invoking Automatic SQL Plan Management
*/
-----
-- Step 1: Initialize Automatic SQL Plan Management
-- 1.) Connect as the Load Generator (LDGN) user
-- 2.) Activate automatic capture of SQL Plan Baselines via the
-- OPTIMIZER_CAPTURE_SQL_PLAN_MANAGEMENT parameter
-- 3.) Insure automatic SQL Plan Baselines are used via the
-- OPTIMIZER_USE_SQL_PLAN_MANAGEMENT parameter
-- 4.) Insure the default optimizer mode is set to maximize throughput
-- via the OPTIMIZER_MODE parameter
-----
CONNECT ldgn/ldgn;
ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;
ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;
ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
-----
-- Step 2: Execute the sample code (SPM_1_1.sql) twice. This activates automatic
-- capture of a SQL Plan Baseline when the statement is run for the second time
-----
@SPM_1_1.sql;
@SPM_1_1.sql;
/*
|| Listing 1.3: Showing Results of Automatic SQL Plan Management
*/
-----
-- View: DBA_SQL_PLAN_BASELINES
-- Purpose: Shows capture and status parameters for all captured SQL Plan
-- Baselines for a specific set of SQL statements
-----
TTITLE 'Current SQL Plan Baselines|(From DBA_SQL_PLAN_BASELINES)'
COL creator FORMAT A08 HEADING 'Creator'
COL hndle FORMAT A08 HEADING 'SQL|Handle'
COL plnme FORMAT A08 HEADING 'Plan|Name'
COL sql_hdr FORMAT A25 HEADING 'SQL Text' WRAP
COL origin FORMAT A12 HEADING 'Origin'
COL optimizer_cost FORMAT 9999999 HEADING 'CBO|Cost'
COL enabled FORMAT A04 HEADING 'Ena-|bled'
COL accepted FORMAT A04 HEADING 'Acpt'
COL fixed FORMAT A04 HEADING 'Fixd'
COL autopurge FORMAT A04 HEADING 'Auto|Purg'
COL create_dt FORMAT A11 HEADING 'Created|On' WRAP
COL lst_exc_dt FORMAT A11 HEADING 'Last|Executed' WRAP
SELECT
creator
,SUBSTR(sql_handle, -8, 8) hndle
,SUBSTR(plan_name, -8, 8) plnme
,SUBSTR(sql_text, 1, 75) sql_hdr
,origin
,optimizer_cost
,enabled
,accepted
,fixed
,autopurge
,TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') create_dt
,TO_CHAR(last_executed, 'yyyy-mm-dd hh24:mi:ss') lst_exc_dt
FROM dba_sql_plan_baselines
WHERE (sql_text LIKE '%SPM%')
ORDER BY 1,2,3
;
TTITLE OFF
Current SQL Plan Baselines
(From DBA_SQL_PLAN_BASELINES)
SQL Plan CBO Ena- Auto Created Last
Creator Handle Name SQL Text Origin Cost bled Acpt Fixd Purg On Executed
-------- -------- -------- ------------------------- ------------ -------- ---- ---- ---- ---- ----------- -----------
LDGN 68516a84 07e0351f SELECT /*SPM_1.1*/ AUTO-CAPTURE 757 YES YES NO YES 2008-01-20 2008-01-20
S.cust_id 10:47:14 10:47:31
,C.cust_last_name
,S.prod_id
,P.pro
-----
-- Listing 1.4: Switch the optimizer mode so that the first few rows are retrieved
-- (the opposite of maximized throughput) by setting the OPTIMIZER_MODE parameter
-- to FIRST_ROWS, and then re-execute the sample code once again
-----
ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';
@SPM_1_1.sql;
-----
-- Finally, deactivate automatic SQL Plan Baseline Capture:
-----
ALTER SESSION SET optimizer_capture_sql_plan_baselines=FALSE;
Current SQL Plan Baselines
(From DBA_SQL_PLAN_BASELINES)
SQL Plan CBO Ena- Auto Created Last
Creator Handle Name SQL Text Origin Cost bled Acpt Fixd Purg On Executed
-------- -------- -------- ------------------------- ------------ -------- ---- ---- ---- ---- ----------- -----------
LDGN 68516a84 07e0351f SELECT /*SPM_1.1*/ AUTO-CAPTURE 757 YES YES NO YES 2008-01-20 2008-01-20
S.cust_id 10:47:14 10:47:31
,C.cust_last_name
,S.prod_id
,P.pro
LDGN 68516a84 ddc1fcd0 SELECT /*SPM_1.1*/ AUTO-CAPTURE 2388 YES NO NO YES 2008-01-20
S.cust_id 11:04:03
,C.cust_last_name
,S.prod_id
,P.pro
/*
|| Listing 1.5: Viewing SQL Plan Baseline information using DBMS_XPLAN
*/
-----
-- Show the resulting SQL Plan Baselines that have been retained
-- for SQL statements containing the text "SPM" via a call to procedure
-- DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE
-----
SET LINESIZE 150
SET PAGESIZE 2000
SELECT PT.*
FROM (SELECT
DISTINCT sql_handle
FROM dba_sql_plan_baselines
WHERE sql_text like '%SPM%') SPB,
TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,'TYPICAL +NOTE')) PT;
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_54df96a768516a84
SQL text: SELECT /*SPM_1.1*/ S.cust_id ,C.cust_last_name ,S.prod_id
,P.prod_name ,S.amount_sold ,S.quantity_sold FROM
sh.sales S ,sh.customers C ,sh.products P WHERE S.cust_id =
C.cust_id AND S.prod_id = P.prod_id AND S.prod_id BETWEEN 40 AND
60 AND S.cust_id BETWEEN 13000 AND 15000
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_68516a8407e0351f
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2383008347
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 223 | 13380 | 758 (1)| 00:00:10 | | |
|* 1 | HASH JOIN | | 223 | 13380 | 758 (1)| 00:00:10 | | |
|* 2 | HASH JOIN | | 451 | 21197 | 352 (1)| 00:00:05 | | |
| 3 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 13 | 390 | 2 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | PRODUCTS_PK | 13 | | 1 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL | | 2563 | 43571 | 349 (0)| 00:00:05 | 1 | 28 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2563 | 43571 | 349 (0)| 00:00:05 | 1 | 28 |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 8 | BITMAP AND | | | | | | | |
| 9 | BITMAP MERGE | | | | | | | |
|* 10 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | | | | 1 | 28 |
| 11 | BITMAP MERGE | | | | | | | |
|* 12 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | | 1 | 28 |
|* 13 | TABLE ACCESS FULL | CUSTOMERS | 1064 | 13832 | 405 (1)| 00:00:05 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."CUST_ID"="C"."CUST_ID")
2 - access("S"."PROD_ID"="P"."PROD_ID")
4 - access("P"."PROD_ID">=40 AND "P"."PROD_ID"<=60)
10 - access("S"."PROD_ID">=40 AND "S"."PROD_ID"<=60)
12 - access("S"."CUST_ID">=13000 AND "S"."CUST_ID"<=15000)
13 - filter("C"."CUST_ID"<=15000 AND "C"."CUST_ID">=13000)
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_68516a84ddc1fcd0
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3352396349
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 223 | 13380 | 2388 (1)| 00:00:29 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 223 | 13380 | 2388 (1)| 00:00:29 | | |
| 3 | NESTED LOOPS | | 451 | 21197 | 1937 (1)| 00:00:24 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 13 | 390 | 2 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | PRODUCTS_PK | 13 | | 1 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL | | 36 | 612 | 1937 (1)| 00:00:24 | 1 | 28 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 36 | 612 | 1937 (1)| 00:00:24 | 1 | 28 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 9 | BITMAP AND | | | | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
| 11 | BITMAP MERGE | | | | | | | |
|* 12 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | | 1 | 28 |
|* 13 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 13 | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("P"."PROD_ID">=40 AND "P"."PROD_ID"<=60)
10 - access("S"."PROD_ID"="P"."PROD_ID")
filter("S"."PROD_ID"<=60 AND "S"."PROD_ID">=40)
12 - access("S"."CUST_ID">=13000 AND "S"."CUST_ID"<=15000)
13 - access("S"."CUST_ID"="C"."CUST_ID")
filter("C"."CUST_ID"<=15000 AND "C"."CUST_ID">=13000)