Oracle SQL Processing, Shared Pool ve Bind Variables

Bu yazımda sizlere oracle’ın bir sorguyu nasıl işlediğini, işleme adımlarının neler olduğunu ve bu sorgu işleme yapısı üzerinden performanslı sorguların nasıl yazılacağı hakkında bilgi vereceğim. Bu kapsamda Oracle’ın Shared Pool yapısından ve sorgularda bind variable kullanmanın sorgu performansı açısından ne kadar faydalı olduğu üzerinde örnek vererek duracağım.

SQL Processing

Oracle’da SQL processing işlemi basit olarak yandaki flowdaki adımlar yapılarak gerçeklenmektedir. Bu işlemleri kısaca açıklarsak.

Create Cursor: Bu adımda yazdığımız SQL için memory’de bir alan ayrılır ve bu alanda yazdığımız SQL’in ve sorgu ile ilgili gerekli bilgiler tutulur.

Parse SQL:  Parse SQL adımında yazılan SQL’in syntax checking işlemi, shared pool’da(Shared pool hakkında bilgi daha sonra verilecektir.) daha önceden olup olmadığı eğer yok ise Execution planının oluşturulduğu en önemli adımdır. Parse SQL adımı Oracle SQL Processing’in en maliyetli ve en önemli adımıdır.

Bind Variables: Eğer çalıştırdığımız veya yazdığımız SQL daha önce farklı bir parametre ile çalıştırıldıysa, aynı SQL’e yeni parametrenin eklendiği adımdır. Ancak bind variable kullandığımızı sorgu içinde farklı bir yazım notasyonu ile belli edilmesi gerekmektedir. Aksi takdirde sorgu ilk kez çalıştırılıyormuş gibi işlem görür. Daha önce çalışmış bir sorgunun yeniden parse edilmesi performans anlamında ciddi kayıplara neden olacaktır. İleride vereceğim örnekle bind variable kullanmanın performansa olumlu  etkilerini inceleyebileceksiniz.

Execute SQL: Bu adımda SQL statementımız bir DML(insert,update,delete) veya DDL(Alter,Create,..) ise SQL tamamı ile işlenir. Ancak SQL’imiz bir sorgu cümlesi ise SQL’in döndüreceği satırları elde etmek için SQL fetch işlemine gönderilir.

Fetch Rows: SQL’in içeriğine göre database’den  ilgili satırlara erişim sağlanarak kayıtlar resultset’e döndürülür.

Close Cursor: SQL’imizin memory üzerinde tüm kullandığı alan dealocate edilir.

Yukarıda da bahsettiğim gibi SQL Processing’de en kritik ve maliyetli adım SQL’in Parse edilme adımıdır.  Eğer bind variable yapısını etkin bir şekilde kullanabilirsek sorgularımızın çalışma süreleri oldukça iyileşecektir. Özellikle sık kullandığımız SQL’leri yada aynı işleri tekrarladığımız bir çok sorguyu bind variable kullanarak implemente etmek sorgu performanslarımızı ciddi ölçüde iyileştirecektir.

Developerların sık olarak yaptığı yanlışlardan biri dynamic SQL oluştururken değişkenleri SQL’in içine concat etmeleridir.  Bu şekilde oluşturulan SQL’lerde genellikle sadece parametre kısımları değişmektedir ve aynı SQL farklı parametreler ile defalarca çalıştırılmaktadır. Oracle parse edilen her SQL’i shared pool denen bir mekanizma ile içinde saklamaktadır. Bunun amacı benzer gelen sorgular için yeniden Parse işlemi yapmak istememesidir. Shared Pool’da sistem üzerinde sadece bizim parse edilen SQL’lerimiz yoktur bu yapı içinde farklı user larında Parse edilen ve çalıştırılan SQL’leride bulunmaktadır.  Böylelikle yazılan SQL’lerin yeniden parse edilme olasılığı mininmuma indirgenmeye çalışılmaktadır. Oracle’ın bu mekanizmasından etkin faydalanabilmek için developerlara düşen görev yazdıkları sorguları hardcoded bir şekilde değil bind variable kullanarak oluşturmalarıdır. Parse işlemine tabi olmayan her sorgu çalışma zamanı olarak hızlanacaktır. Bu sorgu için yeniden Execution plan oluşturulmayacaktır. Aşağıdaki şekilden bu yapıyı daha ayrıntılı bir şekilde inceleyebilirsiniz.

Şimdi Bind Variable Kullanmanın ne kadar etkili olduğunu göstermek için aşağıdaki örneğimizi inceleyelim.

Aşağıdaki gibi bir tablo oluşturalım:

CREATE TABLE HR.DENEME
(
COL NUMBER NOT NULL
);

daha sonra bu tabloya 2 farklı şekilde insert yapıp sorguların ne kadar sürede çalıştıklarını görelim. (1. Yöntem Concatanate edilerek oluşturulan SQL’ler, 2. Yöntem Bind variable kullanarak oluşturulan SQL’ler). Sorularımızın 2 side aynı işlemi yapacaktır. 1 den 100000’e kadar olan sayıları teker teker tabloya insert edecekler.

1.Yöntem: 

SET timing on;

DECLARE
v_sql VARCHAR2 (100);
BEGIN
FOR i IN 1 .. 100000
LOOP
v_sql := ‘insert into deneme values(‘ || i || ‘)’;

EXECUTE IMMEDIATE v_sql;
END LOOP;
END;

Bu sorguyu çalıştırdığımızda, sorgumuzun çalışma zamanı:

PL/SQL procedure successfully completed.
Elapsed: 00:00:30.01 

2.Yöntem (Bind Variables):

SET timing on;

DECLARE
v_sql VARCHAR2 (100);
BEGIN
FOR i IN 1 .. 100000
LOOP
v_sql := ‘insert into deneme values(:i)’;

EXECUTE IMMEDIATE v_sql
USING i;
END LOOP;
END;

Bu sorguyu çalıştırdığımızda, sorgumuzun çalışma zamanı:

PL/SQL procedure successfully completed.
Elapsed: 00:00:03.49

Evet çok basit bir sorguda bile çalışma zamanındaki iyileşmenin yaklaşık 10 kat olduğunu görüyoruz. Bunun nedeni daha öncede bahsettiğim gibi 1. Yöntemde çalışan SQL hep aynı  olmasına rağmen sürekli yeni bir parametre ile conctanate edilip Oracle engine yollanması oracle tarafından farklı SQL olarak işaretlenip yeniden Execution plan çıkarılmasına neden olmuştur. Bunun sonucunda 100000 farklı değer için 100000 parsing işlemi yapılıp yeniden execution planlar oluşturulmuştur. Sonuç olarakta çok uzun ve performansız çalışan bir sql elde edilmiştir. Örneğimizdende görüldüğü gibi sorgularımızda 2. Yöntemdeki gibi  bind variable kullanmak sorgu performanslarımızı ciddi şekilde hızlandıracaktır. Sorgu iyileştirmeleri yaparkende ilk bakacağımız ayrıntı sorguda bind variable kullanılabilip kullanılamıyacağı olmalıdır.

Kaynaklar: tahiti.oracle.com , tonguc.wordpress.com, Oracle SQL High Performance Tuning Second Edition THE Prentence HALL(Guy HARRISON)

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

3 Responses to Oracle SQL Processing, Shared Pool ve Bind Variables

  1. Pingback: Real World Performance Tour, Türkiye | Emrah METE

  2. tolga says:

    güzel anlatım, teşekkürler…

  3. Pingback: Execution Öncesinde ve Sonrasında “Execution Plan” | Emrah METE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s