Java Collections

This is my first blog entry on java technologies. My friend asked me why I don’t publish articles on Java. So i ama little excited. Well, Today we glance at Collection on java. A collection in data structure terms, is a group of elements.It includes Sets and Lists. It also includes Maps which don’t implement Collection. Collection is the interface implemented by classes such as Arraylists,TreeSet and HashSet implement. It defines to methods to them all. Collection classes live in java.util package. I want to give ArrayList and LinkList Class example that implements List interface

public class Data {
protected int  myArray [];
public int size ;
public Data()
{
myArray = new int [50];
}
public Data (int n)
{
myArray = new int[50];
size =n;
}
protected void finalize() {
System.out.println(”cleaned” + size);

}
}
import java.util.*;

public class ArrayListExample
{

public static void main(String[] args) {
ArrayList l = new ArrayList();
int sizeOfL ;
for (int i = 0; i<5; i++)
{
l.add(new Data(i));
}
sizeOfL = l.size();
for (int i = 0; i <sizeOfL; i++) {
int size =((Data) l.get(i)).size;
System.out.println(”elements 1…” + size);
}

l.add(0, new Data(6)); // 6,0,1,2,3,4
l.add(2, new Data(17)); // 6,0,17,1,2,3,4
sizeOfL = l.size();

for (int i = 0; i <sizeOfL; i++) {
int size =((Data) l.get(i)).size;
System.out.println(”elements 2….” + size);
}
l.remove(3); //6,0,17,2,3,4
List sub = l.subList(1, 3); // 0,17
}

}

A LinkedList is similar to an ArrayList in that it is ordered by index position. If you want to have random access of the list
Arraylist offers quick access than LinkList but this comes at slower operations for adding and removing in the middle of the
list.
Vector is synchronized whereas ArrayList is not.ArrayList and Vector class both implement the List interface. Both classes are implemented using dynamically resizable arrays, providing fast random access and fast traversal when you want programs to run in multithreading environment then use concept of vector because it is synchronized. But ArrayList is not synchronized so, avoid use of it in a multithreading environment.Arraylist has no default size but vector has size of ten. We can see using capacity()method.

import java.util.*;
public class Demo1 {
public static void main(String[] args) {
//creating vector
Vector v= new Vector();
v.add(”baran”);
v.add(”ipek”);
//creating enumeration interface
Enumeration E=v.elements();
System.out.println(”Element are : “);
while(E.hasMoreElements())
{
System.out.println(E.nextElement()+”\t”);
}
//Returns the current capacity of this vector.
int i=v.capacity();
System.out.println(”Capacity of the vector is: “+i);
}

}

PL/SQL EXCEPTIONS

Evde yaptıgım ufak bir exceptions örneğini anlatmak istedim. Birazda giriş sevyesinde fonksiyonlar ve ref cursor hakkında bilgi vermiş olacağım. Çok basit bir işlem yapan fonksiyon 3 kolondan oluşan tabloma ID verip diger bilgileri alan fonksiyon yazdım cursor döndürcem sonuç olarak. O zaman hemen fonksiyonu yazalım. Eger exception alırsam 1 parametresini yollicam.

CREATE OR REPLACE FUNCTION test
(
iid IN NUMBER,
ostatu OUT NUMBER
) RETURN SYS_REFCURSOR AS
TYPE v_cursor IS REF CURSOR;
p_cursor v_cursor;
verrormsg VARCHAR2(4000);

l_row VARCHAR2(200) := ”;
BEGIN
l_row := ’select * from deneme where ID = :iID’;

OPEN p_cursor FOR l_row
USING iid;

EXCEPTION
WHEN OTHERS THEN
ostatu := 1;
verrormsg := substr(SQLERRM, 1, 3000);
INSERT INTO log_data VALUES (iid, verrormsg, SYSDATE);
COMMIT;
RETURN p_cursor;

END;

Hemen ardından test ediyim bakalım data getiriyormu ?

DECLARE
refcursor SYS_REFCURSOR;
vid NUMBER;
vname VARCHAR2(100);
vn_statu NUMBER;
vil NUMBER;
BEGIN

refcursor := test(104022008230216, ostatu => vn_statu);

LOOP
FETCH refcursor
INTO vid, vname;
EXIT WHEN refcursor%NOTFOUND;

dbms_output.put_line(vid || ‘ | ‘ || vname);

END LOOP;
CLOSE refcursor;
END;

Getiriyor valla denedim gördüm. Sonra ne zorum varsa gidip select çektiğim tabloyu drop ettim. Dedimki o zaman log_data tablosu yapıyım da hatayı göriyim sanki bilmiyomuşum gibi:) Exception kısmından gelebilecek tüm exceptionlar için when others then kullandım. Sonra yine test ettim .

DECLARE
refcursor SYS_REFCURSOR;

vn_statu NUMBER;

BEGIN

refcursor := test(104022008230216, ostatu => vn_statu);

dbms_output.put_line(vn_statu);
CLOSE refcursor;
END;

OUT parametrem olan 1 gördüm ve merat edip gidip log tabloma baktım. Evet table or view doesn’t exist diyor allah allah ne oldu acaba:) Basit bir örnek göstermek istedim tabi bu arada yaptıgım eksikler de var hızlı yapıyım derken. Mesela paketin içine yazmalıydım fonksiyonu,dynamic sql ‘e gerek varmı ? vb.. ama dediğim gibi örneklendirmek için basit bir örnek göstermek istedim.

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.

Düşünelim Bakalım

Aslında zekanın bir iki soruyla ölçülmeyeceğini düşünsemde(çok yönlü bir kavram) yinede bazı begendiğim sorular oluyor onları paylaşmak istedim.Emrehan Halıcının zeka soruları kitabından yazıcam yavşa yavaş bir-iki soruyla başlayalım sonra yazarım cevapları:)

1-)İki arkadaş bir oyun oynuyor. 10 tane şişeden sırayla 1 ya da 2 şişe alma şansınız var. Sıranızda sizde iken diğerine şişeleri alıp diğerine şişe bırakmayan oyunu kazanıyor. Oyuna sizin başladığınızı düşünürseniz oyunu kazanmak için nasıl bir yol izlersiniz.

2-)İki arkadaş atlarını yarştırmak istiyorlar ama farklı olarak yarışmayı sonuncu kazanacaktır.İkiside başlangıç çizigisine geliyor ama yarışmayı sonuncu kazanacağı için başlamak istemiyorlar. Bu yarışmayı nasıl gerçekleştiririz o zaman?

3-)100 tane kartım var ve üstlerinde yazılar var. İlk kartı açtım ve üstünde “bu kartların yalnızca bir tanesi yalan söylüyor ” yazıyor ikinciyi açıyorum “bu kartların yalnızca iki tanesi yalan söylüyor” yazıyor ve yazılar açtığım kartın sırası olarak artarak gidiyor .Peki kaçıncı kart doğru söylüyor.

Devamı gelecek:)

Parallel Execution

Parallel Execution

Parallel Execution Oracle 7.1 ile gelmiş Standart Edition da olamayıp Enterprise Edition’a ait bir özelliktir. Parallel Execution’u aklınız da canlandırmak için bir ev yaptırdğınızı düşünün evin yapımında birden çok takım çalışacaktır. Evin yapımında herkesin aynı anda aynı iii yapması yerine binanın dikilmesi ve elektirik tesisatı gibi farklı görevlerin aynı anda yapıldığını düşünün şüphesiz evinize erken oturacaksındır.

Oracle da Paralel Execution da aslında aynı mantığı uygular büyük jobları küçük parçalara bölerek concurrent olarak gerçekleştirir. Mesela bir tablo full scan yapılıcaksa 4 processin tablonun farklı yerlerini okuması ve sonucu process koordinatöre getirerek client a sonuc döndürülür ve bu response time ‘ı düşürecektir. En kaba tabirle Oracle ‘ın bir processle yapacağı bir işi birden çok küçük iş parçacıklarına bölüp bunların aynı anda çalışmasıdır.

Seri işlemlerde mesela tablolarmuz full scan edilsin dedicated server process sonucu user processe döndürürür. Paralel query de ise mesela server ikinci derecen paralel hinti görünce Query coordinator tarafından iki dedicated server process yaratılır ve scanlenecek bloklar ikiye ayrılıp slave processler gönderilir.

Parallel Execution uygulamadan önce iki şeyden emin olmalısınız.

1-) Büyük bir taskınız olmalı (Mesela 50 gb full scan data)
2-) Yeterli kaynağınız oldugundan emin olmalısınız yeterli cpu ve I/O gibi (Paralel Processlerin çalışması için).50 gb verininde bieden çok diske dagılması ve disklerden datayı getirmek için I/O kanallarının yeterli olması beklenir.

A Paralel Analogy

Şimdi neden fazla veri ve yeterli kaynak istediğimizi örneklerle açıklayalım. İki task olduğunu düşünün birincisi bir sayfanın özetini alacak ikincisi ise 10 chapter’ın kısa bir özetini alacak ve her bir chapter birbirinden bağımsız halde farklı konular içeriyor.

Birinci görev de sayfada 12 paragraf var ve paragraflar birbirinden bağımsız değiller. Siz bir paralel process kordinatör oldugunuzu düşünün ve 12 kişilik bir takım tuttunuz ve her bir paragrafı bir kişiye verip problemi anlattınız. Sonra her paragraf özetini alıp dogru yazılıp yazılmadığını kontrol edip sıraya koyup yazdırdınız. Bütün bu işlemler sizin serial olarak sayfanın özetini yazmanızdan daha çok zaman alacaktır. İkinci göreve gelince her üniteyi bir kişi verip onlardan gelen sonuçları direk yazdırmanız çok daha performanslı olacaktır.Kaynak yeterliliği konusundan ise bir yine manager olun ve bir işi takımdaki bireylere dağıtmak istediğinizi düşünün eger takım arkadaşlarınıza yapabileceklerinden fazla iş veremezsiniz ve kaynağınız yani çalışanlarınız üstlendikleri işleri yerine getiremiyeceklerdir.

Parallel Execution büyük tablo scanlerken,joinlerken, büyük indeksler yaratırken,tablolar,materialized view yaratırken,bulk insert,merge,update ve delete lerde performansı artırması beklenir.Ayrıca LOB gibi database objelerine de paralel ulaşabilirsiniz.Multiprocessor, yeterli I/O bandwith,yeterli olarak kullanılmayan CPU (mesela %30 un altı) paralel execution performansı için olumlu koşullar olacaktır. Data Warehouse ,OLTP batch processler için paralel execution uygun koşullarda performansı artıracaktır. 1-2 saniye süren kısa tipik OLTP işlemlerde paralel execution yararlı olmayacaktır çünkü slave processlerin koordine edilmesi de bir cost yarattığından bu tip uygulamalarda paralel execution kullanmak performansı düşürecektir. Ayrıca I/O ve CPU yeterince kullanılan sistemlerde kullanmak da yine performansı düşürecektir.

Paralel Query

Paralel query de bir sql ‘in birden çok işparçacıklarına bölünüp bunların eşzamanlı çalışmasıdır. Paralel query de tablo birbiriyle kesişmeyen parçalara bölünür ve her bir slave process tablonun ilgili kısmını okuyup sayar ve asıl kullanıcıya sonucu döndürecek olan server process olan kordinatöre sonuç döner.

Select count(status) from big tables;

Id | Operation | Name | Rows |Bytes | Costs (%CPU) | Time
1 SELECT STATEMENT 2 00:06:29
2 SORT AGGREGATE
3 TABLE ACCES FULL 2 00:06:29

Yukarıda beklediğimiz gibi seri planla karşılaştık Parallelism uygulamak için sql ‘e hint koyabilir ya da objeye paralleliğin derecesi dikte edebiliriz.

>> alter table big_table parallel 4 ;
Ya da

>> alter table big_table paralel ;
Şeklinde yazıp parallelism derecesini Oracle ‘a bırakıyor ve Oracle kaynaklarını uygunsa dinamik olarak parallelik derecesini artıracaktır ya da kaynaklar yogunsa paralellik derecesini düşürecektir.

>>explain plan for
2 select count (status) from big_table ;

Id | Operation | Name | Rows |Bytes | Costs (%CPU) | Time
0 SELECT STATEMENT 1 00:00:54
1 SORT AGGREGATE
2 PX COORDINATOR
3 PX SEND QC (RANDOM)
4 SORT AGGREGATE
5 PX BLOCK ITERATOR
6 TABLE ACCES FULL 1
7

Genel anlamda paralel execution çalıştırırken datayı oldukça fazla diskte tutmak ,raid diskler arasında strinping için ve partitioning datayı fiziksel olarak datalara dagıtmak için kullanılmalıdır ve tablespace de birden çok datafile oluşturmak Oracle ‘ın tablo segmentleri extent etmesi için performanslı olacaktır. Bütün bunlar datayı tek diskte tutulursa paralel execution kullanmak işe yaramayacaktır anlamına gelmiyor fakat ideal olan tablo yukarda bahsettiklerimden oluşuyor.

Parallel DML(PMDL)

Insert,update,delete,merge gibi dml cümlecikleri paralelleştirilebilinir.PMDL de yine parallel execution servers lar tek process yerine DML işlemlerini gercekleştirir.PDML ler yine direk OLTP işlemlerin performansını artırmak için düşünülen bir yol değildir. Genel de tek kullanıcın kaynakları(CPU,disk,memory) en fazla kullanılmasına yönelik bir özelliktir. Paralel execution datawarehouse(çok data,az kullanıcı) işlemlerde uzun süren table summary alırken performans sağlar ve OLTP de long batch job işlemler de yine performansı artırır. OLTP işlemlerde haliyle kısa transaction ve çok kullanıcılı ortamlarda makine kaynaklarını tamamen kullanıcın kullanmasını haliyle istemeyeceksinizdir.DML işlemleri paralelleştirilirken oracle belli kurallar uygular. UPDATE ve DELETE işlemleri için ayrı İNSERT işlemleri için ayrı kuralları vardır.

• Trigerlar PDML operasyonları süresince desteklenmez çünkü ilk update ler sırasında tigerlar sisteme yük olabilir ve sizde PDML hızlı olması için kullandıgınız için iki özellik aynı anda çalışmaz.

• PDML ile değişikliğe uğrayan tabloya commit ya da rollback denilmeden ulaşamazsınız.

• Clustured table desteklenmez.

• Bitmap indeks ve lob kolonlarında eger tablo partitioned ise çalışır ve slave process sayısı partition sayısı ile aynı olmalıdır.

• Insert values desteklenmez .

Oracle UPDATE ve DELETE statementları sadece partitioned tablolarda gecerlidir. Nonpartitioned table da DML ifadeleri paralelleştirlemez. Standart INSERT ifadeleri paralelleştirlemez .INSERT SELECT FROM statemenları paralelleştirilebilinir.Örnek vermek gerekirse:

alter session enable parallel dml;
insert /*+ parallel (emp_big,4,1) */
into emp_big select * from emp;
commit;
alter session disable parallel dml;

How Parallel Execution Works

Oracle taskları alıştıran SQL statementları küçük birimlere ayırarak bagımsız process (threads wndows enirement )olarak çalıştırır. Query coordinator parallelleştirmek için işi küçük parçacıklara böler.Bu processler parallel execution server processes, parallel slave processes gibi adlandırılabilinir. Her bir processe bir gorev atar. Sonra her slave processeden dönen sonucu toplayıp statement’ın sahip oldugu user process’e döner. İş bitiminden sonra her slave process bırakılır ve server pool’a döner.

How Parallel Execution Servers Communicate

Sorgunuzun parallel çalışması için Oracle producer queue server ve consumer server yaratır.Producer queue server tablodan datayı getirir ve consumer server join,dml ve ddl işlemlerini yapar. Her producer bir consumer’a connection ile bağlıdır. Bu da virtual bağlantı sayısı paralleliğin derecesini vermekte olduğunu gösterir.

The Pool of Parallel Slave Processes

Oracle instance’ı başladığında açılışında sayısı PARALLEL_MIN_SERVERS ile belirtilen parametre ile parallel execution servers için pool yaratır. Parallel Operasyon çalıştırıldıgında coordinator parallel execution servers ları pooldan alır ve göreve atar.Statment bittikten sonra processler poola döner. Sql statement alıştırıldıgında optimizer parallel olup olmayacağına ve paralleligin derecesine DOP(degree of parallelism) ‘a gore karar verir.

Slave processlerin sayısı paralleligin derecesiyle belirlenir. Oracle paralleligi iki component den oluşur. Birincisi kullanılacak instance sayısı ikincis her instance daki slave process sayısı. Parallelik derecesi Statement level , Object level, Instance level gibi 3 level da belirlenir. Oracle hint ya da parallel clause kullanan statement level’a bakar birşey bulamazsa objct level da tablo ya da indekse bakar orda da dışardan değer verilmemişse instance için verilmiş default değeri uygular. SELECT /*+ PARALLEL(orders,4,1) */ COUNT(*) FROM orders; gibi hintle statement level bazında belirlemiş oluruz. ALTER TABLE order_items PARALLEL (DEGREE 4); dersek object level bazında belirlemiş oluruz.

Parallel DDL

Parallel DDL partitioned ve nonpartitioned tablo ve indekslerde çalışır. CREATE TABLE…AS SELECT, CREATE INDEX, ALTER INDEX…REBUILD gibi nonpartitioned ya da ALTER TABLE…MOVE PARTITION, ALTER INDEX…REBUILD PARTITION partitioned tablo ya da indekslerde paralelleştirilebilinir. Kısıtlama olarak Object kolonları ya da LOB kolonları paralelleştirilemez.

Parallel Data Loading

Oracle’s SQL*Loader parallel olarak belli kısıtlamlar dahilinde harici dosyalardan data load etmemize izin verir. Eger çok fazla data load edecekseniz sql loaderın parallel gerçekleştirmesi elapsed time’ı düşürecektir. Paralel loading de birden çok path tanımlayarak aynı tabloya ya da tablonun aynı bölümüme data load edebilirsiniz.
Bunun için birden fazla input datafile, her datafile için control file ve her control file ve datafile için bir sqlloader oturumu açmanız yeterli olacaktır. Örnekleycek olursak:

SQLLOAD scott/tiger CONTROL=con1.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con2.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con3.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con4.ctl DIRECT=TRUE PARALLEL=TRUE

Parallel Recovery

Seri recovery de SMON backgroung process’i hem redologları okur ve değişiklikleri datafile ‘a yazar .Recovery için birden fazla datafile gerektiğini düşünürsek seri işlem oldukça zaman alacaktır. Parallel Recovery de SMON redo logları okur ve multiple parallel slave processes ler datafile lara yazar ve böylece recovery zamanı düşer.

RECOVER TABLESPACE tab PARALLEL (DEGREE 4);

Procedural Parallelism

İki türlü procedural parallelism bunlar parallel pipelined functions ve do-it-yourself parallelism DIY .Bunlardan pipelined fonksiyonları örneklendirelim.. Bir seri çalışan batch process prosedürmüz var.

Create procedure process_data
As
Begin
For x in(select * from some_table)
Perform complex process on X
Update some other table ,insert into somewhere else
End loop
End;

Bu prosedür sistem kaynaklarını çok kullanmadığını düşünün ve çalışması saatlerce sürüyor . Runtime da zamanı düşürmek istiyoz bunu yukarda bahsettiğim iki yaklaşımla çözmeye çalışalım.

Parallel Pipelined Functions

Data önceden çalışan process_data prosedürünü parallelleştirelim.Bunu gerçekleştimek için bir tablodan seçip onu işleyip başka tabloya insert etmek yerine loopdaki insert yerine PIPE ROW koyup,sonucu başka tabloya koyup orda işlemeye çalışalım.İki tablo kullanacağız birincisi t1 daha önceden okudugmuz tablo ikincisi ise taşıyacağımız tablo t2 olsun.

Create table t1
As select object id ,object_name text
From all_objects;

Create table t2 as
Select t1.*,0 session_id
From t1
Where 1=0;

T2 tablosunu t1 ile aynı yapıda yarattık. Session_id ile parallelismi görmek için ekledik. Sonra pipelined fonksiyonun döndüreceği output type’ı yarattık.

Create or replace type t2_type
As object

(
Id number ,
Text varchar2(30),
Session_id number

)

Create or replace type t2_tab_type
As table of t2_type

Yazacağımız fonksiyonda refcursor ile input alıp t2_tab_type ‘a değer döneceğiz. Pipelined fonksiyonda parallel_enable durumda ve partition clause ile datayı en ideal biçimde bölümleyerek getir diyoruz. Fonksiyonu yazıyoruz.

Create or replace function parallel pipelined (l_cursor in sys_refcursor)
Return t2_tab_type
Pipelined parallel_enable (partition l_cursor by any)

Is

L_session_id number;
l_rec t1%rowtype;
begin
select sid into l_session_id
from v$mystat
where rownum=1;

loop
fetch l_cursor into l_rec;
exit when l_cursor&notfound;
—complex process here
Pipe row(t2_type(l_rec.id,l_rec.text,l_session_id));
End loop;
Close l_cursor;
Return;
End;

Datayı parallel işlemek için artık hazırız parallelik derecesini Oracle kaynak durumuna gore otomatik ayarlayacaktır.

Alter session enable parallel dml;

>>insert /* +append */
into t2(id,text,session_id)
Select * from table (parallel_pipelined(CURSOR (select /*+parallel(t1)*/* from t1)))

>> select session_id,count(*)
From t2
Group by session_id;

Session_id count(*)

241 8040
246 8045
253 8042
254 8042
258 8040
259 8041
Yukarda gördüğümüz gibi Oracle dereceyi 6 olarak belirledi ve her bir slave process yaklaşık 8000 satır işledi.

Instance Configuration

Parallel Execution’un size performans sağlayacağını düşündüğünüz durumlarda konfirigasyon çok da zor olmamaktadır. Eger session ALTER SESSION FORCE PARALLEL … çalıştırılmışsan sql ler için derece CPU_COUNT ile belirtilen ve defaultda system cpu sayısı olan parallelikte çalışır.

parallel_adaptive_multi_user

Parallel execution da çok önemli parametrelerden biridir. Default olarak false gelir ve true olması tavsiye edilir. Bu parametreyi bir örnekle açıklayalım. Çok datadan rapor elde ediyorsunuz ve rapor almak yaklaşık 8 dk sürüyor ve siz dördüncü dereceden parallellikle çalıştırıp süreyi 2 dk ya düşürüyorsunuz. Sonra dereceyi biraz daha artırıp süreyi daha fazla düşürmek istiyorsunuz ama server kaynakları yeterli olmadıgından rapor işlemini gerçekleştiremeyebilirsiniz işte Oracle bunun çözümü için bu adaptive_multi_user ‘I getirdi. Eger server meşgulse talep ettiğinizden daha düşük derecede çalışır parallel sorgularınız ve böylece overheadden kurtulmuş olursunuz.

parallel_max_servers and parallel_min_servers

User process ile PX ler pooldan çağırılır bu iki parametre ile pool’un size’ını ayarlayabilirsiniz. Yine kritik olacak maksimum değeri olacaktır. Bu değeri yüksek verip bir cpu da yüzlerce process çalıştırmanın anlamı yoktur. Maksimum değeri cpu sayısının iki ya da dört katı verilebilinir ama sürekli üstünde durduğumuz gibi tüm etkenler üstünde düşünülmelidir.

parallel_execution_message_size

Bu parametre ile slave processler ile coordinator arasındaki mesajların geçmesindeki buffer miktarı ayarlanır. Defaultda 2 KB gelir. Eger mesajlar değerden büyükse parçalara ayrılır bu az da olsa etkiler performansı. parallel_automatic_tuning tarafında mesajlar 2kb dan 8kb ye kadar çıkarılabilinir.

Bu parametreler ayarlamak dışında dışında data dictionary viewden de processler hakkında kolayca bilgi alabiliriz. Aslında birçok uzmanda bu parametrelerin farkında değildir. Şimdi bazılarına göz atalım.

V$PQ_SESSTAT

Sorgumuzun parallelliğini görmmemiz açısından faydalı olacaktır ayrıca mesaj aktivitelerini de görebiliriz.

SELECT * FROM v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
—————————— ———- ————-
Queries Parallelized 1 2
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 2
Server Threads 7 0
Allocation Height 7 0
Allocation Width 1 0
Local Msgs Sent 491 983
Distr Msgs Sent 0 0
Local Msgs Recv’d 491 983
Distr Msgs Recv’d 0 0

Kullanılan px processlerin sayısını ve durumunu görmemiz açısında faydalı olacaktır. Aynı zamanda parallel_max_servers ve parallel_min_servers parametrelerini ayarlamak açısıdan yararlı olacaktır.Unix de |ps -ef|grep “ora_p” da aynı sonucu döndürecektir.

select
statistic,
value
from
v$pq_sysstat
where
statistic = ‘Servers Busy’;

STATISTIC VALUE
——— —–
Servers Busy 30

Monitoring the SQL being executed by slaves

Slave processlerin hangi sql li çalıştırdığını da viewleri joinleyerek bakabiliriz. Burda processler aktif olmak zorundadır.

select p.server_name,
sql.sql_text
from v$px_process p, v$sql sql, v$session s
WHERE p.sid = s.sid
and p.serial# = s.serial#
and s.sql_address = sql.address
and s.sql_hash_value = sql.hash_value

P000 SELECT /*+ PARALLEL (attendance, 2) */ * FROM attendance ORD
ER BY amount_paid

P003 SELECT /*+ PARALLEL (attendance, 2) */ * FROM attendance ORD
ER BY amount_paid

P002 SELECT /*+ PARALLEL (attendance, 2) */ * FROM attendance ORD
ER BY amount_paid

P001 SELECT /*+ PARALLEL (attendance, 2) */ * FROM attendance ORD
ER BY amount_paid

Session Tracing and Wait Events

Parallel execution kullanan bir uygulamayı trace etmek kullanmayana gore daha birkaç yönüyle daha karmaşıktır. Trace file her slave process için üretilir. Versiyona gore değişse de slave processlerin trace dosyaları background_dump_dest de query coordinator trace leri ise user_dump_dest de oluşur. Processler arasında mesaj alışverişleri beklemelere yol açar. En büyük zorluklardan biri de çok sayıda analiz etmeniz gereken trace dosyası olmasıdır.

Parallel-specific Wait Events

Px leri monitor ederken high level da statspack (istatistik görebileceğiniz bir tool) low level da ise event trace kullanabilirsiniz.

Events indicating Consumers are waiting for data from Producers

Daha once bahsettiğimiz consumer’ın producer’i beklemesinden dolayı beklemeler aslında herhangi bir process’in çalışması için diğer processi beklemesinden farklı değildir. Ama bu beklemler çok fazla sürüyorsa bu bir problem göstergesidir. Aşağıda statpacks ile alınmış topten events var ona bakıp yorumlarda bulunalım.

Event Waits Timeouts Time (s) (ms) /txn
direct Path read 2,249,666 0 115,813 51 25.5
PX Deq: Execute Reply553,797 22,006 75,910 137 6.3
PX qref latch 77,461 39,676 42,257 546 0.9
library cache pin 27,877 10,404 31,422 1127 0.3
db file scattered read 1,048,135 0 25,144 24 11.9

Yukardaki değerlere yorumlarsak direk Path read slave processlerin fullscan ya da index full scan yaparken okuma şeklidir. Yukardaki istatistiğin datawarehouse da uzun sure alan bir select oldugu düşünülürse 51 ms çok uzun sure değildir. Execute Reply bir idle process olarak algılanır ve bize çok fakir vermez. PX qref latch değeri bize consumer’ın producer’I beklediğini ve consumerın işleyemeyeceği kadar data getirdiği fikrini verir. Bu işlem çok fazla süren batch işlemioldugu için büyük miktarda mesaj transferinin oldugu görülmektedir. Wait events’I düşürmek için parallel_execution_message_size ‘ düşürmek ya da DOP derecesini düşürmek çözüm olabilir. Bu durum bilinçsizce DOP u artırarak her zaman hızlanacağını düşünenler tarafında karşılaşılabilinir bir durumdur. Ama asıl yukardaki sorun
library cache pin daki değerdeki sorundur. Bu parametre kodun library cache load edilirken göterdiği bekleme değeridir ya da bir başka deyişle library cache concurrency ‘i yöneten değerdir ve shared pool’un yogun kullanıldığı durumlarda yüksek değerde bir değer alacağınız kesindir.

Events indicating producers are quicker than consumers (or QC)

PX qref latch Consumer processin data beklemesinden oluşan beklelerdir. Bu durumda ise parallel_execution_message_size ‘ı artırmak iletişim overhead’ı azaltıp consemer’ın data beklemesini azaltıp haliyle toplam beklemyi de azaltacaktır.

Synchronisation Message Events

• PX Deq Credit: need buffer
• PX Deq: Signal Ack
• PX Deq: Join Ack

Yukardaki gibi QC nin ve slave processlerin haberleşmesinden kaynaklanan beklemelerle karşılaşabilirsiniz bu durumda DOP ve parallel_max_servers değerlerini düşürmeniz yararlı olacaktır.

Query Coordinator waiting for the slaves to parse their SQL statements

• PX Deq: Parse Reply SQL lin parse etmesinde gerçekleşen beklemerdir. Bu beklemeler library cache pin değerlerine de yansır.

Partial Message Event

• PX Deq: Msg Fragment bu event parallel_execution_message_size ‘ın düşük olmasından kaynaklanır. Mesajların processler arasında taşınırken büyük gelip bölümlere ayrılmasından kaynaklanabilir mesaj size’ı dşürüp tecrübe edilmesinde yarar vardır.

Şimdi de teknik alandan çıkıp genel resmi özetleyelim.

• Parallel execution kullanmadan once araştırmalı eger imkan varsa initial testler yapılmalıdır aksi halde büyük problemlerle karşılaşabilinir.
• Parallel Execution donanımı en yoğun kullanmak için tasarlanmıştır. Tek cpu, iki 512 MB lik disk le çok büyük performans artışı beklemek yanlış olacaktır.
• Parallel Execution kullanırken once sql tune edin edin kötü yazılmış ve tasarlanmış uygulamaya parallel execution uygulamaya kalkmayın.
• Kısa süren transactionlarda PX kullanmanız çok az iyileşmeye hatta daha yavaş çalışmaya mahkum olabilir. Kısa sorgu için tüm kaynakları kullanmış olursunuz.
• Parallel Executions geceyarıları batch operasyonları için ya da multiple streams joblarında genelde performans gösterir ama yine de bu uygulamanın türüne bağlıdır.
• Create as Select ya da büyük bulk insert into parallel olarak gerçekleştiriliyorsa NOLOGING modunda çalışırsa performans artar çünkü redo log’a yazdıgınız herşey extra I/O dur aslında ama recovery durumunda logları manuel olarak oluşturmanız gerekecektir.
• PARALLEL_AUTOMATIC_TUNING parametresini true yapın

Oracle Content Management (içerik yönetimi)

Content Management

Oracle relational modeli (structured data) desteklemesinin yanında complex structured (collections,types), semi-structured (Advence Queuing) object relational model tiplerini de desteklemektedir. Bugün daha çok unstructured data tiplerini üzerinde duracağız.

Günümüzde internetin de yaygınlasıyla video,foto gibi formatlarında kullanımı artmış ve veritabanı uygulamarının da vazgeçilmez ihtiyacı haline gelmiştir. Bunun için artık bizim örneklemede sürekli kullandığımız employees tablosunun içinde artık ilk employeenin fotosunu görmek isteyebilirsiniz Oracle unstructed datayı kendi native datası olarak görür ve yönetimi için toollar sağlar ve bütün tipler bir sql cümleciği kadar yakın ve hızlıdır.

XML in Oracle

XML DB xml’in depolanması,yönetilmesi ve sql ve xml modelinin sağlanması için oracle teknolojisidir. Ayrı bir server olarak düşünülmemesi gerekir. Xml Db xml’e native data type ‘ı gibi davranır. Database de xmltype olarak tutulur ve getClobVal() ile select çekebilirsiniz. Dönen değer clob değeridir ve pl/sql ile DBMS_LOB.freeTemporary() metodunda çağırabilirsiniz. Xmltype object tipidir yani tablo yaratabilirsiniz ve xml içeriği clob olarak tutulur ve çekilir. XML DB xml için sql ile ulaşılabilecek repository sağlar. Xml’e ftp,http gibi protokoller ve sql,pl/sql,java ile ulaşabilirsiniz

Overview of LOB Datatypes

BLOB, CLOB, NCLOB, and BFILE unstructed verinin binary ya da karakter olarak saklanmasını ve işlenmesini sağlar. BLOB veritipi veritabanında binary unstructured veriyi tutar. 8 terabyte binary veri tutar.CLOB character data set NCLOB da Unicode character setidir. BFİLE file sistemlerde binary veri tipidir .Read only dir .

Oracle text

Oracle text Oracle domain indeksleridir. Oracle Text, veritabanında saklanan metinlerin içeriklerinin indekslenmesi ve bu indekslerin üzerinden içerige dayalı gelişmiş sorgulamalar yapılmasına olanak verir. MS Office, Adobe PDF, HTML ve XML gibi 150 döküman tipini ve 40’tan fazla dili destekler. Oracle text veritabanına integredir ve oracle processi olarak çalışır. Oracle text web de ,database de , ya da dosyadaki dökümanları analiz,search,indeklerken standart sql kullanır. Oracle text kullanılırken textin kullanılacağı kolonun uygun indeks seçilerek indexlenir. Oracle text kullanmak CREATE INDEX yazıp tipini berlirtmek kadar kolaydır.Oracle text design ederken hangi tipte sorgu kullancağanız önemlidir. Böylece en uygun index’i seçmiş olursunuz .Çünkü her uygulama türü farklı indeks kullanır.
Index types

Standart arama da metin eşleme yapılır. Dökümanlar document table da saklanır ve html,pdf,doc gibi formatlarda olabilir. Bu tür dökümanlar CONTEXT index ile document table da saklanırlar. Sorgular kelime ya da phrases lerden oluşabilir. OR ve AND operatörleri kullanılabilinir. Sql ifadesi contains operatörü içerir. Cataloglama da ctxcat indeksi catsearch operatörü ile kullanılır.Mixed querylerde performance açısından daha iyidir. Context indekse gore daha fazla oluşturulurken ve kullanılırken daha fazla disk kullanımı gerçekleştirir. Ctxrule ise stream olarak gelen dökümanları daha önceden belirlenmiş kurallara gore sınıflayarak diğer tablolara

İntermedia

Intermedia Resim SesVideo gibi nesnesel objeleri depolayan yöneten Oracle uygun, etkin ve etkili relational model dışında blob,image,video gibi complex objelere destek vererek object relational modele destek veren teknolojisidir.. Oracle ORDAudio, ORDImage, ORDVideo gibi object typelarına destek vererek orta katmamın objeyi kullanamasını ve işlemesini destekler. Obje tiplerini create table olarak yarabilirsiniz yani varchar2 kolon yaratmaktan farkı yok. Media ortam verisi metadata objenin uzunluğu compression type vb Methots da objenin üzerinde gerçeleşebilcek procedürler getContent( ) and setProperties( ).

İntermedia objeleri ya internal blob olarak ya da url si tutularak http serverda external olarak tutulur.External olarak tutulması consistency açısından sakıncalıdır. Kullanıcı tarafından farklı lokasyonlara konulursa ulaşılamama durumuyla karşılalbilinir. SQL, PL/SQL, OCI, or Java ile multimedia objelerine ulaşabilir ve işleyebilirsiniz. Java servlets,jsp lerdeki classlarla intermedia objelerine ulaşabilirsiniz. Java ıntermedia classes jai databasede deki intermedia objelerinden ordimage I yazar ve okur.
Content-Based Retrieval

Içerik temelli sunum sadece image ler için geçerlidir. Içerik temeli sunum imaged-based bilgisini almada zamani azaltir. Büyük veritabanlarinda her update ve insertde gibi sorgularda image’in özelliklerini getirmek pratik degildir. Içerik temelli sunum objenin color,shape,texture gibi özellikleri getirmede pratiklik saglar.

Content base retrival resmin içerigi soyutlar ve daha compact halde veritabanina insert edilir. Sorgular bu abstraction üzerinden çalışır. Böylece resim analiz edilir resmi temsil eden vektörler olusturulur buna signature denir. Signature rengi ,sekli gibi image’in özelliklerini saklar. Signature’lar image’i renklere göre bölümlere ayırır ve her bir bölüm 3000 – 4000 arasında bytelardan oluşur. Daha iyi performace için index-based signature kullanılabilinir. Şekilde görüldüğü gibi signature

Bu indeksler ORDImageIndex olarak geçiyor. Her update ve insert işleminde işleminde inkdes kendini update eder. Default olarak image search işlemlerinde signature lar karşılaştırılır fakat bu linear search milyonlarca image ‘in olduğu veritabanlarında performance kaybı olarcaktır. Bunda dolayı Indeksler işimizi kolaylaştırcaktır.

Analytic Functions

Analitik Fonksiyonlar

ilk Oracle 8 ailesi tarafından traditional sql’in kümülatif toplamlar,ranking,lead,lag gibi analitik hesaplarındaki eksiliklerini kapatmak için sağlananan sql extensionudur. Data warehousing işlemlerinde çok sık kullanılmasının yanısıra OLAP raporlamada da önemli rol oynamaktadır. Yaklaşık 26 fonksiyon civarında olup AVG ,RANK,DENSE_RANK, FIRST_VALUE, LAST_VALUE,ROW_NUMBER STDDEV,VARIANCE en yaygın kullanılan fonksiyonlarından birkaçıdır. Analitik fonsiyonların faydaları performans artışı, yazım kolaylığı ve kolay anlaşılabilirliği olarak sıralanabilinir. Birazdan örneklerle faydalarını göreceğiz.

Sorgularımızı analiz ederken Tkprof kullancağız .Onun için Tkprof anlamaya çalışalım.Command line’a tkprof yazarak kullanımını görebiliriz. Tkprof sorgularımızı analiz ederken trace dosyalarımızı kullanıp daha okunaklı rapor almamızı sağlar. Show parameter user_dump_dest görebiliriz. Ben alter system set user_dump_dest=”d:sqltrace”; diyerek defaultda udump dosyasına atılacak trace dosyamın yerini değiştirdim. Alter session set sql_trace = true; komutuyla trace’i aktif hale getiriyorum fakat istatistik aldıktan sonra Alter session set events ‘10046 trace name context off‘; komutuyla off duruma getirilmelidir .Aksi halde dosyamız şişer . Alter session set timed_statistics = true; diyerek raporuma CPU değerlerini ekledim. Sorgu performansında bu değeri çok kullanacağım. Alter session set max_dump_file_size =unlimited; deyip trace ‘i limitsiz yaptım . Şu anda XE için çok kritik olmadığı için limitsiz yapıyorum ama normalde 50-100 MB yeterli bir miktar ve alter session set statistics_level = all ; deyip her level da rapor almak istedim.
Şimdi tekrar analitik fonksiyonlara dönelim. Analitik fonksiyon kıyaslamasında Scott’ın ünlü emp tablosu kullanılacak daha çok ename,sal ve deptno kolonlarını analiz edeceğiz. Emp tablosunda aynı departmanda en çok kazanan 3 kişi alacak Top-N sorgusu yazalım. Bunu bir analitik,bir de traditional sql ile yazıp performansları kıyaslayalı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 = analytics ;

alter session set events ‘10046 trace name context forever,all’;

select * from

(select deptno,ename,sal,dense_rank()

over(partition by deptno order by sal desc)

top_three from hr.emp )

where top_three <=3

order by deptno,sal desc;

Alter session set events ‘10046 trace name context off’;

Elapsed; 00:00:00.21

Dedikten sonra sqlrace dosyamda ora_exe_(SID)_analytics.trc isimli trace dosyam oluştu. Daha okunaklı hale gelitmek için command line’a D:\sqltrace>tkprof xe_ora_940_analytics.trc analytics.txt explain=sys/ sys=yes waits=yes sort=prscpu yazdıktan sonra aşağıdaki okunaklı şekilde istatistik alıyoruz.

DEPTNO ENAME SAL TOP_THREE

10 KING 5000 1

10 CLARK 2450 2

10 MILLER 1300 3

20 SCOTT 3000 1

20 FORD 3000 1

20 JONES 2975 2

20 ADAMS 1100 3

30 BLAKE 2850 1

30 ALLEN 1600 2

30 TURNER 1500 3

10 rows selected

call count cpu elapsed disk query current rows

Parse 1 0.03 0.14 2 2 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.02 0 7 0 10

total 4 0.03 0.17 2 9 0 10

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

——- —————————————————

10 VIEW (cr=7 pr=0 pw=0 time=28703 us)

14 WINDOW SORT PUSHED RANK (cr=7 pr=0 pw=0 time=28615 us)

14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=160 us)

Şimdi aynı çıktıyı sonucu analitik fonksiyon kullanmadan alacağım.

select deptno,ename,sal from hr.emp e1

where sal in (select sal from(

select distinct sal,deptno from hr.emp e3 order by deptno,sal desc) e2

where e2.deptno=e1.deptno and rownum <=3)

order by deptno,sal desc;

Elapsed:00:00:00.25

call count cpu elapsed disk query current rows

Parse 1 0.03 0.04 2 5 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.01 0.00 0 91 0 10

total 4 0.04 0.04 2 96 0 10

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

——- —————————————————

10 SORT ORDER BY (cr=91 pr=0 pw=0 time=4254 us)

10 FILTER (cr=91 pr=0 pw=0 time=4121 us)

14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=137 us)

9 FILTER (cr=84 pr=0 pw=0 time=3749 us)

27 COUNT STOPKEY (cr=84 pr=0 pw=0 time=3580 us)

27 VIEW (cr=84 pr=0 pw=0 time=3275 us)

27 SORT UNIQUE STOPKEY (cr=84 pr=0 pw=0 time=2930 us)

59 TABLE ACCESS FULL EMP (cr=84 pr=0 pw=0 time=916 us)

CPU I/O (Kaynak kullanımı disk+query)

Analytics

0.03

11

Yukarda çalıştırdığım iki sorgu da bana her departmanın en çok kazanan üç kişisini getirdi. İstatistikleri karşılaştırdığımda yukardaki gibi bir tabloyla karşılaştım. Analitik fonksiyon da 0.03 süresinde disk+query değerinin toplamı 11 I/O kaynak kullanımı gerçekleşirken analitik fonksiyon kullanmadığımda 0.04 süresince toplam 98 I/O kullanımı gerçekleşti. Bu da analitik fonksiyon kullanmadığımız durumlarda fazladan kaynak kullanımı,yani performans kaybı olduğunu gösterdi. Ayrıca ikinci sorgumda altsorgu her satır için çalıştı belki bu bizim 14 satırlık tablomuzda çok kritik gözükmemiş gibi olabilir ama milyonlarca satırdan oluşan tabloda çok önem kazanacağı ve yukardaki değerlerin arasında farkın artıp performans farkının daha artacağı kesin bir yargı olacaktır.

Şimdi bir örnek daha yapıp farkı tekrar görelim. Emp tablosunda her çalışanın adını ,maaşını,departmanın toplam maaşını alıp,çalışan maaşının departman ve toplam şirketteki maaşlarına gore yüzdesine bakalım.

select deptno,ename,sal,

sum(sal) over (partition by deptno order by sal,ename) cum_sal,

round(100*ratio_to_report(sal) over (partition by deptno),1) pct_dept,

round(100*ratio_to_report(sal) over(),1) pct_overall

from emp

order by deptno,sal

DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL

10 MILLER 1300 1300 14.9 4.5

10 CLARK 2450 3750 28 8.4

10 KING 5000 8750 57.1 17.2

20 SMITH 800 800 7.4 2.8

20 ADAMS 1100 1900 10.1 3.8

20 JONES 2975 4875 27.4 10.2

20 FORD 3000 7875 27.6 10.3

20 SCOTT 3000 10875 27.6 10.3

30 JAMES 950 950 10.1 3.3

30 MARTIN 1250 2200 13.3 4.3

30 WARD 1250 3450 13.3 4.3

30 TURNER 1500 4950 16 5.2

30 ALLEN 1600 6550 17 5.5

30 BLAKE 2850 9400 30.3 9.8

14 rows selected

Elapsed:00:00:00.97

call count cpu elapsed disk query current rows

Parse 1 0.01 0.36 2 2 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.03 0 3 0 14

total 4 0.01 0.40 2 5 0 14

Rows Row Source Operation

——- —————————————————

14 WINDOW SORT (cr=3 pr=0 pw=0 time=39283 us)

14 TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=660 us)

select emp.deptno,emp.ename,emp.sal,

sum(emp4.sal) cum_sal,

round(100*emp.sal/emp2.sal_by_dept,1) pct_dept,

round(100*emp.sal/emp3.sal_overall,1) pct_overall

from emp,

(select deptno, sum(sal) sal_by_dept from emp group by deptno) emp2,

(select sum(sal) sal_overall from emp )emp3,

emp emp4

where emp.deptno= emp2.deptno

and emp.deptno=emp4.deptno

and (emp.sal > emp4.sal or (emp.sal=emp4.sal and emp.ename >= emp4.ename))

group by emp.deptno,emp.ename,emp.sal,

round(100*emp.sal/emp2.sal_by_dept,1),

round(100*emp.sal/emp3.sal_overall,1)

order by deptno,sal

call count cpu elapsed disk query current rows

Parse 1 0.03 0.20 2 5 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.08 0 12 0 14

total 4 0.03 0.28 2 17 0 14

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

——- —————————————————

14 SORT GROUP BY (cr=12 pr=0 pw=0 time=81357 us)

42 HASH JOIN (cr=12 pr=0 pw=0 time=80646 us)

42 HASH JOIN (cr=9 pr=0 pw=0 time=2092 us)

14 NESTED LOOPS (cr=6 pr=0 pw=0 time=422 us)

1 VIEW (cr=3 pr=0 pw=0 time=235 us)

1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=219 us)

14 TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=145 us)

14 TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=84 us)

14 TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=90 us)

3 VIEW (cr=3 pr=0 pw=0 time=58457 us)

3 HASH GROUP BY (cr=3 pr=0 pw=0 time=58424 us)

14 TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=145 us)

Sonuçları kıyasladığımızda yine aynı yargılara ulaştık. Cpu değeri analitik de yine az çıktı ve I/O kullanımında fark yine açık. Ayrıca son örnekte yazım kolaylığı çok daha fazla belli oldu. İkinci sorguda self-join yapıldı ve analitik sql’e göre zor ve kompleks bir sorgu yazıldı. İlk sorguda farklı olarak window sort yapıldığını gördük Örneklerden de çıkaracağımız gibi analitik fonksiyonlar bize kullanımı kolay ve performası yükselten olanaklar sunuyor. O zaman bizede ihtiyaca göre kullanmak düşüyor.

FUNCTİON_NAME(argument,argument)

OVER (<Partition-Clause<Order-by-Clause><Windowing Clause>)

Performansın yanısıra yukarda da kullanımı görüldüğü gibi Analitik fonksiyonun developer tarafından yazması ve anlaşılması çok kolay. Over() ifadesi gördüğümüzde analitik sql var diyoruz .Yaklaşık 26 tane olan fonksiyonları ihtiyacınız doğrultusunda kullanabilirsiniz.