Table of contents
Preamble
A long time back I have worked on a very interesting, under rated in my opinion, feature called SQL Plan Management (SPM) that’s for once is included in the Oracle Enterprise Edition option.
Oracle enhanced this feature to make it automatic and self evolve queries to make them moving to better execution plans automatically !! Unfortunately this feature is reserved for autonomous system (Oracle Exadata Database Machine or Oracle Database Appliance and obviously Autonomous offering on OCI):
But on what Oracle did not communicate much is on the backend layer required to make this functionality working called Automatic SQL Tuning Sets (ASTS) that has been released in 19c RU 7. There was few blog posts about it but nothing in the official Database New Features Guide about the new features of each Release Update:
ASTS has been released and activated by default in 19c RU 7 and deactivated in 19c RU 8. As Nigel Bayliss is saying it will occupy a bit of space in your SYSAUX tablespace but the added value in return is just huge ! The crucial question ASTS can help you to answer is a classical one similar to “my query used to run fast and is now suddenly running in hours”.
I am recycling the example I have used in my SPM article and I will try to re instantiate the initial bad plan (Full Table Scan) to see if I can get some help from ASTS with and without SPM.
Testing has been done using Oracle Database 19c Enterprise Edition Release 19.17 running on a Red Hat Enterprise Linux release 8.6 (Ootpa) server with 8 cores.
ASTS activation
If, like me, you are 19c RU 8 or above then the task should not be enabled:
SQL> SET lines 200 SQL> col task_name FOR a22 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 ---------- ---------- ---------------------- ---------- ---------- ------------ ----- ------------ --------------------------------------------------------------------------- 2376385350 5 Auto STS Capture Task SUCCEEDED 900 900 FALSE 0 15-APR-23 10.56.01.367 PM +02:00 |
Activate it with (I have not found on how to change the interval and maximum run time):
SQL> EXEC dbms_auto_task_admin.enable(client_name => 'Auto STS Capture Task', operation => NULL, window_name => NULL); PL/SQL PROCEDURE successfully completed. 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 ---------- ---------- ---------------------- ---------- ---------- ------------ ----- ------------ --------------------------------------------------------------------------- 2376385350 5 Auto STS Capture Task SUCCEEDED 900 900 TRUE 2 10-NOV-23 02.29.16.137 PM +01:00 |
Remark
In 21c the ENABLED column has gone so you have to check the LAST_SCHEDULE_TIME column to understand if the schedule is active or not… Quite strange…
As shared in the MOS note you can have more statistics on the task job using:
SQL> WITH dsjrd AS ( SELECT (TO_DATE('1','j')+run_duration-TO_DATE('1','j'))* 86400 duration_sec, (TO_DATE('1','j')+cpu_used-TO_DATE('1','j'))* 86400 cpu_used_sec FROM dba_scheduler_job_run_details WHERE job_name = 'ORA$_ATSK_AUTOSTS' ) SELECT MIN(duration_sec) ASTS_Min_Time_Sec, MAX(duration_sec) ASTS_Max_Time_Sec, AVG(duration_sec) ASTS_Average_Time_Sec, AVG(cpu_used_sec) ASTS_Average_CPU_Sec FROM dsjrd; ASTS_MIN_TIME_SEC ASTS_MAX_TIME_SEC ASTS_AVERAGE_TIME_SEC ASTS_AVERAGE_CPU_SEC ----------------- ----------------- --------------------- -------------------- 0 16 3.79347826 2.27173913 |
The ASTS job will store all the execution plans of the queries running on your databse in a STS. Let the task run for a while (ideally for few days) and you should start to see a bunch a SQL statements in the SYS_AUTO_STS SQL Tuning Set (STS):
SQL> col name FOR a15 SQL> col description FOR a30 SQL> col owner FOR a10 SQL> SELECT name, owner, description, created, last_modified, statement_count FROM dba_sqlset WHERE name='SYS_AUTO_STS'; NAME OWNER DESCRIPTION CREATED LAST_MODIFIED STATEMENT_COUNT --------------- ---------- ------------------------------ -------------------- -------------------- --------------- SYS_AUTO_STS SYS SYSTEM auto SQL Tuning SET 15-APR-2023 21:15:47 10-NOV-2023 15:14:27 18117 |
Currently I have only one ASTS STS in my test database:
SQL> SELECT DECODE(sqlset_name,'SYS_AUTO_STS','ASTS','NON-ASTS') sqlsets, COUNT(*) COUNT FROM dba_sqlset_statements GROUP BY DECODE(sqlset_name,'SYS_AUTO_STS','ASTS','NON-ASTS'); SQLSETS COUNT -------- ---------- ASTS 19343 |
The diffence is explained by the multiple execution plan a single query (sql_id) might have and so exactly what we would like to understand and why suddently a query execution time might diverge drastically…
If you want to restart fresh and remove ASTS you can still drop it with:
EXEC dbms_sqlset.drop_sqlset(sqlset_name => 'SYS_AUTO_STS', sqlset_owner => 'SYS'); |
In the MOS note you have a query to tell you how much space it takes in your SYSAUX tablesapce. I have almost 35,000 statements (it evolved since above statements) in ASTS for less than half a gigabyte:
SQL> col table_name FOR a30 SQL> SELECT table_name, ROUND(SUM(size_b) / 1024 / 1024, 3) AS table_size_mb, ROUND(MAX(total_size_b) / 1024 / 1024, 3) AS total_size_mb FROM ( SELECT table_name, size_b, SUM(size_b) OVER() AS total_size_b 2 FROM ( SELECT segment_name AS table_name, bytes AS size_b FROM dba_segments WHERE segment_name NOT LIKE '%WORKSPA%' AND owner = 'SYS' AND (segment_name LIKE 'WRI 3 %SQLSET%' OR segment_name LIKE 'WRH$_SQLTEXT') UNION ALL SELECT t.table_name, bytes AS size_b FROM dba_segments s, (SELECT table_name, segm 4 ent_name FROM dba_lobs WHERE table_name IN ('WRI$_SQLSET_PLAN_LINES', 'WRH$_SQLTEXT') AND owner = 'SYS' ) t WHERE s.segment_name = t.SEGMENT 5 _name ) ) GROUP BY table_name ORDER BY table_size_mb DESC; TABLE_NAME TABLE_SIZE_MB TOTAL_SIZE_MB ------------------------------ ------------- ------------- WRI$_SQLSET_PLAN_LINES 266.188 460.563 WRH$_SQLTEXT 143.188 460.563 WRI$_SQLSET_PLAN_LINES_PK 22 460.563 WRI$_SQLSET_PLANS 16 460.563 WRI$_SQLSET_STATISTICS 5 460.563 WRI$_SQLSET_STATEMENTS 4 460.563 WRI$_SQLSET_PLANS_PK 2 460.563 WRI$_SQLSET_STATISTICS_PK 2 460.563 WRI$_SQLSET_MASK_PK 2 460.563 WRI$_SQLSET_STATEMENTS_IDX_02 2 460.563 WRI$_SQLSET_STATEMENTS_IDX_01 2 460.563 WRI$_SQLSET_MASK .938 460.563 WRI$_SQLSET_STATEMENTS_PK .813 460.563 WRI$_SQLSET_BINDS_PK .063 460.563 WRI$_SQLSET_REFERENCES .063 460.563 WRI$_SQLSET_DEFINITIONS_IDX_01 .063 460.563 WRI$_SQLSET_REFERENCES_PK .063 460.563 WRI$_SQLSET_DEFINITIONS .063 460.563 WRI$_SQLSET_BINDS .063 460.563 WRI$_SQLSET_DEFINITIONS_PK .063 460.563 20 ROWS selected. |
ASTS test case
Set maximum statistics level for your session to avoid boring warnings as well as few display variables for execution plans:
SQL> SET lines 200 SQL> SET pages 1000 SQL> ALTER SESSION SET statistics_level=ALL; SESSION altered. |
Same as for SPM testing I create below test table:
DROP TABLE test01; CREATE TABLE test01(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 test01 VALUES(1,RPAD('A',49,'A')); ELSE INSERT INTO test01 VALUES(nbrows,RPAD('A',49,'A')); END IF; i:=i+1; END LOOP; COMMIT; END; / CREATE INDEX test01_idx_id ON test01(id) TABLESPACE users; |
SQL> EXEC dbms_stats.gather_table_stats(ownname=>'yjaquier', tabname=>'test01', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); PL/SQL PROCEDURE successfully completed. |
At first gather statistics Oracle has not created any histograms on ID columns:
SQL> col table_name FOR a15 SQL> col column_name FOR a15 SQL> SELECT table_name, column_name, num_distinct, num_buckets, histogram FROM dba_tab_col_statistics WHERE owner='YJAQUIER' AND table_name='TEST01'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM --------------- --------------- ------------ ----------- --------------- TEST01 ID 2 1 NONE |
So when selecting the single row where id is equal to 1:
SQL> SELECT /* Yannick */ * FROM test01 WHERE id=1; ID DESCR ---------- -------------------------------------------------- 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA |
SQL> SELECT sql_id, child_number, plan_hash_value FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ --------------- cq1jj9nxhkaat 0 262542483 |
As we have seen in the SPM blog post, we have no histograms and so Oracle is not using the index and is doing a full table scan (FTS):
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => 'cq1jj9nxhkaat', cursor_child_no => 0, format => 'allstats')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cq1jj9nxhkaat, child NUMBER 0 ------------------------------------- SELECT /* Yannick */ * FROM test01 WHERE id=1 PLAN hash VALUE: 262542483 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS | A-ROWS | A-TIME | Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 443 | |* 1 | TABLE ACCESS FULL| TEST01 | 1 | 25000 | 1 |00:00:00.01 | 443 | -------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - filter("ID"=1) 18 ROWS selected. |
To be able to re-apply those bad statistics I create a statistics table and export these statistics into it with:
SQL> EXEC dbms_stats.create_stat_table(ownname => 'yjaquier', stattab => 'my_statistics_table'); PL/SQL PROCEDURE successfully completed. SQL> EXEC dbms_stats.export_table_stats(ownname=>'yjaquier', tabname=>'test01', stattab=>'my_statistics_table'); PL/SQL PROCEDURE successfully completed. |
Let’s re-gather the statistics with the no_invalidate parameter to have the cursor marked for immediate invalidation and see that Oracle is learning from its mistakes:
SQL> EXEC dbms_stats.gather_table_stats(ownname=>'yjaquier', tabname=>'test01', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE, no_invalidate=>FALSE); PL/SQL PROCEDURE successfully completed. |
An histogram has been created:
SQL> SELECT table_name, column_name, num_distinct, num_buckets, histogram FROM dba_tab_col_statistics WHERE owner='YJAQUIER' AND table_name='TEST01'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM --------------- --------------- ------------ ----------- --------------- TEST01 ID 2 2 FREQUENCY |
The plan hash value has changed:
SQL> SELECT /* Yannick */ * FROM test01 WHERE id=1; ID DESCR ---------- -------------------------------------------------- 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> SELECT sql_id,child_number,plan_hash_value FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ --------------- cq1jj9nxhkaat 0 4138272685 |
And we now do an indexx range scan:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => 'cq1jj9nxhkaat', cursor_child_no => 0, format => 'allstats')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cq1jj9nxhkaat, child NUMBER 0 ------------------------------------- SELECT /* Yannick */ * FROM test01 WHERE id=1 PLAN hash VALUE: 4138272685 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS | A-ROWS | A-TIME | Buffers | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | TEST01_IDX_ID | 1 | 1 | 1 |00:00:00.01 | 3 | --------------------------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - ACCESS("ID"=1) 19 ROWS selected. |
After a short period you should be able to see your statement and its multiple execution plan in ASTS:
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='cq1jj9nxhkaat' ORDER BY plan_timestamp DESC; PLAN_HASH_VALUE CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES EXECUTIONS OPTIMIZER_COST PLAN_TIMESTAMP --------------- ---------- ----------- ---------- ------------- -------------- ---------- ---------- -------------- -------------------- 4138272685 2010 34 7 0 3 6 3 2 14-nov-2023 12:23:31 262542483 16067 513 11 0 1 2 1 161 14-nov-2023 12:01:44 |
ASTS testing
Even if not super user firendly, particularly if you have a multi page query and so, a multi page execution plan. You can use ASTS in a standalone fashion by just checking how was your query execution plan in the past. By chronological order we see the FTS followed by the index range scan. To have extra statsitics I would need to have statistics_level set to all at database level which is not recommened on a production database:
SQL> SELECT * FROM TABLE(dbms_xplan.display_sqlset(sqlset_name => 'SYS_AUTO_STS', sql_id => 'cq1jj9nxhkaat', plan_hash_value => '262542483', format => 'allstats', sqlset_owner => 'SYS')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- SQL Tuning SET Name: SYS_AUTO_STS SQL Tuning SET Owner: SYS SQL_ID: cq1jj9nxhkaat SQL Text: SELECT /* Yannick */ * FROM test01 WHERE id=1 -------------------------------------------------------------------------------- PLAN hash VALUE: 262542483 --------------------------------------------- | Id | Operation | Name | E-ROWS | --------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | TABLE ACCESS FULL| TEST01 | 25000 | --------------------------------------------- Note ----- - Warning: basic PLAN STATISTICS NOT available. These are only collected WHEN: * hint 'gather_plan_statistics' IS used FOR the statement OR * parameter 'statistics_level' IS SET TO 'ALL', AT SESSION OR SYSTEM LEVEL 21 ROWS selected. SQL> SELECT * FROM TABLE(dbms_xplan.display_sqlset(sqlset_name => 'SYS_AUTO_STS', sql_id => 'cq1jj9nxhkaat', plan_hash_value => '4138272685', format => 'allstats', sqlset_owner => 'SYS')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL Tuning SET Name: SYS_AUTO_STS SQL Tuning SET Owner: SYS SQL_ID: cq1jj9nxhkaat SQL Text: SELECT /* Yannick */ * FROM test01 WHERE id=1 -------------------------------------------------------------------------------- PLAN hash VALUE: 4138272685 ---------------------------------------------------------------------- | Id | Operation | Name | E-ROWS | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 1 | | 2 | INDEX RANGE SCAN | TEST01_IDX_ID | 1 | ---------------------------------------------------------------------- Note ----- - Warning: basic PLAN STATISTICS NOT available. These are only collected WHEN: * hint 'gather_plan_statistics' IS used FOR the statement OR * parameter 'statistics_level' IS SET TO 'ALL', AT SESSION OR SYSTEM LEVEL 22 ROWS selected. |
SPM and ASTS testing
To delete pre-existing SQL Plan baselines and their associated SQL plan and start fresh use the code from Connor (see refrences section):
SET serveroutput ON SIZE 999999 DECLARE nb_plans PLS_INTEGER; l_cnt PLS_INTEGER; BEGIN nb_plans:=0; FOR i IN ( SELECT DISTINCT plan_name FROM dba_sql_plan_baselines ) LOOP l_cnt := dbms_spm.drop_sql_plan_baseline(sql_handle => NULL, plan_name => i.plan_name); nb_plans:=nb_plans + l_cnt; END LOOP; dbms_output.put_line('Number of SQL Plan Baselines deleted: ' || nb_plans); END; / |
When my test query is using the good explain plan with the index I simulate a query deviation by importing the statistics I eexporting when the explain was doing a FTS. As expected we are back to the original situation::
SQL> EXEC dbms_stats.import_table_stats(ownname => 'yjaquier', tabname => 'test01', stattab => 'my_statistics_table', no_invalidate => FALSE); PL/SQL PROCEDURE successfully completed. SQL> SELECT /* Yannick */ * FROM test01 WHERE id=1; ID DESCR ---------- -------------------------------------------------- 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> SELECT sql_id, child_number, plan_hash_value FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ --------------- cq1jj9nxhkaat 0 262542483 SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => 'cq1jj9nxhkaat', cursor_child_no => 0, format => 'allstats')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cq1jj9nxhkaat, child NUMBER 0 ------------------------------------- SELECT /* Yannick */ * FROM test01 WHERE id=1 PLAN hash VALUE: 262542483 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS | A-ROWS | A-TIME | Buffers | Reads | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.09 | 443 | 439 | |* 1 | TABLE ACCESS FULL| TEST01 | 1 | 25000 | 1 |00:00:00.09 | 443 | 439 | ----------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - filter("ID"=1) 18 ROWS selected. |
Load the plan from cursor cache in SPM with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function. The enabled parameter set to NO is important. If you keep the default YES value you will have a weird behavior and be obliged to play with DBMS_SPM.ALTER_SQL_PLAN_BASELINE to disabled the loaded plan even when you have evolved a better good one with SPM…:
SQL> SET serveroutput ON SIZE 999999 SQL> DECLARE nb_plans INTEGER; BEGIN nb_plans:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'cq1jj9nxhkaat', plan_hash_value=> '262542483', enabled => 'no'); dbms_output.put_line('SQL Plan Baseline created with ' || nb_plans || ' plans.'); END; / PL/SQL PROCEDURE successfully completed. |
Then it’s not super user firendly but you have to find your sql_handle in DBA_SQL_PLAN_BASELINES table with something like:
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_fc60933c5f632809 SQL_PLAN_gss4m7jgq6a0904c70b35 MANUAL-LOAD-FROM-CURSOR-CACHE NO YES NO |
Remark:
Notice the NO in ENABLED column set by the enabled => ‘no’ parameter of DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function call (or it would be YES).
No let’s create a SPM task that will only look into ASTS. Here in my small example ASTS is the ONLY possible source to find a candidate for a better plan. My query is too small to be found in AWR on my busy test database:
SQL> SELECT * FROM TABLE(dbms_xplan.display_workload_repository(sql_id => 'cq1jj9nxhkaat')); no ROWS selected |
And using DBMS_XPLAN.DISPLAY_CURSOR would be of no help as the one in cursor cache is the one with bad plan we are trying to solve…
So let’s see what SPM can do. I create and execute a task that will look in ASTS:
SQL> SET serveroutput ON SIZE 999999 SQL> DECLARE task_name VARCHAR2(50); execution_name VARCHAR2(50); BEGIN task_name := dbms_spm.create_evolve_task(sql_handle => 'SQL_fc60933c5f632809', time_limit => DBMS_SPM.NO_LIMIT, description => 'SPM for sql_id cq1jj9nxhkaat'); dbms_output.put_line('SPM evolve tak name ' || task_name || ' created.'); dbms_spm.set_evolve_task_parameter(task_name => task_name, parameter => 'alternate_plan_source', VALUE => 'SQL_TUNING_SET'); execution_name := dbms_spm.execute_evolve_task(task_name => task_name); dbms_output.put_line('Execution name ' || execution_name); END; / SPM evolve tak name TASK_103080 created. Execution name EXEC_104137 PL/SQL PROCEDURE successfully completed. |
Display the result of the task with:
SQL> var output clob; SQL> EXEC :output:=dbms_spm.report_evolve_task(task_name => 'TASK_103052', execution_name => 'EXEC_104099'); SQL> SET LONG 999999 SQL> SET longchunksize 200 SQL> print :output OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : TASK_103080 Task Owner : YJAQUIER Description : SPM FOR sql_id cq1jj9nxhkaat Execution Name : EXEC_104137 Execution TYPE : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 11/16/2023 12:14:47 Finished : 11/16/2023 12:14:50 LAST Updated : 11/16/2023 12:14:50 Global TIME Limit : 2147483647 Per-PLAN TIME Limit : UNUSED NUMBER OF Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- NUMBER OF plans processed : 1 NUMBER OF findings : 1 NUMBER OF recommendations : 1 NUMBER OF errors : 0 --------------------------------------------------------------------------------------------- DETAILS SECTION --------------------------------------------------------------------------------------------- Object ID : 2 Test PLAN Name : SQL_PLAN_gss4m7jgq6a09244e48e4 Base PLAN Name : Cost-based PLAN SQL Handle : SQL_fc60933c5f632809 Parsing SCHEMA : YJAQUIER Test PLAN Creator : SYS SQL Text : SELECT /* Yannick */ * FROM test01 WHERE id=1 Execution STATISTICS: ----------------------------- Base PLAN Test PLAN ---------------------------- ---------------------------- Elapsed TIME (s): .000727 .000006 CPU TIME (s): .000765 .000006 Buffer Gets: 442 3 Optimizer COST: 161 250 Disk Reads: 0 0 Direct Writes: 0 0 ROWS Processed: 1 1 Executions: 10 10 FINDINGS SECTION --------------------------------------------------------------------------------------------- Findings (1): ----------------------------- 1. The PLAN was verified IN 0.01600 seconds. It passed the benefit criterion because its verified performance was 147.32937 times better than that OF the baseline PLAN. Recommendation: ----------------------------- Consider accepting the PLAN. EXECUTE dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_103080', object_id => 2, task_owner => 'YJAQUIER'); EXPLAIN PLANS SECTION --------------------------------------------------------------------------------------------- Baseline PLAN ----------------------------- PLAN Id : 3408 PLAN Hash VALUE : 80153397 -------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST | TIME | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25000 | 1325000 | 161 | 00:00:01 | | * 1 | TABLE ACCESS FULL | TEST01 | 25000 | 1325000 | 161 | 00:00:01 | -------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): ------------------------------------------ * 1 - filter("ID"=1) Test PLAN ----------------------------- PLAN Id : 3409 PLAN Hash VALUE : 609110244 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST | TIME | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25000 | 1325000 | 250 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | TEST01 | 25000 | 1325000 | 250 | 00:00:01 | | * 2 | INDEX RANGE SCAN | TEST01_IDX_ID | 25000 | | 49 | 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): ------------------------------------------ * 2 - ACCESS("ID"=1) --------------------------------------------------------------------------------------------- |
Implement the recommendation of the task with (you can also use the command that is suggested in the report):
SQL> SET serveroutput ON SIZE 999999 SQL> DECLARE nb_plans INTEGER; BEGIN nb_plans:=dbms_spm.implement_evolve_task(task_name => 'TASK_103052'); dbms_output.put_line('Number of plan accepted: ' || nb_plans); END; / NUMBER OF PLAN accepted: 1 PL/SQL PROCEDURE successfully completed. |
Let’s check how the query behave:
SQL> SELECT /* Yannick */ * FROM test01 WHERE id=1; ID DESCR ---------- -------------------------------------------------- 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> SELECT sql_id, child_number, plan_hash_value FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ --------------- cq1jj9nxhkaat 1 4138272685 SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => 'cq1jj9nxhkaat', cursor_child_no => 1, format => 'allstats')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cq1jj9nxhkaat, child NUMBER 1 ------------------------------------- SELECT /* Yannick */ * FROM test01 WHERE id=1 PLAN hash VALUE: 4138272685 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS | A-ROWS | A-TIME | Buffers | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 1 | 25000 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | TEST01_IDX_ID | 1 | 25000 | 1 |00:00:00.01 | 3 | --------------------------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - ACCESS("ID"=1) Note ----- - SQL PLAN baseline SQL_PLAN_gss4m7jgq6a09244e48e4 used FOR this statement 23 ROWS selected. 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_fc60933c5f632809 SQL_PLAN_gss4m7jgq6a0904c70b35 MANUAL-LOAD-FROM-CURSOR-CACHE NO YES NO SQL_fc60933c5f632809 SQL_PLAN_gss4m7jgq6a09244e48e4 EVOLVE-LOAD-FROM-STS YES YES NO |
Remark:
Notice the note in the explain pan stating that the SQL plan baseline is used…
The index is used back again even if we still have no histogram on ID column:
SQL> SELECT table_name, column_name, num_distinct, num_buckets, histogram FROM dba_tab_col_statistics WHERE owner='YJAQUIER' AND table_name='TEST01'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM --------------- --------------- ------------ ----------- --------------- TEST01 ID 2 1 NONE |
If you have not invalidated by default the SQL Plan Baseline when manually loading it from cusor cache (enabled => ‘no’) then it will still be used by Oracle optimizer even when you have evolve the SPM task… The only option is to use DBMS_SPM.ALTER_SQL_PLAN_BASELINE and mark the original SQL Plan Baseline as disabled to make optimzer use the one you have enabled:
SQL> SET serveroutput ON SIZE 999999 SQL> DECLARE nb_plans INTEGER; BEGIN nb_plans:=dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_fc60933c5f632809', plan_name => 'SQL_PLAN_gss4m7jgq6a0904c70b35', attribute_name => 'enabled', attribute_value => 'no'); dbms_output.put_line('Number of plan changed: ' || nb_plans); END; / PL/SQL PROCEDURE successfully completed. 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_fc60933c5f632809 SQL_PLAN_gss4m7jgq6a0904c70b35 MANUAL-LOAD-FROM-CURSOR-CACHE NO YES NO SQL_fc60933c5f632809 SQL_PLAN_gss4m7jgq6a09244e48e4 EVOLVE-LOAD-FROM-STS YES YES NO SQL> SELECT /* Yannick */ * FROM test01 WHERE id=1; ID DESCR ---------- -------------------------------------------------- 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> SELECT sql_id, child_number, plan_hash_value FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ --------------- cq1jj9nxhkaat 1 4138272685 SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => 'cq1jj9nxhkaat', cursor_child_no => 1, format => 'allstats')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cq1jj9nxhkaat, child NUMBER 1 ------------------------------------- SELECT /* Yannick */ * FROM test01 WHERE id=1 PLAN hash VALUE: 4138272685 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS | A-ROWS | A-TIME | Buffers | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 1 | 25000 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | TEST01_IDX_ID | 1 | 25000 | 1 |00:00:00.01 | 3 | --------------------------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - ACCESS("ID"=1) Note ----- - SQL PLAN baseline SQL_PLAN_gss4m7jgq6a09244e48e4 used FOR this statement 23 ROWS selected. |
Remark:
The cool added value of DBMS_SPM.ALTER_SQL_PLAN_BASELINE is that you can ping-pong between the SQL Plan Baselines and choose the one you would like to use per sql_id… Flexibility you said ?
References
- Automatic SQL Tuning Sets (ASTS) 19c RU 19.7 Onwards (Doc ID 2686869.1)
- What is the Automatic SQL Tuning Set?
- Repairing SQL Performance Regression with SQL Plan Management
- Drop all baselines
- What is automatic SQL plan management and why should you care?
- Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0
