Oracle深度學(xué)習(xí)筆記——概要文件

1、25.oracle 深度學(xué)習(xí)筆記sql profiles要文件1. 引子oracle 系統(tǒng)為了合理分配和使用系統(tǒng)的資源提出了概要文件的概念。 所謂概要文件， 就 是一份描述如何使用系統(tǒng)的資源(主要是cpu資源)的配置文件。將概要文件賦予某個(gè)數(shù)據(jù)庫(kù)用戶，在用戶連接并訪問(wèn)數(shù)據(jù)庫(kù)服務(wù)器時(shí)，系統(tǒng)就按照概要文件給他分配資源。包括 :1 、管理數(shù)據(jù)庫(kù)系統(tǒng)資源。 利用profile來(lái)分配資源限額，必須把初始化參數(shù)resource_limit設(shè)置為true默認(rèn)是true的。2、管理數(shù)據(jù)庫(kù)口令及驗(yàn)證方式。默認(rèn)給用戶分配的是default既要文件，將該文件賦予了每個(gè)創(chuàng)建的用戶。但該文件對(duì)資源沒(méi)有任何限制，因此管理

2、員常常需要根據(jù)自己數(shù)據(jù)庫(kù)系統(tǒng)的環(huán)境自行建立概要文件。2. 概要文件限制概要文件主要可以對(duì) 數(shù)據(jù)庫(kù)系統(tǒng)如下指標(biāo)進(jìn)行限制。1)用戶的最大并發(fā)會(huì)話數(shù)(session_per_user)2)每個(gè)會(huì)話的cpu 時(shí)鐘限制(cpu_per_sessio)n3) 每次調(diào)用的 cpu 時(shí)鐘限制， 調(diào)用包含解析、 執(zhí)行命令和獲取數(shù)據(jù)等等。( cpu_per_cal)l4)最長(zhǎng)連接時(shí)間。一個(gè)會(huì)話的連接時(shí)間超過(guò)指定時(shí)間之后，oracle 會(huì)自動(dòng)的斷開(kāi)連接( connect_time)5)最長(zhǎng)空閑時(shí)間。如果一個(gè)會(huì)話處于空閑狀態(tài)超過(guò)指定時(shí)間，oracle 會(huì)自動(dòng)斷開(kāi)連接( idle_time)6)每個(gè)會(huì)話可以讀取的最大數(shù)

3、據(jù)塊數(shù)量(logical_reads_per_sess)ion7)每次調(diào)用可以讀取的最大數(shù)據(jù)塊數(shù)量(logical_reads_per_ca)ll8) sga私有區(qū)域的最大容量(private_sga概要文件對(duì) 口令的定義和限制 如下：1)登錄失敗的最大嘗試次數(shù)(failed_login_attempt)s2)口令的最長(zhǎng)有效期(password_life_time)3)口令在可以重用之前必須修改的次數(shù)(password_reuse_max)4)口令在可以重用之前必須經(jīng)過(guò)的天數(shù)(password_reuse_time)5)超過(guò)登錄失敗的最大允許嘗試次數(shù)后，賬戶被鎖定的天數(shù)6)指定用于判斷口令復(fù)雜

4、度的函數(shù)名在指定概要文件之后， dba 可以手工的將概要文件賦予每個(gè)用戶。 但是概要文件不是立即生效，而是要將初始化參數(shù)文件中的參數(shù)resource_limi改置為true之后，概要文件才會(huì)生效。3. sql profilesql profil施oracle10g中弓|入，主要目的側(cè)重于 sql優(yōu)化，彌補(bǔ)了存儲(chǔ)概要的缺點(diǎn). dba可以使用sql調(diào)整顧問(wèn)（stan sql訪問(wèn)顧問(wèn)（saa誄識(shí)別可以得到更好性能的 sql語(yǔ)句， 這些語(yǔ)句可以保存在 sql調(diào)整集、一個(gè) awr快照或保存在當(dāng)前的庫(kù)緩存中，一旦識(shí)別 出調(diào)整候選者，這些顧問(wèn)程序就開(kāi)始分析捕獲到的語(yǔ)句以期獲得更好的性能，然后生成專用的語(yǔ)句擴(kuò)

5、展（就叫做sql配置文件）并重寫sql語(yǔ)句以在執(zhí)行期間獲取更佳的性能。與存儲(chǔ)概要類似，一個(gè) sql配置文件提供了使用更好的執(zhí)行計(jì)劃的能力（如果這個(gè)執(zhí)行計(jì)劃是可用的），sql配置文件也可以象存儲(chǔ)概要一樣分階段執(zhí)行，或限制到對(duì)特定會(huì)話才能 執(zhí)行該sql配置文件，但是大多數(shù)重要的改進(jìn)超過(guò)了存儲(chǔ)概要sql profile對(duì)于一下類型語(yǔ)句有效：selec研句；update語(yǔ)句；insert語(yǔ)句（僅當(dāng)使用 select?句時(shí)有效）；deleted 句；created句（僅當(dāng)使用 select?句時(shí)有效）；merge語(yǔ)句（僅當(dāng)作 updated insert作時(shí)有效）。另外，使用 sql profile還必

6、須有 create any sql profjidrop any sql profile alter any sql profil繇系統(tǒng)權(quán)限。4. 測(cè)試一創(chuàng)建表tpcctoaddb create table t1 as select object_id,object_name from dba_objects where rownum create table t2 as select * from dba_objects;table created.創(chuàng)建索引：tpcctoaddb create index t2_idx on t2(object_id);index created.收集統(tǒng)計(jì)信

7、息：tpcctoaddb exec dbms_stats.gather_table_stats(user,t1,cascade=true,method_opt=forall columns size 1);pl/sql procedure successfully completed.tpcctoaddb exec dbms_stats.gather_table_stats(user,t1,cascade=true,method_opt=forall columns size 1);pl/sql procedure successfully completed.執(zhí)行無(wú)hint的sqltpcct

8、oaddb set autotrace ontpcctoaddb select t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_id;42 rows selected.execution planplan hash value: 1838229974| id | operation| name | rows | bytes | cost (%cpu)| time|0 | select statement | 2500 |97k|498(1)| 00:00:01 |*1 | ha

9、sh 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 by operation id):1 - access(t1.object_id=t2.object_id)2 - filter(t1.object_name like %t1% and t1.object_name is

10、not null)statistics0 recursive calls0 db block gets1789 consistent gets0 physical reads0 redo size2350 bytes sent via sql*net to client573 bytes received via sql*net from client0 sql*net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 2 rows processed執(zhí)行帶 hint 的 sqlsqlselect /*+ use_nl(t1 t2

11、) 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 planplan 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

12、(1)| 00:00:012 | nested loops|2500 |97k|5069(1)| 00:00:01*3 | table access full | t1|2500 | 72500 |68 (0)| 00:00:01 |1 (0)| 00:00:01 |11 |2(0)| 00:00:01*4 | index range scan | t2_idx |1 |5 | table access by index rowid| t2 |1 |predicate information (identified by operation id):3 - filter(t1.object_n

13、ame like %t1% and t1.object_name is not null)4 - access(t1.object_id=t2.object_id)statistics1 recursive calls0 db block gets304 consistent gets24 physical reads0 redo size2350 bytes sent via sql*net to client573 bytes received via sql*net from client4 sql*net roundtrips to/from client0 sorts (memory

14、)0 sorts (disk)42 rows processed使用 sql profile查找執(zhí)行 sql的sql_idtpcctoaddb select sql_id,sql_text from v$sql where sql_text like %t1.object_name%;sql_idsql_text4zbqykx89yc8vselect t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_id18bphz37dajq9select /*+ use_nl(t1 t2)

15、index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like %t1% andt1.object_id=t2.object_id運(yùn)行存儲(chǔ)過(guò)程如下：var tuning_task varchar2(100);declarel_sql_id v$session.prev_sql_id%type;l_tuning_task varchar2(30);beginl_sql_id:= 4zbqykx89yc8v;l tuning task := dbms sqltune.create tuning task(sql id = l sql

16、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的名字tpcctoaddb print tuning_task;tuning_tasktask_114查看執(zhí)行報(bào)告set long 99999col comments format a200select dbms_sqltune.report_tun

17、ing_task(:tuning_task)comments from dual; commentsgeneral information sectiontuning 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:2

18、7:24schema name: tpccsql id: 4zbqykx89yc8vsql text : select t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_idfindings section (1 finding)1- sql profile finding (see explain plans section below) a potentially better execution plan was found for this statement.recom

19、mendation (estimated benefit: 83.08%) -consider accepting the recommended sql profile.execute dbms_sqltune.accept_sql_profile(task_name = task_114, task_owner = tpcc, replace = true);validation resultsthe sql profile was tested by executing both its plan and the original plan and measuring their res

20、pective execution statistics. a plan may have been only partially executed if the other could be run to completion in less time.completion status:completecompleteelapsed time (s):.012865.00455664.58 %cpu time (s):.0124.004563.7 %user i/o time (s):00buffer gets:178730283.1 %physical read requests:00p

21、hysical write requests:00physical read bytes:00physical write bytes:00rows processed:4242fetches:4242executions:11notes1. statistics for the original plan were averaged over 10 executions.2. statistics for the sql profile plan were averaged over 10 executions.explain plans section1- original with ad

22、justed costplan hash value: 1838229974| id | operation| name | rows | bytes | cost (%cpu)| time|0 | select statement |hash join|42 |table access full| t1 |42 |1680 |1680 |42 |table access full| t2 | 92021 |4981218 |988k|498(1)| 00:00:01 |(1)| 00:00:01 |68430(0)| 00:00:01 |(1)| 00:00:01 |predicate in

23、formation (identified by operation 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 profileplan hash value: 1022743391| id | operation| name | rows | bytes | cost (%cpu)| time |0 |select statement|42 |1680 |152 (0)| 00:00:01 |1

24、|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:01predicate information (identified by operation id):3

25、 - filter(t1.object_name like %t1% and t1.object_name is not null)4 - access(t1.object_id=t2.object_id)接受分析建議報(bào)告中給出了執(zhí)行方法，如上紅色部分接受報(bào)告的建議，驗(yàn)證一下如下：tpcctoaddb execute dbms_sqltune.accept_sql_profile(task_name = task_114,task_owner = tpcc, replace = true);pl/sql procedure successfully completed.執(zhí)行測(cè)試再執(zhí)行原先命令如

26、下：tpcctoaddb select t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_id;42 rows selected.execution planplan hash value: 1022743391| id | operation| name | rows | bytes | cost (%cpu)| time1 |nested loops|42 |1680 |2 |nested loops|42 |1680 |3 |table access full| t1|42 |1218 |4 |index range scan| t2_idx |1 |5 |table access by index rowid| t2 |1|110 | select statement |152 (0)| 00:00:01 |152 (0)| 00:00:01 |68 (0)| 00:00:01 |1 (0)| 00:00:01 |2(0)| 00:00:01|42 |1680 |152 (0)| 00:00:01 |predicate information (identified by operation id):3 - filter