Oracle的一项“AI“技能介绍

Oracle从10g推出了SQL优化器，SQL Tuning Advisor(STA)，一条SQL语句执行慢的时候，不知所措的时候，可以看看Oracle的建议，由于这是根据SQL语句、表、索引等信息进行的评估，因此若某些信息不准，则优化建议未必准确，所以只能说是Advisor，究竟是否可以直接采用，需要视情况而定，使用的是dbms_sqltune包执行，用户具有advisor权限，且必须需要运行在CBO下。

STA的使用可以有两种方法，一种方法是使用SQL ID作为参数，创建优化任务，另一种方法是直接使用SQL语句，创建优化任务。

方法二可以参考Dave的文章《如何用 SQL Tuning Advisor (STA) 优化SQL语句(http://blog.csdn.net/tianlesoftware/article/details/5630888)》，方法一可以参考如下的操作。

步骤1. 获取待优化的SQL ID

注：SQL涉及一些业务，所以就不粘贴了，而且对于STA方法的使用，是什么SQL并不重要。

select sql_id from v$sql where sql_text like ‘%2017-06-01%’;
ct1a9aah2rfav

步骤2. 创建并执行优化任务

var tuning_task varchar2(100); declare   l_sql_id v$session.prev_sql_id%TYPE;   l_tuning_task VARCHAR2(30); begin   l_sql_id := 'ct1a9aah2rfav';   l_tuning_task := dbms_sqltune.**create_tuning_task**(sql_id=l_sql_id);   :tuning_task := l_tuning_task;   dbms_sqltune.**execute_tuning_task**(l_tuning_task);   dbms_output.put_line(l_tuning_task); end; /

可以打印输出任务号，

print tuning_task; TASK_622

步骤3. 检索SQL Advisor报告，

select dbms_sqltune.report_tuning_task(:tuning_task) from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK) -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : TASK_624 Tuning Task Owner  : SYS Workload Type      : Single SQL Statement Scope              : COMPREHENSIVE --这些参数均可以设置

Time Limit(seconds): 1800 Completion Status  : COMPLETED Started at         : 01/31/2018 11:06:45 Completed at       : 01/31/2018 11:06:47 ------------------------------------------------------------------------------- Schema Name: XN_DEP_02 SQL ID     : 7wdfnb3dxp6p9 SQL Text   : SELECT ... --此处为SQL原始语句 ------------------------------------------------------------------------------- FINDINGS SECTION (1 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: 53.84%) --表示采用此建议，预计可以提升的效率

-------------------------------------------

Consider running the Access Advisor to improve the physical schema design or creating the recommended index.

create index XN_02.IDX$$_02700001 on XN_02.T("CKI","A_NO"); --表示建议创建的索引语句

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. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original --这是原始SQL的执行计划

----------- Plan hash value: 2097212109 -------------------------------------------------------------------------------------------------------- | Id  | Operation   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

Pstart| Pstop | -------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |                 |     1 |    30 |  13   (8)| 00:00:01 |  |  |

|   1 |  HASH GROUP BY   |                 |     1 |    30 |  13   (8)| 00:00:01 |  |  | |   2 |   PARTITION RANGE SINGLE |    |  1 |  30 |  12  (0)| 00:00:01 |  20 |  20 |

|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| T  |    1 |    30 |  12   (0)| 00:00:01 |  20 |  20 |

|*  4 |     INDEX RANGE SCAN | IDX_C_01 |  1 |    |   3  (0)| 00:00:01 | 20 | 20 | ------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------    3 - filter("A_NO"='3960' AND "A_CODE"='CZ')    4 - access("CKI"=12822 AND "A_DATE"=TO_DATE(' 2017-06-01 00:0 0:00', 'syyyy-mm-dd hh24:mi:ss'))

2- Using New Indices --这是采用建议的执行计划

-------------------- Plan hash value: 3064987082 ------------------------------------------------------------------------------------------------------ | Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     1 |    30 | 6  (17)| 00:00:01 |       |       | |   1 |  HASH GROUP BY |                |     1 |    30 |6  (17)| 00:00:01 |       |       | |*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T |     1 |    30 |     5   (0)| 00:00:01 |    20 |    20 | |*  3 |    INDEX RANGE SCAN                 | IDX$$_02700001 |     1 |       |     4  (0)| 00:00:01 |       |       |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("A_DATE"=TO_DATE(' 2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A_CODE"='CZ')    3 - access("CKI"=12822 AND "A_NO"='3960') -------------------------------------------------------------------------------

步骤4. 执行优化建议

create index XN_02.IDX$$_02700001 on XN_02.T(“CKI”,”A_NO”);

STA整个操作，其实比较简单，建议写的也比较清楚，但至于是否可以采用建议，尤其是生产环境，需要斟酌一番，至少可以作为优化的参考。

现在AI概念炒的比较热，什么AI运维、AI计算，其实有些就是个噱头，有些不是AI的技术，沾了AI的边，就让人误以为AI，作为技术人员，对此要有所甄别，技术是为业务服务的，因此只要是合适的技术，就会有他的价值，无论是否AI。

STA其实就是某种意义的AI，可以叫做SQL智能AI优化，随着Oracle版本的发展，类似这种STA的智能AI优化，会越来越准确，让DBA和开发人员可以依赖。

Oracle作为一款商业数据库，在满足基本需求的同时，也在升级他的各种功能，比如Oracle 18c，宣传的就是Autonomous自治，让数据库可以进行自诊断、自恢复，真正实现数据库运维的自动，对于DBA来说，是一个挑战，但真正让数据库自己做主，there is a long way to go，因此对于DBA，还是有这个缓冲，可以增强自己的技能，当然，这完全是取决于自己。

如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，