下面的測試環境:OS:WIN7 DB：11.2.0.3

當表所佔的BLOCK數大於_small_table_threshold時，表就認為是大表，FTS大表操作結果是放到LRU末端，只CACHE一次，對小表的操作是放到MRU的末端。FTS大表時，是透過direct path read的方式讀取資料。我們可以透過配置10949事件，使用程式透過db file scattered read的方式來讀取資料。

_small_table_threshold的值在資料庫啟動的時候自動配置成_db_block_buffer*0.02。

1，檢視相當引數的值

SQL> @parameter_hide

SQL> set echo off

+------------------------------------------------------------------------+

| display hide parameter value |

+------------------------------------------------------------------------+

Enter Search Parameter (i.e. max|all) : db_block_buffer

PARAMETER SESSION_VALUE INSTANCE_VALUE DESCRIPTION

---------------------------------------- -------------------- -------------------- ------------------------------------------------------------

_db_block_buffers 63680 63680 Number of database blocks cached in memory: hidden parameter

SQL> @parameter_hide

SQL> set echo off

+------------------------------------------------------------------------+

| display hide parameter value |

+------------------------------------------------------------------------+

Enter Search Parameter (i.e. max|all) : small

PARAMETER SESSION_VALUE INSTANCE_VALUE DESCRIPTION

---------------------------------------- -------------------- -------------------- ------------------------------------------------------------

_small_table_threshold 1273 1273 lower threshold level of table size for direct reads

2，建立一個表TEST1,塊的個數大小small_table_threshold

SQL> exec show_space(p_segname=>'TEST1',P_OWNER=>'SCOTT',P_TYPE=>'TABLE');

Unformatted Blocks ..................... 62

FS1 Blocks (0-25) ..................... 1

FS2 Blocks (25-50) ..................... 0

FS3 Blocks (50-75) ..................... 0

FS4 Blocks (75-100)..................... 52

Full Blocks ..................... 1,137

Total Blocks............................ 1,280

Total Bytes............................. 10,485,760

Total MBytes............................ 10

Unused Blocks........................... 0

Unused Bytes............................ 0

Last Used Ext FileId.................... 5

Last Used Ext BlockId................... 411,008

Last Used Block......................... 128

PL/SQL procedure successfully completed.

3,建立一個小表

SQL> create table scott.test2 as select * from scott.test1 where rownum<75000;

Table created.

SQL> exec show_space(p_segname=>'TEST2',P_OWNER=>'SCOTT',P_TYPE=>'TABLE');

Unformatted Blocks ..................... 0

FS1 Blocks (0-25) ..................... 0

FS2 Blocks (25-50) ..................... 0

FS3 Blocks (50-75) ..................... 0

FS4 Blocks (75-100)..................... 0

Full Blocks ..................... 1,067

Total Blocks............................ 1,152

Total Bytes............................. 9,437,184

Total MBytes............................ 9

Unused Blocks........................... 59

Unused Bytes............................ 483,328

Last Used Ext FileId.................... 5

Last Used Ext BlockId................... 413,440

Last Used Block......................... 69

PL/SQL procedure successfully completed.

4，對大表進行多次訪問

我們可以看到物理讀都是一樣，並沒有減小，說明BLOCK沒有CACHE到BUFFER CACHE中

SQL> set autotrace traceonly;

SQL> select count(*) from scott.test1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3896847026

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 349 (1)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST1 | 66578 | 349 (1)| 00:00:05 |

--------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

1 db block gets

1200 consistent gets

1190 physical reads

0 redo size

425 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select count(*) from scott.test1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3896847026

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 349 (1)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST1 | 66578 | 349 (1)| 00:00:05 |

--------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

1 db block gets

1200 consistent gets

1190 physical reads

0 redo size

425 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

5，下面是trace程式

檢視程式是以那種方式訪問資料塊的

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 12;

Statement processed.

SQL> select count(*) from scott.test1;

SQL> oradebug tracefile_name

E:APPLUOPINGdiagrdbmswinorclwinorcltracewinorcl_ora_8624.trc

PARSING IN CURSOR #262715460 len=32 dep=0 uid=0 oct=3 lid=0 tim=13695518487 hv=3636002668 ad='b88bc3dc' sqlid='gtwt4m7cbj

select count(*) from scott.test1

END OF STMT

PARSE #262715460:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13695518486

EXEC #262715460:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13695518602

WAIT #262715460: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=75700 tim=13695518629

WAIT #262715460: nam='direct path read' ela= 598 file number=5 first dba=409859 block cnt=13 obj#=75700 tim=13695519645

WAIT #262715460: nam='direct path read' ela= 367 file number=5 first dba=409889 block cnt=15 obj#=75700 tim=13695520603

WAIT #262715460: nam='direct path read' ela= 316 file number=5 first dba=409905 block cnt=15 obj#=75700 tim=13695521114

WAIT #262715460: nam='direct path read' ela= 136 file number=5 first dba=409921 block cnt=15 obj#=75700 tim=13695521445

WAIT #262715460: nam='direct path read' ela= 4731 file number=5 first dba=409937 block cnt=15 obj#=75700 tim=13695526371

WAIT #262715460: nam='direct path read' ela= 1603 file number=5 first dba=409986 block cnt=126 obj#=75700 tim=13695529850

WAIT #262715460: nam='direct path read' ela= 553 file number=5 first dba=410114 block cnt=126 obj#=75700 tim=13695531752

WAIT #262715460: nam='direct path read' ela= 734 file number=5 first dba=410242 block cnt=126 obj#=75700 tim=13695533842

WAIT #262715460: nam='direct path read' ela= 589 file number=5 first dba=410370 block cnt=126 obj#=75700 tim=13695535660

WAIT #262715460: nam='direct path read' ela= 1648 file number=5 first dba=410498 block cnt=126 obj#=75700 tim=13695538106

WAIT #262715460: nam='direct path read' ela= 374 file number=5 first dba=410626 block cnt=126 obj#=75700 tim=13695539715

WAIT #262715460: nam='direct path read' ela= 834 file number=5 first dba=410754 block cnt=126 obj#=75700 tim=13695541868

WAIT #262715460: nam='direct path read' ela= 634 file number=5 first dba=410882 block cnt=126 obj#=75700 tim=13695543782

這裡可以看到是以direct path read的方式訪問資料

7，重新整理一下buffer_cache

SQL> alter system flush buffer_cache;

System altered.

8.訪問小表

每一次訪問小表有物理讀，從2次開發物理讀消失，說明BLOCK已經CACHE到BUFFER CACHE中了

SQL> select count(*) from scott.test2;

Execution Plan

----------------------------------------------------------

Plan hash value: 634289536

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 299 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST2 | 74999 | 299 (1)| 00:00:04 |

--------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1071 consistent gets

1068 physical reads

0 redo size

425 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select count(*) from scott.test2;

Execution Plan

----------------------------------------------------------

Plan hash value: 634289536

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 299 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST2 | 74999 | 299 (1)| 00:00:04 |

--------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1071 consistent gets

0 physical reads

0 redo size

425 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

9，下面透過配置10949事件來改變塊的訪問方式

SQL> alter session set events '10949 trace name context forever, level 1';

Session altered.

SQL> select count(*) from scott.test1;

select count(*) from scott.test1

END OF STMT

PARSE #258512224:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13852556637

EXEC #258512224:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13852556706

WAIT #258512224: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=0 tim=13852556724

WAIT #258512224: nam='db file sequential read' ela= 551 file#=5 block#=409858 blocks=1 obj#=75700 tim=13852557314

WAIT #258512224: nam='db file scattered read' ela= 367 file#=5 block#=409859 blocks=5 obj#=75700 tim=13852557812

WAIT #258512224: nam='db file scattered read' ela= 424 file#=5 block#=409864 blocks=8 obj#=75700 tim=13852558366

WAIT #258512224: nam='db file scattered read' ela= 408 file#=5 block#=409873 blocks=7 obj#=75700 tim=13852558909

WAIT #258512224: nam='db file scattered read' ela= 521 file#=5 block#=409880 blocks=8 obj#=75700 tim=13852559557

WAIT #258512224: nam='db file scattered read' ela= 412 file#=5 block#=409889 blocks=7 obj#=75700 tim=13852560061

WAIT #258512224: nam='db file scattered read' ela= 427 file#=5 block#=409896 blocks=8 obj#=75700 tim=13852560716

WAIT #258512224: nam='db file scattered read' ela= 519 file#=5 block#=409905 blocks=7 obj#=75700 tim=13852561331

WAIT #258512224: nam='db file scattered read' ela= 528 file#=5 block#=409912 blocks=8 obj#=75700 tim=13852561948

WAIT #258512224: nam='db file scattered read' ela= 459 file#=5 block#=409921 blocks=7 obj#=75700 tim=13852562493

WAIT #258512224: nam='db file scattered read' ela= 477 file#=5 block#=409928 blocks=8 obj#=75700 tim=13852563103

WAIT #258512224: nam='db file scattered read' ela= 429 file#=5 block#=409937 blocks=7 obj#=75700 tim=13852563662

WAIT #258512224: nam='db file scattered read' ela= 470 file#=5 block#=409944 blocks=8 obj#=75700 tim=13852564261

WAIT #258512224: nam='db file scattered read' ela= 424 file#=5 block#=409953 blocks=7 obj#=75700 tim=13852564809

WAIT #258512224: nam='db file scattered read' ela= 472 file#=5 block#=409960 blocks=8 obj#=75700 tim=13852565401

WAIT #258512224: nam='db file scattered read' ela= 409 file#=5 block#=409969 blocks=7 obj#=75700 tim=13852565972

WAIT #258512224: nam='db file scattered read' ela= 426 file#=5 block#=409976 blocks=8 obj#=75700 tim=13852566498

WAIT #258512224: nam='db file scattered read' ela= 2543 file#=5 block#=409986 blocks=126 obj#=75700 tim=13852569237

WAIT #258512224: nam='db file scattered read' ela= 2434 file#=5 block#=410114 blocks=126 obj#=75700 tim=13852572799

WAIT #258512224: nam='db file scattered read' ela= 2535 file#=5 block#=410242 blocks=126 obj#=75700 tim=13852576312

WAIT #258512224: nam='db file scattered read' ela= 2641 file#=5 block#=410370 blocks=126 obj#=75700 tim=13852580912

WAIT #258512224: nam='db file sequential read' ela= 284 file#=5 block#=410881 blocks=1 obj#=75700 tim=13852582218

WAIT #258512224: nam='db file sequential read' ela= 292 file#=5 block#=409857 blocks=1 obj#=75700 tim=13852582591

WAIT #258512224: nam='db file scattered read' ela= 297 file#=5 block#=411008 blocks=2 obj#=75700 tim=13852582924

WAIT #258512224: nam='db file scattered read' ela= 2563 file#=5 block#=410498 blocks=126 obj#=75700 tim=13852585689

WAIT #258512224: nam='db file scattered read' ela= 2604 file#=5 block#=410626 blocks=126 obj#=75700 tim=13852589282

WAIT #258512224: nam='db file scattered read' ela= 2548 file#=5 block#=410754 blocks=126 obj#=75700 tim=13852592817

WAIT #258512224: nam='db file scattered read' ela= 1853 file#=5 block#=410882 blocks=78 obj#=75700 tim=13852595699

WAIT #258512224: nam='db file scattered read' ela= 1267 file#=5 block#=410960 blocks=48 obj#=75700 tim=13852597583

WAIT #258512224: nam='db file scattered read' ela= 1550 file#=5 block#=411072 blocks=64 obj#=75700 tim=13852599520

已經更改成db file scattered read了。

oracleORACLE 基礎

2

ORACLE _small_table_threshold與event

相關文章