[20150504]奇怪的逻辑读union or.txt

[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);
Index created.

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
set termout off
select * from t where id = 100 union all select * from t where name='test'';
set termout on

SCOTT@test> @ax.sql
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
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 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1
   4 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   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 ;
Index dropped.

SCOTT@test> @ax
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6mt1njq6qpw8a, child number 0
-------------------------------------
select * from t where id = 100 union all select * from t where
name='test'
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 |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   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;
Session altered.

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 |
--------------------------------------------------------------------------------------------------------------------
|   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 |
--------------------------------------------------------------------------------------------------------------------
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.

SCOTT@test> @ax
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6mt1njq6qpw8a, child number 0
-------------------------------------
select * from t where id = 100 union all select * from t where
name='test'

Plan hash value: 1078996545

---------------------------------------------------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$2 / T@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"=100)
   3 - filter("NAME"='test')
29 rows selected.

--同样是全表扫描为什么不一样呢?实际上这个跟输出的记录行有关,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
http://blog.itpub.net/267265/viewspace-1430902/

时间： 2024-07-30 10:59:53

[20150504]奇怪的逻辑读union or.txt的相关文章

[20171115]关于逻辑读的疑问.txt

[20171115]关于逻辑读的疑问.txt --//有网友指出[20150209]为什么少1个逻辑读.txt,链接:http://blog.itpub.net/267265/viewspace-1430902/ --//如何验证是这样操作的. 1.环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----

[20120605]逻辑读那个高.txt

1.建立测试环境: SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 

[20150320]逻辑读增加.txt

[20150320]逻辑读增加.txt --昨天在看awr报表发现一个奇怪的现象,一个小表在全表扫描时逻辑读增加许多. --有一个小表逻辑读很高比平时高,一开始以为是高水位的问题,检查发现不是.后来才发现一开发在做维护后,在执行select for -- upate查看,然后离开办别的事情,忘记提交或者回滚.导致其它用户执行时访问回滚段,导致逻辑读增加.我kill后恢复正常. --我做一个简单的例子来说明: 1.session 1: SCOTT@test> select * from emp f

[20150309]逻辑读产生CBC Latch的解析.txt

[20150309]逻辑读产生Cache Buffer Chain(简称CBC) Latch的解析.txt --参考链接http://blog.csdn.net/guoyjoe/article/details/8585391,自己也做1次. 逻辑读的过程 1.Oracle以每个块的文件号.块号和类型做HASH运算,得到HASH值.根据HASH值,到HASH表中取出指定块的内存地址 2.获取CBC Latch(实验的重点测试部分) 3.根据HASH值,搜索CBC链表 4.根据DBA找到BH(Buf

[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 ----------

大量逻辑读的瓶颈分析和优化

原创 转载请注明出处 我的工作记录:  昨天早上生产库出现了大约一个小时资源耗尽的情况,分析得出语句如下: MERGE INTO T_DPD_AGENT_CHANGE A USING (SELECT A.AGENT_ID,               A.ENTER_COMPANY_DATE ENTER_DATE,               A.LEAVE_COMPANY_DATE LEAVE_DATE,               A.AGENT_STATUS,              

数据读取的逻辑读简单解析：关于BUFFER CACHE

数据读取之逻辑读简单解析--BUFFER CACHE   关于consistent read--一致性读--Logical read-逻辑读-current read当前读--物理读,详见:http://blog.csdn.net/haibusuanyun/article/details/11489091 一.实验数据准备--查出一条数据的ROWID,及FILE_ID,BLOCK_ID等信息 BYS@ bys3>select rowid,test.* from test where rownum

buffer cache实验9:从buffer caceh中读取数据块解析-从逻辑读到物理读

先来张大图: 所用SQL语句: BYS@ ocm1>select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,deptno from bys.test;     FILE#     BLOCK#     DEPTNO ---------- ---------- ----------         4        391         10 就以上图为例,文字描述

逻辑读产生Cache Buffer Chain(简称CBC) Latch的解析

测试环境:版本11gR2 SQL> select * from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 一.逻辑读的过程  1.Oracle以每个块的文件号.