SQL Tune Report & ndash; sqltrpt. SQL, reportsqltrpt. SQL

SQL Tune Report-sqltrpt. SQL, reportsqltrpt. SQL

ORACLE 10 Gb provides a script sqltrpt. SQL to query the most resource-consuming SQL statements. The output result is divided into two parts:

15 Most expensive SQL in the cursor cache

15 Most expensive SQL in the workload repository

In addition, you can generate the corresponding execution plan and optimization suggestions based on the entered SQL _ID, which is a good optimization script. Sqltrpt is short for 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      To tune multiple statements, create a sql tuning set and create a

Rem      tuning task with it as input (see dbmssqlt.sql).

Rem

Rem    NOTES

Rem      <other useful comments, qualifications, etc.>

Rem

Rem    MODIFIED   (MM/DD/YY)

Rem    pbelknap    04/11/05 - remove linesize 

Rem    kyagoub     07/05/04 - kyagoub_proj_13448-2

Rem    pbelknap    06/29/04 - feedback from rae burns 

Rem    pbelknap    06/17/04 - Created

Rem

SET NUMWIDTH 10

SET TAB OFF

set long 1000000;

set longchunksize 1000;

set feedback off;

set veri off;

-- Get the sql statement to tune

prompt

prompt 15 Most expensive SQL in the cursor cache

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

column elapsed format 99,990.90;

variable newl varchar2(64);

begin

  :newl := '

';

end;

/

select * from (

 select sql_id, elapsed_time / 1000000 as elapsed, SUBSTRB(REPLACE(sql_text,:newl,' '),1,55) as sql_text_fragment

 from   V$SQLSTATS

 order by elapsed_time desc

) where ROWNUM <= 15;

prompt

prompt 15 Most expensive SQL in the workload repository

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select * from (

 select stat.sql_id as sql_id, sum(elapsed_time_delta) / 1000000 as elapsed, 

     (select to_char(substr(replace(st.sql_text,:newl,' '),1,55)) 

     from dba_hist_sqltext st

     where st.dbid = stat.dbid and st.sql_id = stat.sql_id) as sql_text_fragment

 from dba_hist_sqlstat stat, dba_hist_sqltext text

 where stat.sql_id = text.sql_id and

       stat.dbid   = text.dbid

 group by stat.dbid, stat.sql_id

 order by elapsed desc

) where ROWNUM <= 15;

prompt

prompt Specify the Sql id

prompt ~~~~~~~~~~~~~~~~~~

column sqlid new_value sqlid;

set heading off;

select 'Sql Id specified: &&sqlid' from dual;

set heading on;

prompt

prompt Tune the sql

prompt ~~~~~~~~~~~~

variable task_name varchar2(64);

variable err       number;

-- By default, no error

execute :err := 0;

set serveroutput on;

DECLARE

  cnt      NUMBER;

  bid      NUMBER;

  eid      NUMBER;

BEGIN

  -- If it's not in V$SQL we will have to query the workload repository

  select count(*) into cnt from V$SQLSTATS where sql_id = '&&sqlid';

  IF (cnt > 0) THEN

    :task_name := dbms_sqltune.create_tuning_task(sql_id => '&&sqlid');

  ELSE

    select min(snap_id) into bid

    from   dba_hist_sqlstat

    where  sql_id = '&&sqlid';

    select max(snap_id) into eid

    from   dba_hist_sqlstat

    where  sql_id = '&&sqlid';

    :task_name := dbms_sqltune.create_tuning_task(begin_snap => bid,

                                                  end_snap => eid,

                                                  sql_id => '&&sqlid');

  END IF;

  dbms_sqltune.execute_tuning_task(:task_name);

EXCEPTION

  WHEN OTHERS THEN

    :err := 1;

    IF (SQLCODE = -13780) THEN

      dbms_output.put_line ('ERROR: statement is not in the cursor cache ' ||

                            'or the workload repository.');

      dbms_output.put_line('Execute the statement and try again');

    ELSE

      RAISE;

    END IF;   

END;

/

set heading off;

select dbms_sqltune.report_tuning_task(:task_name) from dual where :err <> 1;

select '   ' from dual where :err = 1;

set heading on;

undefine sqlid;

set feedback on;

set veri on;

Generally, run the following command in sqlplus @? /Rdbms/admin/sqltrpt. It is recommended that you call the dbms_sqltune package to generate optimization. It greatly facilitates the analysis and optimization of some SQL statements. The following is an example of optimization. The following is a simple script. The PRDNO data type is VARCHAR (32), and a unique index is created on this field, however, we intentionally constructed the following SQL statement that will undergo implicit conversion. Suppose this is the script sent by an application. The following shows that an estimated execution plan is based on Index Scan, the actual execution plan shown in sqltrtp goes through full table scan.

SQL> set linesize 1200

SQL> set autotrace on;

SQL> variable prd_no nvarchar2(20);

SQL> exec :prd_no :='01A10133301I';

PL/SQL procedure successfully completed.

SQL> SELECT  COUNT(1) FROM TEST

  2  WHERE PRDNO=:prd_no  

  3    AND JO_STATUS<>'L2'  

  4    AND STATUS<>'X';

  COUNT(1)

----------

         0

Execution Plan

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

Plan hash value: 2198057827

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

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |         |     1 |    17 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |         |     1 |    17 |            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    17 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_TEST |     1 |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("JO_STATUS"<>'L2' AND "STATUS"<>'X')

   3 - access("PRDNO"=:PRD_NO)

Statistics

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

          0  recursive calls

          0  db block gets

     112319  consistent gets

     112279  physical reads

          0  redo size

        514  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> 

Find the corresponding SQL _ID of the SQL statement and execute sqltrpt. The corresponding analysis optimization suggestions are displayed. For example, it prompts that the statement has implicit conversion, as shown below. We recommend that you optimize this problem.