Table of contents
Preamble
Automatic SQL Plan Management (ASPM) is an extension of SQL Plan Management (SPM) and is targeting at automating SPM on your workload by inspecting Automatic Workload Repository (AWR) and Automatic SQL Tuning Set (ASTS) that I have recently explored.
As already shared SPM does not require a license for Oracle Diagnostics Pack or Oracle Tuning Pack and is included in all version of the Oracle database version. Here is an extract of the Licensing guide from the 19c (always refer to live documentation for latest up-to-date information):
Automatic SPM is also included in the base edition but only starting with the Enterprise Edition (always refer to live documentation for latest up-to-date information):
I have tried to test this Automatic SPM feature on a 21.14 database but the feature has not been backported on the 21c release.
So my testing has been done with an Enterprise Edition Release 19.23 database (a pluggable database to be exact) running on Red Hat Enterprise Linux release 8.7 (Ootpa).
Automatic SQL Plan Management (ASPM) setup
As advised I kept optimizer_capture_sql_plan_baselines to its default false value:
SQL> show parameter optimizer%baselines NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines BOOLEAN FALSE optimizer_use_sql_plan_baselines BOOLEAN TRUE |
Then I activate the Automatic SPM with (AUTO in this 19c is equivalent to off, must go for 23ai to see a difference, not yet available at the time of writing this blog post but will test it later):
SQL> EXEC dbms_spm.configure('auto_spm_evolve_task','ON'); PL/SQL PROCEDURE successfully completed. |
Remark:
as clearly stated in the official documentation: The high-frequency task runs every hour and runs for no longer than 30 minutes. These settings are not configurable. The frequent executions mean that the optimizer has more opportunities to find and evolve better performing plans. Too bad when you are doing testing you have to wait multiple hours for the task to auto run…
SQL> SET lines 200 SQL> SET pages 1000 SQL> col parameter_name FOR a35 SQL> col parameter_value FOR a30 SQL> col last_modified FOR a30 SQL> col modified_by FOR a30 SQL> SELECT * FROM dba_sql_management_config WHERE parameter_name LIKE 'AUTO_SPM_EVOLVE_TASK%'; PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY ----------------------------------- ------------------------------ ------------------------------ ------------------------------ AUTO_SPM_EVOLVE_TASK ON 05-JUL-24 02.22.28.000000 PM SYS AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 |
The list of tunable parameters with DBMS_SPM.CONFIGURE:
SQL> col description FOR a40 word_wrapped SQL> SET pages 1000 SQL> SELECT parameter_name, parameter_value, description FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK' AND parameter_value != 'UNUSED'; PARAMETER_NAME PARAMETER_VALUE DESCRIPTION ----------------------------------- ------------------------------ ---------------------------------------- DAYS_TO_EXPIRE UNLIMITED The expiration TIME IN days FOR the CURRENT SQL ACCESS Advisor task DEFAULT_EXECUTION_TYPE SPM EVOLVE Tune the performance OF SQL statements EXECUTION_DAYS_TO_EXPIRE 30 Specifies the expiration TIME IN days FOR individual executions OF the CURRENT task JOURNALING INFORMATION Specifies logging OF messages TO the advisor journal MODE COMPREHENSIVE Specifies either a LIMITED OR comprehensive analysis operation, WHERE LIMITED runs IN less TIME but may produce slightly LOWER quality results TARGET_OBJECTS 1 Deprecated Parameter TIME_LIMIT 3600 The maximum TIME that an analysis can EXECUTE ACCEPT_PLANS TRUE TRUE IF SQL PLAN baselines should be accepted BY the task, FALSE otherwise ALTERNATE_PLAN_BASELINE AUTO ALTERNATE_PLAN_LIMIT UNLIMITED ALTERNATE_PLAN_SOURCE AUTO 11 ROWS selected. |
When the Automatic SPM task has last executed:
SQL> SELECT * FROM dba_autotask_schedule_control WHERE dbid = SYS_CONTEXT('userenv','con_dbid') AND task_name = 'Auto SPM Task'; DBID TASK_ID TASK_NAME STATUS INTERVAL MAX_RUN_TIME ENABL ELAPSED_TIME ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ------------ ----- ------------ LAST_SCHEDULE_TIME --------------------------------------------------------------------------- 2134414583 6 Auto SPM Task SUCCEEDED 3600 1800 TRUE 0 05-JUL-24 02.21.04.829 PM +02:00 |
Automatic SQL Plan Management (ASPM) test case
We live in a peaceful world (so far)
The test table I am using is the same one I have used for SPM. I directly create an histogram to have the good explain plan doing an index range scan on my test query:
DROP TABLE test1; CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE users; DECLARE i NUMBER; nbrows NUMBER; BEGIN i:=1; nbrows:=50000; LOOP EXIT WHEN i>nbrows; IF (i=1) THEN INSERT INTO test1 VALUES(1,RPAD('A',49,'A')); ELSE INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A')); END IF; i:=i+1; END LOOP; COMMIT; END; / CREATE INDEX test1_idx_id ON test1(id) TABLESPACE users; EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 2', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); |
I set maximum statistics_level (if you have done multiple testing it could be good to flush the shared pool, on your test instance of course) for my session and execute my test query that we can easily find in the libray cache thanks to my comment::
SQL> ALTER SESSION SET statistics_level=ALL; SESSION altered. SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. SQL> SELECT /* Yannick */ * FROM test1 WHERE id=1; ID DESCR ---------- -------------------------------------------------- 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> SELECT sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE ------------- ------------ --------------- - - - - -------------------------------------------------------------------------------------------------------------------------------- 0qv83mgpj3g7z 0 1636184578 N N Y N |
Thanks to the created histogram the explain is doing the expected index range scan:
SQL> SET pages 1000 SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id=>'0qv83mgpj3g7z', cursor_child_no=>'0', format=> 'all adaptive allstats')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0qv83mgpj3g7z, child NUMBER 0 ------------------------------------- SELECT /* Yannick */ * FROM test1 WHERE id=1 PLAN hash VALUE: 1636184578 ---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME | A-ROWS | A-TIME | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | | | 2 (100)| | 2 |00:00:00.01 | 8 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 2 | 1 | 30 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 8 | |* 2 | INDEX RANGE SCAN | TEST1_IDX_ID | 2 | 1 | | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 6 | ---------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (IDENTIFIED BY operation id): ------------------------------------------------------------- 1 - SEL$1 / TEST1@SEL$1 2 - SEL$1 / TEST1@SEL$1 Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - ACCESS("ID"=1) COLUMN Projection Information (IDENTIFIED BY operation id): ----------------------------------------------------------- 1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,50] 2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22] 31 ROWS selected. |
After a an hour the task run for the first time and does absolutely nothing (as expected):
SQL> variable report clob SQL> EXEC :report:=DBMS_SPM.REPORT_AUTO_EVOLVE_TASK(TYPE => 'TEXT', LEVEL => 'ALL', section => 'ALL') PL/SQL PROCEDURE successfully completed. SQL> SET longchunksize 200 SQL> SET LONG 999999 SQL> print report REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : SYS_AUTO_SPM_EVOLVE_TASK Task Owner : SYS Description : Automatic SPM Evolve Task Execution Name : SYS_SPM_2024-07-05/15:21:28 Execution TYPE : SPM EVOLVE REPORT -------------------------------------------------------------------------------- Scope : COMPREHENSIVE Status : COMPLETED Started : 07/05/2024 15:21:29 Finished : 07/05/2024 15:21:29 LAST Updated : 07/05/2024 15:21:29 Global TIME Limit : 3600 Per-PLAN TIME Limit : UNUSED NUMBER OF Errors : 0 --------------------------------------------------------------------------------------------- REPORT -------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- NUMBER OF plans processed : 0 NUMBER OF findings : 0 NUMBER OF recommendations : 0 NUMBER OF errors : 0 --------------------------------------------------------------------------------------------- |
Who has messed up ?
For my test case let’s imagine someone has destroyed the histogram and you index range scan is now moving to a full table scan (FTS). I simulate this by:
SQL> EXEC dbms_stats.gather_table_stats(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); PL/SQL PROCEDURE successfully completed. |
If you re-execute the same query you can now see the FTS:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id=>'0qv83mgpj3g7z', cursor_child_no=>'0', format=> 'all adaptive allstats')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0qv83mgpj3g7z, child NUMBER 0 ------------------------------------- SELECT /* Yannick */ * FROM test1 WHERE id=1 PLAN hash VALUE: 4122059633 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME | A-ROWS | A-TIME | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | | | 136 (100)| | 2 |00:00:00.01 | 886 | |* 1 | TABLE ACCESS FULL| TEST1 | 2 | 25000 | 732K| 136 (0)| 00:00:01 | 2 |00:00:00.01 | 886 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (IDENTIFIED BY operation id): ------------------------------------------------------------- 1 - SEL$1 / TEST1@SEL$1 Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - filter("ID"=1) COLUMN Projection Information (IDENTIFIED BY operation id): ----------------------------------------------------------- 1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,50] 28 ROWS selected. |
Looking at Automatic SQL Tuning Sets (ASTS) we could have an idea where will be the source of information for Automatic SPM:
SQL> SELECT plan_hash_value, cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches, executions, optimizer_cost, TO_CHAR(plan_timestamp,'dd-mon-yyyy hh24:mi:ss') AS plan_timestamp FROM dba_sqlset_statements WHERE sqlset_name='SYS_AUTO_STS' AND sql_id='0qv83mgpj3g7z' ORDER BY plan_timestamp DESC; PLAN_HASH_VALUE CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES EXECUTIONS OPTIMIZER_COST PLAN_TIMESTAMP --------------- ---------- ----------- ---------- ------------- -------------- ---------- ---------- -------------- ----------------------------- 4122059633 9492 1333 0 0 3 6 3 136 05-jul-2024 15:31:03 1636184578 101784 921 0 0 1 2 1 2 05-jul-2024 14:27:19 |
Automatic SPM to the rescue
If you wait for the next execution of the Auto SPM task and generate the execution report this time you should see that the task has done something. How do you know if the AUTO SPM task has been executed recently or not ? Using this query:
SQL> col task_name FOR a25 SQL> SELECT * FROM dba_autotask_schedule_control WHERE task_name = 'Auto STS Capture Task'; DBID TASK_ID TASK_NAME STATUS INTERVAL MAX_RUN_TIME ENABL ELAPSED_TIME LAST_SCHEDULE_TIME ---------- ---------- ------------------------- ---------- ---------- ------------ ----- ------------ --------------------------------------------------------------------------- 2134414583 5 Auto STS Capture Task SUCCEEDED 900 900 TRUE 0 05-JUL-24 04.36.53.015 PM +02:00 |
Generate the report with:
SQL> EXEC :report:=DBMS_SPM.REPORT_AUTO_EVOLVE_TASK(TYPE => 'TEXT', LEVEL => 'ALL', section => 'ALL') PL/SQL PROCEDURE successfully completed. SQL> print report REPORT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : SYS_AUTO_SPM_EVOLVE_TASK Task Owner : SYS Description : Automatic SPM Evolve Task Execution Name : SYS_SPM_2024-07-05/16:21:48 Execution TYPE : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 07/05/2024 16:21:48 Finished : 07/05/2024 16:21:49 LAST Updated : 07/05/2024 16:21:49 Global TIME Limit : 3600 Per-PLAN TIME Limit : UNUSED NUMBER OF Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- NUMBER OF plans processed : 1 NUMBER OF findings : 2 NUMBER OF recommendations : 1 NUMBER OF errors : 0 --------------------------------------------------------------------------------------------- DETAILS SECTION --------------------------------------------------------------------------------------------- Object ID : 2 Test PLAN Name : SQL_PLAN_fv9ffqfgg4ya46c0c005c Base PLAN Name : Cost-based PLAN SQL Handle : SQL_eda5ceb39ef27944 Parsing SCHEMA : YJAQUIER Test PLAN Creator : YJAQUIER SQL Text : SELECT /* Yannick */ * FROM test1 WHERE id=1 Execution STATISTICS: ----------------------------- Base PLAN Test PLAN ---------------------------- ---------------------------- Elapsed TIME (s): .000913 .000023 CPU TIME (s): .000866 .000023 Buffer Gets: 442 3 Optimizer COST: 136 249 Disk Reads: 0 0 Direct Writes: 0 0 ROWS Processed: 1 1 Executions: 10 10 FINDINGS SECTION --------------------------------------------------------------------------------------------- Findings (2): ----------------------------- 1. The PLAN was verified IN 0.04200 seconds. It passed the benefit criterion because its verified performance was 147.24931 times better than that OF the baseline PLAN. 2. The PLAN was automatically accepted. Recommendation: ----------------------------- Consider accepting the PLAN. EXECUTE dbms_spm.accept_sql_plan_baseline(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', object_id => 2, task_owner => 'SYS'); EXPLAIN PLANS SECTION --------------------------------------------------------------------------------------------- Baseline PLAN ----------------------------- PLAN Id : 1 PLAN Hash VALUE : 4261572351 ------------------------------------------------------------------------ | Id | Operation | Name | ROWS | Bytes | COST | TIME | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 25000 | 750000 | 136 | 00:00:01 | | * 1 | TABLE ACCESS FULL | TEST1 | 25000 | 750000 | 136 | 00:00:01 | ------------------------------------------------------------------------ Predicate Information (IDENTIFIED BY operation id): ------------------------------------------ * 1 - filter("ID"=1) Hint Report (IDENTIFIED BY operation id / Query Block Name / Object Alias): Total hints FOR statement: 5 ------------------------------------------------------------------------------- 0 - STATEMENT - ALL_ROWS - DB_VERSION('19.1.0') - IGNORE_OPTIM_EMBEDDED_HINTS - OPTIMIZER_FEATURES_ENABLE('19.1.0') 1 - SEL$1 / TEST1@SEL$1 - FULL(@"SEL$1" "TEST1"@"SEL$1") Test PLAN ----------------------------- PLAN Id : 2 PLAN Hash VALUE : 1812725852 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST | TIME | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25000 | 750000 | 249 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | TEST1 | 25000 | 750000 | 249 | 00:00:01 | | * 2 | INDEX RANGE SCAN | TEST1_IDX_ID | 25000 | | 49 | 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): ------------------------------------------ * 2 - ACCESS("ID"=1) Hint Report (IDENTIFIED BY operation id / Query Block Name / Object Alias): Total hints FOR statement: 6 ------------------------------------------------------------------------------- 0 - STATEMENT - ALL_ROWS - DB_VERSION('19.1.0') - IGNORE_OPTIM_EMBEDDED_HINTS - OPTIMIZER_FEATURES_ENABLE('19.1.0') 1 - SEL$1 / TEST1@SEL$1 - BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TEST1"@"SEL$1") - INDEX_RS_ASC(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."ID")) --------------------------------------------------------------------------------------------- |
The report itself says a lot of things and we already this that a better plan (147.24931 times better) has been found and SQL Plan Baseline has been created. If we check the created SQL Plan Baselines we can see it:
SQL> col plan_name FOR a30 SQL> SELECT sql_handle, plan_name, origin, enabled, accepted, fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ------------------------------ ------------------------------ ----------------------------- --- --- --- SQL_eda5ceb39ef27944 SQL_PLAN_fv9ffqfgg4ya46c0c005c EVOLVE-LOAD-FROM-STS YES YES NO SQL_eda5ceb39ef27944 SQL_PLAN_fv9ffqfgg4ya4fe026eff AUTO-CAPTURE YES NO NO |
And in the library cache we see that our SQL id has a child number 2:
SQL> SELECT sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE I I I I SQL_PLAN_BASELINE ------------- ------------ --------------- - - - - -------------------------------------------------------------------------------------------------------------------------------- 0qv83mgpj3g7z 0 4122059633 N N Y N 0qv83mgpj3g7z 2 1636184578 N N Y N SQL_PLAN_fv9ffqfgg4ya46c0c005c |
When we display the explain plan for the second child:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id=>'0qv83mgpj3g7z', cursor_child_no=>'2', format=> 'all adaptive allstats')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0qv83mgpj3g7z, child NUMBER 2 ------------------------------------- SELECT /* Yannick */ * FROM test1 WHERE id=1 PLAN hash VALUE: 1636184578 ---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME | A-ROWS | A-TIME | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 249 (100)| | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 1 | 25000 | 732K| 249 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | TEST1_IDX_ID | 1 | 25000 | | 49 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | ---------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (IDENTIFIED BY operation id): ------------------------------------------------------------- 1 - SEL$1 / TEST1@SEL$1 2 - SEL$1 / TEST1@SEL$1 Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - ACCESS("ID"=1) COLUMN Projection Information (IDENTIFIED BY operation id): ----------------------------------------------------------- 1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,50] 2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22] Hint Report (IDENTIFIED BY operation id / Query Block Name / Object Alias): Total hints FOR statement: 6 --------------------------------------------------------------------------- 0 - STATEMENT - ALL_ROWS - DB_VERSION('19.1.0') - IGNORE_OPTIM_EMBEDDED_HINTS - OPTIMIZER_FEATURES_ENABLE('19.1.0') 1 - SEL$1 / TEST1@SEL$1 - BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TEST1"@"SEL$1") - INDEX_RS_ASC(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."ID")) Note ----- - SQL PLAN baseline SQL_PLAN_fv9ffqfgg4ya46c0c005c used FOR this statement 49 ROWS selected. |
We can see that the SQL Plan Baseline is used and that the query has been automatically repaired to an index range scan without the creation of an histogram:
SQL> col table_name FOR a30 SQL> col column_name FOR a30 SQL> SELECT table_name, column_name, num_distinct, num_buckets, histogram FROM DBA_TAB_COL_STATISTICS WHERE owner='YJAQUIER' AND table_name='TEST1'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------------------------ ------------------------------ ------------ ----------- --------------- TEST1 ID 2 1 NONE |
Huh magical you would say ?
References
- What is automatic SQL plan management and why should you care?
- SQL Plan Management Cheat Sheet – Part 1
- SQL Plan Management Cheat Sheet – Part 2
