[20120605]逻辑读那个高.txt
1.建立测试环境:
2.执行如下:
那个逻辑读很高一些!
--按照oracle索引的定义,oracle NULL放在后面.也就是讲如果建立索引,NULL值应该放在最右边.当然如果两列都是NULL,就不存在索引中.
--第1个sql语句一定还存在一个的排序操作,因为NULL要放在前面.而第2个执行INDEX FULL SCAN就可以了.
--看看真实的执行计划:
--两个对比,使用nulls first,要多一个排序操作,效率不高.
3.对比逻辑读:
--为什么前面的逻辑读很大呢?
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<=1000 ;
Table created.
SQL> insert into t select null id1,rownum+1000 id2,'TEST' name from dual connect by level<=9000;
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
2
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大小的影响.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31369455/viewspace-2134028/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
sqlplus显示乱码处理方法
请登录后发表评论
登录
全部评论
