/*
|| Oracle 11g Automatic SQL Tuning Examples
||
|| Demonstrates Oracle 11g Automatic SQL Tuning techniques, including:
|| - Creation of required sample database objects
|| - Examples of invoking Automatic SQL Tuning via PL/SQL
|| - Examples of using Enterprise Manager Database Control to view and maintain
|| Automatic SQL Tuning settings and results
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| Automatic SQL Tuning, and they should be carefully proofread before
|| executing it against any existing Oracle database to avoid potential damage!
*/
/*
|| Listing 1: An example of a poorly performing SQL query because of over-
|| zealous optimizer HINTs
*/
>>> SQL statement:
EXPLAIN PLAN FOR
SELECT /*+ USE_NL(S C) FULL(C) FULL(S) LDGN*/
S.cust_id
,C.cust_last_name
,SUM(S.quantity_sold) qty_sold
,SUM(S.amount_sold) amt_sold
FROM
sh.sales S
,sh.customers C
WHERE S.cust_id = C.cust_id
AND S.cust_id IN (19010, 20403, 20407, 25939)
GROUP BY
S.cust_id
,C.cust_last_name
ORDER BY
S.cust_id
,C.cust_last_name
;
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
>>> Resulting output from PLAN_TABLE (with the deleterious HINT included).
Plan hash value: 3190495007
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 2418 (4)| 00:00:30 | | |
| 1 | SORT GROUP BY | | 4 | 104 | 2418 (4)| 00:00:30 | | |
| 2 | NESTED LOOPS | | 4 | 104 | 2417 (4)| 00:00:30 | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 4 | 52 | 406 (1)| 00:00:05 | | |
| 4 | PARTITION RANGE ALL| | 1 | 13 | 503 (5)| 00:00:07 | 1 | 28 |
|* 5 | TABLE ACCESS FULL | SALES | 1 | 13 | 503 (5)| 00:00:07 | 1 | 28 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."CUST_ID"=19010 OR "C"."CUST_ID"=20403 OR "C"."CUST_ID"=20407 OR
"C"."CUST_ID"=25939)
5 - filter(("S"."CUST_ID"=19010 OR "S"."CUST_ID"=20403 OR "S"."CUST_ID"=20407 OR
"S"."CUST_ID"=25939) AND "S"."CUST_ID"="C"."CUST_ID")
>>> Resulting output from PLAN_TABLE (without the deleterious HINT). Note the dramatic improvement in the overall optimizer cost.
Plan hash value: 936090140
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 218 (1)| 00:00:03 | | |
| 1 | SORT GROUP BY | | 4 | 104 | 218 (1)| 00:00:03 | | |
|* 2 | HASH JOIN | | 4 | 104 | 217 (1)| 00:00:03 | | |
| 3 | INLIST ITERATOR | | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 4 | 52 | 9 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 4 | | 5 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL | | 521 | 6773 | 208 (0)| 00:00:03 | 1 | 28 |
| 7 | INLIST ITERATOR | | | | | | | |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 521 | 6773 | 208 (0)| 00:00:03 | 1 | 28 |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | | | | 1 | 28 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CUST_ID"="C"."CUST_ID")
5 - access("C"."CUST_ID"=19010 OR "C"."CUST_ID"=20403 OR "C"."CUST_ID"=20407 OR "C"."CUST_ID"=25939)
10 - access("S"."CUST_ID"=19010 OR "S"."CUST_ID"=20403 OR "S"."CUST_ID"=20407 OR "S"."CUST_ID"=25939)Plan hash value: 3190495007
/*
|| Listing 2: Setting up the LDGN (Load Generator) user account and
|| related load generation objects
*/
CONNECT / AS SYSDBA
GRANT EXECUTE ON sys.dbms_lock TO PUBLIC;
DROP USER ldgn CASCADE;
CREATE USER ldgn IDENTIFIED BY ldgn;
GRANT CREATE SESSION TO ldgn;
GRANT DBA TO ldgn;
GRANT SELECT ON sh.sales TO ldgn;
GRANT SELECT ON sh.customers TO ldgn;
GRANT SELECT ON sh.products TO ldgn;
GRANT SELECT ON hr.countries TO ldgn;
GRANT SELECT ON hr.departments TO ldgn;
GRANT SELECT ON hr.employees TO ldgn;
GRANT SELECT ON hr.job_history TO ldgn;
GRANT SELECT ON hr.locations TO ldgn;
CONNECT ldgn/ldgn;
@?/rdbms/admin/utlxplan.sql;
CREATE OR REPLACE PACKAGE ldgn.pkg_load_generator
/*
|| Package: LDGN.PKG_LOAD_GENERATOR
|| Version: 11.1.0.6.0
|| Description: Generates different loads on the target database
|| for evalution of various Oracle 11G features.
|| Author: Jim Czuprynski (G+R)
*/
IS
TYPE rcReport IS REF CURSOR;
PROCEDURE RandomQuery(
rpt_cursor OUT LDGN.PKG_LOAD_GENERATOR.rcReport
);
END pkg_load_generator;
/
CREATE OR REPLACE PACKAGE BODY ldgn.pkg_load_generator
/*
|| Package Body: LDGN.PKG_LOAD_GENERATOR
|| Version: 11.1.0.6.0
|| Description: Generates various different loads on the target
|| database for evalution of various Oracle features.
|| Author: Jim Czuprynski (G+R)
*/
IS
FUNCTION Randomizer
/*
|| Function: Randomizer
|| Purpose: Returns a randomized Customer ID from SH.CUSTOMERS
|| Arguments: - none -
|| Returns: RandomCustID
*/
RETURN NUMBER
IS
RandomCustID NUMBER := 0;
BEGIN
SELECT cust_id
INTO RandomCustID
FROM (SELECT cust_id
FROM sh.customers SAMPLE(1)
ORDER BY DBMS_RANDOM.VALUE)
WHERE rownum < 2;
RETURN RandomCustID;
EXCEPTION
WHEN OTHERS THEN
NULL;
END Randomizer;
PROCEDURE RandomQuery(
rpt_cursor OUT LDGN.PKG_LOAD_GENERATOR.rcReport
)
/*
|| Procedure: RandomQuery
|| Purpose: Generates a random SQL query over a brief time frame
|| by calculating a count of all objects for a random range
|| of objects based on their OBJECT_ID value.
|| Arguments: Iteration - Beginning range of OBJECT_ID
*/
IS
iCustID NUMBER := 0;
BEGIN
iCustID := Randomizer;
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'LoadGenerator'
,action_name => 'RandomQuery'
);
OPEN rpt_cursor FOR
SELECT /*+ USE_NL(S C) FULL(S) FULL(C) LDGN */
S.cust_id
,C.cust_last_name
,SUM(S.quantity_sold) qty_sold
,SUM(S.amount_sold) amt_sold
FROM
sh.sales S,
sh.customers C
WHERE S.cust_id = C.cust_id
AND S.cust_id = iCustID
GROUP BY
S.cust_id
,C.cust_last_name
ORDER BY
S.cust_id
,C.cust_last_name
;
DBMS_LOCK.SLEEP(1);
EXCEPTION
WHEN OTHERS THEN
NULL;
END RandomQuery;
END pkg_load_generator;
/
/*
|| Listing 3: Executing Automatic SQL Tuning
*/
/*
|| Set SQL Tuning Task parameters specific to Automatic SQL
|| Tuning (AST) so that:
|| 1.) An AST task can perform test executions
|| 2.) An AST task can run for up to 30 minutes (1800 seconds)
|| 3.) Each candidate SQL statement can be evaluated for up to 15 minutes
|| 4.) SQL Profiles will be accepted automatically
|| 5.) A maximum of 25 SQL profiles will be generated during the next
|| AST execution
|| 6.) A maximum of 20000 SQL Profiles can exist
|| 7.) The history of prior AST runs will be retained for 60 days
*/
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,parameter => 'TEST_EXECUTE'
,value => 'FULL'
);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,parameter => 'TIME_LIMIT'
,value => 7200
);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,parameter => 'LOCAL_TIME_LIMIT'
,value => 1800
);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,parameter => 'ACCEPT_SQL_PROFILES'
,value => 'TRUE'
);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,parameter => 'MAX_SQL_PROFILES_PER_EXEC'
,value => 25
);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,parameter => 'MAX_AUTO_SQL_PROFILES'
,value => 20000
);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
,parameter => 'EXECUTION_DAYS_TO_EXPIRE'
,value => 60
);
END;
/
-----
-- Performs the following tasks:
-- 1.) Flushes the Shared Pool and Database Buffer Cache
-- 2.) Activates a hidden initialization parameter to temporary disable all
-- automatic tasks
-- 3.) Locates all current SQL Profiles and removes them
-- 4.) Starts the Automatic SQL Tuning task
-- 5.) Determines the current maintenance window name
-- 6.) Opens the corresponding maintenance window, but with other clients disabled
-- 7.) Once Automatic SQL tuning is completed, closes the AST maintenance window
-----
CONNECT / AS SYSDBA
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM SET "_enable_automatic_maintenance" = 0;
DECLARE
CURSOR existing_profiles
IS
SELECT name
FROM dba_sql_profiles
WHERE sql_text LIKE '%LDGN%'
;
min_snapshot NUMBER;
max_snapshot NUMBER;
BEGIN
DBMS_SQLTUNE.RESET_TUNING_TASK(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
);
FOR existing_profile IN existing_profiles
LOOP
DBMS_SQLTUNE.DROP_SQL_PROFILE(
name => existing_profile.name
);
END LOOP;
END;
/
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name => 'SYS_AUTO_SQL_TUNING_TASK'
);
END;
/
VARIABLE window VARCHAR2(20);
BEGIN
SELECT UPPER(TO_CHAR(SYSDATE,'fmday'))||'_WINDOW'
INTO :window
FROM dual
;
END;
/
PRINT window;
ALTER SYSTEM SET "_enable_automatic_maintenance" = 1;
DECLARE
nASTState NUMBER;
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection'
,operation => NULL
,window_name => :window
);
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto space advisor'
,operation => NULL
,window_name => :window
);
DBMS_SCHEDULER.OPEN_WINDOW(
window_name => :window
,duration => NULL
,force => TRUE
);
-- Pause for a brief time
DBMS_LOCK.SLEEP(120);
-- Check if the Automatic SQL Tuning task is still running. If it's not,
-- then leave the loop; otherwise, pause briefly and check again
LOOP
SELECT COUNT(*)
INTO nASTState
FROM dba_advisor_executions
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND status = 'EXECUTING';
IF (nASTState = 0)
THEN exit;
END IF;
DBMS_LOCK.SLEEP(90);
END LOOP;
DBMS_SCHEDULER.CLOSE_WINDOW(
window_name => :window
);
END;
/
-----
-- Re-enable automatic optimizer statistics collection and
-- automatic space advisor tasks. (However, though these tasks
-- will appear to be enabled in EM, they will actually still be
-- disabled because of the setting for _ENABLE_AUTOMATIC_MAINTENANCE.)
-----
ALTER SYSTEM SET "_enable_automatic_maintenance" = 0;
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection'
,operation => NULL
,window_name => :window
);
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto space advisor'
,operation => NULL
,window_name => :window
);
END;
/
/*
|| Listing 4: Generate a simulated load for Automatic SQL Tuning's consumption
*/
-----
-- 1.) Connect as LDGN user (for easier tracking)
-- 2.) Create an AWR Snapshot starting point
-- 3.) Generate a sufficiently "bad" SQL Workload that Automatic
-- SQL Tuning will detect and analyze
-- 4.) Create an AWR Snapshot end point
-----
CONNECT ldgn/ldgn
DECLARE
myRefCursor LDGN.PKG_LOAD_GENERATOR.RCREPORT;
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
FOR i IN 1..50
LOOP
LDGN.PKG_LOAD_GENERATOR.RANDOMQUERY(myRefCursor);
END LOOP;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
END;
/