当前位置:   article > 正文

SQL优化篇--STA缺陷研究_sta sql

sta sql

案例一:
–登陆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.
 
-------------------------------------------------------------------------------
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

从上面的优化建议输出文档中我们可以发现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)
 
-------------------------------------------------------------------------------
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50

–从上面的优化建议文档中可以看到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’);
–执行计划如下
在这里插入图片描述

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/神奇cpp/article/detail/863098
推荐阅读
相关标签
  

闽ICP备14008679号