6 Mayıs 2011 Cuma

DBMS_STATS Paketi Nedir ve Kullanımı / ANALYZE Komutu

DBMS_STATS

DBMS_STATS paketi 9i ile birlikte aramıza katılan ve Oracle veritabanının kurulumu ile gelen bir "built-in" pakettir. Bu paketin amacı Cost Based Optimizer (CBO) için gerekli istatistikleri, objeler üzerinde toplamaktır. DBMS_STATS paketini ya da ANALYZE ifadesini kullanarak istatistikleri toplamanız mümkündür ancak Oracle 9i ile birlikte DBMS_STATS paketinin kullanılması tavsiye edilmektedir. Bu konuyla ilgili bir OTN forumuna cevap yazmıştım. Buradan görebilirsiniz.

DBMS_STATS paketini kullanarak bir tablonun, indeksin veya cluster'ın fiziksel depolama karakteristikleri ile ilgili istatistikleri toplayabilirsiniz. Bu istatistikleri data dictionary olarak adlandırdığımız alanda tutulmaktadır ve gerektiği taktirde CBO tarafından direkt olarak kullanılmaktadır ve CBO bu istatistikleri göre bir SQL planı çıkartmaktadır. Bu bağlamda objelerin üzerindeki istatistikleri güncel olması kimi zaman ciddi önem taşımaktadır (performans açısından).

Oracle'ın bu paketin kullanımı için bize tavsiyede bulunduğunu söylemiştim ancak boş blokların, ortalama alanın istatistiklerinin toplanması gibi yukarıda belirttiğim aksiyonlar dışında kalan istatistik toplama işlemlerinde yine ANALYZE komutunu kullanmamız gerekmektedir.

İstatistikleri data dictionary dışında da tutulabilmektedir ve optimizer'ı etkilemeden manipüle edilebilmektedir. Toplanan istatistikleri başka bir veritabanına da kopyalayabilir, taşıyabilirsiniz. Birkaç DBMS_STATS paketi objesi örneği vermem gerekirse;

GATHER_INDEX_STATS
GATHER_TABLE_STATS
GATHER_SCHEMA_STATS
GATHER_DATABASE_STATS

İstatistiklerin temel amacının performansı arttırmak olduğunu söylemekte bir sakınca görmüyorum ve DBMS_STATS paketi yalnızca optimizer istatistikleri ile ilgili işlemlere izin vermektedir. Oracle'ın GATHER_STATS_JOB isminde bir otomatik istatistik toplayan görevi bulunmaktadır ve hafta içi 22:00'den sonra ve hafta sonu sabah saatlerinde otomatik olarak devreye girerek, veritabanı istatistiklerini toplamaktadır. DBMS_STATS paketiyle toplamak istediğiniz diğer obje istatistikleriyle ilgilenebilirsiniz.

GATHER_DATABASE_STATS

Söz Dizimi;

DBMS_STATS.GATHER_DATABASE_STATS(
estimate_percent NUMBER DEFAULT to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
statown VARCHAR2 DEFAULT NULL,
gather_sys BOOLEAN  DEFAULT TRUE,
no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
obj_filter_list ObjectTab DEFAULT NULL);

estimate_percent:Tahmin edilecek satırların yüzdesidir. 0.000001 - 100 arasında bir yüzdelik değer almaktadır. Boş değer verirseniz Oracle otomatik olarak bir yüzde atayacaktır ve bunu daha iyi istatistik seviyesi için yapacaktır.
block_sample: Rastgele satır örneklemesi yerine rastgele blok örneklemesinin kullanılıp kullanılmayacağına karar verdiğimiz argümandır. Rastgele blok örneklemesi daha efektif çalışmaktadır ancak verileri disk üzerinde rastgele bir şekilde dizilmediyse örnekleme değeleri bir şekilde ilişkilendirilebilir.
method_opt:
                      FOR ALL [INDEXED | HIDDEN] COLUMNS [boyut_ifadesi]
                      FOR COLUMNS [boyut_ifadesi] column [boyut_ifadesi], column [boyut_ifadesi], ...
boyut_ifadesi (size_clause) := SIZE {integer | REPEAT | AUTO | SKEWONLY}
REPEAT: Sadece histograma sahip sütunlar üzerinde histogramları toplar
AUTO: Veri dağılımı ve iş yüküne göre yapılan bir dağılım ile histogramların hangi sütun üzerinde toplanacağına Oracle tarafından karar verilir.
SKEWONLY: Sadece veri dağılımına göre histogramların hangi sütun üzerinde toplanacağına Oracle tarafından karar verilir.
degree: Paralellik derecesini belirleyebilirsiniz. Varsayılan değeri NULL'dır.
granularity: Sadece partitioned bir tablo için geçerlidir ve istatistiklerin aralıklarının nasıl toplanacağını belirler.
                     ALL: Bütün istatistiklerini (subpartition, partition, global) toplar.
                     AUTO: Partition yapısına göre hangi istatistiklerin toplanacağına karar verilir. Varsayılan değerdir.
                     DEFAULT: Global ve partition seviyesindenki istatistikleri toplarsınız. Bu seçenek artık desteklenmemektedir ancak yine de dokümante edilmektedir.
                     GLOBAL: Sadece global istatistiklerini toplar.
                     GLOBAL AND PARTITION: Global ve partition seviyesindeki istatistikleri toplarsınız. Subpartition'lar devreye alınmaz.
                     PARTITION: Sadece partition'ların istatistikleri toplanır.
                     SUBPARTITION: Sadece subpartition'ların istatistikleri toplanır.
cascade: İstatistiklerin eşzamanlı olarak index'ler üzerinde toplanmasını sağlar. GATHER_INDEX_STATS çalıştırmakla aynı şeydir ve aynı görevi tamamlamanızı sağlar.
stattab: Kullanıcı tarafından tanımlanmış istatistik tablosunun gösterilerek, istatistik değerlerinin burada toplanmasını ifade edebilirsiniz.
statid: stattab içerisindeki istatistiklerle bağlantı kurulabilmesi için verilen bir id'dir.
option: İstatistikler toplanırken kullanılabilecek diğer opsiyonları tanımlayabilirsiniz.
                     GATHER: Verilen şema için bütün obje istatistiklerini toplarsınız.
                     GATHER AUTO: Sadece gerekli istatistiklerin toplanmasını sağlarsınız. Oracle arka planda hangi objenin istatistiklerinin toplanması gerektiğini hesaplar. GATHER_AUTO seçeneğini kullandığınız zaman, stattab, statid, objlist ve statown dışında kalan bütün argümanlar gözardı edilecektir.
                     GATHER_STALE: Yalnızca bayat (stale) istatistiklerin toplanmasını sağlarsınız. Bayat değeri varsayılan olarak %10'dur ve *_tab_modifications tablosuna bakılarak bir değer hesaplanır.
                     GATHER_EMPTY: İstatistikleri hiç toplanmamış olan istatistikleri toplayabilmenize yarar.
                     LIST AUTO: GATHER_AUTO ile toplanacak istatistikleri görmenize yarayan bir komuttur ve bir liste oluşturulur.
                     LIST STALE: Bayat objelerin bulunmasını sağlar ve *_tab_modifications data dictionary görüntüsüne bakılarak bir list oluşturulmasını gerçekleştirir.
                     LIST EMPTY: İstatistikleri hiç olmayan objeleri görüntülersiniz.
objlist: Bayat ya da boş istatistikleri olan objelerin listelenmesidir.
statown: Stattab'ı olan şemayı gösterir.
gather_sys: Yalnızca SYS kullanıcısının sahip olduğu objeler üzerinde istatistikleri toplarsınız.
no_validate: TRUE olarak atandığı zaman bağımlı cursor'ların devre dışı kalmasını engeller.
obj_filter_list: Obje filtrelerinin listelenmesini sağlar.

Kullanım Notları;

External tablolar için istatistik toplanması bu prosedür ile sağlanamamaktadır.

Ayrıcalıklar;

ORA-20000: Insufficient Privileges
ORA-20001: Bad Input Value

GATHER_DICTIONARY_STATS

Söz Dizimi;


DBMS_STATS.GATHER_DICTIONARY_STATS (
comp_id VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER   DEFAULT to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN  DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER AUTO',
objlist OUT   ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
obj_filter_list ObjectTab DEFAULT NULL);

Kullanım Notları;

Bu prosedürü çalıştırabilmek için SYSDBA veya ANALYZE ANY DICTIONARY ya da ANALYZE ANY sistem haklarına sahip olmanız gerekmektedir.

Ayrıcalıklar;

ORA-20000: Index does not exist or insufficient privileges
ORA-20001: Bad input value
ORA-20002: Bad user statistics table, may need to upgrade it


GATHER_FIXED_OBJECTS_STATS

Söz Dizimi;



DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE'))); 

Kullanım Notları;

Bu prosedürü çalıştırabilmek için SYSDBA veya ANALYZE ANY DICTIONARY sistem haklarına sahip olmanız gerekmektedir.

Ayrıcalıklar;

ORA-20000: Insufficient privileges
ORA-20001: Bad input value
ORA-20002: Bad user statistics table, may need to upgrade it


GATHER_INDEX_STATS

Söz Dizimi;


DBMS_STATS.GATHER_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER   DEFAULT to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
degree NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);

Ayrıcalıklar;

ORA-20000: Insufficient privileges
ORA-20001: Bad input value

GATHER_SCHEMA_STATS

Söz Dizimi;



DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER   DEFAULT to_estimate_percent_type  (get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN  DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT      ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
obj_filter_list  ObjectTab DEFAULT NULL);

Kullanım Notları,
External tablolar için istatistik toplanması bu prosedür ile sağlanamamaktadır.

Ayrıcalıkları;
ORA-20000: Schema does not exist or insufficient privileges
ORA-20001: Bad input value

GATHER_SYSTEM_STATS

Söz Dizimi;


DBMS_STATS.GATHER_SYSTEM_STATS (
gathering_mode VARCHAR2 DEFAULT 'NOWORKLOAD',
interval INTEGER  DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);

Kullanım Notları;

Bir Oracle veritabanı kurulumundan sonra Oracle, NOWORKLOAD opsiyonu ile veritabanı ve tablespace istatistiklerinin toplanmasını tavsiye etmektedir.

Ayrıcalıkları;


ORA-20000: Object does not exist or insufficient privileges
ORA-20001: Invalid input value
ORA-20002: Bad user statistics table; may need to be upgraded
ORA-20003: Unable to gather system statistics
ORA-20004: Error in the INTERVAL mode: system parameter job_queue_processes must be >0

GATHER_TABLE_STATS

Söz Dizimi;


DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN  DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT  to_no_invalidate_type (get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN  DEFAULT FALSE);

Kullanım Notları;
Indeks istatistiklerinin toplanmasını paralel olarak çalıştırabilirsiniz ancak cluster, domain ve join indeksleri için yapılamıyor.

Ayrıcalıkları;

ORA-20000: Table does not exist or insufficient privileges
ORA-20001: Bad input value

ANALYZE

ANALYZE komutunu istatistik toplamak için kullanıyoruz;

- Bir indeks, indeks partition, tablo, tablo partition, index-organized table, cluster veya scalar objelerle ilgili istatistikleri toplamak ya da silmek için,
- Bir indeks, indeks partition, tablo, tablo partition, index-organized table veya cluster'ın yapısının doğrulanması için, (DBMS_STATS paketi ile yapının doğrulanmasını gerçekleştiremezsiniz),
- Migrated ya da chained satırların bir tablo ya da cluster üzerinde bulunması.

Önkoşulları;

- Analizi yapılacak şema objesi local olmalıdır veya sizin şemanız içinde bulunmalıdır ya da ANALYZE ANY sistem yetkisine sahip olmalısınız. 
- Chained satırları listelemek isterseniz bu tablo sizin şemanızda olmalıdır ya da INSERT yetkisine sahip olmalısınız veya INSERT ANY TABLE sistem hakkınız olmalıdır.
- Eğer bir partitioned tabloyu doğrulamak isterseniz, o tablo üzerinde INSERT hakkınızın olması gerekiyor ya da INSERT ANY TABLE hakkınızın bulunması.

Örnek Söz Dizimleri;

ANALYZE TABLE ogan_deneme DELETE STATISTICS;
ANALYZE TABLE ogan_deneme COMPUTE STATISTICS;
ANALYZE INDEX ogan_deneme_idx VALIDATE STRUCTURE [ONLINE];
ANALYZE TABLE ogan_deneme VALIDATE STRUCTURE [CASCADE];

ANALYZE TABLE ogan_deneme LIST CHAINED ROWS INTO chained_rows;

SQL> SELECT owner_name, table_name, head_rowid, analyze_timestamp
FROM chained_rows
ORDER BY owner_name, table_name, head_rowid, analyze_timestamp;

SONUÇ;

Kullandığınız veritabanı bir 9i, 10g veya 11g veritabanı ise ve herhangi bir objenin yalnızca istatistiğinin toplanması ile ilgili işlemler yapmak istiyorsanız DBMS_STATS paketini kullanmanız şiddetle tavsiye edilmektedir. Bunun nedeni daha sağlam ve çok yönlü bir istatistik toplama aracı olmasıdır. İstatistik toplamak yerine bir tablonun ya da indeksin yapısını baştan düzenlemek istiyorsanız ANALYZE komutunu kullanmanız gerekiyor. Aslında DBMS_STATS'ın yaptıklarını (yalnızca istatistik toplamak anlamında) ANALYZE komutu da yapabilmektedir ancak ANALYZE'ın obje yapısı doğrulaması işlemini DBMS_STATS yapamamaktadır. DBMS_STATS, ANALYZE'ın yerini almıştır diyebilmemiz için bu paketin bütün ANALYZE komutlarını yapabiliyor olması gerekmektedir fakat böyle bir durum yoktur. ANALYZE komutu hala kullanılmaktadır. Benim de tavsiyem istatistiklerin toplanması için DBMS_STATS paketinin kullanılmasıdır.

İyi çalışmalar.

Ogan

1 yorum:

Adsız dedi ki...

Çok kıymetli bilgiler... Teşekkürler, başarılarınızın devamını dilerim.

Takip et: @oganozdogan