深入分析direct path read（11G）

wei-xh

lfree 发表于 2014-8-18 15:21
存在physical reads direct 能作为判断的依据吗？

direct path read会被统计到段级别的physical reads direct资料 中

lfree

我的测试有点问题：

SCOTT@test> SELECT VALUE FROM V$SEGMENT_STATISTICS WHERE OWNER = USER AND OBJECT_NAME = 'T' AND STATISTIC_NAME = 'physical reads direct';

     VALUE
----------
      1102

SCOTT@test> alter system flush buffer_cache;
System altered.

SCOTT@test> SELECT VALUE FROM V$SEGMENT_STATISTICS WHERE OWNER = USER AND OBJECT_NAME = 'T' AND STATISTIC_NAME = 'physical reads direct';
     VALUE
----------
      1102

SCOTT@test> @hide _small_table_threshold
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%_small_table_threshold%')
NAME                                     DESCRIPTION                                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_small_table_threshold                   lower threshold level of table size for direct reads               TRUE                   1182                   1182

set serveroutput on
DECLARE
  L_TRSH NUMBER;
BEGIN
  L_TRSH := GET_ADR_TRSH(2, 1100, 1250);
  DBMS_OUTPUT.PUT_LINE(L_TRSH);
END;
/
SCOTT@test>   2    3    4    5    6    7
1100

PL/SQL procedure successfully completed.

--停在1100。

SCOTT@test> SCOTT@test> host cat sqllaji/sess.sql
set verify off
column name format a30
SELECT b.NAME, a.statistic#, a.VALUE,a.sid
  FROM v$mystat a, v$statname b
WHERE lower(b.NAME) in ('consistent gets direct','physical reads direct','table scans (direct read)') AND a.statistic# = b.statistic# ;

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76          0          5
physical reads direct                  81       1102          5
table scans (direct read)             380          0          5

SCOTT@test> select /*+ full(t)*/ count(*) from t ;
  COUNT(*)
----------
      1102

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76          0          5
physical reads direct                  81       1102          5
table scans (direct read)             380          0          5

lfree

SCOTT@test> @sess

NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76          0          5
physical reads direct                  81          0          5
table scans (direct read)             380          0          5

set serveroutput on
DECLARE
  L_TRSH NUMBER;
BEGIN
  L_TRSH := GET_ADR_TRSH(2, 1100, 1250);
  DBMS_OUTPUT.PUT_LINE(L_TRSH);
END;
  7  /
1100

PL/SQL procedure successfully completed.

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76          0          5
physical reads direct                  81       1102          5
table scans (direct read)             380          0          5

--执行函数后，确实physical reads direct ＝1102，但是table scans (direct read) 没有增加。

我的版本是

SCOTT@test> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

--我在windows下12c测试是好的。

lfree

SCOTT@test> SELECT VALUE FROM V$SEGMENT_STATISTICS WHERE OWNER = USER AND OBJECT_NAME = 'T' AND STATISTIC_NAME = 'physical reads direct';

     VALUE
----------
         0

SCOTT@test> INSERT /*+ append */ INTO T SELECT RPAD('*', 100, '*')  FROM DUAL CONNECT BY LEVEL <= 1102;

1102 rows created.

SCOTT@test> commit ;

Commit complete.

SCOTT@test> SELECT VALUE FROM V$SEGMENT_STATISTICS WHERE OWNER = USER AND OBJECT_NAME = 'T' AND STATISTIC_NAME = 'physical reads direct';
     VALUE
----------
      1102

SCOTT@test> alter system flush buffer_cache;

System altered.

SCOTT@test> SELECT VALUE FROM V$SEGMENT_STATISTICS WHERE OWNER = USER AND OBJECT_NAME = 'T' AND STATISTIC_NAME = 'physical reads direct';
     VALUE
----------
      1102

－－alter system flush buffer_cache;并没有清除V$SEGMENT_STATISTICS里面的统计。

lfree

另外我的测试脏块 有时候是50%。
cache 各种结果都有 50% ， 85% ，接近100的 ，还有全部cache也不会改变的。

wei-xh

lfree 发表于 2014-8-19 08:14
另外我的测试脏块 有时候是50%。
cache 各种结果都有 50% ， 85% ，接近100的 ，还有全部cache也不会改变的 ...

测试版本，测试过程，和代码能够给出来，我们来看看，我这边反复测试过，误差很小

lfree

我的测试：

--我的测试：

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


SCOTT@test> @hide _small_table_threshold
NAME                                     DESCRIPTION                                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_small_table_threshold                   lower threshold level of table size for direct reads               TRUE                   1182                   1182

SCOTT@test> @hide db_cache_size
NAME                                     DESCRIPTION                                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
__db_cache_size                          Actual size of DEFAULT buffer pool for standard block size buffers FALSE                  503316480              503316480
db_cache_size                            Size of DEFAULT buffer pool for standard block size buffers        FALSE                  452984832              452984832

SCOTT@test> @hide _db_block_buffers
NAME                                     DESCRIPTION                                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_db_block_buffers                        Number of database blocks cached in memory: hidden parameter       TRUE                   59130                  59130

-- 1182/0.02=59100
-- 59130*0.02=1182.6
-- 实际上占用_db_block_buffers的2%。我的理解应该是_db_block_buffers的2%。

create table t as  select rownum id,cast('testtest' as varchar2(20)) name from dual connect by level<=2;
alter table t minimize records_per_block ;
truncate table t;
insert into t select rownum id,cast('testtest' as varchar2(20)) name from dual connect by level<=1182*2;
commit;
create unique index pk_t on t(id);
execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 ',no_invalidate=>false);

SCOTT@test> select owner,table_name,BLOCKS,EMPTY_BLOCKS from dba_tables where owner=user and table_name='T';
OWNER  TABLE_NAME     BLOCKS EMPTY_BLOCKS
------ ---------- ---------- ------------
SCOTT  T                1252            0

SCOTT@test> host cat sqllaji/sp_use.sql
set verify off
set serveroutput on size 1000000
declare
  unf number;
  unfb number;
  fs1 number;
  fs1b number;
  fs2 number;
  fs2b number;
  fs3 number;
  fs3b number;
  fs4 number;
  fs4b number;
  full number;
  fullb number;
begin
  dbms_space.space_usage(nvl('&1',user),upper('&2'),'TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
  dbms_output.put_line('Total number of blocks that are unformatted: '||unf);
  dbms_output.put_line('Number of blocks that has at least 0 to 25% free space: '||fs1);
  dbms_output.put_line('Number of blocks that has at least 25 to 50% free space: '||fs2);
  dbms_output.put_line('Number of blocks that has at least 50 to 75% free space: '||fs3);
  dbms_output.put_line('Number of blocks that has at least 75 to 100% free space: '||fs4);
  dbms_output.put_line('Total number of blocks that are full in the segment: '||full);
end;
/

SCOTT@test> @sp_use '' T
Total number of blocks that are unformatted: 48
Number of blocks that has at least 0 to 25% free space: 0
Number of blocks that has at least 25 to 50% free space: 0
Number of blocks that has at least 50 to 75% free space: 0
Number of blocks that has at least 75 to 100% free space: 24
Total number of blocks that are full in the segment: 1180
unfb=393216  fs1b=0  fs2b=0  fs3b=0  fs4b=0  fullb=9666560

PL/SQL procedure successfully completed.


$ cat sess.sql
set verify off
column name format a30
SELECT b.NAME, a.statistic#, a.VALUE,a.sid
  FROM v$mystat a, v$statname b
WHERE lower(b.NAME) in ('consistent gets direct','physical reads direct','table scans (direct read)') AND a.statistic# = b.statistic# ;


SCOTT@test> alter system flush buffer_cache;
System altered.

SCOTT@test> @sess

NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76          0        213
physical reads direct                  81          0        213
table scans (direct read)             380          0        213

SCOTT@test> select /*+ full(t)*/ count(*) from t ;
  COUNT(*)
----------
      2364

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76       1204        213
physical reads direct                  81       1204        213
table scans (direct read)             380          1        213

--出现直接路径读。

SCOTT@test> @sess

NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76          0        210
physical reads direct                  81          0        210
table scans (direct read)             380          0        210

SCOTT@test> select /*+ index(t pk_t) */count(name) from t where id between 1 and 2364*.84;
COUNT(NAME)
-----------
       1985

SCOTT@test> select /*+ full(t)*/ count(*) from t ;
  COUNT(*)
----------
      2364

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76       1204        210
physical reads direct                  81       1204        210
table scans (direct read)             380          1        210


SCOTT@test> select /*+ index(t pk_t) */count(name) from t where id between 1 and 2364*.85;
COUNT(NAME)
-----------
       2009

SCOTT@test> select /*+ full(t)*/ count(*) from t ;
  COUNT(*)
----------
      2364

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76       1204        210
physical reads direct                  81       1204        210
table scans (direct read)             380          1        210

lfree

－－另外的测试
SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> @hide _small_table_threshold
NAME                    DESCRIPTION                                           DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
----------------------- ----------------------------------------------------- -------------- -------------- -------------
_small_table_threshold  lower threshold level of table size for direct reads  TRUE           1182           1182

SCOTT@test> @hide db_cache_size
NAME                     DESCRIPTION                                                        DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
------------------------ ------------------------------------------------------------------ -------------- -------------- --------------
__db_cache_size          Actual size of DEFAULT buffer pool for standard block size buffers FALSE          503316480      503316480
db_cache_size            Size of DEFAULT buffer pool for standard block size buffers        FALSE          452984832      452984832

SCOTT@test> @hide _db_block_buffers
NAME                     DESCRIPTION                                                        DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
------------------------ ------------------------------------------------------------------ -------------- -------------- -------------
_db_block_buffers        Number of database blocks cached in memory: hidden parameter       TRUE           59130          59130


create table t as select rownum id,rownum idx,lpad('a',2000,'a') a,lpad('b',2000,'b') b,lpad('c',500,'c') c from dual connect by level<=1182;
create unique index pk_t on t(id);
execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 ',no_invalidate=>false);

SCOTT@test> select owner,table_name,BLOCKS,EMPTY_BLOCKS from dba_tables where owner=user and table_name='T';
OWNER  TABLE_NAME     BLOCKS EMPTY_BLOCKS
------ ---------- ---------- ------------
SCOTT  T                1210            0


SCOTT@test> @sp_use '' T
Total number of blocks that are unformatted: 0
Number of blocks that has at least 0 to 25% free space: 0
Number of blocks that has at least 25 to 50% free space: 0
Number of blocks that has at least 50 to 75% free space: 0
Number of blocks that has at least 75 to 100% free space: 0
Total number of blocks that are full in the segment: 1182
unfb=0  fs1b=0  fs2b=0  fs3b=0  fs4b=0  fullb=9682944

PL/SQL procedure successfully completed.


SCOTT@test> alter system flush buffer_cache;

System altered.

SCOTT@test> @sess

NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76          0        210
physical reads direct                  81          0        210
table scans (direct read)             380          0        210

SCOTT@test> select /*+ full(t)*/ count(*) from t ;
  COUNT(*)
----------
      1182

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76       1182        210
physical reads direct                  81       1182        210
table scans (direct read)             380          1        210

--出现直接路径读。

SCOTT@test> select /*+ index(t pk_t) */count(idx) from t where id between 1 and 1182;
COUNT(IDX)
----------
      1182


SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76       1182        210
physical reads direct                  81       1182        210
table scans (direct read)             380          1        210

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76       1182        210
physical reads direct                  81       1182        210
table scans (direct read)             380          1        210

SCOTT@test> select /*+ full(t)*/ count(*) from t ;
  COUNT(*)
----------
      1182

SCOTT@test> @sess
NAME                           STATISTIC#      VALUE        SID
------------------------------ ---------- ---------- ----------
consistent gets direct                 76       2364        210
physical reads direct                  81       2364        210
table scans (direct read)             380          2        210

--100%  cache依旧出现。

wpsource

wei-xh

lfree 发表于 2014-8-19 10:23
－－另外的测试
SCOTT@test> @ver

最近重感冒，等病好了再看