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

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select rownum id1 ,rownum id2 ,'test' name from dual connect by level

Table created.

SQL> insert into t select null id1,rownum+1000 id2,'TEST' name from dual connect by level

9000 rows created.

SQL> alter table t modify(id2  not null);

SQL> create index i_t_id1_id2 on t(id1,id2);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

2.执行如下:

set arraysize 200

select /*+ index(t i_t_id1_id2) */ id1 from t order by id1 nulls first;

select /*+ index(t i_t_id1_id2) */ id1 from t order by id1 ;

那个逻辑读很高一些!

--按照oracle索引的定义,oracle NULL放在后面.也就是讲如果建立索引,NULL值应该放在最右边.当然如果两列都是NULL,就不存在索引中.
--第1个sql语句一定还存在一个的排序操作,因为NULL要放在前面.而第2个执行INDEX FULL SCAN就可以了.

--看看真实的执行计划:

select /*+ index(t i_t_id1_id2) */ id1 from t order by id1 nulls first;

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

SQL_ID  7n231h7pgrdj6, child number 0

-------------------------------------

select /*+ index(t i_t_id1_id2) */ id1 from t order by id1 nulls first

Plan hash value: 3323278756

-----------------------------------------------------------------------------------------

| Id  | Operation        | Name        | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |             |        |    25 (100)|       |       |          |

|   1 |  SORT ORDER BY   |             |  10000 |    25   (4)|   302K|   302K|  268K (0)|

|   2 |   INDEX FULL SCAN| I_T_ID1_ID2 |  10000 |    24   (0)|       |       |          |

-----------------------------------------------------------------------------------------

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

20 rows selected.

SQL> select /*+ index(t i_t_id1_id2) */ id1 from t order by id1 ;

SQL> @dpc

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------

SQL_ID  5649wdayj1uxg, child number 0

-------------------------------------

select /*+ index(t i_t_id1_id2) */ id1 from t order by id1

Plan hash value: 3094005775

--------------------------------------------------------------

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

--------------------------------------------------------------

|   0 | SELECT STATEMENT |             |        |    24 (100)|

|   1 |  INDEX FULL SCAN | I_T_ID1_ID2 |  10000 |    24   (0)|

--------------------------------------------------------------

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

19 rows selected.

--两个对比,使用nulls first,要多一个排序操作,效率不高.

3.对比逻辑读:

SQL> set autot traceonly

SQL> select /*+index(t i_t_id1_id2) */ id1 from t order by id1 nulls first;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3323278756

--------------------------------------------------------------------------------

| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |             | 10000 | 20000 |    25   (4)| 00:00:01 |

|   1 |  SORT ORDER BY   |             | 10000 | 20000 |    25   (4)| 00:00:01 |

|   2 |   INDEX FULL SCAN| I_T_ID1_ID2 | 10000 | 20000 |    24   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         24  consistent gets

          0  physical reads

          0  redo size

      63419  bytes sent via SQL*Net to client

       1059  bytes received via SQL*Net from client

         51  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      10000  rows processed

SQL> select /*+index(t i_t_id1_id2) */ id1 from t order by id1 ;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3094005775

--------------------------------------------------------------------------------

| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |             | 10000 | 20000 |    24   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | I_T_ID1_ID2 | 10000 | 20000 |    24   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         74  consistent gets

          0  physical reads

          0  redo size

      33368  bytes sent via SQL*Net to client

       1059  bytes received via SQL*Net from client

         51  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10000  rows processed

--为什么前面的逻辑读很大呢?

SQL> validate index i_t_id1_id2;

Index analyzed.

SQL> host cat  i.sql

set linesize 200;

column name format a10

/* select height, blocks, lf_blks, lf_rows_len, lf_blk_len, br_blks, br_rows, br_rows_len, br_blk_len, btree_space, used_space, pct_used from index_stats; */

select HEIGHT, BLOCKS, NAME, LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN, BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN, DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS from index_stats;

select MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE, PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS, PRE_ROWS, PRE_ROWS_LEN, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;

SQL> @i

    HEIGHT     BLOCKS NAME          LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS

---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------

         2         32 I_T_ID1_ID      10000         23      162692       7996         22          1         262       8028           0               0         10000

MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------

                1      191936     162954         85            1                    3          0            0              1                1

--占用LF_BLKS=23块.

select /*+index(t i_t_id1_id2) */ id1 from t order by id1 nulls first;

是先INDEX FULL SCAN,再 SORT ORDER BY,这样逻辑读的数量是24.

SQL> show array

arraysize 200

--我定义的arraysize=200.

select /*+index(t i_t_id1_id2) */ id1 from t order by id1 ;

是先INDEX FULL SCAN,再输出.

10000/200=50+24=74.这样逻辑读74就正常了.

SQL> set array 100

SQL> set autot traceonly ;

SQL> select /*+index(t i_t_id1_id2) */ id1 from t order by id1 ;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3094005775

--------------------------------------------------------------------------------

| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |             | 10000 | 20000 |    24   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | I_T_ID1_ID2 | 10000 | 20000 |    24   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        124  consistent gets

          0  physical reads

          0  redo size

      42268  bytes sent via SQL*Net to client

       1609  bytes received via SQL*Net from client

        101  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10000  rows processed

--10000/200=100+24=124,符合推论.

SQL> select /*+index(t i_t_id1_id2) */ id1 from t order by id1 nulls first;

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3323278756

--------------------------------------------------------------------------------

| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |             | 10000 | 20000 |    25   (4)| 00:00:01 |

|   1 |  SORT ORDER BY   |             | 10000 | 20000 |    25   (4)| 00:00:01 |

|   2 |   INDEX FULL SCAN| I_T_ID1_ID2 | 10000 | 20000 |    24   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         24  consistent gets

          0  physical reads

          0  redo size

      72569  bytes sent via SQL*Net to client

       1609  bytes received via SQL*Net from client

        101  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      10000  rows processed

-- 而这样不受arraysize大小的影响.

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

[20120605]逻辑读那个高.txt的相关文章

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

[20150504]奇怪的逻辑读union or.txt --在优化一个项目时遇到一个奇怪的情况,自己做例子测试看看. SCOTT@test> @ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------

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

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

[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

浅析Oracle全表扫描下的逻辑读

T1表全表扫描产生逻辑读的分析做个实验给你演示一下:以表t1为例,对段t1做dump 1.t1表就一条数据 gyj@OCM> select * from t1; ID NAME ---------- ---------- 1 AAAAA 2.找t1段的段头块 gyj@OCM> select header_file,header_block from dba_segments where segment_name='T1' and owner='GYJ'; HEADER

理解SQL SERVER中的逻辑读，预读和物理读

SQL SERVER数据存储的形式在谈到几种不同的读取方式之前,首先要理解SQL SERVER数据存储的方式.SQL SERVER存储的最小单位为页(Page).每一页大小为8k,SQL SERVER对于页的读取是原子性,要么读完一页,要么完全不读,不会有中间状态.而页之间的数据组织结构为B树.所以SQL SERVER对于逻辑读,预读,和物理读的单位是页. SQL SERVER一页的总大小为:8K 但是这一页存储的数据会是:8K=8192字节-96字节(页头)-36字节(行偏移)=8060字节

理解SQL SERVER中的逻辑读，预读和物理读_MsSql

SQL SERVER数据存储的形式在谈到几种不同的读取方式之前,首先要理解SQL SERVER数据存储的方式.SQL SERVER存储的最小单位为页(Page).每一页大小为8k,SQL SERVER对于页的读取是原子性,要么读完一页,要么完全不读,不会有中间状态.而页之间的数据组织结构为B树(请参考我之前的博文).所以SQL SERVER对于逻辑读,预读,和物理读的单位是页. SQL SERVER一页的总大小为:8K 但是这一页存储的数据会是:

数据读取的逻辑读简单解析：关于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 就以上图为例,文字描述