通過SQL PROFILE自動優化SQL語句

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

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10130206/viewspace-1035332/，如需轉載，請註明出處，否則將追究法律責任。