DBMS_SQLTUNE paketi ile SQL uygulamalarının analizi
Oracle 11g sürümünden itibaren, DBMS_SQLTUNE paketi içinden SELECT_SQL_TRACE fonksiyonu çalıştırılmaktadır. Bu fonsiyonun amacı; SQL izleme dosyasının içeriğini bir SQL iyileştirme seti içerisine yüklemektir. Bu yazımın amacı SELECT_SQL_TRACE fonksiyonunu kullanarak SQL komutlarının içeriğinin SQL izleme dosyasından görülebilmesinin kullanımını ve faydalarını göstermektir.
SELECT_SQL_TRACE özelliği Oracle 11.1.0.7 sürümünden itibaren işlemdedir, yani bu sürüm öncesi Oracle 11g sürümlerinde çalışmaz. Kısaca DBMS_SQLTUNE.SELECT_SQL_TRACE fonsiyonu ile kullanılan parametrelerin kısaca anlamlarına ve nasıl kullanıldığına gelirsek;
dbms_sqltune.select_sql_trace(
directory => 'TRACE',
file_name => 'aysun_ora_21351.trc',
select_mode => 1 | 2 )
directory: SQL izleme dosyalarının yer aldığı fiziksel lokasyonu işaret eden sanal dizinin ismidir. CREATE DIRECTORY komutu ile oluşturulur.
file_name: SQL izleme setine yüklenecek olan SQL izleme dosyasının adı.
select_mode: 1 veya 2 integer değerinden birini alır. 1=> SINGLE_EXECUTION, yani tek bir SQL çalıştırmasını döndürür 2=>ALL_EXECUTIONS, yani tüm SQL çalıştırmalarını döndürür
İlk olarak senaryoyu belirleyelim.
Gerekli veritabanı objelerini oluşturup obje istatistiklerini topluyoruz. Bu amaçla 10,000 satırdan oluşan test1 adında bir tablo ile bu tablonun id kolonunu işaret eden primary key indeksini oluşturup, tablonun tüm satırlarını istatistiklemeye dahil etmek üzere tablo istatistiklerini topluyorum.
SQL> CREATE TABLE test1 2 AS 3 SELECT rownum AS id, dbms_value.string(‘U’,50) AS adres 4 FROM dual 5 CONNECT BY level <= 10000 6 ORDER BY dbms_random.value; SQL> ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id); SQL> BEGIN 2 dbms_stats.gather_table_stats( 3 ownname => hr, 4 tabname => 'test1', 5 estimate_percent => 100, 6 method_opt => 'for all columns size 1' 7 ); 8 END; 9 /
SQL izlemesini etkinleştiriyorum. Bu arada Oracle 11g itibariyle SQL izlemesinde devreye giren PLAN_STAT adlı parametreyide kullanıyorum. PLAN_STAT parametresi, satır kaynak istatistiklerinin hangi sıklıkta izleme dosyalarına yazıldığını belirtmeye yaramaktadır. Bu parametre 3 değer alabilmektedir. NEVER: çalıştırma planı hakkında hiç bir bilgi izleme dosyasına yazılmaz. FIRST_EXECUTION: Çalıştırma planı ile ilgili bilgi ilk çalıştırmadan sonra izleme dosyasına yazılır. ALL_EXECUTIONS: Her çalıştırma sonrasında çalıştırma planı ile ilgili bilgi izleme dosyasına yazılmaktadır. Bu önemli bir parameterdir, çünkü Oracle 10g’de bilhassa imleçleri uzun zaman açık tutan uygulamalar için, her bir imleç için çalıştırma planları hakkında bilgiye izleme dosyalarında rastlamak mümkün değildi.
SQL> execute dbms_monitor.session_trace_enable(binds => TRUE, plan_stat => 'ALL_EXECUTIONS')
Sistemde bir takım sorgular oluşturuyoruz. Bilhassa imleç işlemleri ile bu basit sorguları çalıştırıyorum.
SQL> EXECUTE :id := 5;
SQL> SELECT count(adres) FROM test1 WHERE id < :id;
COUNT(adres)
------------
4
SQL> EXECUTE :id := 850;
SQL> SELECT count(adres) FROM test1 WHERE id < :id;
COUNT(adres)
----------
849
SQL> SELECT count(adres) FROM test1 WHERE id < :id;
COUNT(adres)
----------
849
SQL> EXECUTE :id := 35;
SQL> SELECT count(adres) FROM test1 WHERE id < :id;
COUNT(adres)
----------
34
SQL> SELECT sum(id) FROM test1;
SUM(ID)
----------
50005000
SQL izlemesini devredışı bırakıyorum ve SQL izleme dosyasının adını alıyorum.
SQL> execute dbms_monitor.session_trace_disable SQL> SELECT value 2 FROM v$diag_info 3 WHERE name = 'Default Trace File'; VALUE -------------------------------------------------------------- /u00/app/oracle/diag/rdbms/aysun/aysun/trace/aysun_ora_21351.trc
SQL komutları arasından SQL dosyalarını okumak için bir dizin oluşturuyorum.
SQL> CREATE DIRECTORY trace AS '/u00/app/oracle/diag/rdbms/aysun/aysun/trace/';
Şimdi artık bir SQL izleme dosyası mevcuttur. Şimdi bu dosyanın içeriğini bazı basit sorgular ile nasıl okuyabileceğimizi ve ne tür birgileri dışarı çıkarabileceğimizi göreceğiz.
Bu kullanıcı tarafından çalıştırılan SQL komutlarının listesini tamamlanma süreleri ve çalıştırma sayıları ile alıyorum ve SYS kullanıcısı tarafından çalıştırılan recursive SQL komutlarını hariç tutuyorum.
SQL> SELECT sql_id, 2 sum(elapsed_time) AS elapsed_time, 3 sum(executions) AS executions, 4 round(sum(elapsed_time)/sum(executions)) AS elapsed_time_per_execution 5 FROM table(dbms_sqltune.select_sql_trace( 6 directory => 'TRACE', 7 file_name => 'aysun_ora_21351.trc', 8 select_mode => 2 9 )) t 10 WHERE parsing_schema_name = 'HR' 11 GROUP BY sql_id 12 ORDER BY elapsed_time DESC; SQL_ID ELAPSED_TIME EXECUTIONS ELAPSED_TIME_PER_EXECUTION ------------- ------------ ---------- -------------------------- aot15tfsat23u 249757 4 62439 3tgnxpwym1dcq 4200 1 4200
Belirli bir SQL komutunun SQL textini alıyoruz.
SQL> SELECT sql_text 2 FROM table(dbms_sqltune.select_sql_trace( 3 directory => 'TRACE', 4 file_name => 'aysun_ora_21351.trc', 5 select_mode => 1 6 )) t 7 WHERE sql_id = 'aot15tfsat23u'; SQL_TEXT --------------------------------------- SELECT count(adres) FROM t WHERE id < :id
Belirli bir SQL komutu hakkında daha fazla çalıştırma istatistiklerini alıyorum.
SQL> SELECT plan_hash_value, executions, fetches, elapsed_time, cpu_time, disk_reads, buffer_gets, rows_processed
2 FROM table(dbms_sqltune.select_sql_trace(
3 directory => 'TRACE',
4 file_name => 'aysun_ora_21351.trc',
5 select_mode => 2
6 )) t
7 WHERE sql_id = 'aot15tfsat23u'
8 ORDER BY elapsed_time DESC;
PLAN_HASH_VALUE EXECUTIONS FETCHES ELAPSED_TIME CPU_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
--------------- ---------- ---------- ------------ ---------- ---------- ----------- --------------
5294676952 1 2 129056 127981 731 992 1
5294676952 1 2 113667 112982 691 1434 1
5294676952 1 2 5993 6999 11 11 1
5294676952 1 2 1041 1000 0 21 1
Bind değişkenlerinin değerlerini alıyorum.
SQL> SELECT elapsed_time,
2 value(b).gettypename() AS type,
3 value(b).accessnumber() AS value
4 FROM table(dbms_sqltune.select_sql_trace(
5 directory => 'TRACE',
6 file_name => 'aysun_ora_21351.trc',
7 select_mode => 2
8 )) t,
9 table(bind_list) b
10 WHERE sql_id = 'aot15tfsat23u'
11 ORDER BY elapsed_time DESC;
ELAPSED_TIME TYPE VALUE
------------ ---------- -----
120543 SYS.NUMBER 990
112375 SYS.NUMBER 990
5891 SYS.NUMBER 10
1172 SYS.NUMBER 20
Herşeyin iyi gözükmesine rağmen DBMS_SQLTUNE.SELECT_SQL_TRACE kullanımı ile alınan sonuçta bir takım hatalarda yok değildir. Özellikle, çalıştırma planın hash değeri tüm çalıştırılan SQL komutları için aynı gözüktüğünden dolayı hash değerleri her zaman doğru listelenmeyebilir. Bunun sebebi Oracle 11g ile kullanılmaya başlanan “adaptive cursor sharing” özelliğindendir. Doğru hash değerleri, SQL izleme dosyalarına TKPROF ile bakıldığında ortaya çıkmaktadır. Aşağıda, TKPOF komutunun AGGREGATE parametresinin “NO” değeri ile çalıştırılması sonuçları yer almaktadır. Üçüncü çalıştırmanın(kırmızı olan) hash değerinin farklı olduğuna dikkat ediniz.
... SQL ID: aot15tfsat23u Plan Hash: 5170755809 SELECT count(adres) FROM t WHERE id < :id ... SQL ID: aot15tfsat23u Plan Hash: 5170755809 SELECT count(adres) FROM t WHERE id < :id ... SQL ID: aot15tfsat23u Plan Hash: 2966233522 SELECT count(pad) FROM t WHERE id < :id ... SQL ID: aot15tfsat23u Plan Hash: 5170755809 SELECT count(adres) FROM t WHERE id < :id ...
SELECT_SQL_TRACE fonksiyonu üzerinden alınabilecek diğer bir bilgi ise çalıştırma planıdır. Ne yazıkki, bunu direct olarak bu fonksiyon üzerinden ekstrakt etmek sakıncalıdır. Bunun sebebi format işleminin kendi başımıza yapılması gerekliliğidir. Daha kolayı ise, bir SQL iyileştirme seti oluşturarak bilginin bu SQL iyileştirme setine yüklenmesi ve ardından DBMS_XPLAN paketi ile bu SQL iyileştirme setinin içeriğinin gösterilmesidir. Aşağıdaki örnekte bu olay gösterilmektedir.
SQL> DECLARE
2 c sys_refcursor;
3 BEGIN
4 dbms_sqltune.create_sqlset('TEST');
5 OPEN c FOR
6 SELECT value(t)
7 FROM table(dbms_sqltune.select_sql_trace(
8 directory => 'TRACE',
9 file_name => 'aysun_ora_21351.trc',
10 select_mode => 2
11 )) t;
12 dbms_sqltune.load_sqlset('TEST', c);
13 CLOSE c;
14 END;
15 /
SQL> SELECT *
2 FROM table(dbms_xplan.display_sqlset(
3 sqlset_name => 'TEST',
4 sql_id => 'aot15tfsat23u'
5 ));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL Tuning Set Name: TEST
SQL Tuning Set Owner: HR
SQL_ID: aot15tfsat23u
SQL Text: SELECT count(adres) FROM t WHERE id < :id
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| UNKNOWN_OBJECT_90222 | 9045 | 11 |
| 3 | INDEX RANGE SCAN | UNKNOWN_OBJECT_90223 | | 2 |
-----------------------------------------------------------------------------
Bu durumda sadece tek bir çalıştırma planı görülmektedir. Her ne kadar yeni ve iyi bir özellik olsada bekleme olaylarının görülmemesinden dolayı profiler aracının yerine geçmesi mümkün olmamaktadır, ancak bekleme olaylarının gözlenmesinin gerekmediği durumlarda, okuma kolaylığı ve komutların kolayca çalıştırılıp sonuçların düzgün formatta alınmasından dolayı kullanımı elverişli olacaktır.
Uğur İnal tarafından yayınlandı
