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.

1.)    Simülasyon hazırlığı: Bu yazı için kullanacağım tabloları Oracle veritabanındaki örnek şemalardan biri olan OE şemasındaki objelerden türeteceğim. Bunun yanında bu tabloların güncel istatistiklerinin alınmasıda bu adımda yer alacaktır

CONN OE/OE

CREATE TABLE CUSTOMERSD AS SELECT * FROM CUSTOMERS;

CREATE TABLE ORDERSD AS SELECT * FROM ORDERS;

CREATE TABLE ORDER_ITEMSD AS SELECT * FROM ORDER_ITEMS;

CREATE TABLE PRODUCT_INFORMATIOND  AS SELECT * FROM PRODUCT_INFORMATION;

--- İstatistikleri topluyoruz.---

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’CUSTOMERSD’,cascade=> true);

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’ORDERSD’,cascade=> true);

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’ORDER_ITEMSD’,cascade=> true);

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’PRODUCT_INFORMATIOND’,cascade=> true);

2.)    SQL komutlarının toplanmasına hazırlık: Ardından, yukarda oluşturduğum tablolara erişim sağlamak üzere Oracle veritabanınında aşağıdaki iki adet SQL komutunu çalıştırıyorum.

select a.customer_id,a.cust_first_name||' '||a.cust_last_name Customer, b.order_id, b.order_total, d.product_name, c.unit_price, c.quantity,d.supplier_id

from customersd a, ordersd b,order_itemsd c, product_informationd d

where a.customer_id=b.customer_id

and b.order_id=c.order_id

and c.product_id=d.product_id

and a.customer_id=144;

select a.customer_id,b.order_date,c.order_id,d.product_name,d.list_price

from customersd a,ordersd b,order_itemsd c,product_informationd d

where a.customer_id=b.customer_id

and b.order_id=c.order_id

and d.product_id=c.product_id

and c.order_id in

(select order_id

from ordersd

where order_status=3);

3.)    “Önceki” olarak adlandırılan şablonun  hazırlanması:  “Önceki”  olarak adlandıracağım şablonun test için hazır olmasından itibaren yukardaki SQL komutlarını içerecek olan SQL Tuning Task hazırlanması işlemine geçeceğim.  Ardından, bu oluşturulan SQL Tuning Set,  SQL Performance Analyzer görevi içerisine eklenecek ve performans kıyaslaması için “sonraki” olarak adlandırılacak şablona karşı kullanılmak üzere SQL Performance Analyzer görevi  içerisinden “önceki” larak adlandırılan adımda çalıştırılacaktır.

4.)    Veritabanında yapısal değişiklik yapılması: “Sonraki” olarak adlandırdığım şablonu hazırlamadan önce veritabanında mevcut SQL komutlarının çalıştırma planını ve kaynak kullanım istatistiklerini optimize edebilecek olan iyileştirme işlemlerine geçeceğim. Bu amaçla bu noktada;  üç adet indeks oluşturacağım. Bu indekslerden birisi CUSTOMERSD tablosundaki CUSTOMER_ID kolonunu indeksleyecek, ikincisi ORDER_ITEMSD tablosundaki ORDER_ID  ve ORDER_STATUS kolonlarını birlikte indeksleyecektir, üçüncüsü ise PRODUCT_INFORMATIOND tablosundaki PRODUCT_ID kolonunu indeksleyecektir.  Daha sonra, doğru bir çalıştırma planı oluşturabilmek için bu indeksleme yapılan tabloların istatistikleri yeniden toplanacaktır.

CREATE INDEX CUSTID_IDX ON CUSTOMERSD(CUSTOMER_ID)PCTFREE 30;

CREATE INDEX ORDITMS_IDX ON ORDER_ITEMSD(ORDER_ID,ORDER_STATUS)PCTFREE 20;

CREATE INDEX PRODID_IDX ON PRODUCT_INFORMATION(PRODUCT_ID)PCTFREE 25;

--- İstatistikleri topluyoruz.---

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’CUSTOMERSD’,cascade=> true);

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’ORDERSD’,cascade=> true);

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’ORDER_ITEMSD’,cascade=> true);

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘OE’,’PRODUCT_INFORMATIOND’,cascade=> true);

5.)    “Sonraki” olarak adlandırılan performans şablonunun oluşturulması: Değişikliklerin orjinal özdeş işyükünde sonuçlarını belirlemek için, SQL Performance Analyzer içerisinde “sonraki” adımında çalıştıracağım.

6.)    “Önceki “ ve “Sonraki” şablonlarının sonuçlarının kıyaslaması: Son adımda ise “önceki” ve “sonraki” şablonlarında elde edilen bulgular birbiriyle kıyaslanacak ve bir rapor ile çalıştırma planı üzerilerinde değişimler, kaynak kullanım istatistikleri arasındaki değişimler pozitif ve/veya negatif yönden kolaylıkla görülebilecektir. Bu şekilde yapılacak değişimin üretim ortamına etkileri test ortamında simüle edilebilecek ve SQL komutlarının performanslarının gerçek platform için iyileştirmesi için çok etkili bir simülasyon ortamı oluşturulmuş olacaktır.

Şimdi yukarda bahsettiğim adımları Oracle Enterprise Manager(OEM) grafiksel arayüzünden adım adım uygulayalım:

1.)    İlk adımda iyileştirme yapılması planlanan SQL komutlarını içerecek olan SQL Tuning Set’inin oluşturulması gerekmektedir. Bu amaçla Oracle Enterprise Manager konsolundan “Performance” tabı altında yer alan “SQL Tuning Set” linkine tıklıyorum ve alttaki ilk adım ekrana geliyor.

SQL Tuning Set oluşturmasının ikinci adımında hedef SQL komutlarının nereden yakalanacağı ile ilgili kısım yer almaktadır. “Data Source”  kısmında yer alan değerler;

·         AWR snapshots – otomatik iş yükü ambarında kayıtlı olan SQL komutları ile geçmişte çalıştırılan SQL komutları yakalanabilir. Ne kadar geçmişe gidileceği AWR snapshots kısmında seçilir.

·         Cursor Cache – Henüz AWR raporuna yazılmamış ve önbellek içinde yer alan SQL komutları yakalanır.

Eğer imleç önbelleği  içerisinden belirli zaman aralığında(duration) birden çok çalıştırılan ve imleç kullanan SQL komutları yakalanmak isteniyorsa bu durumda ilk kısım seçilmelidir. Benim örneğimde tek sefer çalıştırılan SQL komutları yüklenmek istendiğinden ben ikinci kısmı seçiyorum ve burada “Cursor Cache” seçeneğini seçip aşağıdaki gibi ilerliyorum.

Ardından hangi SQL komutlarının yakalanacağı ile ilgili filtreleme alanına gelinir. Bu alanda ilgili şemaya ait SQL komutu gibi filtreleme seçenekleri mevcuttur. Bunun yanında eğer SQL cümlesinin ID si biliniyorsa bu filtre alanına direkt bu SQL ID de belirtilebilir. Ben SQL ID yi bilmediğimden ve bu komutları OE şemasından çalıştırdığımdan aşağıdaki gibi tanımlama yapıp ilerliyorum.

Bu SQL Tuning Setin hemen çalıştılıp ilgili SQL komutlarını toplamasını istediğimden bu görevin hemen çalışması için “immediatelly” kısmını işaretleyip ilerliyorum.

Son aşamada ise oluşturduğumuz SQL Tuning Set ekranda listelenir.

2.)    SQL Tuning Set oluşturulduktan ve içerisine hedef SQL komutları yüklendikten sonra artık SQL Performance Analyzer aracı çalıştırılmalıdır. SQL Performance Analyzer aracıda ana konsoldaki “Performance” tabı altından çalıştırılmaktadır. Ekranda gözüken “Guided Workflow “ ile “önceki” ve “sonraki” performans kıyaslaması işlemine başlayacağız. Bu aşamada yer alan diğer seçenekler ile ilgili makaleleri ilerleyen zamanlarda yayınlayacağımdan bunların ne anlama geldiği konusuna girmiyorum.

“Guided Wokflow “ ile aslında Oracle tüm aşamaları iş akışı şeklinde adım adım basite indirgenmektedir. İlk sıradaki görevin yanındaki “Execute” sembolüne tıklayarak işleme başlıyorum.

Oluşturacağım SQL Performance Analyzer görevine DEMO SPA adını veriyorum, SQL Tuning Set bölümü altında bir önceki adımda oluşturduğum SQL Tuning Setini tanımlayarak “CREATE” komutu ile bir sonraki adıma ilerliyorum.

Bir sonraki aşamada mevcut ortamda SQL denemesini oluşturuyorum.  Bu kısımda önemli olan nokta Creation Method ve Per-SQL Time Limit kısımlarıdır. “Creation Method” kısmında;  çalıştırılan SQL komutunun lokal ortamdamı yoksa uzak bir makinede varsayımı ile simulasyonu yapılacağı, veya lokal veya uzak makine varsayımı ile SQL komutunun çalıştırma planı simülasyonumu yapılacak, yoksa hem SQL komutu çalıştırma planı hemde çalışma esnasında kaynak kullanımı ile ilgili detay istatistikleme simülasyonu yapılacak gibi seçenekler yer alır. “Execute SQLs locally” ile lokal veritabanında çalıştırılan SQL komutlarının hem kaynak kullanımı detayları istatistiki olarak simüle edilmekte, hemde çalıştırma planı detayları bu simülasyona dahil edilmektedir. “Per-SQL Time Limit” kısmı ise bu test ortamında yüklü SQL komutunun ne kadar süre deneme simülasyonunda tutulacağını belirtir. Aşağıdaki seçenekler ile ilerliyorum ve “önceki” olarak adlandırılan şablonu hazırlamış oluyorum.

*** Bu aşamada karşınıza gelen ekranın sağ alt kısmındaki “Trial environment established” seçeneğini tıklamayı unutmayın. ***



Bir sonraki aşamaya geçmeden önce mevcut veritabanı ortamında değişiklik yapıyorum ve bu aşamada ilgili indeksleri oluşturup, gerekli istatistikleri yeniden topluyorum(bakınız 4. adım Veritabanında yapısal değişiklik yapılması) Bu işlem sonunda aşağıdaki gibi üçüncü görev alanınında “execute”  sembolüne tıklayarak, “sonraki” olarak adlandırılan şablonun oluşturulması işlemine geçiyorum.



“Sonraki” kısmındaki tanımlamalarım “önceki” aşamasındaki ile aynıdır.

Bir sonraki aşamada “önceki” ve “sonraki” aşamalarının kıyaslaması işlemi yapılacaktır, alttaki gibi ilerliyorum.

Kıyaslama metodu için kullanılacak kıstası belirliyorum. Bu kıyaslama metrikleri aşağıda yer almaktadır.  Ben bu örnekte “buffer gets “ kıstasını kullanacağım. Rapor  alındıktan sonra diğer metrikler içinde kıyaslama raporu alınabilmektedir.

Elapsed Time   -  SQL komutunun ne kadar sürede tamamlandığı

CPU Time        -  Bu komut için İşlemci çalışma zamanı

User I/O Time  -  Bu işlem için I/O süresi

Buffer Gets      -  Bu komut sonuçseti için önbellekten alınan veri miktarı 

Physical I/Os   -  Bu komut sonuçseti için fiziksel diskten alınan veri miktarı

Optimizer Cost -  İyileştirici maliyeti

I/O interconnect bytes -  Cluster ortamı için düğümler arası bağlantılarda transfer olan veri miktarı

Son aşamada deneme ortamında kıyaslama raporunu alacağım.

Aşağıdaki gibi bu SQL Performance Analiz görevinde oluşturulan kıyaslama raporu görülmektedir.  Gerekli iyileştirmeler sonucunda perfromansın iyileştiği “Improved” grafiğindende görülmektedir.

Örnek SQL cümlelerinden birisinin SQL ID si üzerine tıklayarak bu SQL cümlesi ile ilgili çalıştırma planı istatistiklerini “önceki” ve “sonraki” olarak ayrı ayrı görebilmekte olup aradaki değişimin etkisini yüzdesel olarakta görebilmekteyiz.

Bunun yanında “önceki” ve “sonraki” test ortamlarının simülasyon edilmiş çalıştırma planları aşağıda yer almaktadır. İndekslerin sisteme eklenmesi sonucunda cost değerinde bir  azalma meydana gelmiştir.