一、通过 AWR 定位问题 SQL#
在业务高峰（周一 10:00~11:00）拉取 AWR 报告，在 SQL ordered by Elapsed Time 章节找到耗时最重的 SQL，记录：
SQL_ID = f6asas4cp2n53
二、使用 SQL Monitor工具#
用SQL Monitor工具简单的分析一下这条SQL
2.1 确认参数#
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> show parameter control_management_pack_access;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> 2.2 生成 SQL Monitor 报告（ACTIVE HTML）#
SQL> set long 2000000 longchunksize 2000000 pages 0 lines 32767 trimspool on
SQL> spool sqlmon_f6asas4cp2n53_old.html
SQL> select dbms_sqltune.report_sql_monitor(sql_id=>'f6asas4cp2n53',report_level=>'ALL',type=>'ACTIVE') as report from dual;
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<base href="http://download.oracle.com/otn_software/"/>
。。。省略
</head>
<body>
<script type="text/javascript" language="JavaScript1.2" charset="utf-8" src="emviewers/scripts/document.js">
<!--Run document script-->
</script>
</body>
</html>
</iframe>
</body>
</html>
SQL> spool off2.3 分析SQL Monitor 报告#
将ACTIVE Report下载到本地查看一下，看到执行需要27秒，需要大量的用户I/O等待。
- Elapsed（总体耗时 27 秒）
- Top waits（大量 User I/O）
- 执行计划走了“明显不合理”的路径（走了低选择性索引、回表过多）
三、运行 SQL Tuning Advisor（生成建议）#
使用SQL Tuning Advisor工具来优化SQL。
3.1 创建 Tuning Task#
通过调用函数DBMS_SQLTUNE.CREATE_TUNING_TASK来创建优化任务
SQL> declare
my_task_name varchar2(30);
begin
my_task_name := dbms_sqltune.create_tuning_task(
2 3 4 5 begin_snap => 75585,
end_snap => 75586,
sql_id => 'f6asas4cp2n53',
scope => 'COMPREHENSIVE',
time_limit => 1000,
task_name => 'sql_tuning_advisor_test',
6 7 8 9 10 11 description => 'Task to tune a query on a specified table');
end;
/ 12 13 14
PL/SQL procedure successfully completed.
SQL> 3.2 执行任务#
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
SQL> exec dbms_sqltune.execute_tuning_task('sql_tuning_advisor_test');
PL/SQL procedure successfully completed.
SQL> 3.3 查看任务状态#
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name='sql_tuning_advisor_test';
TASK_NAME STATUS
------------------------------ -----------
sql_tuning_advisor_test EXECUTING
SQL> 3.4 查看优化建议（报告）#
SQL> set linesize 500 pagesize 500
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_advisor_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_ADVISOR_TEST')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_advisor_test
Tuning Task Owner : GRYL
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_89642
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 1000
Completion Status : COMPLETED
Started at : 12/17/2025 15:30:03
Completed at : 12/17/2025 15:32:46
-------------------------------------------------------------------------------
Schema Name: GRYL
SQL ID : f6asas4cp2n53
SQL Text : select count(*) from (
select distinct id from (
SELECT o.id
FROM ui_sales_invoices o
JOIN spd_cstapp_ref a ON a.IOTYPE = o.IOTYPE AND
a.CSTID = o.CSTID
join scm_salbill_hdr b on o.billid = b.id
join pub_dept pd on b.deptid = pd.deptid and
pd.spdsubdeptid = a.udeptid and pd.spdtype = '20'
WHERE a.APPID = :1
AND a.appdeptcode = :2
AND o.transflag = '00'
union all
SELECT o.id
FROM ui_sales_invoices o
JOIN spd_cstapp_ref a ON a.IOTYPE = o.IOTYPE AND
a.CSTID = o.CSTID
join scm_salbill_hdr b on o.billid = b.id
join pub_dept pd on b.deptid = pd.deptid and
pd.spdtype <> '20'
WHERE a.APPID = :3
AND a.appdeptcode = :4
AND o.transflag = '00'
)
)
Bind Variables :
1 - (VARCHAR2(128)):plemisetbj 21023
2 - (VARCHAR2(32)):6
3 - (VARCHAR2(128)):plemisetbj 21023
4 - (VARCHAR2(32)):6
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划 2。选择以下 SQL 概要文件之一进行实施。
Recommendation (estimated benefit: 98.02%)
------------------------------------------
- 考虑接受推荐的 SQL 概要文件。
execute dbms_sqltune.accept_sql_profile(task_name =>
'sql_tuning_advisor_test', task_owner => 'GRYL', replace => TRUE);
Validation results
------------------
已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,
则另一计划可能只执行了一部分。
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): 2.636928 .19837 92.47 %
CPU Time (s): 2.638678 .197866 92.5 %
User I/O Time (s): 0 0
Buffer Gets: 519126 10263 98.02 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. the original plan 的统计信息是 1 执行的平均值。
2. the SQL profile plan 的统计信息是 6 执行的平均值。
Recommendation (estimated benefit: 99.25%)
------------------------------------------
- 考虑接受建议的 SQL 概要文件, 以便对此语句使用并行执行。
execute dbms_sqltune.accept_sql_profile(task_name =>
'sql_tuning_advisor_test', task_owner => 'GRYL', replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
与 DOP 32 并行执行此查询会使 SQL 概要文件计划上的响应时间缩短 95.16%。但是, 启用并行执行时要付出一些代价。它将增加语句的资源消耗
(预计为 55.03%), 这会导致系统吞吐量降低。此外, 由于在非常短的持续时间内消耗了这些资源, 因此如果没有足够可用的硬件容量,
并发语句的响应时间将受到负面影响。
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
2- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以改进此语句的执行计划。
Recommendation (estimated benefit: 99.98%)
------------------------------------------
- 考虑运行访问指导以改进物理方案设计或者创建推荐的索引。如果选择创建推荐的索引, 请考虑删除索引
"GRYL"."IDV_UI_SALES_INVOICES_IOTYPE", 因为它是推荐的索引的前缀。
create index GRYL.IDX$$_1521E0001 on GRYL.UI_SALES_INVOICES("IOTYPE","CSTID
");
Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 优化程序不能合并位于执行计划的行 ID 4 处的视图。. 优化程序不能合并包含设置运算符的视图。.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1729567282
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 469K (1)| 01:34:00 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 2 | | 469K (1)| 01:34:00 |
| 3 | HASH UNIQUE | | 2 | 12 | 469K (1)| 01:34:00 |
| 4 | VIEW | | 2 | 12 | 469K (1)| 01:34:00 |
| 5 | UNION-ALL | | | | | |
| 6 | NESTED LOOPS | | 1 | 86 | 391K (1)| 01:18:20 |
| 7 | NESTED LOOPS | | 46 | 86 | 391K (1)| 01:18:20 |
| 8 | NESTED LOOPS | | 46 | 3450 | 391K (1)| 01:18:19 |
|* 9 | HASH JOIN | | 10 | 480 | 26 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | SPD_CSTAPP_REF | 1 | 37 | 3 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | PUB_DEPT | 91 | 1001 | 23 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | UI_SALES_INVOICES | 5 | 135 | 39148 (1)| 00:07:50 |
|* 13 | INDEX RANGE SCAN | IDV_UI_SALES_INVOICES_IOTYPE | 419K| | 797 (1)| 00:00:10 |
|* 14 | INDEX UNIQUE SCAN | PK_SCM_SALBILL_HDR | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | SCM_SALBILL_HDR | 1 | 11 | 2 (0)| 00:00:01 |
|* 16 | HASH JOIN | | 1 | 81 | 78324 (1)| 00:15:40 |
|* 17 | TABLE ACCESS FULL | PUB_DEPT | 2070 | 18630 | 23 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 72 | 78301 (1)| 00:15:40 |
| 19 | NESTED LOOPS | | 1 | 72 | 78301 (1)| 00:15:40 |
| 20 | NESTED LOOPS | | 1 | 61 | 78299 (1)| 00:15:40 |
|* 21 | TABLE ACCESS FULL | SPD_CSTAPP_REF | 2 | 68 | 3 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID| UI_SALES_INVOICES | 1 | 27 | 39148 (1)| 00:07:50 |
|* 23 | INDEX RANGE SCAN | IDV_UI_SALES_INVOICES_IOTYPE | 419K| | 797 (1)| 00:00:10 |
|* 24 | INDEX UNIQUE SCAN | PK_SCM_SALBILL_HDR | 1 | | 1 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | SCM_SALBILL_HDR | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("PD"."SPDSUBDEPTID"="A"."UDEPTID")
10 - filter("A"."UDEPTID" IS NOT NULL AND "A"."APPDEPTCODE"=:2 AND "A"."APPID"=:1)
11 - filter("PD"."SPDTYPE"='20' AND "PD"."SPDSUBDEPTID" IS NOT NULL)
12 - filter("O"."TRANSFLAG"='00' AND "A"."CSTID"="O"."CSTID")
13 - access("O"."IOTYPE"=TO_NUMBER("A"."IOTYPE"))
14 - access("O"."BILLID"="B"."ID")
15 - filter("B"."DEPTID"="PD"."DEPTID")
16 - access("B"."DEPTID"="PD"."DEPTID")
17 - filter("PD"."SPDTYPE"<>'20')
21 - filter("A"."APPDEPTCODE"=:4 AND "A"."APPID"=:3)
22 - filter("O"."TRANSFLAG"='00' AND "A"."CSTID"="O"."CSTID")
23 - access("O"."IOTYPE"=TO_NUMBER("A"."IOTYPE"))
24 - access("O"."BILLID"="B"."ID")
2- Using SQL Profile
--------------------
Plan hash value: 1915901977
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 71849 (1)| 00:14:23 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 2 | | 71849 (1)| 00:14:23 |
| 3 | HASH UNIQUE | | 2 | 12 | 71849 (1)| 00:14:23 |
| 4 | VIEW | | 2 | 12 | 71848 (1)| 00:14:23 |
| 5 | UNION-ALL | | | | | |
| 6 | NESTED LOOPS | | 1 | 86 | 23955 (1)| 00:04:48 |
| 7 | NESTED LOOPS | | 1 | 86 | 23955 (1)| 00:04:48 |
| 8 | NESTED LOOPS | | 1 | 75 | 23954 (1)| 00:04:48 |
| 9 | NESTED LOOPS | | 1 | 64 | 23952 (1)| 00:04:48 |
|* 10 | TABLE ACCESS FULL | SPD_CSTAPP_REF | 1 | 37 | 3 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | UI_SALES_INVOICES | 1 | 27 | 23952 (1)| 00:04:48 |
| 12 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 13 | BITMAP AND | | | | | |
| 14 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 15 | INDEX RANGE SCAN | IDV_UI_SALES_INVOICES_IOTYPE | 419K| | 797 (1)| 00:00:10 |
| 16 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 17 | INDEX RANGE SCAN | IDV_UI_SALES_INVOICES_TRFLAG | 419K| | 2210 (1)| 00:00:27 |
| 18 | TABLE ACCESS BY INDEX ROWID | SCM_SALBILL_HDR | 1 | 11 | 2 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_SCM_SALBILL_HDR | 1 | | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_PUB_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | PUB_DEPT | 1 | 11 | 1 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 81 | 47892 (1)| 00:09:35 |
| 23 | NESTED LOOPS | | 1 | 81 | 47892 (1)| 00:09:35 |
| 24 | NESTED LOOPS | | 1 | 72 | 47891 (1)| 00:09:35 |
| 25 | NESTED LOOPS | | 1 | 61 | 47889 (1)| 00:09:35 |
|* 26 | TABLE ACCESS FULL | SPD_CSTAPP_REF | 2 | 68 | 3 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | UI_SALES_INVOICES | 1 | 27 | 47889 (1)| 00:09:35 |
| 28 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 29 | BITMAP AND | | | | | |
| 30 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 31 | INDEX RANGE SCAN | IDV_UI_SALES_INVOICES_IOTYPE | 419K| | 797 (1)| 00:00:10 |
| 32 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 33 | INDEX RANGE SCAN | IDV_UI_SALES_INVOICES_TRFLAG | 419K| | 2210 (1)| 00:00:27 |
| 34 | TABLE ACCESS BY INDEX ROWID | SCM_SALBILL_HDR | 1 | 11 | 2 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PK_SCM_SALBILL_HDR | 1 | | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_PUB_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | PUB_DEPT | 1 | 9 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - filter("A"."UDEPTID" IS NOT NULL AND "A"."APPDEPTCODE"=:2 AND "A"."APPID"=:1)
11 - filter("A"."CSTID"="O"."CSTID")
15 - access("O"."IOTYPE"=TO_NUMBER("A"."IOTYPE"))
17 - access("O"."TRANSFLAG"='00')
19 - access("O"."BILLID"="B"."ID")
20 - access("B"."DEPTID"="PD"."DEPTID")
21 - filter("PD"."SPDTYPE"='20' AND "PD"."SPDSUBDEPTID" IS NOT NULL AND "PD"."SPDSUBDEPTID"="A"."UDEPTID")
26 - filter("A"."APPDEPTCODE"=:4 AND "A"."APPID"=:3)
27 - filter("A"."CSTID"="O"."CSTID")
31 - access("O"."IOTYPE"=TO_NUMBER("A"."IOTYPE"))
33 - access("O"."TRANSFLAG"='00')
35 - access("O"."BILLID"="B"."ID")
36 - access("B"."DEPTID"="PD"."DEPTID")
37 - filter("PD"."SPDTYPE"<>'20')
3- Using New Indices
--------------------
Plan hash value: 3981297468
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (25)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | | Q1,01 | PCWP | |
| 5 | VIEW | | 2 | | 8 (25)| 00:00:01 | Q1,01 | PCWP | |
| 6 | HASH UNIQUE | | 2 | 12 | 8 (25)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 2 | 12 | 8 (25)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 2 | 12 | 8 (25)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | HASH UNIQUE | | 2 | 12 | 8 (25)| 00:00:01 | Q1,00 | PCWP | |
| 10 | VIEW | | 2 | 12 | 6 (0)| 00:00:01 | Q1,00 | PCWP | |
| 11 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 12 | NESTED LOOPS | | 1 | 86 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
| 13 | NESTED LOOPS | | 1 | 86 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
| 14 | NESTED LOOPS | | 1 | 75 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
| 15 | NESTED LOOPS | | 1 | 64 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 17 | TABLE ACCESS FULL | SPD_CSTAPP_REF | 1 | 37 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 18 | TABLE ACCESS BY INDEX ROWID| UI_SALES_INVOICES | 1 | 27 | 21 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 19 | INDEX RANGE SCAN | IDX$$_1521E0001 | 102 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 20 | TABLE ACCESS BY INDEX ROWID | SCM_SALBILL_HDR | 1 | 11 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 21 | INDEX UNIQUE SCAN | PK_SCM_SALBILL_HDR | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 22 | INDEX UNIQUE SCAN | PK_PUB_DEPT | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 23 | TABLE ACCESS BY INDEX ROWID | PUB_DEPT | 1 | 11 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 24 | NESTED LOOPS | | 1 | 81 | 4 (0)| 00:00:01 | Q1,00 | PCWP | |
| 25 | NESTED LOOPS | | 1 | 81 | 4 (0)| 00:00:01 | Q1,00 | PCWP | |
| 26 | NESTED LOOPS | | 1 | 72 | 4 (0)| 00:00:01 | Q1,00 | PCWP | |
| 27 | NESTED LOOPS | | 1 | 61 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
| 28 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 29 | TABLE ACCESS FULL | SPD_CSTAPP_REF | 2 | 68 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 30 | TABLE ACCESS BY INDEX ROWID| UI_SALES_INVOICES | 1 | 27 | 21 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 31 | INDEX RANGE SCAN | IDX$$_1521E0001 | 102 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 32 | TABLE ACCESS BY INDEX ROWID | SCM_SALBILL_HDR | 1 | 11 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 33 | INDEX UNIQUE SCAN | PK_SCM_SALBILL_HDR | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 34 | INDEX UNIQUE SCAN | PK_PUB_DEPT | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 35 | TABLE ACCESS BY INDEX ROWID | PUB_DEPT | 1 | 9 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
17 - filter("A"."UDEPTID" IS NOT NULL AND "A"."APPDEPTCODE"=:2 AND "A"."APPID"=:1)
18 - filter("O"."TRANSFLAG"='00')
19 - access("O"."IOTYPE"=TO_NUMBER("A"."IOTYPE") AND "A"."CSTID"="O"."CSTID")
21 - access("O"."BILLID"="B"."ID")
22 - access("B"."DEPTID"="PD"."DEPTID")
23 - filter("PD"."SPDTYPE"='20' AND "PD"."SPDSUBDEPTID" IS NOT NULL AND "PD"."SPDSUBDEPTID"="A"."UDEPTID")
29 - filter("A"."APPDEPTCODE"=:4 AND "A"."APPID"=:3)
30 - filter("O"."TRANSFLAG"='00')
31 - access("O"."IOTYPE"=TO_NUMBER("A"."IOTYPE") AND "A"."CSTID"="O"."CSTID")
33 - access("O"."BILLID"="B"."ID")
34 - access("B"."DEPTID"="PD"."DEPTID")
35 - filter("PD"."SPDTYPE"<>'20')
4- Using Parallel Execution
---------------------------
Plan hash value: 2730138638
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3481 (1)| 00:00:42 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | | Q1,01 | PCWP | |
| 5 | VIEW | | 2 | | 3481 (1)| 00:00:42 | Q1,01 | PCWP | |
| 6 | HASH UNIQUE | | 2 | 12 | 3481 (1)| 00:00:42 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 2 | 12 | 3481 (1)| 00:00:42 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 2 | 12 | 3481 (1)| 00:00:42 | Q1,00 | P->P | HASH |
| 9 | HASH UNIQUE | | 2 | 12 | 3481 (1)| 00:00:42 | Q1,00 | PCWP | |
| 10 | VIEW | | 2 | 12 | 3480 (1)| 00:00:42 | Q1,00 | PCWP | |
| 11 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 12 | NESTED LOOPS | | 1 | 86 | 1361 (1)| 00:00:17 | Q1,00 | PCWP | |
| 13 | NESTED LOOPS | | 1 | 86 | 1361 (1)| 00:00:17 | Q1,00 | PCWP | |
| 14 | NESTED LOOPS | | 1 | 75 | 1361 (1)| 00:00:17 | Q1,00 | PCWP | |
| 15 | NESTED LOOPS | | 1 | 64 | 1361 (1)| 00:00:17 | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 17 | TABLE ACCESS FULL | SPD_CSTAPP_REF | 1 | 37 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 18 | TABLE ACCESS BY INDEX ROWID| UI_SALES_INVOICES | 1 | 27 | 1359 (1)| 00:00:17 | Q1,00 | PCWP | |
|* 19 | INDEX RANGE SCAN | IDV_UI_SALES_INVOICES_IOTYPE | 419K| | 28 (0)| 00:00:01 | Q1,00 | PCWP | |
| 20 | TABLE ACCESS BY INDEX ROWID | SCM_SALBILL_HDR | 1 | 11 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 21 | INDEX UNIQUE SCAN | PK_SCM_SALBILL_HDR | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 22 | INDEX UNIQUE SCAN | PK_PUB_DEPT | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 23 | TABLE ACCESS BY INDEX ROWID | PUB_DEPT | 1 | 11 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 24 | NESTED LOOPS | | 1 | 81 | 2118 (1)| 00:00:26 | Q1,00 | PCWP | |
| 25 | NESTED LOOPS | | 1 | 81 | 2118 (1)| 00:00:26 | Q1,00 | PCWP | |
| 26 | NESTED LOOPS | | 1 | 72 | 2118 (1)| 00:00:26 | Q1,00 | PCWP | |
| 27 | NESTED LOOPS | | 1 | 61 | 2118 (1)| 00:00:26 | Q1,00 | PCWP | |
| 28 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 29 | TABLE ACCESS FULL | UI_SALES_INVOICES | 2329 | 62883 | 2117 (1)| 00:00:26 | Q1,00 | PCWP | |
|* 30 | TABLE ACCESS BY INDEX ROWID| SPD_CSTAPP_REF | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 31 | INDEX RANGE SCAN | IDX_SPD_CSTAPP_REF_CSTID | 10 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 32 | TABLE ACCESS BY INDEX ROWID | SCM_SALBILL_HDR | 1 | 11 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 33 | INDEX UNIQUE SCAN | PK_SCM_SALBILL_HDR | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 34 | INDEX UNIQUE SCAN | PK_PUB_DEPT | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 35 | TABLE ACCESS BY INDEX ROWID | PUB_DEPT | 1 | 9 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
17 - filter("A"."UDEPTID" IS NOT NULL AND "A"."APPDEPTCODE"=:2 AND "A"."APPID"=:1)
18 - filter("O"."TRANSFLAG"='00' AND "A"."CSTID"="O"."CSTID")
19 - access("O"."IOTYPE"=TO_NUMBER("A"."IOTYPE"))
21 - access("O"."BILLID"="B"."ID")
22 - access("B"."DEPTID"="PD"."DEPTID")
23 - filter("PD"."SPDTYPE"='20' AND "PD"."SPDSUBDEPTID" IS NOT NULL AND "PD"."SPDSUBDEPTID"="A"."UDEPTID")
29 - filter("O"."TRANSFLAG"='00')
30 - filter("A"."APPDEPTCODE"=:4 AND "A"."APPID"=:3 AND "O"."IOTYPE"=TO_NUMBER("A"."IOTYPE"))
31 - access("A"."CSTID"="O"."CSTID")
33 - access("O"."BILLID"="B"."ID")
34 - access("B"."DEPTID"="PD"."DEPTID")
35 - filter("PD"."SPDTYPE"<>'20')
-------------------------------------------------------------------------------
SQL> 你通常会看到的两类“可参考建议”
- SQL Profile（含并行 Profile 选项）：可能改变执行计划、甚至建议并行
- ⚠️ 生产 OLTP 谨慎：并行可能放大并发压力，需要压测验证
- 索引建议：常见是对过滤列/连接列给出复合索引建议
- ⚠️ 需要评估：新增索引会增加 DML 成本，占用空间，且可能与现有索引重复
3.5 删除任务#
SQL> exec dbms_sqltune.drop_tuning_task('sql_tuning_advisor_test');
PL/SQL procedure successfully completed.
SQL>四、落地建议#
4.1 创建索引#
仔细阅读上述报告，选择采纳最佳建议，创建索引。
SQL> create index GRYL.IDV_UI_SALES_INVOICES_IC on GRYL.UI_SALES_INVOICES (IOTYPE,CSTID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
); 2 3 4 5 6 7 8 9 10 11 12
Index created.
SQL>4.2 收集统计信息#
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'GRYL',
2 3 4 tabname => 'UI_SALES_INVOICES',
5 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => TRUE,
no_invalidate => FALSE
);
END;
/ 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL>五、验证#
5.1 找到最新执行的 sql_exec_id#
SQL> select sql_id,sql_exec_id,sql_exec_start from v$sql_monitor where sql_id='f6asas4cp2n53';
SQL_ID SQL_EXEC_ID SQL_EXEC_START
------------- ----------- -------------------
f6asas4cp2n53 18820639 2025-12-18 09:01:38
f6asas4cp2n53 18820714 2025-12-18 09:03:08
f6asas4cp2n53 18820747 2025-12-18 09:03:36
f6asas4cp2n53 18820785 2025-12-18 09:04:27
f6asas4cp2n53 18820827 2025-12-18 09:06:39
5 rows selected.
SQL> 5.2 生成“优化后”SQL Monitor 报告#
SQL> set long 2000000 longchunksize 2000000 pages 0 lines 32767 trimspool on
SQL> spool sqlmon_f6asas4cp2n53_new.html
SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'f6asas4cp2n53',sql_exec_id => 18820827,report_level=>'ALL',type=>'ACTIVE') as report from dual;
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<base href="http://download.oracle.com/otn_software/"/>
<script language="javascript" type="text/javascript" src="emviewers/scripts/flashver.js">
<!--Test flash version-->
。。。省略
</head>
<body>
<script type="text/javascript" language="JavaScript1.2" charset="utf-8" src="emviewers/scripts/document.js">
<!--Run document script-->
</script>
</body>
</html>
</iframe>
</body>
</html>
SQL> spool off
SQL>5.3 分析“优化后”SQL Monitor 报告#
将ACTIVE Report下载到本地查看一下，用 SQL Monitor验证执行计划已走新索引。性能从约 27s 降到约 5s。
对比 old vs new：
- Elapsed 明显下降
- Top waits 用户I/O 明显降下来了
- 使用了新复合索引
六、总结#
通过 AWR 锁定高耗时 SQL（SQL_ID：f6asas4cp2n53），SQL Monitor 显示主要等待为用户 I/O，单次执行约 27s。
运行 SQL Tuning Advisor（COMPREHENSIVE）后，报告给出两类核心建议：
- 接受 SQL Profile（含并行 PX Profile 选项）
- 创建复合索引 (IOTYPE, CSTID)，并提示现有 IOTYPE 索引可能为前缀索引需评估是否删除。
最终选择落地“创建复合索引 + 收集统计信息”，并用 SQL Monitor验证执行计划已走新索引。性能从约 27s 降到约 5s。