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.