DBMS_XPLAN
paketi ile SQL komutlarının çalıştırma planının analiz edilmesi istenildiğinde, bazı durumlarda
çalıştırma planı boyunca aşağıdaki satır görülebilmektedir.
Note
-----------
SQL profile "SYS_SQLPROF_012ad8267d9c0000"
used FOR this statement
Bunun
anlamı şudur;
·
SQL
komutunun değerlendirmesi esnasında harici bir yardımcı nesne kullanılmıştır.
·
Bu
yardımcı nesne çalıştırma planını değiştirmiştir veya en azından etkilemiştir.
Bu noktada
bir takım yardımlar almak faydalı olacaktır. Mesela, bu profil nesne nedir? Ne
iş yapar? Ve en önemliside SQL komutlarını değerlendirirken neden “harici” bir
yardıma ihtiyaç duyulmaktadır?
Oracle
optimizer ile ilgili problem aslında, geleneksel olarak Oracle optimizer’ın
çalıştırma planları oluşturmak için veri istatistiklerine güvenmesinden
kaynaklanmaktadır. İstatistikler aslında
gerçek verinin oldukça basitleştirilmiş tanımı şeklinde yer almaktadır. Oldukça
büyük GB boyutundaki bir tablonun kolon istatistikleri, sadece belirli şeyleri
içermektedir, değerlerin toplamı, benzersiz(distinct) değerlerin miktarı,
minimum ve maksimum değerler gibi... Diğer bir deyişle, istatistikler verinin
genel şeklini yakalar, ancak pekçok alt seviye detay bilgisi kaybolmaktadır.
Bu kayıp
detay bilgisini telafi etmek ve hala mantıklı ve doğru tahminler sağlamak için,
optimizer veri hakkında bazı varsayımları bulundurmaktadır. Bilhassa optimizer
bu durumlarda şu şekilde düşünmektedir:
·
Veri
değerleri uniform olarak dağılmıştır(diğer bir deyişle 2 nolu değer sıklıkla 5
nolu değerdir)
·
Kayıtlar
uniform olarak dağılmıştır(diğer bir deyişle fiziksel bir kümeleme veya veri
sıralaması yoktur).
·
Değerlerin
sıralaması aralıksızdır(diğer bir deyişle aralık arasında bir boşluk yoktur)
Bu
istatiksel analiz, verilerin çoğunluğu için şaşırtıcı şekilde doğru çalışma
eğiliminde olmaktadır, ancak kaçınılmaz olarak bu kuralı bozacak istisnalarda
olabilmektedir(örneğin segment üzerinde verinin %98 i mükemmel bir şekilde
rastgele dağılmış, geri kalan %2 lik kısım birkaç veri bloğunda yoğunlaşmış
olabilir-indeks clustering factor değerini çarpıtarak- ). Veya bakılan kayıtlar,
yüklemler arasında karakteristik olmayan bağlantılı değerleri bulundurabilir.
Ne
yazıkki, istatistikler bu tip enstrümanlar için ham olduğundan istenilen
seviyede detay bilgisini kayıt etmez. Böylece optimizer bu exceptional veri için potensiyel olarak daha
iyi olabilecek çalıştırma planı alternatiflerini gözden kaçırmaktadır.
Sonuç
olarak, dünya üzerindeki tüm istatistiklerin optimizer’ın daha iyi bir karar
alabilmesine yardımcı olamayacağı durumlar mevcuttur. Mesela, Oracle 11g
sürümüne kadar optimizer PL/SQL tablo fonksiyonlarından çıkan kayıt sayılarını
doğru şekilde tahmin edememekteydi, ve bunun yerine ne olursa olsun hiç bir
gerçekçi temeli olmayan çalıştırma planlarını oluşturmaktaydı.
İşte bu
noktada bu durumu düzeltmek için ne yapılabilir sorusu ortaya çıkıyor? Yani
optimizer’ı sadece “genel” için değil, ancak özel veri olacak şekilde etkin
nasıl çalıştırabiliz?
Bu noktada
yukardaki sorunların cevabı Oracle’dan şu şekilde gelmektedir:
·
“Özel
durum” SQL’ler için bireysel çalıştırma planı oluşturma ve kayıt etme.
·
Bu
bireysel planları tasarlamaya yardımcı olacak gerçek çalışma zamanı
istatistikleri kullanma.
SQL
Profiller bu yaklaşımın uygulaması olarak karşımıza çıkmakta ve aslında çalışma
biçimleride oldukça basit olmaktadır.
İlk önce,
hangi SQL lerin “özel veri” içerdiğinin ve
potansiyel olarak daha iyi çalıştırılabilir olduğunun Oracle’a
bildirilmesi gerekmektedir. Bu ya açıkça (DBMS_SQLTUNE.CREATE_TUNING_TASK
prodesüründe elle SQL lerin belirtilmesiyle yapılabilir) veya Oracle’ın “en
aktif” SQL leri analiz için seçmesine
izin verecek şekilde dolaylı olarak yapılabilir(Oracle 11g otomatik görevlerden
yapılabilir).
İkinci
olarak, optimizer’ın tahminlerinin “ayarsız” ve “daha iyi olabilir” olduğunun
kanıtlanması gerekmektedir. Bunu gerçekleştirmek için ise
DBMS_SQLTUNE.EXECUTE_TUNING_TASK prosedürü çalıştırılabilirdiği gibi otomatik
görevlerden yapılmasınada imkan verilebilir. Bu, prosesin en can alıcı kısmıdır
ve 3 büyük adım atılmalıdır:
İlk
adım: Veri örneklemesi yapılmalı ve gerçek sayılar elde
edilmelidir(en önemlisi ise cardinality)
İkinci
adım: Bu sayılar
optimizer’a sağlanır ve çalıştırma planını yeniden değerlendirmesine izin
verilir.
Üçüncü
adım: Eğer optimizer
farklı bir çalıştırma planı ile gelirse, SQL komutunu orjinal hali ile
çalıştırıp, yeni bir çalıştırma planı almalı(emin olmak için birkaç sefer
almalı) ve performans değişimlerini görmek için bu çalıştırma planlarının
kıyaslaması yapılmalıdır.
Eğer bu
denemenin sonuçlarında;
a) Optimizer
yeni bir çalıştırma planı getirmiş.
b) Bu yeni
plan kullanılırken performans esasen öncekinden daha iyi ise,
o zaman bu
belirli SQL için varsayılan istatistiklerin yalan söylediği ve optimizer’ın
gerçek değerleri kaçırmasına sebebiyet verdiği kanıtlanmış olacaktır. Dahası,
gerçek sayıların ne olduğu bilinmekte ve elbette gelecek için bunların saklanmasıda mantıklı olacaktır.
Peki,
gerçek cardinality sayıları nasıl saklanacaktır? Aslında, düzenli istatistikler
bu detay seviyesi için oldukça ham
olduklarından dolayı güncellenemez. Böylece, bunun yerine kendi SQL komutumuza
iliştireceğimiz ve bu tip bilgiyi saklayacağımız ayrı bir obje(“SQL Profili”)
kullanılacaktır.
Yeni
cardinality değerleri tam olarak nasıl saklanmaktadır? Burada elbette bir sihir
yapılamayacağından “hints” formları içinde saklanmaktadır. Daha önceden
denenmiş ve gerçek bir /*+ cardinality
*/ hinti kullanılacağını düşünebilirsiniz, ama Oracle aslında (biraz daha
gelişmiş yeni bir ) /*+ opt_param */ hinti kullanarak varsayılan cardinality
değerini artırıp azaltarak belli bir ölçekte
tutar. Örneğin;
/*+
opt_estimate(table, t, scale_rows=100) */
-- veya
/*+ opt_estimate(index_scan,
t, t_n_idx, scale_rows=0.001) */
Böylece,
şimdi SQL profiller kullanılırsa tahmini olarak varsayılan cardinality değeri
bu sayılarla çarıplacak ve sonuç olarak
SQL değerlendirme esnasında, optimizer verinin daha gerçekçi bir görünüşü ile
çalışabilecektir.
Bu kısaca
SQL profillerin teoride nasıl çalıştığıdır. Şimdi aşağıdaki örneklerde gerçek
SQL problemleri ile savaşmak üzere bu bilgiler nasıl kullanılacak ona bakalım.
Aşağıda
yer alan problem oldukça sık görülebilecek bir senaryodur. Genellikle
optimizer’ın “aralık tamin eksikliği” şeklinde tanımlanmaktadır. Bir tabloda SHIP_DATE
adlı kolonun sipariş tarihine karşılık geldiğini düşünelim.
SQL> CREATE
TABLE orders_tbl (order_no, ship_date)
AS
SELECT level, CAST (sysdate-level/24 AS DATE)
FROM
dual
CONNECT
BY level <= 500000;
Bu sipariş
tarihi kolonunda indekste mevcut olsun;
CREATE INDEX orders_shipping_idx
ON orders_tbl (ship_date);
Böylece
hızlıca aşağıdaki basit sorgu ile en son siparişler listelenmektedir:
SELECT * FROM
orders_tbl WHERE ship_date >= trunc(sysdate, 'DD');
Bunun
dışında verinin bir uyarısı mevcuttur; siparişler henüz gönderilmemiştir.
İşaretlemek için developer arkadaşlar ilerde “özel” bir tarih kullanılmasına
karar vermiştir. January 1st 9999 kullanılmakta
ve bu tarihte siparişin gönderileceği beklentimiz yoktur aslında.
Aşağıdaki
gibi bir tane gönderilmeyen bir sipariş olduğunu farzedelim.
SQL>INSERT
INTO orders_tbl VALUES (-1, DATE '9999-01-01');
SQL>COMMIT;
Sonuçta
veri hakkında taze bilgiyi optimizer’a sağlamak için istatistikleri topluyoruz:
EXEC
dbms_stats.gather_table_stats(APP1, 'orders_tbl', cascade => TRUE);
Artık en
son siparişleri talep etmeye hazırız. Tablomuzda 500,000 kayıt mevcut
olduğundan, bunların yaklaşık 24 tanesi bugün sipariş edildiğinden(tablo
oluşturuken belirledik, tablonun yapısına bkz), belli ki bu veriyi almak için
indeks kullanılması en verimli metot olacaktır:
SELECT * FROM
orders_tbl WHERE ship_date >= trunc(sysdate, 'DD');
----------------------------------------------------------------------------
| Id | Operation | Name | ROWS
| Bytes | Cost (%CPU)| TIME |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 496K| 6302K|
452 (24)| 00:00:06 |
|* 1 |
TABLE ACCESS FULL| ORDERS_TBL |
496K| 6302K| 452
(24)| 00:00:06 |
--------------------------------------------------------------------
Predicate
Information (IDENTIFIED BY operation id):
---------------------------------------------------
1 - FILTER("SHIP_DATE">=TRUNC(SYSDATE@!,'fmdd'))
Baktığımızda
işler umduğumuz gibi gitmedi ve FULL TABLE SCAN meydana geldi, indeks
kullanılmadı! Eğer AUTOTRACE çıktısına
bakılırsa cardinality beklentisinin besbelli “ayarsız” olması yüzünden bunun oluştuğu
açık olmaktadır(yaklaşık 24 olması gerekirken 496.000 kayıt).
Peki neden
besbelli yanlış hesaplanmış olan bu cardinality sonucunu almaktayız? Aslında
cevap basittir. Bunun sebebi optimizer’ın “sürekli aralık” varsayımıdır.
Optimizer
“gönderilmemiş” siparişleri değerlendirmeye almamış, böylece daha geniş bir
aralık görmüştür. Ancak bu şekilde sadece 1 masum kayıt ve optimizer’ın
hassaslığı direkt çöpe gitmiştir.
Elbette
buradaki ana sebep kötü veri tasarımıdır ve bunu düzeltecek doğru yol
“gönderilmemiş” kayıtları farklı işaretlemekten geçmektedir.(örneğin; sipariş
gönderim durumunu kayıt edecek ayrı bir kolon ekleme gibi).
Ancak,
burda kolon ekleme imkanı olmadığını
farzederek bu özel SQL için bir SQL profil oluşturma yoluna gitmek en mantıklı
yol olacaktır.
Bu noktada
ilk yapılması gereken; Oracle’a optimizer’dan daha hassas özel bir SQL
komutunun varlığını bildirmektir.
var task_name
varchar2(30)
EXEC :task_name
:= dbms_sqltune.create_tuning_task(
sql_text => 'SELECT * FROM orders WHERE
ship_date >= trunc(sysdate, ''DD'')'
);
Sonrasında,
Oracle’a bu SQL komutunu analiz etmesine imkan verilecektir.İşlemin nasıl
gitiiğini görmek için SQL izlemeside devreye alınacaktır:
SQL> ALTER
SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> EXEC
dbms_sqltune.execute_tuning_task(:task_name);
Once tuning task
is complete, let’s see if ORACLE found a better execution plan for our SQL (you
can see the full listing here):
SET linesize 180
SET longchunksize
180
SET pagesize 900
SET long 1000000
SELECT
dbms_sqltune.report_tuning_task(:task_name) FROM dual;
-------------------------------------------------------------------------------
FINDINGS SECTION
(1 finding)
-------------------------------------------------------------------------------
1- SQL Profile
Finding (see EXPLAIN plans SECTION below)
--------------------------------------------------------
A potentially better execution plan was found
FOR this statement.
Recommendation (estimated benefit: 99.53%)
------------------------------------------
- Consider accepting the recommended SQL
profile.
EXECUTE
dbms_sqltune.accept_sql_profile(task_name => 'TASK_120',
task_owner => 'APP1', REPLACE
=> TRUE);
Validation results
------------------
The SQL profile was tested BY executing BOTH
its plan AND the original plan AND
measuring their respective execution statistics. A plan may have been ONLY
partially executed IF the other could be run TO completion IN less TIME.
Original Plan WITH SQL Profile % Improved
------------- ---------------- ----------
Completion STATUS: COMPLETE COMPLETE
Elapsed
TIME(us): 234447 444 99.81 %
CPU TIME(us): 236666 0 100 %
USER I/O TIME(us): 0 0
Buffer Gets: 838 4 99.52 %
Physical READ Requests: 0 0
Physical WRITE Requests: 0 0
Physical READ Bytes: 0 0
Physical WRITE Bytes: 0 0
ROWS Processed: 13 13
Fetches: 13 13
Executions: 1 1
Notes
-----
1. The original plan was FIRST executed TO
warm the buffer cache.
2. Statistics FOR original plan were averaged
OVER NEXT 2 executions.
3. The SQL profile plan was FIRST executed TO
warm the buffer cache.
4. Statistics FOR the SQL profile plan were
averaged OVER NEXT 9 executions.
2- USING SQL
Profile
--------------------
Plan hash VALUE: 2399245311
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21
| 273 | 4
(0)| 00:00:01 |
| 1 |
TABLE ACCESS BY INDEX ROWID| ORDERS_TBL |
21 | 273 | 4
(0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN |
ORDERS_SHIPPING_IDX| 13 | |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate
Information (IDENTIFIED BY operation id):
---------------------------------------------------
2 - access("SHIP_DATE">=TRUNC(SYSDATE@!,'fmdd'))
Yukarda doğru
cardinality değerinini işleme girdiğini gördüğümüz gibi, aynı zamanda arzu
ettiğimiz INDEX SCAN işlemininde gerçekleştiğini görmekteyiz.
Peki,
Oracle bu doğru sayılara nasıl geldi? bu noktada Oracle SQL izlemesi ile elde
edilen sonuca bakıyoruz:
İlk adım: Veri örneklemesi yapılmaktadır.
-- Kolondaki tüm
veri örneklenmektedir.
SELECT /* DS_SVC
*/ SUM(C1) FROM (
SELECT /*+ NO_INDEX_FFS( "ORDERS_TBL") */ 1 AS C1
FROM "ORDERS_TBL" SAMPLE
BLOCK(62.5, 8) SEED(1)
)
-- Kolonda NOT
NULL veriler örneklenir .
SELECT /* DS_SVC
*/ SUM(C1) FROM (
SELECT /*+ INDEX_FFS( "ORDERS_TBL"
"ORDERS_SHIPPING_IDX") */ 1 AS
C1
FROM "ORDERS_TBL" SAMPLE
BLOCK(58.7803, 8) SEED(1)
WHERE ( "ORDERS_TBL".SHIP_DATE
IS NOT NULL)
)
-- Kolonda
“uygun” durumda veri örneklenir.
SELECT /* DS_SVC
*/ SUM(C1) FROM (
SELECT /*+ NO_INDEX_FFS( "ORDERS_TBL") */ 1 AS C1
FROM "ORDERS_TBL" SAMPLE
BLOCK(62.5, 8) SEED(1)
WHERE ("ORDERS_TBL"."SHIP_DATE">=TRUNC(SYSDATE@!,'fmdd'))
)
İkinci
adım: "Örnekleme”
sonuçlarına dayanarak satırlar “ölçeklenir”.
SELECT /* DS_SVC
*/
/*+ OPT_ESTIMATE(TABLE, "ORDERS_TBL",
SCALE_ROWS=4.189686512e-05) */ C1, C2, C3
FROM (
SELECT /*+ INDEX( "ORDERS_TBL"
"ORDERS_SHIPPING_IDX") */
COUNT(*) AS C1,
4294967295 AS C2, COUNT(*) AS C3
FROM "ORDERS_TBL"
WHERE ("ORDERS_TBL"."SHIP_DATE">=TRUNC(SYSDATE@!,'fmdd'))
)
Üçüncü
adım: Yeni planın
daha iyi olup olmadığının doğrulanması.
-- Orjinal plan
ile çalıştırma: 3 sefer
/* SQL
Analyze(144,0) */ SELECT * FROM orders_tbl WHERE ship_date >=
trunc(sysdate, 'DD')
-- Ardından 10
sefer yeni plan çalıştırılır.
/* SQL
Analyze(144,0) */ SELECT * FROM orders_tbl WHERE ship_date >=
trunc(sysdate, 'DD')
Böylece
Oracle veriyi örneklemiştir, yeni bir plan ile gelmiştir ve yeni planın eski
plandan daha iyi olduğunuda doğrulamıştır. Ardından bu çalışmanın sonuçlarını
SQL Profil içine kaydetmiştir.
Son adım
olarak yapılması gereken bu planın onaylanması ve böylece aynı SQL komutu için
bu üstün çalıştırma planının sonraki çalıştırmalarda da yeniden kullanılmasını
sağlamaktır.
EXEC
dbms_sqltune.accept_sql_profile(:task_name);
Kayıtlar
0 yorum:
Yorum Gönder