- 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
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
Ancak, eğer
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
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
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
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;
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.
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.
Kayıtlar
0 yorum:
Yorum Gönder