/*
|| Oracle 11g SQL Plan Management Listing 2
||
|| Demonstrates Oracle 11g SQL Plan Management (SPM) advanced techniques,
|| including:
|| - Capturing SQL Plan Baselines via manual methods with DBMS_SPM
|| - Transferring captured SQL Plan Baselines between Oracle 10g and 11g databases
|| to "pre-seed" the SQL Management Baseline (SMB) with the most optimal execution
|| plans before an upgrade of an Oracle 10g database to Oracle 11g
|| - Transferring captured SQL Plan Baselines between test and production environments
|| to "pre-seed" the SQL Management Baseline (SMB) with the most typical execution
|| plans prior to deployment of a brand-new application
|| - Dropping existing SQL Plan Baselines from the SMB via manual methods
||
|| 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 2.1:
|| Create and prepare to populate a SQL Tuning Set (STS)
|| for selected SQL statements. Note that this STS will capture
|| all SQL statements which are executed by the LDGN user account
|| within a 5-minute period, and Oracle will check every 5 seconds
|| for any new statements
*/
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'STS_SPM_200'
);
END;
@SPM_2_1.sql;
/
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_SPM_200'
);
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'STS_SPM_200'
,basic_filter=> q'#sql_text LIKE '%SPM_2_1%' AND parsing_schema_name = 'LDGN'#'
,time_limit => 300
,repeat_interval => 5
);
END;
/
/*
|| Listing 2.2:
|| "Packing up" and exporting the Oracle 10gR2 SQL Tuning Set prior to
|| its transport to Oracle 11g
*/
-----
-- Create a staging table to hold the SQL Tuning Set statements just created,
-- and then "pack up" (i.e. populate) the staging table
-----
DROP TABLE ldgn.sts_staging PURGE;
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => 'STS_STAGING'
,schema_name => 'LDGN'
,tablespace_name => 'USERS'
);
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'STS_SPM_200'
,sqlset_owner => 'SYS'
,staging_table_name => 'STS_STAGING'
,staging_schema_owner => 'LDGN'
);
END;
/
-----
-- Invoke DataPump Export to export the table that contains the staged
-- SQL Tuning Set statements
-----
rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/log/*.log
rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/log/*.dmp
expdp system/oracle PARFILE=DumpStagingTable.dpectl
#####
# Contents of DumpStagingTable.dpectl parameter file:
#####
JOB_NAME=DumpStagingTable
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=LDGN_STS_Staging.dmp
SCHEMAS=LDGN
>>> Results:
Export: Release 10.2.0.1.0 - Production on Monday, 18 February, 2008 19:03:57
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."DUMPSTAGINGTABLE": system/******** PARFILE=DumpStagingTable.dpectl
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 576 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "LDGN"."STS_STAGING" 22.67 KB 8 rows
. . exported "LDGN"."STS_STAGING_CPLANS" 35.35 KB 25 rows
. . exported "LDGN"."STS_STAGING_CBINDS" 9.476 KB 0 rows
. . exported "LDGN"."PLAN_TABLE" 0 KB 0 rows
Master table "SYSTEM"."DUMPSTAGINGTABLE" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.DUMPSTAGINGTABLE is:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/LDGN_STS_Staging.dmp
Job "SYSTEM"."DUMPSTAGINGTABLE" successfully completed at 19:05:21
/*
|| Listing 2.3:
|| Transporting, importing, and "unpacking" the staged Oracle 10gR2 SQL Tuning
|| Set on the target Oracle 11g database
*/
-----
-- Invoke DataPump Import to import the table that contains the staged
-- SQL Tuning Set statements. Note that the default action of SKIPping
-- a table if it already exists has been overridden by supplying a value
-- of REPLACE for parameter TABLE_EXISTS_ACTION.
-----
impdp system/oracle PARFILE=LoadStagingTable.dpictl
#####
# Contents of LoadStagingTable.dpictl parameter file:
#####
JOB_NAME=LoadStagingTable
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=LDGN_STS_Staging.dmp
TABLE_EXISTS_ACTION=REPLACE
>>> Results of DataPump Import operation:
Import: Release 11.1.0.6.0 - Production on Monday, 18 February, 2008 19:09:29
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."LOADSTAGINGTABLE" successfully loaded/unloaded
Starting "SYSTEM"."LOADSTAGINGTABLE": system/******** PARFILE=LoadStagingTable.dpictl
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LDGN" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LDGN"."STS_STAGING" 22.67 KB 8 rows
. . imported "LDGN"."STS_STAGING_CPLANS" 35.35 KB 25 rows
. . imported "LDGN"."STS_STAGING_CBINDS" 9.476 KB 0 rows
. . imported "LDGN"."PLAN_TABLE" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Job "SYSTEM"."LOADSTAGINGTABLE" completed with 1 error(s) at 19:11:07
-----
-- Accept the SQL Tuning Set statements from the imported staging table
-- into the Oracle 11gR1 database
-----
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'STS_SPM_200'
);
END;
/
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => 'STS_SPM_200'
,sqlset_owner => 'SYS'
,replace => TRUE
,staging_table_name => 'STS_STAGING'
,staging_schema_owner => 'LDGN'
);
END;
/
-----
-- Listing 2.4:
-- Prove that the SQL Plan Baselines loaded into the SMB via manual methods are
-- actually being utilized by executing EXPLAIN PLANs against each statement from the
-- target Oracle 11g database
-----
SQL> EXPLAIN PLAN FOR
2 SELECT /*SPM_2_1.1*/
3 CTY.country_total_id
4 ,PR.promo_total_id
5 ,COUNT(S.amount_sold)
6 ,SUM(S.amount_sold)
7 ,SUM(S.quantity_sold)
8 FROM
9 sh.sales S
10 ,sh.customers C
11 ,sh.countries CTY
12 ,sh.promotions PR
13 WHERE S.cust_id = C.cust_id
14 AND C.country_id = CTY.country_id
15 AND S.promo_id = PR.promo_id
16 GROUP BY
17 CTY.country_total_id
18 ,PR.promo_total_id
19 ;
Explained.
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
3 ;
Plan hash value: 491136032
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | | 2325 (5)| 00:00:28 | | |
| 1 | HASH GROUP BY | | 1 | 44 | | 2325 (5)| 00:00:28 | | |
|* 2 | HASH JOIN | | 918K| 38M| | 2270 (3)| 00:00:28 | | |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 503 | 3521 | | 17 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 918K| 32M| | 2246 (2)| 00:00:27 | | |
| 5 | TABLE ACCESS FULL | COUNTRIES | 23 | 230 | | 3 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 918K| 23M| 1200K| 2236 (2)| 00:00:27 | | |
| 7 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 406 (1)| 00:00:05 | | |
| 8 | PARTITION RANGE ALL| | 918K| 14M| | 498 (4)| 00:00:06 | 1 | 28 |
| 9 | TABLE ACCESS FULL | SALES | 918K| 14M| | 498 (4)| 00:00:06 | 1 | 28 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROMO_ID"="PR"."PROMO_ID")
4 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")
6 - access("S"."CUST_ID"="C"."CUST_ID")
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_587c0594825d2e47" used for this statement
27 rows selected.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT /*SPM_2_1.2*/
3 CTY.country_id
4 ,CTY.country_subregion_id
5 ,CTY.country_region_id
6 ,CTY.country_total_id
7 ,PR.promo_total_id
8 ,COUNT(S.amount_sold)
9 ,SUM(S.amount_sold)
10 ,SUM(S.quantity_sold)
11 FROM
12 sh.sales S
13 ,sh.customers C
14 ,sh.countries CTY
15 ,sh.promotions PR
16 WHERE S.cust_id = C.cust_id
17 AND C.country_id = CTY.country_id
18 AND S.promo_id = PR.promo_id
19 GROUP BY
20 CTY.country_id
21 ,CTY.country_subregion_id
22 ,CTY.country_region_id
23 ,CTY.country_total_id
24 ,PR.promo_total_id
25 ;
Explained.
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
3 ;
Plan hash value: 491136032
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228 | 12312 | | 2325 (5)| 00:00:28 | | |
| 1 | HASH GROUP BY | | 228 | 12312 | | 2325 (5)| 00:00:28 | | |
|* 2 | HASH JOIN | | 918K| 47M| | 2270 (3)| 00:00:28 | | |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 503 | 3521 | | 17 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 918K| 41M| | 2246 (2)| 00:00:27 | | |
| 5 | TABLE ACCESS FULL | COUNTRIES | 23 | 460 | | 3 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 918K| 23M| 1200K| 2236 (2)| 00:00:27 | | |
| 7 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 406 (1)| 00:00:05 | | |
| 8 | PARTITION RANGE ALL| | 918K| 14M| | 498 (4)| 00:00:06 | 1 | 28 |
| 9 | TABLE ACCESS FULL | SALES | 918K| 14M| | 498 (4)| 00:00:06 | 1 | 28 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROMO_ID"="PR"."PROMO_ID")
4 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")
6 - access("S"."CUST_ID"="C"."CUST_ID")
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_54f64750825d2e47" used for this statement
27 rows selected.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT /*SPM_2_1.3*/
3 CTY.country_total_id
4 ,P.prod_id
5 ,P.prod_subcategory_id
6 ,P.prod_category_id
7 ,P.prod_total_id
8 ,CH.channel_id
9 ,CH.channel_class_id
10 ,CH.channel_total_id
11 ,PR.promo_total_id
12 ,COUNT(S.amount_sold)
13 ,SUM(S.amount_sold)
14 ,SUM(S.quantity_sold)
15 FROM
16 sh.sales S
17 ,sh.customers C
18 ,sh.countries CTY
19 ,sh.products P
20 ,sh.channels CH
21 ,sh.promotions PR
22 WHERE S.cust_id = C.cust_id
23 AND C.country_id = CTY.country_id
24 AND S.prod_id = P.prod_id
25 AND S.channel_id = CH.channel_id
26 AND S.promo_id = PR.promo_id
27 GROUP BY
28 CTY.country_total_id
29 ,P.prod_id
30 ,P.prod_subcategory_id
31 ,P.prod_category_id
32 ,P.prod_total_id
33 ,CH.channel_id
34 ,CH.channel_class_id
35 ,CH.channel_total_id
36 ,PR.promo_total_id
37 ;
Explained.
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
3 ;
Plan hash value: 2634317694
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5940 | 435K| | 8393 (2)| 00:01:41 | | |
| 1 | HASH GROUP BY | | 5940 | 435K| 74M| 8393 (2)| 00:01:41 | | |
|* 2 | HASH JOIN | | 918K| 65M| | 2593 (3)| 00:00:32 | | |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 503 | 3521 | | 17 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 918K| 59M| | 2569 (3)| 00:00:31 | | |
| 5 | TABLE ACCESS FULL | PRODUCTS | 72 | 1080 | | 3 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 918K| 46M| | 2560 (2)| 00:00:31 | | |
| 7 | TABLE ACCESS FULL | COUNTRIES | 23 | 230 | | 3 (0)| 00:00:01 | | |
|* 8 | HASH JOIN | | 918K| 37M| | 2550 (2)| 00:00:31 | | |
| 9 | TABLE ACCESS FULL | CHANNELS | 5 | 45 | | 3 (0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 918K| 29M| 1200K| 2541 (2)| 00:00:31 | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 406 (1)| 00:00:05 | | |
| 12 | PARTITION RANGE ALL| | 918K| 21M| | 498 (4)| 00:00:06 | 1 | 28 |
| 13 | TABLE ACCESS FULL | SALES | 918K| 21M| | 498 (4)| 00:00:06 | 1 | 28 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROMO_ID"="PR"."PROMO_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
6 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")
8 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
10 - access("S"."CUST_ID"="C"."CUST_ID")
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_8ec1a5862d9d97db" used for this statement
33 rows selected.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT /*SPM_2_1.4*/
3 CTY.country_total_id
4 ,P.prod_category_id
5 ,P.prod_total_id
6 ,CH.channel_id
7 ,CH.channel_class_id
8 ,CH.channel_total_id
9 ,PR.promo_total_id
10 ,COUNT(S.amount_sold)
11 ,SUM(S.amount_sold)
12 ,SUM(S.quantity_sold)
13 FROM
14 sh.sales S
15 ,sh.customers C
16 ,sh.countries CTY
17 ,sh.products P
18 ,sh.channels CH
19 ,sh.promotions PR
20 WHERE S.cust_id = C.cust_id
21 AND C.country_id = CTY.country_id
22 AND S.prod_id = P.prod_id
23 AND S.channel_id = CH.channel_id
24 AND S.promo_id = PR.promo_id
25 GROUP BY
26 CTY.country_total_id
27 ,P.prod_category_id
28 ,P.prod_total_id
29 ,CH.channel_id
30 ,CH.channel_class_id
31 ,CH.channel_total_id
32 ,PR.promo_total_id
33 ;
Explained.
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
3 ;
Plan hash value: 2634317694
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 568 | | 2648 (5)| 00:00:32 | | |
| 1 | HASH GROUP BY | | 8 | 568 | | 2648 (5)| 00:00:32 | | |
|* 2 | HASH JOIN | | 918K| 62M| | 2593 (3)| 00:00:32 | | |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 503 | 3521 | | 17 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 918K| 56M| | 2569 (3)| 00:00:31 | | |
| 5 | TABLE ACCESS FULL | PRODUCTS | 72 | 792 | | 3 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 918K| 46M| | 2560 (2)| 00:00:31 | | |
| 7 | TABLE ACCESS FULL | COUNTRIES | 23 | 230 | | 3 (0)| 00:00:01 | | |
|* 8 | HASH JOIN | | 918K| 37M| | 2550 (2)| 00:00:31 | | |
| 9 | TABLE ACCESS FULL | CHANNELS | 5 | 45 | | 3 (0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 918K| 29M| 1200K| 2541 (2)| 00:00:31 | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 406 (1)| 00:00:05 | | |
| 12 | PARTITION RANGE ALL| | 918K| 21M| | 498 (4)| 00:00:06 | 1 | 28 |
| 13 | TABLE ACCESS FULL | SALES | 918K| 21M| | 498 (4)| 00:00:06 | 1 | 28 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROMO_ID"="PR"."PROMO_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
6 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")
8 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
10 - access("S"."CUST_ID"="C"."CUST_ID")
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_96f761da2d9d97db" used for this statement
33 rows selected.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT /*SPM_2_1.5*/
3 CTY.country_id
4 ,CTY.country_subregion_id
5 ,CTY.country_region_id
6 ,CTY.country_total_id
7 ,P.prod_id
8 ,P.prod_subcategory_id
9 ,P.prod_category_id
10 ,P.prod_total_id
11 ,CH.channel_id
12 ,CH.channel_class_id
13 ,CH.channel_total_id
14 ,PR.promo_total_id
15 ,COUNT(S.amount_sold)
16 ,SUM(S.amount_sold)
17 ,SUM(S.quantity_sold)
18 FROM
19 sh.sales S
20 ,sh.customers C
21 ,sh.countries CTY
22 ,sh.products P
23 ,sh.channels CH
24 ,sh.promotions PR
25 WHERE S.cust_id = C.cust_id
26 AND C.country_id = CTY.country_id
27 AND S.prod_id = P.prod_id
28 AND S.channel_id = CH.channel_id
29 AND S.promo_id = PR.promo_id
30 GROUP BY
31 CTY.country_id
32 ,CTY.country_subregion_id
33 ,CTY.country_region_id
34 ,CTY.country_total_id
35 ,P.prod_id
36 ,P.prod_subcategory_id
37 ,P.prod_category_id
38 ,P.prod_total_id
39 ,CH.channel_id
40 ,CH.channel_class_id
41 ,CH.channel_total_id
42 ,PR.promo_total_id
43 ;
Explained.
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
3 ;
Plan hash value: 2634317694
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 74M| | 20707 (1)| 00:04:09 | | |
| 1 | HASH GROUP BY | | 918K| 74M| 168M| 20707 (1)| 00:04:09 | | |
|* 2 | HASH JOIN | | 918K| 74M| | 2593 (3)| 00:00:32 | | |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 503 | 3521 | | 17 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 918K| 68M| | 2569 (3)| 00:00:31 | | |
| 5 | TABLE ACCESS FULL | PRODUCTS | 72 | 1080 | | 3 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 918K| 55M| | 2560 (2)| 00:00:31 | | |
| 7 | TABLE ACCESS FULL | COUNTRIES | 23 | 460 | | 3 (0)| 00:00:01 | | |
|* 8 | HASH JOIN | | 918K| 37M| | 2550 (2)| 00:00:31 | | |
| 9 | TABLE ACCESS FULL | CHANNELS | 5 | 45 | | 3 (0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 918K| 29M| 1200K| 2541 (2)| 00:00:31 | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 406 (1)| 00:00:05 | | |
| 12 | PARTITION RANGE ALL| | 918K| 21M| | 498 (4)| 00:00:06 | 1 | 28 |
| 13 | TABLE ACCESS FULL | SALES | 918K| 21M| | 498 (4)| 00:00:06 | 1 | 28 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROMO_ID"="PR"."PROMO_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
6 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")
8 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
10 - access("S"."CUST_ID"="C"."CUST_ID")
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_816fca3a2d9d97db" used for this statement
33 rows selected.
/*
|| Listing 2.5:
|| Prepare to deploy a simulated new application to the current Oracle 11g database.
|| Note that all SQL Plan Baselines that are currently tagged as SPM_2 statements
|| will first be purged from the SMB.
*/
-----
-- Clear all SQL Plan Baselines whose SQL text contains the tag "SPM_2"
-----
SET SERVEROUTPUT ON
VARIABLE nRtnCode NUMBER;
BEGIN
:nRtnCode := 0;
FOR r_SPMB IN (
SELECT sql_handle, plan_name
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM_2%'
)
LOOP
:nRtnCode :=
DBMS_SPM.DROP_SQL_PLAN_BASELINE(r_SPMB.sql_handle, r_SPMB.plan_name);
DBMS_OUTPUT.PUT_LINE('Drop of SPBs for Handle ' || r_SPMB.sql_handle
|| ' and Plan ' || r_SPMB.plan_name
|| ' completed: RC = ' || :nRtnCode);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fatal error during cleanup of SQL Plan Baselines!');
ROLLBACK;
END;
/
-----
-- Run DDL commands to create the Sales Force Administration (SFA) schema
-- and all related objects
-----
@SFA_Setup.sql;
/*
|| Listing 2.6:
|| Generate a SQL workload against the new application objects using six
|| queries tagged with a comment of SPM_2_2, and then capture the SQL Plan
|| Baselines into the SMB using DBMS_SPM.LOAD_PLANS_FROM CURSOR_CACHE
*/
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
@SPM_2_2.sql;
SET SERVEROUTPUT ON
VARIABLE plans_cached NUMBER;
BEGIN
:plans_cached :=
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
attribute_name => 'SQL_TEXT'
,attribute_value => '%SPM_2_2%'
,fixed => 'NO'
,enabled => 'YES'
);
DBMS_OUTPUT.PUT_LINE('>>> ' || :plans_cached || ' SQL statement(s) loaded from the cursor cache.');
END;
/
/*
|| Listing 2.7:
|| Staging, Packing, and Exporting SQL Plan Baselines
*/
-----
-- Create a SQL Plan Baseline staging table and then "pack" those SQL
-- Plan Baselines into a staging table
-----
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE (
table_name => 'SPM_STAGING'
,table_owner => 'SFA'
,tablespace_name => 'EXAMPLE'
);
END;
/
SET SERVEROUTPUT ON
VARIABLE plans_staged NUMBER;
BEGIN
:plans_staged :=
DBMS_SPM.PACK_STGTAB_BASELINE (
table_name => 'SPM_STAGING'
,table_owner => 'SFA'
,creator => 'SYS'
);
DBMS_OUTPUT.PUT_LINE('Total SQL Plan Baselines Staged: ' || :plans_staged);
END;
/
-----
-- Export SPM staging table via DataPump Export
-----
rm -f /u01/app/oracle/admin/orcl/dpdump/*.log
rm -f /u01/app/oracle/admin/orcl/dpdump/*.dmp
expdp system/oracle PARFILE=DumpStagedSPMs.dpectl
#####
# Contents of DumpStagedSPMs.dpectl parameter file:
#####
JOB_NAME=DumpStagedSPMs
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=SFA_SPM_Staging.dmp
TABLES=SFA.SPM_STAGING
>>> Results:
Export: Release 11.1.0.6.0 - Production on Tuesday, 19 February, 2008 9:28:34
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."DUMPSTAGEDSPMS": system/******** PARFILE=DumpStagedSPMs.dpectl
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SFA"."SPM_STAGING" 46.49 KB 6 rows
Master table "SYSTEM"."DUMPSTAGEDSPMS" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.DUMPSTAGEDSPMS is:
/u01/app/oracle/admin/orcl/dpdump/SFA_SPM_Staging.dmp
Job "SYSTEM"."DUMPSTAGEDSPMS" successfully completed at 09:29:50
/*
|| Listing 2.8:
|| Importing and "unpacking" the staged Oracle 11g SQL Plan Baselines into
|| the target Oracle 11g database
*/
-----
-- Invoke DataPump Import to import the table that contains the staged
-- SQL Tuning Set statements. Note that the default action of SKIPping
-- a table if it already exists has been overridden by supplying a value
-- of REPLACE for parameter TABLE_EXISTS_ACTION.
-----
impdp system/oracle PARFILE=LoadStagedSPMs.dpictl
#####
# Contents of LoadStagedSPMs.dpictl parameter file:
#####
JOB_NAME=LoadStagedSPMs
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=SFA_SPM_Staging.dmp
TABLE_EXISTS_ACTION=REPLACE
>>> Results of DataPump Import operation:
Import: Release 11.1.0.6.0 - Production on Tuesday, 19 February, 2008 9:31:41
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."LOADSTAGEDSPMS" successfully loaded/unloaded
Starting "SYSTEM"."LOADSTAGEDSPMS": system/******** PARFILE=LoadStagedSPMs.dpictl
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SFA"."SPM_STAGING" 46.49 KB 6 rows
Job "SYSTEM"."LOADSTAGEDSPMS" successfully completed at 09:31:52
-----
-- Clear all SQL Plan Baselines whose SQL text contains the tag "SPM_2"
-----
SET SERVEROUTPUT ON
VARIABLE nRtnCode NUMBER;
BEGIN
:nRtnCode := 0;
FOR r_SPMB IN (
SELECT sql_handle, plan_name
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SPM_2%'
)
LOOP
:nRtnCode :=
DBMS_SPM.DROP_SQL_PLAN_BASELINE(r_SPMB.sql_handle, r_SPMB.plan_name);
DBMS_OUTPUT.PUT_LINE('Drop of SPBs for Handle ' || r_SPMB.sql_handle
|| ' and Plan ' || r_SPMB.plan_name
|| ' completed: RC = ' || :nRtnCode);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fatal error during cleanup of SQL Plan Baselines!');
ROLLBACK;
END;
/
-----
-- Now, "unpack" the SQL Plan Baselines staging table directly into the SMB
-----
SET SERVEROUTPUT ON
VARIABLE plans_loaded NUMBER;
BEGIN
:plans_loaded :=
DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name => 'SPM_STAGING'
,table_owner => 'SFA'
,creator => 'SYS'
);
DBMS_OUTPUT.PUT_LINE('Total SQL Plan Baselines Loaded: ' || :plans_loaded);
END;
/
/*
|| Listing 2.9:
|| Show the current contents of the SQL Management Base
*/
Tue Feb 19 page 1
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
SYS 0047dfb5 e86f00e7 SELECT /*SPM_2_2.5*/ MANUAL-LOAD 13 YES YES NO YES 2008-02-19
SR.abbr 09:32:42
,SD.abbr
,SUM(SH.quantity_sold)
SYS 1e72d0bd dd777d18 SELECT /*SPM_2_2.4*/ MANUAL-LOAD 13 YES YES NO YES 2008-02-19
rgn_abbr 09:32:42
,dst_abbr
,ter_abbr
,cust_id
SYS 7f161ead bb24e20c SELECT /*SPM_2.2.2*/ MANUAL-LOAD 415 YES YES NO YES 2008-02-19
SR.abbr, 09:32:42
SD.abbr,
SZ.geo_id,
SYS 831c508c 3519879f SELECT /*SPM_2_2.3*/ MANUAL-LOAD 71 YES YES NO YES 2008-02-19
SR.abbr 09:32:42
,SD.abbr
,SZ.geo_id
,C.cust_id
SYS 9c7bbbfb 9d1c7b8e SELECT /*SPM_2.2.1*/ MANUAL-LOAD 921 YES YES NO YES 2008-02-19
C.cust_state_provinc 09:32:42
e
,SUM(sh.quantity_sold
)
SYS f6743c1d b197d40d SELECT /*SPM_2_2.6*/ MANUAL-LOAD 60 YES YES NO YES 2008-02-19
SR.abbr 09:32:42
,SUM(SH.quantity_sold)
,AVG(SH.quant
8 rows selected.