前言:经常可以碰到优化sql的需求,开发人员直接扔过来一个SQL让DBA优化,然后怎么办?
当然,经验丰富的DBA可以从各种方向下手,有时通过建立正确索引即可获得很好的优化效果,但是那些复杂SQL错综复杂的表关联,却让DBA们满头大汗。
如下特别介绍一种oracle官方提供的科学优化方法STA,经过实践,不敢说此特性绝对有效,但是可以开阔思路,并且从中学到许多知识,不再用“猜”的方式去创建索引了。
SQL优化器SQL Tuning Advisor (STA),是oracle的sql优化补助工具。
其实优化sql主要有两个方案,其一是改写sql本身,改写sql需要对sql语法、数据库的执行方式都要有较好地理解。
其二就是这个STA,它属于DBMS_SQLTUNE包,它的主要作用是对于sql使用到的表创建正确的索引。
使用STA前提:
要保证优化器是CBO模式下。
show parameter OPTIMIZER_MODE
all_rows /*CBO,sql所有返回行都采用基于成本的方式运行*/
first_rows /*CBO,使用成本和试探法相结合的方法,查找一种可以最快返回前面少数行*/
first_rows_n /*CBO,全部采用基于成本的优化方法CBO,并以最快的速度,返回前N行记录*/
choose /*如果有统计信息,采用CBO,否则采用RBO*/
rule /*RBO*/
执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限:
grant advisor to scott;
如下是STA使用例子:
1.首先创建两个练习表obj与ind,仅创建表,无需创建索引:
SQL> create table obj as select * from dba_objects;
Table created
SQL> create table ind as select * from dba_indexes;
Table created
SQL> insert into obj select * from obj;
76714 rows inserted
SQL> insert into obj select * from obj;
153428 rows inserted
SQL> insert into obj select * from obj;
306856 rows inserted
SQL> insert into ind select * from ind;
5513 rows inserted
SQL> insert into ind select * from ind;
11026 rows inserted
SQL> insert into ind select * from ind;
22052 rows inserted
SQL>
2.然后对这两个表,obj与ind进行联合查询,并通过autotrace查看其执行计划:
SQL> set timing on
SQL> set autot trace
SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;
Elapsed: 00:00:00.72
Execution Plan
----------------------------------------------------------
Plan hash value: 380737209
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 2884 (1)| 00:00:35 |
| 1 | SORT AGGREGATE | | 1 |