一 写在前面
前一段时间写了几篇使用Oracle 自动优化工具分析和优化数据库SQL语句性能的文章，这篇文章的目的是把前边写的内容串联起来，形成一个从负载抓取、性能分析、性能优化直至优化效果分析一个流程性的、总体性的过程，顺便也补充一下以前没有提过的知识点。
二 负载选取
选取待优化的负载的时间段，一般是典型的业务时间段，或者是业务高峰时间段，也可以是数据库出现性能问题的时间段。时间段的选取不需要太长，一般是十几几分钟，最长也不多是三四个小时，太长的时间段容易掩盖高峰期的问题。
三 获取分析ORACLE AWR 报告
oracle数据库默认每个小时生成一个AWR快照，这个值可以根据需要更改，很多生产环境数据库改成了半小时生成一个快照。如果是现场捕捉，需要在开始捕捉和结束捕捉前手动生成awr快照，使用下面的命令
exec dbms_workload_repository.create_snapshot();
AWR报告的分析这里不做全面的介绍，和SQL相关的在SQL Statistics部分，如下
另外还要结合等待事件和Time Model Statistics等部分进行综合分析，从Oracle 12C 以后，AWR报告也包括ADDM报告，里面也可能有对SQL进行优化的指导，也是SQL 优化很好的参考。
四 创建STS（SQL tuning set），载入工作负载
STS可以捕获数据库负载，用作性能性能监控和优化的输入，STS包括以下组件：
- 包括一系列SQL语句
- 语句的相关执行背景（context），例如用户模式, 应用模块名和和动作（action）, 绑定变量值列表, 以及游标的sql编译环境。
- 语句的基本执行统计信息, 例如执行事件, CPU事件, 缓冲区读次数, 磁盘读次数, 处理的行数,游标获取数, 执行次数, 完成执行次数, 优化器成本, 以及命令类型。
- 相关执行计划和行源统计信息。
使用STS，可以将一系列SQL语句和相关元数据打包到一个数据库对象中，这个数据库对象可以用作性能优化建议和其它优化工具输入，也可以将其传输到其它数据库上使用。捕获到STS中的负载可以是自动负载仓库(AWR), 共享SQL区, 特定的SQL语句, 追踪文件, 或者其它STS。从AWR报告捕获数据库负载是一个常用的方法，因为AWR快照是自动捕获的，要做的只是将特定时间段特定用户的负载捕获到STS集中即可。
1 创建sts
BEGIN
DBMS_SQLSET.CREATE_SQLSET (
sqlset_name => 'STS_f_AWR' --STS集名称
, description => 'STS from awr snapshots' --STS集描述
);
END;
2 载入负载
通过设置DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY函数的parsing_schema_name参数，只载入用户TEST的负载，这里TEST前后面要使用双引号。
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(55,56,
'parsing_schema_name = ''TEST''',
NULL, NULL,NULL,NULL,
1,
NULL,
'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS_f_AWR',
populate_cursor => cur,
load_option => 'MERGE',
update_option => 'ACCUMULATE');
END;
3 查看载入的负载
SQL> select NAME,DESCRIPTION,CREATED,STATEMENT_COUNT from user_sqlset where name='STS_f_AWR';
NAME DESCRIPTION CREATED STATEMENT_COUNT
-------------------------------- ------------------------------------------------ --------- ---------------
STS_f_AWR STS from awr snapshots 04-SEP-25 9
五 使用SQL tuning task 分析工作负载
1 创建SQL 优化任务
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'STS_f_AWR',
scope => DBMS_SQLTUNE.scope_comprehensive,
basic_filter =>'parsing_schema_name=''TEST''',
rank1 => 'BUFFER_GETS',
result_limit => 20,
time_limit => 3600,
task_name => 'my_SQL_Capture_task',
description => 'Tuning task for captured SQL');
END;
rank1设置语句的排序依据，这里选择的缓冲区读，实际选择哪个排序需要根据AWR报告的分析结果来确定，缓冲区读是一个比较合适的指标，因为缓冲区读比较大的SQL往往CPU执行时间和物理IO也比较大。
2 执行优化任务
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_SQL_Capture_task');
3 查看和分析优化报告
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_SQL_Capture_task') FROM DUAL;
这里只摘录报告比较重要的部分
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 9
Number of SQLs in the Report : 3
Number of SQLs with Findings : 3
Number of SQLs with Alternative Plan Findings: 2
Number of SQLs with SQL profiles recommended : 1
Number of SQLs with Index Findings : 1
这一部分报告的汇总，分析了9条SQL语句，在报告中（可以优化的）有3条，有两条语句有替代的较优的执行计划，一条语句有可以推荐的SQL Profile，一条语句可以通过索引优化。
-------------------------------------------------------------------------------
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID SQL ID statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
2 dbafg4pk1czwu 74.18%
7 c4uzwc14895n6 58.00%
3 b2n572dgtndrn
这一部分是SQL可以从优化中获得的收益。
-------------------------------------------------------------------------------
Tables with New Potential Indices (ordered by schema, number of times, table)
-------------------------------------------------------------------------------
Schema Name Table Name Index Name Nb Time
--------------------------- --------------------------- -------------- --------
TEST CUSTOMER_ORDERS IDX$$_00C80002 1
-------------------------------------------------------------------------------
这一部分是索引优化影响到的表。下面是报告的详细部分。
DETAILS SECTION
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 2
Schema Name : TEST
Container Name: FREEPDB1
SQL ID : dbafg4pk1czwu
SQL Text : SELECT
o.ORDER_ID,
o.CUSTOMER_ID,
........
这一部分是按照SQL语句的最大收益排序的，收益最大的在最前面，这条语句有两个finding（优化建议）
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: 74.18%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index TEST.IDX$$_00C80002 on TEST.CUSTOMER_ORDERS("TOTAL_AMOUNT","OR
DER_DATE");
索引优化，评估的收益是74.18%,Oracle 给出了两个优化建议，一是创建索引，在这里给出了索引的创建语句，二是运行访问建议来提高物理模式设计。
对于这条语句的第二个优化措施是更优的物理计划
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 3272358955 2025-09-04/02:58:55 13.938 AWR
2 4246839055 2025-09-04/06:03:12 14.640 AWR original plan
3 1987091166 2025-09-04/02:58:55 17.939 AWR
从上面可以看到，这条语句的三个执行计划的执行时间差别不是太大，优化措施提升有限，应该首先考虑第一个创建索引来优化，如果因为某种原因不能创建索引，可以考虑这个优化措施。下面看对下一条语句的优化措施
Object ID : 7
Schema Name : TEST
Container Name: FREEPDB1
SQL ID : c4uzwc14895n6
SQL Text : SELECT ORDER_ID FROM CUSTOMER_ORDERS WHERE ROWNUM <= 1000
ORDER BY DBMS_RANDOM.RANDOM
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 58%)
---------------------------------------
- Consider accepting the recommended SQL profile.
BEGIN
dbms_sqltune.accept_sql_profile(
task_name => 'my_SQL_Capture_task',
object_id => 7,
task_owner => 'SYS',
replace => TRUE);
END;
/
这一条语句可以通过SQL profile来优化，报告里给出了接受这个profile的语句。看一下采用这个profile后这条语句的收益
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .002962 .002455 17.11 %
CPU Time (s): .002994 .002446 18.3 %
User I/O Time (s): 0 0
Buffer Gets: 36 15 58.33 %
这条语句的性能本身没有明显的问题，执行时间才.002962秒，这个措施根据情况也可以不实施。下面看第三条语句的优化措施
Object ID : 3
Schema Name : TEST
Container Name: FREEPDB1
SQL ID : b2n572dgtndrn
SQL Text : SELECT
c.CUSTOMER_ID,
c.CUSTOMER_NAME,
......
.......
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- 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 660144547 2025-09-04/06:03:12 14.271 AWR original plan
2 305751537 2025-09-04/02:58:55 57.524 AWR
这条语句有更好的执行计划，可以设置为SQL plan基线，将这个执行计划固定下来，报告也给出了设定基线的模板
BEGIN
dbms_sqltune.create_sql_plan_baseline(
task_name => 'my_SQL_Capture_task',
object_id => 3,
owner_name => 'SYS',
plan_hash_value => xxxxxxxx);
END;
/
将plan_hash_value值改为性能更优的执行计划的值，就可以创建sql plan 基线了。
六 使用SQL Access Advisory 分析工作负载
1 创建DBMS建议任务并关联负载
1） 创建任务
begin
DBMS_ADVISOR.CREATE_TASK (advisor_name =>'SQL Access Advisor',
task_name=> 'my_task');
END; /
2）查看创建的任务
SQL> select TASK_NAME,ADVISOR_NAME,TASK_ID,STATUS from USER_ADVISOR_TASKS where TASK_NAME='my_task'
TASK_NAME ADVISOR_NAME TASK_ID STATUS
-------------------------------- -------------------------------- ---------- -----------
my_task SQL Access Advisor 205 INITIAL
3）关联数据库负载
SQL> EXECUTE DBMS_ADVISOR.ADD_STS_REF('my_task', null, 'STS_f_AWR');
4）设置任务参数
SQL> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('my_task','STORAGE_CHANGE', 1000000000);
5）执行访问建议任务
SQL> EXECUTE DBMS_ADVISOR.EXECUTE_TASK('my_task');
2 获取任务分析报告
1) 检查任务完成状态
SQL> select TASK_NAME,ADVISOR_NAME,TASK_ID,STATUS from USER_ADVISOR_TASKS where TASK_NAME='my_task';
TASK_NAME ADVISOR_NAME TASK_ID STATUS
-------------------------------- -------------------------------- ---------- -----------
my_task SQL Access Advisor 205 COMPLETED
2)获取报告
select DBMS_ADVISOR.GET_TASK_REPORT ('my_task') from dual;
3 查询相关视图分析
1） 查询建议记录及收益
SQL> SELECT REC_ID, RANK, BENEFIT
FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME ='my_task'; 2
REC_ID RANK BENEFIT
---------- ---------- ----------
1 1 1766240
2 2 0
3 3 0
- 查询建议对整个负载的影响
SQL> l
1 SELECT a.sql_id, a.rec_id, a.precost, a.postcost,
2 (a.precost-a.postcost)*100/a.precost AS percent_benefit,
3 dbms_lob.substr(h.sql_text,40) sql_text
4 FROM USER_ADVISOR_SQLA_WK_STMTS a left join dba_hist_sqltext h on a.sql_id=h.sql_id
5* WHERE TASK_NAME = 'my_task' order by 2,3
SQL> /
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT SQL_TEXT
------------- ---------- ---------- ---------- --------------- ----------------------------------------------------------------
c4uzwc14895n6 0 7775 7775 0 SELECT ORDER_ID FROM CUSTOMER_ORDERS WHE
c4uzwc14895n6 0 7775 7775 0 SELECT ORDER_ID FROM CUSTOMER_ORDERS WHE
cr2852yv20dak 0 2499221 2499221 0 SELECT
order_date,
cr2852yv20dak 0 2499221 2499221 0 SELECT
order_date,
5k2dzhzjk2575 0 7487304 7487304 0 INSERT INTO CUSTOMER_ORDERS (ORDER_ID, C
5k2dzhzjk2575 0 7487304 7487304 0 INSERT INTO CUSTOMER_ORDERS (ORDER_ID, C
dbafg4pk1czwu 0 12094240 12094240 0 SELECT
o.ORDER_ID,
dbafg4pk1czwu 0 12094240 12094240 0 SELECT
o.ORDER_ID,
9s3d9fr4fxc9s 1 3859240 3039960 21.2290503 WITH customer_sales AS (
9s3d9fr4fxc9s 1 3859240 3039960 21.2290503 WITH customer_sales AS (
b2n572dgtndrn 1 4488092 3541132 21.0993892 SELECT
c.CUSTOMER_I
b2n572dgtndrn 1 4488092 3541132 21.0993892 SELECT
c.CUSTOMER_I
a6kst3w6cjppj 2 7161 7161 0 UPDATE CUSTOMERS SET STATUS = :1, LAST_U
a6kst3w6cjppj 2 7161 7161 0 UPDATE CUSTOMERS SET STATUS = :1, LAST_U
fft09zpbz57xf 2 9210 9210 0 SELECT * FROM CUSTOMERS WHERE CUSTOMER_I
fft09zpbz57xf 2 9210 9210 0 SELECT * FROM CUSTOMERS WHERE CUSTOMER_I
7fbag4fbuq5g1 3 4668 4668 0 DELETE FROM CUSTOMER_ORDERS WHERE ORDER_
7fbag4fbuq5g1 3 4668 4668 0 DELETE FROM CUSTOMER_ORDERS WHERE ORDER_
3) 查询每条建议及操作
SQL> select REC_ID,ACTION_ID,SUBSTR(command,1,30) AS command,ATTR1,ATTR3,ATTR4 from user_advisor_actions
where task_name='my_task'
REC_ID ACTION_ID COMMAND ATTR1 ATTR3 ATTR4
---------- ---------- ------------------------ -------------------------------- ------------------------ ------------
1 3 CREATE INDEX "TEST"."CUSTOMERS_IDX$$_00CD0000 "TEST"."CUSTOMERS" BTREE
"
2 2 RETAIN INDEX "TEST"."SYS_C008516" "TEST"."CUSTOMERS" BTREE
3 1 RETAIN INDEX "TEST"."SYS_C008547" "TEST"."CUSTOMER_ORDERS" BTREE
- 生成建议的执行脚本
SQL> select DBMS_ADVISOR.GET_TASK_SCRIPT('my_task') from dual;
DBMS_ADVISOR.GET_TASK_SCRIPT('MY_TASK')
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 23.0.0.0.0 - Production
Rem
Rem Username: SYS
Rem Task: my_task
Rem Execution date: 04/09/2025 07:58
Rem
/* RETAIN INDEX "TEST"."SYS_C008547" */
/* RETAIN INDEX "TEST"."SYS_C008516" */
CREATE INDEX "TEST"."CUSTOMERS_IDX$$_00CD0000"
ON "TEST"."CUSTOMERS"
("CUSTOMER_ID","CUSTOMER_NAME")
COMPUTE STATISTICS;
六 应用SQL profile
在SQL优化任务报告中有一个推荐的SQL profile，接受这个profile，运行报告中的相关命令
SQL> BEGIN
dbms_sqltune.accept_sql_profile(
task_name => 'my_SQL_Capture_task',
object_id => 7,
task_owner => 'SYS',
replace => TRUE);
END; 2 3 4 5 6 7
8 /
PL/SQL procedure successfully completed.
检查创建的SQL profile
select * from DBA_SQL_PROFILES;
SYS_SQLPROF_019917db92610000 DEFAULT 10456046097614862085 SELECT ORDER_ID FROM CUSTOMER_ORDERS WHERE ROWNUM <= 1000 ORDER BY DBMS_RANDOM.RANDOM 2025-09-05 03:11:29.907 2025-09-05 03:11:29.000 MANUAL ENABLED NO 200 EXEC_171 7 6 4 0
检查语句的执行计划
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 12g82ymb6dxa5, child number 0
-------------------------------------
SELECT ORDER_ID FROM CUSTOMER_ORDERS WHERE ROWNUM <= 1000
ORDER BY DBMS_RANDOM.RANDOM
Plan hash value: 1211848268
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT ORDER BY | | 1000 | 6000 | 2 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | INDEX FAST FULL SCAN| SYS_C008547 | 1001 | 6006 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=1000)
Note
-----
- SQL profile SYS_SQLPROF_019917db92610000 used for this statement
note部分优化器在解析语句的过程中使用了SQL profile，两条语句的sql_id并不相同，这是因为两条语句的空格可能不完全一样，经Oracle normalize（标准化）之后相同，所以SQL profile照样可用。
语句原来的执行计划也附在下面
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3349094533
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 1893 (1)| 00:
00:01 |
| 1 | SORT ORDER BY | | 1000 | 6000 | 1893 (1)| 00:
00:01 |
|* 2 | COUNT STOPKEY | | | | |
|
| 3 | TABLE ACCESS FULL| CUSTOMER_ORDERS | 1009K| 5912K| 1893 (1)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=1000)
通过对比两个执行计划，发现应用了SQL profile后的执行计划使用在第三个操作使用了INDEX FAST FULL SCAN，成本降低到了2（原来是1893），调整后的执行计划更适应大数据量下的查询。
七 应用SQL plan baseline
调优报告推荐的SQL plan baseline，对语句的优化幅度还是比较大的。也采用一下
SQL> BEGIN
dbms_sqltune.create_sql_plan_baseline(
task_name => 'my_SQL_Capture_task',
object_id => 3,
owner_name => 'SYS',
plan_hash_value => 660144547);
END;
/ 2 3 4 5 6 7 8
BEGIN
*
ERROR at line 1:
ORA-13846: Cannot create SQL plan baseline on the given plan
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 19873
ORA-06512: at "SYS.DBMS_SQLTUNE", line 11941
ORA-06512: at line 2
Help: https://docs.oracle.com/error-help/db/ora-13846/
创建的过程中报错了，这个报错有两个原因，一个是输入值对应多个执行计划，另一种情况是执行计划不可重建，如果仍然试图优化，可以针对这条语句运行SQL 调优任务，步骤如下
---创建单条SQL调优任务，使用sql_id作为参数
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'gs6dd5p7xj7ry',
task_name => 'singlesql');
END;
---执行调优任务
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('singlesql');
----查看调优报告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('singlesql') FROM DUAL;
检查调优报告，发现没有任何优化建议。如果想要将这条SQL语句现在的执行计划设置为SQL plan 基线，可以通过设置optimizer_capture_sql_plan_baselines参数自动捕捉运行语句的执行计划为SQL plan 基线，运行下面语句
ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
--要捕捉的语句
ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
检查创建的基线
SQL>SELECT b.sql_handle,b.plan_name,b.notes from dba_sql_plan_baselines b;
SQL_HANDLE PLAN_NAME NOTES
------------------------------ ------------------------------------ ------------------------------------------------------------
SQL_600ff232b5fd1ed8 SQL_PLAN_603zk6auzu7qs23275cf2 <notes><plan_id>589782258</plan_id><flags>11</flags></notes>
刷新数据库共享缓冲区后再次运行SQL语句，执行计划如下
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gs6dd5p7xj7ry, child number 0
-------------------------------------
SELECT c.CUSTOMER_ID,
c.CUSTOMER_NAME,
COUNT(o.ORDER_ID) AS order_count,
SUM(o.TOTAL_AMOUNT) AS total_amount,
AVG(o.TOTAL_AMOUNT) AS avg_amount,
MAX(o.TOTAL_AMOUNT) AS max_amount,
MIN(o.TOTAL_AMOUNT) AS min_amount
FROM CUSTOMERS c
JOIN
CUSTOMER_ORDERS o.CUSTOMER_IDR_ID =
WHERE
o.ORDER_DATE >= ADD_MONTHS(SYSDATE, -6)
GROUP BY c.CUSTOMER_ID,
c.CUSTOMER_NAME HAVING
Plan hash value: 660144547
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 3601 (100)| |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 735 | 95550 | | 3601 (1)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 735 | 75705 | | 3601 (1)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | HASH GROUP BY | | 735 | 75705 | | 3601 (1)| 00:00:01 |
|* 6 | HASH JOIN | | 14688 | 1477K| 1352K| 3597 (1)| 00:00:01 |
| 7 | VIEW | VW_GBC_6 | 14688 | 1176K| | 1913 (2)| 00:00:01 |
| 8 | HASH GROUP BY | | 14688 | 243K| | 1913 (2)| 00:00:01 |
|* 9 | TABLE ACCESS FULL| CUSTOMER_ORDERS | 210K| 3502K| | 1908 (2)| 00:00:01 |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 300K| 6152K| | 1149 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
4 - filter(NVL(SUM("ITEM_2"),0)>=5)
6 - access("C"."CUSTOMER_ID"="ITEM_1")
9 - filter("O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,(-6)))
Note
-----
- SQL plan baseline SQL_PLAN_603zk6auzu7qs23275cf2 used for this statement
Note 部分显示这条语句应用了SQL plan baseline,使用的SQL plan同前面查询的相同。查看这个基线的详细内容用下面的语句
SQL> SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_600ff232b5fd1ed8')); 2
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_600ff232b5fd1ed8
SQL text: SELECT c.CUSTOMER_ID,
c.CUSTOMER_NAME,
---中间省略多行
total_amount DES FETCH FIRST
100 ROWS ONLY
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_603zk6auzu7qs23275cf2 Plan id: 660144547
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From Auto SQL Tuning Set
--------------------------------------------------------------------------------
Plan hash value: 660144547
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 3601 (100)| |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 735 | 95550 | | 3601 (1)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 735 | 75705 | | 3601 (1)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | HASH GROUP BY | | 735 | 75705 | | 3601 (1)| 00:00:01 |
|* 6 | HASH JOIN | | 14688 | 1477K| 1352K| 3597 (1)| 00:00:01 |
| 7 | VIEW | VW_GBC_6 | 14688 | 1176K| | 1913 (2)| 00:00:01 |
| 8 | HASH GROUP BY | | 14688 | 243K| | 1913 (2)| 00:00:01 |
|* 9 | TABLE ACCESS FULL| CUSTOMER_ORDERS | 211K| 3503K| | 1908 (2)| 00:00:01 |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 300K| 6152K| | 1149 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
4 - filter(NVL(SUM("ITEM_2"),0)>=5)
6 - access("C"."CUSTOMER_ID"="ITEM_1")
9 - filter("O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,(-6)))
可以看到基线的详细信息包括其执行计划。使用下面语句可以显示基线的hint
SQL> SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_600ff232b5fd1ed8',
format => 'outline'));
---内容比较多，只摘取基线的hint部分
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$433063BA" GROUP_BY)
FULL(@"SEL$433063BA" "O"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$C4C0C15C" GROUP_BY)
USE_HASH(@"SEL$C4C0C15C" "C"@"SEL$1")
LEADING(@"SEL$C4C0C15C" "VW_GBC_6"@"SEL$9E8AE3DA" "C"@"SEL$1")
FULL(@"SEL$C4C0C15C" "C"@"SEL$1")
NO_ACCESS(@"SEL$C4C0C15C" "VW_GBC_6"@"SEL$9E8AE3DA")
NO_ACCESS(@"SEL$3" "from$_subquery$_004"@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$9E8AE3DA")
OUTLINE_LEAF(@"SEL$3")
PLACE_GROUP_BY(@"SEL$58A6D7F6" ( "O"@"SEL$1" ) 6)
OUTLINE_LEAF(@"SEL$C4C0C15C")
OUTLINE_LEAF(@"SEL$433063BA")
ALL_ROWS
DB_VERSION('23.1.0')
OPTIMIZER_FEATURES_ENABLE('23.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
八 写在后面
本文提供了一个使用Oracle SQL优化工具系统进行数据库SQL进行优化的过程，使用这个过程可以节省DBA大量的分析工作，提高工作效率。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
