Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 index_stats  [new]
Paranoiac
Member

Откуда: Saint Petersburg
Сообщений: 389
Нужна помощь в понимания некоторых вещей:
SQL> create table hr.test_fragmen(id,value,num) tablespace users as select * from (select level,mod(level,5),dbms_random.value(1,50) from dual connect by level<=10000000);
 
Table created
 
SQL> create index hr.idx_test_id_val on hr.test_fragmen(id,value) parallel 12 nologging;

Index created
SQL> exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'TEST_FRAGMEN',estimate_percent => 100,degree => 6,cascade => true);
SQL> 
 
PL/SQL procedure successfully completed

 
SQL> analyze index hr.IDX_TEST_ID_VAL validate structure;
 
Index analyzed
 
SQL> select * from index_stats;
 
    HEIGHT     BLOCKS NAME                           PARTITION_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 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
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         3      28160 IDX_TEST_ID_VAL                                                 10000000      27486   196888893       7996      27485         50      354545       8028           0               0      10000000                 1   220179456  197243438         90            1                    4          0            0              0                0
 

 SQL> select sum(bytes) from dba_extents where segment_name='IDX_TEST_ID_VAL';
 
SUM(BYTES)
----------
 230686720
 
SQL> delete from hr.TEST_FRAGMEN where value=2;
 
2000000 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select sum(bytes) from dba_extents where segment_name='IDX_TEST_ID_VAL';
 
SUM(BYTES)
----------
 230686720
 
SQL> analyze index hr.IDX_TEST_ID_VAL validate structure;
 
Index analyzed
 
SQL> select * from index_stats;
 
    HEIGHT     BLOCKS NAME                           PARTITION_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 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
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         3      28160 IDX_TEST_ID_VAL                                                  8221617      27486   161321233       7996      27485         50      354545       8028      221617         4230320       8221617                 1   220179456  161675778         74            1                    4          0            0              0                0
 
SQL> exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'TEST_FRAGMEN',estimate_percent => 100,degree => 6,cascade => true);
 
PL/SQL procedure successfully completed
 
SQL> analyze index hr.IDX_TEST_ID_VAL validate structure;
 
Index analyzed
 
SQL> select * from index_stats;
 
    HEIGHT     BLOCKS NAME                           PARTITION_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 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
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         3      28160 IDX_TEST_ID_VAL                                                  8221617      27486   161321233       7996      27485         50      354545       8028      221617         4230320       8221617                 1   220179456  161675778         74            1                    4          0            0              0                0
 

SQL> alter index hr.IDX_TEST_ID_VAL shrink space compact;
 
Index altered
 
SQL> analyze index hr.IDX_TEST_ID_VAL validate structure;
 
Index analyzed
 
SQL> select * from index_stats;
 
    HEIGHT     BLOCKS NAME                           PARTITION_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 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
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         3      28160 IDX_TEST_ID_VAL                                                  8000000      22013   157090913       7996      22012         50      283781       8028           0               0       8000000                 1   176417348  157374694         90            1                    4          0            0              0                0
 
SQL> select sum(bytes) from dba_extents where segment_name='IDX_TEST_ID_VAL';
 
SUM(BYTES)
----------
 230686720
 
SQL> alter index hr.IDX_TEST_ID_VAL shrink space;
 
Index altered
 
SQL> select sum(bytes) from dba_extents where segment_name='IDX_TEST_ID_VAL';
 
SUM(BYTES)
----------
 184483840


Непонятно следующее:
1.почему сразу после удаления хотя удалилось 2000000 строк в index_stats DEL_LF_ROWS=221617
2.почему пока я не спресовал индекс, у меня LF_ROWS=8221617
С чем это связано,подскажите,плиз.
Заранее спасибо!
18 мар 14, 15:53    [15746729]     Ответить | Цитировать Сообщить модератору
 Re: index_stats  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1635
Paranoiac,

Это случай delayed block cleanout для блоков, для которых не был произведен commit cleanout.
Я использовал скрипт из вложения для демонстрации. В нем регулируется кол-во строк в таблице для теста, из которой удаляется четверть строк.
В своем окружении я использовал: 1, 3, 5, 7, 10 миллионов строк для тестов.
В скрипте дополнительно сохраняется index_stats, v$mystat для анализа.
Ниже index_stats для разного кол-ва строк (num_rows - исходное кол-во строк, lf_rows - index_stats.lf_rows):
SQL> with nr as (
  2    select 1e6 num_rows from dual union all
  3    select 3e6 from dual union all
  4    --select 4e6 from dual union all
  5    select 5e6 from dual union all
  6    select 7e6 from dual union all
  7    select 10e6 from dual)
  8  select num_rows, lf_rows, lf_blks, del_lf_rows,
  9         round(num_rows/lf_blks) rows_per_block,
 10         round(num_rows/lf_blks/4) del_rows_per_block,
 11         round((1-del_lf_rows/(num_rows/4))*100) del_diff_pct,
 12         round((num_rows/4-del_lf_rows)/(num_rows/lf_blks/4)) del_block_diff
 13    from
 14  (select iss.*
 15    from index_stats_snap iss, nr
 16   where iss.num_rows = nr.num_rows);

  NUM_ROWS    LF_ROWS    LF_BLKS DEL_LF_ROWS ROWS_PER_BLOCK DEL_ROWS_PER_BLOCK DEL_DIFF_PCT DEL_BLOCK_DIFF
---------- ---------- ---------- ----------- -------------- ------------------ ------------ --------------
   1000000    1000000       2226      250000            449                112            0           0
   3000000    2701001       6966      451001            431                108           40           2777
   5000000    4055144      11705      305144            427                107           76           8848
   7000000    5250000      16444           0            426                106          100          16444
  10000000    7500000      23553           0            425                106          100          23553

Видно, что с увеличением кол-ва строк в тестовой таблице (num_rows) -> lf_rows (кол-во строк в листовых блоках) стремится к кол-ву строк после удаления.
del_diff_pct (отклонение del_lf_rows от реального кол-ва удаленных строк) - увеличивается.
Если же посмотреть снимки v$mystat до и после analyze index, то видим:
SQL> with st1 as (select 'ANALYZ' stat_type from dual),
  2    st2 as (
  3    select 1e6 num_rows from dual union all
  4    select 3e6 from dual union all
  5    --select 4e6 from dual union all
  6    select 5e6 from dual union all
  7    select 7e6 from dual union all
  8    select 1e7 from dual)
  9  select name, "1M", "3M",/* "4M",*/ "5M", "7M", "10M"
 10    from (
 11  select s1.num_rows, s1.name, s2.value-s1.value value_diff
 12    from stats_snap s1, stats_snap s2,
 13         (select * from st1, st2)
 14           stat_type
 15   where s1.stat_type = 'BEFORE_'||stat_type.stat_type
 16     and s1.num_rows = stat_type.num_rows
 17     and s2.stat_type = 'AFTER_'||stat_type.stat_type
 18     and s2.num_rows = s1.num_rows
 19     and s2.statistic# = s1.statistic#
 20     and s2.value>s1.value
 21     and regexp_like(s1.name, 'cleanout|^calls|redo size|consistent|db block')
 22     )
 23   pivot(max(value_diff) for num_rows in (1e6 "1M", 3e6 "3M",/* 4e6 "4M",*/ 5e6 "5M", 7e6 "7M", 1e7 "10M"))
 24   order by name;

NAME                                                   1M         3M         5M         7M        10M
---------------------------------------------- ---------- ---------- ---------- ---------- ----------
active txn count during cleanout                        2          6          3          3          5
calls to get snapshot scn: kcmgss                      15         15         15         13         13
calls to kcmgas                                         9          9          8          8          6
calls to kcmgcs                                        29         30         29         14         12
cleanout - number of ktugct calls                       2       2841       8875      16447      23558
cleanouts only - consistent read gets                           2835       8872      16444      23553
commit cleanouts                                        6          7          6          7          6
commit cleanouts successfully completed                 6          7          6          7          6
commit txn count during cleanout                                2835       8872      16444      23553
consistent gets                                      4529      16855      32374      49402      70741
consistent gets - examination                          21       2860       8894      16466      23577
consistent gets from cache                           4529      16855      32374      49402      70741
consistent gets from cache (fastpath)                2272       7012      11753      16461      23569
db block changes                                       73       2910       8947      16502      23593
db block gets                                          96         96         92         70         48
db block gets from cache                               96         96         92         70         48
db block gets from cache (fastpath)                    31         26         24         16         12
deferred (CURRENT) block cleanout applications          1          1          2          1          1
immediate (CR) block cleanout applications                      2835       8872      16444      23553
no work - consistent read gets                       4472      11124      14573      16484      23604
redo size                                           49432     253740     689188    1232264    1741704

Конкретно, cleanouts only - consistent read gets и другие статистики указывают на вклад delayed block cleanout в отличие index_stats.del_lf_rows от реального кол-ва удаленных строк.
В скрипте также дополнительно производится дамп двух индексных блоков. "Левого", затронутого fast commit, и "правого", не затронутого fast commit.
Левый блок (видим upper bound commit во втором ITL слоте):
+

Block header dump:  0x01f2e88f
 Object id on Block? Y
 seg/obj: 0x2647ae  csc: 0x00.35437002  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1f2e888 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x002e.011.000018eb  0x00c09ca4.07fb.3c  --U-  120  fsc 0x0703.35443c67
Leaf block dump
===============
header address 139661009141860=0x7f055cd6c064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 479
kdxcofbo 994=0x3e2
kdxcofeo 1810=0x712
kdxcoavs 816
kdxlespl 0
kdxlende 120
kdxlenxt 32696464=0x1f2e890
kdxleprv 32696462=0x1f2e88e
kdxledsz 0
kdxlebksz 8032
row#0[8019] flag: ---D--, lock: 2, len=13
col 0; len 3; (3):  c2 0f 2d
col 1; len 6; (6):  01 f2 e8 17 00 01
row#1[8006] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 0f 2e
col 1; len 6; (6):  01 f2 e8 17 00 02
row#2[7993] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 0f 2f
col 1; len 6; (6):  01 f2 e8 17 00 03
row#3[7980] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 0f 30
col 1; len 6; (6):  01 f2 e8 17 00 04
row#4[7967] flag: ---D--, lock: 2, len=13
col 0; len 3; (3):  c2 0f 31
col 1; len 6; (6):  01 f2 e8 17 00 05
row#5[7954] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 0f 32
col 1; len 6; (6):  01 f2 e8 17 00 06

Правый блок до delayed block cleanout:
+

Block header dump:  0x01f36c34
 Object id on Block? Y
 seg/obj: 0x2647ae  csc: 0x00.35437002  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1f36c00 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x002e.011.000018eb  0x00c28416.0845.2c  ----  106  fsc 0x0706.00000000
Leaf block dump
===============
header address 139661009141860=0x7f055cd6c064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 422
kdxcofbo 880=0x370
kdxcofeo 1706=0x6aa
kdxcoavs 826
kdxlespl 0
kdxlende 106
kdxlenxt 32730165=0x1f36c35
kdxleprv 32730163=0x1f36c33
kdxledsz 0
kdxlebksz 8032
row#0[8017] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 44
col 1; len 6; (6):  01 f3 4d b2 00 42
row#1[8002] flag: ---D--, lock: 2, len=15
col 0; len 5; (5):  c4 03 64 53 45
col 1; len 6; (6):  01 f3 4d b2 00 43
row#2[7987] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 46
col 1; len 6; (6):  01 f3 4d b2 00 44
row#3[7972] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 47
col 1; len 6; (6):  01 f3 4d b2 00 45
row#4[7957] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 48
col 1; len 6; (6):  01 f3 4d b2 00 46
row#5[7942] flag: ---D--, lock: 2, len=15
col 0; len 5; (5):  c4 03 64 53 49
col 1; len 6; (6):  01 f3 4d b2 00 47

и после:
+

Block header dump:  0x01f36c34
 Object id on Block? Y
 seg/obj: 0x2647ae  csc: 0x00.35443d7d  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1f36c00 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x002e.011.000018eb  0x00c28416.0845.2c  C---    0  scn 0x0000.35443c67
Leaf block dump
===============
header address 139661009141860=0x7f055cd6c064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 316
kdxcofbo 668=0x29c
kdxcofeo 1706=0x6aa
kdxcoavs 2624
kdxlespl 0
kdxlende 0
kdxlenxt 32730165=0x1f36c35
kdxleprv 32730163=0x1f36c33
kdxledsz 0
kdxlebksz 8032
row#0[8017] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 44
col 1; len 6; (6):  01 f3 4d b2 00 42
row#1[7987] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 46
col 1; len 6; (6):  01 f3 4d b2 00 44
row#2[7972] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 47
col 1; len 6; (6):  01 f3 4d b2 00 45
row#3[7957] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 48
col 1; len 6; (6):  01 f3 4d b2 00 46
row#4[7927] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 4a
col 1; len 6; (6):  01 f3 4d b2 00 48
row#5[7912] flag: ------, lock: 0, len=15
col 0; len 5; (5):  c4 03 64 53 4b
col 1; len 6; (6):  01 f3 4d b2 00 49

Видим, что kdxconro/kdxlende изменились с 422/106 (до cleanout) до 316/0 - после.
Чтобы получить "крайний" случай, когда все индексные блоки будут подвержены delayed block cleanout, в скрипте нужно указать:
def dump_before_commit="true"

Это приведет к результатам аналогичным с "большим" кол-вом строк (index_stats.del_lf_rows=0).
Собственно, указанное разобрано у Richard Foote: https://richardfoote.wordpress.com/2008/06/23/deleted-index-entries-part-iii-slip-away/

К сообщению приложен файл (index_stats_del_lf_rows_oddity_cleanout.sql - 4Kb) cкачать
8 апр 14, 19:02    [15851883]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить