[20150504]奇怪的逻辑读union or.txt
--在优化一个项目时遇到一个奇怪的情况,自己做例子测试看看.
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1.建立测试例子:
SCOTT@test> create table t as select rownum id , 'test' name,lpad('a',100,'a') pad from dual connect by level Table created.
SCOTT@test> create index i_t_id on t(id);
Index created.
SCOTT@test> create index i_t_name on t(name);
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
--很明显,name字段都是一样的字符串'test',建立索引基本没用.
2.开始测试:
SCOTT@test> alter session set statistics_level=all;
Session altered.
$ cat aa.sql
set termout off
select * from t where id = 100 or name = 'test';
set termout on
--主要目的避免屏幕大量输出.
SCOTT@test> @aa
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8r52ykgtjbwcx, child number 0
select * from t where id = 100 or name = 'test'
Plan hash value: 1601196873
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 290 (100)| | 100K|00:00:00.13 | 2115 | 1613 |
|* 1 | TABLE ACCESS FULL| T | 1 | 100K| 10M| 290 (2)| 00:00:01 | 100K|00:00:00.13 | 2115 | 1613 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("NAME"='test' OR "ID"=100))
23 rows selected.
--注意逻辑读的数量是2115.
3.改写使用union all:
$ cat ab.sql
select * from t where id = 100 union all select * from t where name='test'';
SCOTT@test> @ax.sql
SQL_ID 6mt1njq6qpw8a, child number 0
select * from t where id = 100 union all select * from t where
name='test'
Plan hash value: 114996438
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 292 (100)| | 100K|00:00:00.52 | 2102 | 1613 |
| 1 | UNION-ALL | | 1 | | | | | 100K|00:00:00.52 | 2102 | 1613 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 111 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | 0 |
|* 3 | INDEX RANGE SCAN | I_T_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 |
|* 4 | TABLE ACCESS FULL | T | 1 | 100K| 10M| 290 (2)| 00:00:01 | 100K|00:00:00.13 | 2098 | 1613 |
1 - SET$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
4 - SEL$2 / T@SEL$2
3 - access("ID"=100)
4 - filter("NAME"='test')
31 rows selected.
--不管那个效率高,注意看逻辑读,第2种方式的全表扫描为什么是2098逻辑读呢?
4.现在把索引删除看看.
SCOTT@test> drop index i_t_id ;
Index dropped.
SCOTT@test> drop index i_t_name ;
SCOTT@test> @ax
Plan hash value: 1078996545
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 579 (100)| | 100K|00:00:00.54 | 3714 | 3226 |
| 1 | UNION-ALL | | 1 | | | | | 100K|00:00:00.54 | 3714 | 3226 |
|* 2 | TABLE ACCESS FULL| T | 1 | 1 | 111 | 289 (1)| 00:00:01 | 1 |00:00:00.02 | 1616 | 1613 |
|* 3 | TABLE ACCESS FULL| T | 1 | 100K| 10M| 290 (2)| 00:00:01 | 100K|00:00:00.13 | 2098 | 1613 |
3 - SEL$2 / T@SEL$2
2 - filter("ID"=100)
3 - filter("NAME"='test')
29 rows selected.
--为什么id=2,全表扫描的逻辑读仅仅1616呢?注意看后面还有物理读,难倒是direct path read的影响吗?关闭它再测试看看.
5.通过设置隐含参数_serial_direct_read来设置是否启用direct path read，在session级别做测试:
SCOTT@test> alter session set "_serial_direct_read"=never;
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | | 290 (100)| | 100K|00:00:00.12 | 2117 |
|* 1 | TABLE ACCESS FULL| T | 1 | 100K| 10M| 290 (2)| 00:00:01 | 100K|00:00:00.12 | 2117 |
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | | 579 (100)| | 100K|00:00:00.52 | 3718 |
| 1 | UNION-ALL | | 1 | | | | | 100K|00:00:00.52 | 3718 |
|* 2 | TABLE ACCESS FULL| T | 1 | 1 | 111 | 289 (1)| 00:00:01 | 1 |00:00:00.01 | 1618 |
|* 3 | TABLE ACCESS FULL| T | 1 | 100K| 10M| 290 (2)| 00:00:01 | 100K|00:00:00.12 | 2100 |
--同样是全表扫描为什么不一样呢?实际上这个跟输出的记录行有关,id=2仅仅输出1行,而id=3输出全表记录.
SCOTT@test> show array
arraysize 200
SCOTT@test> select blocks from dba_tables where owner=user and table_name='T';
BLOCKS
----------
1647
--看到这应该明白了吧,前面仅仅输出1行,这样逻辑读的数量=高水位下读块的数量.
--有兴趣的可以看看我另外一篇blog,这样上面的问题就不奇怪了.
[20150209]为什么少1个逻辑读.txt
<a href="http://blog.itpub.net/267265/viewspace-1430902/">http://blog.itpub.net/267265/viewspace-1430902/</a>
![linux-svn卸载与安装[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)