Selamlar,
Genelde yazılarımın başlıklarını Türkçe, içeriği Türkçe ve mümkün olan bütün kelimeleri de yine Türkçe yazmaya özen ve dikkat gösteriyorum. Ancak bu sefer başlığı İngilizce yazmamın nedeni birçok insanın İngilizce olarak arama yapması ancak Türkçe bulduğu zaman daha işlerine yaramasını sağlayabilmek. Etiket olarakta ayrıca ekledim.
Geçtiğimiz gün tarafıma iletilen bir soru üzerine çözüm ürettim ancak ürettiğim çözümün internet üzerinde, bu başlık ile ilişkilendirilmediğini gördüm. Konu şu; bir fiziksel veritabanımız var ve veritabanınızın v$archived_log fixed view'ını görüntülediğiniz zaman "applied" başlığı altındaki en güncel archivelog'un, fiziksel veritabanınıza işlendiğini gördünüz. v$dataguard_status view'ında da işler yolunda gidiyor. Peki bu oluşan archivelog'lar neden silinmiyor? Buradaki önemli sorulardan birisi budur aslında. Archivelog'ların bakımı (silinmesi, yedeklenmesi) ile ilgili inanılmaz boyutlarda soru - cevap internet üzerinde mevcut ancak data guard söz konusu olunca işler değişiyor.
Bunu sağlayabilmenin iki tane yolu var. Archivelog'ları Oracle'a otomatik olarak sildirtebilirsiniz ya da yedeği alınırken silinebilir. Her iki yolu da anlatacağım.
1) Archivelog'ların Oracle Tarafından Otomatik Olarak Silinmesi:
Bunu sağlayabilmek için 2 tane parametre tanımlamamız gerekiyor. Bir tanesi db_recovery_file_dest ve diğeri de db_recovery_file_dest_size. Bu parametreleri 10g için SGA_TARGET, 11g için MEMORY_TARGET gibi düşünebilirsiniz (mantık aynı, görevleri farklı). Oracle oluşan archivelog'ları tanımlamış olduğunuz db_recovery_file_dest alanına;
/db_recovery_file_dest/ORACLE_SID/archivelog/DATE/%arc_%s_%t şeklinde tanımlar. Buradaki archivelog formatını ise log_archive_format parametresi belirler. Oracle oluşan archivelog'ları gün gün ayırır, silinmesi gerekenleri siler. Silinmesi gerekenler nedir? db_recovery_file_dest_size parametresine tanımladığınız bilgi eğer %100'e yaklaşmış ise size uyarıda bulunur. Tepki vermezseniz kendisi otomatik olarak siler! Bu arada bu iki parametreyi de eğer spfile kullanıyorsanız dinamik olarak tanımlayabilirsiniz. (alter system ... scope=both);
Bu uyarının çıktısı ise şöyle olacaktır;
Errors in file /usr/ORACLE/u01/oracle/admin/smstby/udump/smstby_rfs_18686.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 bytes is 97.09% used, and has 31258624 remaining bytes available.
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Uyarının nedeni db_recovery_file_dest_size'ın neredeyse dolmuş olması. Yukarıdaki örnekte %97 dolu durumda gözüküyor. Peki bu 4 maddeyi yapmazsanız Oracle ne yapıyor? O da aşağıda;
Wed Jun 30 09:31:40 2010
RFS[13]: Archived Log: '/usr/ORACLE/archive/SMSTBY/flash_recovery_area/SMSTBY/archivelog/2010_06_30/o1_mf_1_48375_62oqpf58_.arc'
Primary database is in MAXIMUM PERFORMANCE mode
Deleted Oracle managed file /usr/ORACLE/archive/SMSTBY/flash_recovery_area/SMSTBY/archivelog/2010_06_30/o1_mf_1_48372_62on259d_.arc
Deleted Oracle managed file /usr/ORACLE/archive/SMSTBY/flash_recovery_area/SMSTBY/archivelog/2010_06_30/o1_mf_1_48373_62oojwrq_.arc
Veritbanının devamlılığını sağlayabilmek için bu archivelog dosyalarını otomatik olarak siliyor.
Bu veritabanı için db_recovery_file_dest parametresini /usr/ORACLE/archive/SMSTBY/ olarak tanımladım ancak Oracle archivelog'ları /usr/ORACLE/archive/SMSTBY/flash_recovery_area/SMSTBY/archivelog/2010_06_30/ altında oluşturdu. Bu da kendisinin yönettiğini gösteriyor. Bunu da yapabilmek için ilgili dizinde Oracle kullanıcısının (unix) okuma ve yazma hakkının olması gerekiyor. Bu bilgiyi işletim sistemi yöneticisinden alabilirsiniz.
2) RMAN ile Archivelog'ların Silinmesi:
Bir diğer yöntem ise her zaman geçerli olan Recovery Manager ile archivelog'ların yedeklenmesi. Bu başlıkta kendi içerisinde ikiye ayrılabilir. Birincisi kişinin *nix üzerinde oluşan archivelog'ları elle silmesi, diğer de bu log'ları RETENTION PERIOD belirleyerek "obsolete" hale getirerek Recovery Manager tarafından sildirmesi. Ben ikinci yolu her zaman daha olumlu buluyorum çünkü elle silindiği zaman ek işler ve masraf devreye giriyor.
Elle sildiğimiz zaman bunu Oracle'a bildirmek zorundayız. Oracle otomatik olarak bu archivelog'ların silindiğini anlayamaz. Yedekleme yaparken hata alırsınız ve yedeklemeye çalışır sonra da bulamıyorum der. Bunun için;
RMAN> crosscheck archivelog all;
Komutunu amacı archivelog'ları Oracle'a göstertmek ve varlığını öğretmek. Crosscheck komutundan sonra "obsolete" archivelog'ları silebilmek için;
RMAN> delete noprompt obsolete;
Bu yöntemler eski archivelog'lardan kurtulabilirsiniz. Diğer yöntemde ise recovery catalog değil de control dosyasını yedek listelemede kullandığınızı varsayıyorum. Control dosyasının yedek bilgilerini tuttuğunu ve aşağıdaki parametreye göre bakımını yapması gerektiğini söyleyebilirim;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Yukarıdaki komut aldığınız yedeklerin, archivelog'ların vb. 7 gün boyunca "expired" ya da "obsolete" olmayacağını garanti eder.
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
Yukarıdaki komut ise aldığınız 5 tane veritabanı yedeğinden sonraki 6ncı ile birlikte, 1ncinin "expired" yani aslında kullanılamaz olarak etiketlenmesini sağlar.
Peki bu komutların standby archivelog'ları ile ne ilgisi var? Bir diğer parametrenin tanımlanması ile;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
Varsayılan değeri NONE olan yukarıdaki parametreyi APPLIED ON STANDBY ile değiştirirseniz eğer "obsolete" olan archivelog'lar yedekleme sırasında standby veritabanından silinecektir.
Eğer yedekleme işlemini birincil veritabanında yapıyorsanız;
Birincil veritabanında;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
Yedek veritabanında;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
Eğer yedeklemeyi yedek veritabanında yapıyorsanız;
Birincil veritabanında;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
Yedek veritabanında;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
Oracle dokümantasyonuna göre (11g) yedekleme işlemine gerek kalmadan, APPLIED ON STANDBY derseniz mantıksal ya da fiziksel yedeğin archivelog'ları silinecektir.
Yukarıdaki genel bilgiler ışığında benim tercih ettiğim method ilk olarak anlattığım. "Flash recovery area" bilgisinin tanımlanmış olması önemli bir kriter zira birçok veritabanı archivelog'ların şişmesi ve eksik bakım yapıldığından çökebiliyor. Ölümcül bir hata değil, veritabanı bir süre daha varlığını devam ettirebiliyor ancak bir süre sonra ARCHn görevi ölüyor ve archive alınması işlemini veritabanı terk ediyor. Bu durumda veritabanını kapatmanız, archivelog'ların artık bulunmadığını Oracle'a öğretmeniz (eğer elle silmişseniz) ve veritabanını yeniden açmanız gerekecektir.
İyi çalışmalar,
Ogan
30 Haziran 2010 Çarşamba
23 Haziran 2010 Çarşamba
Automatic Workload Repository & Create Snapshot
Merhabalar,
Oracle 10g ile birlikte aramıza katılan bir özellik olan Automatic Workload Repository (AWR), diagnostic pack bedelini ödediğiniz zaman kullanabileceğiniz bir özelliktir. AWR ve statspack farklı araçlardır ancak amaçları aynıdır ve istenildiği zaman statspack kullanımı yine söz konusudur.
Bugün bahsetmek istediğim konu bu raporları elle nasıl yaratabiliriz? Oracle aslında bizim için bunu her saat başı yapıyor ve sistemi kontrol edebilmemiz bir bize bir AWR raporu sunuyor. Bu noktada çok kısa bahsetmek istiyorum ki AWR dışında bir de ADDM denen bir kavram vardır, yani Automatic Database Diagnostic Monitor. ADDM'in amacı AWR raporlarını inceleyerek, bize taleplerde bulunması. Örneğin AWR raporunda bir SQL veritabanını çok fazla yormuş. ADDM bu SQL'i inceleyerek bize; tablo üzerinde index mi yaratmalıyız, index var ancak bitmap olmalı ya da yeni bir SQL profili geliştirmek gibi faydalı bilgiler sunar.
Gelelim konumuza. AWR raporlarını genelde enterprise manager (EM) aracılığı ile kontrol edebilirsiniz. Ancak bazı durumlarda saatlik alınan AWR raporunu okumayı değil, kendi aldığınız AWR raporunu incelemeyi tercih edebilirsiniz. Bunu tercih etmenizdeki en büyük etken ise o andaki performans problemlerini teşhis edebilmek olabilir.
Aşağıdaki komut ile manuel olarak snapshot'ımızı alabiliriz;
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
Ya da
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
Bir snapshot aralığını silmek ve veritabanının bilgisinden çıkartmak isterseniz eğer;
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 1, high_snap_id => 10 dbid => 1921262300);
END;
/
Peki bu noktada şöyle bir soru sorabilirsiniz; "Neden AWR raporları Oracle tarafından her saatinde başında alınmakta ve bunu değiştiremez miyiz?" Cevap, evet değiştirebilirsiniz. Hemen bir örnek göstereyim;
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 14400,
interval => 15, dbid => 1921262300);
END;
/
Retention period değeri AWR raporunun kaç gün boyunca saklanacağını, internal değeri ise kaç dakikada bir snapshot alınması gerektiğini temsil etmektedir. Daha sık ya da daha geç AWR raporu görmek isterseniz eğer yukarıdaki MODIFY_SNAPSHOT_SETTINGS sizin için biçilmiş kaftan diyebilirim.
DBA_HIST_WR_CONTROL data dictionary view'u ise size veritabanınızın AWR ayarlarını gösterecektir. MODIFY_SNAPSHOT_SETTINGS ile herhangi bir değişikliği yerine getirmeniz durumunda DBA_HIST_WR_CONTROL view'unu sorgulayarak en son halini görüntüleyebilirsiniz.
AWR snapshot'larını aldıktan sonra da awrrpt.sql isimli script'i koşmanız gerekiyor. Bu script'in amacı size bir AWR raporu hazırlamak.
awrrpt.sql scripti ise aşağıdaki dizinde bulunmaktadır;
$ORACLE_HOME/rdbms/admin/
Bu script'i çalıştırmak için;
cd $ORACLE_HOME/rdbms/admin/
sqlplus / as sysdba
SQL> @awrrpt.sql
İyi çalışmalar,
Ogan
Oracle 10g ile birlikte aramıza katılan bir özellik olan Automatic Workload Repository (AWR), diagnostic pack bedelini ödediğiniz zaman kullanabileceğiniz bir özelliktir. AWR ve statspack farklı araçlardır ancak amaçları aynıdır ve istenildiği zaman statspack kullanımı yine söz konusudur.
Bugün bahsetmek istediğim konu bu raporları elle nasıl yaratabiliriz? Oracle aslında bizim için bunu her saat başı yapıyor ve sistemi kontrol edebilmemiz bir bize bir AWR raporu sunuyor. Bu noktada çok kısa bahsetmek istiyorum ki AWR dışında bir de ADDM denen bir kavram vardır, yani Automatic Database Diagnostic Monitor. ADDM'in amacı AWR raporlarını inceleyerek, bize taleplerde bulunması. Örneğin AWR raporunda bir SQL veritabanını çok fazla yormuş. ADDM bu SQL'i inceleyerek bize; tablo üzerinde index mi yaratmalıyız, index var ancak bitmap olmalı ya da yeni bir SQL profili geliştirmek gibi faydalı bilgiler sunar.
Gelelim konumuza. AWR raporlarını genelde enterprise manager (EM) aracılığı ile kontrol edebilirsiniz. Ancak bazı durumlarda saatlik alınan AWR raporunu okumayı değil, kendi aldığınız AWR raporunu incelemeyi tercih edebilirsiniz. Bunu tercih etmenizdeki en büyük etken ise o andaki performans problemlerini teşhis edebilmek olabilir.
Aşağıdaki komut ile manuel olarak snapshot'ımızı alabiliriz;
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
Ya da
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
Bir snapshot aralığını silmek ve veritabanının bilgisinden çıkartmak isterseniz eğer;
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 1, high_snap_id => 10 dbid => 1921262300);
END;
/
Peki bu noktada şöyle bir soru sorabilirsiniz; "Neden AWR raporları Oracle tarafından her saatinde başında alınmakta ve bunu değiştiremez miyiz?" Cevap, evet değiştirebilirsiniz. Hemen bir örnek göstereyim;
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 14400,
interval => 15, dbid => 1921262300);
END;
/
Retention period değeri AWR raporunun kaç gün boyunca saklanacağını, internal değeri ise kaç dakikada bir snapshot alınması gerektiğini temsil etmektedir. Daha sık ya da daha geç AWR raporu görmek isterseniz eğer yukarıdaki MODIFY_SNAPSHOT_SETTINGS sizin için biçilmiş kaftan diyebilirim.
DBA_HIST_WR_CONTROL data dictionary view'u ise size veritabanınızın AWR ayarlarını gösterecektir. MODIFY_SNAPSHOT_SETTINGS ile herhangi bir değişikliği yerine getirmeniz durumunda DBA_HIST_WR_CONTROL view'unu sorgulayarak en son halini görüntüleyebilirsiniz.
AWR snapshot'larını aldıktan sonra da awrrpt.sql isimli script'i koşmanız gerekiyor. Bu script'in amacı size bir AWR raporu hazırlamak.
awrrpt.sql scripti ise aşağıdaki dizinde bulunmaktadır;
$ORACLE_HOME/rdbms/admin/
Bu script'i çalıştırmak için;
cd $ORACLE_HOME/rdbms/admin/
sqlplus / as sysdba
SQL> @awrrpt.sql
İyi çalışmalar,
Ogan
14 Haziran 2010 Pazartesi
Dynamic Sampling - Nested Loops & CBO
Selamlar,
Dynamic Sampling, bir Oracle hint'idir ve optimizer'a yapması gerekenleri öğretmek için kullanılır. Dynamic sampling bir yön belirtici değildir, sadece optimizer'a query'i çalıştırdığınız anda geçerli olması üzerine bir istatistik toplanması gerçekleştirir. Hard parser yaptırabileceği gibi daha iyi bir plan da çıkarabilir. Dynamic sampling hint'i ile optimizer'a sadece istatistikleri güncelle diyebilirsiniz ve eğer güncelleme oluşmuşsa ve explain plan'de bir yol değişikliği olmuş ise bunu sağlayan dynamic sampling değildir, sadece optimizer daha iyi bir yol keşfetmiştir.
Dynamic Sampling ile ilgili güzel bir yazı ve cevap Thomas Kyte'dan gelmiş.
Kullanım şekli ise diğer Oracle hint'leri ile aynıdır;
/*+ dynamic_sampling(,2) */
Bir diğer hint ise Nested Loop. Bununla ilgili süper bir örnek gösterebilirim;
Aşağıdaki sorguyu inceleyelim;
SQL>
SELECT
A.DATETIME, A.IPADDRESS,
DECODE (A.IPADDRESS,'212.156.116.117', '06_ulus_t1_1', '212.156.116.118','06_ulus_t1_2', '212.156.116.119', '00_gayrettepe_t1_1', '212.156.116.120', '00_gayrettepe_t1_2','212.156.116.121' , '34_acibadem_t1_1', '212.156.116.122', '34_acibadem_t1_2', '212.156.116.123', '35_izmir_t1_1', '212.156.116.124', '35_izmir_t1_2', '212.156.116.125', '01_adana_t1_1', '212.156.116.126', '01_adana_t1_2') MACHINE,
SUBSTR(A.IDX,0,INSTR(A.IDX,'.')-1) IFINDEX, DECODE (SUBSTR(A.IDX,INSTR(A.IDX,'.')+1),'2.97.102','Af','2.98.101','Be','2.101.102','Ef','2.104.49','H1','2.104.50','H2','2.108.49','L1', '2.108.50','L2','2.110.99','Nc') FORW_CLASS,
SUBSTR(A.IDX,INSTR(A.IDX,'.')+1) IDX,
A.JNXCOSIFQTOTALREDDROPPKTS,
A.JNXCOSIFQTXEDBYTES,
CASE
WHEN (A.JNXCOSIFQTXEDBYTES - B.JNXCOSIFQTXEDBYTES) < datetime =" B.DATETIME" ipaddress =" B.IPADDRESS" idx =" B.IDX" style="font-weight: bold;">Execution Plan
----------------------------------------------------------
Plan hash value: 3451555024
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 700K| 62M| | 32094 (15)| 00:02:03 | | |
|* 1 | HASH JOIN | | 700K| 62M| 39M| 32094 (15)| 00:02:03 | | |
| 2 | PARTITION RANGE ALL| | 700K| 31M| | 10400 (28)| 00:00:40 | 1 | 107 |
|* 3 | TABLE ACCESS FULL | TRAFFIC | 700K| 31M| | 10400 (28)| 00:00:40 | 1 | 107 |
| 4 | PARTITION RANGE ALL| | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.01041666666666666666666666666666
66666667 AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
3 - filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL)
İki defa full table scna yapıldı, çok yavaş.
Bir de dynamic sampling hint'ini eklediğimiz zaman neler oluyor, ona bakalım (sorgu aynı, sadece hint var ve her iki ana tablo için);
Execution Plan
----------------------------------------------------------
Plan hash value: 3451555024
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14M| 1256M| | 43788 (13)| 00:02:48 | | |
|* 1 | HASH JOIN | | 14M| 1256M| 788M| 43788 (13)| 00:02:48 | | |
| 2 | PARTITION RANGE ALL| | 14M| 628M| | 10532 (29)| 00:00:41 | 1 | 107 |
|* 3 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 10532 (29)| 00:00:41 | 1 | 107 |
| 4 | PARTITION RANGE ALL| | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.01041666666666666666666666666666
66666667 AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
3 - filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
Dynamic sampling ile hash join maliyetimiz arttı. Bu durumda daha farklı bir yol izlemeliyiz. Hash Join yerine Nested Loop ile bu iki ana tabloyu daha hızlı çekebiliriz. Bu iki tabloyu bir nested loop ile çok daha hızlı görüntüleyebiliriz çünkü yığın olarak kümelediğimiz zaman full table scan yapmakta;
Bunun için sorguyu aşağıdaki gibi değiştiriyorum;
SQL>
SELECT
/*+ USE_NL(A) USE_NL(B)*/
A.DATETIME, A.IPADDRESS,
DECODE (A.IPADDRESS,'212.156.116.117', '06_ulus_t1_1', '212.156.116.118','06_ulus_t1_2', '212.156.116.119', '00_gayrettepe_t1_1', '212.156.116.120', '00_gayrettepe_t1_2','212.156.116.121' , '34_acibadem_t1_1', '212.156.116.122', '34_acibadem_t1_2', '212.156.116.123', '35_izmir_t1_1', '212.156.116.124', '35_izmir_t1_2', '212.156.116.125', '01_adana_t1_1', '212.156.116.126', '01_adana_t1_2') MACHINE,
SUBSTR(A.IDX,0,INSTR(A.IDX,'.')-1) IFINDEX, DECODE (SUBSTR(A.IDX,INSTR(A.IDX,'.')+1),'2.97.102','Af','2.98.101','Be','2.101.102','Ef','2.104.49','H1','2.104.50','H2','2.108.49','L1', '2.108.50','L2','2.110.99','Nc') FORW_CLASS,
SUBSTR(A.IDX,INSTR(A.IDX,'.')+1) IDX,
A.JNXCOSIFQTOTALREDDROPPKTS,
A.JNXCOSIFQTXEDBYTES,
CASE
WHEN (A.JNXCOSIFQTXEDBYTES - B.JNXCOSIFQTXEDBYTES) < datetime =" B.DATETIME" ipaddress =" B.IPADDRESS" idx =" B.IDX" style="font-weight: bold;">45:05:57 | | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID| TRAFFIC | 1 | 47 | 3 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 700K| 62M| 42M (2)| 45:05:57 | | |
| 3 | PARTITION RANGE ALL | | 14M| 628M| 8738 (14)| 00:00:34 | 1 | 107 |
| 4 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | PARTITION RANGE ITERATOR | | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | TRAFFIC_PK | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.0104166666666666666666666666666666666667
AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL AND "A"."IDX"="B"."IDX")
Maliyetin inanılmaz arttığını göreceksiniz. Yalnız Oracle'da maliyetin yüksek olması sorgunun da yavaş olacağı anlamına asla ve asla GELMEZ! USE_NL hint'ine sahip olmayan sorgunun cevabı 1 dakika 15 saniyede dönerken, USE_NL hint'ine sahip sorgunun cevabı 240 milisaniye gibi çok farklı bir sonuçla geliyor. Tek bir full scan yapıldı, ikinciye gerek kalmadan bütün veriler rowid'lerine erişilerek (index ile) elde edildi. Bunun dışında CPU'da geçirilecek sürede de inanılmaz bir azalma görülüyor.
Oracle dokümantasyonlarına göre CBO (cost based optimizer) her zaman, her zaman maliyeti düşük olan sorgu planını seçer ve uygular. Bunu değiştirmenin tek yolu optimizer hint'leridir. Optimizer'ın her zaman en optimum sorguyu getireceği asla garanti edilmez, edilemez. Çünkü optimizer'ın etkilendiği birçok durum vardır. Örneğin;
1) CBO tablo ve indeks üzerindeki istatistiklere göre bir sorgu planı oluşturur. İstatistikler eksik ya da toplanmamış ise farklı ve daha kötü planlar ortaya çıkabilir.
2) CBO bir sütunun içeriğine bakarakta plan oluşturabilir. Sütunun tipi varchar2 ise ve içerisinde sadece rakamsal değerler varsa bu optimizer'ı yanıltabilir!
3) CBO hint'leri kullanmak yerine ve duruma göre oldukça olumlu ve yüz güldüren sonuçlar elde etmenizi sağlar.
4) CBO sorgunun mantığının hatalı olduğunu, sorguda eksik eşitlik, hatalı bağlantılar ya da unutulmuş indeksler olduğunu anlamaz ve size öneride bulunmaz. Bunu yapan başka bir araç vardır (Detaylı bilgi için AWR). Bu sebepten dolayı önce SQL tuning sonra Oracle hint.
İyi çalışmalar,
Ogan
Dynamic Sampling, bir Oracle hint'idir ve optimizer'a yapması gerekenleri öğretmek için kullanılır. Dynamic sampling bir yön belirtici değildir, sadece optimizer'a query'i çalıştırdığınız anda geçerli olması üzerine bir istatistik toplanması gerçekleştirir. Hard parser yaptırabileceği gibi daha iyi bir plan da çıkarabilir. Dynamic sampling hint'i ile optimizer'a sadece istatistikleri güncelle diyebilirsiniz ve eğer güncelleme oluşmuşsa ve explain plan'de bir yol değişikliği olmuş ise bunu sağlayan dynamic sampling değildir, sadece optimizer daha iyi bir yol keşfetmiştir.
Dynamic Sampling ile ilgili güzel bir yazı ve cevap Thomas Kyte'dan gelmiş.
Kullanım şekli ise diğer Oracle hint'leri ile aynıdır;
/*+ dynamic_sampling(
Bir diğer hint ise Nested Loop. Bununla ilgili süper bir örnek gösterebilirim;
Aşağıdaki sorguyu inceleyelim;
SQL>
SELECT
A.DATETIME, A.IPADDRESS,
DECODE (A.IPADDRESS,'212.156.116.117', '06_ulus_t1_1', '212.156.116.118','06_ulus_t1_2', '212.156.116.119', '00_gayrettepe_t1_1', '212.156.116.120', '00_gayrettepe_t1_2','212.156.116.121' , '34_acibadem_t1_1', '212.156.116.122', '34_acibadem_t1_2', '212.156.116.123', '35_izmir_t1_1', '212.156.116.124', '35_izmir_t1_2', '212.156.116.125', '01_adana_t1_1', '212.156.116.126', '01_adana_t1_2') MACHINE,
SUBSTR(A.IDX,0,INSTR(A.IDX,'.')-1) IFINDEX, DECODE (SUBSTR(A.IDX,INSTR(A.IDX,'.')+1),'2.97.102','Af','2.98.101','Be','2.101.102','Ef','2.104.49','H1','2.104.50','H2','2.108.49','L1', '2.108.50','L2','2.110.99','Nc') FORW_CLASS,
SUBSTR(A.IDX,INSTR(A.IDX,'.')+1) IDX,
A.JNXCOSIFQTOTALREDDROPPKTS,
A.JNXCOSIFQTXEDBYTES,
CASE
WHEN (A.JNXCOSIFQTXEDBYTES - B.JNXCOSIFQTXEDBYTES) < datetime =" B.DATETIME" ipaddress =" B.IPADDRESS" idx =" B.IDX" style="font-weight: bold;">Execution Plan
----------------------------------------------------------
Plan hash value: 3451555024
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 700K| 62M| | 32094 (15)| 00:02:03 | | |
|* 1 | HASH JOIN | | 700K| 62M| 39M| 32094 (15)| 00:02:03 | | |
| 2 | PARTITION RANGE ALL| | 700K| 31M| | 10400 (28)| 00:00:40 | 1 | 107 |
|* 3 | TABLE ACCESS FULL | TRAFFIC | 700K| 31M| | 10400 (28)| 00:00:40 | 1 | 107 |
| 4 | PARTITION RANGE ALL| | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.01041666666666666666666666666666
66666667 AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
3 - filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL)
İki defa full table scna yapıldı, çok yavaş.
Bir de dynamic sampling hint'ini eklediğimiz zaman neler oluyor, ona bakalım (sorgu aynı, sadece hint var ve her iki ana tablo için);
Execution Plan
----------------------------------------------------------
Plan hash value: 3451555024
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14M| 1256M| | 43788 (13)| 00:02:48 | | |
|* 1 | HASH JOIN | | 14M| 1256M| 788M| 43788 (13)| 00:02:48 | | |
| 2 | PARTITION RANGE ALL| | 14M| 628M| | 10532 (29)| 00:00:41 | 1 | 107 |
|* 3 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 10532 (29)| 00:00:41 | 1 | 107 |
| 4 | PARTITION RANGE ALL| | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| | 8738 (14)| 00:00:34 | 1 | 107 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.01041666666666666666666666666666
66666667 AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
3 - filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
Dynamic sampling ile hash join maliyetimiz arttı. Bu durumda daha farklı bir yol izlemeliyiz. Hash Join yerine Nested Loop ile bu iki ana tabloyu daha hızlı çekebiliriz. Bu iki tabloyu bir nested loop ile çok daha hızlı görüntüleyebiliriz çünkü yığın olarak kümelediğimiz zaman full table scan yapmakta;
Bunun için sorguyu aşağıdaki gibi değiştiriyorum;
SQL>
SELECT
/*+ USE_NL(A) USE_NL(B)*/
A.DATETIME, A.IPADDRESS,
DECODE (A.IPADDRESS,'212.156.116.117', '06_ulus_t1_1', '212.156.116.118','06_ulus_t1_2', '212.156.116.119', '00_gayrettepe_t1_1', '212.156.116.120', '00_gayrettepe_t1_2','212.156.116.121' , '34_acibadem_t1_1', '212.156.116.122', '34_acibadem_t1_2', '212.156.116.123', '35_izmir_t1_1', '212.156.116.124', '35_izmir_t1_2', '212.156.116.125', '01_adana_t1_1', '212.156.116.126', '01_adana_t1_2') MACHINE,
SUBSTR(A.IDX,0,INSTR(A.IDX,'.')-1) IFINDEX, DECODE (SUBSTR(A.IDX,INSTR(A.IDX,'.')+1),'2.97.102','Af','2.98.101','Be','2.101.102','Ef','2.104.49','H1','2.104.50','H2','2.108.49','L1', '2.108.50','L2','2.110.99','Nc') FORW_CLASS,
SUBSTR(A.IDX,INSTR(A.IDX,'.')+1) IDX,
A.JNXCOSIFQTOTALREDDROPPKTS,
A.JNXCOSIFQTXEDBYTES,
CASE
WHEN (A.JNXCOSIFQTXEDBYTES - B.JNXCOSIFQTXEDBYTES) < datetime =" B.DATETIME" ipaddress =" B.IPADDRESS" idx =" B.IDX" style="font-weight: bold;">45:05:57 | | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID| TRAFFIC | 1 | 47 | 3 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 700K| 62M| 42M (2)| 45:05:57 | | |
| 3 | PARTITION RANGE ALL | | 14M| 628M| 8738 (14)| 00:00:34 | 1 | 107 |
| 4 | TABLE ACCESS FULL | TRAFFIC | 14M| 628M| 8738 (14)| 00:00:34 | 1 | 107 |
| 5 | PARTITION RANGE ITERATOR | | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | TRAFFIC_PK | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."DATETIME"=INTERNAL_FUNCTION("B"."DATETIME")+.0104166666666666666666666666666666666667
AND "A"."IPADDRESS"="B"."IPADDRESS" AND "A"."IDX"="B"."IDX")
filter(SUBSTR("A"."IDX",0,INSTR("A"."IDX",'.')-1) IS NOT NULL AND "A"."IDX"="B"."IDX")
Maliyetin inanılmaz arttığını göreceksiniz. Yalnız Oracle'da maliyetin yüksek olması sorgunun da yavaş olacağı anlamına asla ve asla GELMEZ! USE_NL hint'ine sahip olmayan sorgunun cevabı 1 dakika 15 saniyede dönerken, USE_NL hint'ine sahip sorgunun cevabı 240 milisaniye gibi çok farklı bir sonuçla geliyor. Tek bir full scan yapıldı, ikinciye gerek kalmadan bütün veriler rowid'lerine erişilerek (index ile) elde edildi. Bunun dışında CPU'da geçirilecek sürede de inanılmaz bir azalma görülüyor.
Oracle dokümantasyonlarına göre CBO (cost based optimizer) her zaman, her zaman maliyeti düşük olan sorgu planını seçer ve uygular. Bunu değiştirmenin tek yolu optimizer hint'leridir. Optimizer'ın her zaman en optimum sorguyu getireceği asla garanti edilmez, edilemez. Çünkü optimizer'ın etkilendiği birçok durum vardır. Örneğin;
1) CBO tablo ve indeks üzerindeki istatistiklere göre bir sorgu planı oluşturur. İstatistikler eksik ya da toplanmamış ise farklı ve daha kötü planlar ortaya çıkabilir.
2) CBO bir sütunun içeriğine bakarakta plan oluşturabilir. Sütunun tipi varchar2 ise ve içerisinde sadece rakamsal değerler varsa bu optimizer'ı yanıltabilir!
3) CBO hint'leri kullanmak yerine ve duruma göre oldukça olumlu ve yüz güldüren sonuçlar elde etmenizi sağlar.
4) CBO sorgunun mantığının hatalı olduğunu, sorguda eksik eşitlik, hatalı bağlantılar ya da unutulmuş indeksler olduğunu anlamaz ve size öneride bulunmaz. Bunu yapan başka bir araç vardır (Detaylı bilgi için AWR). Bu sebepten dolayı önce SQL tuning sonra Oracle hint.
İyi çalışmalar,
Ogan
Kaydol:
Kayıtlar (Atom)