How to improve a previously run query on an Oracle database (SQL Tuning Advisor with AWR Snapshots)
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
In previous article we had analyzed how to improve a query in Oracle with its use SQL Tuning Advisor. But in order to run the SQL Tuning Advisor, the query should still be in the Shared Pool that is, in memory (RAM). However, if the query was executed before, it will no longer exist in memory. In this article we will see how we can run the SQL Tuning Advisor again by drawing information from the AWR Snapshots.
We should remember that the SQL Tuning Advisor during its execution it collects information from various sources (as we will see in the image below) and at the end makes suggestions to improve the performance of the query.
The recommendations he suggests we make might be:
- To get statistics on specific tables as they are stale
- Let's create new indexes
- Let's change the way the query is written
- Use a SQL profile to force a different plan for the query
To use the SQL Tuning Advisor we must have Enterprise version of Oracle or have purchased it Tuning Pack
The example
First we have to find it SQL_ID from the query we want to see. If we don't have it SQL_ID in this article you can see how to find it easily through the dynamic view GV$AACTIVE_SESSION_HISTORY.
After we found it SQL_ID with the following query we will see the various plans that may have changed over time.
SELECT SQL_ID, PLAN_HASH_VALUE, TIMESTAMP FROM DBA_HIST_SQL_PLAN WHERE SQL_ID='93uy7ndb6uf2z';
What we care about now is keeping them snap_id from the snapshots we want to study the query.
select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='93uy7ndb6uf2z' --and plan_hash_value='3448248699' order by snap_id desc;
As a next step we should create the tuning task completing it begin_snap/end_snap (time limits he had run), the sql_id and give the task a name to task_name.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 71200,
end_snap => 71201,
sql_id => '93uy7ndb6uf2z',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '93uy7ndb6uf2z_AWR_tuning_task',
description => 'Tuning task for statement 93uy7ndb6uf2z in AWR');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
Then we run the tuning task we created before:
exec dbms_sqltune.execute_tuning_task (TASK_NAME => '93uy7ndb6uf2z_AWR_tuning_task');
After it is completed, we see the results from the analysis with the following select query:
SET LONG 10000;
SET PAGESIZE 1000;
SET LINESIZE 200;
SELECT DBMS_SQLTUNE.report_tuning_task('93uy7ndb6uf2z_AWR_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24;
In the results we will see in three different sections.
The General Section which contains the SQL text.
The Finding Sections which contains the sentences he found such as:
- Statistics Finding: Creating statistics in one stale table that the query reads
- Index Finding: Which suggests the creation index in the field that was in the where clause
- SQL Profile Finding: Suggests to enable it SQL Profile that he created that makes use of a better plan
End of Explain Plan Section it analyzes the 2 different plans, one that has run the query and the other that it recommends.
| OTHER_AFM | 12
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 93uy7ndb6uf2z_AWR_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 01/01/2021 17:22:25
Completed at : 01/01/2021 17:22:54
-------------------------------------------------------------------------------
Schema Name: USERS
SQL ID : 5t5mxfm0ph3n0
SQL Text : SELECT TR.NAME,
TR.AGE,
TR.CITY,
TR.SIGN_DATE
FROM USERS.CUSTOMERS TR
WHERE
TR.SIGN_DATE = TO_DATE('30/12/2020','DD/MM/YYYY')
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "USERS"."CUSTOMERS" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'USERS', tabname =>
'CUSTOMERS', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index USERS.IDX$$_17C0B0001 on
USERS.CUSTOMERS("SIGN_DATE");
3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 68.27%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'SQL_TUNNING_TASK_5t5mxfm0ph3n0', task_owner => 'SYS', replace =>
TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 321555184
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 330 | 19 (0)| 0
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 6 | 330 | 19 (0)| 0
| 3 | PX BLOCK ITERATOR | | 6 | 330 | 19 (0)| 0
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 6 | 330 | 19 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("TR"."SIGN_DATE"=TO_DATE(' 2020-12-30 00:00:00', 'syyyy-mm-dd')
2- Using SQL Profile
--------------------
Plan hash value: 3378393080
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 330
| 1 | PARTITION RANGE SINGLE | | 6 | 330
| 2 | INLIST ITERATOR | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| CUSTOMERS | 6 | 330
|* 4 | INDEX RANGE SCAN | INX_CUSTOMERS_1 | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TR"."SIGN_DATE"=TO_DATE(' 2020-12-30 00:00:00', 'syyyy-mm-dd')
-------------------------------------------------------------------------------
PL/SQL procedure successfully completedFinally, to delete the task or see what state it is in, execute the following:
--SELECT task_name, status FROM dba_advisor_log WHERE task_name = '93uy7ndb6uf2z_AWR_tuning_task';
execute dbms_sqltune.drop_tuning_task('93uy7ndb6uf2z_AWR_tuning_task');
