潛水珽的異想世界

在ask tom中看到一篇解決系統狀況跟我們家系統很相似的文章,
這個公司的statspack  report 跑出來的狀況,有下面的幾個特點:
to5 wait event:
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                       23,174    80.50
latch free                                        866,415       3,789    13.16
log file sync                                     349,051       1,227     4.26
log file parallel write                           359,340         470     1.63
db file parallel write                              2,355          59      .20


特徵:
1. Execute to parse ratio is less.
2. Latch free are high.
3. In Sql ordered by Parse call I see more Parses then executions or 1 to 1 ratio.
   It could be due to library contention.
4. In Latch Activity, I see 21% waits for library cache for nowait calls.

我們家的主機跑statspack report也有這樣的問題,我比較在意的latch free的問題,影響latch的原因非常多,
latch也是Oracle中最虛幻的一個玩意兒,這篇不是在談論latch的原理與作用,而著重在解決latch free wait event的問題。

以下是tom說的:
(Followup   March 19, 2003 - 10am US/Eastern:)

suggestion -- create a table:

create table my_dual( dummy varchar2(1) primary key ) organization index;

insert into my_dual values ( 'X' );

and change

SELECT sys_context(:b2,:b1) from sys.dual

to

SELECT sys_context(:b2,:b1) from my_dual


Also, looks like this was a "cold system" at the beginning (just after a startup)?  
if so -- you only want to do a 15 minute snapshot after it is all warmed up.

這裡我們就好奇啦,為什麼tom建議我們將我們最熟悉的select * from dual改成select * from my_dual呢?為甚麼改變這樣的問題後,可以降低latch free呢?

我覺得原因是在my_dual中有建了1個index,所以讓query的查詢走index而不是走full table scan.

以下是tom提出的說明:

ops$tkyte@ORA920> create table my_dual ( dummy varchar2(1) primary key ) organization index;

Table created.

ops$tkyte@ORA920> insert into my_dual values ( 'X' );

1 row created.

ops$tkyte@ORA920> @trace
ops$tkyte@ORA920> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte@ORA920> declare
  2          l_namespace varchar2(25) default 'userenv';
  3          l_variable  varchar2(25) default 'sessionid';
  4          l_value     varchar2(25);
  5  begin
  6          for i in 1 .. 1000
  7          loop
  8                  select sys_context( l_namespace, l_variable )
  9                    into l_value
 10                    from dual;
 11                  select sys_context( l_namespace, l_variable )
 12                    into l_value
 13                    from my_dual;
 14          end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SELECT sys_context( :b2, :b1 ) from dual

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.26       0.27          0          0          0           0
Fetch     1000      0.10       0.14          0       3000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.37       0.41          0       3000          0        1000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 309     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  TABLE ACCESS FULL DUAL

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

SELECT sys_context( :b2, :b1 ) from my_dual

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.28       0.28          0          0          0           0
Fetch     1000      0.06       0.07          0       1000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.34       0.36          0       1000          0        1000

check out the reduction in LIOs between the two.  I think we can take:


  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     39,912,633   13,315,755            3.0   27.6  2090.54   3640.27 3574766763
Module: dllhost.exe
SELECT sys_context(:b2,:b1) from sys.dual

and make it become:

  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     13,304,211  ......


with that minor, tiny change.

看起來好像是真的因為my_dual建了index後減少了block的讀取。