Oracle SQL优化之sql tuning advisor(STA)
Oracle SQL优化之sql tuning advisor(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;
例子：
SQL> create table obj as select * from dba_objects;
表已创建。
SQL> create table ind as select * from dba_indexes;
表已创建。
SQL> insert into obj select * from obj;
已创建 74603 行。
SQL> insert into obj select * from obj;
已创建 149206 行。
SQL> insert into obj select * from obj;
已创建 298412 行。
SQL> insert into ind select * from ind;
已创建 5134 行。
SQL> insert into ind select * from ind;
已创建 10268 行。
SQL> insert into ind select * from ind;
已创建 20536 行。
这里创建两张表，下面执行一个查询：
SQL> set timing on
SQL> set autot trace
SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;
已用时间: 00: 00: 00.15
执行计划
----------------------------------------------------------
Plan hash value: 380737209
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | | 5063 (1)| 00:01:01 |
| 1 | SORT AGGREGATE | | 1 | 83 | | | |
|* 2 | HASH JOIN | | 5861K| 463M| 1272K| 5063 (1)| 00:01:01 |
| 3 | TABLE ACCESS FULL| IND | 44789 | 743K| | 379 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| OBJ | 577K| 36M| | 2472 (1)| 00:00:30 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9 recursive calls
4 db block gets
10406 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
以上可以看到，在执行以上两个查询的时候，两张表走的全表扫和hash join。
使用STA：
第一步：创建优化任务
通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务：
SQL> set autot off
SQL> set timing off
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from obj o, ind i where o.object_name=i.index_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 30,
task_name => 'tuning_sql_test',
description => 'tuning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
END;
/
PL/SQL 过程已成功完成。
函数CREATE_TUNING_TASK，
sql_text是需要优化的语句，
user_name是该语句通过哪个用户执行，用户名大写，
scope是优化范围（limited或comprehensive），
time_limit优化过程的时间限制，
task_name优化任务名称，
description优化任务描述。
第二步： 执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL 过程已成功完成。
第三步：检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';
TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED
第四步：查看优化结果
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
SQL> set long 999999
SQL> set serveroutput on size 999999
SQL> set line 120
SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------
- 更多Oracle疑问解答：
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有，生产Oracle Rman 备份脚本的工具啊！
- 初学orcle，希望有大大帮忙解说一下详细步骤，从登录oracle到创建表的过程
- oracle语句问题：一张user表，三个字段，id,name,time，插入记录比如：张三2007，李四2008，张三2011
- 如何写一个ORACLE触发器同步两个表中的数据？
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G，在RMAN中如何按指定的时间恢复数据文件啊？
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊？