oracle自动sql优化

oracle的优化器运行在下面的2个模式下：
1常规模式
优化器编译sql，生成执行计划，这个耗时一般很短
2优化模式
优化器进行额外的分析看是否能提供更好的执行计划，优化器的输出不是执行计划，而是一系列动作，在这个模式下的优化器就是Automatic Tuning Optimizer.这个阶段花费的时间一般都很长，只是对那些复杂，高负载的语句有意义。

Automatic Database Diagnostic Monitor (ADDM) 来标记那些语句是能优化的，自动化sql优化功能也会标识有问题的sql，在系统维护窗口中实施优化建议来作为一个自动化的维护任务。
看上去oracle背后智能的调整了很多东西。
自动优化器进行下面的一些操作。
1统计信息分析
2sql 探测
3访问路径分析
4sql结构分析
5可选计划分析

自动优化器会检查每个对象缺失或过期的统计信息。产生2中类型的输出：
1在自动优化器统计收集被关闭的情况下，提示需要收集过期的统计信息。
自动维护任务会在维护窗口运行维护任务。默认，自动统计信息是被开启的，在每个维护窗口运行
启动自动统计信息收集
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => ‘auto optimizer stats collection’
, operation => NULL
, window_name => NULL
);
END;
/
关闭
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘auto optimizer stats collection’
, operation => NULL
, window_name => NULL
);
END;
/
2补充没有统计信息的对象的统计信息，调整过期统计信息对象的因子。

sql profile是一个sql语句的辅助信息的集合。优化器是结合sql profile和坏境的信息一起生成了执行计划的。

你可以跟sql plan管理或独自使用sql profile,如果你使用sql计划管理，那么选取的计划一定是被计划基线启用的。
sql profile有下面的一些好处：
1与hint或存储概要不同，profile不会绑定到具体的计划或子计划上。
2不需要修改源码。
使用DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 来接受sql profile的时候，这个步骤在数据库中创建并存储sql profile

接受profile
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘my_sql_tuning_task’,
name => ‘my_sql_profile’,
profile_type => DBMS_SQLTUNE.PX_PROFILE,
force_match => TRUE );
END;
/
可以通过 DBA_SQL_PROFILES这个视图来查看啊profile的信息。
修改profile，下面的例子中在sql编译的期间禁用profile
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => ‘my_sql_profile’,
attribute_name => ‘STATUS’,
value => ‘DISABLED’);
END;
/

传输sql profile
To transport a SQL profile:

Use the CREATE_STGTAB_SQLPROF procedure to create a staging table where the SQL profiles will be exported.

The following example creates my_staging_table in the DBA1 schema:

BEGIN
DBMS_SQLTUNE.create_stgtab_sqlprof(
table_name => ‘my_staging_table’,
schema_name => ‘DBA1’ );
END;
/
Use the PACK_STGTAB_SQLPROF procedure to export SQL profiles into the staging table.

The following example populates dba1.my_staging_table with the SQL profile my_profile:

BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlprof(
profile_name => ‘my_profile’,
staging_table_name => ‘my_staging_table’,
staging_schema_owner => ‘dba1’ );
END;
/
Move the staging table to the database where the SQL profiles will be imported using the mechanism of choice (such as Oracle Data Pump or database link).

On the database where the SQL profiles will be imported, use the UNPACK_STGTAB_SQLPROF procedure to import SQL profiles from the staging table.

The following example shows how to import SQL profiles contained in the staging table:

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => TRUE,
staging_table_name => ‘my_staging_table’);
END;
/

sql优化信息视图：
DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS
sql优化集视图DBA_SQLSET, DBA_SQLSET_BINDS, DBA_SQLSET_STATEMENTS, and DBA_SQLSET_REFERENCES

sql优化顾问将一个或多个sql作为输入，调用自动优化器来进行sql优化，输出是建议或推荐。推荐是对象上的统计信息收集，新索引创建，重新组织sql,或创建sql profile。
数据库可以自动的在系统维护窗口使用sql优化顾问优化sql,这个时候叫自动sql优化顾问。
oracle会自动的将awr中的负载重的sql标记成候选者，来运行sql优化顾问，下面是流程图

启用自动sql优化
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => ‘sql tuning advisor’
, operation => NULL
, window_name => NULL
);
END;
/
关闭sql自动优化
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘sql tuning advisor’
, operation => NULL
, window_name => NULL
);
END;
/

You can pass a specific window name using the window_name parameter to enable or disable the task in certain maintenance windows only.

配置sql自动优化选项，下面是自动接收sql优化顾问产生的sql profle
BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => ‘ACCEPT_SQL_PROFILES’, value => ‘TRUE’);
END;
/

查看自动sql优化报告
下面的例子生成了一个txt报告
VARIABLE my_rept CLOB;
BEGIN
:my_rept :=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => NULL,
end_exec => NULL,
type => ‘TEXT’,
level => ‘TYPICAL’,
section => ‘ALL’,
object_id => NULL,
result_limit => NULL);
END;
/

PRINT :my_rept

你可以对一个或多个sql语句单独使用sql优化顾问，要条有多个sql，你需要创建一个sql优化集合，
sql优化顾问的输入可以是下面的几种：
1addm
2awr
3共享sql区
4sql优化集合
使用sql优化顾问的步骤：
Create a SQL tuning set (if tuning multiple SQL statements)

Create a SQL tuning task

Execute a SQL tuning task

Display the results of a SQL tuning task

Implement recommendations as appropriate

创建任务：
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘SELECT /+ ORDERED / * ’ ||
‘FROM employees e, locations l, departments d ’ ||
‘WHERE e.department_id = d.department_id AND ’ ||
‘l.location_id = d.location_id AND ’ ||
‘e.employee_id < :bnd’;

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => ‘HR’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘my_sql_tuning_task’,
description => ‘Task to tune a query on a specified employee’);
END;
/
查看用户下的任务
SELECT TASK_NAME FROM DBA_ADVISOR_LOG WHERE OWNER = ‘HR’;
配置一个任务
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => ‘my_sql_tuning_task’,
parameter => ‘TIME_LIMIT’, value => 300);
END;
/

执行任务：
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task’ );
END;
/

查看任务状态
SELECT status
FROM USER_ADVISOR_TASKS
WHERE task_name = ‘my_sql_tuning_task’;

检查sql优化顾问的进程：
SELECT SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USER_NAME = ‘HR’ AND TASK_NAME = ‘my_sql_tuning_task’;

显示优化任务的结果：
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task’)
FROM DUAL;