当前位置: 首页 > news >正文

[20150321]索引空块的问题.txt

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

--这样逻辑读在比较合理的范围.实际上像这种模式选择合理的分析表时间也变的很重要.这是另外的话题,到此结束.

相关文章:

  • C# 多线程之同步输出奇偶数
  • 自已写的框架拿出来等人来拍转 (序)
  • 电商做促销活动的要考虑到的
  • IIS6升级到IIS7后,UrlRewriting提示404,AJAX会提示未声明SYS错误
  • Qt widgets
  • 安装IIS步骤图解
  • centos6.5安装LNMP
  • CISCO 技术大集合
  • 如何删除oracle的用户连接
  • SQL查询主键
  • Makefile编写 五 隐含规则
  • 解决错误“PHP has encountered an Access Violation at 01551077”
  • Qstring
  • Eclipse中的项目删除
  • mysql5.1,5.5,5.6做partition时支持的函数
  • 《Java8实战》-第四章读书笔记(引入流Stream)
  • Angular 4.x 动态创建组件
  • Asm.js的简单介绍
  • ES6核心特性
  • Git同步原始仓库到Fork仓库中
  • idea + plantuml 画流程图
  • Java新版本的开发已正式进入轨道,版本号18.3
  • Service Worker
  • SpiderData 2019年2月16日 DApp数据排行榜
  • 从0到1:PostCSS 插件开发最佳实践
  • 跨域
  • 与 ConTeXt MkIV 官方文档的接驳
  • 源码之下无秘密 ── 做最好的 Netty 源码分析教程
  • 再次简单明了总结flex布局,一看就懂...
  • 东超科技获得千万级Pre-A轮融资,投资方为中科创星 ...
  • 支付宝花15年解决的这个问题,顶得上做出十个支付宝 ...
  • #{}和${}的区别是什么 -- java面试
  • #我与Java虚拟机的故事#连载05:Java虚拟机的修炼之道
  • #我与Java虚拟机的故事#连载06:收获颇多的经典之作
  • #预处理和函数的对比以及条件编译
  • (vue)el-checkbox 实现展示区分 label 和 value(展示值与选中获取值需不同)
  • (ZT)北大教授朱青生给学生的一封信:大学,更是一个科学的保证
  • (待修改)PyG安装步骤
  • (附源码)spring boot北京冬奥会志愿者报名系统 毕业设计 150947
  • (机器学习-深度学习快速入门)第三章机器学习-第二节:机器学习模型之线性回归
  • (转)Unity3DUnity3D在android下调试
  • (转)编辑寄语:因为爱心,所以美丽
  • .NET I/O 学习笔记:对文件和目录进行解压缩操作
  • .NET 中什么样的类是可使用 await 异步等待的?
  • .NET处理HTTP请求
  • .net图片验证码生成、点击刷新及验证输入是否正确
  • /etc/skel 目录作用
  • @ComponentScan比较
  • [ Linux 长征路第二篇] 基本指令head,tail,date,cal,find,grep,zip,tar,bc,unname
  • [ 云计算 | AWS ] 对比分析:Amazon SNS 与 SQS 消息服务的异同与选择
  • [20150707]外部表与rowid.txt
  • [20180224]expdp query 写法问题.txt
  • [BZOJ] 2044: 三维导弹拦截
  • [BZOJ4566][HAOI2016]找相同字符(SAM)
  • [c++] 自写 MyString 类