Pages

25 Nisan 2011 Pazartesi

Indekslerin daha büyük bloğa sahip tablespace içerisine taşınması ve performanslarının durumu

Eğer bir indeks daha büyük blok büyüklüğündeki tablespace içinde yeniden inşa edilirse IFFS(Index Fast Full Scan) performansında iyi veya kötü yönde bir değişiklik olurmu?

  • Rastgele giriş yapılan indeksler %70-75 aralığında PCT_USED değerine sahipken, bu indeksler, doldurulan farklı evreler içinde eş zamanlı olarak rastgele 50-50 blok bölünmesi meydana getirir. Bir indeksi yeniden insa ederek, %15 kadar indeks yoğunluğu artabilir ve böylece tüm indeks büyüklüğü azalabilir. Azalan blok geneli aynı zamanda indeks büyüklüğü ve blok büyüklüklerinin farklılıklarına bağlı olarak, indeksi de bir miktar azaltabilir. Fast Full Index Scan üyelik maliyetleri indeksin tüm büyüklüğü ile orantılı olduğundan, bir indeksi birleştirmek(defragmantation), en fazla potensiyel faydalar sunan erişim yolu olmaktadır. Bir indeksin büyüklüğünü azaltmak bu yüzden daha sonraki performansı etkiler.  Oysaki, mevcut blok büyüklüğünde indeksin yeniden inşası muhtemelen aynı benzer sonuca ulaşır(artı blok genel giderleri), indeksi sıkılaştırır ve potensiyel olarak daha iyi performans ile sonuçlanır. Buna karşın, indeks bloğu bir kez tekrardan bölünmeye başladımı, neticede bu indeks kendisinin önceki durumuna tekrar geri döner. İşte bu yüzden, daha büyük blok büyüklüğünün ötesinde, indeksin birleştirilmesi (defragmentation) genel bir performans iyileştirme yolu olmaktadır.

  • İndeksi daha büyük bloğa sahip tablespace içinde saklayarak, bu indeksin ait olduğu tablodan farklı bir fiziksel veri dosyası içinde saklamak gerekmektedir. Bu veri dosyası performansı arttırmak için nispeten daha hızlı bir disk olmalı ve indeksler dışında başka dosyalar içermemelidir. Daha büyük blok büyüklüğünden ziyade, indeksin depolandığı yeni yerin fiziksel karakteristikleri esasında performansı etkiler. Eğer bir indeks aynı blok büyüklüğünde, daha büyük bloğa sahip indeksler ile aynı fiziksel karakteristiklere sahip yerde yeniden inşa edilirse, daha sonraki performans aynı şekilde artar(veya azalır).
Tabii bunun yanında pekçok muhtemel sebepler vardır, sistem daha büyük blok büyüklüğüne sahip indeksler kullanırken, daha az meşgul olmakla beraber indeksin daha büyük kısmı fiziksel olarak önbelleklenir.

Indeks Fast Full Scan, nadirende olsa bir ölçeklenebilirlik sorunudur. Bu noktada, aslında gerçekten kullandığımız uygulamaların yüzlerce büyüklükte eşzamanlı indeks fast full scan yapmasını istermiyiz? Uygulamayı bu gereksiz indeks fast full scan giderlerinden kurtarmak, indeksleri boş yere daha geniş bloğa sahip tablespace içine taşımaktan daha verimli bir yol olacaktır. Tabii bu başka bir yazıda tartışma konusu olur. 

Peki, esas bu yazının ana konusuna gelelim. Indeks Fast Full Scan işleminin performansı indeksin daha büyük bloğa sahip tablespace içine taşınması neticesinde değişirmi? Kesinlikle, değişir…Ancak, bu bahsedilen değişim, indeksin daha büyük bloğa sahip olması sonucunda çoklu blok(multiblock) okuma performansının geliştirilmiş olması anlamına gelmez.  

Oracle, çoklu blok okuması gerçekleştirirken, çoklu blok başına ne kadar blok okuduğunu belirlemek için db_file_multiblock_read_count parametresindeki değeri kullanır. db_file_multiblock_read_count değerini belirlemek için, Oracle, sistem istatistiklerini kullanarak en uygun değeri atar. Yani, eğer db_file_multiblock_read_count değeri 16 olarak atanmışsa, çoklu-blok okuma işlemi süresince Oracle bir kerede 16 blok okumak için çalışacaktır. Bu parametre değerinin veritabanının varsayılan blok büyüklüğüne dayandığını unutmamak gerekir. Yani, eğer veritabanı varsayılan blok büyüklüğü 8K ve db_file_multiblock_read_count değeri 16 ise, çoklu-blok işlemleri süresince Oracle bir kerede 16k x 8 blok okumasına çalışacak ve okuyacaktır.

Ancak, eğer varsayılan blok büyüklüğünde olmayan(mesela 16k) bir segment varsa, blok okuma süresince Oracle o an okunan blok sayısını genişletir, böylece tüm çoklu blok okumalarının maksimum büyüklüğü, o varsayılan blok büyüklüğü ile özdeş olur

Eğer db_file_multiblock_read_count değeri 16 ve varsayılan blok büyüklüğü 8K ise, bir objenin çoklu blok okuması bir kerede sadece 8 blok olacaktır(16 blok değil)… 2K tablespace blok büyüklüğünde bir tablespace içindeki bir objenin çoklu blok okuması ise bir kerede 64 blok olacaktır. İşte bu yüzden, bir çoklu-blok okumasının gerçek büyüklüğü veritabanı içindeki bir objenin blok büyüklüğüne bağlı kalmaksızın aynıdır.

Bu olayı vurgulamanın en kolay yolu, bir oturumun izlenmesi ve birbirine benzeyen çoklu-blok okumalarının belirli büyüklüklerini görmektir. Indeks Fast Full Scan olayı gerçekleştiren 8K blok büyüklüğündeki bir indeks üzerindeki izleme(trace) örneği aşağıdaki gibidir.

WAIT #1: nam=’db file scattered read’ ela= 1487 file#=8 block#=1050 blocks=16 obj#=78294 tim=615409554677
WAIT #1: nam=’db file scattered read’ ela= 1377 file#=8 block#=1066 blocks=16 obj#=78294 tim=615409557777
WAIT #1: nam=’db file scattered read’ ela= 1143 file#=8 block#=1082 blocks=16 obj#=78294 tim=615409561563

Oracle çoklu-blok okuma işlemi başına 16 x 8K blok (128K) okuma olmaktadır. Ancak, eğer indeks 16 k blok büyüklüğünde bir tablespace içerisinde yeniden oluşturulduğunda Fast Full Index Scan şu şekil olacaktır:

WAIT #1: nam=’db file scattered read’ ela= 1413 file#=6 block#=14 blocks=8 obj#=78296 tim=626802128684
WAIT #1: nam=’db file scattered read’ ela= 1447 file#=6 block#=22 blocks=8 obj#=78296 tim=626802131649
WAIT #1: nam=’db file scattered read’ ela= 2014 file#=6 block#=30 blocks=8 obj#=78296 tim=626802135222

Oracle artık, çoklu-blok başına 8 x 16K blok (128K) okumaktadır. Her iki indekste etkili olarak aynı işi yapmaktadır, her ikiside çoklu-blok başına 128K ya kadar etkili olarak okuma yapmaktadır. Peki o zaman performansta kırılma nerede oluyor?

Şimdi bu tezi incelemek üzere bir örnek yapalım ve canlı olarak performanstaki değişimleri gözlemleyelim.

Bu senaryo doğrultusunda önce 16K blok büyüklüğünde bir tablespace oluşturacağım. Ardından, başlangıçta 2 milyon kayıt bulunan basit bir tablo oluşturacağım.

SQL> alter system set db_16k_cache_size = 80m;
System altered.

SQL> create tablespace ts_16k
datafile '/u02/app/oracle/tbs_16k_test1.dbf'
size 1024M uniform size 1M
segment space management manual
blocksize 16K;
Tablespace created.

SQL> CREATE TABLE test_tbl AS
SELECT rownum id, add_months(sysdate,8)+7/trunc(mod(rownum,100) date
FROM dual
CONNECT BY LEVEL <=2000000;
Table created.

Ardından varsayılan blok büyüklüğünde(8K) bir tablespace içerisinde indeks oluşturacağım.

SQL> CREATE INDEX test_tbl_ix ON test_tbl(date);
Index created.

İndeksi defragmante etmek için tablo içindeki toplam 2 milyon kaydın yarısını siliyorum.

SQL> DELETE test_tbl WHERE mod(id,2)=1;
1000000 rows deleted.

SQL> COMMIT;
Commit complete.

Mevcut istatistikleri topluyorum.

SQL> exec dbms_stats.gather_table_stats(
ownname=>'UGUR',
tabname=>'TEST_TBL',
estimate_percent=>null,
method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

db_file_multiblock_read_count parametre değerinin 16 olarak ayarlandığını kontrol ediyoruz.

SQL> show parameter db_file_multiblock

NAME                         TYPE        VALUE
---------------------------- ----------- -----
db_file_multiblock_read_count integer    16

SQL> set autotrace traceonly

Önbellek içinde muhtemel blokların olmadığından emin olmak ve senaryo çalışmasının eksiksiz olmasını sağlamak için önbellek boşaltılır.

SQL> alter system flush buffer_cache;
System altered.

Oturumu izlemeye(trace) başlıyoruz.

SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.

Ardından Indeks Fast Full Scan yoluyla test_tbl_ix indeksinden çoklu-blok okuması gerçekleştirmek için bazı SQL sorgularını çalıştırıyoruz. “Parsing” olayına bağlı maliyetleri düşürmek için bu SQL komutunu “flush buffer_cache” komutundan önce çalıştırmanız tavsiye edilir.

SQL> SELECT /*+ index_ffs (test_tbl, test_tbl_ix) */ date_field FROM test_tbl where date_field > '’20- DEC-2011';

1000000 rows selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 251320893
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 7812K| 1173 (2)| 00:00:15 |
|* 1 | INDEX FAST FULL SCAN| TEST_TBL_IX | 1000K| 7812K| 1173 (2)| 00:00:15 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_FIELD">TO_DATE('2011-12-20 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))

Statistics
----------------------------------------------------------
0           recursive calls
0           db block gets
6320        consistent gets
5307        physical reads
0           redo size
5167309     bytes sent via SQL*Net to client
11385       bytes received via SQL*Net from client
1001        SQL*Net roundtrips to/from client
0           sorts (memory)
0           sorts (disk)

1000000 rows processed

Bu arada sürekli okumaların(consistent reads) değerinin 6320 olduğuna dikkat edin. İzleme işini kapatıyoruz.

SQL> alter session set events '10046 trace name context off';
Session altered.

Eğer bu sorgunun TKPROF raporuna bakarsak;

SELECT /*+ index_ffs (test_tbl, test_tbl_i) */ date_field FROM test_tbl where date_field > '20-DEC-2011’

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      0     0      0
Fetch    1001     0.73   1.23     5307   6320   0      1000000
------- ------    ----- -------   ------ ----  ------ -------
total    1003     0.73   1.23     5307   6320   0      1000000

CPU maliyetinin 0.73 saniye, geçen sürenin(elapsed) 1.23 saniye ve sorgu okumasının 6320 olduğunu gözlemliyoruz.

WAIT #8: nam='db file scattered read' ela= 738 file#=5 block#=242233 blocks=16 obj#=59933 tim=341634522940

Bu arada izleme dosyasında, db_file_multiblock_read_count değerininin 16 olarak ayarlanmasından beklendiği gibi, dağınık okumalar(scattered reads) bir kerede 16 blok okumaktadır. Şimdi bu performansı geliştirmeye çalışacağız. Şimdi indeksin daha büyük bloğa sahip bir tablespace içerisine taşınması yoluyla neler olacağını gözlemleyeceğiz…

Bu amaçla; ilk olarak 16k blok büyüklüğünde daha önceden oluşturduğumuz tablespace içerisine indeksi taşıyoruz, ardından önbelleği boşaltıyor ve izlemeyi açıyoruz.

SQL> alter index test_tbl_ix rebuild tablespace ts_16k;
Index altered.

SQL> alter system flush buffer_cache;
System altered.

SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.

Şimdi bir önceki çalıştırdığım sorguyu tekrardan çalıştırarak performansta bir değişme olup olmadığını test ediyorum.

SQL> SELECT /*+ index_ffs (test_tbl, test_tbl_i) */ date_field FROM test_tbl where date_field > '20-DEC-2011;

1000000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 251320893
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 7812K| 367 (4)| 00:00:05 |
|* 1 | INDEX FAST FULL SCAN| TEST_TBL_IX | 1000K| 7812K| 367 (4)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_FIELD">TO_DATE('2011-12-20 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))

Statistics
----------------------------------------------------------
1           recursive calls
0           db block gets
2317        consistent gets
1310        physical reads
0           redo size
5167309     bytes sent via SQL*Net to client
11385       bytes received via SQL*Net from client
1001        SQL*Net roundtrips to/from client
0           sorts (memory)
0           sorts (disk)

1000000 rows processed

Başlangıç olarak, sürekli okumaların 6230’dan 2317’ye düştüğünü gözlemliyoruz.

SQL> alter session set events '10046 trace name context off';
Session altered.

SELECT /*+ index_ffs (test_tbl, test_tbl_i) */ date_field FROM test_tbl where date_field > '20-DEC-2011’

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     0     0      0
Fetch    1001     0.79   1.08      1310  2317    0      1000000
------- ------    ----- -------   ------ ----  ------- -------
total    1003     0.79   1.08      1310  2317    0      1000000


Güzel bir sonuç! Geçen(elapsed) sürede 1,23 ten 1.08’e düştü. Yaklaşık %1.1 lik bir gelişme fena sonuç değil. Bunun yanında cevap süreside iyileşti, ve sonuçta indeksin 16K blok büyüklüğünde bir tablespace içine taşınmasıyla genel performansta ortalama %10’luk bir iyileşme meydana geldi.

WAIT #1: nam='db file scattered read' ela= 767 file#=7 block#=46 blocks=8 obj#=59933 tim=342035983379

Bu arada dağınık(scattered) okumaların varsayılan 8K büyüklükte 16 blok okuması yerine 8 blok okuması yaptığını ve böylece çoklu-blok okumalarının aynı kaldığını gözlemliyoruz.

16K blok büyüklüğünde bir tablespace içerisinde indeksin yeniden inşası sonucu performans gelişmesine karşılık, acaba 8K blok büyüklüğündeki eski tablespace içerisinde indeks basitçe yeniden inşa edilirse ne olur peki? Buna da bakalım hemen…

SQL> alter index test_tbl_ix rebuild tablespace test_tbl;
Index altered.

SQL> set autotrace traceonly

SQL> alter system flush buffer_cache;
System altered.

SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.

SQL> SELECT /*+ index_ffs (test_tbl, test_tbl_i) */ date_field FROM test_tbl where date_field > '20-DEC-2011';

1000000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 251320893
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 7812K| 594 (3)| 00:00:08 |
|* 1 | INDEX FAST FULL SCAN| TEST_TBL_IX | 1000K| 7812K| 594 (3)| 00:00:08 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_FIELD">TO_DATE('2011-12-20 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))

Statistics
----------------------------------------------------------
0           recursive calls
0           db block gets
3664        consistent gets
2655        physical reads
0           redo size
5167309     bytes sent via SQL*Net to client
11385       bytes received via SQL*Net from client
1001        SQL*Net roundtrips to/from client
0           sorts (memory)
0           sorts (disk)

1000000 rows processed

Yukardada görüldüğü üzere dağınık okumalar 16K blok büyüklüğündeki tablespace içindeki indeksin altına düşmese bile eski orijinal durumundaki gibide yüksek değil!

SQL> alter session set events '10046 trace name context off';
Session altered.

SELECT /*+ index_ffs (test_tbl, test_tbl_i) */ date_field FROM test_tbl where date_field > '20-DEC-2011'

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      0     0      0
Fetch    1001     0.65   0.99     2655   3664   0      1000000
------- ------    ----- -------   ------ ----  ------ -------
total    1003     0.65   0.99      2655  3664   0      1000000

İndeksin varsayılan blok büyüklüğündeki tablespace içerisinde inşası sonrasında geçen süre değeri, 16K blok büyüklüğündeki tablespace içinde bu indeksin inşası sonrasındaki değerinden bile daha düşük…Bu arada CPU süresi’de yaklaşık %10 daha iyi.

WAIT #1: nam='db file scattered read' ela= 668 file#=5 block#=222953 blocks=16 obj#=59933 tim=342792899797

Bu arada dağınık okumalar tekrardan bir seferde 16 blok okumaya geri döndüler. Yani, bir indeksi daha büyük bloğa sahip tablespace içerisinde inşa atmek kadar, o indeksi yeniden inşa etmekte indeks maliyetini önemli derecede düşürmektedir.

0 yorum:

Yorum Gönder