Skip to main content

Steps to generate SQL tuning Advisory Manually

SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. SQL Tuning Advisor is a mechanism for resolving problems related to sub optimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance of high-load SQL statements, and prevent regressions by only executing optimal plans.

Step1: Create task

variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);

EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
sql_id => '&sql_id', -       
task_name => 'sql_tuning_task1',-
description  => 'Tune my query');

Step2: Execute task

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sql_tuning_task1');

Step3: Review task

SET LONG 1000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_task1') from DUAL;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally.

Comments

Post a Comment

Popular posts from this blog

Original: SQL Tune Report–sqltrpt.sql

  Original:  SQL Tune Report–sqltrpt.sql ORACLE 10g provides a script sqltrpt.sql to query the most resource-intensive SQL. ln addition, according to the input SQL_ID, generate corresponding execution plan and tuning recommendations, which is a good tuning optimization script. In fact, sqltrpt is an abbreviation of SQL Tune Report. This script is located at $ORACLE_HOME/rdbms/admin/sqltrpt.sql. The specific script is as follows Rem Rem $Header: sqltrpt. sql 11-apr-2005.11:01:39 pbelknap Exp $ Rem Rem sqltrpt. sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  Rem Rem    NAME Rem      sqltrpt. sql - SQL Tune RePorT Rem Rem    DESCRIPTION Rem      Script that gets a single statement   as   input   from the user (via SQLID), Rem      tunes that statement , and   then displays the text report. Rem Rem    ...
Read more

SQL Performance issues basic Troubleshooting Stpes

  SQL Performance Troubleshooting In order to troubleshoot SQL Performance , you will need to do some basic troubleshooting in systematic approach Steps : (Request apps user to provide Request id/session details and Level 12 Tkprof for problematic SQL) a)Find Top SQL from Gv$session/gv$active_session_history/dba_hist_active_sess_history(by giving Sid serial #and inst id) b) SQL Text for Top sql C) Execution or online history for the SQL also check for CPU usage LIO PIO  Row executed No of Executions for SQL(time per single execution) and also check for IO latency if any d) check plan_table for plans opted by the SQL(dbms-xplain-awr/cursor) e)check Table and index Stats and data distribution on columns f) if SQL is currently running check How many rows it's processing g)check previous history for the SQL using hash_value h)find bind variable passing in the Query I)find Row source info if SQL is running  1)Get the Execution Statistics for given SQL_ID 2)Get the Ex...
Read more

Performance Tuning Level - I

Image
  Performance Tuning  Level - I SQL Tuning – Level I 1.        Job is running slow : Ask for Details – Template for Tickets. 2.        SQL running slow - Find the SQL 3.        Find the Stale Statistics objects, column details and index status. 4.        How long a SQL has been running 5.        Explain plan for SQL Running -- From Cursor Cache 6.        Explain plan for SQL already been completed. – From AWR 7.         Easy way to find top – running SQL using OEM 8.        My package / procedure running slow – How to find the SQL 9.        Enabling the Trace. 10.    Setup the trace before package starts. – Using Trigger. 11.    SQL Running Slow – I hav...
Read more