Veritabanina gönderdigimiz SQL sorgularina daha kisa sürede yanit almak ve daha düsük donanim gücü tüketmesi açisindan için çogu zaman veritabanina yeni indeksler ekleriz. Sunucuya gönderilen sorgunun içerigine göre olusturdugumuz bu indekslerin kullanim sikligina Oracle veritabani cost based hesabi yaparak (execution plan) karar vermektedir. Bu konuda veritabanindaki mevcut indesklerin hangi siklikla kullanilacagina spesifik olarak “optimizer_index_cost_adj” isimli Oracle sistem parametresi karar verir.
Optimizer_index_cost_adj parametresini biraz açarsak, “1? ile “10.000? arasinda bir deger atayarak (default deger 100) Oracle ‘in indeks hassasiyetini arttirip azaltabilirsiniz. Buna göre parametreye atanacak düsük bir degerde (10-50 arasinda) Oracle sonucunu almak istediginiz sorguya uygun olan bir indeks kullanmaya (force) çalisacaktir. Paremetreye verilecek olan deger arttirildigi taktirde (misal 200-10.000 arasinda) Oracle indeks kullanimindan feragat ederek tablo üzerinde full tablescan ‘e yönelecektir.
Performance tuning amaçli kullanilan optimizer_index_cost_adj parametresini kendi sisteminize uyarlarken dikkat edilmesi gereken nokta, parametrenin degeriyle oynamadan önce veritabanin güncel istatistiginin alinmasidir. Buna göre parametre degisikliginden kaynakli olusabilecek yanlis execution plan kullanimi ve akabinde yasanacak performans kaybinin önüne geçilebilir.
Asagida örnek bir sorgu çalistirarak konuyu açiklamaya çalisacagim.
* Öncelikle optimizer_index_cost_adj parametresinin degerini “10? olarak degistiriyorum.
SQL> alter system set optimizer_index_cost_adj=10 scope=memory;
* SQL sorgunun execution planini incelemek amaciyla trace ‘i etkilestirip sorgumu çalistiriyorum.
SQL> select count(*) from test.test_tbl where isim=ahmet, uyelik_tarihi>sysdate-100
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_9ch1da8da5d612n3
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 7123371084
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 22 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 32 | | | | |
| 2 | CONCATENATION | | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 3 | 96 | 7 (0)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_TBL | 3 | 96 | 7 (0)| 00:00:01 | KEY | KEY |
|* 5 | INDEX RANGE SCAN | TEST_TBL_IDX1 | 2 | | 7 (0)| 00:00:01 | KEY | KEY |
| 6 | PARTITION RANGE ITERATOR | | 90156 | 2817K| 7 (0)| 00:00:01 | KEY | KEY |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_TBL | 90156 | 2817K| 7 (0)| 00:00:01 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | TEST_TBL_IDX1 | 2 | | 7 (0)| 00:00:01 | KEY | KEY |
| 9 | PARTITION RANGE ITERATOR | | 8 | 256 | 7 (0)| 00:00:01 | KEY | KEY |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_TBL | 8 | 256 | 7 (0)| 00:00:01 | KEY | KEY |
|* 11 | INDEX RANGE SCAN | TEST_TBL_IDX1 | 2 | | 7 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------
* Oracle ‘i mevcut indeksleri daha fazla kullanmasi için düzenledikten sonra, yukaridaki detayli execution plan dökümünden anlasilacagi üzere çalistirdigimiz sorgu tabloda dogru indeksi tespit edip (veritabani istatistiginin güncel olmasina bagli olarak) kullandi.
* Simdi farkli bir deneme yapmak için optimizer_index_cost_adj parametresinin degerini “2000? olarak degistiriyorum. Bu noktada beklentim Oracle ‘in indeks kullanimindan uzaklasarak ayni sorgu için farkli bir execution plan seçmesi.
SQL> alter system set optimizer_index_cost_adj=2000 scope=memory;
* SQL sorgunun execution planini incelemek amaciyla yine trace islemini etkilestirip sorgumu çalistiriyorum.
SQL> select count(*) from test.test_tbl where isim=ahmet, uyelik_tarihi>sysdate-100
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1e5dda4d8748d58e
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2481613860
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 1519K (1)| 05:03:50 | | |
| 1 | SORT AGGREGATE | | 1 | 32 | | | | |
| 2 | CONCATENATION | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 3 | 96 | 506K (1)| 01:41:16 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | TEST_TBL | 3 | 96 | 506K (1)| 01:41:16 | KEY | KEY |
| 5 | PARTITION RANGE ITERATOR| | 90156 | 2817K| 506K (1)| 01:41:17 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | TEST_TBL | 90156 | 2817K| 506K (1)| 01:41:17 | KEY | KEY |
| 7 | PARTITION RANGE ITERATOR| | 8 | 256 | 506K (1)| 01:41:17 | KEY | KEY |
|* 8 | TABLE ACCESS FULL | TEST_TBL | 8 | 256 | 506K (1)| 01:41:17 | KEY | KEY |
-------------------------------------------------------------------------------------------------------
* Yukaridaki tablodan da anlasilacagi üzere çalistirdigimiz sorgu tabloda full tablescan ‘e girdi. Her ne kadar kullanmakta oldugum tablo partitioning yapilmis olsada sorgu indeks kullanmadigi için daha yüksek bir cost ‘a sahip. Yanliz bu cümleden her zaman indeks kullanmak performans kazandirir gibi bir düsünce olusmamali çünkü, indeks kullanim performansi mevcut tablonuza ve bu tabloya gönderdiginiz SQL sorgusunun içerige bagli olarak degisiklik gösterebilir.
Konuyu sonuca baglamak gerekirse, optimizer_index_cost_adj parametresi Oracle ‘in veritabani performansini dogrudan etkileyebilecek sistem parametrelerinden bir tanesi. Farkli uzmanlar bu parametre için farkli degerler kullanilmasini öneriyor. Örnek olarak ünlü Oracle uzmani Tom Kyte bu parametrenin AWR raporlari gözlenerek cache/hit oranina göre ayarlanmasi gerektigini belirtiyor. Digerleri OLTP sistemler için optimizer_index_cost_adj parametresinin “10-50? arasinda ayarlanmasinin performansa büyük etkisi olacagi görüsünü paylasiyor. Sonuç itibariyle veritabanin performansina dogrudan etki edecek bir parametre için bende öncelikle AWR raporlarini takip edilmesi konusunda hem fikirim.
boraovali.com
