小白优化Oracle的利器”sqltrpt.sql”脚本

SQL调优顾问是Oracle自带的一个功能强大的内部诊断工具，用于对性能不佳的SQL语句给出优化建议。但如果从命令行调用它比较麻烦，幸运的是，Oracle提供了一个方便的内置脚本“sqltrpt.sql”，简化了调用过程。sqltrpt.sql
脚本位于Oracle主目录的/rdbms/admin/
目录中。它会列出前15个最消耗资源的TOP SQL，您只需要输入需要优化的SQL的SQL_ID即可自动进行优化。

关于号主，姚远：

• Oracle ACE（Oracle和MySQL数据库方向）

• 华为云最有价值专家

• 《MySQL 8.0运维与优化》的作者

• 拥有 Oracle 10g、12c和19c OCM等数十项数据库认证

• 曾任IBM公司数据库部门经理

• 20+年DBA经验，服务2万+客户

• 精通C和Java，发明两项计算机专利

以下是一个示例：

SQL> @?/rdbms/admin/sqltrpt.sql


15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SP2-0246: Illegal FORMAT string "99,"
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
3tdu16m07jbk8 525957.313858 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
1rpdpjs1a0nrq 500573.400376 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
9hs95x2v58b8x 497391.716878 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
55a46zxkgpdtb 485069.13023 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
28xj3j8qr4xum 475518.560608 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
0v5qacvm89vgw 473221.274866 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
9myxaahsdmmh2 457610.887908 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
3yqv69w8u5frx 413551.508816 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
4ghwp3827k97m 242328.0459 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
5ncgz7pyjh1us 230450.152185 UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTIT
f90zn75aphu4w 168059.994696 SELECT COUNT(DISTINCT (S_I_ID)) FROM ORDER_LINE, STOCK,
16dhat4ta7xs9 24442.679464 begin neword(:no_w_id,:no_max_w_id,:no_d_id,:no_c_id,:n
4g2g8zv8tr8vv 5337.656708 UPDATE DISTRICT SET D_YTD = D_YTD + :B3 WHERE D_ID = :B
04udrf68ccyk7 4622.33163 BEGIN slev(:st_w_id,:st_d_id,:threshold,:stocklevel); E
a4akgk9g69h83 3737.73572 SELECT d.* FROM ( SELECT d.*, ROWNUM ROW# FROM (SELECT


15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 3tdu16m07jbk8
'SQLIDSPECIFIED:3TDU16M07JBK8'
Sql Id specified: 3tdu16m07jbk8


Tune the sql
~~~~~~~~~~~~
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TASK_NAME)
----------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_3748
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/06/2024 10:55:29
Completed at : 03/06/2024 10:55:53


-------------------------------------------------------------------------------
Schema Name : TPCC
Container Name: PDBPROD1
SQL ID : 3tdu16m07jbk8
SQL Text : UPDATE STOCK_ITEM SET S_QUANTITY = (CASE WHEN S_QUANTITY < (
:B2 + 10 ) THEN S_QUANTITY + 91 ELSE S_QUANTITY END) - :B3
WHERE I_ID = :B6 AND S_W_ID = :B5 AND I_ID = :B4 RETURNING
S_DIST_06, S_QUANTITY, I_PRICE * :B1 INTO :O0 ,:O1 ,:O2
Bind Variables: :
3 - (NUMBER):75043
4 - (NUMBER):163
5 - (NUMBER):75043


-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------


1- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.


Recommendation (estimated benefit: 99.99%)
------------------------------------------
- A potentially beneficial index exists already but is currently marked
unusable. Consider rebuilding the index so that the optimizer can use it.
alter index TPCC.STOCK_I1 rebuild;


Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.


2- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.


The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.


id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 2892697577 2024-03-01/14:00:34 0.001 AWR not reproducible
2 4165137353 2024-03-05/13:45:45 1047.724 Cursor Cache not reproducible


Information
-----------
- All alternative plans other than the Original Plan could not be
reproduced in the current environment.
- The plan with id 1 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
- The plan with id 2 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
... Removed for simplicity

如果您需要优化的SQL语句不在v$sql
中，那是因为它没有保存在缓存中。如果这个SQL被AWR捕获，您可以通过查询dba_hist_sqltext
找到它的SQL_ID：

select sql_id, sql_text from SYS.DBA_HIST_SQLTEXT
where sql_text like '%SQL TEXT YOU WANT TO TUNE%';

但是，AWR只捕获 TOP SQL语句，而不是所有已执行的SQL语句。因此，您可能找不到它。如果这个问题重复出现，您可以告诉Oracle通过运行以下PL/SQL存储过程为这个SQL“着色”：

EXEC dbms_workload_repository.add_colored_sql('&SQL_ID');

这样可以确保它会保存在AWR中，即使它不是一个TOP SQL。

欢迎关注我的公众号，一起学习数据库技术👇

欢迎加我的微信，拉你进数据库微信群👇

推荐文章👇

从国内外IT人的差异谈如何破除35岁魔咒

试看号主的拙作《MySQL 8.0运维与优化》（清华大学出版社）

托业890分的Oracle ACE为您翻译国际大佬的雄文（合集）

晒一下号主的19个Oracle认证（OCP+OCM），欢迎PK