.Oracle固定執行計劃之SQL PROFILE概要檔案

1. 引子
Oracle系統為了合理分配和使用系統的資源提出了概要檔案的概念。所謂概要檔案，就是一份描述如何使用系統的資源(主要是CPU資源)的配置檔案。將概要檔案賦予某個資料庫使用者，在使用者連線並訪問資料庫伺服器時，系統就按照概要檔案給他分配資源。

包括:

1、管理資料庫系統資源。
利用Profile來分配資源限額，必須把初始化引數resource_limit設定為true預設是TRUE的。
2、管理資料庫口令及驗證方式。
預設給使用者分配的是DEFAULT概要檔案，將該檔案賦予了每個建立的使用者。但該檔案對資源沒有任何限制，因此管理員常常需要根據自己資料庫系統的環境自行建立概要檔案。

2. 概要檔案限制
概要檔案主要可以對資料庫系統如下指標進行限制。

1）使用者的最大併發會話數（SESSION_PER_USER)

2）每個會話的CPU時鐘限制（CPU_PER_SESSION）

3）每次呼叫的CPU時鐘限制，呼叫包含解析、執行命令和獲取資料等等。（CPU_PER_CALL）

4）最長連線時間。一個會話的連線時間超過指定時間之後，Oracle會自動的斷開連線（CONNECT_TIME）

5）最長空閒時間。如果一個會話處於空閒狀態超過指定時間，Oracle會自動斷開連線（IDLE_TIME）

6）每個會話可以讀取的最大資料塊數量（LOGICAL_READS_PER_SESSION）

7）每次呼叫可以讀取的最大資料塊數量（LOGICAL_READS_PER_CALL）

8）SGA私有區域的最大容量（PRIVATE_SGA）

概要檔案對口令的定義和限制如下：

1）登入失敗的最大嘗試次數（FAILED_LOGIN_ATTEMPTS）

2）口令的最長有效期(PASSWORD_LIFE_TIME)

3）口令在可以重用之前必須修改的次數(PASSWORD_REUSE_MAX)

4）口令在可以重用之前必須經過的天數(PASSWORD_REUSE_TIME)

5）超過登入失敗的最大允許嘗試次數後，賬戶被鎖定的天數

6）指定用於判斷口令複雜度的函式名

在指定概要檔案之後，DBA可以手工的將概要檔案賦予每個使用者。但是概要檔案不是立即生效，而是要將初始化引數檔案中的引數RESOURCE_LIMIT設定為TRUE之後，概要檔案才會生效。

3. SQL PROFILE
SQL PROFILE在ORACLE10g中引入，主要目的側重於SQL優化，彌補了儲存概要的缺點.

DBA可以使用SQL調整顧問(STA)或SQL訪問顧問(SAA)來識別可以得到更好效能的SQL語句，

這些語句可以儲存在SQL調整集、一個AWR快照或儲存在當前的庫快取中，一旦識別出調整候選者，這些顧問程式就開始分析捕獲到的語句以期獲得更好的效能，然後生成專用的語句擴充套件(就叫做SQL配置檔案)並重寫SQL語句以在執行期間獲取更佳的效能。

與儲存概要類似，一個SQL配置檔案提供了使用更好的執行計劃的能力(如果這個執行計

劃是可用的)，SQL配置檔案也可以象儲存概要一樣分階段執行，或限制到對特定會話才能執行該SQL配置檔案，但是大多數重要的改進超過了儲存概要.

SQLProfile對於一下型別語句有效：

SELECT語句；

UPDATE語句；

INSERT語句（僅當使用SELECT子句時有效）；

DELETE語句；

CREATE語句（僅當使用SELECT子句時有效）；

MERGE語句（僅當作UPDATE和INSERT操作時有效）。

另外，使用SQL Profile還必須有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系統許可權。

4. 測試一
建立表

tpcc@TOADDB> create table t1 as selectobject_id,object_name from dba_objects where rownum<=50000;

Table created.

tpcc@TOADDB> create table t2 as select * fromdba_objects;

Table created.

建立索引：

tpcc@TOADDB> create index t2_idx on t2(object_id);

Index created.

收集統計資訊：

tpcc@TOADDB> execdbms_stats.gather_table_stats(user,`t1`,cascade=>true,method_opt=>`forall columns size 1`);

PL/SQL procedure successfully completed.

tpcc@TOADDB> execdbms_stats.gather_table_stats(user,`t1`,cascade=>true,method_opt=>`forall columns size 1`);

PL/SQL procedure successfully completed.

執行無HINT的SQL
tpcc@TOADDB> set autotrace on

tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like `%T1%` and t1.object_id=t2.object_id;

42 rows selected.

Execution Plan

———————————————————-

Plan hash value: 1838229974

—————————————————————————

—————————————————————————

| 0| SELECT STATEMENT | | 2500 | 97K| 498 (1)| 00:00:01 |

|* 1| HASH JOIN | | 2500 | 97K| 498 (1)| 00:00:01 |

|* 2| TABLE ACCESS FULL| T1 | 2500 | 72500 | 68 (0)| 00:00:01 |

| 3| TABLE ACCESS FULL| T2 | 92021 | 988K| 430 (1)| 00:00:01 |

—————————————————————————

Predicate Information (identified byoperation id):

—————————————————

1-access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

2- filter(“T1″.”OBJECT_NAME” LIKE `%T1%` AND”T1″.”OBJECT_NAME” IS

NOT NULL)

Statistics

———————————————————-

1 recursive calls

0 dbblock gets

1789 consistent gets

0 physical reads

0 redosize

2350 bytes sent via SQL*Net toclient

573 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

42 rowsprocessed

執行帶Hint的SQL
SQL>select /*+ use_nl(t1 t2) index(t2)*/ t1.*,t2.owner from t1,t2 where t1.object_name like `%T1%` and t1.object_id=t2.object_id;

42 rows selected.

Execution Plan

———————————————————-

Plan hash value: 1022743391

—————————————————————————————

—————————————————————————————

| 0| SELECT STATEMENT | | 2500 | 97K| 5069 (1)|00:00:01 |

| 1| NESTED LOOPS | | 2500| 97K| 5069 (1)| 00:00:01 |

| 2| NESTED LOOPS | | 2500 | 97K| 5069 (1)| 00:00:01 |

|* 3| TABLE ACCESS FULL | T1 | 2500 | 72500 | 68 (0)|00:00:01 |

|* 4| INDEX RANGE SCAN |T2_IDX | 1 | | 1 (0)| 00:00:01 |

| 5| TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|00:00:01 |

—————————————————————————————

Predicate Information (identified byoperation id):

—————————————————

3- filter(“T1″.”OBJECT_NAME” LIKE `%T1%` AND”T1″.”OBJECT_NAME” IS NOT

NULL)

4-access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Statistics

———————————————————-

1 recursive calls

0 dbblock gets

304 consistent gets

24 physical reads

0 redosize

2350 bytes sent via SQL*Net toclient

573 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

42 rowsprocessed

使用SQL PROFILE
查詢執行SQL的SQL_ID

tpcc@TOADDB> select sql_id,sql_text from v$sqlwhere sql_text like `%t1.object_name%`;

SQL_ID

————-

SQL_TEXT

—————————————————————————————————-

4zbqykx89yc8v

select t1.*,t2.owner from t1,t2 wheret1.object_name like `%T1%` and t1.object_id=t2.object_id

18bphz37dajq9

select /*+ use_nl(t1 t2) index(t2) */t1.*,t2.owner from t1,t2 where t1.object_name like `%T1%` and

t1.object_id=t2.object_id

執行儲存過程如下：

var tuning_task varchar2(100);
DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task VARCHAR2(30);
BEGIN
l_sql_id:=`4zbqykx89yc8v`;
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id =>l_sql_id);
:tuning_task:=l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
END;
/

TASK_114

PL/SQL procedure successfully completed.

檢視task的名字

tpcc@TOADDB> print tuning_task;

TUNING_TASK

—————————————————————————————————-

TASK_114

檢視執行報告

set long 99999

col comments format a200
SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual;

COMMENTS

—————————————————————————————————-

GENERAL INFORMATION SECTION

——————————————————————————-

Tuning Task Name : TASK_114

Tuning Task Owner : TPCC

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status : COMPLETED

Started at : 03/06/2016 05:27:21

Completed at : 03/06/2016 05:27:24

——————————————————————————-

Schema Name: TPCC

SQL ID : 4zbqykx89yc8v

SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like `%T1%`

and t1.object_id=t2.object_id

——————————————————————————-

FINDINGS SECTION (1 finding)

——————————————————————————-

1- SQL Profile Finding (see explain planssection below)

——————————————————–

Apotentially better execution plan was found for this statement.

Recommendation (estimated benefit: 83.08%)

——————————————

-Consider accepting the recommended SQL profile.

executedbms_sqltune.accept_sql_profile(task_name => `TASK_114`,

task_owner =>`TPCC`, replace => TRUE);

Validation results

——————

TheSQL profile was tested by executing both its plan and the original plan

andmeasuring their respective execution statistics. A plan may have been

only partially executed if the other could be run to completion in lesstime.

Original Plan With SQL Profile % Improved

————- —————- ———-

Completion Status: COMPLETE COMPLETE

Elapsed Time (s): .012865 .004556 64.58 %

CPUTime (s): .0124 .0045 63.7%

User I/O Time (s): 0 0

Buffer Gets: 1787 302 83.1%

Physical Read Requests: 0 0

Physical Write Requests: 0 0

Physical Read Bytes: 0 0

Physical Write Bytes: 0 0

Rows Processed: 42 42

Fetches: 42 42

Executions: 1 1

Notes

—–

1.Statistics for the original plan were averaged over 10 executions.

2.Statistics for the SQL profile plan were averaged over 10 executions.

——————————————————————————-

EXPLAIN PLANS SECTION

——————————————————————————-

1- Original With Adjusted Cost

——————————

Plan hash value: 1838229974

—————————————————————————

—————————————————————————

| 0| SELECT STATEMENT | | 42 | 1680 | 498 (1)| 00:00:01 |

|* 1| HASH JOIN | | 42 | 1680 | 498 (1)| 00:00:01 |

|* 2| TABLE ACCESS FULL| T1 | 42 | 1218 | 68 (0)| 00:00:01 |

| 3| TABLE ACCESS FULL| T2 | 92021 | 988K| 430 (1)| 00:00:01 |

—————————————————————————

Predicate Information (identified byoperation id):

—————————————————

1-access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

2- filter(“T1″.”OBJECT_NAME” LIKE `%T1%` AND”T1″.”OBJECT_NAME” IS

NOT NULL)

2- Using SQL Profile

——————–

Plan hash value: 1022743391

—————————————————————————————

—————————————————————————————

| 0| SELECT STATEMENT | | 42 | 1680 | 152 (0)|00:00:01 |

| 1| NESTED LOOPS | | 42 | 1680 | 152 (0)| 00:00:01 |

| 2| NESTED LOOPS | | 42 | 1680 | 152 (0)| 00:00:01 |

|* 3| TABLE ACCESS FULL | T1 | 42 | 1218 | 68 (0)| 00:00:01 |

|* 4| INDEX RANGE SCAN |T2_IDX | 1 | | 1 (0)| 00:00:01 |

| 5| TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|00:00:01 |

—————————————————————————————

Predicate Information (identified byoperation id):

—————————————————

3- filter(“T1″.”OBJECT_NAME” LIKE `%T1%` AND”T1″.”OBJECT_NAME” IS NOT

NULL)

4-access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

——————————————————————————-

接受分析建議
報告中給出了執行方法，如上紅色部分

接受報告的建議，驗證一下如下：

tpcc@TOADDB> execute dbms_sqltune.accept_sql_profile(task_name=> `TASK_114`,task_owner => `TPCC`, replace => TRUE);

PL/SQL procedure successfully completed.

執行測試
再執行原先命令如下：

tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like `%T1%` and t1.object_id=t2.object_id;

42 rows selected.

Execution Plan

———————————————————-

Plan hash value: 1022743391

—————————————————————————————

—————————————————————————————

| 0| SELECT STATEMENT | | 42 | 1680 | 152 (0)|00:00:01 |

| 1| NESTED LOOPS | | 42| 1680 | 152 (0)|00:00:01 |

| 2| NESTED LOOPS | | 42 | 1680 | 152 (0)| 00:00:01 |

|* 3| TABLE ACCESS FULL | T1 | 42 | 1218 | 68 (0)| 00:00:01 |

|* 4| INDEX RANGE SCAN |T2_IDX | 1 | | 1 (0)| 00:00:01 |

| 5| TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|00:00:01 |

—————————————————————————————

Predicate Information (identified byoperation id):

—————————————————

3- filter(“T1″.”OBJECT_NAME” LIKE `%T1%` AND”T1″.”OBJECT_NAME” IS NOT

NULL)

4-access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

Note

—–

– SQL profile”SYS_SQLPROF_01534b8309b90000″ used for this statement

– this is an adaptive plan

Statistics

———————————————————-

35 recursive calls

0 dbblock gets

317 consistent gets

1 physical reads

0 redosize

2350 bytes sent via SQL*Net toclient

573 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

42 rowsprocessed

啟用了PROFILE，PS：如果執行中多加幾個空格，並不會影響PROFILE的生效的。

5. 維護操作
禁用命令
如下：

begin

dbms_sqltune.alter_sql_profile(

name => `SYS_SQLPROF_01534b8309b90000`,

attribute_name => `status`,

value => `disabled`);

end;

/

啟用命令
如下：

begin

dbms_sqltune.alter_sql_profile(

name => `SYS_SQLPROF_01534b8309b90000`,

attribute_name => `status`,

value => `enabled`);

end;

/

檢視使用的PROFILE
如下：

SQL>SELECT task_name,status FROMUSER_ADVISOR_TASKS ;

刪除PROFILE
BEGIN

DBMS_SQLTUNE.DROP_SQL_PROFILE(name => `SYS_SQLPROF_01534b8309b90000`);

END;

/
———————
作者：badman250
來源：CSDN
原文：https://blog.csdn.net/notbaron/article/details/50830910
版權宣告：本文為博主原創文章，轉載請附上博文連結！

.Oracle固定執行計劃之SQL PROFILE概要檔案

相關文章