SQL Tune Report–sqltrpt.sql
SQL report
2023-09-14 08:57:54 时间
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 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 its 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 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 Tune Report sqltrpt.sql 原文:SQL Tune Report–sqltrpt.sql ORACLE 10g提供了一个脚本sqltrpt.sql用来查询最耗费资源的SQL语句,其输出的结果分为两部分: 15 Most expensive SQL in the cursor cache 15 Most expensive SQL in the workload repository 另外可以根据输入的SQL_ID,生成对应执行计划和调优建议,是一个不错的调优优化脚本。
在做项目的过程中遇到SSRS与IE9, IE10不兼容的情况,具体表现为报表页面在IE9 和 IE10下面只显示三分之一,靠左显示,下方有滚动条,右三分之二为空白。
SQL Tune Report sqltrpt.sql ORACLE 10g提供了一个脚本sqltrpt.sql用来查询最耗费资源的SQL语句,其输出的结果分为两部分: 15 Most expensive SQL in the cursor cache 15 Most expensive SQL in the workload repository 另外可以根据输入的SQL_ID,生成对应执行计划和调优建议,是一个不错的调优优化脚本。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载
相关文章
- mybatis分页查询之sql server–mysql[通俗易懂]
- SQL 时间查询
- MySQL Error number: MY-010208; Symbol: ER_SQL_HA_READ_FAILED; SQLSTATE: HY000 报错 故障修复 远程处理
- SQL Server阻止保存修改表结构的解决方法
- Hibernate show_sql属性:指定是否输出SQL语句
- 标题:使用Oracle 查询 SQL 语句(oracle查sql)
- 构建基于SQL Server的集群环境(sqlserver集群)
- Linux下使用ODBC连接SQL Server(linux连接sqlserver)
- 防止MySQL注入:决定你系统安全的关键步骤(mysql防止sql注入)
- 大数据管理与SQL Server相结合,突破传统思维(大数据sqlserver)
- SQL Server 数据库遭遇“突然闪退”,应急处理指南(sqlserver 闪退)
- 如何使用MySQL定时自动执行SQL语句(mysql定时执行sql)
- 化SQL Server的范式化:更高效的数据存储(sqlserver的范式)
- SQL Server中管理用户组的实用技巧(sqlserver用户组)
- 保养年末SQL Server系统保养必不可少(sqlserver 年末)
- SQL Server家庭版:赋能家庭数据处理(sqlserver家庭版)
- 备份SQL Server定时备份:保障数据安全(sqlserver 定时)
- sql两大主流数据库之比较—SQL Server和MySQL(sqlserver和my)
- 查询SQL Server中使用倒序查询的运算结果(sqlserver中倒序)
- SQL Server2安装指南:让系统更稳定运行(sqlserver2安装)
- Oracle SQL跟踪利器:掌握Oracle跟踪SQL工具!(oracle跟踪sql工具)
- 运行Oracle终止SQL运行:收尾全搞定(oracle终止sql)
- MySQL中如何导入SQL文件(mysql如何导入sql文件)
- 如何利用Oracle掌握SQL写作技巧(oracle写sql格式)
- Oracle SQL实现数据库修改的技巧(oracle sql修改)