dbAnt基于SQL Tuning Advisor做SQL优化的方法

8 月

基于SQL Tuning Advisor做SQL优化的方法

dbAnt Oracle, Performance 2024-08-06

【背景】

今天有同事反馈业务系统有个简单的查询SQL跑的很慢，要3秒左右，我的第一直觉就是索引问题。

下面我们就用这个例子，来看看怎么用 SQL Tuning Advisor 做SQL优化。

【分析】

使用SQL Tuning Advisor 来代替人工分析，包括：

• 为统计信息丢失或失效的对象收集统计信息
• 考虑优化器的任何数据偏差、复杂谓词或失效的统计信息
• 重新构建 SQL 以优化性能
• 提出新索引建议

原始SQL如下：


select decode(remamt1, '', decode(remamt2, '', t3.remamt, remamt2), remamt1) remamta,
 t3.remamt remamt3,
 tt.*
 from (select t1.remamt remamt1,
 t2.remamt as remamt2,
 t1.bizid,
 t1.serseqno
 from b_wf_voumng t1
 left join bedc_detail t2
 on t1.bizid = t2.bizid) tt
 left join bedc_hisdetail t3
 on tt.bizid = t3.bizid
 where serseqno = #{serseqno}

查找SQL ID

col SQL_FULLTEXT for a100
select SQL_ID, PLAN_HASH_VALUE, SQL_FULLTEXT from v$sql where SQL_TEXT like 'select decode%bedc_detail%';

查看SQL执行计划

select * from table(dbms_xplan.display_cursor('aqfdvtkcrptbx'));

这里可以看到 B_WF_VOUMNG 表确实是走了全表扫描，只要加上对应的索引就好了。

下面我们看看 SQL Tuning Advisor 的分析建议

根据SQL_ID创建优化分析任务，如果有多个执行计划，也可以指定执行计划的hash值

DECLARE
 MY_TASK_NAME VARCHAR2(1024);
BEGIN
 MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(
 sql_id =>; 'aqfdvtkcrptbx',
 --plan_hash_value => '2744121178',
 scope => 'COMPREHENSIVE',
 time_limit => 60,
 task_name => 'my_tuning_task',
 description => 'TUNING TASK');
END;
/

查看创建的优化任务

SELECT task_name, status FROM dba_ADVISOR_TASKS WHERE task_name ='my_tuning_task';

执行优化任务，进行分析

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_tuning_task');

查看分析结果

set LONG 999999
set SERVEROUTPUT ON SIZE 999999
set LINESIZE 200
set PAGES 9999
col DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tuning_task') for a200

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tuning_task') FROM DUAL;

【分析结果如下】
分析结果比较长，分成3段看一下

第一段如下：

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 07/31/2024 14:10:31
Completed at : 07/31/2024 14:10:41

-------------------------------------------------------------------------------
Schema Name: CMS
SQL ID : aqfdvtkcrptbx
SQL Text : select decode(remamt1, '', decode(remamt2, '', t3.remamt,
 remamt2), remamt1) remamta,
 t3.remamt remamt3,
 tt.*
 from (select t1.remamt remamt1,
 t2.remamt as remamt2,
 t1.bizid,
 t1.serseqno
 from b_wf_voumng t1
 left join bedc_detail t2
 on t1.bizid = t2.bizid) tt
 left join bedc_hisdetail t3
 on tt.bizid = t3.bizid
 where serseqno = :1
Bind Variables :
 1 - (VARCHAR2(128)):SKSY24072400000833

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
 A potentially better execution plan was found for this statement.

 Recommendation (estimated benefit: 98.26%)
 ------------------------------------------
 - Consider accepting the recommended SQL profile to use parallel execution
 for this statement.
 execute dbms_sqltune.accept_sql_profile(task_name =&amp;gt; 'my_tuning_task',
 task_owner =&amp;gt; 'SYS', replace =&amp;gt; TRUE, profile_type =&amp;gt;
 DBMS_SQLTUNE.PX_PROFILE);

 Executing this query parallel with DOP 64 will improve its response time
 98.27% over the original plan. However, there is some cost in enabling
 parallel execution. It will increase the statement's resource consumption by
 an estimated 11.03% which may result in a reduction of system throughput.
 Also, because these resources are consumed over a much smaller duration, the
 response time of concurrent statements might be negatively impacted if
 sufficient hardware capacity is not available.

 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 &amp;gt; 2*CPU 0
 Weekly DB time (in sec) 0

 Projected statistics with Parallel Execution
 --------------------------------------------
 Weekly DB time (in sec) 0

SQL Tuning Advisor 给的第一个建议是开启并发执行，可以看到预计收益达到98.26%（estimated benefit: 98.26%），
效果是非常好的，但是可能会对数据库其他SQL造成影响，我们先不考虑。

第二段如下：

2- 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%)
 ------------------------------------------
 - Consider running the Access Advisor to improve the physical schema design
 or creating the recommended index.
 create index CMS.IDX$$_516130001 on CMS.B_WF_VOUMNG("SERSEQNO","BIZID","REM
 AMT");

 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.
&lt;/pre&gt;&lt;pre&gt;

第二个建议是添加索引，也就是我们刚开始说的，这个预估收益达到99.99%（estimated benefit: 99.99%），效果比开启并发执行还要好。

再来看看第三段

3- 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 3788302452 2024-07-26/17:00:04 0.684 AWR
2 2290443376 2024-07-31/08:39:39 5.907 Cursor Cache original plan
Recommendation
--------------
- Consider creating a SQL plan baseline for the plan with the best average
elapsed time.
execute dbms_sqltune.create_sql_plan_baseline(task_name =&amp;gt;
'my_tuning_task', owner_name =&amp;gt; 'SYS', plan_hash_value =&amp;gt;
3788302452);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2290443376
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 175 | 77663 (1)| 00:15:32 |
| 1 | NESTED LOOPS OUTER | | 1 | 175 | 77663 (1)| 00:15:32 |
| 2 | NESTED LOOPS OUTER | | 1 | 111 | 77661 (1)| 00:15:32 |
|* 3 | TABLE ACCESS FULL | B_WF_VOUMNG | 1 | 44 | 77659 (1)| 00:15:32 |
| 4 | TABLE ACCESS BY INDEX ROWID| BEDC_DETAIL | 1 | 67 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | BEDC_DETAIL_INDEX | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | BEDC_HISDETAIL | 1 | 64 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C00139561 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."SERSEQNO"=:1)
5 - access("T1"."BIZID"="T2"."BIZID"(+))
7 - access("T1"."BIZID"="T3"."BIZID"(+))
2- Using New Indices
--------------------
Plan hash value: 2517111532
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 175 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 175 | 7 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 111 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX$$_516130001 | 1 | 44 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| BEDC_DETAIL | 1 | 67 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | BEDC_DETAIL_INDEX | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | BEDC_HISDETAIL | 1 | 64 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C00139561 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."SERSEQNO"=:1)
5 - access("T1"."BIZID"="T2"."BIZID"(+))
7 - access("T1"."BIZID"="T3"."BIZID"(+))
3- Using Parallel Execution
---------------------------
Plan hash value: 3538277696
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 175 | 1347 (0)| 00:00:17 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 175 | 1347 (0)| 00:00:17 | Q1,00 | P-&amp;gt;S | QC (RAND) |
| 3 | NESTED LOOPS OUTER | | 1 | 175 | 1347 (0)| 00:00:17 | Q1,00 | PCWP | |
| 4 | NESTED LOOPS OUTER | | 1 | 111 | 1347 (0)| 00:00:17 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | B_WF_VOUMNG | 1 | 44 | 1347 (0)| 00:00:17 | Q1,00 | PCWP | |
| 7 | TABLE ACCESS BY INDEX ROWID| BEDC_DETAIL | 1 | 67 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 8 | INDEX UNIQUE SCAN | BEDC_DETAIL_INDEX | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 9 | TABLE ACCESS BY INDEX ROWID | BEDC_HISDETAIL | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 10 | INDEX UNIQUE SCAN | SYS_C00139561 | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("T1"."SERSEQNO"=:1)
8 - access("T1"."BIZID"="T2"."BIZID"(+))
10 - access("T1"."BIZID"="T3"."BIZID"(+))
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 2
------
Plan Origin :Cursor Cache
Plan Hash Value :2290443376
Executions :10777
Elapsed Time :5.907 sec
CPU Time :0.723 sec
Buffer Gets :263726
Disk Reads :263668
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan matches the original plan.
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 175 | 77663 (1)| 00:15:32 |
| 1 | NESTED LOOPS OUTER | | 1 | 175 | 77663 (1)| 00:15:32 |
| 2 | NESTED LOOPS OUTER | | 1 | 111 | 77661 (1)| 00:15:32 |
|* 3 | TABLE ACCESS FULL | B_WF_VOUMNG | 1 | 44 | 77659 (1)| 00:15:32 |
| 4 | TABLE ACCESS BY INDEX ROWID| BEDC_DETAIL | 1 | 67 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | BEDC_DETAIL_INDEX | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | BEDC_HISDETAIL | 1 | 64 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C00139561 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."SERSEQNO"=:1)
5 - access("T1"."BIZID"="T2"."BIZID"(+))
7 - access("T1"."BIZID"="T3"."BIZID"(+))
Plan 1
------
Plan Origin :AWR
Plan Hash Value :3788302452
Executions :5764
Elapsed Time :0.684 sec
CPU Time :0.678 sec
Buffer Gets :71476
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 175 | 81836 (1)| 00:16:23 |
| 1 | NESTED LOOPS OUTER | | 1 | 175 | 81836 (1)| 00:16:23 |
| 2 | NESTED LOOPS OUTER | | 1 | 111 | 81834 (1)| 00:16:23 |
| 3 | TABLE ACCESS BY INDEX ROWID| B_WF_VOUMNG | 1 | 44 | 81832 (1)| 00:16:22 |
|* 4 | INDEX SKIP SCAN | B_WF_VOUMNG_IDX7 | 1 | | 81830 (1)| 00:16:22 |
| 5 | TABLE ACCESS BY INDEX ROWID| BEDC_DETAIL | 1 | 67 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | BEDC_DETAIL_INDEX | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | BEDC_HISDETAIL | 1 | 64 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C00139561 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."SERSEQNO"=:1)
filter("T1"."SERSEQNO"=:1)
6 - access("T1"."BIZID"="T2"."BIZID"(+))
8 - access("T1"."BIZID"="T3"."BIZID"(+))
-------------------------------------------------------------------------------

这一段主要是列出各个建议的执行计划情况，包括原始执行计划、添加了索引的执行计划、并发执行的执行计划等等，这里我们可以看到添加索引的执行计划（2- Using New Indices）的成本是最低的，也是最优的。

最后，删除刚刚创建的 SQL Tuning Advisor 任务

EXEC dbms_sqltune.drop_tuning_task('my_tuning_task');

【解决】

最后我们只要根据建议加上索引就可以了。

create index CMS.IDX$$_516130001 on CMS.B_WF_VOUMNG("SERSEQNO","BIZID","REM AMT");

查看索引

col COLUMN_NAME for a20
SELECT TABLE_OWNER,
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
COLUMN_POSITION
FROM dba_ind_columns
WHERE table_name='B_WF_VOUMNG'
order by INDEX_NAME,COLUMN_POSITION;

【参考】

Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor (Doc ID 262687.1)

Address: https://www.dbant.com/2024/08/06/%e5%9f%ba%e4%ba%8esql-tuning-advisor%e5%81%9asql%e4%bc%98%e5%8c%96%e7%9a%84%e6%96%b9%e6%b3%95/

« ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)

OGG报错dirtmp目录占满 »

Trackback