实验内容：
表数据量与索引性能的关系
特别说明：
生产环境中的row_id为应用程序生成，无明显生成规则，因此使用row_id建立分区表可行度不高；
实验结论：
一、各表查询执行计划路线都相同，但cost、consistent gets、physical reads三个指标逐渐增加，说明数据量的增加对于CPU和物理块还是有影响的；
二、通过tkprof工具查看更详细的变化，可以观察到cr、pr、pw、time、us cost几个参数在逐渐递增；
三、随着数据量增加，索引树的高度也会增加，oracle的建议是索引树高度超过4需要重建索引，但如果因为数据量的累加而导致高度增加，重建不起作用，本人唯一了解到的办法只有物理分表，单表数据量的控制可以有效避免索引的性能恶化；
备注：本人实验中最高只将树高度顶至3,没能产生高度为4的索引，所以不清楚数据量的累加是否会导致索引树高度超过3
以下为实验步骤：
一、创建实验表
create table t_index_test as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 10000');//一万笔数据
create table t_index_test_10w as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 100000');//十万笔数据
create table t_index_test_100w as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 1000000') ;//一百万笔数据
create table t_index_test_1000w as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 10000000') ;//一千万笔数据
create table t_index_test_10000w as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 100000000');//一亿笔数据
二、创建索引
create index idx_1w on t_index_test (row_id);
create index idx_10w on t_index_test_10w (row_id);
create index idx_100w on t_index_test_100w (row_id);
create index idx_1000w on t_index_test_1000w (row_id);
create index idx_10000w on t_index_test_10000w (row_id);
三、各表根据row_id查询的执行计划记录
SQL> select * from scott.t_index_test where row_id='1234';
Execution Plan
----------------------------------------------------------
Plan hash value: 2250468099
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |35 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST |1 | 35 |2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_1W | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ROW_ID"=1234)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.t_index_test_10w where row_id='12345';
Execution Plan
----------------------------------------------------------
Plan hash value: 481348210
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST_10W | 1 | 35 | 2(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_10W | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ROW_ID"=12345)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
74 consistent gets
1 physical reads
0 redo size
606 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.t_index_test_100w where row_id='123456';
Execution Plan
----------------------------------------------------------
Plan hash value: 1315594877
-------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 35 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST_100W | 1 | 35 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_100W| 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ROW_ID"=123456)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
75 consistent gets
2 physical reads
0 redo size
607 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.t_index_test_1000w where row_id='1234567';
Execution Plan
----------------------------------------------------------
Plan hash value: 2655249834
--------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 35 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST_1000W | 1 | 35 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_1000W | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ROW_ID"=1234567)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
84 consistent gets
2 physical reads
0 redo size
612 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.t_index_test_10000w where row_id='12345678';
Execution Plan
----------------------------------------------------------
Plan hash value: 584708459
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 |4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST_10000W |1 | 35 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_10000W | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ROW_ID"=12345678)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
86 consistent gets
3 physical reads
0 redo size
610 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
四、tkprof工具生成结果的记录
********************************************************************************
SQL ID: 3as912cxvm33j Plan Hash: 2250468099
select *
from
scott.t_index_test where row_id=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 13 0 0
Execute 1 0.01 0.05 0 29 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.05 0 46 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T_INDEX_TEST (cr=4 pr=0 pw=0 time=171 us cost=2 size=35 card=1)
1 1 1 INDEX RANGE SCAN IDX_1W (cr=3 pr=0 pw=0 time=168 us cost=1 size=0 card=1)(object id 89045)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 29.27 29.27
********************************************************************************
SQL ID: 51mvppfk9ajaz Plan Hash: 481348210
select *
from
scott.t_index_test_10w where row_id=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 6 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T_INDEX_TEST_10W (cr=4 pr=0 pw=0 time=925 us cost=2size=35 card=1)
1 1 1 INDEX RANGE SCAN IDX_10W (cr=3 pr=0 pw=0 time=889 us cost=1 size=0 card=1)(object id 89046)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 24.43 24.43
********************************************************************************
SQL ID: 8wuk0v6rkjxxw Plan Hash: 1315594877
select *
from
scott.t_index_test_100w where row_id=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 0 0
Fetch 2 0.00 0.01 1 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 1 7 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T_INDEX_TEST_100W (cr=5 pr=1 pw=0 time=18161 us cost=4 size=35 card=1)
1 1 1 INDEX RANGE SCAN IDX_100W (cr=4 pr=0 pw=0 time=20 us cost=3 size=0 card=1)(object id 89047)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.01 0.01
SQL*Net message from client 2 18.90 18.90
********************************************************************************
SQL ID: 7chhd95b0jrzn Plan Hash: 2655249834
select *
from
scott.t_index_test_1000w where row_id=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 0 0
Fetch 2 0.00 0.01 1 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 1 7 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T_INDEX_TEST_1000W (cr=5 pr=1 pw=0 time=17290 us cost=4 size=35 card=1)
1 1 1 INDEX RANGE SCAN IDX_1000W (cr=4 pr=0 pw=0 time=19 us cost=3 size=0 card=1)(object id 89048)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.01 0.01
SQL*Net message from client 2 18.32 18.32
********************************************************************************
SQL ID: 9m9uc8ugxgjmn Plan Hash: 584708459
select *
from
scott.t_index_test_10000w where row_id=:"SYS_B_0"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 0 0
Fetch 2 0.00 0.02 1 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 1 7 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T_INDEX_TEST_10000W (cr=5 pr=1 pw=0 time=22253 us cost=4 size=35 card=1)
1 1 1 INDEX RANGE SCAN IDX_10000W (cr=4 pr=0 pw=0 time=22 us cost=3 size=0 card=1)(object id 89049)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.02 0.02
SQL*Net message from client 2 6.83 6.83
********************************************************************************
五、查看索引树高度记录
SQL> select s.table_name,s.index_name,s.blevel from dba_indexes s where s.index_name like 'IDX_1%'
2 ;
TABLE_NAME INDEX_NAME BLEVEL
------------------------------ ------------------------------ ----------
T_INDEX_TEST IDX_1W 1
T_INDEX_TEST_10W IDX_10W 1
T_INDEX_TEST_100W IDX_100W 2
T_INDEX_TEST_1000W IDX_1000W 2
T_INDEX_TEST_10000W IDX_10000W 2
2456
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
