PL/SQL performans

Using Bind or Not

SGA’nın bölümlerinden biri olan shared pool data dictionary cache ve library cache olarak iki bölümden oluşur. Library cache yazdığımız ve kullandığımız en son sql ve pl/sql leri (procedure ve function vb..) saklar yani LRU(last recent used) algoritması doğrultusunda çalıştırır. Örneğin yazdığımız bir function bir kullancı tarafından kullanılmak istendiğinde kullanıcı bunu shared pool dan alır ve function zaten memory de olduğu için performansdan kazanmış olurum. O zaman library cache alanından haberdar olmak bir database developer için bize büyük fayda sağlayacaktır.

Library cache’in shared sql area sında yeni bir sql gelirse yeni alan oluşturulup sql için parse tree ve execution bilgileri oluşturulur. Tüm alanlar dolduğunda daha once bahsettiğimiz LRU algoritması ile alanlar sql lere dağıtılır. SQL’in çalışması için dört evresinden (parsing,optimization,row-source generation,execution) birincisi parsing işlemidir. Parsing işleminde sql’in doğru yazılıp yazılmadığı,sql deki objelerin olup olmaması ,daha önce sql’in kullanılıp kullanılmadığı gibi daha birçok işlem kontrol edilir ki bu da en maliyetli kısım olarak karşımıza çıkıyor. O zaman bir developer olarak performans için elimizden geldiği kadar parsing işlemini minimum sevyede tutmaya çalışacağız. O zaman amacımız ‘one parse-execute many’ olacaktır.

Daha öncede bahsettiğimiz gibi developer sql ve pl/sql yazdıktan sonra Oracle shared pool’a gidip daha önceden parse ve optimized işlemi yapılmış diye bakar. Eğer bulursa tekrar kullanır ve bu sistem için en performanslı yoldur . Eğer bulamazsa yukarda bahsettiğimiz dört aşamadan geçer. İşte bu noktada bind variable yardımımıza koşacaktır. Tek kullanıcılı sistemde performans farkı kullanıp kullanmama arasında daha az olacktır ama analizler yine bind kullanımı destekleyecektir.

Select * from emp where empno=123;

Selct * from emp where empno=:empno;

Mesela yukardaki ilk sorguda 123 nolu employee getirdim. Sonra bunu 234,345 vb gibi değiştirdiğim zaman her seferinde sorgumu baştan parse etmiş olacağım. Bind variable kullandığım ikinci sorguda ise sorgu bir kez compile edilip query planıyla birlikte library cache saklanır. Daha sonra istenildiği zaman parse edilmeden kullanılabilinir. Şimdi daha önceden bahsettiğimiz tkprof ile örneklerimizi analiz edelim. Birincisinde bind variable kullanıp ikincisinde kullanmayacağım. Alter system flush shared_pool ile testimin sağlıklı sonuç vermesi için shared pool’u sıfırlayacağım.

set timing on
set serveroutput on

alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;

alter system flush shared_pool;
alter system flush buffer_cache;

alter session set tracefile_identifier = bind ;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12′;

declare
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy all_objects.object_name%TYPE;
l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000 LOOP
OPEN l_rc FOR ’select object_name
from all_objects
where object_id = ‘ USING i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
END LOOP;
dbms_output.put_line(round((dbms_utility.get_time – l_start) / 100,
2) || ’seconds…’);
END;
/

ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;

1.48seconds…

set timing on
set serveroutput on

alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;

alter system flush shared_pool;
alter system flush buffer_cache;

alter session set tracefile_identifier = nobind1 ;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12′;

declare
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy all_objects.object_name%TYPE;
l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000 LOOP
OPEN l_rc FOR ’select object_name
from all_objects
where object_id = ‘ || i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
END LOOP;
dbms_output.put_line(round((dbms_utility.get_time – l_start) / 100,
2) || ’seconds…’);
END;
/

ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;

31.02seconds…

İki sorgu arasında zaman farkı anlamak için başta ve sonda dbms_utility.get_time ile zamanı alıp kıyasladığımda bind kullanmadan yaklaşık 30 katı kadar sure fazla harcamış gözüküyorum. İlk trace dosyamın boyutu 57kb ikne ikinci trace dosyam 3mb civarında. O zaman trace dosyalarımızın farkının nerden geldiğine bir bakalım.

call count cpu elapsed disk query current rows
Parse 1063 0.02 0.02 0 0 0 0
Execute 1092 0.17 0.23 0 0 0 0
Fetch 1301 0.08 0.93 171 6790 0 1203
total 3456 0.27 1.19 171 6790 0 1203

Misses in library cache during parse: 7

call count cpu elapsed disk query current rows
Parse 2100 29.07 29.73 0 6 0 0
Execute 2296 0.34 0.31 0 0 0 0
Fetch 2612 0.32 2.10 200 10549 0 3646
total 7008 29.73 32.15 200 10555 0 3646

Misses in library cache during parse: 1019

Yukardaki analizleri kıyasladığımda ikinci trace’imde parse işlemin maliyeti görüldüğü gibi cpu değerine de yansıdı. Fazladan parsing,cpu kullanımı,response time performansımın düşmesine neden oldu.

Bind

select object_name
from all_objects
where object_id =

call count cpu elapsed disk query current rows
Parse 1000 0.02 0.01 0 0 0 0
Execute 1000 0.15 0.19 0 0 0 0
Fetch 1000 0.05 0.19 49 6315 0 953
total 3000 0.22 0.40 49 6315 0 953

Nobind

select object_name
from all_objects

where object_id = 1

call count cpu elapsed disk query current rows
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.02 2 5 0 1
total 3 0.07 0.09 2 5 0 1

………………………………………..
………………………………………..
……………………………………….

select object_name
from all_objects

where object_id = 999

call count cpu elapsed disk query current rows
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.02 3 8 0 1
total 3 0.07 0.09 3 8 0 1

Birinci trace dosyamda yukardaki sorgu için bir defa plan oluşturuluken ikinci sorgumda x değerinin 1000’e kadar her değeri için ayrı parse etti ve plan oluşturdu ve bu ikinci trace dosyamın büyük boyutlara ulaşmasına neden oldu. Bind variable’nın parse sayısını azaltması cpu kazanımın yanında çoklu kullanıcılarda library cachedeki latch sayısını da azaltacağı için sistem daha hızlı çalışacaktır. Share sql alanında bir anda tek oturum hard parse edeceğinden birçok kullanıcının hard parse yapması bekleme yaratacaktır. Bu da yine performans kaybına yol açar.

Bind variable performansın yanında veritabanı güvenliği açısından da önemlidir. Sql injection sql üzerinden veritabanına sızma olarak adlandırılır. Bu bir paket çağırma,verileri göstermek olabilir. Sql injection olması için sql’in dynamic sql olması gerekir ve gelecek parametrelere gore runtime sırasında oluşur ve bu da güvenlik açısından bir açık olarak karşımıza çıkabilir. İşte bu noktada bind variable kullanımı yardımımıza yetişiyor.

IF you use dynamic sql
AND
you use string concatenation
THEN
you are subject to sql injection
END IF

Yukarda ifade ettiğimiz gibi concatenation kullanımı sql injection için uygun zemin hazırlayacaktır. Şimdi sql injection yönetmi ile ilgili bir örnek yapıp bind kullanımın yararlarını görelim. Önce bir tablo yaratacağız sonra sonra bir fonksiyon yardımıyla banka hesabına sızmaya çalışağız.

create table my_top_secrets (
PK_SECRET VARCHAR2(30) PRIMARY KEY,
BANK_ACCOUNT VARCHAR2(30),
AMOUNT NUMBER(10)
);
insert into my_top_secrets values(’1′, ‘Malta Bank – 123456′, 123000000);
insert into my_top_secrets values(’2′, ‘Isviçre Bank – 7890′, 4567000000);
commit;

create or replace function get_bankaccount(p_secret varchar2) return varchar2 is
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy varchar2(64) := ‘Fail’;
BEGIN
OPEN l_rc FOR ’select BANK_ACCOUNT
from my_top_secrets
where PK_SECRET = ‘|| p_secret;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
return l_dummy ;
END;

Tabloyu yaratıp satırları ekledik sonra bize banka hesaplarını getirmesini istedik .

set serveroutput on
declare
l_dummy1 varchar2(64) := ‘-1′;
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/
Fail
PL/SQL procedure successfully completed.

Dedikten sonra beklendiği üzere fail mesajını aldık

declare
l_dummy1 varchar2(64) := ‘-1′ || ‘ or 1=1′;
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/

Malta Bank – 123456

Yukarıda kırmızıyla eklenen kısımla banka hesabımızın içine sızılmış oldu. Halbuki fonksiyonu yazarken bu durumu düşünmemiştik. O zaman bu gibi durumlara karşı önlem almak için fonksiyonda bind kullanıp tekrar sızmaya çalışalım.

create or replace function get_bankaccount (p_secret varchar2) return varchar2 is
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy varchar2(64) := ‘Fail’;
BEGIN
OPEN l_rc FOR ’select BANK_ACCOUNT
from my_top_secrets
where PK_SECRET = ‘ USING p_secret;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
return l_dummy ;
END;
/
declare
l_dummy1 varchar2(64) := ‘-1′|| ‘or 1=1′;
l_dummy2 varchar2(64);
begin
l_dummy2 := get_bankaccount(l_dummy1);
dbms_output.put_line(l_dummy2);
end;
/

Fail

Bind kullanımından sonra bilgilerimize ulaşılamadı. Zaten bind kullanımın performasını yukarıda ıspatlamıştık. O zaman dynamic sql ifadelerde veya java da bind kullanamadığımız durumlar hariç(tablo isimleri,colon isimleri vb) her zaman bind kullanımı performans ve güvenliğimiz açısından yararlı olacaktır.

Cursor

PL/SQL içinde SQL yazdığımız zaman Oracle sqller için özel bir alan tahsis eder. Bu alan yazdığımız sqllerden ve getirdiği kayıtlardan oluşur. Cursor bu alana isim verebilmemizi ve içerdiği kayıtarı işlememizi sağlayan pl/sql mekanizmasıdır ya da başka bir deyişle veritabanımdaki tabloların içindeki pointer olarak söylenebilinir. İki çeşit cursor tanımlayabiliriz.

Kapalı İmleçler(İmplicit Cursor): Yazılan her sql komutları için Oracle tarafından otomatik olarak açılan imleçlerdir. Yazılan her kapalı imleç için SQL%NOTFOUND, SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND özellikleri açıktır.Cursor declare etmeye gerek yoktur .

SELECT SUM (salary) INTO department_total
FROM employee
WHERE department_number = 10;

Yukardaki ifader için Oracle implicit cursor otomatik olarak yaratacaktır. Eğer ifade birden fazla satır getitirse exception yaratacaktır. İkinci satırı getirmem için TOO_MANY_ROWS exception’ a ihtiyacım olacaktır .

Açık İmleçler(Explicit Cursor):Programcı tarafından belirli bir işi yapabilmek için açılan imleçlerdir..Cursordaki birden çok satıra tek tek erişme, processde olan satırı işlemek gibi işlemleri programcıya sunan cursor çeşididir. Parametre alabilir.

declare
cursor c1 is select ename, job, sal from emp;
v_ename emp.ename%type;
v_job emp.job%type;
v_sal emp.sal%type;
begin
open c1;
loop
fetch c1 into v_ename, v_job, v_sal;
exit when c1%notfound;
dbms_output.put_line(v_ename||’ ‘|v_job||’ ‘||v_sal);
end loop;
end;
/

Yukarda gördüğümüz önce cursorı declare ettik sonra open ile açtık ve bir döngü içerisinde daha once kolonla aynı type da kolon değişkenlere fetch ettik. Yukarıda gördüğümüz gibi cursorun açılması,fetch edilmesi ve kapanması herşeyi programcı yapıyor fakat implicit cursorda Oracle bu işlemleri otomatik yapar. Şimdi implicit ve explicit cursorları kıyaslayalım ve hangisi kullanacağımıza karar verelim. İlk önce performansları kıyaslayalım.

İmplicit cursor

create or replace procedure implicit_interpreted( p_times in number )
as
l_dummy dual.dummy%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. p_times
loop
select * into l_dummy from dual;
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ‘ hsecs…’ );
end;
/

Explicit cursor

create or replace procedure explicit_interpreted( p_times in number )
as
cursor c is select * from dual;
l_dummy dual.dummy%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. p_times
loop
open c;
fetch c into l_dummy;
if ( c%notfound ) then
raise no_data_found;
end if;

fetch c into l_dummy;
if ( c%found ) then
raise too_many_rows;
end if;

close c;
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ‘ hsecs…’ );
end;
/

exec explicit_interpreted( 50000 );

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.51

call count cpu elapsed disk query current rows
Parse 31 0.00 0.00 0 0 0 0
Execute 50035 1.14 1.13 0 0 0 0
Fetch 100113 1.41 1.89 78 150264 0 50093
total 150179 2.55 3.02 78 150264 0 50093

exec implicit_interpreted( 50000 );

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.48

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 31 0.00 0.00 0 0 0 0
Execute 50035 1.00 1.05 0 0 0 0
Fetch 50113 1.43 2.27 76 150267 0 50093
——- —— ——– ———- ———- ———- ———- ———-
total 100179 2.43 3.33 76 150267 0 50093

Parse ve execute aynı değerlere sahip çıktı. Elapsed süreleri için explicit cursor’ın daha fazla çıktı. Cpu kullanımı explicit de daha fazla olduğunu gördük. İmplicit cursor daha performanslı olduğunu burda ıspatladı Ayrıca select into yazmanın open,fetch,close yazmaya gore kadar kolay olduğu örneğimizden de görebiliriz. Sadece sql ile yapabileceğimiz sorguları sql ile yapmalı ve bunu pl/sql bırakmalıyız. Bu bize performans kaybı olarak dönecektir. Daha öncede bahsetdiğimiz gibi kolay yazılıp okunan kompleks olmayan kodlar herzaman bizim için daha avantajlı olacaktır.

İmplicit

for x in ( select * from t )
loop
….
end loop

Explicit
Cursor is select * from t;
open c;
loop
fetch c into x;
exit when c%notfound
….
end loop;
close c;

Yukardaki aynı işlevi yapan iki farklı cursora baktığım zaman daha öncede bahsettiğimiz yazım kolaylığı ortaya çıkıyor. Explicit cursorda bazen nerde cursor’ın açılacağını nerde kapanacağını ya da nıasl yazılacağını unutabilirsiniz. Kısacası kompleks sorgulardan uzak durmaktan yana olacağız. İmplicit ile ilgili ilk örneğimizde birden fazla satır getirmemiz gerektiğinde TOO_MANY_ROWS exceptionu kullanmamız gerekir demiştik. Aşağıdaki örnekleri inceleyip bu durumu tekrar gözden geçirelim.

Explicit cursor

declare
l_dname dept.dname%type;
l_deptno dept.deptno%type;
l_ename emp.ename%type;

cursor c1
is
select deptno, dname
from dept
order by deptno;

cursor c2( p_deptno in number )
is
select ename
from emp
where deptno = p_deptno
and sal = (select max(sal)
from emp
where deptno = p_deptno);
begin
open c1;
loop
fetch c1 into l_deptno, l_dname;
exit when c1%notfound;
open c2(l_deptno);
fetch c2 into l_ename;
close c2;
dbms_output.put_line
( l_deptno || ‘, ‘ || l_dname || ‘, ‘ || l_ename );
end loop;
close c1;
end;
/

İmplicit cursor

declare
l_ename emp.ename%type;
begin
for x in (select deptno, dname from dept order by deptno)
loop
begin
select ename into l_ename
from emp
where deptno = x.deptno
and sal = (select max(sal)
from emp
where deptno = x.deptno)
and rownum = 1;
exception when no_data_found
then
l_ename := ‘(none)’;
end;
dbms_output.put_line
( x.deptno || ‘, ‘ || x.dname || ‘, ‘ || l_ename );
end loop;
end;
/

Yukarda yazdığımız implicit cursor bu sefer birden fazla satır getirme ihtimaline karşı hata vermesine karşın rownum=1 ile tek tek getirmesini ve no_data_found ile boş satır getirmesi durumunda ne yapması gerktiğini söyledik ve böylece implicit cursor ile ilgili birden fazla satır getirirken TOO_MANY_ROWS exception kullanmamız gerektiği ve explicit cursor’ın bize daha fazla kontrol etme imkanı sunduğu gibi ifadeleri yıkmış olduk. İmplicit cursor’ın performansını ve kullanım kolaylığını ıspatladıktan sonra artık geriye bize implicit cursor kullanmak düşüyor.

Using the BULK COLLECT Clause

Yukarıda gördüğümz gibi sql ifadelerimiz çalışırken pl/sql engine ve sql engine sürekli birbirleriyle satır transferi halindedirler. Bulk Binding iki engine arasında satırları transfer ederken collections(dizi veya liste) halinde gönderme olanağı sağlar. Bu da performansı artıracaktır.

Bulk Collect bulk binding ‘in select sorguları için kullandığı sentaksıdır.Bulk Collect bütün satırları tek seferde resul set olarak almamızı sağlar. Satırda istediğimiz kolonu değişkene atarak istediğimiz işlemi yapmamızı sağlar ve bunu çok hızlı bir şekilde yapar. SELECT INTO, FETCH INTO, and RETURNING INTO clauseları yardımıyla kullanabiliriz.Şimdi bir tablo yaratıp all_objects kayıtlarını alalım ve birkaç kez defa insert edelim.

create table t_all_objects as select * from all_objects;

insert into t_all_objects select * from t_all_objects;

insert into t_all_objects select * from t_all_objects;

Bulk

SQL> declare
2 cursor c1 is select object_name from t_all_objects;
3 type c1_type is table of c1%rowtype;
4 rec1 c1_type;
5 begin
6 open c1;
7
8 fetch c1 bulk collect into rec1;
9
10
11 end;
12 /

call count cpu elapsed disk query current rows
Parse 28 0.01 0.02 2 2 0 0
Execute 30 0.02 0.02 0 0 0 0
Fetch 84 0.97 2.47 1433 1609 0 110660
total 142 1.00 2.52 1435 1611 0 110660

Nobulk

declare
cursor c1 is select object_name from t_all_objects;
rec1 c1%rowtype;
begin
open c1;
loop
fetch c1 into rec1;
exit when c1%notfound;
null;
end loop;
end;

call count cpu elapsed disk query current rows
Parse 14 0.01 0.01 1 1 0 0
Execute 15 0.02 0.01 0 0 0 0
Fetch 98346 1.50 2.16 1259 98405 0 98338
total 98375 1.53 2.19 1260 98406 0 98338

Yukarıda görüldüğü gibi ilk örneğimde fetch ederken bulk collect kullandım. Sql ve pl/sql engine arasındaki row satır interaction azaldı, fetch sayım azaldı ve daha az cpu kullanımı sağlamış oldum.