你的位置:首页 > 数据库

[数据库]SQL Tune Reportsqltrpt.sql


ORACLE 10g提供了一个脚本sqltrpt.sql用来查询最耗费资源的SQL语句,其输出的结果分为两部分:

    15 Most expensive SQL in the cursor cache

    15 Most expensive SQL in the workload repository

另外可以根据输入的SQL_ID,生成对应执行计划和调优建议,是一个不错的调优优化脚本。其实是sqltrpt是SQL Tune Report的缩写。这个脚本位于$ORACLE_HOME/rdbms/admin/sqltrpt.sql。 具体脚本如下所示

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;


一般在sqlplus里面执行下面命令@?/rdbms/admin/sqltrpt即可。它生成调优优化建议是通过调用dbms_sqltune包来完成的。使用它很大程度上方便我们对一些SQL的分析和优化。下面我们构造一个调优例子,如下所示,很简单的一个脚本,其中PRDNO的数据类型为VARCHAR(32),在这个字段上建有唯一索引,但是我们故意构造了下面这样会发生隐式转换的SQL,假设这是某个应用程序发出的脚本,下面会看到一个预估的执行计划是走Index Scan,在sqltrtp里面看到的实际执行计划走全表扫描。

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> 


我们先找到该SQL对应的SQL_ID,执行sqltrpt,就会看到对应的分析优化建议,例如它提示语句存在隐式转换,如下截图所示,建议你优化这个问题。

image

clip_image001