Veritabanına gönderdiğimiz SQL sorgularına daha kısa sürede yanıt almak ve daha düşük donanım gücü tüketmesi açısından için çoğu zaman veritabanına yeni indeksler ekleriz. Sunucuya gönderilen sorgunun içeriğine göre oluşturduğumuz bu indekslerin kullanım sıklığına Oracle veritabanı cost based hesabı yaparak (execution plan) karar vermektedir. Bu konuda veritabanındaki mevcut indesklerin hangi sıklıkla kullanılacağına spesifik olarak “optimizer_index_cost_adj” isimli Oracle sistem parametresi karar verir.

Optimizer_index_cost_adj parametresini biraz açarsak, “1″ ile “10.000″ arasında bir değer atayarak (default değer 100) Oracle ‘ın indeks hassasiyetini arttırıp azaltabilirsiniz. Buna göre parametreye atanacak düşük bir değerde (10-50 arasında) Oracle sonucunu almak istediğiniz sorguya uygun olan bir indeks kullanmaya (force) çalışacaktır. Paremetreye verilecek olan değer arttırıldığı taktirde (misal 200-10.000 arasında) Oracle indeks kullanımından feragat ederek tablo üzerinde full tablescan ‘e yönelecektir.

Performance tuning amaçlı kullanılan optimizer_index_cost_adj parametresini kendi sisteminize uyarlarken dikkat edilmesi gereken nokta, parametrenin değeriyle oynamadan önce veritabanın güncel istatistiğinin alınmasıdır. Buna göre parametre değişikliğinden kaynaklı oluşabilecek yanlış execution plan kullanımı ve akabinde yaşanacak performans kaybının önüne geçilebilir.

Aşağıda örnek bir sorgu çalıştırarak konuyu açıklamaya çalışacağım.

* Öncelikle optimizer_index_cost_adj parametresinin değerini “10″ olarak değiştiriyorum.

SQL> alter system set optimizer_index_cost_adj=10 scope=memory;

* SQL sorgunun execution planını incelemek amacıyla trace ‘i etkileştirip sorgumu çalıştırıyorum.

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 ‘ı mevcut indeksleri daha fazla kullanması için düzenledikten sonra, yukarıdaki detaylı execution plan dökümünden anlaşılacağı üzere çalıştırdığımız sorgu tabloda doğru indeksi tespit edip (veritabanı istatistiğinin güncel olmasına bağlı olarak) kullandı.

* Şimdi farklı bir deneme yapmak için optimizer_index_cost_adj parametresinin değerini “2000″ olarak değiştiriyorum. Bu noktada beklentim Oracle ‘ın indeks kullanımından uzaklaşarak aynı sorgu için farklı bir execution plan seçmesi.

SQL> alter system set optimizer_index_cost_adj=2000 scope=memory;

* SQL sorgunun execution planını incelemek amacıyla yine trace işlemini etkileştirip sorgumu çalıştırıyorum.

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 |

-------------------------------------------------------------------------------------------------------

* Yukarıdaki tablodan da anlaşılacağı üzere çalıştırdığımız sorgu tabloda full tablescan ‘e girdi. Her ne kadar kullanmakta olduğum tablo partitioning yapılmış olsada sorgu indeks kullanmadığı için daha yüksek bir cost ‘a sahip. Yanlız bu cümleden her zaman indeks kullanmak performans kazandırır gibi bir düşünce oluşmamalı çünkü, indeks kullanım performansı mevcut tablonuza ve bu tabloya gönderdiğiniz SQL sorgusunun içeriğe bağlı olarak değişiklik gösterebilir.

Konuyu sonuca bağlamak gerekirse, optimizer_index_cost_adj parametresi Oracle ‘ın veritabanı performansını doğrudan etkileyebilecek sistem parametrelerinden bir tanesi. Farklı uzmanlar bu parametre için farklı değerler kullanılmasını öneriyor. Örnek olarak ünlü Oracle uzmanı Tom Kyte bu parametrenin AWR raporları gözlenerek cache/hit oranına göre ayarlanması gerektiğini belirtiyor. Diğerleri OLTP sistemler için optimizer_index_cost_adj parametresinin “10-50″ arasında ayarlanmasının performansa büyük etkisi olacağı görüşünü paylaşıyor. Sonuç itibariyle veritabanın performansına doğrudan etki edecek bir parametre için bende öncelikle AWR raporlarını takip edilmesi konusunda hem fikirim.

Alıntıdır --> http://www.boraovali.com/?p=363

 

Büyük tablolar için indeks yaratılması çoğu zaman dba ‘lar için problemdir çünkü tablodaki kayıt sayısı fazla olduğunda indeksin yaratılmasıda zaman alır. Bu süreçte veritabanı sunucusu indeksin yaratılmasıyla da meşgul olacağından veritabanının genel performansında bir düşüş meydana gelir. Buna göre indeks oluşturken veritabanının performansını düşüren 3 ana unsur bulunmaktadır. Bunlardan ilki yazının başında da belirtiğim gibi indeks yaratılacak tablodaki kayıt sayısıdır. Tablodaki kayıt sayısı arttıkça oluşturmak istediğiniz indeksin büyüklüğüde artar. İkinci unsur, indeksi oluştururken yapılan işlemin kaydının redolog ‘a yazılmasıdır. 10 milyon kayda sahip bir tabloya indeks oluşturmak istediğinizde, işlem süresince 10 milyon kez redolog dosyalarına yapılan işlemin logu da yazılır. Üçüncü dikkat etmemiz gereken unsur indeksi oluşturulurken bu işlem için kaç tane process ‘in meşgul olacağının belirtilmesidir. Sunucu donanımızın kapasitesine göre process sayısını arttırıp azaltabiliriz.

Yukarıda anlattıklarım haricinde daha hızlı indeks oluşturmak için kendinize session bazlı bir sort area size oluşturabilirsiniz. Böylece indeks oluşturlurken sort işlemi sırasında daha fazla memory alanıyla çalışacağınızdan indeksin oluşturulmasıda buna göre kısa zaman alacaktır. Şimdi aşağıda TEST kullanıcısına ait DENEME isimli tablo üzerinde DENEME_IDX isimli bir indeks oluşturalım.

Öncelikle sqlplus ‘a bağlanalım.

sqlplus / as sysdba

Session bazlı kendimize özel bir work area size oluşturalım.

SQL> alter session set workarea_size_policy=manual;

Daha sonra kendimize 40Mb ‘lık bir memory alanı tanımlayalım.

SQL> alter session set sort_area_size=41943040;

Manuel olarak work area size tanımladıktan sonra artık indeksi oluşturmaya hazırız. Bu noktada dikkat etmemiz gereken nokta indeksi oluşturken sistemin kesintiye uğramaması için ONLINE parametresini vermemiz gerektiği. Eğer komut içersinde ONLINE parametresini vermezsek, indeks oluşturlurken tablo kilitli kalacağından çalışan sisteminiz kesintiye uğrayabilir. (ONLINE parametresi sadece Oracle Database Enterprise Edition ile desteklenmektedir.)

SQL> create index test.deneme_idx on test.deneme(kolon1, kolon2) nologging, parallel, online;

İndeksimiz oluşturuldu. Komut içersinde “nologging” parametresini kullanarak yapılan işlemin redolog ‘da kaydının tutulmasını engelledik. “Parallel” parametresiyle index ‘in birden fazla process tarafından oluşturmasını sağladım, böylece yapılan işlemin süresi daha da kısaldı. Bu noktada veritabanından gerçekleştirilecek işlem için kaç tane paralel process açılması gerektiğine veritabanı parametrelerinden “parallel_threads_per_cpu” karar veriyor. Eğer bu parametre üzerinde herhangi bir değişiklik yapmadıysanız Oracle indeks oluşturulması için arka planda 2 adet thread çalıştıracaktır.