「バッファキャッシュ」関連の待機イベントと「パフォーマンス統計情報」を読み解く：しばちょう先生の試して納得！　DBAへの道（改）（5）（2/2 ページ）

$ sqlplus TRY/TRY
SQL>
/* TBS39表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成 */
create tablespace TBS39 datafile '+DATA(DATAFILE)' size 512m ;
create user TRY identified by TRY default tablespace TBS39 ;
grant connect, resource, dba to TRY ;
　
/* TAB39_SMALL表の作成 */
connect TRY/TRY
create table TAB39_SMALL (COL1 number NOT NULL, COL2 char(1000)) ;
　
/* TAB39_SMALL表へ8MB分のレコードを挿入 */
insert /*+append */ into TAB39_SMALL
select LEVEL, 'hoge'||to_char(LEVEL) from DUAL connect by LEVEL <=7*128*7 ;
commit ;
　
/* TAB39_SMALL表のCOL1列に主キーを作成 */
create unique index IDX_TAB39_SMALL_COL1 on TAB39_SMALL(COL1) ;
alter table TAB39_SMALL add primary key (COL1) using index ;
　
/* TAB39_BIG表の作成 */
create table TAB39_BIG (COL1 number NOT NULL, COL2 char(1000)) ;
　
/* TAB39_BIG表へ約200MB分のレコードを挿入 */
insert /*+append */ into TAB39_BIG
select LEVEL, 'hoge'||to_char(LEVEL) from DUAL connect by LEVEL <=7*128*200 ;
commit ;
　
/* TAB39_BIG表のCOL1列に主キーを作成 */
create unique index IDX_TAB39_BIG_COL1 on TAB39_BIG(COL1) ;
alter table TAB39_BIG add primary key (COL1) using index ;
　
/* TAB39_BIG表のCOL2列にBツリーを作成 */
create index IDX_TAB39_BIG_COL2 on TAB39_BIG(COL2) ;
　
/* TRYスキーマ内の全オブジェクトのサイズを確認 */
set linesize 120 pages 5000
col SEGMENT_NAME for a24
select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024
from USER_SEGMENTS order by 2, 1 ;
　
SEGMENT_NAME             SEGMENT_TYPE       BYTES/1024/1024
------------------------ ------------------ ---------------
IDX_TAB39_BIG_COL1       INDEX                            4
IDX_TAB39_BIG_COL2       INDEX                          208
IDX_TAB39_SMALL_COL1     INDEX                        .1875
TAB39_BIG                TABLE                          208
TAB39_SMALL              TABLE                            8
　
　
/* TRYスキーマ内の全オブジェクトのオプティマイザ統計情報を取得 */
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRY');

$ sqlplus /nolog
SQL>
/* テストの都合上、バッファキャッシュ上のデータをフラッシュ */
connect / as sysdba
alter system flush buffer_cache ;
　
/* クエリ実行 */
connect TRY/TRY
set autotrace on explain
select /*+FULL(t) */ sum(ora_hash(COL2, 10)) from TAB39_SMALL t ;
　
SUM(ORA_HASH(COL2,10))
----------------------
                 31333
　
Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |  1001 |   253   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |             |     1 |  1001 |            |          |
|   2 |   TABLE ACCESS FULL| TAB39_SMALL |  6272 |  6131K|   253   (1)| 00:00:04 |
----------------------------------------------------------------------------------
　
　
/* パフォーマンス統計情報の確認 */
set lines 150 pages 5000
col NAME for a48
select S.NAME, M.VALUE
  from V$MYSTAT M, V$STATNAME S
 where M.STATISTIC# = S.STATISTIC#
   and S.NAME like 'physical read%'
 order by 1 ;
　
NAME                                                  VALUE
------------------------------------------------ ----------
physical read IO requests                                39
physical read bytes                                 7421952
physical read flash cache hits                            0
physical read partial requests                            0
physical read requests optimized                          0
physical read total IO requests                          39
physical read total bytes                           7421952
physical read total bytes optimized                       0
physical read total multi block requests                 13
physical reads                                          906
physical reads cache                                    906
physical reads direct                                     0
physical reads direct (lob)                               0
physical reads direct temporary tablespace                0
physical reads for flashback new                          0
physical reads prefetch warmup                            0
physical reads retry corrupt                              0