Dynamic Sampling, bir Oracle hint'idir ve optimizer'a yapması gerekenleri öğretmek için kullanılır. Dynamic sampling bir yön belirtici değildir, sadece optimizer'a query'i çalıştırdığınız anda geçerli olması üzerine bir istatistik toplanması gerçekleştirir. Hard parser yaptırabileceği gibi daha iyi bir plan da çıkarabilir. Dynamic sampling hint'i ile optimizer'a sadece istatistikleri güncelle diyebilirsiniz ve eğer güncelleme oluşmuşsa ve explain plan'de bir yol değişikliği olmuş ise bunu sağlayan dynamic sampling değildir, sadece optimizer daha iyi bir yol keşfetmiştir.
Dynamic Sampling ile ilgili güzel bir yazı ve cevap Thomas Kyte'dan gelmiş.
Kullanım şekli ise diğer Oracle hint'leri ile aynıdır;
/*+ dynamic_sampling(
Bir diğer hint ise Nested Loop. Bununla ilgili süper bir örnek gösterebilirim;
Aşağıdaki sorguyu inceleyelim;
SQL>
SELECT
A.DATETIME, A.IPADDRESS,
DECODE (A.IPADDRESS,'212.156.116.117', '06_ulus_t1_1', '212.156.116.118','06_ulus_t1_2', '212.156.116.119', '00_gayrettepe_t1_1', '212.156.116.120', '00_gayrettepe_t1_2','212.156.116.121' , '34_acibadem_t1_1', '212.156.116.122', '34_acibadem_t1_2', '212.156.116.123', '35_izmir_t1_1', '212.156.116.124', '35_izmir_t1_2', '212.156.116.125', '01_adana_t1_1', '212.156.116.126', '01_adana_t1_2') MACHINE,
SUBSTR(A.IDX,0,INSTR(A.IDX,'.')-1) IFINDEX, DECODE (SUBSTR(A.IDX,INSTR(A.IDX,'.')+1),'2.97.102','Af','2.98.101','Be','2.101.102','Ef','2.104.49','H1','2.104.50','H2','2.108.49','L1', '2.108.50','L2','2.110.99','Nc') FORW_CLASS,
SUBSTR(A.IDX,INSTR(A.IDX,'.')+1) IDX,
A.JNXCOSIFQTOTALREDDROPPKTS,
A.JNXCOSIFQTXEDBYTES,
CASE
WHEN (A.JNXCOSIFQTXEDBYTES - B.JNXCOSIFQTXEDBYTES) < datetime =" B.DATETIME" ipaddress =" B.IPADDRESS" idx =" B.IDX" style="font-weight: bold;">Execution Plan
----------------------------------------------------------
Plan hash value: 3451555024
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 700K| 62M| | 32094 (15)| 00:02:03 | | |
|* 1 | HASH JOIN | | 700K| 62M| 39M| 32094 (15)| 00:02:03 | | |
| 2 | PARTITION RANGE ALL| | 700K| 31M| | 10400 (28)| 00:00:40 | 1 | 107 |
|* 3 | TABLE ACCESS FULL | TRAFFIC | 700K| 31M| | 10400 (28)| 00:00:40 | 1 | 107 |
| 4 | PARTITION RANGE ALL| | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.01041666666666666666666666666666
66666667 AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
3 - filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL)
İki defa full table scna yapıldı, çok yavaş.
Bir de dynamic sampling hint'ini eklediğimiz zaman neler oluyor, ona bakalım (sorgu aynı, sadece hint var ve her iki ana tablo için);
Execution Plan
----------------------------------------------------------
Plan hash value: 3451555024
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14M| 1256M| | 43788 (13)| 00:02:48 | | |
|* 1 | HASH JOIN | | 14M| 1256M| 788M| 43788 (13)| 00:02:48 | | |
| 2 | PARTITION RANGE ALL| | 14M| 628M| | 10532 (29)| 00:00:41 | 1 | 107 |
|* 3 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 10532 (29)| 00:00:41 | 1 | 107 |
| 4 | PARTITION RANGE ALL| | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.01041666666666666666666666666666
66666667 AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
3 - filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
Dynamic sampling ile hash join maliyetimiz arttı. Bu durumda daha farklı bir yol izlemeliyiz. Hash Join yerine Nested Loop ile bu iki ana tabloyu daha hızlı çekebiliriz. Bu iki tabloyu bir nested loop ile çok daha hızlı görüntüleyebiliriz çünkü yığın olarak kümelediğimiz zaman full table scan yapmakta;
Bunun için sorguyu aşağıdaki gibi değiştiriyorum;
SQL>
SELECT
/*+ USE_NL(A) USE_NL(B)*/
A.DATETIME, A.IPADDRESS,
DECODE (A.IPADDRESS,'212.156.116.117', '06_ulus_t1_1', '212.156.116.118','06_ulus_t1_2', '212.156.116.119', '00_gayrettepe_t1_1', '212.156.116.120', '00_gayrettepe_t1_2','212.156.116.121' , '34_acibadem_t1_1', '212.156.116.122', '34_acibadem_t1_2', '212.156.116.123', '35_izmir_t1_1', '212.156.116.124', '35_izmir_t1_2', '212.156.116.125', '01_adana_t1_1', '212.156.116.126', '01_adana_t1_2') MACHINE,
SUBSTR(A.IDX,0,INSTR(A.IDX,'.')-1) IFINDEX, DECODE (SUBSTR(A.IDX,INSTR(A.IDX,'.')+1),'2.97.102','Af','2.98.101','Be','2.101.102','Ef','2.104.49','H1','2.104.50','H2','2.108.49','L1', '2.108.50','L2','2.110.99','Nc') FORW_CLASS,
SUBSTR(A.IDX,INSTR(A.IDX,'.')+1) IDX,
A.JNXCOSIFQTOTALREDDROPPKTS,
A.JNXCOSIFQTXEDBYTES,
CASE
WHEN (A.JNXCOSIFQTXEDBYTES - B.JNXCOSIFQTXEDBYTES) < datetime =" B.DATETIME" ipaddress =" B.IPADDRESS" idx =" B.IDX" style="font-weight: bold;">45:05:57 | | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID| TRAFFIC | 1 | 47 | 3 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 700K| 62M| 42M (2)| 45:05:57 | | |
| 3 | PARTITION RANGE ALL | | 14M| 628M| 8738 (14)| 00:00:34 | 1 | 107 |
| 4 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | PARTITION RANGE ITERATOR | | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | TRAFFIC_PK | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.0104166666666666666666666666666666666667
AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL AND "A"."IDX"="B"."IDX")
Maliyetin inanılmaz arttığını göreceksiniz. Yalnız Oracle'da maliyetin yüksek olması sorgunun da yavaş olacağı anlamına asla ve asla GELMEZ! USE_NL hint'ine sahip olmayan sorgunun cevabı 1 dakika 15 saniyede dönerken, USE_NL hint'ine sahip sorgunun cevabı 240 milisaniye gibi çok farklı bir sonuçla geliyor. Tek bir full scan yapıldı, ikinciye gerek kalmadan bütün veriler rowid'lerine erişilerek (index ile) elde edildi. Bunun dışında CPU'da geçirilecek sürede de inanılmaz bir azalma görülüyor.
Oracle dokümantasyonlarına göre CBO (cost based optimizer) her zaman, her zaman maliyeti düşük olan sorgu planını seçer ve uygular. Bunu değiştirmenin tek yolu optimizer hint'leridir. Optimizer'ın her zaman en optimum sorguyu getireceği asla garanti edilmez, edilemez. Çünkü optimizer'ın etkilendiği birçok durum vardır. Örneğin;
1) CBO tablo ve indeks üzerindeki istatistiklere göre bir sorgu planı oluşturur. İstatistikler eksik ya da toplanmamış ise farklı ve daha kötü planlar ortaya çıkabilir.
2) CBO bir sütunun içeriğine bakarakta plan oluşturabilir. Sütunun tipi varchar2 ise ve içerisinde sadece rakamsal değerler varsa bu optimizer'ı yanıltabilir!
3) CBO hint'leri kullanmak yerine ve duruma göre oldukça olumlu ve yüz güldüren sonuçlar elde etmenizi sağlar.
4) CBO sorgunun mantığının hatalı olduğunu, sorguda eksik eşitlik, hatalı bağlantılar ya da unutulmuş indeksler olduğunu anlamaz ve size öneride bulunmaz. Bunu yapan başka bir araç vardır (Detaylı bilgi için AWR). Bu sebepten dolayı önce SQL tuning sonra Oracle hint.
İyi çalışmalar,
Ogan
