注：測試環境 win7+oracle10.2.0.4

基礎知識：

STA—sql tuning advisor；

使用sqlprofile 時需要建立 sql tuning task，也就是說要先搞清楚 sta 的使用情況，下面來看看sta 的使用

Sta 的使用可以通過em完成，也可以通過oracle db中的 dbms_sqltue完成；我比較習慣後者；dbms_sqltune的使用主要分為兩步：

—建立sql tuning task

—執行 sql tuning task

來看一個簡單的例項：

SQL> desc emp 名稱是否為空? 型別 —————————————————– ——– EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)SQL> show userUSER 為 “SCOTT”SQL> DECLARE 2 huosi_task1 VARCHAR2(30); 3 huosi_txt1 CLOB; 4 BEGIN 5 huosi_txt1 := ‘SELECT * ‘ || 6 ‘FROM EMP ‘ || ‘WHERE EMPNO=7369′ ; 7 huosi_task1 := DBMS_SQLTUNE.CREATE_TUNING_TASK( 8 sql_text => huosi_txt1, 9 user_name => ‘SCOTT’, 10 scope => ‘COMPREHENSIVE’, 11 time_limit => 60, 12 task_name => ‘test_task1′, 13 description => ‘first test task’); 14 END; 15 /PL/SQL 過程已成功完成。SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => ‘test_task1′);PL/SQL 過程已成功完成。SQL> select status from dba_advisor_log where task_name=’test_task1′;STATUS———————-COMPLETEDSQL> set long 65536SQL> set longchunksize 65536SQL> set linesize 100SQL> select dbms_sqltune.report_tuning_task(‘test_task1′) from dual 2 ;SQL>

注意此時沒有返回資料的原因是，sqlplus 不顯示lob資料，可以plsql檢視，如圖：

GENERAL INFORMATION SECTION——————————————————————————-Tuning Task Name : test_task1Tuning Task Owner : SCOTTScope : COMPREHENSIVETime Limit(seconds) : 60Completion Status : COMPLETEDStarted at : 05/29/2012 10:35:39Completed at : 05/29/2012 10:35:40Number of Statistic Findings : 1——————————————————————————-Schema Name: SCOTTSQL ID : 31qp81kj64a38SQL Text : SELECT * FROM EMP WHERE EMPNO=7369——————————————————————————-FINDINGS SECTION (1 finding)——————————————————————————-1- Statistics Finding——————— 表 “SCOTT”.”EMP” 及其索引的優化程式統計資訊已失效。 Recommendation ————– – 考慮收集此表的優化程式統計資訊。 execute dbms_stats.gather_table_stats(ownname => ‘SCOTT’, tabname => ‘EMP’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’); Rationale ——— 為了選擇好的執行計劃, 優化程式需要此表的最新統計資訊。——————————————————————————-EXPLAIN PLANS SECTION——————————————————————————-1- Original———–Plan hash value: 2949544139————————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |————————————————————————————–| 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |————————————————————————————–Predicate Information (identified by operation id):————————————————— 2 – access(“EMPNO”=7369)——————————————————————————-

到這裡 STA 的基本使用就完成了；

Sql profile :

Sql profile 是10g 的一個新特性，可以通過em 也可以通過dbms_sqltune進行管理；

Sql profile 本身是儲存在資料字典中的資訊的一個集合，這些資訊可以被cob使用，以使優化器能夠生成一個更優化的執行計劃；profile中的資訊能夠改善優化器中的cardinality和selectivity，從而使優化器能夠選擇更好的執行計劃；

當時sql profile 並不包括單個的執行計劃的資訊，當優化器選擇執行計劃時它本身會包含以下的資訊：

The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
The supplemental statistics in the SQL profile

Sql profile 本身並不會像 outline一樣固定一個sql 的執行計劃，比如當資料量發生變化時執行計劃可能就會變化；能夠應用sql profile 的語句包括：

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)

在10g當中，oracle CBO的功能進一步加強，也就是說oracle 的優化器會根據系統的統計資訊、sqlprofile等來自動選擇執行計劃，比如在某些情況下優化器會跳過hint；

下面來看看 sqlprofile是如何偷樑換柱，避開hint 的；

SQL> show userUSER 為 “SCOTT”SQL> create table test_sp(n number);表已建立。SQL> declare 2 begin 3 for i in 1 .. 10000 loop 4 insert into test_sp values(i); 5 commit; 6 end loop; 7 end; 8 /PL/SQL 過程已成功完成。

SQL> create index test_idx on test_sp(n);索引已建立。SQL> exec dbms_stats.gather_table_stats(”,’TEST_SP’);PL/SQL 過程已成功完成。SQL> set autotrace onSQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1 2 ; N———- 1執行計劃———————————————————-Plan hash value: 3988747878—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_SP | 1 | 3 | 7 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – filter(“N”=1)統計資訊———————————————————- 1 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>

此時可以看出 sql是進行的全表掃描；

重新開啟一個會話：

SQL> declare 2 my_task_name VARCHAR2(30); 3 my_sqltext CLOB; 4 begin 5 my_sqltext := ‘select /*+ no_index(test_sp test_idx) */ * from test_sp 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_3′, 12 description => ‘Task to tune a query on a specified table’); 13 end; 14 /PL/SQL 過程已成功完成。SQL> begin 2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_3′); 3 end; 4 /PL/SQL 過程已成功完成。SQL>

看看 sqlprofile的資訊；

GENERAL INFORMATION SECTION——————————————————————————-Tuning Task Name : my_sql_tuning_task_3Tuning Task Owner : SYSScope : COMPREHENSIVETime Limit(seconds) : 60Completion Status : COMPLETEDStarted at : 05/29/2012 11:32:13Completed at : 05/29/2012 11:32:13Number of SQL Profile Findings : 1——————————————————————————-Schema Name: SCOTTSQL ID : 1ks8q8x9ttbbySQL Text : select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1

——————————————————————————-FINDINGS SECTION (1 finding)——————————————————————————-1- SQL Profile Finding (see explain plans section below)——————————————————– 為此語句找到了效能更好的執行計劃。 Recommendation (estimated benefit: 86.4%) —————————————– – 考慮接受推薦的 SQL 概要檔案。 execute dbms_sqltune.accept_sql_profile(task_name => ‘my_sql_tuning_task_3′, replace => TRUE);——————————————————————————-EXPLAIN PLANS SECTION——————————————————————————-1- Original With Adjusted Cost——————————Plan hash value: 3988747878—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_SP | 1 | 3 | 7 (0)| 00:00:01 |—————————————————————————–

Predicate Information (identified by operation id):————————————————— 1 – filter(“N”=1)2- Using SQL Profile——————–Plan hash value: 2882402178—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – access(“N”=1)——————————————————————————-

SQL> DECLARE2 my_sqlprofile_name VARCHAR2(30);

3 begin

4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

5 task_name => ‘my_sql_tuning_task_3′,

6 name => ‘my_sql_profile’);

7 end;

8 /

PL/SQL 過程已成功完成。

SQL>

此時再次執行查詢語句：

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

N———- 1執行計劃———————————————————-Plan hash value: 2882402178—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – access(“N”=1)Note—– – SQL profile “my_sql_profile” used for this statement統計資訊———————————————————- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>

此時sql的執行已經跳開了hint，開始使用索引，也就是說現在優化器選擇一個比hint更優的執行計劃

此時再次修改下 sql：

SQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=2;

N———- 2執行計劃———————————————————-Plan hash value: 3988747878—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_SP | 1 | 3 | 7 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – filter(“N”=2)統計資訊———————————————————- 1 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>

可以發現，此時sql執行的仍舊為全表掃描，僅僅是將1變成了2，sqlprofile 就失去作用；

那麼對於此種literal sql 是否可以使用相同的sql profile呢，答案是肯定的，下面演示下：

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>’my_sql_tuning_task_3′, replace => TRUE, force_match=>true);PL/SQL 過程已成功完成。

此時sql profile將啟用force match，其作用型別crsor_sharing 的force設定；

此時再次執行sql（scott）：

SQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=2; N———- 2執行計劃———————————————————-Plan hash value: 2882402178—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – access(“N”=2)Note—– – SQL profile “SYS_SQLPROF_014de5abcc24c000″ used for this statement統計資訊———————————————————- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>

此時可以看出sql 已經開始使用索引；

通過以上的簡單例子可以發現對sql profile可以幫助用來更好的優化sql，所以掌握sql profile 是非常有必要的；

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

Oracle sqlprofile 偷樑換柱

基礎知識：

Sql profile :

相關文章