通过SQL PROFILE自动优化SQL语句

最新推荐文章于 2022-07-27 18:23:35 发布

cuiliao2030

最新推荐文章于 2022-07-27 18:23:35 发布

阅读量126

点赞数

SQL的自动优化可以涉及以下操作：

SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)

此项功能一般都属于oracle企业版的一部分，不过sql的自动优化功能可以通过执行DBMS_SQLTUNE包来实现，如果你想通过调用sql automatic tune的API，则首先你需要具备以下权限：CREATE ANY SQL PROFILE,DROP ANY SQL_PROFILE和ALTER ANY SQL_PROFILE的系统权限。

使用SQL的自动优化功能：

你可以通过使用DBMS_SQLTUNE.ACCEPT_SQL_PROFILE过程来实现SQL的自动优化功能，执行此包后会自动在数据内生成和保存一个SQL得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');

END;

在以上的过程片段中my_sql_tuning_task是所执行的SQL优化的人物名，你同样可以访问DBA_SQL_PROFILES视图来得到更多关于SQL PROFILE的信息。

编辑SQL PROFILE的属性

你可以编译一个已经存在的SQL PROFILE的状态、名字、说明等属性，通过执行ALTER_SQL_PFOFILE过程来达到目的，例如：

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name=>'my_sql_profile',

attribute_name=>'STATUS',

value=>'DISABLE');

END;

在这个例子中my_sql_profile就是你需要修改的那个SQL PROFILE的名字，这个状态就是使你已经创建的一个SQL PROFILE设置为不可用状态。

删除一个SQL PROFILE

你可以通过执行DROP_SQL_PROFILE来达到删除一个SQL PROFILE的效果，例如：

BEGIN

DBMS_SQLTUNE.DROP_SQL_PROFILE(name=>'my_sql_profile');

END;

在此例中my_sql_profile就是你想要删除的那个SQL PROFILE名，你同样可以设置某些属性以达到忽略由于删除某个SQL PROFILE时不存在的异常，在默认情况下这个属性是出于FALSE状态，也就是不忽略错误。

下面是一个简单的完整的例子：

==================

SESSION 1 --SCOTT

==================

SQL> create table test (n number );

Table created.

declare
begin
for i in 1 .. 10000
loop
insert into test values(i);
commit;
end loop;
end;

PL/SQL procedure successfully completed.

create index test_idx on test(n);

Index created.

analyze table test estimate statistics（你也可以使用dbms_stats来实现)

Table analyzed.
select /*+ no_index(test test_idx) */ * from test where n=1

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=13)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6 Card=1 Bytes
=13)

===================

SESSION 2 -- SYS

====================

1 DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=
1';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'SCOTT',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'my_sql_tuning_task_2',
12 description => 'Task to tune a query on a specified table');
13* END;
14 /
PL/SQL procedure successfully completed.

1 BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
3* end;
SQL> /

PL/SQL procedure successfully completed.
1 SET LONG 1000
2 SET LONGCHUNKSIZE 1000
3 SET LINESIZE 100
4* SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
--------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/04/2004 17:36:05
Completed at : 05/04/2004 17:36:05

-------------------------------------------------------------------------------
SQL ID : d4wgpc5g0s0vu

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
--------------------
SQL Text: select /*+ no_index(test test_idx) */ * from test where n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 83.84%)

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
--------------------
------------------------------------------
Consider accepting the recommended

1 DECLARE
2 my_sqlprofile_name VARCHAR2(30);
3 BEGIN
4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
5 task_name => 'my_sql_tuning_task_2',
6 name => 'my_sql_profile');
7* END;
8 /

PL/SQL procedure successfully completed.

SQL> select to_char(sql_text) from dba_sql_profiles;

TO_CHAR(SQL_TEXT)
------------------------------------------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=1

SESSION 1 --- SCOTT

SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (INDEX) (Cost=1 Card=1 By
tes=13