1. Bölüm:
Bir SQL'in planını nasıl görüntülenir, nasıl okunur?
Start --> Run --> Cmd
sqlplus /nolog
conn /as sysdba
conn sh/sh
set autotrace traceonly explain
select * from sales
sorgusu çalıştırıldığında Sql çalışma yolu ekranda görüntülenir. Sales tablosunda 1 milyon kayıt olduğunu düşünelim.
bu tabloya yazılan select full table scan şeklinde gitmektedir. Yani tabloya full ulaşıp full scan yapmaktadır. FTS cost'u yükseltir.
set autotrace traceonly explain --> sadece trace only'dir yani Sql'in planı ekranda görüntülenir ama Sql çalıştırılmaz.
set autotrace on --> hem query'i çalıştırır hem query çalışırken izlediği yolu yani planını ekranda görüntüler.
set autotrace off --> trace'i kapatır.
execution plan : izleyeceği gideceği yol
sorgudan sonra çıkan plan aşağıdan yukarıya doğru okunmalıdır. Nedeni SQL'in izlediği yol aşağıdan yukarıya doğru ilerler.
Dip not: Tablo bazında istatistik tabloma çok önemlidir. 9i de bu manuel olarak yapılması gerekirken 10g ile birlikte otomatize edilmiştir. Scheduler olarak çalışan GATHER_STATS job'ı gün bazlı saat 22:00 da çalışacak şekilde db kurulduğu andan itibaren gelmektedir. Bu doğrultuda yanlış istatistiki olan tablo bir günlüktür diyebiliriz.
İstatistik sorgulamayı örnekliyoruz:
sqlplus hr/hr
create table emp as select * from employees; --> database de var olan employees tablosunun bir kopyası emp adında yaratılır. (employees tablosu 107 row'dur=
select table_name.num_row from user_table;
sonuç olarak;
emp tablosundaki kayıt sayısını boş görürüz nedeni bu tablonun istatistik'inin toplanmış olmamasıdır. Eğer ben bu tabloya bir insert/delete/update girersem kayıt sayısını doğru görebilirim ya da bir başka yol izleyip tablonun istatistikini toplayabilirim. Doğru değerlere doğru planla gitmek istiyorsak istatistik toplamak şarttır.
COST: Bir sql'in costu ne kadar yüksekse sistem kaynakları o kadar etkileniyor demektir. (Sistem kaynakları: CPU, MEMORY, I/O(DISK), NETWORK)
Hedef: Cost'u yani maliyeti aşağı çekmektir. Cost'u aşağı çekmek SQL'in çalışma süresiyle ölçülebildiği gibi bazen yanıltıcıda olabilir.
Örn:
SQL1 Output:;
Cost: Time:
453 00:06 --> Costu 453 olan sorgu 6 sn de çalışabilir.
SQL2 Output:
Cost: Time:
552 00:03 --> Costu 552 olan sorgu 3 sn de çalışabilir. Bu durumda cost yanıltıcı olabilir.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn1:
Customer adlı tablonun üzerine customer_pk adlı bir index yaratıldı
select cust_first_name,
cust_last_name
from customers
where customer_id=1030
Customers tablosunun row sayısı 55.500 adettir. where koşulu olmadan kayıtı select edersek Full table scan yapmış oluruz ki bu durum Cost'u yükseltir. Biz customer_id kolonuna index koyduğumuzdan ve where şartında doğru koşulu verdiğimizden select sonucu dönen explain plan index unique scan şeklindedir. (index'li okuma) Sorgudan dönen Toplam Cost:2'dir ve sorgu çok kısa bir süre içinde output vermiştir.
Yukarıdaki sorgu customer id'si 1030 a eşit olan tek bir kaydı döndürür ve aramayı 1030 no'lu id'yi bulunca stop eder.
Not: Index ve table ayrı segment'lerdir. Günlük hayatta bu iki segment'i örnekleyecek olursak en güzel örnek bir kitap benzetmesi olabilir. Index kitabın fihristi table ise kitabın içeriği gibi yorumlanabilir. Bir konuyu aramaya kalktığımızda fihristten sayfasını bulup bulduğumuz sayfadaki içerikten yararlanabiliriz. Kitabı teker teker karıştırmaktansa fihriste bakmak daha mantıklı olsa gerek
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn2:
select cust_first_name,
cust_last_name
from customers
where customer_id<10
Explain plan : index range scan --> bunun anlamı bir aralığı scan etmektir. (Aralık: 0,1,2,3,4,5,6,7,8,9 yani 10'dan küçük veriler). Sorgudan dönen output toplam 10 row'dur.
Her zaman unique scan okuma yapmak en iyisidir. yani where koşulunun = 'lik ile sağlanması...
Yukarıdaki sorgunun toplam maliyeti : 7'dir. (Index:2, Toplam:7)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn3:
select cust_first_name,
cust_last_name
from customers
where customer_id<10000
Customer table: 55.500 row
sorgudan dönen row sayısı 5311 --> BTREE index'leri yani default yaratılan index'leri kullanmak için %5 oranı
sağlanmalıdır yani dönen row sayısı toplam row sayısının %5 ini geçiyorsa index'li okuma yapılmaz full table scan okuma yapılır
1. Bölümü özetlersek;
Index'li okumalarda where koşuluna = koymak performanslı okumaya işaret eder ve tavsiye edilen bu yöntemdir --> index unique scan BTREE index yapısı %5 i sağlıyorsa eğer kullanılır. Index'ler herzaman where koşullarıyla kullanılır.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2. Bölüm:
Matematiksel işlemlerde index'li okuma yapılacaksa where koşulununda ='liğin sağına konulmalıdır.
Örn1:
Customer tablosunun cust_last_name kolonuna index yaratıldı
select cust_last_name
from customers
where upper(cust_last_name) = 'cinar' --> (Yanlış) sorgu çalışır ama full table scan yaparak output üretir.
Nedeni matematiksel işlemin soluna fonksiyon konulmasıdır. Kolonda index varsa ve index ile okuma yapılıyorsa muhakkak eşitliğin sağ tarafına fonksiyonu yazmak gerekir.
select cust_last_name
from customers
where cust_last_name=upper('cinar') --> (Doğru) Sorgu index unique scan yaparak output üretir böylelikle cost ciddi anlamda düşer.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn2:
select cust_last_name
from customers
where cust_last_name like 'S%' --> Soyadının baş harfi S ile başlayan kişileri getirir. Last name kolonunda
index olduğu için index'li okuma yapar--> index range scan
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn3:
select cust_last_name
from customers
where cust_id like '7%'
cust_id: number
'7%': char olduğundan cust_last_name kolonunda index olmasına rağmen index'li okuma olmaz nedeni data tiplerinin aynı olmayışıdır. (cust_id numberken cust_last_name char'dır)
Yukarıdaki sorguda fonksiyonel index'ler kullanılabilir.
Fonksiyonel index'ler normal index'ler ile aynı performansı sağlamaz
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn4:
Customer tablosundaki email kolonundaki 18000 tane row'u null yaptık ve sonrasında email kolonuna index create ettik
select cust_id
from customers
where cust_email is not null
email kolonunda index olmasına rağmen index'li okuma yapılmaz
select cust_id
from customers
where cust_email is null
email kolonunda index olmasına rağmen index'li okuma yapılmaz
Her iki sorgunun explain plan'ı Full Table Scan'dir. Null kolonlara index create edilmemelidir. Eğer null olan kolonu illaki index'lemek index'li okumak istiyorsak select'te seçtiğimiz kolonla birlikte index'leme yapmalıyız. yani cust_id ve email kolonlarına beraber index konulmalıdır.
Not: 55.500 kayıttan 18.000 kayıt geri dönüyorsa index kullanılamaz index yapısı düzgün olsa where de indexli kolon sorgulansada kullanılamaz nedeni %5 kavramının aşılmasıdır.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn5:
ORDER BY sorgunun performansını etkiler mi?
order by sorgunun cost'unu iki katına çıkarabilir diyebiliriz. Order by kullanıdığımızda bir sort işlemi yaptığı için TEMP TS'yi de kullanmak zorunda kalırız. ORDER BY dan önce muhakkak where yani index kullanılmalı. Nedeni index'li okumada dönen kayıtların koşul sağlaması sonucu output vermesi ve az olmasıdır. Az olan veri daha rahat bir şekilde sort edilir. Where olmayan sorgularda okuma full table scan şeklinde olur.
Explain Plan (order by yapılan herhangi bir sorgunun explain planı)
Cost
full table scan 332
sort 669
ve planda ek olarak temp ts görüntülenir
where olan sorgularda (ilgili kolonda index olmasa dahi) görüntü aşağıdaki gibi olur
cost
full table scan 332 --> index olmadığı için yine full table scan yaptı
sort 333 --> koşula göre output döndürdükten sonra sıralama daha düşük cost ile yapıldı.
Dip not: Where hiç bir zaman cost'u arttırmaz aksine azaltır.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn6:
Interset --> Iki tablonun kesişimine denir.
select cust_last_name
from customers
where cust_city='Paris'
intersect
select cust_last_name
from customers
where cust_credit_limit<10000
Explain plan:
Her iki sorguda sonucunda sort işlemini yapıp kesişim alacağı için cost çok yüksek gözükür... (her iki sorguda indexsiz okuma yapıyor yani FTS)
Cost: 823 (yüksek)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn7:
Munis --> Iki tablonun farkını alır.
select cust_last_name
from customers
where cust_city='Paris'
munis
select cust_last_name
from customers
where cust_credit_limit<10000
Explain plan:
Yukarıdaki sorguda da ilk Full table scan okuma yapıp sonra sort etme işlemine geçilir ardından output olarak iki tablo arasındaki fark gösterilir.
Cost: 733 (yüksek)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn8:
Union / Union all
union all'un union'a göre cost'u daha azdır.
Nedeni;
Union all--> sort yapmaz --> iki tablodaki rowları birbirine ekler
Union--> sort yapar --> iki tablodaki aynı verileri distict çeker ve eklemeyi yapar. Distinct işlemi performans
açısından cost'u yükseltir.
A tablosunda: merve, emre, cenk
B tablosunda: merve, ali, ayşe
C: tablosu iki tablonun union'u olsun sonuç --> merve,emre,cenk,ali,ayşe şeklindedir.(Merve her iki tabloda olmasına rağmen distinct çektiği için iki kez yazılmaz)
C: tablosu iki tablonun union all'u olsun sonuç -->merve,emre,cenk,merve,ali,ayşe şeklindedir
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn9:
select cust_city
avg(cust_credit_limit)
from customers
group by cust_city --> Full Table Scan okuma yapar. Nedeni index'siz okuma oluşudur yani where koşulunun
kullanılmayışı
Group by yapınca dönen kayıt sayısını azaltabiliriz. Group by where gibi maliyeti azaltmaz. Ama maliyeti çokda yukarıya çekmez
Cost
Full Table Scan : 333
Hash group by : 341
cost 333 iken 341 e yükseldi. Görüldüğü gibi maliyet hat seviyede artmadı.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Not: İç içe select(Sub query) kullanırken selectlerden birini froma tablo gibi yazmak mantıklıdır.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Örn10:
customer tablosunun cust_last_name kolonuna index konuldu.
select cust_id, country_id
from customers
where LOWER(cust_last_name) = 'GENTLE'
--> Örnekte index li okuma mümkün değildir. Nedeni eşitliğin sol tarafında koşulun olmasıdır. Normalde oracle
eşitliğin sol tarafındakini kale almaz bunu ortadan kaldırmak için sorgu aşağıdaki şekilde düzenlenmiştir.
select cust_id, country_id
from customers
where cust_last_name = LOWER('GENTLE')
select cust_id, country_id
from customers
where LOWER(cust_last_name) like 'GENTLE'
--> Örnekte index li okuma mümkün değildir. Nedeni eşitliğin sol tarafında koşulun olmasıdır. Normalde oracle eşitliğin sol tarafındakini kale almaz bunu ortadan kaldırmak için sorgu aşağıdaki şekilde düzenlenmiştir.
select cust_id, country_id
from customers
where cust_last_name like LOWER('GENTLE')
eşitliğin sol tarafına koşulu yazıp indexli okuma yapmak istiyorsak muhakkak functional index'leri kullanmak gerekir.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Organized Table
Index Organized Table aşağıdaki şartları true vermelidir.
- Index organized table'larda primary key olmalıdır.
- Index organized table olan tablolarda update olmamalıdır.
Tablespace Tablespace
Normalde table ve index tablespace’lerde yer alan farklı segment tipleridir. Her birinin (table, index) tablespace’de oluşturduğu yer ayrıdır. Ama Index Organized Table da Table üzerinde index oluşturduğu için Tablespace üzerinde aynı yeri kapsar.
Örn : Marketteki ürünün barkodu hiçbir zaman değişmez yani unique’dir. Bu kodların girildiği bir tablo update gerektirmez. Bu doğrultuda bu tabloda Index Organized Table kullanılabilir.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bölüm3:
Materialized View:
Örn: Bizim maliyeti yüksek sorgularımızın olduğunu düşünelim. (Cost: 4983). Bunu biraz daha açalım üç tabloya giden ve bu üç tablodan oldukça büyük sayıda row ‘lar döndüren bir günlük raporumuz mevcut buraya kadar herşey güzel… Bir düşünelim bu sorguyu günde her birimiz 10 kez çekiyoruz bir de departmanımızda benden başka 6 kişi daha bu sorguyu çalıştırıyor o zaman Materialized View hayat kurtaran bir çözüm olarak doğuyor yaşamımıza
Create materialized view deyip raporda çektiğimiz sorguyu devamına yazarsak materialized view’i oluşturduk demektir.
Hergün bir job halinde materialized view yaratırsak o günden önceki raporları çok hızlı bir şekilde sorgulayabiliriz. Düşünelim 09:00 da bir job’ım var ve bu hergün mesai başlangıcında bu job’ı çalıştırıyorum ve önceki günlere ait dataları çekiyorum. Eğer departmanlar tarafından bu kabul edilirse çok hızlı bir şekilde sorgumun geri dönüşünü alabilirim…
Materialized view olmadan çalıştırılan sorgunun costu 4983 iken metarialized view ile yaratılan sorgunun cost’u ilk çalıştırılmada 1921 iken sonra çalıştırıldığında 2’ye düşer. Cost’un ilk yüksek gelmesinin önüne geçilemez ama bu da çok önemli değildir. Bir sorguyu toplamda 60 kez çalıştırıdığımız düşünülürse:D
Create Materialized View:
Create materialized view join_sales_time_product_mv
enable query rewrite
as
select p.prod_id, p.prod_name
t.time_id, s.channel_id
s.promo_id, s_cust_id
t.week_ending_day, s.amount_id
from sales s, products p, times t
where s.time_id=t.time_id
and s.prod_id=p.prod_id
as den sonra gelen sql bizim gün bazlı rapor çıktısı aldığımız sql’dir.
Bu işlem günlük hayatta uygulama tarafındakilerin çok fazla baş vurduğu yöntemdir.
Örn: Geçen aya dair kredi kartı borcumu detaylı bir şekilde öğrenmek istiyorum. Müşteri temsilcisine bağlanıp bu isteğimi söylediğimde arkaplanda bir rapor çalıştırıp sorgunun sonucunu bana söylemelidir. Bu raporu bir materialized view yaratıp alırsak sürede sonuca kavuşabiliriz.
