Merhaba,
Oracle'ın 10g'den sonra kullandığı SQL optimizer türü Cost Based Optimizer'dır. Bundan önce Rule Based Optimizer kullanmaktaydı.
Oracle Hint'leri sayesinde optimizer'a SQL'i nasıl çağırması gerektiğini öğretebiliyoruz. Kimi zaman son derece faydalı olabiliyor Oracle hint'leri fakat execution plan dikkatlice incelenmeli ve en faydalı olacak Optimizer hint'i bulunmalıdır. Cost Based Optimizer oldukça iyi çalışmaktadır fakat günlük operasyonlarımızda görüyoruz ki bazı durumlarda gerçekten sıkıntı yaratabiliyor.
Benim burada yapabileceğim en büyük tavsiye şu olabilir; büyük bir sorgu bloğu hazırladığınız zaman ya da sorguyu çektiğiniz tablodaki kayıt sayısı çok fazla ise mutlaka execution plan'ını inceleyiniz!
Örneğin aşağıdaki sorguyu inceleyelim. Bu sorguyu çalıştırmadan önce aşağıdaki ayarları yapabiliriz;
SQL> set timing on;
SQL> set autotrace traceonly explain;
Ve örnek sorgumuza bakalım;
SELECT
DISTINCT
'PasoLink Plus',
'SDH' TECHNOLOGY,
A.COMMISSIONING_DATA CITY,
A.NENAME AS LINK_1,
A.Opposite_Network_Element LINK_2,
A.NE_TYPE AS SECTION
FROM NEC_TX.NECCFG_SDH1 A, TABLE(AIRCOM.GET_USER_NES('NEC_TX')) B
WHERE A.NENAME=B.NE_NAME
AND A.RADIO_TYPE = 'PlusStm1'
UNION ALL
SELECT
DISTINCT
'PasoLink Plus',
'SDH' TECHNOLOGY,
A.COMMISSIONING_DATA CITY,
A.NENAME AS LINK_1,
A.NENAME AS LINK_2,
A.NE_TYPE as SECTION
FROM NEC_TX.NECCFG_SDH1 A, TABLE(AIRCOM.GET_USER_NES('NEC_TX')) B
WHERE A.NENAME=B.NE_NAME
AND A.RADIO_TYPE = 'PlusStm1'
ORDER BY
TECHNOLOGY,
CITY,
LINK_1,
LINK_2,
SECTION
Yukarıdaki örnek sorguda distinct, union all ve join özelliklerinin hepsini bir arada kullanıyoruz. Oldukça kısa ama karmaşık olabilecek bir sorgu. Şimdi execution plan'ini inceleyelim;
Execution Plan
----------------------------------------------------------
Plan hash value: 1084736986
--------------------------------------------------------------------------------
------------------------------
| Id | Operation | Name | Rows | B
ytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------------
| 0 | SELECT STATEMENT | | 2 |
332 | 120 (5)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 |
332 | 119 (53)| 00:00:01 |
| 2 | UNION-ALL | | |
| | |
| 3 | HASH UNIQUE | | 1 |
166 | 60 (5)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 |
166 | 59 (4)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 1 |
133 | 57 (4)| 00:00:01 |
|* 6 | INDEX SKIP SCAN | IDX_OPTIMA_TEMIP_1 | 1 |
131 | 1 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 16360 | 3
2720 | 56 (4)| 00:00:01 |
| 8 | COLLECTION ITERATOR PICKLER FETCH| GET_USER_NES | |
| | |
|* 9 | INDEX RANGE SCAN | NECCFG_SDH_PK | 1 |
33 | 2 (0)| 00:00:01 |
| 10 | HASH UNIQUE | | 1 |
166 | 60 (5)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 |
166 | 59 (4)| 00:00:01 |
| 12 | MERGE JOIN CARTESIAN | | 1 |
133 | 57 (4)| 00:00:01 |
|* 13 | INDEX SKIP SCAN | IDX_OPTIMA_TEMIP_1 | 1 |
131 | 1 (0)| 00:00:01 |
| 14 | BUFFER SORT | | 16360 | 3
2720 | 56 (4)| 00:00:01 |
| 15 | COLLECTION ITERATOR PICKLER FETCH| GET_USER_NES | |
| | |
|* 16 | INDEX RANGE SCAN | NECCFG_SDH_PK | 1 |
33 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------------------
Yukarıdaki en büyük düşmanımız aslında cost'u düşük olmasına rağmen MERGE JOIN CARTESIAN yapıyor olmasıdır. Son derece maliyetli olan bu join türünden kurtulmanın yolları vardır.
Oracle tablo join'lerini yaparken en uygun planı çıkarmaya çalışır. Bu planı da belirli bir sıraya uyarak yapmak ister. Yukarıdaki durumda bu sıranın yeniden düzenlenmesini sağlayabiliriz. Bu da problemimizi giderecektir. Yukarıdaki sorguyu execution plan'ı olmadan çalıştırıyorum;
SQL> SET AUTOTRACE OFF;
SQL> SELECT
DISTINCT
'PasoLink Plus',
'SDH' TECHNOLOGY,
A.COMMISSIONING_DATA CITY,
A.NENAME AS LINK_1,
A.Opposite_Network_Element LINK_2,
A.NE_TYPE AS SECTION
FROM NEC_TX.NECCFG_SDH1 A, TABLE(AIRCOM.GET_USER_NES('NEC_TX')) B
WHERE A.NENAME=B.NE_NAME
AND A.RADIO_TYPE = 'PlusStm1'
UNION ALL
SELECT
DISTINCT
'PasoLink Plus',
'SDH' TECHNOLOGY,
A.COMMISSIONING_DATA CITY,
A.NENAME AS LINK_1,
A.NENAME AS LINK_2,
A.NE_TYPE as SECTION
FROM NEC_TX.NECCFG_SDH1 A, TABLE(AIRCOM.GET_USER_NES('NEC_TX')) B
WHERE A.NENAME=B.NE_NAME
AND A.RADIO_TYPE = 'PlusStm1'
ORDER BY
TECHNOLOGY,
CITY,
LINK_1,
LINK_2,
SECTION;
524 rows selected.
Elapsed: 00:04:41.08
4 dakika 41 saniyede sadece 524 satır getirebildik ki inanılmaz kötü bir performans.
Yukarıdaki sorguya tek bir satır ekleyeceğim ve optimizer'a, veriyi nasıl çekmesi gerektiğini öğreteceğim. Ekleyeceğim iki özellikten ilki ORDERED optimizer hint'i. Ordered hint'inin amacı, çok yüksek maliyetli ve parse aşaması uzun işlemleri engellemesidir. Tablo join'lerinde kullanılabilir ve FROM aşamasında nasıl sıralanmışlarsa bu hint sayesinde o sıraya göre işleme alınırlar. Optimizer topladığı istatistiklere göre hareket etmek istese bile hint bunu engeller.
Ekleyeceğim diğer optimizer hint'i ise USE_NL. Bu hint'in amacı ise join yapılan tabloların alt kümeleri küçük ise ve sort etmek istemiyorsanız, sorguyu hızlandırmak. Amacımız yine sorgunun bize hızlı dönmesi.
Optimizer hint'i aşağıdaki şekilde ayarlanacak;
/*+ ordered use_nl(A) */
Sorgu ise aşağıdaki gibi şekillenecek;
SELECT
/*+ ordered use_nl(A) */
DISTINCT
'PasoLink Plus',
'SDH' TECHNOLOGY,
A.COMMISSIONING_DATA CITY,
A.NENAME AS LINK_1,
A.Opposite_Network_Element LINK_2,
A.NE_TYPE AS SECTION
FROM NEC_TX.NECCFG_SDH1 A, TABLE(AIRCOM.GET_USER_NES('NEC_TX')) B
WHERE A.NENAME=B.NE_NAME
AND A.RADIO_TYPE = 'PlusStm1'
UNION ALL
SELECT
/*+ ordered use_nl(A) */
DISTINCT
'PasoLink Plus',
'SDH' TECHNOLOGY,
A.COMMISSIONING_DATA CITY,
A.NENAME AS LINK_1,
A.NENAME AS LINK_2,
A.NE_TYPE as SECTION
FROM NEC_TX.NECCFG_SDH1 A, TABLE(AIRCOM.GET_USER_NES('NEC_TX')) B
WHERE A.NENAME=B.NE_NAME
AND A.RADIO_TYPE = 'PlusStm1'
ORDER BY
TECHNOLOGY,
CITY,
LINK_1,
LINK_2,
SECTION
Şimdi ise bu sorguyu koşalım ve execution plan'ına bakalım;
SQL> set autotrace traceonly explain;
Execution Plan
----------------------------------------------------------
Plan hash value: 4144135033
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Byt
es | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 2 | 3
32 | 126 (8)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 3
32 | 125 (54)| 00:00:01 |
| 2 | UNION-ALL | | |
| | |
| 3 | HASH UNIQUE | | 1 | 1
66 | 62 (7)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 1
66 | 61 (5)| 00:00:01 |
|* 5 | HASH JOIN | | 1 | 1
64 | 5 (20)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | NECCFG_SDH_PK | 432 | 142
56 | 3 (0)| 00:00:01 |
|* 7 | INDEX SKIP SCAN | IDX_OPTIMA_TEMIP_1 | 1 | 1
31 | 1 (0)| 00:00:01 |
| 8 | COLLECTION ITERATOR PICKLER FETCH| GET_USER_NES | |
| | |
| 9 | HASH UNIQUE | | 1 | 1
66 | 62 (7)| 00:00:01 |
|* 10 | HASH JOIN | | 1 | 1
66 | 61 (5)| 00:00:01 |
|* 11 | HASH JOIN | | 1 | 1
64 | 5 (20)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | NECCFG_SDH_PK | 432 | 142
56 | 3 (0)| 00:00:01 |
|* 13 | INDEX SKIP SCAN | IDX_OPTIMA_TEMIP_1 | 1 | 1
31 | 1 (0)| 00:00:01 |
| 14 | COLLECTION ITERATOR PICKLER FETCH| GET_USER_NES | |
| | |
--------------------------------------------------------------------------------
----------------------------
Hash join ve index'lerin üzerinden geçerek verileri elde eden optimizer'ın sorguyu bize döndürdüğü süreye bakalım;
SQL> set autotrace off;
SQL> SELECT
DISTINCT
'PasoLink Plus',
'SDH' TECHNOLOGY,
A.COMMISSIONING_DATA CITY,
A.NENAME AS LINK_1,
A.Opposite_Network_Element LINK_2,
A.NE_TYPE AS SECTION
FROM NEC_TX.NECCFG_SDH1 A, TABLE(AIRCOM.GET_USER_NES('NEC_TX')) B
WHERE A.NENAME=B.NE_NAME
AND A.RADIO_TYPE = 'PlusStm1'
UNION ALL
SELECT
DISTINCT
'PasoLink Plus',
'SDH' TECHNOLOGY,
A.COMMISSIONING_DATA CITY,
A.NENAME AS LINK_1,
A.NENAME AS LINK_2,
A.NE_TYPE as SECTION
FROM NEC_TX.NECCFG_SDH1 A, TABLE(AIRCOM.GET_USER_NES('NEC_TX')) B
WHERE A.NENAME=B.NE_NAME
AND A.RADIO_TYPE = 'PlusStm1'
ORDER BY
TECHNOLOGY,
CITY,
LINK_1,
LINK_2,
SECTION;
524 rows selected.
Elapsed: 00:00:01.57
4 dakika 41 saniyede çıktı veren sorgu, ORDERED ve USE_NL hint'leri sayesinde 1 saniye 57 saliseye kadar indi.
Yukarıda açıkladığım optimizer'ın hint'lerinden sadece iki tanesini kapsamaktadır. Bunlar dışında sıklıkla kullanılan bir diğer hint ise INDEX hint'i olabilir.
Optimizer hint'leriyle ilgili yazılabilecek bir dizi yazı var ancak örnekle göstermek istediğim en güzel örnek budur :)
İyi çalışmalar dilerim,
Ogan
1 yorum:
Yorum Gönder