oracle實驗記錄 (buffer_cache分析（2）cbc latch)

關於cache buffers chain latch (cbc latch)
block讀入sga buffer cache中時，對應的buffer header掛在hash bucket上的hash chain上，cache buffer chains latch 可以控制多個hash bucket，確保hash chain的完整性
，當在hash cahins中 新增刪除讀取資料時 程式需要獲得cbc latch（9I開始 讀的話cbc latch可以共享）

datablock放入哪個bucket 演算法為 mod(dba,_db_block_hash_buckets)

SQL> select count(*) from v$latch_children where name='cache buffers chains';

  COUNT(*)
----------
      1024

SQL> select count(distinct hladdr ) from x$bh;   （hladdr= hash latch address）

COUNT(DISTINCTHLADDR)
---------------------
                 1024

 

補充下邏輯讀
process先在運用hash演算法 得到塊所在的hash bucket 然後該bucket中找塊所在的cache buffer chain.找到後 在這個 chain上加一個cache buffer chains latch,latch加上之後在這個chain中需要 所要塊的buffer header,通過buffer header中block資訊 找到該block PIN住，釋放cache buffer chains latch,然後可以訪問該塊,server process不會一次將所有行提取出來,是按照命令抓取,每次讀一定數量的行(比如sqlplus 中set arraysize  X),這些行取出之後,會經由PGA傳給user.行一旦從buffer cache中取出,釋放塊上pin後 邏輯讀結束
每個邏輯讀需要一個latch get 操作 和一個cpu 操作,latch get的目標獲得latch，任意一個時刻 只有一個process可以 擁有cache buffer chain latch，

process每申請一次Cache buffer chains閂，就是一次邏輯讀

SQL> create table t1 (a int);

表已建立。

SQL> insert into t1 values(1);

已建立 1 行。

SQL> insert into t1 values(2);

已建立 1 行。

SQL> insert into t1 values(3);

已建立 1 行。

SQL> insert into t1 values(4);

已建立 1 行。

SQL> commit;

SQL> set autotrace trace stat
SQL> select * from t1;

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        463  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> set arraysize 2
SQL> select * from t1;

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets~~~~~~~~~~~~~~~~~~~~~多了一次邏輯讀 一共4行 sqlplus限制每次輸出2行 多獲取一次 cache buffers chain latch 多了一次邏輯讀
          0  physical reads
          0  redo size
        593  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

備註：consisten gets 演算法公式
consistent reads計算=ceil(獲取行數(card)/arraysize)+used blocks(FTS的話就是HWM下BLOCK)+1
分析:ceil(num_rows/arraysize) 例如取100行 每次顯示到 螢幕10行 需要取10次,oracle 訪問buffer cache 中相應的 hash chain 搜尋需要的buffer時需要 持有 cache buffers chains latch取完資料後釋放,再取時再獲取,這樣需要獲取10次才夠顯示完100行, cache buffers chains latch每獲取一次就是一次邏輯讀 (對於select來說就是).
+1 是多加一次segment header block scan

 

cache buffer chains latch主要爭用原因
1.低效sql，併發會話執行語句 設法獲得相同資料集，少的邏輯讀 意味少的latch get操作
2.hot block
多個會話重複訪問一個或多個子cache buffer chain latch時熱塊就產生了
通過等待時間檢查是否有熱塊

SQL> create table t1 (a int, b int);

表已建立。

SQL> ed
已寫入 file afiedt.buf

  1  declare
  2  begin
  3  for  i in 1..10000 loop
  4  insert into t1 values(i,i+1);
  5  end loop;
  6  commit;
  7* end;
  8  /

PL/SQL 過程已成功完成。
SQL>  select distinct file#,block# from (select dbms_rowid.rowid_relative_fno(ro
wid) file#,dbms_rowid.rowid_block_number(rowid) block# from t1);

     FILE#     BLOCK#
---------- ----------
         1      61635
         1      61645
         1      61646
         1      61647
         1      61634
         1      61649
         1      61638
         1      61648
         1      61640
         1      61651
         1      61643

     FILE#     BLOCK#
---------- ----------
         1      61650
         1      61637
         1      61639
         1      61641
         1      61642
         1      61636
         1      61644

已選擇18行。
SQL> select object_id,data_object_id from user_objects where object_name='T1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     51706          51706

 

已寫入 file afiedt.buf

  1  declare
  2  begin
  3  for i in 1..100000 loop
  4  update t1 set b=i+2 where a=i;
  5  commit;
  6  end loop;
  7* end;
SQL> /

PL/SQL 過程已成功完成。

 

SQL> declare
  2  begin
  3  for i in 1..100000 loop
  4  update t1 set b=i+2 where a=i;
  5  commit;
  6  end loop;
  7  end;
  8  /

PL/SQL 過程已成功完成。

～～～～～～～～～～～～～～～～～～～多個會話併發訪問修改

SQL> select s.event,sid ,s.p1raw,s.p2,s.p3 ,s.seconds_in_wait,s.wait_time,s.stat
e from v$session_wait s where s.event='latch free';

EVENT                                                                   SID
---------------------------------------------------------------- ----------
P1RAW            P2         P3 SECONDS_IN_WAIT  WAIT_TIME STATE
-------- ---------- ---------- --------------- ---------- -------------------
latch free                                                              142
03C49AA0        177          0               0         -1 WAITED SHORT TIME

latch free是一個彙總

製造了latch free 事件 由於實驗環境 製造的事件持有latch時間太短 沒有抓到物件

而且 試驗中 latch address不是 cache buffer chains latch

SQL> select name from v$latch where addr='03C49AA0';

NAME
--------------------------------------------------
dml lock allocation

  1  SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
  2    FROM x$bh a, dba_objects b
  3   WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
  4*    AND a.hladdr = '03C49AA0'
SQL> /

未選定行

 

SQL> col event format a30
SQL> select event,p1,p1text from v$session_wait_history where event like '%cache
 buffer%';

EVENT                                  P1 P1TEXT
------------------------------ ---------- ---------------
latch: cache buffers chains     538535040 address
latch: cache buffers chains     539117440 address~~~~~~~~~~~~~~~~~~這個address型別是number v$latch中衛raw
latch: cache buffers chains     538807680 address
latch: cache buffers chains     538999040 address
latch: cache buffers chains     539117440 address
latch: cache buffers chains     538807680 address
latch: cache buffers chains     538712320 address
latch: cache buffers chains     538498560 address
latch: cache buffers chains     538962560 address

已選擇9行。

SQL> select event,to_char(p1,'xxxxxxxxxxx'),p1text from v$session_wait_history w
here event like '%cache buffer%';

EVENT                          TO_CHAR(P1,' P1TEXT
------------------------------ ------------ ---------------
latch: cache buffers chains        20196480 address～～～～～～～～～～～～～～～轉換下 型別
latch: cache buffers chains        20224780 address
latch: cache buffers chains        201d8d80 address
latch: cache buffers chains        20207900 address
latch: cache buffers chains        201c1900 address

 

  1  SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
  2    FROM x$bh a, dba_objects b
  3   WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
  4*    AND a.hladdr = '03C49AA0'
SQL> /

HLADDR        FILE#     DBABLK        TCH        OBJ OBJECT_NAM
-------- ---------- ---------- ---------- ---------- ----------
.............................................................
-------- ---------- ---------- ---------- ---------- ----------
                                                     $

20196480          1      53192          1        181 ATTRIBUTE$
20196480          1      53192          1        181 METHOD$
20196480          1      53192          1        181 PARAMETER$
20196480          1      53192          1        181 RESULT$

已選擇35行。 ~~~~~~~~~~~~~~~~~~~通過歷史資訊 找到了一個還在佔有該latch的 修改的物件（可以看到 是一些系統表）

 

*************************************************

關於cache buffer chains latchs 爭用 與熱塊 (cache buffer chains latchs 是造成latch free 事件的 原因之一)

第1種方式  通過等待事件 (latch free 是個彙總事件)來檢視是否是熱塊 具體過程是
1.

SQL> select s.event,sid ,s.p1raw,s.p2,s.p3 ,s.seconds_in_wait,s.wait_time,s.stat
e from v$session_wait s where s.event='latch free';
找到等待事件，p1raw代表 latch地址（v$latch_children中address），p2 latch#,p3嘗試次數

如果有大量的latch free 且 p1raw相等（表示大家在爭用這個 latch），那麼可能是熱塊（可以通過v$latch_children 結合P1raw看 是不是cache buffer chains latch select name from v$latch_children where addr='XXXXXXX'）

2.用p1raw連線 x$bh中hladdr 連線dba_objectS找到具體物件，TCH 高一般就是hot block,block在lru端從冷端到熱端時候tch重置為0所以tch 0不代表一定是冷塊

  1  SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
  2    FROM x$bh a, dba_objects b
  3   WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
  4*    AND a.hladdr = 'XXXXX'    (hladdr=hash latch address)

解決：讓塊中放的行數少些，通常是修改應用

 

第2種方式 通過top 方式 來查詢爭用等待最多的cache buffers chains latch 和top 熱塊（按tch =touch方式）

1.從v$latch_children中找 top10(order by sleeps 相當於按等待次數排序)的 子latch資訊 top 10,但這top 10爭用的cache buffers chains latch並不一定是熱塊

SQL> select * from (select addr,child#,gets,misses,sleeps ,immediate_gets,immedi
ate_misses ,spin_gets from v$latch_children where name='cache buffers chains' or
der by sleeps desc ) where rownum<11;

ADDR         CHILD#       GETS     MISSES     SLEEPS IMMEDIATE_GETS
-------- ---------- ---------- ---------- ---------- --------------
IMMEDIATE_MISSES  SPIN_GETS
---------------- ----------
20210780        863   14406851      27589        136             37
               0      27456

201D3600        472    4830021       6128        124            211
               7       6009

201BC180        323    4830264       4440        120            313
               4       4326

ADDR         CHILD#       GETS     MISSES     SLEEPS IMMEDIATE_GETS
-------- ---------- ---------- ---------- ---------- --------------
IMMEDIATE_MISSES  SPIN_GETS
---------------- ----------
20216180        899    4829460       3730        111            375
               6       3624

20207900        806    4834203       5280        101            365
               0       5184

2021F000        956    4830676       4656        100            349
               2       4558

ADDR         CHILD#       GETS     MISSES     SLEEPS IMMEDIATE_GETS
-------- ---------- ---------- ---------- ---------- --------------
IMMEDIATE_MISSES  SPIN_GETS
---------------- ----------
20196480         81    4829726       5823         98            203
               2       5728

2019BC00        116    4830893       3463         95            322
               4       3371

201CA780        415    4832435       4373         94            378
               2       4281

ADDR         CHILD#       GETS     MISSES     SLEEPS IMMEDIATE_GETS
-------- ---------- ---------- ---------- ---------- --------------
IMMEDIATE_MISSES  SPIN_GETS
---------------- ----------
201D8D80        507    4830339       4335         91            372
               1       4247

已選擇10行。

SQL> select addr,ts#,file#,dbarfil,dbablk,tch ,hladdr from x$bh where hladdr='2
210780';

ADDR            TS#      FILE#    DBARFIL     DBABLK        TCH HLADDR
-------- ---------- ---------- ---------- ---------- ---------- --------
089AC730          2          3          3       6315          1 20210780
089AC674          2          3          3       6315          1 20210780
089AC5B8          2          3          3       6315          1 20210780
089AC4FC          2          3          3       6315          1 20210780
089AC730          0          1          1      56596          8 20210780
089AC730          0          1          1      17321          1 20210780
089AC674          0          1          1      17321          1 20210780
089AC5B8          0          1          1      17321          1 20210780
089AC4FC          0          1          1      17321          1 20210780
089AC730          2          3          3      19795          1 20210780
089AC674          2          3          3      19795          1 20210780

ADDR            TS#      FILE#    DBARFIL     DBABLK        TCH HLADDR
-------- ---------- ---------- ---------- ---------- ---------- --------
089AC5B8          2          3          3      19795          1 20210780
089AC4FC          2          3          3      19795          1 20210780
089AC730          0          1          1      52491          1 20210780
089AC730          2          3          3       2443          1 20210780
089AC674          2          3          3       2443          1 20210780
089AC5B8          2          3          3       2443          1 20210780
089AC4FC          2          3          3       2443          1 20210780
089AC730          2          3          3      33275          0 20210780
089AC674          0          1          1       9111          0 20210780
089AC730          0          1          1      61633          1 20210780

已選擇21行。
可以看到 top 10 中 sleeps第一的cache buffer chain latch 管理的block中（cbc latch對應多個bucket所以管理多個塊是正常的） 並沒有熱塊tch都很低(但 block在lru端從冷端到熱端時候tch重置為0所以tch 0不代表一定是冷塊)，造成cache buffers chains latche爭用2個主要原因就是 熱塊和chains太長

 

 

2.檢視系統中10大熱塊(從熱塊角度出發)
SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch ,hladdr from x$bh o
rder by tch desc) where rownum<11;

ADDR            TS#      FILE#    DBARFIL     DBABLK        TCH HLADDR
-------- ---------- ---------- ---------- ---------- ---------- --------
089AC5A0          0          1          1       1658        274 201F7F00
089AC5A0          0          1          1       1657        273 20195080
089AC394          2          3          3       2775        269 2018E000
089AC5A0          2          3          3       2772        269 201A5700
089AC394          2          3          3       2776        269 201F0E80
089AC394          2          3          3       2774        269 201CB180
089AC5A0          2          3          3      32272        269 20223880
089AC394          2          3          3       2773        269 20208580
089AC5A0          2          3          3      32271        269 201C0A00
089AC5A0          0          1          1       1674        265 201E6200

已選擇10行。

 

3. 連線x$bh與v$latch_chindren資訊
 select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps  fro
m (select * from (select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh orde
r by tch desc ) where rownum<11) a,(select addr,gets,misses,sleeps from v$latch_
children where name ='cache buffers chains') b where a.hladdr=b.addr
SQL> /

ADDR            TS#    DBARFIL     DBABLK        TCH       GETS     MISSES
-------- ---------- ---------- ---------- ---------- ---------- ----------
    SLEEPS
----------
20223880          2          3      32272       2505      25908          0
         0

20208580          2          3       2773       2502      23968          0
         0

201F7F00          0          1       1658       2554      15562          0
         0

ADDR            TS#    DBARFIL     DBABLK        TCH       GETS     MISSES
-------- ---------- ---------- ---------- ---------- ---------- ----------
    SLEEPS
----------
201F0E80          2          3       2776       2502      20830          0
         0

201E6200          0          1       1674       2484      14678          0
         0

201CB180          2          3       2774       2502      20818          0
         0

ADDR            TS#    DBARFIL     DBABLK        TCH       GETS     MISSES
-------- ---------- ---------- ---------- ---------- ---------- ----------
    SLEEPS
----------
201C0A00          2          3      32271       2502      29558          0
         0

201A5700          2          3       2772       2505      15735          0
         0

20195080          0          1       1657       2521      18961          0
         0

ADDR            TS#    DBARFIL     DBABLK        TCH       GETS     MISSES
-------- ---------- ---------- ---------- ---------- ---------- ----------
    SLEEPS
----------
2018E000          2          3       2775       2502      20768          0
         0

已選擇10行。
分析將 系統中top 前10的熱塊與 cache buffer chains latch 聯絡起來

通過這些塊 找到 物件資訊 連結dba_extents

 

SQL> col segment_name format a30

  1* select distinct e.owner,e.segment_name,e.segment_type from dba_extents e,(s
elect b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps  from (sel
ect * from (select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh order by t
ch desc ) where rownum<11) a,(select addr,gets,misses,sleeps from v$latch_childr
en where name ='cache buffers chains') b where a.hladdr=b.addr) f  where e.relat
ive_fno=f.dbarfil and e.block_id<=f.dbablk and e.block_id +e.blocks>f.dbablk
SQL> /

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ -----------------

SYS                            JOB$                           TABLE
SYS                            I_JOB_NEXT                     INDEX
SYS                            SYS_IOT_TOP_8802               INDEX

與 直接連線top 10 tch 的結果一樣

  1  SELECT distinct e.owner, e.segment_name, e.segment_type
  2            FROM dba_extents e,
  3                 (SELECT *
  4                    FROM (SELECT  addr, ts#, file#, dbarfil, dbablk, tch
  5                              FROM x$bh
  6                          ORDER BY tch DESC)
  7                   WHERE ROWNUM < 11) b
  8           WHERE e.relative_fno = b.dbarfil
  9             AND e.block_id <= b.dbablk
 10*            AND e.block_id + e.blocks > b.dbablk
SQL> /

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ ------------------

SYS                            JOB$                           TABLE
SYS                            I_JOB_NEXT                     INDEX
SYS                            SYS_IOT_TOP_8802               INDEX

 

最後抓SQL  加rule 這個hint的原因 ：下面連線表比較多 避免 cbo下oracle自己嘗試分析 要採用那種連線（產生執行計劃 解析時間將很長）按rbo順序模式連線

  1* select /*+ rule*/ hash_value,sql_text from v$sqLAREA where (hash_value,addr
ess)in (select g.hash_value,g.address from v$sqlarea g ,(select distinct e.owner
,e.segment_name,e.segment_type from dba_extents e,(select b.addr,a.ts#,a.dbarfil
,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps  from (select * from (select addr,ts#,
file#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc ) where rownum<11) a
,(select addr,gets,misses,sleeps from v$latch_children where name ='cache buffer
s chains') b where a.hladdr=b.addr) f  where e.relative_fno=f.dbarfil and e.bloc
k_id<=f.dbablk and e.block_id+e.blocks>f.dbablk)j where upper(g.sql_text) like '
%'||j.segment_name||'%' and j.segment_type='TABLE') ORDER BY HASH_VALUE,ADDRESS
SQL> /

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

  85383888
select t.inst_id,t.addr,t.kslltnum,t.kslltcnm,n.kslldlvl,        n.kslldnam,n.ks

lldhsh,        t.kslltwgt,t.kslltwff,t.kslltwsl,t.kslltngt,t.kslltnfa,        t.

kslltwkc,t.kslltwth,t.ksllthst0,t.ksllthst1,        t.ksllthst2,t.ksllthst3,t.ks

llthst4,t.ksllthst5,        t.ksllthst6,t.ksllthst7,t.ksllthst8,        t.ksllth

st9,t.ksllthst10, t.ksllthst11,        t.kslltwtt  from x$ksllt t, x$kslld n  wh

ere t.kslltcnm > 0 and t.kslltnum = n.indx

 138190469

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

SELECT snap_id , SQL_ID FROM   (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.sn

ap_id , t1.SQLID_KEWRSIE  SQL_ID FROM X$KEWRSQLIDTAB t1, WRH$_SQLTEXT t2      WH

ERE t2.dbid(+)  = :dbid  AND t2.SQL_ID(+) = t1.SQLID_KEWRSIE) WHERE nvl(snap_id,

 0) < :snap_id

 375665851
SELECT startsn.stat_id, x.keh_id,        GREATEST( 0, (endsn.value - startsn.val

ue) ) as value_diff FROM  (SELECT t1.* FROM WRH$_SYS_TIME_MODEL t1, WRM$_SNAPSHO

T s1   WHERE  t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

AND  t1.snap_id = s1.snap_id AND s1.bl_moved = 0   UNION ALL   SELECT t2.* FROM
WRH$_SYS_TIME_MODEL_BL t2, WRM$_SNAPSHOT s2   WHERE  t2.dbid = s2.dbid AND t2.in

stance_number = s2.instance_number     AND  t2.snap_id = s2.snap_id AND s2.bl_mo

ved <> 0) startsn ,  (SELECT t1.* FROM WRH$_SYS_TIME_MODEL t1, WRM$_SNAPSHOT s1
  WHERE  t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number     AND
t1.snap_id = s1.snap_id AND s1.bl_moved = 0   UNION ALL   SELECT t2.* FROM WRH$_

SYS_TIME_MODEL_BL t2, WRM$_SNAPSHOT s2   WHERE  t2.dbid = s2.dbid AND t2.instanc

e_number = s2.instance_number     AND  t2.snap_id = s2.snap_id AND s2.bl_moved <

> 0) endsn ,  X$KEHTIMMAP x WHERE  startsn.snap_id         = :begin_snap_id   an

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

d  endsn.snap_id           = :end_snap_i

 652170634
SELECT KEY_PART1, KEY_PART2, KEY_PART3, DECODE ( :B4 , KEY_PART1, 1, KEY_PART2,
2, KEY_PART3, 3, 0 ), DECODE ( :B3 , KEY_PART1, 1, KEY_PART2, 2, KEY_PART3, 3, 0

 ) FROM MGMT_BCN_AVAIL_DEF WHERE TARGET_GUID = :B2 AND METRIC_GUID = :B1

 819358145
SELECT snap_id , SQL_ID FROM   (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.sn

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

ap_id , t1.SQLID_KEWRSIE  SQL_ID FROM X$KEWRSQLIDTAB t1, WRH$_SQL_BIND_METADATA
t2      WHERE t2.dbid(+)  = :dbid  AND t2.SQL_ID(+) = t1.SQLID_KEWRSIE AND t2.PO

SITION(+) = 1) WHERE nvl(snap_id, 0) < :snap_id

 979299602
select t1.inst_id,t1.ksllasnam, t2.ksllwnam, t1.kslnowtf, t1.kslsleep, t1.kslwsc

wsl, t1.kslwsclthg, t2.ksllwnam         from  x$ksllw t2, x$kslwsc t1         wh

ere t2.indx = t1.indx

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

1488414063
insert into wrh$_service_name   (snap_id, dbid, service_name_hash, service_name)

 select   :lah_snap_id, :dbid, t2.name_hash, t2.name from   x$kewrattrnew t1, v$

services t2 where t1.num1_kewrattr = t2.name_hash

1494863970
SELECT T2.TARGET_GUID FROM MGMT_TARGETS T1, MGMT_TARGETS T2 WHERE T1.TARGET_GUID

=:B2 AND T1.EMD_URL=T2.EMD_URL AND T2.TARGET_TYPE=:B1

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

2146304988
SELECT snap_id , SERVICE_NAME_HASH FROM   (SELECT /*+ ordered use_nl(t2) index(t

2) */ t2.snap_id , t1.NAME_HASH  SERVICE_NAME_HASH FROM V$SERVICES t1, WRH$_SERV

ICE_NAME t2      WHERE t2.dbid(+)  = :dbid  AND t2.SERVICE_NAME_HASH(+) = t1.NAM

E_HASH) WHERE nvl(snap_id, 0) < :snap_id

2298265060
declare begin for i in 1..100000 loop update t1 set b=i+3 where a=i; commit; end

 loop; end;

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

3327029265
SELECT snap_id , SQL_ID, PLAN_HASH_VALUE FROM   (SELECT /*+ ordered use_nl(t2) i

ndex(t2) */ t2.snap_id , t1.SQLID_KEWRSPE  SQL_ID, t1.PLANHASH_KEWRSPE  PLAN_HAS

H_VALUE FROM X$KEWRTSQLPLAN t1, WRH$_SQL_PLAN t2      WHERE t2.dbid(+)  = :dbid
 AND t2.SQL_ID(+) = t1.SQLID_KEWRSPE AND t2.PLAN_HASH_VALUE(+) = t1.PLANHASH_KEW

RSPE AND t2.ID(+) = 0) WHERE nvl(snap_id, 0) < :snap_id

3407318689

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

SELECT snap_id , OPTIMIZER_ENV_HASH_VALUE FROM   (SELECT /*+ ordered use_nl(t2)
index(t2) */ t2.snap_id , t1.OPTENVHV_KEWROEE  OPTIMIZER_ENV_HASH_VALUE FROM X$K

EWRTOPTENV t1, WRH$_OPTIMIZER_ENV t2      WHERE t2.dbid(+)  = :dbid  AND t2.OPTI

MIZER_ENV_HASH_VALUE(+) = t1.OPTENVHV_KEWROEE) WHERE nvl(snap_id, 0) < :snap_id

3554734773
select inst_id, kglnaobj, kglfnobj, kglobt03, kglobhs0+kglobhs1+kglobhs2+kglobhs

3+kglobhs4+kglobhs5+kglobhs6, kglobt08+kglobt11, kglobt10, kglobt01, kglobccc, k

globclc, kglhdlmd, kglhdlkc, kglobt04, kglobt05, kglobt48, kglobt35, kglobpc6, k

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

glhdldc, substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), kglhdivc, kglob

t12, kglobt13, kglobwdw, kglobt14, kglobwap, kglobwcc, kglobwcl, kglobwui, kglob

t42, kglobt43, kglobt15, kglobt02, decode(kglobt32, 0, 'NONE',
1, 'ALL_ROWS',                  2, 'FIRST_ROWS',                  3, 'RULE',
              4, 'CHOOSE', 'UNKNOWN'), kglobtn0, kglobcce, kglobcceh, kglobt17,
kglobt18, kglobts4, kglhdkmk, kglhdpar, kglnahsh, kglobt46, kglobt30, kglobts0,
kglobt19, kglobts1, kglobt20, kglobt21, kglobts2, kglobt06, kglobt07, decode(kgl

obt28, 0, NULL, kglobt28), kglhdadr, decode(bitand(kglobt00,64),64, 'Y', 'N'), d

ecode(kglobsta,        1, 'VALID',        2, 'VALID_AUTH_ERROR',        3, 'VALI

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

D_COMPILE_ERROR',        4, 'VALID_UNAUT

4058483781
select d.inst_id,d.kslldadr,la.latch#,d.kslldlvl,d.kslldnam,d.kslldhsh,        l

a.gets,la.misses,        la.sleeps,la.immediate_gets,la.immediate_misses,la.wait

ers_woken,        la.waits_holding_latch,la.spin_gets,la.sleep1,la.sleep2,
  la.sleep3,la.sleep4,la.sleep5,la.sleep6,la.sleep7,la.sleep8,la.sleep9,
la.sleep10, la.sleep11, la.wait_time  from x$kslld d,    (select kslltnum latch#

,        sum(kslltwgt) gets,sum(kslltwff) misses,sum(kslltwsl) sleeps,        su

HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------

m(kslltngt) immediate_gets,sum(kslltnfa) immediate_misses,        sum(kslltwkc)
waiters_woken,sum(kslltwth) waits_holding_latch,        sum(ksllthst0) spin_gets

,sum(ksllthst1) sleep1,sum(ksllthst2) sleep2,        sum(ksllthst3) sleep3,sum(k

sllthst4) sleep4,sum(ksllthst5) sleep5,        sum(ksllthst6) sleep6,sum(ksllths

t7) sleep7,sum(ksllthst8) sleep8,        sum(ksllthst9) sleep9,sum(ksllthst10) s

leep10,sum(ksllthst11) sleep11,        sum(kslltwtt) wait_time    from x$ksllt g

roup by kslltnum) la  where la.latch# =

已選擇14行。

抓到sql後 就是優化了

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