SQL komutları için delta değerlerinin izlenmesi

V$SQLAREA görünümü library cache içindeki imleçler ile ilişkili tüm alt imleçlerin istatistiklerini birlikte gruplandırır. Buna rağmen gruplandırma bazı işlemleri kolaylaştırırken, “library cache latch” için artan talep sebebiyle bu görünüme erişmek muhtemelen daha fazla kaynak tüketecektir. İstatistikler, sürekli alma(consistent gets) sayısı, diskten blok okumaları, çalıştırmalar, satır alıp getirmeler(fetches), sortlar, bellek kullanımı, yüklemeler, invalidationlar ve bazı diğer alt istatistikler için sağlanmaktadır. 

Oracle 9.2 sürümünden itibaren, bu görünüm tamamlanma süresi(elapsed time) ve CPU kullanımı bilgilerinide sağlamaktadır. Görünüm her 1 saniye ve 5 saniye arasında güncellenmektedir.

V$SQL görünümü tüm alt imleçleri gruplandırmadan benzer bilgiyi sağlamaktadır; böylece görünüme erişmek daha az kaynak tüketmekle beraber, ayrıca “adaptive cursor sharing(uyumlu imleç paylaşımı)”, bağlaç(bind) değişken tanımı/değeri uzunluk değişimleri, değişik optimizer parametreleri çalıştıran oturumlar, istatistik toplama gibi SQL komutu  için “değişken” çalıştırma planına sahip türlü alt imleçler için istatistikleride görmeye imkan vermektedir. Bu görünümde her 1 saniye ve 5 saniye arasında değişmektedir. 

Oracle 10.2 sürümü ile gelen V$SQLSTATS görünümü ise imleç istatistiklerini daha verimli bir metot olarak izlemek için SQL_ID ve  PLAN_HASH_VALUE kolonlarını birincil anahtar kolonlar olarak kullanmaktadır. Bu görünümde her 1 saniye ve 5 saniye arasında değişmektedir. 

V$SESSION_LONGOPS görünümü,  çalıştırma için sorumlu olan oturumun SID ve SERIAL# değerlerini, işlemin tamamlanma süresini, işlemin tamamlanması için tahmini geri kalan süresi ile o an çalışan çalıştırma planının satırını getirerek en azından 6 saniye aktif olan operasyonlar için durum güncellemesi sağlamaktadır.

Oracle 11.1 ile gelen V$SQL_MONITOR görünümü(tuning pack lisansı gerektirmektedir) ile paralel çalıştırma işlevi etkin olan ve çalışan SQL komutları ile beraber, en azından 5 saniye süresince CPU süresi ve/veya  I/O süresi tüketen tüm SQL komutları otomatik olarak izlenmektedir. Aksi durumda, izleme /*+ MONITOR */  hinti kullanılarak etkinleştirilebilmektedir.  V$SQL_MONITOR içindeki istatistikler her bir saniyede bir güncellenmekte olup, SQL çalıştırması tamamlandıktan sonra en azından her bir dakika için görünüm içinde genellikle aynı kalmaktadır.

SQL çalıştırma istatistikleri için 10 dakikalık zamanı periyodu üzerinden SQL istatistik delta değerlerini yakalayan ve en azından 10 saniyesi tamamlanmış çalıştırma süresindeki SQL komutlarının ilgili istatistiklerini toplayan scriptler boyunca kayıt günlüğü tablolarını oluşturan örnek bir script aşağıda yer almaktadır:

SQL> CREATE GLOBAL TEMPORARY TABLE

DATALOG.TS_SQLSTATS ON COMMIT DELETE ROWS AS

SELECT

SQL_ID, PLAN_HASH_VALUE, LAST_ACTIVE_TIME, CPU_TIME, ELAPSED_TIME,AVG_HARD_PARSE_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME,CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME,PARSE_CALLS, LOADS, VERSION_COUNT, INVALIDATIONS, EXECUTIONS, FETCHES,

ROWS_PROCESSED, BUFFER_GETS, DISK_READS, DIRECT_WRITES, SORTS, SHARABLE_MEM,TOTAL_SHARABLE_MEM

FROM V$SQLSTATS

WHERE 0=1;

SQL> CREATE GLOBAL TEMPORARY TABLE

DATALOG.TE_SQLSTATS ON COMMIT DELETE ROWS AS

SELECT

SQL_ID, PLAN_HASH_VALUE, LAST_ACTIVE_TIME, CPU_TIME, ELAPSED_TIME,

AVG_HARD_PARSE_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME,

CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, PARSE_CALLS, LOADS, VERSION_COUNT, INVALIDATIONS, EXECUTIONS, FETCHES, ROWS_PROCESSED, BUFFER_GETS, DISK_READS, DIRECT_WRITES, SORTS, SHARABLE_MEM, TOTAL_SHARABLE_MEM

FROM V$SQLSTATS

WHERE 0=1;

SQL> COMMIT;

SQL> DEFINE CAPTURE_SECONDS=600

SQL> INSERT INTO

DATALOG.TS_SQLSTATS

SELECT

SQL_ID, PLAN_HASH_VALUE, LAST_ACTIVE_TIME, CPU_TIME, ELAPSED_TIME,

AVG_HARD_PARSE_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME,

CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, PARSE_CALLS, LOADS, VERSION_COUNT, INVALIDATIONS, EXECUTIONS, FETCHES, ROWS_PROCESSED, BUFFER_GETS, DISK_READS, DIRECT_WRITES, SORTS, SHARABLE_MEM,TOTAL_SHARABLE_MEM

FROM V$SQLSTATS;

SQL> EXEC DBMS_LOCK.SLEEP(&&CAPTURE_SECONDS);

SQL> INSERT INTO

DATALOG.TE_SQLSTATS

SELECT

SQL_ID, PLAN_HASH_VALUE, LAST_ACTIVE_TIME, CPU_TIME, ELAPSED_TIME,

AVG_HARD_PARSE_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME,

CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME,PARSE_CALLS, LOADS, VERSION_COUNT, INVALIDATIONS, EXECUTIONS, FETCHES,ROWS_PROCESSED, BUFFER_GETS, DISK_READS, DIRECT_WRITES, SORTS, SHARABLE_MEM,TOTAL_SHARABLE_MEM

FROM V$SQLSTATS;

SQL> COLUMN CPU_S FORMAT 9,990.00

SQL> COLUMN ELA_S FORMAT 9,990.00

SQL> COLUMN APP_S FORMAT 9,990.00

SQL> COLUMN CON_S FORMAT 9,990.00

SQL> COLUMN CLU_S FORMAT 9,990.00

SQL> COLUMN USR_S FORMAT 9,990.00

SQL> SET LINESIZE 180

SQL> SET PAGESIZE 200

SQL> SPOOL SQLSTATS.TXT

SQL> SELECT

TE.SQL_ID, TE.PLAN_HASH_VALUE PHV,(TE.CPU_TIME-NVL(TS.CPU_TIME,0))/1000000 CPU_S,(TE.ELAPSED_TIME-NVL(TS.ELAPSED_TIME,0))/1000000 ELA_S, TE.PARSE_CALLS-NVL(TS.PARSE_CALLS,0) PAR_C,TE.EXECUTIONS-NVL(TS.EXECUTIONS,0) EXEC,

(TE.APPLICATION_WAIT_TIME-NVL(TS.APPLICATION_WAIT_TIME,0))/1000000 APP_S,(TE.CONCURRENCY_WAIT_TIME-NVL(TS.CONCURRENCY_WAIT_TIME,0)) /1000000 CON_S,(TE.CLUSTER_WAIT_TIME-NVL(TS.CLUSTER_WAIT_TIME,0)) /1000000 CLU_S,(TE.USER_IO_WAIT_TIME-NVL(TS.USER_IO_WAIT_TIME,0)) /1000000 USR_S

FROM

DATALOG.TS_SQLSTATS TS,DATALOG.TE_SQLSTATS TE

WHERE

TE.SQL_ID=TS.SQL_ID(+)

AND TE.PLAN_HASH_VALUE=TS.PLAN_HASH_VALUE(+)

AND (TE.ELAPSED_TIME-NVL(TS.ELAPSED_TIME,0))>=10000000

ORDER BY (TE.ELAPSED_TIME-NVL(TS.ELAPSED_TIME,0)) DESC;

SQL> SPOOL OFF

SQL_ID        PHV        CPU_S  ELA_S  PAR_C  EXEC APP_S CON_S CLU_S  USR_S

------------- ---------- ------ ------ ----- ----- ----- ----- ----- -----

4q9qq5mta9t07 1584794551 230.32 236.57 2983   2983 0.00  0.00  0.00   0.00

cnbgwf9x3mcqj 85458804    47.17  51.98 2983   2983 0.00  0.00  0.00   0.00

9bz3rn13xf68a 3894554182  28.04  30.30 3039   3039 0.00  0.00  0.00   0.00

0pb8dqzx301b4 1082151180  10.42  10.42    0  21533 0.00  0.00  0.00   0.00

İlk SQL komutunun istatistiği 10 dakikalık zaman periyodunda yaklaşık 4 dakikalık CPU zamanı tüketimiyle beraber 0.01 saniyeden daha az herhangi bir bekleme olayı kategorisinde kayıp olduğunu göstermektedir. Bu kadar yüksek CPU zamanının potansiyel olarak sebebi nedir?  2,983 defa SQL komutu çalıştırmasının her biri ya sert çözümleme(hard parse) ya da yumuşak çözümleme(soft parse) ihtiyacı doğurmuştur. Çözümleme potensiyel olarak zaman tüketirken, CPU tüketiminin kaynağını belirlemek için daha fazla bilgiye gerek duyulmuştur.  Sonuçta, 10 dakikalık yakalama periyodu süresince bir SQL komutu için değişen planın olayında PLAN_HASH_VALUE değeri aşağıdaki sorgu için çıktıyı sınırlandırmada kullanılmaz:

SQL> SELECT

TE.SQL_ID,

TE.PLAN_HASH_VALUE PHV,

TE.EXECUTIONS-NVL(TS.EXECUTIONS,0) EXECUTIONS,

TE.FETCHES-NVL(TS.FETCHES,0) FETCHES,

TE.ROWS_PROCESSED-NVL(TS.ROWS_PROCESSED,0) ROW_PRO,

TE.BUFFER_GETS-NVL(TS.BUFFER_GETS,0) BUFFER_GETS,

TE.DISK_READS-NVL(TE.DISK_READS,0) DISK_READS

FROM

DATALOG.TS_SQLSTATS TS,

DATALOG.TE_SQLSTATS TE

WHERE

TE.SQL_ID=TS.SQL_ID(+)

AND TE.PLAN_HASH_VALUE=TS.PLAN_HASH_VALUE(+)

AND TE.SQL_ID IN

('4q9qq5mta9t07','cnbgwf9x3mcqj','9bz3rn13xf68a','0pb8dqzx301b4')

ORDER BY TE.BUFFER_GETS DESC;

SQL_ID        PHV         EXECUTIONS FETCHES ROW_PRO BUFFER_GETS DISK_READS

------------- ----------  ---------  ------- ------- ----------- ----------

4q9qq5mta9t07 1584794551  2,983      2,983   9,703   21,355,893 0

cnbgwf9x3mcqj 85458804    2,983      2,983   16,459   4,755,669  0

9bz3rn13xf68a 3894554182  3,039      3,039   10,265   1,061,466  0

0pb8dqzx301b4 1082151180 21,533          0   21,533     130,799  0

4q9qq5mta9t07 SQL_ID sine sahip SQL komutu  2,983 defa çalıştırılmış, çalıştırma çağrısı olarak aynı sayıda satır getirme(fetches) olmuş ve ortalama olarak her bir satır getirme yaklaşık 3.25 satır(ROWS_PROCESSED / FETCHES) döndürmüştür. SQL komutu için CPU tüketiminin muhtemel kaynağı, her bir alınıp getirilen satır için 2.201 “consistent gets” ortalamasına sahip 21.355 milyon “consistent gets”  işlemi olabillir. SQL komutunun ve planının incelenmesi, alınıp getirilen satır başına anlamlı sayıda “consistent gets” lerin beklenip beklenmediğinin belirlenmesinde de yardımcı olabilecektir. SQL_ID ve PLAN_HASH_VALUE  kolonları kullanılarak V$SQL görünümünüde sorgulanarak, SQL komutu, tüm alt imleç sayıları(CHILD_NUMBER) ve SQL komutunu başlangıçta çözümleyen kullanıcı bir bütün olarak belirlenebilmektedir.