/*
|| 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; /