HTF Paketi Kullanarak Mail İçeriği Oluşturmak

Herkese Selam,

Bu yazıda Oracle Veritabanı üzerinden mail atarken, atılacak mailin içeriğini oluşturabildiğimiz tekniklerden biri olan HTF(hypertext function) paketinden biraz bahsedip bir örnek vereceğim. 

HTF paketi basit anlamı ile html tag i üreten ve bu tag ler içine veri yazmamızı sağlan paketlerden bir tanesi. Özellikle mail içeriğinde bir rapor sunucak isek, email içeriğinin derli toplu olması ve bir tablo içinde oluşması mailin okunabilirliği açısından oldukça önemlidir. HTF paketi ile bu amaca hizmet edecek mail içerikleri generate edip, ilgili kişilere veritabanı üzerinden gönderebiliriz. HTF paketi ile ilgili detay spec e ve özellikkeri aşağıdaki linkten erişebilirsiniz. 

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/w_htf.htm

Şimdi 2 tane fonksyon yazalım, bunlardan ilki bir mail template(bu örnek için tablo) oluştursun, diğeride oluşmuş template ‘in içeriğini doldursun. 

FUNCTION genarate_mail_template
RETURN CLOB
IS
mailtemplate CLOB;
BEGIN
DBMS_LOB.createtemporary (mailtemplate, TRUE);
mailtemplate := HTF.title (‘Querry’ || ‘–’ || ‘Querry Report’)
|| CHR (10);
mailtemplate :=
mailtemplate
|| HTF.header (3,
‘Database’
|| ‘– ‘
|| ‘IO Querry Report’
|| ‘ ‘
|| TO_CHAR (SYSDATE, ‘DD/MM/YYYY HH24:MI:SS’),
‘CENTER’
)
|| CHR (10);
mailtemplate :=
mailtemplate
|| HTF.tableOpen (‘BORDER=1′, ‘CENTER’, NULL, NULL, ‘CELLPADDING=0′)
|| CHR (10);
mailtemplate := mailtemplate || HTF.tableRowOpen || CHR (10);
mailtemplate :=
mailtemplate
|| HTF.tabledata (HTF.strong (‘SQL ID.’), ‘CENTER’)
|| CHR (10);
mailtemplate :=
mailtemplate
|| HTF.tabledata (HTF.strong (‘SQL TEXT’), ‘CENTER’)
|| CHR (10);
mailtemplate :=
mailtemplate
|| HTF.tabledata (HTF.strong (‘DISK READS’), ‘CENTER’)
|| CHR (10);
mailtemplate :=
mailtemplate
|| HTF.tabledata (HTF.strong (‘OPERATION’), ‘CENTER’)
|| CHR (10);
mailtemplate :=
mailtemplate
|| HTF.tabledata (HTF.strong (‘OSUSER’), ‘CENTER’)
|| CHR (10);
mailtemplate :=
mailtemplate
|| HTF.tabledata (HTF.strong (‘TERMINAL’), ‘CENTER’)
|| CHR (10);
RETURN (mailtemplate);
END;

FUNCTION genarate_email_content (mailtemplate CLOB)
RETURN CLOB
IS
mailtext CLOB;
BEGIN
DBMS_LOB.createtemporary (mailtext, TRUE);
mailtext := mailtemplate;

FOR myrec IN curquerry
LOOP
mailtext := mailtext || HTF.tableRowOpen || CHR (10);
mailtext :=
mailtext || HTF.tabledata (myrec.sql_id, ‘CENTER’)
|| CHR (10);
mailtext :=
mailtext || HTF.tabledata (myrec.sql_text, ‘CENTER’)
|| CHR (10);
mailtext :=
mailtext || HTF.tabledata (myrec.disk_reads, ‘CENTER’)
|| CHR (10);
mailtext :=
mailtext || HTF.tabledata (myrec.operation, ‘CENTER’)
|| CHR (10);
mailtext :=
mailtext || HTF.tabledata (myrec.osuser, ‘CENTER’)
|| CHR (10);
mailtext :=
mailtext || HTF.tabledata (myrec.terminal, ‘CENTER’)
|| CHR (10);
END LOOP;

RETURN (mailtext);
END;

 

generate_mail_template metodu ile dolacak html imizi template olarak hazırladık, generate_email_content metodu ilede dönen template’imizin içinde cursor dan dönen data(all_) ile satır satır doldurduk. Bu aşamadan sonra elimizdeki text i utl_smtp aracılığı ile mail i alacak kişilere gönderebiliriz. Örnek çıktı aşağıdaki gibi olabilir.

Oracle e-Business Suite Day 2013

Herkese Selam,

TROUG, un Kıraça Holding’in sponsorluğunda düzenlediği e-Business Suite 2013 etkinliği 13 Mayıs 2013 te Pera Müzesinde yapılacak. Etkilik hakkında detaylı bilgi almak ve  kayıt olmak için umit.varol@kiraca.com.tr adresine email atınız.

Dağıtık Sorgularda Performans İyileştirmesi (DRIVING_SITE)

Herkese Selam,

Bu yazıda sizlerle dağıtık sorguların performans iyileştirmesi ile ilgili driving_site hintinden ve kullanımından bahs edicem. Umarım farkındalık anlamında faydalı bir yazı olur.

IT dünyasında genellikle farklı veritabanlarında genellikle büyük boyutlu datalar üzerinde oldukça sık işlem yapıyoruz ve bu işlemlerimizi  OLTP ve OLAP sistemlerde dblink’ler aracılığı ile yürütüyoruz. Hepimizin deneyimlediği gibi farklı veritabanlarıdaki datalar üzerinden işlem yapılmaya başlandığı zaman araya network’ude koyduğumuzdan ötürü sorgu performanslarında bir miktar yavaşlama olduğunu görüyoruz. Bu yavaşlama network’e çıkardığımız data miktarı arttığında, lineer olarak performans üzerinde negatif etki yaratarak sorgunun dahada yavaşlamasına neden olmakta. Tek dblink üzerinden farklı makinadaki bir datayı paralel’de çekemiyeceğimizden ötürü performans anlamında yapacak çokta birşey kalmıyor. İşte tam bu noktada Oracle’ın driving_site hinti devreye girip dağıtık sorgu performansımızı geliştirmeye yönelik pozitif etki yapıyor. Bu etkiyi daha iyi ifade edebilme adına bu noktadan sonra örnekler ile ilerleyeceğim. Örnekleri Oracle 10g R2 sürümünde gerçekledim.

Öncelikle 2 Farklı Veritabanım var, A ve B

Şimdi A ve B veritablarında tablo yaratıp örnek sorgu çalıştırıp performansı inceleyelim.

A Veritabanındaki İşlemler

———————————————————————-

CREATE TABLE departments(department_id INTEGER, department_name VARCHAR2(100));

INSERT INTO departments
VALUES (1, ‘finans’);

INSERT INTO departments
VALUES (2, ‘Satis’);

INSERT INTO departments
VALUES (3, ‘Uretim’);

INSERT INTO departments
VALUES (4, ‘Bilgi Islem’);

INSERT INTO departments
VALUES (5, ‘HR’);

COMMIT;

———————————————————————-

B Veritabanındaki İşlemler

———————————————————————-

CREATE TABLE musteri (musteri_no INTEGER, ad VARCHAR2(1000), departments INTEGER);

– Index kullanabilecegim bir sorgu yazmak istedigimden
–departments kolonuna index atiyorum.

CREATE INDEX ind_dep ON musteri(departments);

– Tablo içine 50 bin, departments kolonu 5 olan kayit ekledim.
BEGIN
FOR i IN 1 .. 50000
LOOP
EXECUTE IMMEDIATE ‘insert into musteri values(:1,:2,5)’
USING i, ‘ad_’ || i;
END LOOP;

COMMIT;
END;

– Tablo içine 1M, departments kolonu 1,2,3,4 olan kayıt –ekledim.
BEGIN
FOR i IN 1 .. 1000000
LOOP
EXECUTE IMMEDIATE ‘insert into musteri values(:1,:2,:3)’
USING i, ‘ad_’ || i, MOD (i, 4) + 1;
END LOOP;

COMMIT;
END;

———————————————————————-

Şimdi  A ve B veritabanında 2 tablo oluşturduk. A veritabanındaki departments tablosu bir lookup ve içeride toplam 5 kayıt var. B veritabanındaki müşteri tablosu oda bir lookup ve içinde 1050000 kayıt var. Şimdi A veritabanında aşağıdaki sorguları çalıştırıp sonuçları inceleyelim.

İlk etapta hiç hint kullanmadığımız yöntemde execution plan ile sorgu çalışma zamanına bakalım.

EXPLAIN  PLAN FOR SELECT a.ad, b.department_name
FROM musteri@myLink a, departments b
WHERE a.departments = b.department_id
AND a.departments = 5;

SET linesize 2000
SELECT *
FROM TABLE (DBMS_XPLAN.display);

Evet execution plan’dan da görüldüğü üzere yazdığımız sorgu karakteristiği gereği index kullanması gerekirken dblink ile erişim yaptığımızdan dolayı karşı taraftaki tablo datasının tamamını network’e çıkardı, datanın tamamını sorguyu çalıştırdığımız tarafa getirdi ve işlemi kendi local’imizde yaptı. Ayrıca dblink ile karşı tarafta bir tabloya bağlandığımız için local makinemiz karşı tarafın metadata’sından haberdar değil bu yüzden var olan index’ide kullanamadı. Şimdi sorgunun ne kadar sürede bittiğini görelim.

SET timing on
SELECT a.ad, b.department_name
FROM musteri@myLink a, departments b
WHERE a.departments = b.department_id                                       AND a.departments = 5;
/*
 50000 rows selected.
Elapsed: 00:00:29.63
*/

Şimdi driving_site hintimiz’i yazarak execution plan inceleyip sorgunun ne kadar sürede bittiğini görelim.

EXPLAIN  PLAN FOR SELECT /*+ DRIVING_SITE(a) */ a.ad, b.department_name
FROM musteri@myLink a, departments b
WHERE a.departments = b.department_id
AND a.departments = 5;

SET linesize 2000
SELECT *
FROM TABLE (DBMS_XPLAN.display);

Evet execution plan’dan da anlasildigi uzere sorgumuz index kullanabildi. Bunun nedeni yazdığımız hint kaynaklı local’imizde bulunan küçük tabloyu karşı tarafa transfer edip işlemi karşı tarafta yapıp sonuç dataset’ini network’e çıkaraıp işlemi tamamladı. Böylelikle hem network üzerine ihtiyacımız olmayan dataları çıkarmadı hemde işlemi remote’da yaptığından dolayı index kullanabildi. Şimdi sorgumuzun performansına bakalım.

SET timing on
SELECT /*+ DRIVING_SITE(a) */
a.ad, b.department_name
FROM musteri@myLink a, departments b
WHERE a.departments = b.department_id AND a.departments = 5;

/*
50000 rows selected.
Elapsed: 00:00:20.80
*/

Evet sonuçlardan da anlaşıldığı üzere bu tarz case’lerde işlemi remote’da yaptırarak sorgu performansımızı oldukça iyi seviyelere çekebiliriz. Ancak hint yazmak riskli bir işlem olduğundan kullanmadan önce sorgumuzun karakteristiğini iyi incelememiz gerekmektedir. Aksi takdirde pozitif etki beklerken negatif sonuçlar elde edebiliriz.

Kaynaklar

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm

http://www.dba-oracle.com/t_driving_site_hint.htm

http://rohanrajkalra.blogspot.com/2008/11/drivingsite-hint-and-distributed.html

Veriye Etkin Erişim Yöntemleri – 3

Herkese Selam,

Bu yazı dizisi kapsamında daha önce;

Veriye Etkin Erişim Yöntemleri – 1

Veriye Etkin Erişim Yöntemleri – 2

yazılarını paylaşmıştım. Veriye Etkin Erişim Yöntemleri – 2 yazısında selectivity’nin zayıf olduğu (1′e yaklaştığı) durumlarda veriye hangi metodlarla etkin bir şekilde erişebileceğimizi konuşmuştuk, bu yazıdada selectivity’nin güçlü olduğu (0′a yaklaştığı) durumlarda etkin erişim yönetemlerinin neler olduğu üzerinde konuşucaz. Umarım farkındalık anlamın faydalı bir yazı olur.

Selectivity’nin güç olduğu durumlarda etkin bir şekilde veriye eriştiğimiz yöntemlerin başlıcaları;

- Rowid Access

- Index Access

Şimdi sıra ile bu yöntemleri ve kullanım durumlarını detaylandıralım;

Rowid Access

Öncelikle Rowid kavramından kısaca bahsetmek istiyorum. Rowid en temel ifade ile, ilgili row’un database’deki adresini gösteren bir belirteçtir. Bu belirteç 10 byte’dır  ve aşağıdaki alanlardan oluşmaktadır.

Örnektende anlaşıldığı gibi rowid 10byte’lık bir belirteç. Aynı zamanda Oracle’da pseudo bir kolon işlevide görmekte. Arzu ettiğimiz satırın rowid sinin select cümlesine rowid kolonunuda ekleyerek görebiliriz (select rowid, emp_no …). Yukarıdaki örnektende görüldüğü gibi bu numara kendi içinde bazı bilgiler taşımakta bu bilgileri ayrı ayrıda görüntüleyebilmekteyiz. Bunun için DBMS_ROWID paketini kullanabiliriz. Bunu daha iyi anlayabilmek için aşağıdaki örneği inceleyebiliriz.

Evet locations tablosunun her satırının rowid bileşenlerini incelediğimiz zaman datanın fiziksel anlamda bir arada durup durmadığınıda gözlemleyebiliyoruz. Yukarıdaki örnekte, aynı tablo içindeki tüm datanın aynı bloğa toplanmış olduğunu bu yöntem ile görebildik.

Rowid ile data erişimi selecttivity’nin güçlü olduğu durumda veriye en etkin erişimi sağlayan yöntemdir. Belirli bir satıra erişmenin en etkin ve efektif yolu rowid ile erişmektir. Ancak bu etkin yolu kullanabilmek için ilk başta rowid yi çekip saklamak gerekmektedir. Daha sonra bir sonraki aynı satıra erişimizimizde doğrudan bu bilgiyi kullanabilmekteyiz. İkinci erişimde rowid ile ilgili satıra gitmek ilgili satıra gitmenin en hızlı yöntemidir. Ancak burada satırın rowid’sinin değişmediği ve o süreç içerisinde değişmeyeceği garanti edilmelidir. Aksi takdirde ilgili rowid ye karşılık ilgili kayıt bulunamayacak ve işlem yapılamıyacaktır.

Rowid kullanabileceğimiz en sık durumlardan birisi, ekran geliştirilen projelerde kayıtların update edildiği ekranlar olacaktır. Bu ekranlarda ilk etapta kayıdı getirmek için select atarız ve bu select içerisine birde rowid kolonunu ekleriz. Daha sonra update işlemini yaparken update işlemini çektiğimiz rowid üzerinden yaparak, yaptığımız işlemi oldukça hızlandırabiliriz. Aşağıdaki örnekten bu durumun kullanımını inceleyebilirsiniz.

Önce Update edilecek kayıt DB’den rowid si ile beraber çekilir

SELECT ROWID, first_name, last_name, department_id, start_date, salary,
contractor
FROM employee_comp
WHERE emp_id = 200;

Kayıt formda gösterilir

Şimdi update işlemi yapılmak için form submit edilir ve veriye erişim rowid üzerinden gerçekleştirilir.

UPDATE employee_comp
SET salary = 10000
WHERE ROWID = ‘AAAPecAAFAAAABSAAA’

Yukarda gösterdiğimiz örnek rowid’nin en sık kullanılabileceği durumlardan biridir. Buna benzer case’lerimizin olduğu her yerde dataya en etkin şekilde rowid ile erişebiliriz.

Index Access

Selectivity’nin güçlü olduğu durumlarda index ile veriye erişmek, veriye erişimde en sık kullanılan yöntemdir. Çünkü index ile veriye erişmek, logical read sayımızı ve bunun sebebiyet vereceği physical read sayısını oldukça düşürecektir. Selectivity’si strong olan veriye index ile erişmek, veriye erişim hızımızı ciddi şekilde etkilemektedir. Bu durumu bir örnek ile inceleyelim.

Öncelikle bir tablo yaratalım ve içine 1 milyon kayıt insert edelim;

CREATE TABLE ind_deneme(col NUMBER,col2 VARCHAR2(50));

/

BEGIN
FOR i IN 1 .. 1000000
LOOP
EXECUTE IMMEDIATE ‘INSERT INTO ind_deneme
VALUES (:1,”IND_DENEME_X”)’
USING i;
END LOOP;

COMMIT;
END;

Şimdi tablomuza index atmadan selectivity’si düşük olacak bir sorgu çalıştıralım ve çalışma zamanı ile beraber execution plan’nına bakalım.

SET timing on
SET autotrace on
SELECT *
FROM ind_deneme
WHERE col < 5000;

– Sonuç

4999 rows selected.
Elapsed: 00:00:03.36

Execution Plan
———————————————————-
0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=901 Card=36 Bytes=1 K)
1    0    TABLE ACCESS FULL IND_DENEME (Cost=901 Card=36 Bytes=1 K)

Evet şimdi aynı sorguyu col kolonuna index atıp tekrar deneyip sonucu gözlemliyelim.

CREATE INDEX ind_col ON ind_deneme(col);

– Sonuç

4999 rows selected.
Elapsed: 00:00:02.05

Execution Plan
———————————————————-
0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=19 Card=2 K Bytes=97 K)
1    0    TABLE ACCESS BY INDEX ROWID TTDE_BILLING.IND_DENEME (Cost=19 Card=2 K Bytes=97 K)
2    1      INDEX RANGE SCAN IND_COL (Cost=9 Card=2 K)

Evet sorgunun selectivity’nin düşük olduğu durumlarda index kullanarak veriye eriştiğinde daha hızlı ve efektif sonuçlar ürettiğini görmüş olduk. Ancak istisnai durumların varlığını sorgularımızı yazarken mutlaka göz önünde bulundurmamız gerekir.

Index kullanımı oldukça sık kulanılan veri erişim yöntemidir ancak, çalıştırdığımız sorgunun karakteristiği performansı oldukça etkilemektedir. Örneğin tablo içindeki data’nın büyük bir çoğunluğunu çekmeye çalışan sorguda index kullnarak veriye erişmeye çalışmak sistem performansını drastic bir şekilde düşürecektir. Önnceki yazılarımdada değindiğim gibi bu tarz durumlarda erişim metodumuzun full table access olması performansımızı arttıracaktır.

Index li erişimin performansını etkiliyen durumlardan biride clustering factor’dür ancak bu konu özelinde bu durumu anlatmak yazıyı oldukça sıkıcı bir hale getireceğinden bu konuyu başka bir yazıya bırakıyorum ancak fikir edinme açısından aşağıdaki link okunabilir.

http://www.dba-oracle.com/art_index_clustering_factor.htm

Index’li erişim özellinde çok kısa Index Organized Table‘lardan bahsedicem. Index Organized Table’ların ana fikri tablo segmentlerini okumaktan kaçınmak çünkü index segmentleri tablo segmentlerinden daha küçüktür. Buda daha az lojik okuma yapmamız anlamına gelmektedir. Eğer P.K üzerinden sıkça sorgu yaptığımız bir tablo durumu söz  konusu ise ve bu tablodaki data karakteristiği indexli bir şekilde tutulmaya elverişli ise bu tabloyu IOT formatında tutmak performansı artırır. Böylelikle data hep clustered olarak saklandığından pk üzerinden yapılan range scan sorgulamaları çok efektif ve etkili çalışır. Data primary key üzerinden organize edilmiş bir şekilde tutlduğu içinde olası bir order by işlemi çok hızlı sonuç döndürür.

Şu kısıma kadar anlattığım kısım Oracle’ın B-Tree index altyapısı ile çalışan mekanizmasıydı. Ancak Oracle’ Bitmap Index adı altında bu ürünü rakiplerine göre üstün kılan bir index yapısı daha mevcut. Kısaca bitmap indexler içinde bir kaç cümle söylnemek gerekirse;Bitmap indexler DWH sistemlerinde kullanılmaya oldukça müsait index yapılarıdır. Distinct value değeri sınırlı olan kolonlar üzerinden yapılmalıdır. Cardinalty si düşük bir kolonum varsa ve tablomda çok büyükse, bitmap index btree index e göre daha performanslıdır. Ancak sorguda birden çok or lu and condition ının birleşmesi gerekliki bitmap indexin performansı artsın.

B-tree ve Bitmap index mekanizmasının karşılaştırıldığı güzel bir makale var.  Bu makale ile durumu daha iyi anlayabilirsiniz. Bitmap Index vs. B-tree Index: Which and When?

Bu yazıda selectivity’nin güçlü olduğu sorgularda hangi yöntem ile veri erişiminin ne denli avantaj getirdiği ve entkin veri erişiminin nasıl olduğunu paylaştım. Umarım farkındalık anlamında başarılı bir yazı olmuştur.

Kaynaklar

TroubleShooting Oracle Performance, Christian Antognini, 2008

“Expert Oracle Database Architecture 2011″, Tom KYTE

http://docs.oracle.com/cd/E14072_01/server.112/e10713/indexiot.htm

http://docs.oracle.com/cd/B10501_01/server.920/a96524/c13datyp.htm#918

http://www.orafaq.com/wiki/ROWID

http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

2012 in review

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

4,329 films were submitted to the 2012 Cannes Film Festival. This blog had 14,000 views in 2012. If each view were a film, this blog would power 3 Film Festivals

Click here to see the complete report.

Oracle’da After LOGON Trigger

Herkese Selam,

Bu yazıda sizlerle Oracle’da şemaya erişim anlamında güvenliği bir kademe daha arttıracağınız bir yöntemi aktaracağım. Bu yöntem ile ilgili şema ya login olmak isteyen user’ları ip adresine göre, osuser name’lerine göre veya bildiğiniz ve check edebileceğiniz farklı özelliklerine göre connection kurmalarına izin verebilir yada izin vermeyebilirsiniz. Bu kontrolü oracle üzerinde After Logon tipinde bir trigger yazarak sağlayabiliyoruz. Bu trigger ilgili şemaya user bağlandığı anda devreye girip gerekli kontrolleri yapıp connection sağlamasına izin veriyor veya vermiyor.

Şimdi bir örnek üzerinden bu sürecin nasıl çalıştığını anlatalım.

Öncelikle yeni bir user yaratıyorum ve gerekli hakkı veriyorum;

CREATE      USER emrah IDENTIFIED BY emrah;
ALTER      USER emrah ACCOUNT UNLOCK;

ALTER      USER emrah DEFAULT TABLESPACE myTableSpace;

GRANT UNLIMITED TABLESPACE TO emrah;

GRANT CREATE TABLE TO emrah;

GRANT CREATE TRIGGER TO emrah;

şimdi yeni yarattığım user’a login oluyorum.

CREATE TABLE login_control
( ID NUMBER PRIMARY KEY,
osuser_name VARCHAR2(20),
ip_address VARCHAR2(30));

INSERT INTO login_control
(ID, osuser_name, ip_address
)
VALUES (1, ‘EmrahMete’, NULL
);

INSERT INTO login_control
(ID, osuser_name, ip_address
)
VALUES (2, NULL, ’10.10.2.002′
);

INSERT INTO login_control
(ID, osuser_name, ip_address
)
VALUES (3, NULL, ’10.10.2.003′
);

INSERT INTO login_control
(ID, osuser_name, ip_address
)
VALUES (4, NULL, ’10.10.2.04′
);

INSERT INTO login_control
(ID, osuser_name, ip_address
)
VALUES (5, ‘albertSenior’, NULL
);

Evet olusturduğum tabloya os user name veya ip numarası tanımladım. Şimdi emrah şemasına connect olmak isteyen kullanıcıların bu tabloda tanımlı olup olmadıklarına bakıp erişim izni vereceğim trigger’ı yazıyorum.

CREATE OR REPLACE TRIGGER emrah.login_trg
AFTER LOGON ON DATABASE
DECLARE
v_cnt_os        NUMBER        := 0;
v_cnt_ip        NUMBER        := 0;
v_os_username   VARCHAR2 (30);
v_ipaddress     VARCHAR2 (30);
BEGIN

– gelen user’ın ip adresini aliyorum
SELECT SYS_CONTEXT (‘userenv’, ‘ip_address’)
INTO v_ipaddress
FROM DUAL;
– gelen user’ın os user name ini aliyorum
SELECT SYS_CONTEXT (‘userenv’, ‘os_user’)
INTO v_os_username
FROM DUAL;

SELECT COUNT (osuser_name)
INTO v_cnt_os
FROM login_control
WHERE osuser_name = v_os_username;

SELECT COUNT (ip_address)
INTO v_cnt_ip
FROM login_control
WHERE osuser_name = v_ipaddress;

IF v_cnt_ip = 0 AND v_cnt_os = 0
THEN
raise_application_error
(-20001,
‘Semaya baglanmak icin tanimli bir kullanici degilsiniz, lutfen veritabani yoneticiniz ile iletisime geciniz’
);
END IF;
END;

şimdi login_control tablosunda tanımlı olmayan bir ip adresi ve kullanıcı adı ile login olmaya çalışıyorum.

evet görüldüğü üzere login olamadan trigger’ın bana fırlattığı hatayı aldım. EmrahMete OS user’ının sahip olduğu makinadan girş yapmaya çalıştığımda ise herhangi bir sorun ile karşılaşmadan login olma işlemini gerçekleştirdim. Bu yöntem ile bazı şemalarda güvenlik önlemini şema seviyesindede alıp connection’ları yönetebilirsiniz.

Umarım farkındalık anlamında faydalı bir yazı olmustur.

Veriye Etkin Erişim Yöntemleri – 2

Herkese Selam,

Bu yazıda sizlerle daha önce yazmaya başlamış olduğum Veriye Etkin Erişim Yöntemlerini kaldığım yerden anlatmaya devam edeceğim. Veriye Etkin Erişim Yöntemleri – 1 başlıklı yazımda bu konuya bir giriş yapmıştım ve selectivty üzerinden hangi erişim yöntemlerinin uygulanabileceğini anlatmıştım. Bu yazıdada selectivity si zayıf (Weak) sorgularda uygulanan veri erişim yöntemini anlatacağım.

Selectivity’nin zayıf olduğu durumlarda optimizer’ın aşağıdaki veri erişim yöntemlerinden birini kullanması muhtemeldir. Şimdi bu yöntemleri sırası ile inceleyelim.

-Full Table Scans

- Full Partition Scans

- Full Index Scans

FULL TABLE SCANS

Basit bir ifade ile tabloya ait tüm data bloklarının okunduğu veri erişim yöntemidir. Okunacak tüm data blokları o tabloya ait High Water Mark’ın gösterdiği noktanın altında kalan data bloklarıdır. High Water Mark daha önceki yazılarımda da anlattığım üzere tablo insert aldıkça seviyesi yükselen bir pointer’dır. Ne zaman ilgili tablo ile alakalı bir FULL TABLE SCAN kararı optimizer tarafından alınırsa ozaman ilgil HWM’ın gösterdiği noktanın altındaki tüm data blokları okunur.

Buraya kadar her hangi bir problem yok ancak tablo delete aldığı durumlarda HWM’nin konumu değişmemekte. Buda FULL TABLE SCAN kararı çıkmış sorgularda çoğu zaman fazladan okuma yapılarak performansın düşmesi anlamına gelmekte. Bu tarz durumlarda FULL TABLE SCAN performansını arttırmak için HWM’ın yerini değiştirecek operasyonlar yapmak gerekmektedir. Bu operasyonlar;

- Alter Table Enable Row Movement

-  Alter Table Shrink Space

gibi operasyonlardır. Eğertablo içinde tüm datayı temizliyeceksek ve bu datanın rollback edilmiyeceği konusunda eminsek bu durumdada truncate table deyimini kullanarak HWM nin yerini değiştirip FULL TABLE SCAN performanslarımızı arttırabiliriz. High Water Mark ile ilgili detaylı bilgiye Oracle’da High Water Mark Kavramı başlıklı yazımı okuyabilirsiniz.

Şimdi bir örnekle bu durumu inceleyelim.

1- 2 Kolonu olan bir tablo yaratalım.

CREATE TABLE hwm_deneme(col NUMBER,col2 VARCHAR2(50));

2- Daha Sonra HWM şuan başlangıç konumundayken tablomuza bir count sorgusu atalım ve ne kadar sürdüğüne bakalım.

SET timing on;
SELECT COUNT (*)
FROM hwm_deneme;
–Elapsed: 00:00:00.25

3- HWM’nin konumunu değiştirmek için tabloya 10 Milyom kayıt yazalım ve bu 10 milyon kayıdı silip yeniden bir count sorgusu atıp ne kadar sürdüğüne bakalım.

BEGIN
FOR i IN 1 .. 10000000
LOOP
EXECUTE IMMEDIATE ‘INSERT INTO hwm_deneme
VALUES (:i, ”HWM_DENEME”)’
USING i;
END LOOP;

COMMIT;
END;

–Tablodaki tüm kayitları temizleyim.

SET timing on;
DELETE      hwm_deneme;

SET timing on;
SELECT COUNT (*)
FROM hwm_deneme;
–Elapsed: 00:00:02.06

Evet örnekten de görüldüğü gibi tablomuzda kayıt olmamasına rağmen HWM’nin gösterdiği yer insert işlemi sonunda gösterdiği nokta olduğu için Oracle boş yere I/O yapmak zorunda kalmıştır. Bundan dolayı sorgumuzun çalışma süresi, tabloyu ilk yarattığımızda çalıştırdığımız sorgumuzun süresine göre daha yavaş olmuştur.

Partitioning

Selectivitynin çok zayıf olduğu durumlarda full table scan yapmak veriye erişmek için en etkili yöntem ancak selectivitynin  sıfıra yaklaşmaya başladığı durumlarda bir çok blok gereksiz yere okunmaya başlanır. Selectivitynin zayıf ancak sıfıra yaklaşmaya başladığı durumlarda index kullanmakta efficient bir yöntem değildir. Bu tarz durumlarda logical reads sayısını düşürmek için seçilecek en etkin yöntem partitionlamadır. Partitioning datayı verdiğimiz koşullar doğrultusunda fiziksel parçalara bölerek ilgili parçaları aynı fiziksel alanda tutma yöntemidir. Oracle’ın en güçlü özelliklerinden’de biridir. Partition’lı bir tabloda Optimizer gelen sorguya bakarak ilgilenmediği partitionları okumadan sadece ilglendiği partitionları okuyarak selectivity’si zayıf olan sorgularda ciddi performans artışları sağlamaktadır. Partitioning temelde 3 farklı şekilde yapılabilir bunlar;

Range Partition: Doğal olarak sıralı olan durumlarda örneğin belirli bir zaman aralığında öncesinde sonrasında..

List Partition: Distinct değer sayısı limitli olan kolonlar için önerilir. Örneğin, cinsiyet, status,country,postal_codes gibi

Hash Partition:Distinct değer sayısı çok fazla olan kolonlar için kullanılması önerilir. Örneğin customer_id, sales_id …

Şimdi bir örnek ile Partitioning yapılan bir sorguyu inceleyelim.

Sorgu geldiğinde optimizer where conditionına bakar ve partition keyin join conditionını inceleyerek ilgili datayı hangi partitionda arayacağını data dictionary ye bakarak karar verir ve ilgisi dışında olan partitionları budar (Partition pruning). Partition pruning yapılabilmesi için partition keylerin where koşulunda mutlak suretle olması beklenir aksi takdirde optimizer’ın datanın hangi partitionda aranacağını bilemez ve tüm partitionları tarayarak full table scan yapmak zorunda kalır.

Yukarıdaki örnekte data 12 ay’a(d1) ve her ayda kendi içinde 4 parçaya bölünmüş(n1)  toplam 48 partition’lı bir tablo görmekteyiz. Bu tabloya yukarıdaki sorgu geldiğinde n1=3 olan kayıtların bulunduğu partition ile d1= ’2007-07-19′ tarihine denk gelen partitionların kesiştiği nokta bulunur. Bu bulunan partition gerçekten aradığımız datanın bulunduğu partition’dır. Yalnızca bu partition’ın okunması bizim için yeterli bir durumdur. Bu işlem kaynaklı performansımız oldukça gelişecek ve fazladan lojik,  fiziksel okuma yapıp kaynaklarımızı meşgul etmeyeceğiz.

FULL INDEX SCANS

Index ler yalnızca, kendileri üzerinden rowid listesi çıkarıp bu rowidlere karşılık kayıtları bulmak için tablolara gitmezler, indexler doğal olarak tablo üzerindeki bir yada birden çok kolonu key olarak(composite yada composite olmayan indexler) aldıklarından ötürü üzerlerinde key olarak aldığı data yıda barındırmaktadırlar. Eğer gelen sorgunun dönüşü index key’in olduğu kolon ise rowid’yi alıp tabloya gitme gereksinimi kalmadan index keyi okuyup geriye sorgu sonucu olarak döndürebilmektedir. Bu sayede çok önemli bir veri erişim optimizasyonunu oracle kullanıcılarına sağlamaktadır.

Eğer index querry nin ihtiyaç duyduğu tüm datayı üzerinde barındırıyorsa, full table scan, full partition scan yerine full index scan yapılarak sorgu sonucu döndürülür. Full index scan, full table ve full partition scana göre çok daha efektif ve az lojik okuma yapan bir yöntemdir. Bunun nedenide index segmentlerinin tablo segmentlerinden daha ufak olmasıdır.

Örneğin customer tablomuz üzerinde customer_id kolonu üzerinde bir index olsun. customer tablosuna şöyle bir select gönderirsek;

select customer_id from customer where customer_id >500;

Sorgu sonucu disk üzerinde bulunan tabloya ait data bloklarına gitmeden direk döner. Çünkü sorgunun cevabı doğrudan index içinde mevcut, Oracle bu durumda full index scan yaparak tüm datayı indexten okur ve işlemi bitirir.

Eğer index buffer cache de yoksa, index disk üzerinden getirilmeye başlanır. İndexin büyük olduğu durumlarda okunacak blok sayısı artacağından dolayı daha fazla blok okuması yapılacak. Index full scan yöntemi blokları tek tek okuduğundan toplam blok sayısı, toplam okuma sayısına eşit olucak ve yöntem inefficient hale gelicek. Bu tarz durumlarda multiblock read tekniğini kulnnabilen index_fast_full scan yöntemini gerekli hinti verek kullanmak okuma sayısını düşürüp etkin bir yönteme sistemizmizi tekrar çevirebilir.

Yukarıda  anlattığım yöntemlerin her biri nitelikli Oracle’kaynaklarında ayrı ayrı chapter’lar şeklinde anlatılan konulardır daha detaylı bilgi için referans verdiğim kaynakları takip edebilirsiniz. Umarım farkındalık anlamında faydalı bir yazı olmustur.

Kaynaklar:

TroubleShooting Oracle Performance, Christian Antognini, 2008

“Expert Oracle Database Architecture 2011″, Tom KYTE

http://www.dba-oracle.com/art_9i_indexing.htm

tahiti.oracle.com

docs.oracle.com