How to improve the performance of a query running on an Oracle database (SQL Tuning Advisor)
The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.
Certifications:
Certifications:
Latest posts by Stratos Matzouranis (see all)
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- 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
Many times we will happen to hear that suddenly a query that was going fast suddenly started to slow down or that a query goes unreasonably very slowly.
The easiest way to find what's wrong and fix it is by running it SQL Tuning Advisor of Oracle.
During its execution for the specific query, 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 you will find it easily.
Then we have to fix it tuning task completing it SQL_ID and giving one TASK_NAME.
declare task_nm varchar2(100); begin task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '5t5mxfm0ph3n0',TASK_NAME => 'SQL_TUNNING_TASK_5t5mxfm0ph3n0'); end; /
Then we run the tuning task we created before.
exec dbms_sqltune.execute_tuning_task (TASK_NAME => 'SQL_TUNNING_TASK_5t5mxfm0ph3n0');
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('SQL_TUNNING_TASK_5t5mxfm0ph3n0') 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 two different plans, one that has run the query and the other that it suggests.
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SQL_TUNNING_TASK_5t5mxfm0ph3n0
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
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 completed
Finally, 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 = 'SQL_TUNNING_TASK_5t5mxfm0ph3n0';
execute dbms_sqltune.drop_tuning_task('SQL_TUNNING_TASK_5t5mxfm0ph3n0');
