SQL Performance Analyzer ile SQL komutlarının performans gelişimlerinin izlenmesi

Oracle Database 11g Sürüm 1 (11gR1)  sürümü ile kullanıma sunulan SQL Performance Analyzer aracı, zayıf performans gösteren SQL komutlarının mevcut durumu ve gerekli düzeltme işlemi sonunda  performanslarında meydana gelen değişimleri test ortamında kıyaslayarak, kaynak kullanımında ve çalıştırma planı maliyetinde meydana gelen olumlu/olumsuz gelişimleri okunabilir rapor formatında hazırlayarak, veritabanı yöneticilerine SQL cümlelerinin iyileştirilmesinin veritabanı üzerinde olumlu etkisini kolayca görmesini sağlanır. SQL Performance Analzyer aracında kıyaslama için önceki ve sonraki olarak adlandırılan iki tür şablon kullanılmaktadır. “Önceki” kelimesinden kasıt;  herhangi bir iyileştirme yapılmadan çalıştırılan zayıf SQL komutlarının mevcut durumudur.  “Sonraki” ise; gerekli  yapısal iyileştirme yapıldıktan sonra bu konfigürasyon değişikliğinin sistem üzerinde ne tür performans geliştirmesi yapacağının testine imkan veren bir analiz ve simülasyon metodudur.

Bu makelede, zayıf SQL komutlarının“önceki” ve “sonraki” arasındaki performans değişimlerini  kıyaslama simülasyonu yer alacak ve performans değişim sonuçlarının zengin formatta raporlanması bir örnek ile yapılandırılacaktır. Oracle Enterprise Manager(OEM) grafiksel arayüzünde yer alan adım-adım sihirbazlar yardımıyla oluşturulacak olan SQL Performans Analyzer görevinde “önceki “ve “sonrası” arasındaki değişikliklerinin kıyaslama simülasyonu kolayca yapılmaktadır. Bu işlem için izlenecek adımlar aşağıda sırasıyla  yer almaktadır.

• Oracle veritabanındaki örnek yükü kapsayacak olan zayıf performansa sahip SQL komutlarının yakalanması(SQL Tuning setler yardımıyla).
• Mevcut veritabanı sistemini kullanarak “önceki” olarak adlandırılan imajı/şablonu oluşturmak için örnek işyükünün mevcut performans etkisinin belirlenmesi.
• Oracle veritabanı sisteminde yapılan yapısal değişiklik sonucunda mevcut işyükünün “sonraki” durumuna karşılık değişim göstermiş performansının test edilmesi.
• Yapılan yapısal değişim sonucu hangi işyükü komponentlerinde pozitif veya negatif yönde değişim meydana geldiğinin bulunması için “önceki” ve “sonraki” arasında kıyaslamanın yapılması ,ayrıca hangi işyükü komponentleirnde  değişiklik olmadığının tespiti.
• Zayıf performans gösteren SQL komponentlerinin nasıl en iyi şekilde düzeltileceğinin belirlenmesi, böylece yeni ortamda bu SQL komutlarının en iyi şekilde çalışacağından emin olunması.

SQL Performance Analyzer aracının çalışmasını göstermek için,  bu yazıda kullanılacak olan örnek tabloların oluşturulması ve SQL komutlarının beliritildiği adımları, “önceki” ve “sonraki” değişim simülasyonları ile birlikte aşağıdaki gibi yapılandıracağım.

Uptime izleme aracı ile inaktif oturumların izlenmesi

Uptime izleme aracı ile Oracle veritabanında inaktif oturumlar hakkında uyarıları emaille almak oldukça kolaydır. Bu işlem için uptime izleme aracı içinden “Add service monitor” ile Oracle (basic checks) servisini seçmek yeterlidir. Ardından script altından inaktif oturumlar mevcutsa TRUE değerini döndürecek olan SQL komutunu tanımlanabilir. Servis kıyaslama alanında da değer olarak FALSE tanıtılırsa, ters bir durum oluşma durumunda uptime izleme aracı otomatik email ile bu durumu kritik alert seviyesinde ilgili profile bildirecektir.

 Alttaki örnekte 2 saatten fazla zamandır veritabanına bağlı inaktif oturum olduğunda anında emaille alert olarak döndürecek örnek yer almaktadır.

Uptime ile ilgili geniş bilgiye http://www.uptimesoftware.com/resources.php adresinden erişilebilir.

SQL Profiller nelerdir ve neden ihtiyaç duyulur?

DBMS_XPLAN paketi ile SQL komutlarının çalıştırma planının analiz edilmesi istenildiğinde,  bazı durumlarda çalıştırma planı boyunca aşağıdaki satır görülebilmektedir.

Note

-----------

SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

Bunun anlamı şudur; 

·         SQL komutunun değerlendirmesi esnasında harici bir yardımcı nesne kullanılmıştır.

·         Bu yardımcı nesne çalıştırma planını değiştirmiştir veya en azından etkilemiştir.

Bu noktada bir takım yardımlar almak faydalı olacaktır. Mesela, bu profil nesne nedir? Ne iş yapar? Ve en önemliside SQL komutlarını değerlendirirken neden “harici” bir yardıma ihtiyaç duyulmaktadır?

Oracle optimizer ile ilgili problem aslında, geleneksel olarak Oracle optimizer’ın çalıştırma planları oluşturmak için veri istatistiklerine güvenmesinden kaynaklanmaktadır.  İstatistikler aslında gerçek verinin oldukça basitleştirilmiş tanımı şeklinde yer almaktadır. Oldukça büyük GB boyutundaki bir tablonun kolon istatistikleri, sadece belirli şeyleri içermektedir, değerlerin toplamı, benzersiz(distinct) değerlerin miktarı, minimum ve maksimum değerler gibi... Diğer bir deyişle, istatistikler verinin genel şeklini yakalar, ancak pekçok alt seviye detay bilgisi kaybolmaktadır.   

Bu kayıp detay bilgisini telafi etmek ve hala mantıklı ve doğru tahminler sağlamak için, optimizer veri hakkında bazı varsayımları bulundurmaktadır. Bilhassa optimizer bu durumlarda şu şekilde düşünmektedir: 

·         Veri değerleri uniform olarak dağılmıştır(diğer bir deyişle 2 nolu değer sıklıkla 5 nolu değerdir)

·         Kayıtlar uniform olarak dağılmıştır(diğer bir deyişle fiziksel bir kümeleme veya veri sıralaması yoktur).

·         Değerlerin sıralaması aralıksızdır(diğer bir deyişle aralık arasında bir boşluk yoktur)

EXACT_MATCHING_SIGNATURE ve FORCE_MATCHING_SIGNATURE hakkında

Oracle dökümanlarında EXACT_MATCHING_SIGNATURE normalize SQL textinde hesaplanan imzadır şeklinde tanımlanmıştır. Buradaki normalizasyon kavramı beyaz alanların çıkarılması ve gerçek olmayan tüm stringlerin büyük harfe dönüştürülmesidir. CURSOR_SHARING parametresi EXACT olarak ayarlandığında bu imza kullanılırken, FORCE_MATCHING_SIGNATURE kolon değeri ise CURSOR_SHARING parametresi FORCE olarak ayarlandığında Oracle’ın hesapladığı imzadır.  

Şimdi bu kolonların alacağı bazı değerleri ve bunların anlamlarını aşağıda inceleyelim.  

I. SENARYO:

EXACT_MATCHING_SIGNATURE  ve FORCE_MATCHING_SIGNATURE değerleri sıfır olarak ayarlanmıştır. Bu senaryo eğer komut tipi PL/SQL paketi, INSERT,ALTER INDEX,LOCK, SET ROLE ve mevcut SYS tablolarından($,# olanlar, ancak görünümler hariç) SELECT/UPDATE işlemleri  veya dahili Oracle komutları içindir.  Bunun yanında daha fazla komut olabilir, ancak Oracle SQL in yeniden kullanılamayacağını düşünecek ve ardından değerleri 0 olarak işaretleyecektir.

Oracle 11.2 sürümünde DBMS_PARALLEL_EXECUTE paketi

Oracle 11.2 sürümü ile yeni gelen DBMS_PARALLEL_EXECUTE prosedürü, büyük veri setlerini artalan şekilde güncellemeye izin veren bir pakettir. Temel olarak, ROWID veya nümerik kolon veya kullanıcı tabanlı SQL komutuna dayalı satırlara ayırarak daha küçük veri yığınları oluşturulur, ardından bu yığınlar paralel şekilde güncellenir.  Bu paketin avantajları ise; bu yığınlar bireysel olarak commit edilmektedir ve ROWID’e dayalı yığınlar olduğunda daha iyi performansa ulaşılmaktadır.   

DBMS_PARALLEL_EXECUTE neden önemlidir?

1.    Büyük bir tekil işlem birçok işlem yığınına bölünebilmektedir.

2.    Golden Date veya Streams gibi bağlı sistemlerde daha az etki yapmaktadır.

3.    Yığınlar ROWID ve bloklara bağlı olduğunda paralel DML işlemleri ile kıyaslandığında daha iyi performans göstermektedir.

4.    Daha az undo alanına ihtiyaç duyar ve bu sebeple ORA-01555 hatası ile karşılaşma ihtimali daha düşük olmaktadır.

5.    Hata ile karşılaşıldığında rollback işlemine etkisi şiddetli olmamaktadır.

6.    Kilit süresi daha düşüktür.

7.    Hatalı yığın işlemlerini yeniden başlatabilmektedir.

8.    Diğer paralel özelliklerin aksine, DBMS_PARALLEL_EXECUTE  paketi  Enterprise Edition lisansına şimdilik ihtiyaç duymamaktadır.

9.    Manuel kodlama gereksinimini azaltmakta ve paralel işlemler üzerinden daha iyi tekdüze dağıtım yapabilmektedir.

Oracle 11.2.0.2 sürümünde Flashback Transaction Query

Flashback Transaction Query, işlem seviyesinde veritabanında yapılan değişiklikleri göstermeyi sağlayan kullanışlı bir Oracle aracıdır. Flashback Transaction Query, bir işlem tarafından yapılan tüm değişikliklerin görülmesini sağlayan bir SQL eklentisidir. Örneğin;

SELECT UNDO_SQL

FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = '000200030000002D';

Yukardaki komut bu işlemde sonuçlanan tüm değişiklikleri göstermektedir. İlaveten,  yerini dolduran tüm SQL komutlarıda döner ve bu işlem tarafından tüm satırlara yapılan değişiklikler geri alınabilir.  Flashback Transaction Query gibi hassas bir araç kullanarak, veritabanı yöneticileri ve uygulama geliştiriciler veritabanı veya uygulamadaki mantıksal problemleri kesin olarak teşhis edip gerekli düzeltmeleri yapabilirler.  

Birleşmelerde sorgu geliştiricinin uygun yürütme planını seçmesi

Sorgu geliştirici, bir yürütme planının seçilmesi esnasında aşağıdakileri değerlendirmeye almaktadır:

·         Sorgu geliştirici, önce en azından bir veya birden fazla tablo birleşmesinin kesinlikle en az bir satır içeren bir satır kaynağı olarak sonuçlanıp sonuçlanmayacağını belirler. Sorgu geliştirici, bu gibi durumları tablodaki UNIQUE ve PRIMARY KEY kısıtlamalarına bağlı olarak  tanımaktadır. Eğer böyle bir durum mevcutsa, o zaman sorgu geliştirici bu tablolaları ilk olarak birleşme sırasına göre yerleştirir. Ardından, geri kalan tablo kümelerinin birleşmesi işlemine gelinir.

·         Dış(outer) birleşme durumları ile birleşme komutları için, dış birleşme operatörü olan tablo, birleşme sırasında bu durum için diğer tablodan daha sonra gelmelidir. Sorgu geliştirici, bu kuralı ihlal eden birleşme sıralarını değerlendirmeye almaz. Benzer olarak,  bir altsorgu yarı birleşme ve anti birleşmelere dönüştürüldüğünde, bu alt sorgunun tabloları dış sorgu bloğunda bağlı olunan veya ilişkideki tablolardan daha  sonra gelmelidir. Ancak, bazı durumlarda karma(hash) anti birleşmelerin ve yarı birleşmelerin(semijoins) bu sıralama durumunu geçersiz kılması mümkün olmaktadır.

Sorgu geliştirici ile muhtemel birleşme sırasına, birleşme metoduna ve uygun erişim yollarına bağlı olarak  bir yürütme planı kümesi oluşturulur. Ardından, herbir planın maliyeti değerlendirilir ve en düşük maliyete sahip olan seçilir. Sorgu geliştirici, bu maliyetleri aşağıdaki şekillerde değerlendirmeye almaktadır:

“Oracle Database Smart Flash Cache” özelliğinin yapılandırılması

AWR raporunun daha fazla buffer cache ihtiyacı olduğuna işaret ettiğini düşünelim. Paylaşımlı havuzun doğru şekilde ayarlandığından eminsiniz. Buffer cache miktarını, paylaşımlı havuzdaki bellek tahsisini azaltarak, daha yüksek bir minimum seviyeye çıkaramıyorsunuz ve ilave olarak Oracle’a tahsis edeceğiniz daha fazla fiziksel bellek imkanıda bulunmuyor.

İşte bu sıkıntılı durumda, işletim sistemine bağlı olarak, Oracle 11.2 sürümü ile yeni gelen “Oracle Database Smart Flash Cache”  özelliği ile, buffer cache için daha fazla miktarda bellek gerektiğini işaret eden durumlarda, harici olarak ekstra bellek genişletmesi yapılabilir. Bu özellik şu an sadece Solaris ve Oracle Linux işletim sistemleri ile sınırlıdır.  

Flash cache özelliğini devreye almak için aşağıdaki parametrelerin ayarlanması gerekmektedir:

Oracle 11.2 ile askıda tutulan istatistikler

Oracle 11g Sürüm 2 (11.2) itibariyle istatistik toparlarken aşağıdaki yeni özellikler işleme girmiştir.

·         Toparlama işlemi sonunda istatistiklerin otomatik olarak yayınlanması(varsayılan işlem)

·         Yeni istatistikleri askıda tutacak şekilde saklamak

Yeni istatistiklerin askıda tutulacak şekilde saklanması istatistik onaylanması işlemine izin verir ve  sadece tatminkar olunduğu durumlarda bunlar yayınlanır. İstatistiklerin toparlanır toparlanmaz, otomatik olarak yayınlanıp yayınlanmayacağını kontrol etmek için aşağıdaki gibi DBMS_STATS paketi kullanılır:

SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;

Yukardaki sorgu TRUE veya FALSE değerini döndürür. TRUE değeri, istatistiklerin toparlandığında yayınlanacağını gösterirken, FALSE değeri istatistiklerin askıda kalacak şekilde tutulacağını göstermektedir.

PUBLISH ayarı şema veya tablo seviyesinde değiştirilebilir. Örneğin, HR şemasında employees tablosunun PUBLISH ayarını FALSE olarak değiştirmek için aşağıdaki sorgu çalıştırılabilir:

Exec dbms_stats.set_table_prefs('HR', 'EMPLOYEES', 'PUBLISH', 'false');

Oracle 11g de PGA ve SGA tavsiyecileri arasında bellek dağıtımı hesaplamaları ve AMM

Oracle 11g de PGA ve buffer cache tavsiyecileri arasındaki bellek dağıtım prosedürü daha az karmaşıktır. Ortalama direkt IO zamanını hesaplamak veya PGA tavsiyecisi verisini bytes değerinden tamamlanma süresine çevirmek gerekli değildir, çünkü V$PGA_TARGET_ADVICE view görünümü zaten tamamlanma süresi beklentilerini dahil etmektedir. Bu sebeple Oracle 10g den daha az karışık olan aşağıdaki sorgu kullanılarak her iki tavsiyeci direkt olarak kıyaslanabilmektedir:

Açık ve önbelleklenmiş imleçlerin izlenmesi

Açık imleçler(cursors) library cache içinde yer tutmaktadırlar. Pekçok oturumunun, library cache içini tepeleme doldurmasının önüne geçmek için veya CPU’nun milyonlarca çözümleme(parse) çağrıları ile tıkanmasını önlemek için, OPEN_CURSORS parametresi ayarlanır.

OPEN_CURSORS parametresi, her bir oturumun oturum başına açabileceği maksimum imleç sayısını belirtir. Mesela, eğer OPEN_CURSORS parametresi değeri 1,000 olarak ayarlanırsa, bu durumda her bir oturum bir seferde en çok 1,000 imleci açabilir. Eğer tek bir oturum OPEN_CURSORS parametresince belirlenen değeri aşarsa, bu açacağı imleçler esnasında ORA-01000 hata mesajını alacaktır.

OPEN_CURSORS parametresinin varsayılan değeri 50’dir, ancak  pek çok uygulama için bu sayının en azından 500, hatta pek çok yeni nesil web uygulamalarında yüzlerce kullanıcı oturumunun paylaşımlı havuzları sık şekilde kullanmasından dolayı bu sayının 1,500 olması tavsiye edilir.

Eğer SESSION_CACHED_CURSORS parametresi ayarlanmazsa, değeri 0 olarak ayarlanır ve oturum için hiç bir imleç önbelleklenmez. Tabii, imleçler paylaşımlı havuzda öncelleklenecek, ancak bunları bulmanız gerekecektir ve bu durum en iyimser yaklaşımla bolca yumuşak çözümleme(soft parse) olayına sebep verecektir. Eğer bu parametre ayarlanırsa Oracle, library cache içine bakacak ve bu SQL komutu için 3 seferden fazla çözümleme(parse) çağrısı yapılıp yapılmadığına bakacaktır. Eğer yapılmışsa, Oracle bu komut ile ilişkili oturum imlecini oturum imleç önbelleği içine taşıyacaktır.  Bu oturum tarafından ilgili SQL komutu için yapılan daha sonraki çözümleme çağrıları oturum imleç önbelleğinden doldurulacak, böylece yumuşak çözümleme(soft parse) olayından bile kaçınılmış olunacaktır. Bu noktada çözümleme işleminden kaçınmak mümkün değildir ve “daha yumuşak” çözümleme olayları, daha az CPU tüketimi ve çok daha hızlı çözümleme anlamına gelecektir.

10g sürümden 11g sürüme yükseltmede SQL çalıştırma planlarında kesintisiz performansın sağlanması

Pek çok DBA in ve PL/SQL geliştiricininde bildiği üzere, veritabanında bir parametre değişikliği tüm veritabanı çapında etkin olmaktadır. Bu durum,  SQL komutlarında kullanılan bir “hint” in, outline nın veya obje güncellemesini kapsayacak istatistikleri etkileyen SQL profilinin tam tersidir.

DBA’in veritabanını 10g sürümlerden 11g sürüme yükseltmesinde beklentilerinden birisi, yükseltme sonundaki platformda 10g deki çalıştırma plan varsayılan ayarlarının muhafaza edilmesidir. Ancak bu, 11g ortamında masum bir beklenti olarak kalacaktır!

Değişikliklerden geri dönülmesi  ve/veya daha önceki sürümlerdeki herhangi bir değişikliğin kaldırılması performansı oldukça etkileyebilecektir. Bu yüzden 10g performans baseline’larını  11g de de aynen alıkoyma fırsatı mümkün olursa, bu durum yükseltme sonunda çalıştırma planı maliyeti ile ilişkili karşılaşılacak sorunlar noktasında en önemli kurtarıcı olacaktır. Bu noktada “10g planları 11g planlarından dahamı iyidir” sorusu kafa karıştırıcı olacaktır. Elbette böyle bir iddia %100 kanıtlanır olmasada, 11g deki pek çok “gizli” parametrenin 10g deki değerlerinden oldukça farklı olması sebebiyle SQL çalıştırma planı performanslarında gerçekten dramatik düşüşler olabilecektedir. Aslında 10g öncesi 9i platformundan yükseltmelerde bu tür sıkıntılar mevcuttu, ancak 11g deki performans kaybı daha önceki sürüm yükseltmelerinden daha dramatik olmaktadır. Bu tür 11g sürümüne yükseltme sonrası çalıştırma planı performans problemlerinin detaylı analizlerine ve teknik incelemelerine, Coşkan Gündoğar'ın aşağıdaki yazı dizilerinden erişebilirsiniz, bu dizilerde oldukça faydalı analizler sebepleriyle beraber yer almaktadır.

·         http://coskan.wordpress.com/2011/03/04/plan-stability-through-upgrade-to-11g-why-is-my-plan-changed-extra-nested-loop/

·         http://coskan.wordpress.com/2011/02/17/plan-stability-through-upgrade-why-is-my-plan-changed-new-optimizer-parameters/

İşte bu tür parametrelerin varsayılan ayarlarındaki bariz değişiklikler yüzünden meydana gelebilecek sıkıntılı durumlarda, DBA lere hızlıca performans iyileştirme imkanı vermek ve aslında 10g deki SQL profillerinde uzun çabalar sonunda erişilen gelişmiş çalıştırma planı performansının 11g’de de aynen devam etmesini sağlamanın en iyi metotlardan birisi; 10g çalıştırma planlarının baseline koleksiyonunu dışarı almak ve sistem 11g sürümüne yükseltildiğinde bu koleksiyonun 11g SQL Plan Baseline içerisine yüklenmesi olmaktadır.

Paralel sorgu proseslerinin SQL_TRACE metodu ile izlenmesi

Bir veya daha fazla sayıda belirli paralel sorgu prosesinin SQL_TRACE ile izlemesini yapabilmek için, öncelikle aşağıdaki gibi izlenmesi gerekecek paralel sorgu proseslerinin tespit edilmesi gerekmektedir.

SQL> select inst_id,p.server_name,

p.status as p_status,

p.pid as p_pid,

p.sid as p_sid

from gv$px_process p

order by p.server_name;

Yukardaki sorgu sonucu p002 ve p003 adlı proseslerin izlenmesine karar verildiğini farzederek, aşağıdaki komutu kullanarak sadece bu iki proses için SQL_TRACE izlemesi etkinleştirilebilir.

SQL> alter system set events ‘sql_trace {process: pname = p002 | p003}’;

İzleme işlemi tamamlandığında ise aşağıdaki gibi devredışı bırakılabilmektedir.

SQL> alter system set events ‘sql_trace {process: pname = p002 | p003} off’;

** “Oracle 11g Performance Tuning Recipes- A problem solution approach” adlı kitaptan bir bölüm **

Oracle 11g çoklu kolon CBO istatistikleri

Oracle 11g, tekil tablo sorgularının “where” kısmında çoklu kolonlar belirtildiği durumlarda, kolon grupları istatistikleri kullanarak maliyet tabanlı geliştirici(cost based optimizer-CBO) nin daha iyi karar almasına yardımcı olacak geliştirilmiş istatistiklerin işleme alınmasına imkan vermektedir. Standart istatistiklere ilave olarak, kolon grubu veri dağıtımında çarpıklık yer aldığında gelişmiş maliyet tahminleri için bu kolon grupları üzerinde histogramlarda oluşturulabilir.

CBO’nun bir sorgu için kendi çalıştırma kararlarını sırayla alması için, muhtemel erişim yolları için maliyet tahminlerini hesaplamak için uygun istatistikleri kullanması ile olur. Eğer kolon histogramları mevcutsa, doğrulanmış değerlerin seçiciliğini hesaplamak için optimizer bu histogramları kullanmaktadır. Bu seçicilik, maliyetlerin tahmin edilmesinde ve sonuçta en iyi erişim yolunun tercih edilmesinde önemli bir etkendir. Oracle 11g öncesinde istatistikler bireysel kolonlardan oluşturulabilmekteydi. Tek bir tablo üzerinde birçok kolon arasındaki ilişkiyi gerçekleştirmek için CBO maliyet hesaplamalarında yetersizlik başlıca bir sınırlama olmaktaydı.

Segment Tavsiyecisi raporlarının otomatik olarak e-mail ile gönderilmesi

Segment Tavsiyecisinin otomatik olarak öneri raporları hazırlaması ve bu çıktıları email ile otomatik olarak almak için bir shell script içine Segment Tavsiyecisi çıktısını gösterecek SQL eklenebilir. Aşağıda bununla ilgili bir shell script örneği yer almaktadır.

#!/bin/bash

if [ $# -ne 1 ]; then

echo "Usage: $0 SID"

exit 1

fi

# source oracle OS variables

. /var/opt/oracle/oraset $1

#

BOX=`uname -a | awk '{print$2}'`

#

sqlplus -s <<EOF

dba1/dbapasswd

spo $HOME/bin/log/seg.txt

set lines 80

set pages 100

SELECT

'Segment Advice --------------------------'|| chr(10) ||

'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||

'SEGMENT_OWNER : ' || segment_owner || chr(10) ||

'SEGMENT_NAME : ' || segment_name || chr(10) ||

'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||

'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||

'RECOMMENDATIONS : ' || recommendations || chr(10) ||

'SOLUTION 1 : ' || c1 || chr(10) ||

'SOLUTION 2 : ' || c2 || chr(10) ||

'SOLUTION 3 : ' || c3 Advice

FROM

TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

EOF

cat $HOME/bin/log/seg.txt | mailx -s "Seg. rpt. on DB: $1 $BOX" uinal@hotmail.com

exit 0

Yukardaki shell script, aşağıdaki gibi “cron” aracı ile rütin aralıklarla çalışacak şekilde zamanlanmış görev olarak atanabilir, böylece performans problemine sebebiyet vermeden proaktif sorun çözüm önlemi alınmış olacaktır.

# Segment Advisor report

16 11 * * * /orahome/oracle/bin/seg.bsh DWREP

Oracle ASM Cluster Dosya Sistemi (ACFS)

ASM Cluster Dosya Sistemi ilk olarak Oracle 11.2.0.1 sürümü ile Linux sistemler üzerinde desteklenecek şekilde hizmete girmiş, Oracle 11.2.0.2 sürümü ile Solaris, AIX,HP-UX ve Windows platformlarınada bu destek hizmete girmektedir. Oracle 11.2.0.2 sürümü ile ACFS yapısına replikasyon, etiketleme, şifreleme ve realm tabanlı güvenlik özellikleride ilave edilmiştir.

ACFS sadece ASM volumleri desteklemektedir, ancak ASMLib sürücüleri ile kullanılamaz ve fdisk gibi formatlama araçları ile bu ACFS volumleri formatlanmamalıdır. ACFS, Oracle external binary veri dosyaları (LOB dosyalar ve BFile gibi), external dizinler, Oracle veritabanı home dizin dosyaları, izleme dosyaları gibi çeşitli paylaşılabilir dosyalara senkronize erişimi desteklemektedir. ACFS üzerinde Oracle’nın desteklemediği dosyalar, ASM diskgrupları bünyesinde tutulan kontrol dosyaları, SPFILE dosyaları, redo log dosyaları, arşiv log dosyaları,clusterware dosyaları(OCR ve voting dosyaları) ve veri dosyalarıdır.

Oracle 11.2 Grid mimarisinde RAC düğümlere ilave SCAN listener ekleme

Geçenlerde test ortamda yapılandırdığım Oracle11gR2 grid yapılandırması aşağıdaki loglarda görüldüğü gibi sadece tek bir SCAN listener ile tamamlandı. Oracle 11.2 RAC yapılandırması iki düğümden(rac01 ve rac02) oluşan bir test platformunda yapıldı. Bu yazıda Oracle 11.2 Grid ortamında RAC düğümlere nasıl ilave iki SCAN listener daha eklenecek, onu inceleyeceğiz.

[grid@rac01 grid]$ srvctl config scan
SCAN name: rac-scan, Network: 1/192.168.38.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.bugra.com/192.168.67.192

[grid@rac01 grid]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

[grid@rac01 grid]$ ping rac-scan.bugra.com
PING rac-scan.bugra.com(192.168.67.193) 56(84) bytes of data.
From rac01.test.ant(192.168.38.50) icmp_seq=2 Destination Host Unreachable
From rac01.test.ant(192.168.38.50) icmp_seq=3 Destination Host Unreachable
From rac01.test.ant(192.168.38.50) icmp_seq=4 Destination Host Unreachable

— rac-scan.bugra.com ping istatistikleri —

6 packets transmitted, 0 received, +3 errors, 100% packet loss, time 5000ms
, pipe 3

Büyük partition tablolarda istatistiklerin toplanması

Genellikle büyük partition tablolarda optimizer istatistiklerinin toplanması ve sürdürülmesi ile ilgili pek çok yeni DBA’nin kafasında soru işaretleri vardır. Bu soruları aslında iki konu etrafında toplayabiliriz:

1. Sorgular, eskimiş veya varolmayan partition seviyesi istatistiklerine erişirken menzil dışı değerler sebebiyle bir alt optimal planı alabilir.
2. Global istatistik toplaması zaman ve sistem kaynakları açısından aşırı derecede pahalıdır.

Bu yazıda bu iki sorunu tanımlayıp Oracle 10g ve Oracle 11g sürümlerinde bu iki sorunun temelini inceleyeceğiz.

Menzil dışı (out of range) değerler
Büyük tablolar sıklıkla, sorgu performansını artırmak ve veri yönetimini kolaylaştırmak için “partition” olarak adlandırılan daha küçük bölümlere ayrıştırılır. Oracle sorgusu, SQL sorgusu için iyi bir çalıştırma planı seçmek için hem bütün tablonun istatistiklerini(global istatistikler), hemde bireysel partitionların istatistiklerini(partition istatistikleri) değerlendirmeye almaktadır. Eğer sorgu sadece tekil bir partitiona erişmek ihtiyacı duyarsa, optimizer sadece bu erişilen partition’un istatistiklerini kullanmaktadır. Eğer sorgu birden fazla partitiona erişirse, global ve partition istatistiklerinin kombinasyonunu kullanılmaktadır. Partition kolonların minimum ve maksimum değerlerini şu şekilde düzeltir; İlk partition kolonun maksimum değeri olarak yüksek sınır bölümlendirmesini kullanır ve “range partitioned” tablolar için ilk bölümlendirilmiş kolonun minimum değeri olarak bir önceki partitionun yüksek sınır bölümlendirme değerini kullanır. İsteğe bağlı olarak, hedefin blok sayısı, satır sayısı gibi diğer bazı istatistikleride devredışı bırakır.

Farzedinki STOK adlı tablonun STOK_GIRIS kolonundan dörtte bir oranında “range partition” şeklinde bölümlendirilmiş bir tablo mevcut. Her günün sonunda veri en son partition içine yüklenmekte, ancak istatistikler sadece partitionun tamamen dolduğu yılın dörtte birlerinde toplanmakta. Global ve partition seviyeli istatistiklerin(sadece tamamen dolu partitionlar için) güncel olduğunu varsayarak, alt-optimal planının “menzil dışı” olmasını önlemek için aşağıdaki adımlar izlenebilir.

TEMP tablespace büyüme sorunu

TEMP tablespace büyüme sorunu pek çok DBA tarafından karşılaşılan ve sıkıntı yaratan bir sorundur. Temporary(geçici) tablespace aşırı büyüdüğünde veritabanında geçici tablespace alanı tükenebilmektedir. Bu yazıda, bu gibi durumlarda geçici tablespace alanını arzu edilen boyuta kavuşturulacağı ile ilgili bir takım ipuçları yer alacaktır.

Geçici tablespace hakkında bilgi almak:

Aşağıdaki komutu kullanarak geçici tablespace hakkında bilgi alınabilir.

SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_TEMP_FILES;

TABLESPACE_NAME   FILE_NAME

---------------   -----------
TEMP              /u01/oracle/oradata/arda/TEMP01.DBF

Tempfile dosyasının yeniden boyutlandırılması:

Pek çok durumda ALTER DATABASE TEMPFILE komutu ile geçici tablespace’in alanını artırmak metodu seçilebilmektedir.

Ancak, geçici tablespace dosyalarının(tempfile) yeniden boyutlandırılması sıklıkla ORA-03297 hata mesajının alınması ile sonuçlanmaktadır. Bu gibi durumda TEMP veri dosyasının oto genişleyebilir(auto-extensible) olması gerekmekte ve maxbytes/maxblocks değerinin çok düşük olmadığından emin olunması gerekmektedir. Benzer olarak TEMP tablespace’in büyük yığınlarını tahsis etmekten sorumlu olan SQL sorgularının belirlenmesi ve iyileştirilmeside gerekmektedir.

ALTER DATABASE TEMPFILE ‘/u01/oracle/oradata/arda/TEMP01.DBF' RESIZE RESIZE 250M
*
ERROR AT LINE 1:
ORA-03297: FILE CONTAINS USED DATA BEYOND REQUESTED RESIZE VALUE

Eğer ALTER DATABASE TEMPFILE komutu sonunda ORA-032297 hata mesajı ile karşılaşılırsa aşağıdaki adımları izleyerek temp dosyasının boyutu sorunsuzca arzu edilen boyuta kavuşturulur.

Oracle 11g aktif standby veritabanında otomatik blok bozulması tamiri işlemi

Oracle 11g Active Data Guard özelliğinde gerçek zamanlı sorgu yeteneğinin yanında, Otomatik Blok Medya Düzeltme(Automatic Block Media Repair) özelliği ile daha yüksek erişilebilirlik yeteneği olduğu gözükmektedir. Primary veritabanındaki veri blok bozulmalarını standby siteden bu bozulan blokları elde etmek suretiyle ABMR arkaplan prosesi tarafından tamir edilebilmektedir.

Aynı fonksiyon, aktif standby sitedeki blok bozulmalarını primary siteden aynı şekilde bozulmamış blokların elde edilmesiyle tamir edilmesi yoluylada  kullanılabilmektedir. Aşağıdaki senaryoda bu durumu test edeceğiz.

Bu test senaryosunda kullanılmak üzere bir tablo oluşturup USERS tablespace içine atayacağız.

SQL> create table testtbl
tablespace users
as select * from hr.emp;

Table created.

DBMS_ROWID fonksiyonunu kullanarak bu tablonun kullandığı blokları belirliyoruz.

SQL> select * from
(select distinct dbms_rowid.rowid_block_number(rowid)
from testtbl)
where rownum < 6;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
12
13
14
15
16

Ardından bu bloklardan herhangi birisini bozuyoruz (senaryomda 13 numaralı bloğu bozuyorum, bu testi kesinlikle gerçek üretim veritabanınızda yapmayın!)