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. 測試一
創建表
|
1
2
3
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. |
創建索引：
|
1
2
|
tpcc@TOADDB> create index t2_idx on t2(object_id);Index created. |
收集統計信息：
|
1
2
3
4
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
tpcc@TOADDB> set autotrace ontpcc@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---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
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---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |---------------------------------------------------------------------------------------| 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
|
1
2
3
4
5
6
7
8
9
10
|
tpcc@TOADDB> select sql_id,sql_text from v$sqlwhere sql_text like '%t1.object_name%';SQL_ID-------------SQL_TEXT----------------------------------------------------------------------------------------------------4zbqykx89yc8vselect t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id18bphz37dajq9select /*+ use_nl(t1 t2) index(t2) */t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' andt1.object_id=t2.object_id |
運行存儲過程如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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_114PL/SQL procedure successfully completed. |
查看task的名字
|
1
2
3
4
|
tpcc@TOADDB> print tuning_task;TUNING_TASK----------------------------------------------------------------------------------------------------TASK_114 |
查看執行報告
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
set long 99999col comments format a200SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual; COMMENTS----------------------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name : TASK_114Tuning Task Owner : TPCCWorkload Type : Single SQL StatementScope : COMPREHENSIVETime Limit(seconds): 1800Completion Status : COMPLETEDStarted at : 03/06/2016 05:27:21Completed at : 03/06/2016 05:27:24-------------------------------------------------------------------------------Schema Name: TPCCSQL ID : 4zbqykx89yc8vSQL 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---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 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---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |---------------------------------------------------------------------------------------| 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")------------------------------------------------------------------------------- |
接受分析建議
報告中給出了執行方法，如上紅色部分
接受報告的建議，驗證一下如下：
|
1
2
|
tpcc@TOADDB> execute dbms_sqltune.accept_sql_profile(task_name=> 'TASK_114',task_owner => 'TPCC', replace => TRUE);PL/SQL procedure successfully completed. |
執行測試
再執行原先命令如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
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---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |---------------------------------------------------------------------------------------| 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 planStatistics---------------------------------------------------------- 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. 維護操作
禁用命令
如下：
|
1
2
3
4
5
6
7
|
begindbms_sqltune.alter_sql_profile( name => 'SYS_SQLPROF_01534b8309b90000', attribute_name => 'status', value => 'disabled'); end; / |
啟用命令
如下：
|
1
2
3
4
5
6
7
|
begindbms_sqltune.alter_sql_profile( name => 'SYS_SQLPROF_01534b8309b90000', attribute_name => 'status', value => 'enabled'); end; / |
查看使用的PROFILE
如下：
|
1
|
SQL>SELECT task_name,status FROMUSER_ADVISOR_TASKS ; |
刪除PROFILE
|
1
2
3
|
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01534b8309b90000'); END; |
總結
以上所述是小編給大家介紹的Oracle固定執行計劃之SQL PROFILE概要文件 ，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
原文鏈接：https://www.cnblogs.com/DataArt/archive/2018/10/31/9881852.html
