İstatistikleri incelerken google üzerinden yapılan bir arama gözüme çarptı. Aramada bir SQL sorgusunun nasıl UNDO üretmemesini sağlayacağımız aranmaktaydı. Kısaca bununla ilgili yazmak istedim.
Öncelikle undo'nun ne olduğunu anlamak için tıklayınız.
Undo okuma tutarlılığı ve eşzamanlılığı açısından olmazsa olmazdır. Her redo üreten işlemin undo üretmeyeceği gibi her undo üreten işlemin redo üreteceğini söyleyebiliriz. Yani redo > undo. Bir transaction, veriyi modifiye ettiği zaman undo üretilir. Rollback operasyonu ve veritabanını kurtartma durumlarında undo gerekmektedir.
Redo değişimleri sembolize ederken, undo geriye dönülmesi muhtemel değişimleri sembolize eder. Örneğin SELECT ... FOR UPDATE veya UPDATE gibi ifadeler hem redo hem de undo üretirler. Undo üretimini bypass etmenin bir yolu yoktur. Undo, undo edilmesi gereken yani geri alınması gereken bilgileri içeren segment'lere sahiptir. Redo'nun ise bir log dosyası vardır. Sonuç ne olursa olsa her undo üretimi mutlaka log'lanmalı, redo bilgisi üretilmelidir.
Undo üretimini azaltmak istiyorsanız APPEND hint'ini veya NOLOGGING özelliğini kullanabilirsiniz. Ancak dikkat, bu undo üretimini azaltacağı gibi redo üretimini de azaltacaktır. Redo oluşmasını istiyorsanız undo da oluşacaktır. APPEND veya NOLOGGING özelliklerinin ise undo ile bir ilgisi, direkt olarak yoktur.
Undo ayrıca flashback table ve flashback query için de gerekmektedir. Bu flashback özellikleri undo segment'lerini kullanmaktadır.
Bu konuyla ilgili bir örnek göstermek istiyorum. Bu örnekte undo segment'lerinin nasıl kullanıldığı ve insert - delete arasındaki undo üretim farkının göstermeye çalışacağım.
SQL> conn ogan/password;
Connected.
--> ogan kullanıcısı ile sisteme bağlandık ve herhangi bir işlem gerçekleştirmedik.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
no rows selected
--> Herhangi bir transaction kaydımız bulunmamakta.
SQL> set transaction name 'OGAN';
Transaction set.
--> Transaction'ın adını OGAN olarak değiştirdim.
SQL> create table ogan_deneme
2 as
3 select * from all_users;
Table created.
--> ogan_deneme adında bir tablo yarattık ve içerisinde bütün kullanıcıların bilgisi bulunmakta.
SQL> select sq.sql_fulltext "Sorgu", tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.NOUNDO "Undo Uretmeyen Transaction", tr.NAME "Transaction Ismi",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se, v$sql sq
where tr.ses_addr = se.saddr
and username='OGAN'
and se.sql_id = sq.sql_id;
no rows selected
--> Hala bir kaydımız bulunmuyor.
SQL> insert into ogan_deneme
2 select * from ogan_deneme;
594 rows created.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
2 tr.NOUNDO "Undo Uretmeyen Transaction", tr.NAME "Transaction Ismi",
3 tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
4 from v$transaction tr, v$session se
5 where tr.ses_addr = se.saddr
6 and username='OGAN';
Undo Segment Numarasi Undo Blok Adresi Baslangic Zamani Kullanilan Undo Bloklari Kullanilan Undo Kayitlari Mantiksal I/O Fiziksel I/O
---------------------------------------------------------------------
40 785 12/21/10 11:16:06
---------------------------------------------------------------------
1 4 80 0
--> Kullandığı undo segment numarası 40, undo blok adresi 785. Kullanılan undo bloklari 1, kullanılan undo kayitlari ise 4. Yaptığı mantıksal I/O 80 ve fiziksel I/O 0. Şimdi commit etmeden veri girişine devam edelim;
SQL> insert into ogan_deneme
2 select * from ogan_deneme;
1188 rows created.
SQL> insert into ogan_deneme
2 select * from ogan_deneme;
2376 rows created.
SQL> insert into ogan_deneme
2 select * from ogan_deneme;
4752 rows created.
SQL> insert into ogan_deneme
2 select * from ogan_deneme;
9504 rows created.
SQL> insert into ogan_deneme
2 select * from ogan_deneme;
19008 rows created.
SQL> insert into ogan_deneme
2 select * from ogan_deneme;
38016 rows created.
--> Duruma tekrar bakalım;
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
Undo Segment Numarasi Undo Blok Adresi Baslangic Zamani Kullanilan Undo Bloklari Kullanilan Undo Kayitlari Mantiksal I/O Fiziksel I/O
--------------------------------------------------------------------
40 785 12/21/10 11:16:06
--------------------------------------------------------------------
12 451 3277 11
--> Hala aynı segment numarası ve undo blok adresini kullanıyoruz ancak kullanılan undo blokları ve kayıtlarında artış var. Ürettiğimiz redo ve undo artmakta ve bununla beraber fiziksel ve mantıksal okuma da kümülatif olarak yükselmekte. Şu anda commit edersek;
SQL> commit;
Commit complete.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.NOUNDO "Undo Uretmeyen Transaction", tr.NAME "Transaction Ismi",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
no rows selected
Gördüğünüz gibi her insert işleminden sonra rollback operasyonu için yeni undo bloklarına ihtiyaç duyuldu. Bu undo blokları olmasaydı rollback operasyonunu nasıl yapabilecektir?
Bir başka örnek;
SQL> drop table ogan_deneme;
Table dropped.
SQL> create table ogan_deneme
2 as select * from all_users;
Table created.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
2 tr.NOUNDO "Undo Uretmeyen Transaction", tr.NAME "Transaction Ismi",
3 tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
4 from v$transaction tr, v$session se
5 where tr.ses_addr = se.saddr
6 and username='OGAN';
no rows selected
SQL> insert into ogan_deneme
2 select * from ogan_deneme;
594 rows created.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
Undo Segment Numarasi Undo Blok Adresi Baslangic Zamani Kullanilan Undo Bloklari Kullanilan Undo Kayitlari Mantiksal I/O Fiziksel I/O
---------------------------------------------------------------
97 769 12/21/10 11:24:48 NO
---------------------------------------------------------------
1 4 85 0
--> Şimdi ise 97 numaralı undo segment'i ve 769 numaralı blok adresi kullanılmakta. Dikkat ederseniz kullanılan undo blok miktarı ile kayıt sayısı, bir önceki ile aynı gözüküyor çünkü gerçekleştirdiğimiz işlem aynı. Bir de delete komutunun neler yaptığına bakalım;
SQL> delete ogan_deneme;
1188 rows deleted.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
Undo Segment Numarasi Undo Blok Adresi Baslangic Zamani Kullanilan Undo Bloklari Kullanilan Undo Kayitlari Mantiksal I/O Fiziksel I/O
------------------------------------------------------------------------------------------------------
97 769 12/21/10 11:24:48 11 1195 3712
------------------------------------------------------------------------------------------------------
10
--> Gördüğünüz gibi yine aynı segment içerisindeyiz ancak kullanılan blok sayısı 11 katına ve kullanılan undo kayıtları da yaklaşık 300 katına çıktı. Az önce 6 tane insert ile ürettirdiğimiz undo, 1 tane delete komutu ile yarışamadı. Bu noktada önemli not, delete her zaman daha fazla undo üreten bir operasyondur. Yukarıdaki küçücük operasyonda bile farkı görmek mümkün. Ayrıca yapılan mantıksal I/O ile fiziksel I/O, bir önceki örnekle aynı gerçekleşti. Şimdi ise tabloyu truncate edelim ve duruma bakalım;
SQL> truncate table ogan_deneme drop storage;
Table truncated.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
no rows selected
--> Transaction'ımız sonlandı.
Yukarıdaki işlemleri yaptığımız zaman undo üretimi ile birlikte redo üretiminin de arttığını söyleyebiliriz. Şimdi ise daha da ilginç bir örnekle devam edelim.
Hatırlarsanız append hint'ini, redo üretmemek için, daha doğrusu çok az redo ürettirmek için kullanabileceğimizi söylemiştim. Şimdi bir de konu içerisinde APPEND hint'ini inceleyelim.
SQL> drop table ogan_deneme purge;
Table dropped.
SQL> create table ogan_deneme
2 as select * from all_users;
Table created.
SQL> insert /*+ APPEND */ into ogan_deneme
2 select * from ogan_deneme;
594 rows created.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
Undo Segment Numarasi Undo Blok Adresi Baslangic Zamani Kullanilan Undo Bloklari Kullanilan Undo Kayitlari Mantiksal I/O Fiziksel I/O
----------------------------------------------------------------------------------------------------
77 0 12/21/10 11:38:50 1 1 36 2
--> Gördüğünüz gibi mantıksal I/O azaldı ve fiziksel I/O'da 2 artış meydana geldi. Kullanılan undo bloğu ve kaydı 1. Daha büyük bir örnek ile devam edelim;
SQL> drop table ogan_deneme;
Table dropped.
SQL> create table ogan_deneme
2 as select * from dba_objects;
Table created.
SQL> select count(*) from ogan_deneme;
COUNT(*)
----------
469051
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
no rows selected
SQL> insert /*+ APPEND */ into ogan_deneme
2 select * from ogan_deneme;
469051 rows created.
SQL> select tr.XIDUSN "Undo Segment Numarasi", tr.UBAFIL "Undo Blok Adresi", tr.START_TIME "Baslangic Zamani",
tr.USED_UBLK "Kullanilan Undo Bloklari", tr.USED_UREC "Kullanilan Undo Kayitlari", LOG_IO "Mantiksal I/O", PHY_IO "Fiziksel I/O"
from v$transaction tr, v$session se
where tr.ses_addr = se.saddr
and username='OGAN';
Undo Segment Numarasi Undo Blok Adresi Baslangic Zamani Kullanilan Undo Bloklari Kullanilan Undo Kayitlari Mantiksal I/O Fiziksel I/O
--------------------------------------------------------------------------------------------
63 0 12/21/10 11:44:23 1 1 4326 3874
Buffer cache'i append hint'i ile bypass ettiğimiz için fiziksel I/O oranımız da arttı. Bununla birlikte undo üretimi de bypass edilmiş oldu. Append hint'i ile yapılan direkt yüklemelerde tabloya erişim commit veya rollback yapana kadar engellenir. Bu yükleme tipinde ayrıca boş alanlar kullanılmaz, yalnızca HWM (High Water Mark) üzerinde yükleme yapılır. Yine direkt yükleme ile buffer cache de bypass edilmiş oldu ve veri dosyalarına yazım gerçekleşti. Transactional sistemler için kullanılmayan ancak veriambarı sistemlerinde kullanılabilecek bir yükleme tipidir. Bir önemli nokta, tablo üzerinde bir referans anahtar varsa, append hinti gözardı edilir. Örnek;
SQL> drop table ogan_deneme purge;
Table dropped.
SQL> create table ogan_deneme
2 (
3 userid number primary key,
4 username varchar2(50)
5 );
Table created.
SQL> create table ogan_deneme_2
2 (
3 user_id_ref number references ogan_deneme(userid),
4 username varchar2(40)
5 );
Table created.
SQL> insert /*+ APPEND */ into ogan_deneme
2 select * from ogan_deneme;
0 rows created.
SQL> select count(*) from ogan_deneme;
select count(*) from ogan_deneme
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> create table ogan_deneme_2
2 (
3 user_id_ref number references ogan_deneme(userid),
4 username varchar2(40)
5 );
Table created.
SQL> insert /*+ APPEND */ into ogan_deneme_2
2 select * from ogan_deneme;
0 rows created.
SQL> select count(*) from ogan_deneme_2;
COUNT(*)
----------
0
--> Bir foreign key'in varlığı APPEND hint'inin dışarıda bırakılmasını sağladı.
Sonuç olarak redo üretimi ile undo üretimi bağlıdır. SQL sorgularında undo üretimini direkt olarak bypass etme şansınız yoktur ancak redo ile bağlantılı olarak undo üretimi azaltılabilir. APPEND hint'inin kullanım yeri de yukarıda gösterdiğim gibidir. APPEND hint'i ayrıca tablonun LOGGING veya NOLOGGING olmasına göre değişebilmektedir.
İyi çalışmalar.
Ogan
Hiç yorum yok:
Yorum Gönder