• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/Oracle Database/Oracle Tuning/Analyze SQL with SQL Tuning Advisor
Popular Search:Oracle, SQL Server, MongoDB

Analyze SQL with SQL Tuning Advisor

205 views 0 September 12, 2019 admin

SQL TUNING ADVISOR

  • The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.
  • The output of the SQL Tuning Advisor is in the form of an recommendations, along with a rationale for each recommendation and its expected benefit.The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.
  • You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.
  • Find the problematic SQL_ID from v$session you would like to analyze. Usually the AWR has the top SQL_IDs column.

In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:

sqlplus / as sysdba
GRANT ADVISOR TO DBORG;
CONN DBORG/oracle;
Steps to tune the problematic SQL_ID using SQL TUNING ADVISOR :-

Create Tuning Task :

DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '93x11xxhxy1K6',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/

Execute Tuning task :

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/

Monitor the task executing using below query:

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ='my_sql_tuning_task_1';

TASK_NAME                      STATUS
------------------------------ -----------
my_sql_tuning_task_1           COMPLETED

Check the status is completed for the task and we can get recommendations of the advisor.

Report Tuning task :

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1')

-----------------------------------------------------------------------
GENERAL INFORMATION SECTION
-----------------------------------------------------------------------

Tuning Task Name                  : my_sql_tuning_task_1
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 11/10/2018 19:47:27
Completed at                      : 11/10/2018 19:47:54
--------------------------------------------------------------------
SQL_ID : 93x11xxhxy1K6
SQL_staement : SELECT * FROM DBORG.EMP

Number of SQL Profile Findings    : 1
--------------------------------------------------------------------
FINDINGS SECTION (1 finding)

--------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

--------------------------------------------------------

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.94%)

  ------------------------------------------

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_1',replace => TRUE);

To get detailed information :

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;

Drop SQL Tuning task :

BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'my_sql_tuning_task_1');
END;
/
Another method for adding new task using SQL TUNING ADVISOR :-
Check the PLAN_HASH_VALUE got changed for the specific statement and get SNAP_ID to create a tuning task.
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number 
and executions_delta > 0
order by 1, 2, 3
/

Enter value for sql_id: 873wz173punyb

SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
---------- ------ ------------------------------ ------------- --------
15694 1 10-NOV-18 01.00.04.047 AM 873wz173punyb 2391860790 1 4,586.818 33,924,912.0
15695 1 10-NOV-18 02.00.18.928 AM 873wz173punyb 2 1,488.867 0,064,449.0
15696 1 10-NOV-18 03.00.03.192 AM 873wz173punyb 2 1,053.459 8,780,977.0
Create a tuning task for the specific statement from AWR snapshots:-
Create,Execute and Report the task from given AWR snapshot IDs.
Create Task,

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 1868,
end_snap => 1894,
sql_id => '873wz173punyb',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => '873wz173punyb_tuning_task',
description => 'Tuning task for statement 873wz173punyb in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Execute Task,

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '873wz173punyb_tuning_task');

Report task,

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('873wz173punyb_tuning_task') AS recommendations FROM dual;
Interrupt Tuning task:
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => ‘873wz173punyb_tuning_task’);
Resume Tuning task:
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => ‘873wz173punyb_tuning_task’);
Cancel Tuning task:
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => ‘873wz173punyb_tuning_task’);
Reset Tuning task:
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => ‘873wz173punyb_tuning_task’);

Was this helpful?

Yes  No
Related Articles
  • Linux iostat Command Usage
  • Linux Free Command Usage
  • Linux vmstat Command Usage
  • Linux mpstat Command Usage
  • Linux SAR Command Usage
  • Linux Top Command Usage
Leave A Comment Cancel reply

Oracle Tuning
  • Analyze SQL with SQL Tuning Advisor
  • Linux iostat Command Usage
  • Linux Free Command Usage
  • Linux vmstat Command Usage
  • Linux mpstat Command Usage
  • Linux SAR Command Usage
View All 6  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org