[20150321]索引空块的问题.txt
--晚上看了:
索引空块较多造成index range scan的IO成本较高
http://www.dbaxiaoyu.com/archives/2504
--感觉有点怪怪的:
SELECT /*+gather_plan_statistics ab*/
LOG.OID OID,
LOG.REGION REGION,
LOG.ACCEPT_SEQ ACCEPT_SEQ,
LOG.PROCESS_CODE PROCESS_CODE,
LOG.REQ_CHANNEL REQ_CHANNEL,
OPCODE.ROLLBACK_FLAG ROLLBACK_FLAG
FROM tbcs.INT_LOG_CRM2BOSS_ON LOG, tbcs.INT_CRM2BOSS_OPCODE OPCODE
WHERE LOG.PROCESS_CODE = OPCODE.PROCESS_CODE
AND LOG.REQ_TIME AND (OPCODE.ROLLBACK_FLAG = 1 OR OPCODE.ROLLBACK_FLAG = 2)
AND OPCODE.STATUS = 1
AND ROWNUM
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:01.07 | 46448 | | | |
|* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:01.07 | 46448 | | | |
| 2 | MERGE JOIN | | 1 | 14 | 0 |00:00:01.07 | 46448 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | INT_CRM2BOSS_OPCODE | 1 | 10 | 8 |00:00:00.01 | 7 | | | |
| 4 | INDEX FULL SCAN | PK_INT_CRM2BOSS_OPCODE | 1 | 18 | 9 |00:00:00.01 | 1 | | | |
|* 5 | SORT JOIN | | 8 | 15 | 0 |00:00:01.07 | 46441 | 18432 | 18432 |16384 (0)|
| 6 | PARTITION RANGE ALL | | 1 | 15 | 181 |00:00:00.03 | 46441 | | | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| INT_LOG_CRM2BOSS_ON | 5 | 15 | 181 |00:00:01.07 | 46441 | | | |
|* 8 | INDEX RANGE SCAN | IDX_INT_LOG_CRM2BOSS_ON_RT | 5 | 15 | 181 |00:00:01.07 | 46300 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
--id=8,buffers=46300,而实际的行数=181.很明显索引存在大量空块.
--另外注意看的查询条件(注:我不了解他的应用情况),LOG.REQ_TIME --很明显,这个表在业务中做完一些处理后,存在大量的删除情况,这样LOG.REQ_TIME
sys@CRMDB2>select partition_name,
2 subpartition_name,
3 inserts,
4 updates,
5 deletes,
6 timestamp,
7 truncated,
8 drop_segments
9 from dba_tab_modifications
10 where table_owner = upper('&owner')
11 and table_name = upper('&tab_name')
12 /
Enter value for owner: tbcs
old 10: where table_owner = upper('&owner')
new 10: where table_owner = upper('tbcs')
Enter value for tab_name: INT_LOG_CRM2BOSS_ON
old 11: and table_name = upper('&tab_name')
new 11: and table_name = upper('INT_LOG_CRM2BOSS_ON')
PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
---------------- ------------------ ------- ---------- ---------- ------------------- --- -------------
2057 0 3702 2015-03-18 03:30:21 NO 0
P_R_12 513 0 996 2015-03-18 03:30:21 NO 0
P_R_17 365 0 641 2015-03-18 03:30:21 NO 0
P_R_18 400 0 762 2015-03-18 03:30:21 NO 0
P_R_19 774 0 1293 2015-03-18 03:30:21 NO 0
--删除的记录很多.按照道理如果索引块是空的,才会重用.因为索引结构的特殊性,不是想插那里就插那里.许多疑点我还是没明白,我自己
--还是做一些测试:
--关于索引空块的问题,自己做一些测试:
1.建立测试环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
--------------------- -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t as select rownum id , 'test' name from dual connect by levelcreate unique index i_t_id on t(id);
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);
alter session set statistics_level=all ;
1.测试1:
SCOTT@test01p> select count(id) from t;
COUNT(ID)
----------
99999
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 18tmpvf8my4qr, child number 0
-------------------------------------
select count(id) from t
Plan hash value: 3548397654
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 58 (100)| | 1 |00:00:00.06 | 216 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.06 | 216 |
| 2 | INDEX FAST FULL SCAN| I_T_ID | 1 | 99999 | 488K| 58 (0)| 00:00:01 | 99999 |00:00:00.03 | 216 |
--------------------------------------------------------------------------------------------------------------------------
--逻辑读216.
SCOTT@test01p> select min(id) from t;
MIN(ID)
----------
1
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 01jh0zwxfsdp4, child number 0
-------------------------------------
select min(id) from t
Plan hash value: 3363318368
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.01 | 2 |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_ID | 1 | 1 | 5 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------------------
--逻辑读2.
2.删除1半数据:
SCOTT@test01p> delete from t where id50000 rows deleted.
SCOTT@test01p> commit ;
Commit complete.
3.重复步骤1的测试:
SCOTT@test01p> select count(id) from t;
COUNT(ID)
----------
49999
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 18tmpvf8my4qr, child number 0
-------------------------------------
select count(id) from t
Plan hash value: 3548397654
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 58 (100)| | 1 |00:00:00.03 | 216 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.03 | 216 |
| 2 | INDEX FAST FULL SCAN| I_T_ID | 1 | 99999 | 488K| 58 (0)| 00:00:01 | 49999 |00:00:00.02 | 216 |
--------------------------------------------------------------------------------------------------------------------------
--可以发现执行INDEX FAST FULL SCAN,逻辑读并没有因为删除1半的数据而减少,也就是全索引扫描包含了大量空块.
SCOTT@test01p> select min(id) from t;
MIN(ID)
----------
50001
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 01jh0zwxfsdp4, child number 0
-------------------------------------
select min(id) from t
Plan hash value: 3363318368
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 104 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.01 | 104 |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_ID | 1 | 1 | 5 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 104 |
-------------------------------------------------------------------------------------------------------------------------------
--可以发现我删除前面的id数据,导致取最小值,要扫描索引很多块,逻辑读从2->104.
4.查看索引统计信息看看:
SCOTT@test01p> validate index i_t_id;
Index analyzed.
--type i.sql
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;
SCOTT@test01p> @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 256 I_T_ID 99999 208 1488879 8000 207 1 2256 8032 50000 739397 99999
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 1672032 1491135 90 1 3 0 0 0 0
5.插入记录看看:
SCOTT@test01p> insert into t (id ,name ) select 1e5-1+rownum,'TEST' from dual connect by level10000 rows created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> validate index i_t_id;
Index analyzed.
SCOTT@test01p> @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 256 I_T_ID 98996 208 1480863 8000 207 1 2268 8032 38997 581482 98996
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 1672032 1483131 89 1 3 0 0 0 0
--上下对比,注意看DEL_LF_ROWS数量,已经减少了,而索引大小没有变化.说明一些块已经重用.
6.重复步骤1的测试:
SCOTT@test01p> select count(id) from t;
COUNT(ID)
----------
59999
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 18tmpvf8my4qr, child number 0
-------------------------------------
select count(id) from t
Plan hash value: 3548397654
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 58 (100)| | 1 |00:00:00.03 | 216 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.03 | 216 |
| 2 | INDEX FAST FULL SCAN| I_T_ID | 1 | 99999 | 488K| 58 (0)| 00:00:01 | 59999 |00:00:00.02 | 216 |
--------------------------------------------------------------------------------------------------------------------------
--可以发现执行INDEX FAST FULL SCAN,逻辑读并没有因为增加记录而而减少,也就是全索引扫描包含了大量空块.逻辑读依旧是216.
SCOTT@test01p> select min(id) from t;
MIN(ID)
----------
50001
SCOTT@test01p> @dpc '' ''
argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 01jh0zwxfsdp4, child number 0
-------------------------------------
select min(id) from t
Plan hash value: 3363318368
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 85 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.01 | 85 |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_ID | 1 | 1 | 5 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 85 |
-------------------------------------------------------------------------------------------------------------------------------
--虽然重用了1部分块,但是依旧有许多块没有使用,导致取最小值逻辑读=85,比104少了一点.
7.再做一个查询:
SCOTT@test01p> select id from t where id no rows selected
--没有记录返回.
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gu6hp3xvpwbu8, child number 0
-------------------------------------
select id from t where id Plan hash value: 1420114084
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 43 (100)| | 0 |00:00:00.01 | 29 |
|* 1 | INDEX RANGE SCAN| I_T_ID | 1 | 20000 | 97K| 43 (0)| 00:00:01 | 0 |00:00:00.01 | 29 |
---------------------------------------------------------------------------------------------------------------------
--即使没有记录返回,逻辑读依旧达到了29.执行计划依旧扫描了大量的空索引块.
8.转储索引结构看看:
SCOTT@test01p> select object_id,data_object_id from dba_objects where owner=user and object_name='I_T_ID';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
97514 97514
SCOTT@test01p> alter session set events 'immediate trace name treedump level 97514';
Session altered.
--查看索引结构转储:
----- begin tree dump
branch: 0x2402ce3 37760227 (0: nrow: 208, level: 1)
leaf: 0x2402cf1 37760241 (-1: nrow: 0 rrow: 0)
leaf: 0x2402cf2 37760242 (0: nrow: 513 rrow: 0)
leaf: 0x2402cf3 37760243 (1: nrow: 513 rrow: 0)
leaf: 0x2402cf4 37760244 (2: nrow: 513 rrow: 0)
leaf: 0x2402cf5 37760245 (3: nrow: 513 rrow: 0)
leaf: 0x2402cf6 37760246 (4: nrow: 513 rrow: 0)
leaf: 0x2402cf7 37760247 (5: nrow: 513 rrow: 0)
leaf: 0x2402cf8 37760248 (6: nrow: 0 rrow: 0)
leaf: 0x2402cfa 37760250 (7: nrow: 479 rrow: 0)
leaf: 0x2402cfb 37760251 (8: nrow: 479 rrow: 0)
leaf: 0x2402cfc 37760252 (9: nrow: 479 rrow: 0)
leaf: 0x2402cfd 37760253 (10: nrow: 479 rrow: 0)
leaf: 0x2402cfe 37760254 (11: nrow: 479 rrow: 0)
leaf: 0x2402cff 37760255 (12: nrow: 479 rrow: 0)
leaf: 0x2402d81 37760385 (13: nrow: 479 rrow: 0)
leaf: 0x2402d82 37760386 (14: nrow: 479 rrow: 0)
leaf: 0x2402d83 37760387 (15: nrow: 479 rrow: 0)
leaf: 0x2402d84 37760388 (16: nrow: 479 rrow: 0)
leaf: 0x2402d85 37760389 (17: nrow: 479 rrow: 0)
leaf: 0x2402d86 37760390 (18: nrow: 479 rrow: 0)
leaf: 0x2402d87 37760391 (19: nrow: 479 rrow: 0)
leaf: 0x2402d88 37760392 (20: nrow: 479 rrow: 0)
leaf: 0x2402d89 37760393 (21: nrow: 479 rrow: 0)
leaf: 0x2402d8a 37760394 (22: nrow: 479 rrow: 0)
leaf: 0x2402d8b 37760395 (23: nrow: 479 rrow: 0)
leaf: 0x2402d8c 37760396 (24: nrow: 479 rrow: 0)
leaf: 0x2402d8d 37760397 (25: nrow: 479 rrow: 0)
leaf: 0x2402d8e 37760398 (26: nrow: 479 rrow: 0)
leaf: 0x2402d8f 37760399 (27: nrow: 479 rrow: 0)
leaf: 0x2402d91 37760401 (28: nrow: 479 rrow: 0)
leaf: 0x2402d92 37760402 (29: nrow: 479 rrow: 0)
leaf: 0x2402d93 37760403 (30: nrow: 479 rrow: 0)
leaf: 0x2402d94 37760404 (31: nrow: 479 rrow: 0)
leaf: 0x2402d95 37760405 (32: nrow: 479 rrow: 0)
leaf: 0x2402d96 37760406 (33: nrow: 479 rrow: 0)
leaf: 0x2402d97 37760407 (34: nrow: 479 rrow: 0)
leaf: 0x2402d98 37760408 (35: nrow: 0 rrow: 0)
leaf: 0x2402d9f 37760415 (36: nrow: 479 rrow: 0)
leaf: 0x2402da1 37760417 (37: nrow: 479 rrow: 0)
leaf: 0x2402da2 37760418 (38: nrow: 479 rrow: 0)
leaf: 0x2402da3 37760419 (39: nrow: 479 rrow: 0)
leaf: 0x2402da4 37760420 (40: nrow: 479 rrow: 0)
leaf: 0x2402da5 37760421 (41: nrow: 479 rrow: 0)
leaf: 0x2402da6 37760422 (42: nrow: 479 rrow: 0)
leaf: 0x2402da7 37760423 (43: nrow: 479 rrow: 0)
leaf: 0x2402da8 37760424 (44: nrow: 479 rrow: 0)
leaf: 0x2402da9 37760425 (45: nrow: 479 rrow: 0)
leaf: 0x2402daa 37760426 (46: nrow: 479 rrow: 0)
leaf: 0x2402dab 37760427 (47: nrow: 479 rrow: 0)
leaf: 0x2402dac 37760428 (48: nrow: 479 rrow: 0)
leaf: 0x2402dad 37760429 (49: nrow: 479 rrow: 0)
leaf: 0x2402dae 37760430 (50: nrow: 479 rrow: 0)
leaf: 0x2402daf 37760431 (51: nrow: 479 rrow: 0)
leaf: 0x2402db1 37760433 (52: nrow: 479 rrow: 0)
leaf: 0x2402db2 37760434 (53: nrow: 479 rrow: 0)
leaf: 0x2402db3 37760435 (54: nrow: 479 rrow: 0)
leaf: 0x2402db4 37760436 (55: nrow: 479 rrow: 0)
leaf: 0x2402db5 37760437 (56: nrow: 479 rrow: 0)
leaf: 0x2402db6 37760438 (57: nrow: 479 rrow: 0)
leaf: 0x2402db7 37760439 (58: nrow: 479 rrow: 0)
leaf: 0x2402db8 37760440 (59: nrow: 479 rrow: 0)
leaf: 0x2402db9 37760441 (60: nrow: 479 rrow: 0)
leaf: 0x2402dba 37760442 (61: nrow: 479 rrow: 0)
leaf: 0x2402dbb 37760443 (62: nrow: 479 rrow: 0)
leaf: 0x2402dbc 37760444 (63: nrow: 479 rrow: 0)
leaf: 0x2402dbd 37760445 (64: nrow: 479 rrow: 0)
leaf: 0x2402dbe 37760446 (65: nrow: 479 rrow: 0)
leaf: 0x2402dbf 37760447 (66: nrow: 479 rrow: 0)
leaf: 0x2402dc1 37760449 (67: nrow: 479 rrow: 0)
leaf: 0x2402dc2 37760450 (68: nrow: 479 rrow: 0)
leaf: 0x2402dc3 37760451 (69: nrow: 479 rrow: 0)
leaf: 0x2402dc4 37760452 (70: nrow: 479 rrow: 0)
leaf: 0x2402dc5 37760453 (71: nrow: 479 rrow: 0)
leaf: 0x2402dc6 37760454 (72: nrow: 479 rrow: 0)
leaf: 0x2402dc7 37760455 (73: nrow: 479 rrow: 0)
leaf: 0x2402dc8 37760456 (74: nrow: 479 rrow: 0)
leaf: 0x2402dc9 37760457 (75: nrow: 479 rrow: 0)
leaf: 0x2402dca 37760458 (76: nrow: 479 rrow: 0)
leaf: 0x2402dcb 37760459 (77: nrow: 479 rrow: 0)
leaf: 0x2402dcc 37760460 (78: nrow: 479 rrow: 0)
leaf: 0x2402dcd 37760461 (79: nrow: 479 rrow: 0)
leaf: 0x2402dce 37760462 (80: nrow: 479 rrow: 0)
leaf: 0x2402dcf 37760463 (81: nrow: 479 rrow: 0)
leaf: 0x2402dd1 37760465 (82: nrow: 479 rrow: 6)
leaf: 0x2402dd2 37760466 (83: nrow: 479 rrow: 479)
leaf: 0x2402dd3 37760467 (84: nrow: 479 rrow: 479)
.........
leaf: 0x2402d9b 37760411 (203: nrow: 533 rrow: 533)
leaf: 0x2402d9c 37760412 (204: nrow: 533 rrow: 533)
leaf: 0x2402d9d 37760413 (205: nrow: 533 rrow: 533)
leaf: 0x2402d9e 37760414 (206: nrow: 50 rrow: 50)
----- end tree dump
--前面的leaf 的rrow都是0,也就是索引空块,一直到leaf: 0x2402dd1 37760465 (82: nrow: 479 rrow: 6),才有记录.
--这也可以看出取min(id)的逻辑读为什么是85. root(1次)+leaf(84)[注意leaf从-1开始.]
9.遇到这种模式的应用,最佳的方式我认为定期shrink数据,同时shrink索引,这样一定程度减少索引空块.
ALTER TABLE SCOTT.T ENABLE ROW MOVEMENT;
ALTER TABLE SCOTT.T SHRINK SPACE CASCADE;
SCOTT@test01p> validate index i_t_id;
Index analyzed.
SCOTT@test01p> @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 144 I_T_ID 59999 125 899381 8000 124 1 1363 8032 0 0 59999
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 1008032 900744 90 1 3 0 0 0 0
SCOTT@test01p> select count(id) from t;
COUNT(ID)
----------
59999
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 18tmpvf8my4qr, child number 0
-------------------------------------
select count(id) from t
Plan hash value: 3548397654
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 58 (100)| | 1 |00:00:00.04 | 134 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.04 | 134 |
| 2 | INDEX FAST FULL SCAN| I_T_ID | 1 | 99999 | 488K| 58 (0)| 00:00:01 | 59999 |00:00:00.02 | 134 |
--------------------------------------------------------------------------------------------------------------------------
SCOTT@test01p> select min(id) from t;
MIN(ID)
----------
50001
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 01jh0zwxfsdp4, child number 0
-------------------------------------
select min(id) from t
Plan hash value: 3363318368
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.01 | 2 |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_ID | 1 | 1 | 5 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------------------
SCOTT@test01p> select id from t where id no rows selected
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gu6hp3xvpwbu8, child number 0
-------------------------------------
select id from t where id Plan hash value: 1420114084
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 43 (100)| | 0 |00:00:00.01 | 2 |
|* 1 | INDEX RANGE SCAN| I_T_ID | 1 | 20000 | 97K| 43 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------------
--这样逻辑读在比较合理的范围.实际上像这种模式选择合理的分析表时间也变的很重要.这是另外的话题,到此结束.
