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.