Foreign Key’e Sahip Kolonların Farklı Data Uzunlukta Olanları Nasıl Bulunur?

Oracle veritabanında oluşturulmuş foreign keylerin bulunduğu kolonların farklı data uzunluğuna sahip olanlarını aşağıdaki sql ile bulabilirsiniz.

SELECT acol.owner,
acol.table_name,
acol.column_name,
acol.data_type,
acol.data_length,
acol.data_precision,
bcol.owner,
bcol.table_name,
bcol.column_name,
bcol.data_type,
bcol.data_length,
bcol.data_precision
FROM dba_tab_columns acol, dba_tab_columns bcol
WHERE (acol.owner,
acol.table_name,
acol.column_name,
bcol.owner,
bcol.table_name,
bcol.column_name) IN
(SELECT b.owner b_owner,
b.table_name b_tblname,
b.column_name b_column,
c.owner c_owner,
c.table_name c_tblname,
c.column_name c_column
FROM dba_cons_columns b,
dba_cons_columns c,
dba_constraints a
WHERE b.constraint_name = a.constraint_name
AND a.owner = b.owner
AND b.position = c.position
AND c.constraint_name = a.r_constraint_name
AND c.owner = a.r_owner
AND a.constraint_type = 'R') and (acol.data_length <> acol.data_length or acol.data_precision <> bcol.data_precision );

SQL Tune Nasıl Yapılır

Bu yazıda bir sql’i tune edip sorgu sonucunun hızlandırılması konusunda index ekleme yöntemini açıklayacağım. Burada anlatacağım yöntemi oracle, postgresql, mysql ve diğer veritabanlarında kullanabilirsiniz.

İlk olarak index nedir ve neden eklenmesi gerektiğini açıklamak istiyorum. İndex oluşturulmuş bir ağaç yapısıdır ve birden çok index türü vardır. B-tree index yapısını aşağıdaki şekilde inceleyebilirsiniz. Leaf block dediğimiz yerde ilgili tablonun içerisindeki rowların id leri bulunmaktadır. Bunu adres olarak düşünebiliriz. Branch blocks dediğimiz yer ise datanın belirli bir düzene göre gruplandığı yerdir. Aşağıdaki resimde görebileceğiniz gibi job_id kolonunda 2 değerine sahip satırların id numaraları bir blokta tutulmuş. Bu da job_id = 2 koşulu istendiğinde tabloyu full okumak yerine sadece ilgili bloklara gidip okuma yapacağı anlamına gelmektedir.

Tablo full okunmadığı için ilgili sql daha hızlı sonuç üretecektir. Daha hızlı sonuç üretmesi veritabanının fazla efor harcamamasına ve daha performanslı çalışmasına neden olacaktır.

Çok fazla transaction alan veritabanlarında sql’lerin sorgu sonuç süreleri kritik bir öneme sahiptir. Şu şekilde bir hesap yapacak olursak daha anlaşılır olacaktır. Elimizde çalışması 1 sn süren bir sql olsun ve bu sql uygulamadan veritabanına 10 bin kez gönderilsin. Bu işlem veritabanında 1 sn* 10 bin kez çalışacaktır ve veritabanını toplamda 10 bin saniye / 60 = 166 dakika meşgul edecektir. Tabi cpu’nun saniye de milyarlarca işlem yapabildiği için gerçek hayatta bu işlem 166 dakikada sürmeyecektir.

İlgili sql’e index ekleyip çalışma süresini yarıya düşürdüğümüzü varsayarsak toplamda 83 dakika kazanmış olacağız ki bu muazzam bir süre. Bu örnekte saniyeler üzerinden gidiyorum fakat sql’lerin veritabanında çalışma süreleri mili saniyeleri geçmemesi gerekiyor. 0.5 saniye bile bir sorgu için aşırı büyük bir değer.

Şimdi veritabanının bir sorguyu çalıştırırken sonucu getirmek için izlediği yolu göstereceğim. Örnek olarak elimizde aşağıdaki gibi bir sql sorgusu olsun.

select firts_name, last_name from hr.employee where job_id = 2;

Bu sql’i veritabanı şu şekilde yorumlayacaktır:

– job_id kolonunda değeri 2 olan
– hr.employee tablosundaki
– first_name ve last_name kolonundaki tüm verileri getir.

Sorgu bizim aksimize tersten okunarak istenilen dataları getirmek için işlenmeye başlandı. Bu kısımda job_id kolonunda 2 değerine sahip satırlar için tabloyu satır satır okuyup eşleşen kayıtları bulmaya çalışacaktır. İlgili kolonda eğer bir index yok ise explain plan da aşağıda da görebileceğiniz gibi “full table access” yöntemi kullanılacaktır.

OPERATIONOPTIONSOBJECT_NAMEPOSITION
SELECT STATEMENT2
..TABLE ACCESSTABLE ACCESS FULLEMPLOYEES1

Eğer employees tablosunun job_id kolonunda bir index olmuş olsaydı tabloyu full okumak zorunda kalmayacak ve explain plan’ı aşağıdaki gibi olacaktı.

OPERATIONOPTIONSOBJECT_NAMEPOSITION
SELECT STATEMENT2
..TABLE ACCESSINDEX RANGE SCANEMPLOYEES1

Şekilde görüldüğü gibi index range scan yöntemi ile tabloya yapılan erişim sorgu maliyetini düşürmektedir. Elinizdeki sql’leri bu doğrultuda inceleyerek gerekli olabilecek indexleri oluşturabilirsiniz.

Son olarak aşağıdaki gibi bir sql’i ele alalım.

select firts_name, last_name from hr.employee where job_id = 2 and first_name = 'Emrah';

Bu sqli çalıştırırken job_id kolonunda index olmasına rağmen sorgu çalıştırıldığında ilgili index kullanılmayabilir. Bu durumda bileşik index oluşturulması gerekir. İlk oluşturulan basit index tipini ifade etmekteydi. Bu durumda bileşik index oluşturmak zorunda kalınabilirdi. Oracle veritabanında basit ve bileşik index aşağıdaki gibi oluşturulabilir.

# basit index
create index [index_name] on [table_name] (column);

# bileşik index
create index [index_name] on [table_name] (column1,column2);

Oracle Redo Log Switch Map

Oracle veritabanında redo logların saatlik kaç kez switch olduğu bilgisini aşağıdaki sql komutu kullanarak öğrenebilirsiniz.

set linesize 300 
set pagesize 5000
column 00 format 999 heading "00" 
column 01 format 999 heading "01" 
column 02 format 999 heading "02" 
column 03 format 999 heading "03" 
column 04 format 999 heading "04" 
column 05 format 999 heading "05" 
column 06 format 999 heading "06" 
column 07 format 999 heading "07" 
column 08 format 999 heading "08" 
column 09 format 999 heading "09" 
column 10 format 999 heading "10" 
column 11 format 999 heading "11" 
column 12 format 999 heading "12" 
column 13 format 999 heading "13" 
column 14 format 999 heading "14" 
column 15 format 999 heading "15" 
column 16 format 999 heading "16" 
column 17 format 999 heading "17" 
column 18 format 999 heading "18" 
column 19 format 999 heading "19" 
column 20 format 999 heading "20" 
column 21 format 999 heading "21" 
column 22 format 999 heading "22" 
column 23 format 999 heading "23" 
column 24 format 999 heading "24" 
column "Day" for a5 
column INST_ID for a7 
column DAY_S for a8 
column date_s for a14 
prompt 
prompt Redo Log Switches 
prompt 

select  
CASE INST_ID WHEN 1 THEN date_str END date_s,
to_char(INST_ID) INST_ID, 
CASE INST_ID WHEN 1 THEN "Day" END day_s, 
"00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"  from  (SELECT trunc (first_time) date_str,  INST_ID, 
to_char (trunc (first_time),'Dy') "Day", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 0, 1)) "00", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 1, 1)) "01", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 2, 1)) "02", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 3, 1)) "03", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 4, 1)) "04", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 5, 1)) "05", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 6, 1)) "06", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 7, 1)) "07", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 8, 1)) "08", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 9, 1)) "09", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 10, 1)) "10", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 11, 1)) "11", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 12, 1)) "12", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 13, 1)) "13", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 14, 1)) "14", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 15, 1)) "15", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 16, 1)) "16", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 17, 1)) "17", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 18, 1)) "18", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 19, 1)) "19", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 20, 1)) "20", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 21, 1)) "21", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 22, 1)) "22", 
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 23, 1)) "23" 
from gv$log_history 
where trunc(first_time) > sysdate-30 
group by INST_ID, trunc(first_time)  
order by trunc(first_time) DESC, INST_ID );

Redo logların anlık durumlarını kontrol etmek için aşağıdaki komutu kullanabilirsiniz.

set linesize 2000
set pagesize 2000
col REDOLOG_FILE_NAME for a70 
SELECT 
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    (a.BYTES/1024/1024) AS SIZE_MB,
    b.MEMBER    AS REDOLOG_FILE_NAME
FROM v$log a 
JOIN v$logfile b ON a.Group#=b.Group# 
ORDER BY 2, a.GROUP# ASC; 

Zaman içerisinde sql lerin devamını ekleyeceğim.