小白优化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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

如果您需要优化的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%';
1
2

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

EXEC dbms_workload_repository.add_colored_sql('&SQL_ID');
1

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