2. SQL iyileştirme görevinin(task) oluşturulması
İyileştirilme görevleri tek bir SQL komutunun textinden, birden fazla komutu barındıran bir SQL setinden yada paylaşımlı havuzdaki veya AWR raporundaki bir SQL komutununun SQL ID değeri seçilerek oluşturulabilir.
Bununla beraber standart bir kullanıcının iyileştirme görevi oluşturabilmesi için; önce ADVISOR hakkına sahip olması ve ardından ilgili kullanıcının şema objeleri üzerinde bu fonsiyonun çalıştırılması gerekmektedir.
Aşağıda SQL iyileştirme görevini oluşturmak için kullanılan PL/SQL paketleri yer almaktadır.
- Bir SQL textinden, bind değişkenli yada bind değişkensiz SQL iyileştirme görevi oluşturmak;
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text IN CLOB,
bind_list IN sql_binds := NULL,
user_name IN VARCHAR2 := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
- Bir SQL textinden plan_hash_value değerine göre SQL iyileştirme görevi oluşturmak;
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
- Bir AWR raporundan ilgili snapshot aralığında SQL iyileştirme görevi oluşturmak;
DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
- Bir SQL setinden filtreleme şartlarına uygun SQL iyileştirme görevi oluşturmak;
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
rank1 IN VARCHAR2 := NULL,
rank2 IN VARCHAR2 := NULL,
rank3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := NULL,
result_limit IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL
plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',
sqlset_owner IN VARCHAR2 := NULL)
DBMS_SQLTUNE.CREATE_TUNING_TASK paketinde önemli olan bazı parametrelerin ne anlama geldiğine bakarsak;
bind_list: ANY DATA tipinde bind değişkenlerinin sıralı listesi(mesela 100 adlı bind değişkeni için => sql_binds(anydata.ConvertNumber(100))
plan_hash_value: SQL çalıştırma planının hash değeri
sqlset_name: Daha önceden oluşturulan SQL setinin adı
time_limit: Optimizer’ın derleme için harcayacağı saniye değerinden süre
basic_filter: SQL iyileştirme seti içinden kaynak kullanımı ile ilgili filtreleme yapabilmek için kullanılan filtre değeri veya değerleri
result_limit: Filtre sonucunda göre Top N sıralaması.
result_percentage: Toplam ölçüt değerininin yüzdesi (örneğin paylaşımlı alanının %5 ini kullanan SQL komutlarını bulmak gibi…)
scope: LIMITED veya SCOPE_COMPREHENSIVE değerini alır. Sınırlı yada daha kapsamlı durumlar için tercih edilir, LIMITED seçilirse SQL profil analizi es geçilir.
rank1-2-3: Oracle kaynak kullanım tercihleri(varsayılan “elapsed_time” değeridir, eğer
plan_filter: Aynı komut için birden fazla plan seçildiğinde kullanılan plan filtresidir (plan_hash_value) Aşağıdaki değerlerden birisini alır..
LAST_GENERATED: En güncel zaman mührüne sahip planFIRST_GENERATED: En eski zaman mührüne sahip planLAST_LOADED: En güncelfirst_load_timeistatistiği olan planFIRST_LOADED: En eski first_load istatistiğine sahip planMAX_ELAPSED_TIME: Maksimum tamamlanma süresine sahip planMAX_BUFFER_GETS: Maksmimum tampon alımına sahip planMAX_DISK_READS: Maksimum disk okumasına sahip planMAX_DIRECT_WRITES: Maksimum direkt yazma değerine sahip planMAX_OPTIMIZER_COST: Maksimum optimizer cost değerine sahip plan
Aşağıda bu paket ile ilgili her üç tip veri alım kriteri örnekleri yer almaktadır.
variable test_task VARCHAR2(80);
variable test_task2 VARCHAR2(80);
- SQL text formatından iyileştirme;
EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => ‘SELECT e.employee_id, d.department_name, e.salary, e.hire_date FROM employees e,departments d WHERE e.salary IN (SELECT AVG(salary) FROM employees GROUP BY department_id) AND e.department_id=d.department_id’,
user_name => ’HR’, task_name => ‘test_task’);
- SQL ID formatı(imleç önbelleğinden);
EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7c6hmwaywnha9', task_name => ‘test_task_bycache’);
- LIMITED scope içinde iyileştirme;
EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7c6hmwaywnha9', scope => 'LIMITED', task_name => ‘test_task_bycachescopelimited’);
- SQL komutunu iyileştirmek için derleme zamanına sadece 10 dakika verilmek istenirse;
EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7c6hmwaywnha9', time_limit => 600, task_name => ‘test_task_bycachetimelimited’);
- AWR içinden ilgili SQL ID numarasına göre iyileştirme;
EXEC :test_task2:= DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 102,
end_snap => 103, sql_id => 'lq5m4mtflgw9k', task_name => ‘test_task_byawr’);
- SQL iyileştirme seti kullanarak iyileştirme; Bu işlemden önce ilgili SQL seti yüklenmelidir. Aşağıdaki örnekte, “buffer_gets” sıralamasına göre bir saat süre boyunca test_sqlset setinden iyileştirme işlemi yer almaktadır.
EXEC :test_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name => 'test_sqlset',
rank1 => 'BUFFER_GETS',
time_limit => 3600,
task_name => ‘test_taskbysqlset’);
3. SQL iyileştirme görevinin çalıştırılması
SQL iyileştirme görevini oluşturduktan sonra bu görevin çalıştırılması gerekmektedir. Böylece SQL iyileştirme prosesi başlatılmış olur ve derleme zamanı işlemeye başlar. Bu derleme zamanı, görevi oluştururken verilen “time_limit” değerine bağlıdır. Bu süre sonunda derleme işlemi biter.
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘test_task’);
END;
/
Çalışmakta olan bir SQL iyileştirme görevinin durumunu kontrol etmek için USER_ADVISOR_TASKS veya DBA_ADVISOR_LOGS görünümlerine, görevin çalıştırılma sürecini kontrol etmek için ise V$SESSION_LONGOPS görünümüne sorgu çekilebilir. Aşağıdaki ilk sorgu, derlenmesi devam eden
SELECT SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE TASK_NAME = ‘test_task';
---------------------------------------
SELECT task_name, status
FROM dba_advisor_log
WHERE owner = 'HR';
TASK_NAME STATUS
--------------- -----------
test_task COMPLETED
1 row selected.
4. SQL iyileştirme görev sonuçlarının raporlanması
Bu adımda çalıştırılan SQL iyileştirme görevlerinin sonuçları rapor olarak alınmaktadır. Bu işlem için DBMS_SQLTUNE.REPORT_TUNING_TASK fonksiyonu çalıştırılmaktadır. Bu raporda SQL iyileştirme tavsiyesicisinin bulguları ve tavsiyeleri yer almaktadır. Text, HTML veya XML formatında rapor alınabilir ve analiz raporu tipik, temel ve kapsamlı(all) şeklinde olabilmektedir.
Hazırlanacak tavsiye raporunda, komut içinde kullanılan tablo birleştirmelerinde varsa eksik indeksler ve anahtarlar tavsiye olarak işaret edilmektedir, ayrıca ulaşılan bloklar ve objeler ile ilgili detaylı bir çalıştırma planı kıyaslaması yer almaktadır. Bununla beraber SQ komutunun yeniden yapılandırılması(sorguda kullanılan birleştirme türlerinin uygunluğu,doğru sürücü tablo(lar) seçimi işareti vb.), objeler üzerinde eksik istatistikler varsa bu eksik istatistiklerin toplanması ve SQL profil oluşturulması gibi tavsiyelerde bu raporda yer alır.
Aşağıda SQL iyileştirme setlerinin raporlanması için kullanılan sentaks yer almaktadır.
DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name IN VARCHAR2,
type IN VARCHAR2 := TEXT | HTML |XML ,
level IN VARCHAR2 := TYPICAL | BASIC | ALL ,
section IN VARCHAR2 := FINDING | PLAN | INFORMATION | ERROR | ALL ,
object_id IN NUMBER := NULL,
result_limit IN NUMBER := NULL -- rapor içinde bulunacak maksimum SQL komut sayısı-- )
Aşağıda bu örnekte kullandığım test_task adlı iyileştirme görevinin raporu yer almaktadır. LEVEL ve SECTION parametre değerlerini ALL olarak atıyorum.
SELECT DBMS_SQLTUNE.report_tuning_task('test _task', ‘TEXT’, ‘ALL’, ‘ALL’) FROM dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TASK','TEXT','ALL','ALL')
-----------------------------------------------------------------------
GENERAL INFORMATION SECTION
-----------------------------------------------------------------------
Tuning Task Name : test_task
Tuning Task Owner : HR
Tuning Task ID : 256
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 05/09/2011 15:44:49
Completed at : 05/09/2011 15:44:55
Number of Index Findings : 1
-----------------------------------------------------------------------
Schema Name: HR
SQL ID : 7c6hmwaywnha9
SQL Text : SELECT e.employee_id, d.department_name, e.salary, e.hire_date FROM employees e,departments d WHERE e.salary IN (SELECT AVG(salary) FROM employees GROUP BY department_id) AND
e.department_id=d.department_id
-----------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-----------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design or creating the recommended index.
- Create index HR.IDX$$_01000001 on HR.EMPLOYEES('SALARY');
Rationale
---------
Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.
-----------------------------------------------------------------------
EXPLAIN PLANS SECTION
-----------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3509108563
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 96 | 9 (23) | 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 96 | 9 (23) | 00:00:01 |
|* 2 | HASH JOIN SEMI | | 2 | 64 | 8 (25) | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0) | 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 1 | 13 | 4 (25) | 00:00:01 |
|* 5 | FILTER | | | |
| |
| 6 | HASH GROUP BY | | 1 | 7 | 4 (25) | 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0) | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0) | 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0) | 00:00:01 |
-----------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / E@SEL$1
4 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
5 - SEL$683B0107
7 - SEL$683B0107 / EMPLOYEES@SEL$2
8 - SEL$5DA710D3 / D@SEL$1
9 - SEL$5DA710D3 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."SALARY"="$nso_col_1")
5 - filter(AVG("SALARY")>0)
9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "E"."SALARY"[NUMBER,22], "E"."EMPLOYEE_ID"[NUMBER,22],
"E"."HIRE_DATE"[DATE,7], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
2 - (#keys=1) "E"."SALARY"[NUMBER,22], "E"."EMPLOYEE_ID"[NUMBER,22],
"E"."HIRE_DATE"[DATE,7], "E"."DEPARTMENT_ID"[NUMBER,22]
3 - "E"."EMPLOYEE_ID"[NUMBER,22], "E"."HIRE_DATE"[DATE,7],
"E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
4 - "$nso_col_1"[NUMBER,22]
5 - AVG("SALARY")[22]
6 - (#keys=1) "DEPARTMENT_ID"[NUMBER,22], AVG("SALARY")[22]
7 - "SALARY"[NUMBER,22], "DEPARTMENT_ID"[NUMBER,22]
8 - "D"."DEPARTMENT_NAME"[VARCHAR2,30]
9 - "D".ROWID[ROWID,10]
2- Using New Indices
--------------------
Plan hash value: 2906953147
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 96 | 7 (29)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 96 | 7 (29)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 64 | 6 (34)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 4 (25)| 00:00:01 |
| 4 | HASH UNIQUE | | 1 | 7 | 4 (25)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 1 | 7 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 38 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX$$_01000001 | 2 | | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
4 - SEL$683B0107
7 - SEL$683B0107 / EMPLOYEES@SEL$2
8 - SEL$5DA710D3 / E@SEL$1
9 - SEL$5DA710D3 / E@SEL$1
10 - SEL$5DA710D3 / D@SEL$1
11 - SEL$5DA710D3 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(AVG("SALARY")>0)
9 - access("E"."SALARY"="$nso_col_1")
11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "E"."EMPLOYEE_ID"[NUMBER,22], "E"."HIRE_DATE"[DATE,7],
"E"."SALARY"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
2 - (#keys=0) "E"."EMPLOYEE_ID"[NUMBER,22], "E"."HIRE_DATE"[DATE,7],
"E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
3 - "$nso_col_1"[NUMBER,22]
4 - (#keys=1) AVG("SALARY")[22]
5 - AVG("SALARY")[22]
6 - (#keys=1) "DEPARTMENT_ID"[NUMBER,22], AVG("SALARY")[22]
7 - "SALARY"[NUMBER,22], "DEPARTMENT_ID"[NUMBER,22]
8 - "E"."EMPLOYEE_ID"[NUMBER,22], "E"."HIRE_DATE"[DATE,7],
"E"."SALARY"[NUMBER,22],
"E"."DEPARTMENT_ID"[NUMBER,22]
9 - "E".ROWID[ROWID,10], "E"."SALARY"[NUMBER,22]
10 - "D"."DEPARTMENT_NAME"[VARCHAR2,30]
11 - "D".ROWID[ROWID,10]
-----------------------------------------------------------------------
Yukardaki raporda iyileştirme görevi kapsamındaki SQL komut textinin 1. kısım mevcut mimaride çalıştırılması halinde çalıştırma planını, 2. kısım ise tavsiye sonucunda çalıştırılması halinde çalıştırma planını göstermektedir.
İyileştirme işlemi tamamlandığında veya ilerde bu SQL iyileştirme görevlerine ihtiyaç kalmadığında, tüm içeriğiyle beraber Oracle sistemden silinebilir.
BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'test _task');
DBMS_SQLTUNE.drop_tuning_task (task_name => 'test _taskbycache');
DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_taskbycachescopelimited');
DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_taskbycachetimelimited');
DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_taskbyawr');
DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_taskbysqlset');
END;
/
Kayıtlar
0 yorum:
Yorum Gönder