1、數(shù)據(jù)庫流程方法論及SQL執(zhí)行剖析技術(shù)創(chuàng)新，變革未來 目錄流程方法論介紹與共享池 流程分析方法論什么是流程、資源：流程：作業(yè)、操作、資源：硬資源：、內(nèi)存、，軟資源：、隊(duì)列鎖、等。共有哪些流程前臺進(jìn)程：登陸、數(shù)據(jù)訪問、語句解析、語句執(zhí)行、和提交后臺進(jìn)程：寫流程、寫臟塊流程、檢查點(diǎn)流程 數(shù)據(jù)庫流程介紹前臺進(jìn)程流程登陸數(shù)據(jù)訪問語句解析語句執(zhí)行后臺進(jìn)程流程LGWR寫Redo流程DBWR寫臟塊流程CKPT檢查點(diǎn)流程 數(shù)據(jù)庫流程介紹：數(shù)據(jù)訪問流程輸入指標(biāo)：邏輯讀：邏輯讀次數(shù)，細(xì)化每事務(wù)邏輯讀、每執(zhí)行邏輯讀、每調(diào)用邏輯讀。事務(wù)數(shù)：輸出指標(biāo)：執(zhí)行時間：sql execute elapsed time SQL與共

2、享池：內(nèi)存分配堆(Top Heap)區(qū)Chunk子堆區(qū)假設(shè)此子堆中包含兩個區(qū)，哪么， 這兩個區(qū)的大小是一樣的，并且分別 對應(yīng)父堆中兩個大小一致的Chunk。注意：區(qū)的大小是統(tǒng)一的。Chunk大小各不相同。DS:38b864528Chunk :38b861948Chunk: 38b858f98Chunk : 38b859f98Chunk:38b85af98Extent 0Extent 1Extent 2Extent 3子堆在DUMP結(jié)果中觀察堆內(nèi)存：ALTER SESSION SET EVENTSimmediate trace name heapdump level 2; SQL與共享池：內(nèi)存分

3、配 SQL與共享池：內(nèi)存分配 SQL與共享池： x$ksmsp共享池Chunk的觀察：select count(*),round(sum(KSMCHSIZ)/1024/1024,0) TOTAL_SIZE_MB, round(avg(KSMCHSIZ),0) AVG_SIZEfrom x$ksmsp ;select KSMCHCLS,count(*),round(sum(KSMCHSIZ)/1024/1024,0) TOTAL_SIZE_MB, round(avg(KSMCHSIZ),0) AVG_SIZEfrom x$ksmspgroup by KSMCHCLS order by KSMCH

4、CLS; SQL與共享池： x$ksmsp SQL與共享池： x$ksmsp SQL與共享池：FreeList內(nèi)存分配與FreeList列表：ALTER SESSION SET EVENTSimmediate trace name heapdump level 2; SQL與共享池：保留池保留池的意義保留池大小相關(guān)參數(shù)：_shared_pool_reserved_min_alloc。不以對象為單位。以Chunk 為單位，Chunk大小大于此參數(shù)的，將 被放入保留池。 SQL與共享池：父子游標(biāo)為什么要有父游標(biāo)： SQL與共享池：父子游標(biāo)為什么要有父游標(biāo)： SQL與共享池：父子游標(biāo)為什么要有父游標(biāo)

5、： SQL與共享池：觀察游標(biāo)所占內(nèi)存觀察父游標(biāo)句柄所占內(nèi)存：selectKGLHDADR, KGLNAOBJfrom x$kglobwhere kglnaobj like select * from t1 where id=99 and KGLHDADR=KGLHDPAR;在DUMP文件中查找父游標(biāo)句柄：ALTER SESSION SET EVENTS immediate trace name heapdump level 2050; SQL與共享池：觀察游標(biāo)所占內(nèi)存select*from x$kglobwhere kglnaobj like select * from t1 where id

6、=99 and KGLHDADR=KGLHDPAR;父游標(biāo)句柄中的信息： SQL與共享池：觀察游標(biāo)所占內(nèi)存父游標(biāo)句柄所占內(nèi)存：SQL所占內(nèi)存： SQL與共享池：觀察游標(biāo)所占內(nèi)存游標(biāo)內(nèi)存統(tǒng)計(jì)：select SHARABLE_MEM from V$SQL where sql_text like select * from t1 where id=99;select SHARABLE_MEM from V$SQLAREA where sql_text like select * from t1 where id=99; select KGLOBHS0+KGLOBHS6+kglobt16 from x

7、$kglob where kglnaobj likeselect * from t1 where id=99 and KGLHDADRKGLHDPAR;以上三條語句，統(tǒng)計(jì)出的結(jié)果相同。它們都不包含父游標(biāo)所占內(nèi)存。父游標(biāo)所占內(nèi)存：select KGLOBHS0+KGLOBHS6+kglobt16 from x$kglob where kglnaobj like select * from t1 where id=99 and KGLHDADR=KGLHDPAR;游標(biāo)精確的內(nèi)存耗用：select sum(KGLOBHS0+KGLOBHS6+kglobt16) from x$kglob where

8、kglnaobj likeselect * from t1 where id=99; SQL與共享池：觀察游標(biāo)所占內(nèi)存 SQL與共享池：LRU觀察共享池的LRU：SQL set linesize 1000 SQL col KGLNAOBJ for a35SQL select KGLHDADR,KGLOBHD0,KGLOBHD6 from x$kglob where kglnaobj like select *from vage where id=1 and KGLHDADRKGLHDPAR;SELECT ksmchptr,ksmchcom, ksmchcls ,ksmchsiz FROM x$

9、ksmsp WHERE KSMCHPAR=上條語 句KGLOBHD0列值;在DUMP結(jié)果中搜索KSMCHPTR列值：ALTER SESSION SET EVENTS immediate trace name heapdump level 2;注意瞬時LRU和周期LRU的分隔符：SEPARATOR。select KGHLUTRN ,KGHLURCR from X$KGHLU;如果瞬時LRU鏈Chunk數(shù)量超過周期LRU的 3倍，說明大量只用一次的Chunk堆積在LRU中， 代表共享池太大了。X$KGHLU中的KGHLUFSH列代表進(jìn)程Flush 共享池的次數(shù)。Flush 共享池的主要目的之一，

10、就是合并相鄰的Chunk，這個操作只會在Oracle覺得共享池內(nèi)存緊張時做。還有一個列： KGHLUOPS，表示進(jìn)程Pin Chunk或釋放Chunk Pin的次數(shù)，我們可以認(rèn)為KGHLUOPS列代表 進(jìn)程操作Chunk的次數(shù)。Stev的另一個經(jīng)驗(yàn)值是，如果Flush 次數(shù)和操作Chunk的次數(shù)比小于1：20，說明共享池 太小了。這個比例，簡單點(diǎn)說，Stev認(rèn)為每20次操作Chunk，只有一次Flush Chunk操作， 還是可以接受的。如果再低，說明共享池有點(diǎn)小。 SQL與共享池：LRU SQL與共享池：4031共享池內(nèi)存不足4031報(bào)錯的注意事項(xiàng)： SQL與共享池：4031共享池內(nèi)存不足4

11、031報(bào)錯的注意事項(xiàng)：子堆分配內(nèi)存觸發(fā)內(nèi)存不足declaremsql varchar2(500);mcur number;beginfor i in 1.9000 loop mcur:=dbms_sql.open_cursor;msql:=select id1 from a1 where id2=|to_char(i); dbms_sql.parse(mcur,msql,dbms_sql.native); mstat:=dbms_sql.execute(mcur);end loop; end;/ SQL與共享池：4031共享池內(nèi)存不足觸發(fā)內(nèi)存不足的腳本： SQL與共享池： Session Ca

12、ched Cursor什么是Session Cached CursorSession Cached Cursor的作用Session Cached Cursor的影響 SQL與共享池： SQL執(zhí)行流程SQL declaremcur number;mstat number;v_name varchar2(40); 5 beginmcur:=dbms_sql.open_cursor;dbms_sql.parse(mcur,select name from vage where id=:x,dbms_sql.native);dbms_sql.bind_variable(mcur,:x,1);msta

13、t:=dbms_sql.execute(mcur);mstat:=dbms_sql.fetch_rows(mcur);dbms_sql.define_column(mcur,1,v_name,40);dbms_sql.column_value(mcur,1,v_name);dbms_output.put_line(查詢結(jié)果:|v_name);dbms_sql.close_cursor(mcur); 15 end;16 /查詢結(jié)果:ICOL$觀察SQL執(zhí)行流程的PL/SQL腳本：程序中第6行：“mcur:=dbms_sql.open_cursor”，這是任何SQL執(zhí)行的第一步：打開 游標(biāo)。程序第

14、7行“dbms_sql.parse(mcur,select name from vage whereid=:x,dbms_sql.native)”，這是第二步：解析。第8行“dbms_sql.bind_variable(mcur,:x,1)”，綁定。將綁定變量:x的值定為1。第9行“mstat:=dbms_sql.execute(mcur)”，執(zhí)行。對于DML來說，主要的操作都在執(zhí) 行階段完成。而對于Select來說，執(zhí)行階段只是抓取的前奏，為后面的抓取做一些準(zhǔn) 備工作。Select主要的工作都在后面的抓取階段完成。第10行“mstat:=dbms_sql.fetch_rows(mcur)”抓

15、取，進(jìn)行邏輯讀或物理讀，從數(shù)據(jù)文 件、Buffer Cache讀取滿足條件的數(shù)據(jù)行到PGA的SDU中（Session Data Unit）。最后兩行，第11、12行“dbms_sql.define_column(mcur,1,v_name,40)”、“dbms_sql.column_value(mcur,1,v_name)”，將SDU中的結(jié)果傳送給變量v_name。其 中第11行確定SQL語句中NAME列的值對應(yīng)變量v_name，第12行將PGA的SDU中的結(jié) 果傳給變量v_name。最后，第14行“dbms_sql.close_cursor(mcur)”，關(guān)閉游標(biāo)。 SQL與共享池： SQL

16、執(zhí)行流程SQL執(zhí)行流程總結(jié)：1、打開游標(biāo)2、解析3、綁定4、執(zhí)行5、抓取6、設(shè)定列與變量對應(yīng)關(guān)系7、將抓取到SDU中的結(jié)果傳送給對應(yīng)的客戶端變量。8、關(guān)閉游標(biāo)。 SQL與共享池： SQL執(zhí)行流程 SQL與共享池：共享池與鎖機(jī)制Oracle針對公共內(nèi)存（也就是SGA），共設(shè)置了5種內(nèi)存鎖：Library Cache Lock/PinBuffer PinRow Cache Lock各種LatchMutex SQL與共享池：共享池與鎖機(jī)制Oracle的重要特性：鎖池 SQL與共享池：共享池與鎖機(jī)制A進(jìn)程申請鎖： SQL與共享池：共享池與鎖機(jī)制A進(jìn)程申請鎖： SQL與共享池：共享池與鎖機(jī)制A進(jìn)程申請鎖

17、： SQL與共享池：共享池與鎖機(jī)制另一進(jìn)程B進(jìn)程申請鎖： SQL與共享池：共享池與鎖機(jī)制另一進(jìn)程B進(jìn)程申請鎖： SQL與共享池：共享池與鎖機(jī)制另一進(jìn)程B進(jìn)程申請鎖： SQL與共享池：共享池與鎖機(jī)制另一進(jìn)程B進(jìn)程申請鎖：持有者隊(duì)列 SQL與共享池：共享池與鎖機(jī)制C進(jìn)程申請不相容的獨(dú)占鎖： SQL與共享池：共享池與鎖機(jī)制C進(jìn)程申請不相容的獨(dú)占鎖：等待者隊(duì)列 SQL與共享池：共享池與鎖機(jī)制C進(jìn)程申請不相容的獨(dú)占鎖：A進(jìn)程釋放鎖 SQL與共享池： Library Cache Lock/pinSQL desc X$KGLLKNameNull?Type- - -ADDR INDX INST_ID KGLL

18、KADR KGLLKUSE KGLLKSES KGLLKSNM KGLLKHDL KGLLKPNC KGLLKPNS KGLLKCNTKGLLKMOD KGLLKREQ KGLLKFLG KGLLKSPN KGLNAHSH KGLLKSQLID KGLHDPAR KGLHDNSP USER_NAME KGLNAOBJ KGLLKEST KGLLKEXC KGLLKCTPRAW(8) X$視圖本身的地址。 NUMBERNUMBERRAW(8) Library Cache Lock地址 RAW(8)RAW(8) 持有Lock會話的地址NUMBER 持有Lock會話的SID RAW(8) Lock針

19、對對象的句柄地址 RAW(8)RAW(8)NUMBER 有幾個進(jìn)程持有此Lock NUMBER 持有模式NUMBER 請求模式NUMBERNUMBERNUMBER Lock針對對象的HASH值 VARCHAR2(13) Lock針對SQL的SQL_IDRAW(8) Lock針對對象的父句柄地址 NUMBERVARCHAR2(30)VARCHAR2(60) Lock針對對象的對象名DATE NUMBERVARCHAR2(64)觀察Library Cache Lock的視圖：觀察 Library Cache Pin的視圖： SQL與共享池： Library Cache Lock/pinSQL de

20、sc x$kglpnNameNull?TypeADDR INDX INST_ID KGLPNADR KGLPNUSE KGLPNSES KGLPNSID KGLPNHDL KGLPNLCK KGLPNCNTKGLPNMOD KGLPNREQ KGLPNFLG KGLPNDMK KGLPNSPN KGLNAHSHRAW(8) 同x$kgllk，x$視圖本身地址NUMBERNUMBER 實(shí)例編號 RAW(8) Pin鎖的內(nèi)存地址 RAW(8)RAW(8) 持有Lock會話的地址NUMBER持有Lock會話的SID（11G前無此列） RAW(8) Pin鎖針對對象的句柄地址RAW(8)NUMBER 有幾個進(jìn)程持有此Pin鎖 NUMBER 持有的Pin鎖模式 NUMBER 請求的