オラクル11ｇのserial全表スキャンに対するdirect path read非活性化しよう。

これはいい知らせです－オラクル11ｇは全表スキャンでもdirect path readを具現しています。

• http://oraclue.com/2009/07/17/direct-path-reads-and-serial-table-scans-in-11g/
  
  
• http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html
  

しかし、私はこのような一方的な機能が好きではないんです。私の願うのはこの機能を自分の必要によって手軽に制御するのです。すなわち、オラクルを私のコントロールの下に置かなければいけません。


それで、いく時間この機能に対して調査をしましたし、Disableさせる方法を見つけました。 オラクル11ｇはこれをために、10949イベントを新しく追加しました。

UKJA@ukja116> @oerr 10949
10949                                                                           
 "Disable autotune direct path read for full table scan"                        
// *Cause:                                                                      
// *Action:  Disable autotune direct path read for serial full table scan.      
//                                                                              

次はこのイベントを利用する簡単な例です。

1．充分に大きい表を作ります。最小の大きさは5 * _small_table_thresholdだと知られています。

UKJA@ukja116> col value new_value sth
UKJA@ukja116> @para small_table
old   9: and i.ksppinm like '%&1%'
new   9: and i.ksppinm like '%small_table%'

NAME                           VALUE                IS_DEFAUL SES_MODIFI        
------------------------------ -------------------- --------- ----------        
SYS_MODIFI                                                                      
----------                                                                      
DESCRIPTION                                                                     
--------------------------------------------------------------------------------
_small_table_threshold         637                  TRUE      true              
deferred                                                                        
threshold level of table size for direct reads                                  
                                                                                
Elapsed: 00:00:00.04
UKJA@ukja116> 
UKJA@ukja116> create table t1(c1 number, c2 char(2000), c3 char(2000), c4 char(2000));

Table created.

Elapsed: 00:00:00.21
UKJA@ukja116> insert into t1
  2  select level, 'x', 'x', 'x'
  3  from dual connect by level <= 10 + 5*&sth;
old   3: from dual connect by level <= 10 + 5*&sth
new   3: from dual connect by level <= 10 + 5*637

3195 rows created.

Elapsed: 00:00:01.14
UKJA@ukja116> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja116> @gather t1
UKJA@ukja116> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.68

2. 10949イベントを使用しなかったケースと使用したケースを10046イベントを通じて見比べます。

-- case#1
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
select count(*) from t1;

-- case#2
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
alter session set events '10949 trace name context forever, level 1';
select count(*) from t1;

3. 10949イベントが活性化した2番目のケースでは以前のバージョンと同じにdb file scattered read待機イベントが観察されます。

SQL ID : 5bc0v4my7dvr5
select count(*) 
from
 t1

-- Case #1
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         3        0.02          0.04
  db file scattered read                          1        0.02          0.02
  direct path read                              231        0.29          1.67
  SQL*Net message from client                     2        0.03          0.03


-- Case #2
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                        213        0.26          1.77
  SQL*Net message from client                     2        0.00          0.00

予想できなかった性能問題のためにこの立派な機能を使いたくなければ、10949イベントを覚えてください。