[20171120]11G停歇直接途径读.txt

[20171120]11G关闭直接途径读.txt

–//今天做filesystemio_options参数测试时,境遇三个有关直接途径读的问题.
–//固然看在此此前的博客介绍,设置”_serial_direct_read”=never只怕events
‘10949 trace name context forever’;就可以关闭直接途径读.

–//小编的测试在11G锐界2下set events ‘10949 trace name context
forever’;不行.
–//通过例子来表达难题

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER



x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g
Enterprise Edition Release 11.2.0.4.0 – 64bit Production

SCOTT@book> create table t as select rownum id from dual connect by
level<=2;
Table created.

SCOTT@book> ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
–//那样可以落成每块2条记录.

SCOTT@book> insert into t select rownum+2 from dual connect by level
<=64000-2;
63998 rows created.

SCOTT@book> commit ;
Commit complete.

insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit ;

–//分析表略.

SCOTT@book> select
OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS
from dba_segments where owner=user and segment_name=’T’;
OWNER  SEGMENT_NAME         SEGMENT_TYPE       HEADER_FILE
HEADER_BLOCK      BYTES     BLOCKS



SCOTT  T                    TABLE                        4          546
2153775104     262912

–//占用2153775104/1024/1024=2054M,262912块.

SCOTT@book> select object_id,data_object_id from dba_objects
where owner=user and object_name=’T’;
 OBJECT_ID DATA_OBJECT_ID


     90702          90702

2.测试1:
SCOTT@book> alter system flush buffer_cache;
System altered.
    
SCOTT@book> select count(*) from v$bh where OBJD=90702 and
STATUS<>’free’;

  COUNT(*)

         0

SCOTT@book> select count(*) from t ;

  COUNT(*)

    512000

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  45vdc2q5hs1f3, child number 1

select count(*) from t

Plan hash value: 2966233522

| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)|

E-Time   | A-Rows |   A-Time   | Buffers | Reads  |

|   0 | SELECT STATEMENT   |      |      1 |        | 71059
(100)|          |      1 |00:00:01.24 |     256K|    256K|
|   1 |  SORT AGGREGATE    |      |      1 |      1 |           
|          |      1 |00:00:01.24 |     256K|    256K|
|   2 |   TABLE ACCESS FULL| T    |      1 |    512K| 71059   (1)|

00:14:13 |    512K|00:00:01.21 |     256K|    256K|

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$1
   2 – SEL$1 / T@SEL$1

SCOTT@book> select count(*) from v$bh where OBJD=90702 and
STATUS<>’free’;

  COUNT(*)

        11
   
–//看reads=256K,仅仅缓存11块就能显然问题.将来是平素途径读.执行数10回看实践依旧.

SCOTT@book> @ &r/viewsess “physical reads direct”
NAME                                       STATISTIC#      VALUE       
SID



physical reads direct                              97     256064       
274
physical reads direct temporary tablespace        110          0       
274
physical reads direct (lob)                       176          0       
274

3.测试set events ‘10949 trace name context forever’;

SCOTT@book> host oerr ora 10949
10949, 00000, “Disable autotune direct path read for full table scan”
// *Cause:
// *Action:  Disable autotune direct path read for serial full table
scan.
–//按照介绍相应这么些也得以.

SCOTT@book> alter session set events ‘10949 trace name context
forever’;
Session altered.

SCOTT@book> alter session set statistics_level=all ;
Session altered.

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

SCOTT@book> select count(*) from v$bh where OBJD=90702 and
STATUS<>’free’;

  COUNT(*)

         0
SCOTT@book> select count(*) from t ;

  COUNT(*)

    512000

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  45vdc2q5hs1f3, child number 1

select count(*) from t

Plan hash value: 2966233522

| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)|

E-Time   | A-Rows |   A-Time   | Buffers | Reads  |

|   0 | SELECT STATEMENT   |      |      1 |        | 71059
(100)|          |      1 |00:00:00.71 |     256K|    256K|
|   1 |  SORT AGGREGATE    |      |      1 |      1 |           
|          |      1 |00:00:00.71 |     256K|    256K|
|   2 |   TABLE ACCESS FULL| T    |      1 |    512K| 71059   (1)|

00:14:13 |    512K|00:00:00.69 |     256K|    256K|

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$1
   2 – SEL$1 / T@SEL$1
20 rows selected.

SCOTT@book> @ &r/viewsess “physical reads direct”
NAME                                       STATISTIC#      VALUE       
SID



physical reads direct                              97     256064       
274
physical reads direct temporary tablespace        110          0       
274
physical reads direct (lob)                       176          0       
274

SCOTT@book> select count(*) from v$bh where OBJD=90702 and
STATUS<>’free’;

  COUNT(*)

        11

–//看reads=256K,仅仅缓存11块就能鲜明难点.未来是直接途径读.执行多次看实践照旧.
–//表明设置set events ‘10949 trace name context
forever’;无法关闭直接途径读.

4.设置”_serial_direct_read”=never;

SCOTT@book> alter session set “_serial_direct_read”=never;
Session altered.

SCOTT@book> alter session set statistics_level=all ;
Session altered.

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

SCOTT@book> select count(*) from v$bh where OBJD=90702 and
STATUS<>’free’;

  COUNT(*)

         0
SCOTT@book> select count(*) from v$bh where OBJD=90702 and
STATUS<>’free’;

  COUNT(*)

         0

SCOTT@book> select count(*) from t ;

  COUNT(*)

    512000

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  45vdc2q5hs1f3, child number 1

select count(*) from t

Plan hash value: 2966233522

| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)|

E-Time   | A-Rows |   A-Time   | Buffers | Reads  |

|   0 | SELECT STATEMENT   |      |      1 |        | 71059
(100)|          |      1 |00:00:01.80 |     256K|    256K|
|   1 |  SORT AGGREGATE    |      |      1 |      1 |           
|          |      1 |00:00:01.80 |     256K|    256K|
|   2 |   TABLE ACCESS FULL| T    |      1 |    512K| 71059   (1)|

00:14:13 |    512K|00:00:01.78 |     256K|    256K|

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$1
   2 – SEL$1 / T@SEL$1

SCOTT@book> @ &r/viewsess “physical reads direct”
NAME                                                                  

STATISTIC#      VALUE        SID


physical reads
direct                                                         
97          0        274
physical reads direct temporary
tablespace                                    110          0       
274
physical reads direct
(lob)                                                   176         
0        274
–//直接路径读为0

SCOTT@book> select count(*) from v$bh where OBJD=90702 and
STATUS<>’free’;

  COUNT(*)

     49959

–//已经缓存了49959块.反复实施如下语句:

SCOTT@book> select count(*) from t ;

  COUNT(*)

    512000

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  45vdc2q5hs1f3, child number 1

select count(*) from t

Plan hash value: 2966233522

| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)|

E-Time   | A-Rows |   A-Time   | Buffers | Reads  |

|   0 | SELECT STATEMENT   |      |      1 |        | 71059
(100)|          |      1 |00:00:01.37 |     256K|    217K|
|   1 |  SORT AGGREGATE    |      |      1 |      1 |           
|          |      1 |00:00:01.37 |     256K|    217K|
|   2 |   TABLE ACCESS FULL| T    |      1 |    512K| 71059   (1)|

00:14:13 |    512K|00:00:01.35 |     256K|    217K|

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$1
   2 – SEL$1 / T@SEL$1
20 rows selected.

–//你可以窥见读的数据降到了217K块.

总结:
–//测试一定要小心一些oracle版本的变化,一些参数大概随着版本的转变发生变化.
–//那也是本身干什么老是写blog都指明小编当下的应用版本.
–//关闭直接途径读alter session set
“_serial_direct_read”=never;对于11GR2才有效.

相关文章