14 Ocak 2010 Perşembe

ORDERED & USE_NL Oracle Hints

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

13 Ocak 2010 Çarşamba

Synonym

Merhaba,

Oracle veritabanında bulunan objelere "synonym" olarak bilinen takma isimler verilebilir. Bu takma isimleri kullanmamızdaki sebeplerden birkaçı, başka kullanıcıların synonym'i yaratan kullanıcının objesine direkt erişim sağlayabilmeleri ya da veri güvenliğinin sağlanması olarak özetlenebilir. Bir diğer avantajı ise uzun isimlendirilmiş obje isimlerinin kısaltılmasıdır.

Synonym'ler CREATE SYNONYM komutu koşturularak yaratılırlar ve PUBLIC ya da PRIVATE olabilirler. PUBLIC synonym'leri bütün veritabanı kullanıcıları okuyabilirken, PRIVATE synonym'leri sadece yaratan kullanıcı okuyabilir. Sonuçta PRIVATE synonym yaratmak biraz daha güvenli olacaktır ancak kullanım alanına da dikkat etmek gerekiyor.

Synonym'lerin devreye girdiği en önemli nokta ise bir SELECT bloğundaki kullanımları. Bu konu üzerine çok fazla soru gelir ve sertifikasyon sınavlarında bile karmaşık synonym soruları sorulabilir. Ben şimdi sorgudaki sıralamadan bahsetmek istiyorum.

Diyelim aşağıdaki gibi bir sorgu koşturduk (HR kullanıcısı ile);

SQL > SELECT EMP_NO, EMP_NAME FROM EMPLOYEES;

Yukarıdaki sorgunun ilk bakacağı EMPLOYEES objesi, HR şeması içerisindeki EMPLOYEES view ya da tablosudur. Eğer böyle bir obje geçerli ise tablodaki bütün emp_no ve emp_name bilgileri döndürülecektir. Ancak HR şemasında böyle bir view ya da tablo yoksa Oracle'ın ikinci adımda bakacağı bilgi PRIVATE synonym'i olup olmadığıdır. PRIVATE synonym ile isimlendirilmiş bir tablo ya da view'dan veri getirilecektir. Bu noktada aşağıdaki örneği dikkatli incelemenizde fayda var;

# sqlplus nortel_switching/*******@

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 13 16:52:47 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc brstat;
Name Null? Type
----------------------------------------- -------- ----------------------------
DATETIME_INS DATE
DATETIME NOT NULL DATE
SWITCH_NAME NOT NULL VARCHAR2(50)
DURATION NUMBER
BRSCAP NUMBER
BRSCMPLX NUMBER
BRSSCHED NUMBER
BRSFORE NUMBER
BRSMAINT NUMBER
BRSDNC NUMBER
BRSOM NUMBER
BRSGTERM NUMBER
BRSBKG NUMBER
BRSIDLE NUMBER
BRSAUXCP NUMBER
BRSNETM NUMBER
BRSSNIP NUMBER

Yukarıda BRSTAT isimli bir objemizin olduğu anlaşıldı. Şimdi bu isimle bir synonym yaratmaya çalışıyorum;

SQL> create synonym BRSTAT for BRSTAT;
create synonym BRSTAT for BRSTAT
*
ERROR at line 1:
ORA-01471: cannot create a synonym with same name as object

Yaratırken yukarıdaki hatayı aldım. Bir obje ile aynı isimli synonym yaratılamayacağını öğrendim. Şimdi ise private değil de, public synonym yaratmak istiyorum;

SQL> create public synonym BRSTAT for nortel_switching.BRSTAT;
create public synonym BRSTAT for nortel_switching.BRSTAT
*
ERROR at line 1:
ORA-01031: insufficient privileges

Yine yaratamadım çünkü create synonym hakkına sahip bir kullanıcı ile bağlanmadım. Bu hakka sahip bir kullanıcı ile bağlanıyorum.

SQL> conn system/******
Connected.
SQL> create public synonym BRSTAT for nortel_switching.BRSTAT;

Synonym created.

BRSTAT isminde bir public synonym tanımladım ve başarılıyla yaratıldı. Bu noktadan itibaren bütün veritabanı kullanıcı ben aşağıdaki hakkı verdikten sonra nortel_switching kullanıcısın BRSTAT tablo ya da view'ına erişim sağlayabilecek;

SQL> conn nortel_atm/password
Connected.
SQL> select * from BRSTAT where rownum <>
select * from BRSTAT where rownum <>
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn system/******
Connected.
SQL> grant select on brstat to public;

Grant succeeded.

SQL> conn nortel_atm/password
Connected.
SQL> select * from BRSTAT where rownum <>

DATETIME_ DATETIME SWITCH_NAME DURATION BRSCAP BRSCMPLX BRSSCHED BRSFORE BRSMAINT BRSDNC BRSOM BRSGTERM BRSBKG BRSIDLE BRSAUXCP BRSNETM BRSSNIP
--------- --------- -------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
12-OCT-09 12-OCT-09 AFYNTUSHDS0 15 0 335 196 24 51 5 33 1 752 0 13 0 5
12-OCT-09 12-OCT-09 ANTLTUALDS0 15 0 197 201 24 51 5 37 0 665 0 13 0 5
12-OCT-09 12-OCT-09 ISBLTUTT02T 15 14 408 87 30 38 105 29 0 621 0 3 0 2
12-OCT-09 12-OCT-09 AGRITUAG01T 15 0 238 180 31 56 5 30 1 873 0 13 0 5
12-OCT-09 12-OCT-09 GRSNTUBACG0 15 0 199 197 28 51 5 28 0 769 0 13 0 5


Yukarıdaki örnekler dikkatli incelendiği zaman public synonym'in neye yaradığı gözükmektedir. Şimdi gelelim sıralamaya;

1) Sorgu sahibi kullanıcının kendi objesine bakılır. (OBJE)
2) Sorgu sahibi kullanıcının kendi objesi yoksa private synonym aranır. (PRIVATE SYNONYM)
3) Private synonym de bulunamazsa Public synonym kontrol edilir. (PUBLIC SYNONYM)

Synonym'lerin gerçekten kullanışlı olduğunu ve çok kuvvetli olduklarını belirtmem gerekiyor.

İyi çalışmalar dilerim,

Ogan


Takip et: @oganozdogan