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

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

--//今天做filesystemio_options参数测试时,遇到一个关于直接路径读的问题.
--//如果看以前的博客介绍,设置"_serial_direct_read"=never或者events '10949 trace name context forever';就可以关闭直接路径读.

--//我的测试在11GR2下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块就能确定问题.现在是直接路径读.执行多次看执行依旧.

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才有效.

时间： 2024-07-31 09:20:01

[20171120]11G关闭直接路径读.txt的相关文章

[20170810]直接路径读特例2.txt

[20170810]直接路径读特例2.txt --//以前在11.2.0.4下测试,出现直接路径读的一种特殊情况,就是使用rowid访问数据块(使用between). --//参考链接:http://blog.itpub.net/267265/viewspace-2134894/ --//在11.2.0.3下测试看看是否也存在类似的情况. 1.环境: SCOTT@zzzzzz> select * from v$version where rownum<=1; BANNER ----------

[20170308]直接路径读特例.txt

[20170308]直接路径读特例.txt --//昨天测试一些sql语句时,无意中发现出现直接路径读的一种特殊情况,就是使用rowid访问数据块(使用between).而且还发现一些我无 --//法解析的情况.通过例子说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------

[20170918]exp 直接路径导出.txt

[20170918]exp 直接路径导出.txt --//昨天看链接blogs.oracle.com/database4cn/%e5%af%b9%e4%ba%8e%e4%b8%80%e4%b8%aa%e9%9d%9e%e7%a9%ba%e5%ad%97%e6%ae%b5%e5%ae%9a%e4%b9%89%e7%9a%84%e8%a1%a8%e5%af%bc%e5%87%ba%e5%90%8e%ef%bc%8c%e5%86%8dimp%e6%97%b6%e5%80%99%e6%8a%a5%e9%

JS基于FileSystemObject创建一个指定路径的TXT文本文件_javascript技巧

本文实例讲述了JS基于FileSystemObject创建一个指定路径的TXT文本文件.分享给大家供大家参考.具体如下: Js创建一个指定路径的TXT文本文件,创建一个新的new ActiveXObject对象,使用这个对象的CreateTextFile方法来创建一个指定路径的TXT文本文件,简单吧? 在IE浏览器上运行该实例会有对应的安全提示,选择"允许阻止的内容",使程序正常运行即可.如下图所示: 文本文件创建成功后提示如下: 若文本文件已存在,则有如下提示: 具体代码如下: &l

[20150706]11G谓词推入问题.txt

[20150706]11G谓词推入问题.txt --生产系统遇到一个sql语句的问题. --生产系统的sql语句比较复杂,做一个简化的例子来说明问题.来说明自己优化遇到的困惑. 1.建立测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------------------------------

[20141014]11G长时间分析问题.txt

[20141014]11G长时间分析问题.txt http://www.itpub.net/thread-1495845-1-1.html http://space.itpub.net/267265/viewspace-723066 http://blog.itpub.net/267265/viewspace-752117/ --我以前看到这条sql语句,我做了一些简单修改. 1. 问题提出: SCOTT@test> @ver1 PORT_STRING                    VE

java-【求助】怎么用Java读txt文件，根据其中的条件，输出一个csv文件？（具体情况见内容）

问题描述 [求助]怎么用Java读txt文件,根据其中的条件,输出一个csv文件?(具体情况见内容) 用Java, 读txt文件(根据Eclipse软件的下方的console改成的log.txt) -------------------------------------- 例: [2013-08-26 10:45:45,042] ERROR does not exist. [2013-08-26 14:43:09,145] ERROR Certification failed. [2013-0

MFC读txt数据时，数据出错

问题描述 MFC读txt数据时,数据出错 我用mfc写了一个程序用于读取数据进行一元线性回归计算,读进来的数据总会莫名其妙的出问题,比如数据是12.34,读进来就会变成12.33999999999999999或者12.340000000000001,求各路大神帮忙看看是什么回事 解决方案 你有考虑过精度问题吗? 解决方案二: 浮点数在计算机中无法精确存储,只是无限接近这个数而已比如你存储12.34也只是存储一个无限接近12.34的值(比如12.339999....)所以我们比较浮点数的时候会设置

c#读txt文件 分割拼接问题，

问题描述 c#读txt文件 分割拼接问题, caozhy老师,发了,帮我看看,我估计循环哪里逻辑不清,多谢了 或者您哪里有什么好的方法 还有一种情况是某行最后那一列被分成了两行, 多谢了 解决方案 你的文件发在什么地方了??? 解决方案二: c#读txt文件中文乱码问题C#中读取txt文件中的中文时出现乱码问题c# 操作txt文件 解决方案三: caozhy老师 这里http://pan.baidu.com/s/1slzfWCl