DBMS_XPLAN paketi kullanarak çalıştırma planlarını farklı fonksiyonlar ile görüntülemek

DBMS_XPLAN paketi EXPLAIN_PLAIN komutu çıktılarını bir çok önceden tanımlanmış formatta görüntülemek için kullanılan oldukça kullanışlı bir PL/SQL paketidir. Ayrıca DBMS_XPLAN paketi kullanılarak bir SQL iyileştirme seti içinde veya AWR snapshotları içinde bulunan SQL komutlarının görüntülenmesinde de kullanılabilir.Bunun ilerisinde,  V$SQL_PLAN ve V$SQL_PLAN_STATISTICS_ALL görünümleri içerisinde saklanan bilgilere dayalı olarak, önbelleklenmiş SQL imleçleri içinde çalıştırma zamanı istatistiklerini ve SQL çalıştırma planını görüntülemeye yaramaktadır. Oracle 11.2 sürümünden itibaren iki farklı SQL planının farklılıları kolayca kıyaslanabilmekte, AWR snapshotu içinde hash id değerlerine göre farklı SQL planlarıda kıyaslayabilmektedir. Sonuçta, bir SQL planından çalıştırma ve açıklama planlarını görüntüleme imkanı veren bir pakettir.

DBMS_XPLAN paketi 10 tablo fonksiyonu sağlamaktadır, son 5 fonksiyon Oracle 11.2 sürümünden itibaren hizmettedir.

• DISPLAY - Bir plan tablosunun içeriğini gösterir ve formatlar.
• DISPLAY_AWR - AWR içerisinde saklanan SQL komutunun çalışma planı içeriklerini listeler ve formatlar.
• DISPLAY_CURSOR - Herhangi yüklenmiş bir imlecin(cursor) çalıştırma planı içeriklerini görüntüler ve formatlar.
• DISPLAY_SQL_PLAN_BASELINE - Eldeki SQL planınca tanımlanan SQL komutu için bir veya daha fazla açıklama planını görüntüler.
• DISPLAY_SQLSET - SQL iyileştirme seti içinde saklanan komutların çalıştırma planı içeriklerini görüntüler ve formatlar.
• DIFF_PLAN_CURSOR - Girilen child imleç numarasından türetilen iki SQL planının farklılıklarını kıyaslar.
• DIFF_PLAN_AWR - Girilen farklı hash id değerlerince tanımlanan iki SQL planının farklılıklarını kıyaslar.
• DIFF_PLAN - İki SQL planının referans planını kıyaslar.
• DIFF_PLAN_SQL_BASELINE - plan_names parametresi vasıtasıyla belirtilen 2 SQL planını kıyaslar.
• DIFF_PLAN_OUTLINE - Girilen özet tarafından oluşturulan iki SQL planını kıyaslar. 

DBMS_XPLAN.DISPLAY fonksiyonunu kullanarak bir plan tablosunu görüntülemek

Bu DBMS_XPLAN fonksiyonu bir plan tablosunun içeriğini görüntüler.İlave olarak, bu fonsiyonu kullanarak, istatistikler eklenirse V$SQL_PLAN_STATISTICS_ALL görünümü, eğer istatistikler eklenmezse plan tablosunun kolonları ile aynı şekilde adlandırılmış tablonun kolonlarını mümkün oldukça uzun süre bu tablo içinde saklayan herhangi bir planı görüntülemeye yaramaktadır. Görüntülemek üzere belirlenen tablo üzerindeki plan satırlarını seçmek için bir dayanak uygulanabilmektedir. 

DBMS_XPLAN.DISPLAY(

   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',

   statement_id  IN  VARCHAR2  DEFAULT  NULL,

   format        IN  VARCHAR2  DEFAULT 'TYPICAL',

   filter_preds  IN  VARCHAR2 DEFAULT NULL);

Yukardaki “format” parametresi plan için detay seviyesini kontrol etmektedir. Aşağıdaki 4 değerden birisini alır;

BASIC: Plan içindeki en az bilgiyi gösterir (işlem ID si, işlem adı ve seçenekleri).

TYPICAL: Varsayılan ayar olarak plan içindeki en uygun bilgileri gösterir( işlem ID si, adı ve seçenekleri, satır sayısı, bytes değerleri ve optimizer maliyeti) Budama, paralel ve dayanak bilgileri sadece uygun olduğunda görüntülenmektedir. Sadece PROJECTION, ALIAS ve REMOTE bilgilerini hariç tutar.

SERIAL: TYPICAL gibidir, sadece paralel bilgisi görüntülenmez, hatta plan paralel modda çalıştırılsa dahi.

ALL: Maksimum kullanıcı seviyesidir. TYPICAL seviyesinde ilave bilgileride (PROJECTION, ALIAS ve işlem dağıtıksa REMOTE SQL hakkında bilgi) içeren bilgiyi görüntüler.

Ekran çıktısında daha iyi kontrol için, aşağıdaki anahtar kelimeler varsayılan işlemi özelleştirmek amacıyla yukardaki 3 standart format içine(BASIC dışındakiler) eklenebilmektedir. Her bir anahtar kelime ya plan tablo kolonlarının mantıksal bir grubunu(PARTITION gibi) temsil etmekte, yada temel plan tablo çıktısına mantıksal ilaveler(PREDICATE gibi) yapmaktadır . Format anahtar kelimeler boşluk veya virgül ile birbirinden ayrılmalıdır.

ROWS -  Eğer uygunsa optimizer tarafından tahmin edilen satır sayısını gösterir.

BYTES - Eğer uygunsa optimizer tarafından tahmin edilen bytes değerini gösterir.

COST - Eğer uygunsa optimizer maliyet bilgisini gösterir.

PARTITION - Eğer uygunsa bölümlendirme(partition) budama bilgisini gösterir.

PARALLEL - Eğer uygunsa PX bilgisini gösterir(dağıtım metodu ve tablo kuyruk bilgisi).

PREDICATE - Eğer uygunsa dayanak bölümünü gösterir.

PROJECTION - Eğer uygunsa proje bölümünü gösterir.

ALIAS - Eğer uygunsa Sorgu Blok İsmi / Obje Alias bölümünü gösterir.

REMOTE - Eğer uygunsa dağıtık bir sorgunun bilgisini gösterir( mesela, uzak SQL ve seri dağıtımdan uzak)

NOTE - Eğer uygunsa açıklama planının not bölümünü gösterir.

Format anahtar kelimelerinin başına ‘-‘ işareti eklenerek belirtilen bilgi bu plandan hariç tutulabilir. Örneğin,  '-PARALLEL'  kullanımı ile PX bilgisi hariç tutulur.

Eğer hedef plan tablosu ayrıca plan istatistik kolonlarınıda saklıyorsa, DISPLAY fonsiyonu kullanılırken hangi istatistik sınıfının görüntüleceğini belirtmek için ilave format anahtar kelimelerikullanılabilir. Bu ilave format anahtar kelimeleri IOSTATS, MEMSTATS, ALLSTATS ve LAST dır.

Bir SELECT komutu üzerinde açıklama plan komutunu çalıştırıyoruz;

EXPLAIN PLAN

SET STATEMENT_ID = 'test1' FOR

SELECT * FROM employees e, deparments d

   WHERE e.department_id = d.department_id

   AND d.department_name='Treasury';

DBMS_XPLAN.DISPLAY fonksiyonu kullanılarak TYPICAL formatta planın görüntülenmesi;

SET LINESIZE 120

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY(‘PLAN_NAME’,’test1’));

Yukardaki DISPLAY fonsiyonunu içeren komut ile aşağıdaki çıktı alınır.

Plan hash value: 2052257371

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |              |     1 |  1530 |   7 (15)| 00:00:01 |

|*  1 |  HASH JOIN         |              |     1 |  1530 |   7 (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| DEPARTMENTS  |     1 |    20 |   7  (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| EMPLOYEES    |     4 | 14231 |   7  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

   2 - filter("D"."DEPARTMENT_NAME"='Treasury')

Note

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

 - dynamic sampling used for this statement

20 rows selected.

DBMS_XPLAN.DISPLAY_CURSOR fonksiyonu ile imleç çalıştırma planını görüntülemek

Varsayılan olarak DISPLAY_CURSOR fonksiyonu oturum tarafından en son çalıştırılan SQL komutunun çalıştırma planını formatlamaktadır.

SELECT e.last_name, d.department_name

FROM  employees e, departments d

WHERE e.department_id = d.department_id 

AND   e.employee_id=155;

Bu oturum tarafından en son çalıştırılan komutun çalıştırma planını görüntülemek için;

SET PAGESIZE 0

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

Bu sorgu sonucunda aşağıdaki çıktı oluşturulur;

DISPLAY_CURSOR fonksiyonu ile ayrıca imleç önbelleği içerisinde saklanan herhangi bir yüklenmiş imleç için çalıştırma planıda görüntülenebilir.Bu durumda, bu fonksiyona bir child imleç referans sağlanmalıdır. Bu da komutun SQL ID değeri ve opsiyonel olarak child numarasıdır.

Aşağıdaki senaryoda aynı SQL_ID nin iki farklı child imleç oluşturacağız(yani aynı SQL komutunu farklı optimizer parametreleri ile çalıştıracağız). Ardından her iki imlecin çalıştırma planını görüntüleceğiz ve burada farklılık belirginleşecek. SQL_ID değerini bulabilmek için komut içine UGUR adında bir yorum ekliyorum.

SELECT /* ugur */ e.last_name,d.department_name

FROM employees e,departments d

WHERE e.department_id=d.department_id

AND e.employee_id > 10;

Ardından oturumun optimizer_mode başlangıç parametresinde FIRST_ROWS değerini 10 satır ile sınırlandırıyorum.

ALTER SESSION SET OPTIMIZER_MODE=’FIRST_ROWS_10’;

Ardından bu SQL komutunun SQL_ID ve CHILD_NUMBER değerlerini V$SQL görünümünden buluyoruz.

SELECT sql_id, child_number

FROM v$sql

WHERE sql_text LIKE '%ugur%';

SQL_ID         CHILD_NUMBER

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

d8jvh9k2346pd   0

d8jvh9k2346pd   1

 0 numaralı child imleç için çalıştırma planını görüntülüyoruz.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('d8jvh9k2346pd',0));

27 rows selected

Ardından 1 numaralı child imleç için çalıştırma planını görüntülüyoruz.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('d8jvh9k2346pd',1));

30 rows selected

Her iki imleç için ayrı ayrı sorgu almak yerine her iki imlecin çalıştırma planlarını tek bir seferde görüntülemek için  SQL_ID ve CHILD_NUMBER değerleri alınarak aşağıdaki gibi çalıştırılabilir.

SELECT t.*

FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%UGUR%';

Plan tablosunu paralel bilgi ile görüntüleme

Varsayılan olarak, sadece ilgili bilgiler DISPLAY ve DISPLAY_CURSOR fonsiyonlarınca raporlanmaktadır.  Yukardaki örnekte paralel çalıştırma işlemi yoktu, böylece paralellik planı ile ilişkili bilgi yer almaz. Aşağıdaki örnekte görüldüğü gibi paralel bilgi padece sorgu paralel modda çalıştırıldığında raporlanır.

ALTER TABLE employees PARALLEL;

EXPLAIN PLAN for

SELECT * FROM employees e, departments d

WHERE e.department_id = d.department_id

AND e.employee_id =155

ORDER BY e.employee_id;

Display the plan using the DBMS_XPLAN.DISPLAY table function

SET LINESIZE 130

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Sorgu paralel olduğunda, paralelizm ile ilişkili bilgiler tablo kuyruk numarası(TQ kolon), tablo kuyruk tipi(INOUT) ve tablo kuyruk dağıtım metodu(PQ Distrib) dur.

Varsayılan olarak, eğer plan tablosu içindeki bazı planlar DISPLAY fonksiyonuna geçen statement_id parametresi ile eşleşirse, sadece son EXPLAIN_PLAN komutuyla ilişkili plan görüntülenir. Böylece, bir plan tablosunu her EXPLAIN_PLAN sonrasında tasfiye etmeye gerek kalmaz. Oysa, plan tablosunu belirli aralıklarla manuel boşaltarak, DISPLAY fonsiyonunun çalıştırması esnasında iyi bir performans devamlılığı sağlanmalıdır. Eğer herhangi bir plan tablosu oluşturulmadıysa, Oracle bireysel kullanıcılar için herhangi bir plan bilgisini saklamak için global bir geçici tablo kullanacak ve kendi içeriğini oturumun ömrü boyunca muhafaza edecektir.

Kolay kullanım için, DISPLAY fonsiyonunun en üstüne bir görünüm tanımlanabilir ve sonra EXPLAIN PLAN komut çıktısını almak için bu görünüm kullanılabilir.

Görünüm kullanarak en son açıklama planını görüntülemek

CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT * FROM PLAN;

DISPLAY_AWR Fonksiyonu

Bu fonsiyon AWR içinde saklanan çalıştırma planı içeriklerini görüntüler.

DBMS_XPLAN.DISPLAY_AWR(

   sql_id            IN      VARCHAR2,

   plan_hash_value   IN      NUMBER DEFAULT NULL,

   db_id             IN      NUMBER DEFAULT NULL,

   format            IN      VARCHAR2 DEFAULT TYPICAL

'atfwcg8anrykp' SQL ID si ile ilişkili farklı çalıştırma planını listelemek:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));

AWR snapshotu içinden ‘UGUR’ kelimesi içeren SQL komutlarını görüntülemek.

SELECT tf.*

FROM DBA_HIST_SQLTEXT ht, table

(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null,  'ALL' )) tf

WHERE ht.sql_text like '%ugur%';

DISPLAY_SQL_PLAN_BASELINE Fonksiyonu

Bu fonksiyon SQL plan baseline’nın belirlenen SQL işlem, için bir veya daha fazla çalıştırma planını görüntüler. Bu prosedür, planları tanımlama ve görüntüleme için plan baseline içerisinde saklanan plan bilgisini kullanır. SQL yönetim temelinde saklanan plan_id değeri, oluşturulan planın plan_id değeri ile her zaman aynı olmayabilir.Bu ikisi arasındaki farklılık, bunun yeniden üretilebilemeyen bir plan olması demektir. Böyle bir plan geçersiz olarak kabul edilir ve SQL derlemesi esnasında optimizer tarafından es geçilir.

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (

   sql_handle      IN VARCHAR2 := NULL,

   plan_name       IN VARCHAR2 := NULL,

   format          IN VARCHAR2 := 'TYPICAL')

 RETURN dbms_xplan_type_table;

• TYPICAL formatta SQL işlemi 'SYS_SQL_b1d49f6074ab95af'  ile belirtilen SQL komutunun tüm planlarını görüntüleme

SET LINESIZE 150

SET PAGESIZE 2000

SELECT t.*

FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

'SYS_SQL_b1d49f6074ab95af')) t;

• TEST1 kelimesi içeren bir veya birden fazla SQL komutlarını BASIC formatta görüntüleme

SET LINESIZE 150

SET PAGESIZE 2000

SELECT t.*

FROM (SELECT DISTINCT sql_handle FROM dba_sql_plan_baselines

WHERE sql_text like '%HR2%') pb,

TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(pb.sql_handle, NULL,

'BASIC')) t;

DISPLAY_SQLSET Fonksiyonu

Bu fonksiyon bir SQL iyileştirme seti içinde saklanan verilen komutun çalıştırma planını görüntüler.

DBMS_XPLAN.DISPLAY_SQLSET(

   sqlset_name      IN  VARCHAR2,

   sql_id           IN  VARCHAR2,

   plan_hash_value  IN NUMBER := NULL,

   format           IN  VARCHAR2  := 'TYPICAL',

   sqlset_owner     IN  VARCHAR2  := NULL)

  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

SQL iyileştirme seti içinde 'atfwcg8anrykp' SQL ID sine sahip komutun tüm çalıştırma planlarını görüntüleme

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

      'OLTP_optimization_0405','qwp547xch5tsd'));

DIFF_PLAN_CURSOR Fonsiyonu

Oracle 11.2 sürümünden itibaren hizmete giren bu fonksiyon verilen imleç child numarasınca türetilmiş iki SQL planını kıyaslar.

SELECT sql_id, COUNT(*)
FROM v$sql
GROUP BY sql_id
HAVING COUNT(*) > 2;

SELECT child_number
FROM v$sql
WHERE sql_id = 'bhvyz9bgyrhb2';

set serveroutput on

DECLARE
 x VARCHAR2(13) := 'bhvyz9bgyrhb2';
 y NUMBER := 1;
 z NUMBER := 2;
 r VARCHAR2(4000);
BEGIN
  r := dbms_xplan.diff_plan_cursor(x, y, z);
  dbms_output.put_line(r);
END;
/

DIFF_PLAN_AWR Fonksiyonu

Verilen hash id değerlerince belirlenen iki SQL planını kıyaslar.

dbms_xplan.diff_plan_awr(
sql_id IN VARCHAR2,
plan_hash_value1 IN NUMBER,
plan_hash_value2 IN NUMBER)
RETURN VARCHAR2;

DIFF_PLAN Fonksiyonu

dbms_xplan.diff_plan(
sql_text  IN CLOB,
outline   IN CLOB,
user_name IN VARCHAR2 := NULL)
RETURN VARCHAR2;

DIFF_PLAN_SQL_BASELINE Fonksiyonu

dbms_xplan.diff_plan_sql_baseline(

baseline_plan_name1 IN VARCHAR2,

baseline_plan_name2 IN VARCHAR2)
RETURN VARCHAR2;