SQL优化篇--STA缺陷研究

案例一：
–登陆fst用户

create table obj as select * from dba_objects; update obj set
object_name = null where object_id = 10; update obj set object_name =
null where object_id = 100;

–我们使用STA调优下面的SQL语句

select * from obj where object_name is null;

–创建优化任务并执行

DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN
my_sqltext := ‘select * from obj where object_name is null’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => ‘FST’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘tuning_sql_test’,
description => ‘Task to tune a query on a specified table’); DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘tuning_sql_test’);
END; /

–查看优化任务是否完成，并查看优化结果

select task_name, status from USER_ADVISOR_LOG where
task_name=‘tuning_sql_test’; SELECT sofar, totalwork FROM
V$ADVISOR_PROGRESS;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘tuning_sql_test’) from DUAL;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tuning_sql_test
Tuning Task Owner  : FST
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 10/24/2018 12:49:37
Completed at       : 10/24/2018 12:49:38
 
-------------------------------------------------------------------------------
Schema Name: FST
SQL ID     : f407fxdgta75f
SQL Text   : select * from obj where object_name is null
 
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
 
-------------------------------------------------------------------------------

从上面的优化建议输出文档中我们可以发现STA没有给出任何优化建议
实际上这里我们可以通过创建如下索引，实现sql语句的优化：

create index idx_obj_2 on obj(object_name,0);

注：对应普通的单键值索引，null值步入索引，所以在object_name列上创建单列索引是没有用的，但是可以通过添加一个常数列来创建复合索引。

案例二：

create table obj as select * from dba_objects; exec
dbms_stats.gather_table_stats(ownname=>‘FST’,tabname=>‘OBJ’,estimate_percent=>100,no_invalidate=>False,Cascade=>True);

–我们使用STA优化如下sql语句：

select object_name,object_id from obj where object_name like ‘%EMP’;

–创建优化任务并执行

DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN
my_sqltext := ‘select * from (select object_name,object_id from obj
order by object_id desc) where rownum<=5’; my_task_name :=
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => ‘FST’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘tuning_sql_test’,
description => ‘Task to tune a query on a specified table’); DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘tuning_sql_test’);
END; /

–查看优化任务是否完成，并查看优化结果

select task_name, status from USER_ADVISOR_LOG where
task_name=‘tuning_sql_test’; SELECT sofar, totalwork FROM
V$ADVISOR_PROGRESS; SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
‘tuning_sql_test’) from DUAL;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tuning_sql_test
Tuning Task Owner  : FST
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 10/24/2018 14:10:41
Completed at       : 10/24/2018 14:10:41
 
-------------------------------------------------------------------------------
Schema Name: FST
SQL ID     : afyvnamn6f4aq
SQL Text   : select * from (select object_name,object_id from obj order by
             object_id desc) where rownum<=5
 
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
  The optimizer cannot merge a view that contains an "ORDER BY" clause unless
  the statement is a "DELETE" or an "UPDATE" and the parent query is the top
  most query in the statement.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 3075738639
 
----------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     5 |   395 |       |  1059   (1)| 00:00:13 |
|*  1 |  COUNT STOPKEY          |      |       |       |       |            |          |
|   2 |   VIEW                  |      | 87001 |  6711K|       |  1059   (1)| 00:00:13 |
|*  3 |    SORT ORDER BY STOPKEY|      | 87001 |  2548K|  3424K|  1059   (1)| 00:00:13 |
|   4 |     TABLE ACCESS FULL   | OBJ  | 87001 |  2548K|       |   347   (1)| 00:00:05 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=5)
   3 - filter(ROWNUM<=5)
 
-------------------------------------------------------------------------------

–从上面的优化建议文档中可以看到STA并没有给出优化的建议
实际上这条sql可以通过如下方法优化：
1、将object_name字段使用reverse函数颠倒顺序后再创建索引
create index idx_obj_2 on obj(reverse(object_name));
2、将原来的sql语句改下如下：
select object_name,object_id from obj where reverse(object_name) like reverse(’%EMP’);
–执行计划如下