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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.