Oracle 11g, tekil tablo sorgularının “where” kısmında çoklu kolonlar belirtildiği durumlarda, kolon grupları istatistikleri kullanarak maliyet tabanlı geliştirici(cost based optimizer-CBO) nin daha iyi karar almasına yardımcı olacak geliştirilmiş istatistiklerin işleme alınmasına imkan vermektedir. Standart istatistiklere ilave olarak, kolon grubu veri dağıtımında çarpıklık yer aldığında gelişmiş maliyet tahminleri için bu kolon grupları üzerinde histogramlarda oluşturulabilir.
CBO’nun bir sorgu için kendi çalıştırma kararlarını sırayla alması için, muhtemel erişim yolları için maliyet tahminlerini hesaplamak için uygun istatistikleri kullanması ile olur. Eğer kolon histogramları mevcutsa, doğrulanmış değerlerin seçiciliğini hesaplamak için optimizer bu histogramları kullanmaktadır. Bu seçicilik, maliyetlerin tahmin edilmesinde ve sonuçta en iyi erişim yolunun tercih edilmesinde önemli bir etkendir. Oracle 11g öncesinde istatistikler bireysel kolonlardan oluşturulabilmekteydi. Tek bir tablo üzerinde birçok kolon arasındaki ilişkiyi gerçekleştirmek için CBO maliyet hesaplamalarında yetersizlik başlıca bir sınırlama olmaktaydı.
Çalışanların ID lerini, bunların ne kadar yıl çalıştığını, aylık maaşlarını ve aylık primlerini listeleyen bir tablo düşünün. Bu şirkette farzedinki çalışanların aldıkları prim çalıştıkları yıl sayısı değişkeniyle hesaplanmakta... Geleneksel kolon histogramları, optimizera her bir kolondaki değerin dağılımı hakkında bilgi sağlamaktadır. Oysa, bu tabloyu hem çalışanların çalıştığı yıl süresi, hemde yıllık prim ile sorgulamada CBO için her iki kolon arasında bir bağlantı gerçekleştirmek mümkün olmaz. Eğer 1 yıldır çalışan ancak yıllık 20,000TL prim alan çalışanlar bulunmak istenirse, CBO bu iki yüklemin boş bir sonuç seti getireceğini kavrayamaz. Bunun yerine CBO her bir bireysel kolon üzerindeki istatistikleri ayrı ayrı kullanarak muhtemelen verimsiz bir çalıştırma planı oluşturacaktır. Örneğin, bu şirketteki çalışanların çoğunluğu sadece bir yıldan beri çalışıyorsa, tablodaki mevcut satır sayısının çoğu bu veriden oluştuğundan dolayı optimizer muhtemelen index erişimi yerine full tablo taramasını seçecektir. Eğer optimizer her iki yüklem kombinasyonunun hiç bir satır döndürmeyeceğini bilseydi, CBO bu bilgiyi daha iyi maliyet hesaplamalarında kullanabilecekteydi ve o zaman bu iki kolonun birlikte indekslendiği indeksi kullanmanın daha avantajlı olacağını bilecekti.
Yeni “çoklu kolon istatistikleri”, tek bir tablo içinde kolon grupları oluşturmaya izin vererek bu sınırlamanın üstesinden gelen yeni bir özelliktir. Bu kolon grupları tablo üzerinde yeni bir kolon gibi davranırlar. İstatistikleri ise DBMS_STATS paketi ile elde edilebilmektedir. Kolon grup istatistiklerine bir dizi benzersiz(distinct) değer ve histogramlarda ilave olmaktadır.Bu özelliklerin sonucu olarak, optimizer kolon grup istatistiklerini kullanarak, daha önceki Oracle sürümlerinde kullanılan çoklu kolonlar için ilişkili istatistiklere nazaran daha iyi maliyet hesaplamaları oluşturabilmektedir.
Bu yeni özelliği göstermek için, ALL_OBJECTS tablosundan oluşturulan aşağıdaki örneği inceleyelim. Oracle 11g temel kurulumunda where kısmında bir çok kolon kullanıldığında belirli sorgular için verimsiz çalıştırma planları olabilmektedir. objelerin çoğunluğu PUBLIC sahipliği altındadır ve ikinci en sık kullanılan obje tipi ise JAVA CLAS’tır.
SQL> select owner, count(*) from all_objects group by owner
order by count(*) desc;
OWNER COUNT(*)
------------------------------ ----------
SYS 30812
PUBLIC 27702
SYSMAN 3404
ORDSYS 2532
APEX_030200 2251
MDSYS 1415
OLAPSYS 717
XDB 503
SYSTEM 503
CTXSYS 364
EXFSYS 308
WMSYS 306
SH 299
ORDDATA 239
OE 122
DBSNMP 65
IX 48
HR 38
OWBSYS_AUDIT 12
FLOWS_FILES 11
ORDPLUGINS 10
PM 10
SI_INFORMTN_SCHEMA 8
ORACLE_OCM 8
BI 8
OUTLN 8
SCOTT 6
APPQOSSYS 3
OWBSYS 2
29 rows selected.
SQL> select object_type, count(*) from all_objects group by object_type
order by count(*) desc;
OBJECT_TYPE COUNT(*)
------------------- ----------
SYNONYM 27805
JAVA CLASS 22920
VIEW 5054
INDEX 3895
TABLE 2863
TYPE 2820
PACKAGE 1310
PACKAGE BODY 1250
JAVA RESOURCE 834
TRIGGER 617
JAVA DATA 309
FUNCTION 302
INDEX PARTITION 300
TYPE BODY 240
SEQUENCE 228
LIBRARY 183
PROCEDURE 159
LOB 153
TABLE PARTITION 144
OPERATOR 55
XML SCHEMA 51
JOB 32
CONSUMER GROUP 25
RULE SET 23
PROGRAM 19
QUEUE 17
EVALUATION CONTEXT 14
JOB CLASS 13
RESOURCE PLAN 10
CLUSTER 10
UNDEFINED 9
INDEXTYPE 9
WINDOW 9
DIRECTORY 8
CONTEXT 7
SCHEDULER GROUP 4
MATERIALIZED VIEW 3
SCHEDULE 3
JAVA SOURCE 2
DESTINATION 2
RULE 1
LOB PARTITION 1
EDITION 1
43 rows selected.
Eğer where kısmındaki kolonların değerleri OWNER= ‘PUBLIC’ ve OBJECT_TYPE= ‘JAVA CLASS’ ise, bu kolon yüklemlerinin her biri toplam satırların büyük kısmını gösterir(%40 dan fazla) ve PUBLIC tarafından sahip olunan hiç bir JAVA CLASS yoktur.
SQL> select * from all_objects
where owner='PUBLIC' and object_type='JAVA_CLASS';
no rows selected
Bu dictionary tablosunun kopyasını kullanmak, optimizer’ın nasıl çoklu kolon istatistiklerini kullandığını gösteren güzel bir örnek olacaktır.
1. İlk olarak ALL_OBJECTS tablosundan bir test tablosu oluşturulacak ve bu OWNER ve OBJECT_TYPE kolonları üzerinden bir indeks oluşturulup tablonun istatistikleri toplanacaktır.
SQL> create table allobjects_test
as select * from all_objects;
Table created.
SQL> create index TEST_IDX on allobjects_test (owner, object_type);
Index created.
SQL> begin
DBMS_STATS.GATHER_TABLE_STATS(NULL,'ALLOBJECTS_TEST', method_opt => 'for all columns size skewonly');
end;
/
PL/SQL procedure successfully completed.
2. Yeni tablo üzerinde kolon istatistikleri görüntülenir.
SQL> select column_name, num_distinct, histogram
from user_tab_col_statistics
where table_name = 'ALLOBJECTS_TEST';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER 29 FREQUENCY
OBJECT_NAME 42928 HEIGHT BALANCED
SUBOBJECT_NAME 106 FREQUENCY
OBJECT_ID 71715 NONE
DATA_OBJECT_ID 6883 HEIGHT BALANCED
OBJECT_TYPE 43 FREQUENCY
CREATED 995 HEIGHT BALANCED
LAST_DDL_TIME 1039 HEIGHT BALANCED
TIMESTAMP 1108 HEIGHT BALANCED
STATUS 1 FREQUENCY
TEMPORARY 2 FREQUENCY
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
GENERATED 2 FREQUENCY
SECONDARY 2 FREQUENCY
NAMESPACE 20 FREQUENCY
EDITION_NAME 0 NONE
15 rows selected.
3. Oto izleme(autotrace) devreye alınır ve public tarafından sahip olunan tüm java sınıflarını bulacak bir sorgu çalıştırılır:
SQL> set autotrace on
SQL> select * from allobjects_test where owner = 'PUBLIC' and object_type
= 'JAVA CLASS';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1539201261
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10291 | 1025K| 233 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| ALLOBJECTS_TEST1 | 10291 | 1025K| 233 (1)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='PUBLIC')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
832 consistent gets
0 physical reads
0 redo size
1116 bytes sent via SQL*Net to client
370 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Bu örnekte; OWNER ve OBJECT_TYPE kolonlarını içeren CBO TEST_IDEX1 indeksini kullanmamaktadır. Eğer her bir kolonun veri dağılımını hesaba kattığınızda(her bir kolon yaklaşık tabl o satır toplamının %40 ını içermektedir), full tablo taraması indeks kullanımına nazaran daha efektif gözükmektedir. CBO tarafından alınan bu çalışırma planı kararını analiz etmek için bu sorgu için aşağıdaki gibi bir 10053 izleme dosyası oluşturuyoruz.
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for ALLOBJECTS_TEST[ALLOBJECTS_TEST]
Column (#1):
NewDensity:0.000092, OldDensity:0.000009 BktCnt:5410, PopBktCnt:5408, PopValCnt:11, NDV:13
Column (#6):
NewDensity:0.000092, OldDensity:0.000009 BktCnt:5410, PopBktCnt:5408, PopValCnt:18, NDV:27
ColGroup (#0, Index) TEST_IDX
Col#: 1 6 CorStregth: -1.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Table: ALLOBJECTS_TEST Alias: ALLOBJECTS_TEST
Card: Original: 55095.000000 Rounded: 10291 Computed: 10290.84 Non Adjusted: 10290.84
Access Path: TableScan
Cost: 233.14 Resp: 233.14 Degree: 0
Cost_io: 232.00 Cost_cpu: 25496803
Resp_io: 232.00 Resp_cpu: 25496803
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (AllEqRange)
Index: TEST_IDX
resc_io: 386.00 resc_cpu: 9233056
ix_sel: 0.186784 ix_sel_with_filters: 0.186784
Cost: 386.41 Resp: 386.41 Degree: 1
Best:: AccessPath: TableScan
Cost: 233.14 Degree: 1 Resp: 233.14 Card: 10290.84 Bytes: 0
***************************************
Yukardaki izleme dosyasında full tablo taramasının maliyeti TEST_IDX indeksinin kullanılmasına nazaran daha düşük hesaplanmıştır. Ayrıca, bu izleme dosyasında OWNER ve OBJECT_TYPE kolon istatistiklerinin maliyet kıyaslama hesaplamasında kullanıldığı görülmektedir.TEST_IDX için indeks seçiciliği, indeksteki benzersiz(distinct) değerlerinin tablodaki toplam satır sayısı oranına dayalıdır (10291:55095). İndeks seçiciliği için bu zayıf bir değerdir ve full tablo taramasına nazaran daha yüksek maliyet değeri oluşturmaktadır.
Oysa, eğer optimizer bu kolonlar için bir grup olarak istatistiklere sahip olsaydı, belirtilen iki kolondan oluşan tek bir yüklemin çok düşük bir seçiciliğe sahip olduğunu bilecekti.
4. Bu istatistikleri sırayla toplamak için DBMS_STATS.CREATE_EXTENDED_STATS paketi kullanarak bir kolon grubu oluşturulur.
SQL> declare
test_name varchar2(30);
begin
test_name := dbms_stats.create_extended_stats(NULL,'ALLOBJECTS_TEST',
'(owner,object_type)');
end;
/
PL/SQL procedure successfully completed.
Aşağıdaki sorguyla bu kolon grubunun oluşturulduğu doğrulanır.
SQL> Select extension_name, extension
2 from user_stat_extensions
3 where table_name='ALLOBJECTS_TEST';
EXTENSION_NAME EXTENSION
------------------------------ ---------------------------
SYS_GRUXKS0YTS_56RQD1O0PEA514IY ("OWNER","OBJECT_TYPE")
5. Optimizer’ın sırayla bu yeni kolon grubunu kullanmaya başlamasına izin vermek için, DBMS_STATS kullanarak kolonların istatistikleri yeniden toplanır.
SQL> begin
dbms_stats.gather_table_stats(null,'ALLOBJECTS_TEST',
method_opt =>'for all columns size skewonly for columns
(owner,object_type) skewonly');
end;
/
PL/SQL procedure successfully completed.
Yeni oluşturulan kolon gruplarının istatistikler ve histogram ile görüntülendiği tablo kolon istatistikleri yeniden sorgulanır.
SQL> select column_name, num_distinct, histogram from
user_tab_col_statistics
where table_name = 'ALLOBJECTS_TEST';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER 13 FREQUENCY
OBJECT_NAME 31096 HEIGHT BALANCED
SUBOBJECT_NAME 0 NONE
OBJECT_ID 55095 NONE
DATA_OBJECT_ID 1181 HEIGHT BALANCED
OBJECT_TYPE 27 FREQUENCY
CREATED 2983 HEIGHT BALANCED
LAST_DDL_TIME 2619 HEIGHT BALANCED
TIMESTAMP 3020 NONE
STATUS 1 FREQUENCY
TEMPORARY 2 FREQUENCY
GENERATED 2 FREQUENCY
SECONDARY 1 FREQUENCY
NAMESPACE 11 FREQUENCY
EDITION_NAME 1 FREQUENCY
SYS_GRUXKS0YTS_56RQD1O0PEA514IY 94 HEIGHT BALANCED
16 rows selected.
Bu yeni kolon OWNER ve OBJECT_TYPE birleşik değerlerini göstermektedir. Bu sorgu ile kolon grubu üzerinde yüksek dengeli bir histogram oluşturulduğu görülmektedir.Ayrıca, bu kolon grubu için benzersiz(distinct) değerlerin sayısı 94 olarak listelenmektedir. Diğer bir şekilde, bu tablo içinde her iki kolon bileşimiyle 94 benzersiz değer bulunmaktadır.
SQL> select count(*)
from
(select distinct owner, object_type from allobjects_test);
COUNT(*)
----------
94
6. Şimdi aynı sorguyu daha önceki şekilde çalıştırarak, yeni kolon grubu ile sağlanan ilave istatistikler sebebiyle çalıştırma planı ve I/O nun büyük şekilde geliştiği görülecektir.
SQL> set autotrace on
SQL> select * from allobjects_test where owner = 'PUBLIC' and object_type = 'JAVA CLASS';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1510138874
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 6498 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ALLOBJECTS_TEST | 57 | 6498 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX | 57 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='JAVA CLASS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1116 bytes sent via SQL*Net to client
370 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
7. 10053 izleme dosyası analizi aşağıdaki gibidir.
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for ALLOBJECTS_TEST[ALLOBJECTS_TEST]
Column (#1):
NewDensity:0.000271, OldDensity:0.000009 BktCnt:5537, PopBktCnt:5537, PopValCnt:12, NDV:13
Column (#6):
NewDensity:0.000090, OldDensity:0.000009 BktCnt:5537, PopBktCnt:5535, PopValCnt:17, NDV:27
Column (#16):
NewDensity:0.001037, OldDensity:0.006993 BktCnt:75, PopBktCnt:68, PopValCnt:4, NDV:94
ColGroup (#0, VC) SYS_GRUXKS0YTS_56RQD1O0PEA514IY
Col#: 1 6 CorStregth: -1.00
ColGroup Usage:: PredCnt: 2 Matches Full: Using density: 0.001037 of col #16 as selectivity of unpopular value pred
#0 Partial: Sel: 0.0010
Table: ALLOBJECTS_TEST Alias: ALLOBJECTS_TEST
Card: Original: 55095.000000 Rounded: 57 Computed: 57.14 Non Adjusted: 57.14
Access Path: TableScan
Cost: 233.06 Resp: 233.06 Degree: 0
Cost_io: 232.00 Cost_cpu: 23708255
Resp_io: 232.00 Resp_cpu: 23708255
ColGroup Usage:: PredCnt: 2 Matches Full: Using density: 0.001037 of col #16 as selectivity of unpopular value pred
#0 Partial: Sel: 0.0010
ColGroup Usage:: PredCnt: 2 Matches Full: Using density: 0.001037 of col #16 as selectivity of unpopular value pred
#0 Partial: Sel: 0.0010
Access Path: index (AllEqRange)
Index: TEST_IDX
resc_io: 3.00 resc_cpu: 58754
ix_sel: 0.001037 ix_sel_with_filters: 0.001037
Cost: 3.00 Resp: 3.00 Degree: 1
Best:: AccessPath: IndexRange
Index: TEST_IDX
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 57.14 Bytes: 0
***************************************
Yoğunluk tablo satırlarındaki belli değerleri bulma olasılığı ile ilgilidir. Tabloda istatistikler toplandığında, kolon yoğunluğu hesaplanır ve kolon istatistiklerinde saklanır. Eğer değişik erişim yolları için optimizer tahminler oluştururken kolon histogramı uygun olursa, histogram içindeki bilgiye bağlı olarak yoğunluğu yeniden hesaplamak için tanımlı yüklem değerleri kullanılmaktadır.
Sorguda kullanılan yüklemler hiç bir satır getirmediği andan itibaren kolon grubu üzerindeki histogram yüklemleri, rağbet görmeyen yüklem değerlerini içeren yüklemleri gözönüne serer ve böylece düşük yoğunluğa sahip olurlar. Yukardaki optimizer dump dosyası, yüklemlerdeki değerlerin kolon grubu için 0.001037’lik bir yoğunluk üretimi hesapladığını göstermektedir (mavi ile belirtilmiş kısım).
Bu dump dosyası ayrıca, bu sorgu için maliyet hesaplamasını düzeltmek için kolon grubunun otomatik olarak kullanıldığını göstermektedir. Kolon grubunu yoğunluğunu, indeks için yeni seçicilik değeri olarak kullanarak bunu yapmaktadır. Seçicilik, satır setinden satır kesitini simgelemektedir. Böylece, indeks seçiciliği bir indeksi tararken okuma ihtiyacı duyacağı blok sayısını etkilemektedir ve az sayıda blok okunacağından dolayı daha düşük maliyet oluşmaktadır.
Yukardaki örnekte, indeks seçicilik değeri ix_sel: 0.186784 den ix_sel: 0.001037 e düştüğünden(kırmızı kısımda) itibaren indeks kullanım maliyet beklentisi aşırı oranda azalmıştır. Diğer bir deyişle, optimizer toplam satırların %18.6784 lık kısmının döneceği beklentisindedir. Aynı şekilde, 0.001037 olan yeni seçicilik değeri satırların %0.1037’lik bir kısmının döneceği anlamına gelmektedir. Bu yeni seçicilik değeri TEST_IDX indeksi kullanılan erişim yolu için çok daha düşük maliyet beklentisi ile sonuçlanmaktadır(yeşil kısım). Bu maliyet beklentisine bağlı olarak optimizer, indeks erişim yolu kullanılan erişim yolu planını seçecek ve I/O oranında belirgin bir iyileşme meydana gelecektir.
Otomatik Kolon Grubu Oluşturma Özelliği - Oracle 11.2.0.2
Bunun yanında Oracle 11.2.0.2 sürümü itibariyle gelen "Auto Column Group Creation" özelliği kullanılarak veritabanının tavsiye edeceği kolon gruplarını basitçe oluşturma imkanı olmaktadır. Bu özellik ile Oracle'ın tavisyeleri için mevcut işyükünün sistem tarafından izlenmesinin aktive edilmesi gerekmektedir.
begin
dbms_stats.seed_col_usage(null,null,900);
end;
/
Yukardaki komutu çalıştırarak veritabanın 15 dakika süresince işyükü izlenmesinin devreye alınmaktadır. DBMS_STATS.SEED_COL prosedürü kolon kullanım bilgilerini bu süre içerisinde izler ve SYS.COL_GROUP_USAGE$ görünümü içerisine kayıt eder.
Bu 15 dakika süre sonunda DBMS_STATS.REPORT_COL_USAGE prosedürüne sorgu ile yakalanan kolon kullanım bilgilerine erişilebilir. Aşağıda yukarıdaki bölümdeki örnekte kullandığımız allobjects_test adlı tablonun bu 15 dakikalık süre içerisinde kolon kullanım istatistiklerine erişilebilmektedir.
SQL> select dbms_stats.report_col_usage(user1,'allobjects_test') from dual;
Yukardaki sorgu sonucu alınan kolon kullanım raporuna baktıktan sonra, bu raporun GROUP_BY kolonu altındaki bilgiler ışığında tablonun yoğun kullandığı kolon kombinasyonları (Oracle tarafından gözlem sonunda CBO performansını iyileştirmesi kesinleşen kolon grup kombinasyonları) neticesinde otomatik olarak oluşturulması istenen kolon grupları için Oracle üzerinde aşağıdaki komut çalıştırılır.
SQL> select dbms_stats.create_extended_stats(user1,'allobjects_test') from dual;
Kayıtlar
0 yorum:
Yorum Gönder