Oracle Indexes 2

Bitmap Index:

Bitmap indeksler tablodaki değerleri bit şeklinde depolarlar. Veriler, başlangıç bitiş rowid ‘ler arasında oluştulan bitmaplar içinde depolanır.

Örnek:

create bitmap index person_region on person (region);

Row Region North East West South
1 North 1 0 0 0
2 East 0 1 0 0
3 West 0 0 1 0
4 West 0 0 1 0
5 South 0 0 0 1
6 North 1 0 0 0

Avantajları:

Örnekte de görüldüğü gibi düşük çeşitliğe sahip (low cardinality) Region columnu için bir Bitmap indeks oluşturulmuştur. Column değerleri ne kadar az çeşitse Bitmap okadar kısa olacak ve verinin bitlerden okunup getirilme işi o kadar hızlı olacaktır. Low cardinality columnlar için b tree indeksler yerine Bitmap indeks kullanmak daha verimli olacaktır.

Bitmap indeksler Bitmap sıkıştırılması mümkün olabilmektedir. Sıkıştırılmış bitmapin decompress(çözümleme) işi extra cpu kaynağı ve geniş bant genişliği ihtiyacını gerektirmektedir. Fakat sıkıştırılmış yapının diskten okunma hızı hızlı olmaktadır.

“Where” cümlesinde kullanılan “and” yada ” or “ operatörleri ile şartlar çoğaltıldığında Bitmap indeks kullanmak çok daha avantajlıdır.

Bitmap indeksler datawarehouse yapıdaki veritabanı sistemleri için idealdir. Hiç yok denecek kadar az transaction yapılar için kullanılması avantajdır. Aksi durumu performans kaybı olur. B tree indeksler ise OLTP transctional yapılar için uygun indeks yapılarıdır.

Dezavantajları:

Read only table or no DML:

Eğer tablomuz read-only değilde yani dml aktivesi varsa kesinlikle Bitmap index tercih etmeyiniz. Bunu sebebinde dml esnasında Bitmap oluşturma yada var olan bitmapın update edilmesi cpu bazında extra zaman demektir ve dolayısıyla kaynak tüketimini artıracaktır. Bir değere sebep ise dml esnasında dml deadlock’ ların oluşma sebebidir.

Kullanımı sadece datawarehouse sistemler için sınırlandırılmıştır. Diğer sistemler için kullanılması Bitmap modifikasyonu olacağından istenilmeyen performans kaybı yaşatır.

Oracle Indexes

Tablo içerisinde rastgele dağıtılmış kayıtları(rows) aranılan küçük bir set getirmek için kullanılırlar. Bilindiği gibi indeksler indeks segmentinde saklanır. Logical olarak hiyerarşik bir zincir oluşturup ve bu zincirin leaf(yaprak) seviyelerinde referans alınan tablo rowidleri ile aranılan datayı/dataları nokta atışı olarak bulmamızı sağlayan objelerdir.Disk I/O ‘unu azaltırlar.Eğer indeks kullanılmayan bir HOT(heap Organization Table) istenilen bir küme setini FTS(Full Table Scan) olarak tarayacaktır FTS demek, tablo içerisindeki tüm kayıtlardaki her satırı inceleyerek sorgulanan yöntemdir. Indeks kullanıldığında ise , aranılan veri/verilerin hızlı bir şekilde bulunup sorgu nun en kısa zamanda çalışması hedeflenir. Yanlış index kullanımı, yanlış indeks tipi seçimi, yanlış yazılamış Hintler, ve invalidate indeksler performansa kötü etki yapmaktadır.

İndeks oluşturma:

CREATE INDEX ord_customer_ix ON orders (customer_id);

İndex ne zaman kullanılmalıdır?

İndeks gerekliği, tablodaki tüm satırlara bakıldığında aranılan sorgu sonucunun tüm kayıtlara oranı olarak hesaplanır. Genel olarak %2 ile %10 arası indeks kullanılması tavsiye edilebilir.

Unique/Nonunique indexes:

Unique bir indeks , her bir tek değer için tek bir rowid içerir. Aynı olan kayıttan bir tane saklar. Leaf (yaprak)’ larda sıralı bir şekilde bu bilgiler tutulur.

NonUnique indeksler ise, birden fazla aynı datayı(örneğin isim column’ u için birden fazla Ahmet değeri gibi) tutmayı izin verir.Nonunique indeksler index leaf’lerda indeks key tarafından artan şekilde sıralanmış rowid’ ler içermektedir.

Not:Primary key ve unique constraintleri otoamitik olarak unique(benzersiz) index oluşturular.

İndekslerin karakteristik Özellikleri:

Unusable: Mevcut olan bir indeksi kullanılamaz duruma düşürmektir. Kullanılmak istenildiğinde tekrar rebuild edilerek usable duruma getirilebilir. Genellikle mevcut bir indeksi kullanmayı önlemek için unusable kullanılır. Unusable indeksler, DML(insert-update-delete-merge) operasyonlarında ve optimizer tarafından dikkate alınmazlar.Bir indeks unusable olduğu zaman data tarafından indeks segmentinden silinir. Default olarak oluşan tüm indeksler usable’dir.

Bir indexin usable/unusable olduğunu görebilmek için aşağıdaki sorguya bakmamız gerekir.

SELECT index_name, status
FROM user_indexes;

ALTER INDEX test_idx UNUSABLE;

Online yapmak için

ALTER INDEX test_idx_state REBUILD;

Invisible: Indeksi gizli duruma getirmektir. Bir invisible index DML operayonları tarafından dikkate alınırlar. Fakat optimizer tarafından dikkate alınmazlar. İndeksi invisible yapmadaki amaç, silinmiş gibi gösterip optimizer tarafından dikkate alınmayıp (indeks kullanmayıp)mevcut sorgulardaki performans testi yapmaktır. Default olarak tüm indeksler oluşturulduklarında visible’ dır.

Yeniden adlandırma:

ALTER INDEX test_idx RENAME TO i my_test_idx;

Rebuild etme:

ALTER INDEX my_test_idx REBUILD ONLINE;

Paralel özelliği verme:

ALTER INDEX my_test_idx PARALLEL 3;

Reverse özelliği verme:

ALTER INDEX _test_idx _person_id REBUILD REVERSE;

İstatistik toplama:

ALTER INDEX idx_last_name

REBUILD COMPUTE STATISTICS;

ifadesi indeks oluşturma esnasında istatistiklerin toplanması istenmeyebilir yada unutulabilir. Alter index komutuyla sonradan verilen indeks için istatistikler toplanabilir.

Index silme:

DROP INDEX idx_last_name;

Komutuyla verilen isimdeki bir indeksi silebiliriz.

B tree Index:

B tree indeks , Balanced Trees ifadesinin kısaltılmış halidir.

Örnek olarak employees tablosundaki department_id column için b tree indeks yapısına bakalım. Leaf seviyesinde önce value sonra rowid gelecek şekilde temsil edilir. Şuan burada kullanılan indeks nonunique bir indekstir. Zincirin leaf seviyesinde sıralı şekilde duplicate kayıtlar mevcuttur.

B tree indeksler veriyi sıralı bir şekilde kaydederler. dolayısıyla herhangi bir sort işlemini önlemek içinde kullanılırlar. Bitmap indexler için böyle bir durum söz konusu değildir.

Btree indeksler OLTP sistemler için idealdir.

B tree indeksler null değerler için index kullanamazlar.

Kullanımı:

CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, column_n) [ COMPUTE STATISTICS ];

Compute Statistics:

Genel olarak bir indeksi yukarıdaki şekilde oluştururuz.

COMPUTE STATISTICS, index oluşturma esnasında istatistiklerin oracle tarafından söylenilmesini bildirir.

CREATE INDEX t_id_idx ON t(id);

Peki bu aşamada neler yaşanır? “id” kolonunun değerleri artan sırada dizilir.Degeri ve tablodaki satır adresi(rowid) bilgisi saklanır, bu şekilde indeksimiz oluşturulmuş olur.Aşağıdaki sorguya bakalım :

select * from T where id = 12345

sorgusunda normal şartlarda indeks tarama (index scan) yapılır, önce “id” değeri indeksten bulunur ve satırının rowid bilgisine ulaşılır ve rowid bilgisi ile tabloya nokta atışı yapılarak veriler getirilir. “….where id between 200 and 300…” gibi bir sorguda “leaf” ler üzerinde gezilerek aralık (range) bilgisine ulaşılır.B Tree indeks yapısında tekil(unique) olmayan bir indeks değeri yoktur.

Concatenated btree index:

ÖRNEK:

SQL>create table xcount(t1 number,t2 number);

SQL> begin

2 for k in 0..10000

3 loop

4 insert into xcount values(k,mod(k,5));

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL>create index xc_idx1 on xcount(t1);

SQL>create index xc_idx1 on xcount(t2);

SQL>create index xc_idx3 on xcount(t1,t2);

SQL> set autotrace on

SQL> select * from xcount where t1=0 and t2=0 order by t1 desc;

T1 T2

---------- ----------

0 0

Execution Plan

----------------------------------------------------------

Plan hash value: 2128003261

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| XC_IDX3 | 1 | 26 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T1"=0 AND "T2"=0)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

30 consistent gets

0 physical reads

0 redo size

478 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Yukarıdaki sorgumuz xc_idx3 indeksini kullanarak CPU bazında en düşük Cost'u çıkarıp en iyi execution planı seçmiştir. Şayet aynı sorguda xc_idx3 değilde xc_idx1 yada xc_idx 2 yi seçseydik o zaman sonuç aşağıdaki gibi olacaktı.

SQL> select /*+INDEX(xcount xc_idx2)*/ * from xcount where t1=0 and t2=0 order by t1 asc;

T1 T2

---------- ----------

0 0

Execution Plan

----------------------------------------------------------

Plan hash value: 3776712309

--------------------------------------------------------------------------------

-------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim

e |

--------------------------------------------------------------------------------

-------

| 0 | SELECT STATEMENT | | 1 | 26 | 24 (0)| 00:

00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| XCOUNT | 1 | 26 | 24 (0)| 00:

00:01 |

|* 2 | INDEX RANGE SCAN | XC_IDX2 | 2001 | | 6 (0)| 00:

00:01 |

--------------------------------------------------------------------------------

-------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("T1"=0)

2 - access("T2"=0)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

24 consistent gets

0 physical reads

0 redo size

478 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Hint kullanarak xc_idx2 indeksini kullanarak sorguyu çalıştır dedik. Fakat böyle bir (t1 and t2) filtresinde CPU bazındaki cost'u 24 çıkmıştır.Combine indexler böyle durumlarda verimli olabilir.

Function-based Index:

indeksler sadece kolonlar için değil aynı zamanda fonksiyonlar içinde kullanılırlar.

ÖRNEK:

SQL>create index idx_adv_f_name on adv_table(lower(first_name));

index created.

SQL>create table adv_table as select * from hr.employees;

table created.

SQL> set timing on

SQL> set autotrace on

sQL> select first_name from adv_table where lower(first_name)='anthony';

FIRST_NAME

------------------------------

Anthony

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3152802285

--------------------------------------------------------------------------------

--------------

| Id | Operation | Name | Rows | Bytes | Cost (%CP

U)| Time |

--------------------------------------------------------------------------------

--------------

| 0 | SELECT STATEMENT | | 1 | 24 | 2 (

0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| ADV_TABLE | 1 | 24 | 2 (

0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_ADV_F_NAME | 1 | | 1 (

0)| 00:00:01 |

--------------------------------------------------------------------------------

--------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access(LOWER("FIRST_NAME")='anthony')

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

434 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Heap Organized Table Ve Index Organized Table

Üç tür tablo çeşiti vardır.

Bunlar;

•       Heap organization table

•       Index organization table

•       External organization table  

Biz burada sadece HOT ve IOT indeks yapılarını analiz edeceğiz. Ve karşılaştırmasını yapacağız.

Öncelikle  team tablosu oluşturacağız. Heap organized table’da Create cümlesinin sonunda “organization Heap” demeye gerek yoktur. Yazmadığımız zaman da bu HOT table olur. Default olarak tüm tablolar HOT’dur.

Sorgunun çalıştırılmasında kullanılan execution planda sorgunun erişiminde Index rowid’si kullanılmıştır. Yani indeks segmentindeki kayıtlı index bloğu kullanılmıştır.

Aşağıdaki tabloda HOT(heap organization table) ‘un index yapsını şeması vardır. Yani bir index segmenti içerisindeki index tablosundaki index bloğunun yapısını anlatmaktadır. 2 byte Row header’ a sahiptir ve 6 byte row id adresi tutulur.

HOT tablolarda ise table blok yapısı aşağıdaki gibidir. 3 byte Row Header’ a sahiptir.

Örnek bir IOT tablolardaki index yapısı aşağıdaki gibidir.

•          Burada sadece bir index row header’a sahiptir. Index  uzunuluğu 3 karakter ve bilgisi FER dir.IOT için , Create table ifadesinden sonunda Organization Index zorundayız. Fiziksel table Rowid lerdetutulmazlar ,index Rowidlerde tutulur.
•         IOT’ lar ayrı bir Table segmentine ihtiyaç duymaz. Primary Key  Constrainti almak zorundadır. İndex segment ismi primary key Constraint’ine bağlıdır.ismini oradan alır.( SYS_IOT_TOP_<object_id>)
•          IOT’ lar LONG türünde kolonlar içermezler. Reversed index olamazlar. Yerden tasarruf sağlamak için sıkıştırılabilirler.
•          IOT Tablolarda eğer primary key kolonu ilk sırada tanımlanmamışsa oracle bu yapıyı tekrardan bir reorder yapacaktır.

IOT Overflow table:

• Çok büyük satırları IOT’ larda tutmak verimsiz bir davranıştır. Bu yüzden bu tür satırları overflow segmentinde saklamak daha mantıklıdır.
•   Primary key  satırları indeks segmentinde bulunur. Non-key olan satırlar indeks  segmenti yada  overflow segmentinde bulunur. 
• Overflow segmenti ilave extra bir table segmentidir. Bir indeks overflow için  row 6 byte Rowid içerir.

Aşağıda örnek bir IOT overflow index bloğu verilmiştir.

d

Karşılaştırma:

HOT ve IOT'u karşılaştıracak olursak daha az leaf kullancağından bilginin aranıp bulunması daha kısa sürer.

           

        

Resim Kaynakları: Julian Dyke

        

Enterprise Manager Console ‘de ERROR: NMO not setuid-root (Unix-only) sorunu

Enterprise Managerda host bazı önemli işlerde host credintials girişler gerekmektedir. Dolayısıyla veritabanının kurulu olduğu ve sahiplenildiği user ‘ın login olması gerekmektedir.

Ve geçenlerde almış olduğum bir hatayı sizinle paylaşmak istiyorum. Linux işletim sistemi üzerinde mevcut oracle kullanıcımızın password ‘unu değiştirmiştim. Fakat enterprise manager’ da host credentials ekranında login olmak isterken ERROR: NMO not setuid-root (Unix-only) şeklinde bir hata aldım.

Ve aşağıdaki şekilde root olarak sisteme bağlanarak ve aşağıdaki işletim sistemi komutlarını girelim.

root#>

cd $ORACLE_HOME
chmod 6750 bin/nm?
chmod 700 bin/emdctl
chmod 700 bin/emagent
chown root bin/nm?

daha sonra oracle kullanıcına bağlanarak

oracle $>

emctl stop dbconsole
emctl start dbconsole

komutlarını girerek enterprise manager’i ayarların etkili olması için yeniden başlattım. Ve artık sorun çözülmüş oldu.

ADDM Nedir?

Automatic Database Diagnastic Monitor(ADDM)
Her bir AWR snapshot’u alındığında son iki snapshot ‘a karşılık periyodun analizini yapar.

• Her AWR snapshotundan sonra çalışır

• Instance ‘i monitor eder ve oluşacak darboğazları tespit eder.

• Sonuçları AWR’ da depolar.

ADDM tarafından belirlenen bazı ortak sorunlar şunlar olabilir.

• Cpu bootlenecks

• Lock Contention

• IO Kapasite sorunu

• High- Load SQL statements

• High PLSQL and JAVA

• Çok hızlı Checkpoint gerçekleşmesi

• Oracle Net Bağlantı sorunları

ADDM aşağıdaki dynamic Performance view leri yada Enterprise Manager Konsolu ile hem bulgularını hemde tavsiyelerini görebiliriz.

ADDM Views

View Name	Description
DBA_ADVISOR_TASKS	Provides information about existing task, like – task id, name, etc.
DBA_ADVISOR_LOG	Contain the current task information such as status, progress, error messages, and execution times.
DBA_ADVISOR_RECOMMENDATIONS	Complete result of diagnostic task.
DBA_ADVISOR_FINDINGS	Display all symptoms and specific recommendations.

Genel olarak ADDM tavsiyeleri(Recommendation) şunlardır:

• Schema Changes

• Hardware Changes.

• Application Changes

• Database Configuration

• Diğer Advisorları tavsiye eder.

Diğer Advisorlar ise

SQL Tuning Advisor, tek bir SQL statementi analiz eder. Bu statementin performansını arttıracak tavsiyeler verir. Instance Konfigrasyosyon değişimi, Statement’in yeniden yazılması yada SQL profile, Index Ekleme gibi tavsiyeler verir. Daha çok tek bir SQL cümlesi yerine Tuning Setler oluşturarak performans talebinde bulunuruz.

SQL Access Advisor, perfromansı geliştirecek materialized view yada materialized log yada ilave index oluşturmamasını tavsiye eder. Verilen bir zamanda girilmiş tüm SQL statementleri analiz eder.

Memory Advisor(SGA Advisor, PGA advisor), Instance tarafından kullanılacak toplam memory için en ayarları bulup tavsiye eder.)SGA advisorunda altında çalışan alt advisorlar vardır.(Shared Pool advisor,Java Pool Advisor, Buffer Cache Advisor, Strams pool advisor gibi)

Segment Advisor verimsiz kullanılan alan tüketimini kontrol eder. Öneride bulunur. Shrink, Reorganize gibi tavsiyeleri vardır.

Undo data ile ilgili sorunlarda Undo Advisor

Backup – Rocovery de oluşacak sorunlarda MTTR (Mean time to Recovery) advisor’ ı

tavsiye eder. Genel olarak Instance crash olduktan sonra bir database recovery için gerekli zaman ayarıdır.

Automatic Workload Repository

Genel olarak Self-Tuning amaçlı, problem belirleme özelliği olan ve istatistik toplama ve saklama özelliği olan bir altyapıdır.Her 60 dk ‘da ( default) Database otomatk olarak SGA’dan istatistik bilgilerini alır ve AWR içerisinde Snapshot formatında saklar. Default olarak AWR içerisinde bu snapshotlar 8 gün saklanır. Saklanma süresini yada default snapshot aralığını değiştirebiliriz.

Oracle 11g ’de SYSAUX tablespace’ nde depolanan ve SYSMAN shemasına ait yüzlerce tablo bulundurur. DBMS_WORKLOAD_REPOSITORY paketiyle yada Enterprise Manager Konsol ile Repository ‘ kullanabiliriz. Oracle da zaten bu iki yöntemi tavsiye etmektedir.

AWR tablolarına karşı gelen DML istekleri desteklenmez. Sadece okunur.

AWR yapısı ikiye ayrılır.

1. Memory’ de saklanan istatistikler vardır.

Bu istatistikler performans nedenlerinden ötürü memory de saklanır.

Dinamik performance view’ ler (V$ )ile memory de saklanan database istatistiklerine erişilebilir.

2. AWR içerisinde saklanan istatistikler kalıcı olarak saklanan istatistiklerdir. İstatistikler birkaç nedenden dolayı kalıcı olarak saklanmaya ihtiyaç duyulur.

a. Bir instance crash(çökmesine )karşı istatistik ihtiyacı duyulur.

b. Bazı analizler, bir Baseline karşılaştırması için Historical bir dataya ihtiyaç duyulur.

Memory Monitor (MMON) proces’i tarafından Memory’ deki Instance istatistiği kalıcı olarak saklamak için diske yazılır.

Baseline, AWR snapsotların bir kümesidir. Başlangıç ile bitiş snapshotların arası  performans kıyaslaması yapılması için kullanılır

Create Baseline:

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 200,
end_snap_id => 220,
baseline_name => ‘Test_baseline);
END;
Delete Baseline:
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => ‘test_baseline’,
cascade => TRUE);
END;
/
Retention Period: Default 8 gündür ve sonsuza kadar saklama seçenekleri vardır.
Collection Interval: Snapshot aralığıdır. default olarak 60 dk dır.
Collection Level: İstatistik toplama seçenekleridir. Basic seçeneğinde ADDM fonksiyonunu kapatır. Yani Self tuning disable olacak. Typical yada All seçenekleri tavsiye edilir
Örneklerle anlatırsak.
SQLplus ‘a bağlanalım
$>sqlplus / as sysdba
SQL>Select * from DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION TOPNSQL
--------- ------------------ -------------------- ---------
1272787738 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
2189780853 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
Sonuç olarak iki tane DB var. Ve bunların snapshot aralığı 1 saat. Retention (saklama) 8 gün olarak görünüyor.

Önemli AWR View’ leri

View Name	Description
V$ACTIVE_SESSION_HISTORY	Displays the active session history (ASH) sampled every second.
V$METRIC	Displays metric information.
V$METRICNAME	Displays the metrics associated with each metric group.
V$METRIC_HISTORY	Displays historical metrics.
V$METRICGROUP	Displays all metrics groups
DBA_HIST_ACTIVE_SESS_HISTORY	Displays the history contents of the active session history.
DBA_HIST_BASELINE	Displays baseline information.
DBA_HIST_DATABASE_INSTANCE	Displays database environment information.
DBA_HIST_SNAPSHOT	Displays snapshot information.
DBA_HIST_SQL_PLAN	Displays SQL execution plans.
DBA_HIST_WR_CONTROL	Displays AWR settings.