SQL profile是一個SQL語句的表或索引的物件級的統計資料。呼叫SQL Tuning Advisor進行SQL profile建立

在分析SQL語句時，SQL Tuning Advisor會使用一組特定的繫結值作為輸入，然後比較最佳化估計與執行該語句的片段上的資料取樣獲得的值。當發現顯著差異，SQL Tuning Advisor會捆綁糾正措施一起在一個SQL PROFILE，然後建議其接受。

在SQL PROFILE校正的統計資訊可以改進最佳化的基數估計，這反過來又導致最佳化器選擇更好的計劃。

1不同於hints 和 stored outline，SQL profile不指定最佳化器到一個特定的計劃或子計劃。 SQL profile修正不正確的估計，同時給最佳化器可以靈活地選擇最佳的方案。
2不同於hints，無需更改應用程式原始碼、。由資料庫使用SQL訪問是對使用者透明。

建立環境

SQL> create table profile_test tablespace users as select * from dba_objects;

Table created.

SQL> create index ix_objd on profile_test(object_id);

Index created.

SQL> set linesize 200 pagesize 2000

SQL> exec dbms_stats.gather_table_stats('','PROFILE_TEST');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;

SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;

Execution Plan

----------------------------------------------------------

Plan hash value: 663678050

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 93 | 444 (2)| 00:00:06 |

|* 1 | TABLE ACCESS FULL| PROFILE_TEST | 1 | 93 | 444 (2)| 00:00:06 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=5060)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

2470 consistent gets

0 physical reads

0 redo size

1413 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autotrace off

SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;

OWNER OBJECT_NAME SUBOBJECT_NAME

------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------

OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S

---------- -------------- ------------------- --------- --------- ------------------- ------- - - -

SYS SCHEDULER$_JOB_EXTERNAL

5060 TYPE 22-OCT-05 22-OCT-05 2005-10-22:21:49:42 VALID N N N

SQL> select sql_text,sql_id from v$sql a where a.SQL_FULLTEXT like '%profile_test%' and a.SQL_FULLTEXT like '%FULL%';

SQL_TEXT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID

-------------

EXPLAIN PLAN SET STATEMENT_ID='PLUS4308718' FOR select /*+ FULL( profile_test) */ * from profile_test where object_id=5060

dsd1y6w0rgsj8

select /*+ FULL( profile_test) */ * from profile_test where object_id=5060

9xjbpjspsvws4

1 建立和執行最佳化任務

支援sql_id和sql text兩種方式

SQL> DECLARE

2 my_task_name VARCHAR2(50);

3 my_sql_id VARCHAR2(64);

4 BEGIN

5 my_sql_id := '5xg48sy8tjbp5';

6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

7 sql_id => '9xjbpjspsvws4',

8 scope => 'COMPREHENSIVE',

9 time_limit => 60,

10 task_name => '9xjbpjspsvws4_tuning_task',

11 description => 'Task to tune a query on a specified table');

12 END;

13 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '9xjbpjspsvws4_tuning_task');

3 end;

4 /

PL/SQL procedure successfully completed.

2檢視最佳化報告

SQL> SELECT to_char(DBMS_SQLTUNE.REPORT_TUNING_TASK( '9xjbpjspsvws4_tuning_task')) from DUAL;

TO_CHAR(DBMS_SQLTUNE.REPORT_TUNING_TASK('9XJBPJSPSVWS4_TUNING_TASK'))

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : 9xjbpjspsvws4_tuning_task

Tuning Task Owner : TEST

Scope : COMPREHENSIVE

Time Limit(seconds) : 60

Completion Status : COMPLETED

Started at : 07/24/2014 11:49:53

Completed at : 07/24/2014 11:49:53

Number of SQL Profile Findings : 1

-------------------------------------------------------------------------------

Schema Name: TEST

SQL ID : 9xjbpjspsvws4

SQL Text : select /*+ FULL( profile_test) */ * from profile_test where

object_id=5060

-------------------------------------------------------------------------------

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: 99.54%)

------------------------------------------

- Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name =>

'9xjbpjspsvws4_tuning_task', replace => TRUE);

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

1- Original With Adjusted Cost

------------------------------

Plan hash value: 663678050

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 93 | 444 (2)| 00:00:06 |

|* 1 | TABLE ACCESS FULL| PROFILE_TEST | 1 | 93 | 444 (2)| 00:00:06 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=5060)

2- Using SQL Profile

--------------------

Plan hash value: 938196023

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| PROFILE_TEST | 1 | 93 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IX_OBJD | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=5060)

3 接受sql profile

execute dbms_sqltune.accept_sql_profile(task_name =>

'9xjbpjspsvws4_tuning_task', replace => TRUE);

PL/SQL procedure successfully completed.

4 再次檢視執行計劃

SQL> set autotrace traceonly;

SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;

Execution Plan

----------------------------------------------------------

Plan hash value: 938196023

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| PROFILE_TEST | 1 | 93 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IX_OBJD | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=5060)

Note

-----

- SQL profile "SYS_SQLPROF_0151d99cf8b60000" used for this statement

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

1417 bytes sent via SQL*Net to client

469 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

5 刪除最佳化任務

SQL> begin

2 dbms_sqltune.drop_tuning_task('9xjbpjspsvws4_tuning_task');

3 end;

4 /

PL/SQL procedure successfully completed

6 刪除sql pfofile

SQL> BEGIN

2 DBMS_SQLTUNE.DROP_SQL_PROFILE (

3 name => 'SYS_SQLPROF_0151d99cf8b60000'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

SQL PROFILE 測試

相關文章