26 Mayıs 2011 Perşembe

DBMS_AUDIT_MGMT Paketi ve Audit Trail Bilgilerinin Taşınması

DBMS_AUDIT_MGMT

Oracle veritabanı 11g R1 ile audit işlemleri varsayılan olarak açık şekilde gelmekteydi. Oracle veritabanı 11g R2 ile birlikte audit trail bilgilerinin daha iyi yönetilmesi sağlanmıştır. Bunu, DBMS_AUDIT_MGMT paketi ile sağlayabilmekteyiz.

SET_AUDIT_TRAIL_LOCATION bir prosedür ve DBMS_AUDIT_MGMT paketine aittir. Bu prosedür ile standart ve/veya FGA (Fine Grained Auditing) lokasyonunu değiştirebiliyoruz. OS seviyesinde bir değişiklik yapabilmek şu an için mümkün değildir.

İlgili prosedür iki adet argüman almaktadır;

1) AUDIT_TRAIL_TYPE: Hangi tipteki audit trail'in yerinin değiştirileceği bilgisidir.
2) AUDIT_TRAIL_LOCATION_VALUE: Hangi tablespace'a audit trail'in yerinin taşınacağını bilgisidir.

AUDIT_TRAIL_TYPE için 3 sabit bulunmaktadır;

1) DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standart audit trail (AUD$)
2) DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: FG audit trail (FGA_LOG$)

3) DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Hem standart hem de FGA trail

Küçük bir örnek ile devam edersek;

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME IN ('AUD$', 'FGA_LOG$');

TABLE_NAME                            TABLESPACE_NAME
------------------------------------------------------------------------

AUD$                                            SYSTEM
FGA_LOG$                                  SYSTEM

--> Standart ve FGA bilgilerinin system tablespace'inde bulunduğunu ve herhangi bir audit aksiyonu ile buralara log'lanacağını gördük. Şimdi ise bu tabloların bulunmasını istediğimiz başka bir alanı, yani tablespace'i göstereceğiz ancak önce ilgili tablespace'i yaratmamız gerekiyor;

SQL> CREATE TABLESPACE 'OGAN_AUDIT'
DATAFILE '/u01/oracle/ogan_audit.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M;

--> OGAN_AUDIT isminde bir tablespace yarattım ve 100 MB büyüklüğünde bir de datafile tanımladım. Şimdi ise ilgili prosedür ile hem startdart hem de FGA audit bilgilerini taşıyacağım;

SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'OGAN_AUDIT');
END;
/

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME IN ('AUD$', 'FGA_LOG$');

TABLE_NAME                            TABLESPACE_NAME
------------------------------------------------------------------------
AUD$                                           OGAN_AUDIT
FGA_LOG$                                 SYSTEM

SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'OGAN_AUDIT');
END;
/

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME IN ('AUD$', 'FGA_LOG$');

TABLE_NAME                            TABLESPACE_NAME
------------------------------------------------------------------------
AUD$                                           OGAN_AUDIT
FGA_LOG$                                 OGAN_AUDIT

--> Her iki tipteki audit trail bilgilerinin bundan sonra OGAN_AUDIT tablespace'inde bulundurulmasını sağladım. Şimdi ise geri alacağım.

SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'SYSTEM');
END;
/

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME IN ('AUD$', 'FGA_LOG$');

TABLE_NAME                            TABLESPACE_NAME
------------------------------------------------------------------------
AUD$                                           SYSTEM
FGA_LOG$                                  SYSTEM

Önceki audit trail bilgilerinin taşınması, işletim sistemi kaynaklarına ve önceki bilgilerin ne kadar fazla veya az olduğuna göre süre bakımından değişebilmektedir.

İyi çalışmalar.

Ogan

9 Mayıs 2011 Pazartesi

SQL Nedir, Parse Call ve Plan / Optimizer

SQL

SQL hakkında 2008 yılından kalma bir yazı yazmıştım. Buradan ulaşabilirsiniz. Bu yazımda biraz SQL'in ne olduğundan, hangi amaçlara hizmet ettiğinden bahsedeceğim ve ardından optimizer ile olan ilişkisinden, SQL planından ve SQL çalışma mantığından bahsedeceğim.

Bütün veritabanı operasyonları SQL sorguları aracılığı ile yapılmaktadır ve bir bilgisayar dilidir. Birçok insana göre bir programlama dili değildir fakat bana göre programlamaya yöneliktir. SQL bir çeşit arayüzdür ve veritabanındaki verilerle ve diğer bilgilerle konuşmamızı sağlar. Tıpkı iki farklı milliyetten olan insanın kendi aralarında İngilizce konuşarak anlaşması gibi. SQL kullanarak;

- Objeler yaratabilir, değiştirebilir ve düşürebiliriz.
- Tablolara satılar ekleyebilir, silebilir ya da güncelleyebiliriz.
- Verinin sorgulanmasında kullanabiliriz.
- Veritabanı objelerine olan erişmin kontrol edilmesini sağlayabiliriz.
- Veritabanının eş zamanlılığını ve tutarlılığını garanti altına alabiliriz.

SQL sorgularını bir C veya Java kodu içerisinde de kullanabilmek ve bu program aracılığı ile veritabanı ile konuşabilmek mümkündür.

SQL Veri Erişimi

Bütün programlama dillerini iki ana başlık altında toplayabilmek mümkündür. Dekleratif ve prosedürel diller. Dekleratif diller neyin yapılacağını, prosedürel diller ise işlerin nasıl yapılacağını anlatmak için kullanılırlar. SQL bir dekleratif dildir ve SQL derleyicisi (compiler) bir prosedür yaratarak, veritabanı üzerinde istenen işlerin tamamlanmasını sağlamaktadır.

SQL sorguları sayesinde mantıksal seviyede sorgulama yapılabilmektedir. Oldukça temel bir SQL sorgusunun örneğini verelim;

SQL> SELECT first_name, last_name
           FROM hr.employees
           WHERE first_name = 'John'
           ORDER BY first_name, last_name;

WHERE koşuluna "predicate" yani yüklem denmektedir çünkü bir cümlenin yüklemi gibi çalışmaktadır. Veritabanı WHERE koşulunu sağlayan bütün verileri bize getirecektir. Bütün SQL sorguları optimizer'ı kullanırlar. Optimizer ise kullanıcı tarafından girilen sorguları daha efektif halde çalıştırmak için vardır. Bir Oracle veritabanı çeşitli yöntemlerle optimizer sonuçlarının iyileştirilmesini sağlamaktadır.

SQL Standartları

ANSI ve ISO standartları üzerine geliştirilen SQL, Oracle tarafından da kabul edilerek geliştirilmiştir. Bu standartlar her veritabanında aynıdır. ANSI ve ISO'nun SQL standartları ilişkisel bütün veritabanları tarafından kullanılmaktadır.

Oracle SQL'i, standart SQL'e göre çok daha fazla özelliklere sahiptir. Örneğin Oracle SQL'ine özel tablo birleştirme operasyonu ve işareti (+). SQL*Plus, SQL Developer ve Enterprise Manager ANSI/ISO standartlarında SQL sorgularını çalıştırmanızı sağlamaktadır.

SQL İfadelerine Genel Bakış

Oracle veritabanındaki bütün operasyonların bir SQL sorgusu aracılığı ile gerçekleştirildiğini daha önce ifade etmiştim. Bir SQL ifadesi tanımlayıcılar, parametreler, argümanlar, isimler, veri tipleri ve SQL ayrılmış kelimeleri (reserved words) kullanmaktadır. Bir not olarak şunu ekleyebiliriz bu noktada; SQL ayrılmış kelimelerinin özel anlamı bulunmaktadır ve başka bir amaç için kullanılmamalıdır. Örneğin SELECT, WHERE gibi ayrılmış kelimeleri tablo adı olarak kullanmamalıyız.

SQL> SELECT first_name, last_name
           FROM hr.employees;

Oracle SQL ifadeleri farklı biçimlerde konumlandırılmaktadır. Bunlar;

Data Definition Language (DDL)

Veritabanının yapısını değiştiren DDL komutlarıdır. DDL ile yapabileceğiniz işlemler;

- CREATE, DROP, ALTER komutları ile veritabanı objelerinin yapılarının değiştirilmesi. Kullanıcılar üzerindeki işlemler de dahildir.
- TRUNCATE komutu ile objelerin kendisini kaldırmadan içerisinde bütün verilerin silinmesinin sağlanması.

SQL> TRUNCATE TABLE hr.employees;
SQL> CREATE USER ogan IDENTIFIED BY deneme;

NOT: TRUNCATE komutu DELETE komutunun yarattığı kadar undo verisi yaratmaz aksine hiç undo verisi yaratmaz. DELETE komutundan daha hızlıdır ve DELETE trigger'larının tetiklenmesini gerçekleştirmez.

- GRANT, REVOKE yetkilerinin kullanılması.
- AUDIT ve NOAUDIT komutları ile denetleme mekanizmalarının devreye alınması, devreden çıkartılması.
- COMMENT komutu ile data dictionary'e yorum eklemek için kullanılır.

DDL komutları ile objeye erişen uygulamanın yapısını değiştirmeden objenin özelliklerini değiştirebiliyorsunuz. Aynı şekilde veritabanındaki kullanıcılar etkilenmeden yine objenin özelliklerini değiştirebiliyorsunuz.

SQL> GRANT SELECT ON hr.employees TO ogan WITH GRANT OPTION;
SQL> INSERT INTO hr.employees (last_name, first_name) VALUES ('ozdogan','ogan');
SQL> REVOKE SELECT ANY DICTIONARY FROM ogan;
SQL> DROP TABLE hr.employees;

DDL komutlarından sonra gizli COMMIT ya da ROLLBACK komutu çalıştırılmaktadır ve DDL komutlarından önce de yine gizli COMMIT koşulmaktadır. DDL komutlarını bir SAVEPOINT bloğunun içerisine sokarsanız dikkatli olmanız gerekir zira DML komutlarından sonra koşacağınız bir DDL komutu ile gizli COMMIT koşulacağından geride kalan işlemlerin tamamı, aynı transaction içerisinde COMMIT edilmiş sayılırlar, buraya dikkat.

Data Manipulation Language (DML)

DML komutlarını varolan veritabanı objelerinin sahip olduğu verileri manipüle ederken kullanmaktayız. DDL komutları bize veritabanının yapısının değiştirilmesinin şansını sunarken, DML içerğin değiştirilmesinin şansını sunmaktadır. Örneğin ALTER TABLE bir DDL komutu olup objenin özelliğini ve yapısını değiştirirken, INSERT komutu objenin içerisine veri eklemeye yaramaktadır.

Genelde kullanılan DML komutları arasında;

- SELECT komutunu kullanarak tablo ya da görüntü (view) veritabanı objelerinden bilgi çekmek için kullanılır.
- INSERT komutu ile bir görüntü ya da tabloya veri girişinde bulunabilirsiniz.
- UPDATE DML komutunu kullanarak varolan sütun değerlerinin değiştirilmesini sağlayabilirsiniz.
- MERGE INTO komutunun sağladığı update veya insert özelliklerinden faydalanmak.
- DELETE ile verilerin silinmesinin sağlanması.
- EXPLAIN PLAN ile SQL execution plan (çalıştırma planı) çıkartılması.
- LOCK TABLE ile bir tablo üzerinde kilit almak için kullanılmaktadır.

SQL> DELETE hr.employees
           WHERE last_name = 'King';
SQL> UPDATE hr.employees 
           SET salary = 10000
           WHERE employee_id = 100;

Birden çok DML'in arka arkaya girilmesi ile bir transaction oluşmaktadır. Transaction'la ilgili yazdığım yazıya buradan ulaşabilirsiniz. DDL komutlarında olduğu gibi hiçbir DML komutu gizli COMMIT veya ROLLBACK çalıştırmamaktadır. Genelde verilen örnek banka işlemleridir. Hesaptan almak ve karşıya aktarmak bir transaction'dır ve işlem bittikten sonra COMMIT gönderilmektedir. Bu DML bloğu sık kullanılan bir örnektir.

Transaction Control Statements 

Transaction kontrol ifadeleri ile DML ifadelerinin kontrol edilmesini sağlayabilmekteyiz;

- COMMIT komutu ile DML ifadelerinin sağlama alınması, kesinleştirilmesi sağlanmaktadır. COMMIT komutu ile bir DML transaction'ı sonlandırılmaktadır. 
- ROLLBACK ya da ROLLBACK TO SAVEPOINT komutu ile bir transaction içerisinde geriye gidilebilir ya da bütün bir transaction'dan vazgeçilebilir. 
- SAVEPOINT komutuyla ROLLBACK yapabileceğiniz noktaları tanımayabiliriz.
- SET TRANSACTION komutu ile başlattığımız transaction'ın özelliklerini değiştirebiliriz. 
- SET CONSTRAINT ile bir kısıtlayıcının (constraint) özelliklerini değiştirebiliriz.

SQL> SET TRANSACTION NAME 'ogan_transaction';
SQL> SAVEPOINT ogan_savepoint_1;
SQL> COMMIT COMMENT 'ogan_guncelleme';

Session Control Statements

Bir session (bağlantı) hakkındaki bilgileri değiştirdiğimiz, güncellediğimiz SQL ifade tipi session kontrol ifadeleridir. 

- ALTER SESSION komutu ile SQL trace özelliğini açıp kapatabiliriz. 
- SET ROLE ile geçerli bağlantı için rolleri açıp kapatabiliriz.

SQL> ALTER SESION SET SQL_TRACE = TRUE;
SQL> SET ROLE ALL EXCEPT ogan_role_man;

System Control Statements

Veritabanı instance'ının özelliklerinin değiştirilmesi için kullanılmaktadır. Bir tane sistem kontrol ifadesi vardır o da ALTER SYSTEM'dir. 

SQL> ALTER SYSTEM SWITCH LOGFILE; --> Redolog grup geçisini sağlar.
SQL> ALTER SYSTEM KILL SESSION '100,203' --> 100 session id'li, 203 serial numaralı bağlantıyı öldürür.

Embedded SQL Statements

Gömülü SQL ifadeleri DDL, DML için değil, prosedürel dil programı içerisinde kullanılır.

- DECLARE CURSOR, OPEN, CLOSE ile Cursor'ların tanımlanması, tahsis edilmesi ya da bırakılması için kullanılır.
- DECLARE DATABASE, CONNECT komutları ile bağlanılacak veritabanının belirlenmesi.
- DECLARE komutu ile değişkenlerin atanması.
- DECSRIBE ya da DESC (synonym) ile objelerin tanımlarının izlenmesi.
- FETCH ile veritabanından veri alınmasını sağlayabiliriz.

Optimizer'a Genel Bakış

Geçmişte yazdığım hiçbir optimizer yazısı olmadığını gördüm ve açıkçası biraz üzüldüm. Çok ciddi ve önemli bir konu olduğunu düşünüyorum.

Optimizer ya da diğer isimleri ile query optimizer veya cost based optimizer bir veritabanı parçasıdır ve SQL ifadelerinin nasıl koşulması gerektiğinin yolunun çıkartılmasını sağlar. Bütün SQL ifadeleri optimizer'ı kullanmaktadır. "Cost" adı verilen bir miktar hesaplanmakta ve SQL ifadeleri bu yönden koşturulmaktadır. Cost dediğimiz öğenin nasıl hesaplandığını açıklayacağım.

Optimizer Kullanımı

Bir DML ifadesinin çalıştırılması için Oracle veritabanı birden fazla aşama devreye sokmaktadır. Mesela bir tablo ya da indekse hangi yollardan ve sırayla erişileceği işleri değiştirebilir. Veritabanının, verilen SQL sorgusunu hangi yollardan çalıştıracağı ve istenilen veriyi getireceği, sorgu performansını oldukça etkilemekte ve sorgunun ne kadar hızlı cevap vereceğini değiştirmektedir. Bu sebeplerden dolayı optimizer'ın hesaplayacağı cost (maliyet) ve sizin sorgunuzun algortiması eşittir ne kadar hızlı sorgu yanıtıdır.

Cost Based Optimizer birkaç yol ile sorgunun nasıl çalıştırılacağına karar vermektedir. Bu koşullara sorgunun algortiması ve yapılandırılması, access path (erişim yolları), sistem veya obje istatistiklerinin güncelliği ve hint (ipucu). İpuçları ve erişim yolları hakkında daha detaylı konuşacağım. Herhangi bir SQL sorgusu için Oracle Optimizer aşağıdaki işlemleri gerçekleştirmektedir;

- Predicate (yüklem - WHERE koşulu) yapılandırması ve sorgunun bütün koşulları.
- Integrity constraint'lerinin (sınırlayıcı) gözden geçirilmesi.
- Optimizer hedefleri ve seçimleri.
- Erişim yollarının saptanması.
- Join sıralamasının hesaplanması.

Optimizer bu işlemleri yaparken her bir erişim yolu için bir cost, yani maliyet hesaplar. Bu maliyetlerin tamamına ve erişim yollarının sıralanmış haline "Execution plan" denmektedir. Çalıştırma planı bir Optimizer için yol haritasıdır ve SQL sorgularını nasıl ve hangi yollardan çalıştırması gerektiğini algılamaktadır. Alternatif erişim yolları ile oluşan çalıştırma planları arasından maliyeti en düşük olan plan geçerli kılınır ve ilgili SQL sorgusu çalıştırıldığı zaman o plan üzerinden çalıştırılır. Bir SQL sorgusunu çalıştırmadan da çalıştırma planını görebiliyorsunuz.

SQL> EXPLAIN PLAN FOR SELECT first_name, last_name FROM hr.employees;

Bir veritabanı yöneticisi Optimizer'ın çalışman mantığı üzerinde etkili olabilmektedir. Bunu yapabilmenin bir yolu SQL sorgusu içerisinde bulunan obje veya objelere ait sistemsel istatistiklerin toplanmasıdır. İstatistikler güncel olduğu zaman ilgili objeler hakkında Oracle veritabanı net bir bilgiye sahip olacak ve Optimizer'ın tavırlarında bir değişiklik göreceksiniz. Örneğin bir tablonuz var ve içerisinde bir milyon satır bulunmakta. İstatistikleri günceldir. Tablodaki 1 kayıt haricinde bütün satırları sildiğiniz ve COMMIT ettiğiniz zaman Oracle bu işlemi yapacaktır ancak Optimizer'ın haberi olmayacaktır. Optimizer'ın baktığı tablodaki istatistiklerin artık eski sayıldığını düşünürseniz, Optimizer tabloda hala bir milyon kayıt olduğunu zannedecektir. Bu durum çalışma mantığı üzerinde yanlışlıklara neden olacak, belki de FTS (Full Table Scan) yapılması gerekirken hala bir milyon kayıt varmış gibi indeks taraması yapılacaktır. İşte bu durumda performans ciddi olarak etkilenebilecektir.

Optimizer'ın hedefini aşağıdaki şekillerle değiştirebilirsiniz (örnektir);

- ALL_ROWS hint'ini kullanarak son kullanıcı uygulaması için en son satıra, olabilecek en hızlı şekilde gidilmesi sağlanır.
- FIRST_ROWS hint'ini kullanarak Optimizer'dan son kullanıcı uygulamasına ilk satırı, olabilecek en hızlı şekilde getirmesi istenir.

Optimizer Bileşenleri

Bir Optimizer'ın bileşenleri varmadan önce girdisi yapılan bileşene "parsed query" yani parse edilmiş SQL sorgusu denmektedir. İlerleyen bölümlerde parsed sorgudan bahsediyor olacağım. Optimizer bileşenlerini aşağıdaki diyagramda görebilirsiniz;


Optimizer sırasıyla aşağıdaki aşamalardan geçerek ve bileşenlerini kullanarak işini yapmaktadır;

1) Optimizer parser'dan geçmiş parsed SQL sorgusunu kabul eder ve hint ve erişim yollarının yardımı ile de potansiyel planları oluşturmaya çalışır.
2) Data Dictionary'daki istatistikler baz alınarak bütün potansiyel SQL plan'larının maliyetleri çıkartılır.
3) Optimizer en düşük olan maliyetli planı bulur ve kullanır.

Query Transformer: Parse edilmiş sorgunun yapısının değiştirilmesine ihtiyaç varsa ve bu faydalı bir çalışma olarak kabul edilmekteyse Query Transformer sorgunun yapısını değiştirebilir. Bunun amacı daha iyi execution plan çıkartmaktır.
Estimator: Bu bileşenin görevi maliyet (cost) hesaplamaktır. Şimdi bu maliyetlerin nasıl hesaplandığı ve nelere bakıldığını anlatacağım.
a) Seçilebilirlik; Yüklemler kontrol edilir ve sorgunun neleri talep ettiği gözden geçirilir. WHERE koşulundakiler ölçülmektedir.
b) Kardinalite; Satır kümesinden gelen satırların sayısını simgeler.
c) Maliyet (Cost); Cost Based Optimizer'ın en önemli noktalarından birisi olan maliyeti, yapılan işin ya da kaynağın miktarını ölçmek için kullanıyoruz. Disk I/O, CPU kullanımı ve bellek tüketimi işin üniteleridir ve maliyeti oluşturur.
Plan Generator: Bir diğer bileşen olan plan oluşturucu girilen sorguyla ilgili olarak birden fazla plan çıkartmaya çalışmaktadır. Ardından en düşük olanı seçmektedir. Optimizer her bir alt sorgu için (subquery) ayrı alt planlar oluşturmaktadır. Başka tipte plan oluşturabilmek için farklı erişim yolları, join operasyonları ve join sıralamaları gerçekleştirmektedir. Bu farklılıklarda günün sonunda farklı birer plan olarak çıkartılır ve maliyetlerine bakılır.

Access Paths (Erişim Yolları)

Erişim yolları veritbanındaki veriye nasıl ulaşılması gerektiğini gösteren yol haritalarıdır. Örnek olarak bir sorgunun indeks üzerinden getirilmesi, indeks üzerinden ilerlemeyen sorguya göre farklıdır. Bir genelleme olarak küçük bloklardaki satırlara index erişim yolu ile gitmek daha mantıklıyken, büyük bir tablodaki bütün verilere ulaşırken tablonun hepsini okumak daha mantıklı olabilir. Bir Oracle veritabanı, veriye ulaşırken farklı erişim yolları kullanmaktadır ve aşağıda listelenmektedir;

1) Full Table Scans: Bu tipte aramlarada bütün tablonun satırları teker teker okunur ve yüklemdeki isteğe göre filtrelenir. Veritabanı bir segmentteki bütün blokları sırasıyla okur, buna high water mark altında kalanlar da dahildir.
2) Rowid Scans: Rowid bir satır için adres bilgisini içermektedir ve blok içerisinde nerede bulunduğunu göstermektedir. Veritabanı ilk olarak seçilen satırların rowid'sine ulaşır ve bunu ya WHERE koşulundaki isteğe göre ya da bir indeks range scan ile yapabilir. Ardından seçilen her bir satırı rowid'lerinden getirir.
3) Index Scans: SQL sorgusu tarafından istenen değerlerin bir indeks araması ile getirilmesi işlemidir. Eğer bir ifade içerisinde yalnızca indeksin bulunduğu sütunlar talep edilmiş ise bir indeks taraması ile direkt olarak bu indeks içerisindeki değerlere ulaşılmaktadır.
4) Cluster Scans: Bir indeksli tablo cluster'dan veri erişimi sağlayabilmek için kullanılan erişim yoludur. Rowid'leri bulunarak yapılan bir taramadır.
5) Hash Scans: Bir hash cluster'dan veri erişimi sağlayabilmek için kullanılan erişim yoludur.

Cost Based Optimizer geçerli ve olabilecek erişim yollarından, tahmin edilen en düşük maliyete sahip olan plan kombinasyonunu seçer ve uygulamaya sokar.

Optimizer İstatistikleri

Bir Oracle veritabanındaki objelerin detaylarının bütününe Optimizer istatistikleri denmektedir. Bildiğimiz klasik normal dağılım, regresyon, korelasyon gibi istatistikler terimlerle bir ilgili bulunmadığını düşünmekteyim. Oracle iş içleri gereği bir istatistiki hesaplamanın yapılıp yapılmadığını bilmiyorum ancak dokümantasyona göre "istatistiksel olarak doğru" ve "dağılım" kelimeleri geçmektedir. Bu bağlamda ne tipte bir istatistiki operasyonun gerçekleştirildiğini net olarak kestiremiyorum fakat işin en temelinde tabloya ait "doğru, istatistiki bilgi" ifadesinin yer aldığını söyleyebilirim. Basit bir normal dağılımda Z veya T değerine bakılıyorsa bile konumuzun Oracle veritabanı yönetimi olduğunu hatırlatıyorum ve daha ileri yazılarımda muhasebe, istatistik ya da işletme ekonomisi gibi konulardan bahsetmeyi umuyorum. Hazır konu biraz Oracle'dan sapmışken, işletmeyle ilgili konularda da benimle iletişim halinde kalabilirsiniz. Yardımcı olmaktan mutluluk duyarım.

Bir Optimizer istatistiği aşağıdaki istatistiki bilgileri içermektedir;

1) Tablo İstatistikleri: Ortalama satır uzunluğu, toplam satır sayısı veya blok sayısı gibi obje seviyesi istatistiklerini içermektedir.
2) Sütun İstatistikleri: Veri dağılımı içerisindeki NULL ve tekil değerlerin istatistiğini içermektedir.
3) İndeks İstatistikleri: İndeks seviyelerini ve indeks yaprak sayılarını tutan istatistiklerdir.
4) Sistem İstatistikleri: CPU ve I/O utilizasyon istatistiklerini içermektedir.

Bir Oracle veritabanı otomatik olarak sistem ve bütün veritabanına ait objelerin istatistiklerini toplayabilmektedir. Bunu, yanılmıyorsam 10g ile aramıza katılan GATHER_STATS_JOB aracılığı ile sağlamaktadır. Bir önceki yazımda DBMS_STATS paketinden bahsetmiştim. DBMS_STATS paketini kullanarak elle istatistiklerin toplanmasını da sağlayabilirsiniz. Optimizer optimizasyonu bu istatistikler baz alınarak yapılır ve normal şartlar altında data dictionary içerisinde barındırılır. Bu istatistikleri lütfen performans istatistikleri ile karıştırmayınız (dinamik performans görüntüleri içerisinde tutulan).

Optimizer Hints

Benim yazılarımı devamlı okuyan okurlarım bilirler, teknik terimleri bile Türkçe'ye çevirmeye çalışırım çünkü benim size "hint" demem her ne kadar bir şeyleri açıklasa da Türkçe'sini bilmenin ve kullanmanın faydalı olduğuna inanıyorum. Bu yüzden bu andan itibaren hint yerine ipucu kelimesini kullanıyor olacağım. Oracle'da çok merak edilen konulardan birisi olan optimizer ipuçları, Cost Based Optimizer'ın davranışlarına emir vermek demiktir. "Bak, sen bunu yanlış biliyorsun, bu şekilde davranmalısın" demenin Oracle'cası veya SQL sorgu dilinde olanıdır. İpuçları bir yorumdur ve Optimizer'a bir eğitmen gibi davranmantakdır. Optimizer zaten en düşük olan maliyeti seçmektedir ancak kimi zaman seçtiği maliyet "kendine göre düşüktür" ve bizim veri hakkında daha fazla bilgimiz olduğu için Optimizer'ı yönlendirmek isteyebiliriz. Uygulamayı tasarlayan geliştirici arkadaşların sıklıkla başvurduğu bir yöntemdir fakat guru diye tabir edebileceğim insanların pek hoşlanmadığı bir çözümdür. Bunun nedeni ise ipuçları dinamik değildir ve Optimizer'ın karakteristiği gereği değiştirilmeleri gerekebilir. Bir ipucu o an için iyi sonuçlar verirken, aradan geçen bir aydan sonra sorguyu inanılmaz derecede yavaşlatabilir ya da çok fazla kaynak tüketmesine neden olabilir. Bu bağlamda ipuçlarını kullanmadan önce istatistiklerin toplanması, verinin detaylı analizin yapılması, sorgunun tune edilmesi ve algoritmasının gözden geçirilmesi geremektedir.

Aşağıda bir Optimizer ipucunun kullanımı göstermekteyim;


SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

------------------------------------------------------------------------
| Id | Operation                    | Name              | Rows | Bytes
------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                   | 26   | 182
|  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         | 26   | 182
|* 2 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      |

Yüklem bilgisi olarak departman id'leri 50'den büyük olan, hr kullanıcısına ait employees tablosundan, employee_id ve department_id bilgilerinin ilk 25 satırını istediğimizi gösterdik. Koyu olarak işaretlediğim alana hint ya da ipucu denmektedir. İpucunu kaldırarak yeniden deneyebilirsiniz. Ben çıktısını gösteriyor olacağım;

SELECT employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

------------------------------------------------------------------------
| Id | Operation              | Name              | Rows | Bytes | Cos
------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |                   | 50   | 350   |
|* 1 |  VIEW                  | index$_join$_001  | 50   | 350   |
|* 2 |   HASH JOIN            |                   |      |       |
|* 3 |    INDEX RANGE SCAN    | EMP_DEPARTMENT_IX | 50   | 350   |
|  4 |    INDEX FAST FULL SCAN| EMP_EMP_ID_PK     | 50   | 350   |

SQL İşlemeye Genel Bakış

Bu bölümde bir Oracle veritabanının SQL sorgularını nasıl işlediğine bakacağız. Bu işlemeye yukarıda anlattığım Cost Based Optimizer safhası da dahildir ve bir bütünü kapsar.

SQL İşlemenin Aşamaları


1) SQL Parsing: Yukarıdaki diyagramda gözüktüğü üzere SQL işlemesinin ilk aşaması SQL sorgusunun parse edilmesidir. Parse etmeyi şöyle açıklayabilirim. Bir uygulamaya ya da bir operasyona anlamlı gelecek şekle getirmektir. Binary bir dosyayı parse ederek CSV formatında bir dosyaya çevirirseniz, sizin için anlamlı bir hale gelecektir ancak öncelikle parser'ın binary dosyanın dilinden anlıyor olabilmesi ve sizin elinizde bunu tercüme edecek bir dokümantasyonun bulunması gerekmektedir. Oracle'ın yaptığıda aslında bundan çok farklı değildir. Oracle da kendisi için anlamlı gelecek bir format oluşturmaya çalışır fakat sistematiği biraz farklıdır. 

Bir uygulama bir SQL ifadesi çalıştırdığı zaman bir de "parse call" yapar, yani bir parse isteğinde bulunur ve veritabanından bu parse işlemini istediğini söyler. Parse istediği (parse call) veritabanında bir "cursor" yaratır ve SQL ifadesinin parse edilmiş halini barındırır. Bu ifade "Private SQL Area" dediğimiz session'a özel bir alanda saklanır. Yaratılan cursor ve özel SQL alanı PGA içerisinde yer almaktadır. PGA içerisinde yer aldığından dolayı diğer session'larla (bağlantı) paylaşılmamaktadır.

Bir parse call'da 3 işlem yapılmaktadır ve aklınızda daha iyi kalabilmesi için 3S işlemi olarak ifade edeceğim. Bunlar;

a) Syntax Check: Bir Oracle veritabanı her SQL ifadesinin sentaksının veya diğer bir ifade ile söz diziminin doğru olduğunu kontrol etmek zorundadır. Eğer söz dizimi doğru yazılmış ise sonrası gelebilir. Aksi halde hata basmak durumundadır. Örnek;


SQL> SELECT * FORM employees;
SELECT * FORM employees
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

b) Semantic Check: Bu kontrol sırasında FROM kısmında kullandığımız tablonun adının doğru olup olmadığı, doğru ise SELECT kısmındaki sütunların, ilgili tabloda olup olmadığı kontrol edilmektedir. Örnek;

SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

c) Shared Pool Check: Parse aşamasındayken shared pool alanı kontrol edilir. Kaynak bakımından çok yüklü olan parse aşamasının atlanması için shared pool kontrol edilir ve her bir SQL ifadesi için bir hash değer çıkartır. Bu hash değere SQL_ID denir ve V$SQL.SQL_ID olarak kontrol edilebilir. Ne zaman bir SQL ifadesinin parse call'u veritabanına ulaşırsa, veritabanı shared SQL area olarak adlandırılan bir alanda daha önce parse edilmiş bir SQL planı olup olmadığını kontrol eder. Bunu da az önce bahsettiğim SQL_ID aracılığı ile sağlar.

Bir parse operasyonu iki aşamaya ayrılarak devam etmektedir;

1) Hard Parse: Eğer bir Oracle veritabanı daha önceki kodu yeniden kullanamazsa bu durumda uygulama kodu için yeni bir versiyon çıkartması gerekmektedir. Bu operasyonun adı hard parse'tır ya da diğer bir ismi ile "library cache miss"dir. Bu arada çok önemli bir not, her DDL komutu için hard parse yapılması zorunludur ve yapılır. Hard parse işlemi sırasında data dictionary ve library cache defalarca taranır ve veritabanı tarafından erişilir. Bir veritabanı bu alanlara eriştiği zaman "latch" ismini verdiğimiz ve library cache ya da data dictionary üzerinde oluşan "lock" yani kilitler oluşmaktadır. Satır seviyesinde bir kilit değil, mantıksal bir kilittir. Latch olarak adlandırılan bu kilit mekanizmaları eş zamanlılık çatışmalarına ve sorgularda yavaşlamalara neden olmaktadır. Kaynaklar paylaşılamıyor olabilir. 
2) Soft Parse: Soft parse bir hard parse işlemi değildir. Diğer bir adı ile "library cache hit"tir. Eğer girilen SQL ifadesinin parse call sırasında shared pool içerisinde yeniden kullanılabilecek durumda olan bir planı varsa bu durumda soft parse gerçekleşir. Genel olarak soft parse > hard parse olması tercih edilebilen bir durumdur.


Yukarıdaki diyagramda bir dedicated sunucu için UPDATE işleminin gerçekleştirdiği shared pool taramasını görmektesiniz. Private SQL Area'da oluşan cursor ve SQL_ID, library cache üzerinde aranıyor. Bir eşleşme sağlanıyor ve soft parse aşamasına geçiliyor. Bir önemli not vermek istiyorum, SQL ifadelerinin aynı olması, her kullanıcı için soft parse işlemi gerçekleştirileceğini garanti altına almaz. Şema seviyesindeki obje isim farklılıklarından dolayı SQL ifadeniz hard parse'a geçebilir. Bütün şartların, kullanıcı ve SQL sorgusu dahil, aynı olduğu bir ortamda bile ortamsal nedenlerden dolayı hard parse işlemi gerçekleşebilir. Bir örnek;

ALTER SYSTEM FLUSH SHARED_POOL;
SELECT * FROM my_table; --> #1

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;
SELECT * FROM my_table; --> #2

ALTER SESSION SET SQL_TRACE=TRUE;
SELECT * FROM my_table; --> #3

Yukarıdaki örnekte SELECT ifadesi üç farklı optimizer ve veritabanı ortamında gerçekleşmektedir. SELECT ifadeleri aynı olsa dahi ortam farklı olduğu için her bir sorguda yine farklı SQL_ID'leri ve cursor'lar oluşacak, hard parse gerçekleşecektir.

SQL Optimizasyonu

Bir sorgunun optimizasyonu, Optimizer'ın seçtiği en düşük maliyetli sorgu planı olarak taınmlanmaktadır. Veritabanı, bir SQL sorgusunun optimizasyonunu, sorgu içerisinde bulunan objelerin istatistiklerine göre yapmaktadır. Bunu yaparken de data dictionary'yi kullanmaktadır. 

Bir Oracle veritabanı ilk defa çalıştırılan bütün DML komutları için mecburen hard parse yapmak durumunda kalabilir. DDL komutları hiçbir zaman optimize edilmez ne var ki içerisinde bir DML içeriyorsa (alt sorgu olarak), bu durumda DML optimize edilebilir. 

SQL Satır Kaynağı Yaratılması

"Row Source Generator" bir optimal execution plan kabul eder. Bir satır kaynağı execution plan içerisinde yer alan aşamalardan gelen kontrol yapısını içermektedir. Satır kaynağı bir tablo, görüntü ya da grup veya join operasyonundan gelen sonuç olabilir. 

Satır kaynak yaratıcısı bir satır kaynak ağacı oluşturur ve içerisinden birden fazla satır kaynağı barındırır. Bu satır kaynakları aşağıdaki bilgileri göstermektedir;

- SQL ifadesindeki tabloların sıralaması.
- SQL ifadesindeki her bir tablonun erişim yolunun ne olduğu.
- SQL ifadesindeki tabloların etkilendiği join operasyonları ve metodu.
- Filter, sort ya da toplama gibi veri manipülasyon işlemleri.

Aşağıdaki örnek AUTOTRACE açık durumda görülen bir execution plan'ını içermektedir. Bu sorgunun execution plan'ı aynı zaman satır kaynak yaratıcısının da çıktısıdır;

SELECT e.last_name, j.job_title, d.department_name
FROM   hr.employees e, hr.departments d, hr.jobs j
WHERE  e.department_id = d.department_id
AND    e.job_id = j.job_id
AND    e.last_name LIKE 'A%' ;

Execution Plan
----------------------------------------------------------
Plan hash value: 975837011

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     3 |   189 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN                    |             |     3 |   189 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN                   |             |     3 |   141 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     3 |    60 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_NAME_IX |     3 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | JOBS        |    19 |   513 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL           | DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - access("E"."JOB_ID"="J"."JOB_ID")
   4 - access("E"."LAST_NAME" LIKE 'A%')
       filter("E"."LAST_NAME" LIKE 'A%')

Gördüğünüz gibi predicate (yüklem ya da WHERE koşulu) içerisinde bulunan 3 öğe "Predicate Information" kısmında gösterildi ve filter operasyonu gerçekleşti. Bu aşamalarda satır kaynaklarını teker teker görmekteyiz.

SQL Çalıştırılması

Bir SQL ifadesinin çalıştırılması (execution) sırasında satır kaynak yaratıcısının oluşturduğu ağaç yapısı üzerinden geçilir ve ağaç yapısı içerisindeki bütün satır kaynakları teker teker çalıştırılır. Bu aşama bir DML işlemesi sırasındaki tek zorunlu aşamadır. 

"Execution Tree" ya da parse ağacına ait diyagramı aşağıda gösteriyorum;


Yukarıdaki diyagramda numaralandırılmış her aşamaya satır kaynağı, ağaca ise satır kaynak ağacı denir. Bu ağac satır kaynak yaratıcısı tarafından oluşturulmuştur. Çalıştırmanın sırası görüntünün aksine aşağıdan yukarıya doğru ilerlemektedir. 

Diyagramda siyahla gösterilen aşamalar veritabanındaki bir objeden fiziksel olarak verinin elde edildiğini göstermektedir. Bu aşamalar erişim yoludur. 
- 6ncı aşamada departments tablosundaki bütün satırlara full table scan yapılarak erişilmektedir.
- 5nci aşama ise jobs tablosundan verileri yine full table scan ile getirmektedir.
- 4ncü aşamada emp_name_ix indeksi kullanılarak, A ile başlayan bütün rowid'ler bulunmaktadır.
- 3ncü aşamada employees tablosundan satırlar getirilmektedir ve bu satırlar 4ncü aşamadaki rowid'lerden gelenlerdir. 
- 2nci aşamada bir hash join yani yığın birleşme yapılmaktadır. Burada 3 ve 5nci aşamalardan gelen satır kaynakları kabul edilmektedir ve 1nci aşamaya gönderilmektedir. Daha önce bahsettiğim gibi bu tipte olan birleştirmeler de birer satır kaynağıdır.
- 1nci aşama başka bir hash join yapmaktadır ve 2 ve 6ncı satır kaynaklarından gelenleri kabul etmektedir.

Bütün bu çalıştırma safhaları içerisinde eğer SQL ifadesinde istenen veri seti bellekte bulunmuyorsa veritabanı diskten fiziksel okumalar yapmaktadır. Bununla birlikte gerekli olan durumlarda latch ya da lock olarak tabir edilen kilit mekanizmaları da devreye girmektedir. SQL çalıştırmasının ve SQL ifadesinin en son aşamasında ise ilk başta açılan cursor kapatılır.

Oracle DML İşlemlerini Nasıl İşliyor?

Genelde bütün DML işlemleri bir sorgu bileşeni içermektedir. Bir sorguda, cursor'ın çalıştırılmasıyla birlikte ortaya çıkan sorgular, veri seti olarak adlandırılan (result set) bir satırlar kümesinde barındırılır.

Genel olarak bir veritabanında sorgunun ne kadar satır getireceği, en son satıra varılıncaya kadar bilinemez. Oracle veritabanı verileri bir fetch mekanizması ile getirir ve bunlara "fetch calls" adı verilmektedir. Bazı sorgular için veritabanı ilk satırı mümkün olduğunca hızlı getirir, buna karşın bazıları için bütün veri setini daha ilk satır için fetch call yapmadan oluşturabilir.

Oracle DDL İşlemlerini Nasıl İşliyor?

Oracle veritabanı DDL komutlarını DML komutlarından farklı işleme almaktadır. Örnek olarak bir CREATE TABLE komutu çalıştırdığınız zaman veritabanı, DML'de olduğu gibi bir optimizasyon yapmaz. Bunun yerine Oracle veritabanı DDL komutunu direkt olarak parse eder ve girilen komutu işleme alır.

Genel olarak Oracle veritabanı bir DDL komutunu çalıştırmak için mükerrer SQL işlemleri çağırmaktadır. Örnek;

SQL> CREATE TABLE ogan_deneme (kullanici_adi VARCHAR2);

Oracle veritabanı her ne kadar siz tek bir DDL girmiş olsanız ve amacınız sadece basit bir tablo yaratmak olsa bile, arka planda bir düzine sorgu çalıştırmaktadır. Bu mükerrer SQL sorguları;

- CREATE TABLE komutundan hemen önce COMMIT emrinin verilmesi.
- CREATE TABLE komutunun çalışması için kullanıcı haklarının geçerli olup olmadığının kontrolünün gerçekleştirilmesi.
- Yaratılacak tablonun hangi tablespace üzerinde bulunacağının kesinleştirilmesi.
- Tablespace kotasının aşılmadığının ya da var olduğunun kontrolünün sağlanması.
- İlgili şemada bulunan diğer objelerle isim çakışmasının olmadığının anlaşılması.
- Data Dictionary içerisine tablo ve tablonun sütunları ile ilgili bilgilerin girilmesi.
- DDL komutu başarıyla sonlanırsa COMMIT aksi halde ROLLBACK operasyonunun gerçekleştirilmesi.

İyi çalışmalar dilerim.

Ogan

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
Takip et: @oganozdogan