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，就會看到對應的分析優化建議，例如它提示語句存在隱式轉換，如下截圖所示，建議你優化這個問題。
