[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才有效.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库