PDF-Version

Warum wird mein Index
nicht benutzt?
MuniQSoft GmbH
DOAG 2012
Index Nutzung-1
MuniQSoft GmbH
Tätigkeitsbereiche:
Oracle Support
Hotline: Mo-Fr 8.00 – 18.00 Uhr
Erweiterung um eine Rufbereitschaft auch am Wochenende
möglich
Oracle IT-Consulting & Services
Oracle Schulungen (SQL, PL/SQL, DBA, APEX, B&R, …)
Software-Lösungen
Oracle Lizenzen
MuniQSoft GmbH
Schulungszentrum
Grünwalder Weg 13a
82008 Unterhaching
Tel.: 089 / 679090 40
MuniQSoft GmbH
MuniQSoft GmbH
IT-Consulting, Support,
& Software-Lösungen
Witneystr. 1
82008 Unterhaching
Tel.: 089 / 6228 6789 0
DOAG 2012
Index Nutzung-2
Ist ein Index IMMER besser ?
Nicht jeder Ausführungsplan wird durch Index Zugriff
anstelle eines Full Table Scans schneller
Das einzige, das zählt ist die
Ausführungszeit !
Ausführungszeit !
Ausführungszeit !
MuniQSoft GmbH
DOAG 2012
Index Nutzung-3
Fall 1: Index ist unusable
Sie haben einen Index, nur ist der leider unbrauchbar
Beispiel:
ALTER TABLE emp
MOVE;
SELECT 1 FROM emp
WHERE empno=7369;
Lösung:
ALTER INDEX pk_emp REBUILD;
SELECT 1 FROM emp
WHERE empno=7369;
MuniQSoft GmbH
DOAG 2012
Index Nutzung-4
Fall 2: Es gibt keine Statistiken
Sie haben keine aktuellen Statistiken
Lösung:
Index Statistiken erzeugen:
BEGIN
dbms_stats.gather_index_stats(
'<OWNER>','<IND_NAME>');
END;
Tabellen Statistiken erzeugen:
BEGIN
dbms_stats.gather_index_stats(
'<OWNER>','<TAB_NAME>');
END;
MuniQSoft GmbH
DOAG 2012
Index Nutzung-5
Fall 3a: Es gibt keine (korrekten) Histogramme
Die Werte in der indizierten Spalte treten ganz
unterschiedlich oft auf
Es liegen keine korrekten Histogramminformationen vor
Ermittlung der Verteilungsstatistiken
BEGIN
dbms_stats.gather_table_stats(
'<OWNER>','<TAB_NAME>', method_opt=>
'FOR ALL INDEXED COLUMNS SIZE AUTO',
ESTIMATE_PERCENT=>100); -- Kann lange dauern!
END;
/
Statistiken dazu stehen in USER_HISTOGRAMS
MuniQSoft GmbH
DOAG 2012
Index Nutzung-6
Fall 3b: Die Statistiken sind falsch
Sie können mit einem Hint den Optimizer bitten, die
Estimated Rows mit den Actual Rows zu vergleichen
SELECT /*#1*//*+ GATHER_PLAN_STATISTICS */
FROM emp WHERE sal=3000;
WITH s AS(SELECT sql_id FROM v$sqlarea
WHERE sql_text like '%/*#1*/%')
SELECT plan_table_output FROM
s,table(dbms_xplan.display_cursor(
s.sql_id,null,'ALLSTATS'))
MuniQSoft GmbH
DOAG 2012
Index Nutzung-7
Fall 4a: Abfrage verwendet LIKE
Sie haben die VARCHAR2 Spalte (ename) indiziert
SELECT * FROM emp
WHERE ename like 'S%';
-- OK
SELECT * FROM emp
WHERE ename like '%S%';
-- 11.2.0.3 OK
Workaround für ältere Versionen
SELECT /*+ INDEX (emp) */ * FROM emp
WHERE ename like '%S%';
MuniQSoft GmbH
DOAG 2012
Index Nutzung-8
Fall 4b: Abfrage verwendet LIKE
Sie haben die NUMBER Spalte (sal) indiziert
SELECT * FROM emp
WHERE sal like '%33%';
-- Index wird nicht verwendet
Workaround:
Function Based Index anlegen
CREATE INDEX emp_sal_fbi ON
EMP(to_char(sal));
select 1 from scott.emp
where sal like '%33%'
MuniQSoft GmbH
DOAG 2012
Index Nutzung-9
Fall 4c: Abfrage verwendet LIKE
Sie haben die DATE Spalte (hiredate) indiziert
SELECT * FROM emp
WHERE hiredate like '%1980%';
-- Geht nicht
Workaround: Function Based Index anlegen oder BETWEEN
CREATE INDEX emp_hiredate_fbi ON
EMP(to_char(hiredate,'DDMMYYYY'));
SELECT /*+ INDEX(emp) */ *
FROM scott.emp
WHERE hiredate LIKE '%1980%'; --oder
SELECT * FROM scott.emp
WHERE hiredate between
to_date('01.01.80','DD.MM.RR')
and to_date ('31.12.80','DD.MM.RR');
MuniQSoft GmbH
DOAG 2012
Index Nutzung-10
Fall 5: Es wird der falsche Index verwendet
Sie haben mehrere Indizes auf der Tabelle, aber der
falsche wird verwendet
Workaround:
1. Statistiken aktualisieren oder
2. Index Hint mit richtigem Index verwenden oder
SELECT /*+ INDEX(emp,mein_index) */ *
FROM emp
WHERE sal=3000
AND comm=1000;
3. Unnötige Indizes von der Tabelle entfernen
MuniQSoft GmbH
DOAG 2012
Index Nutzung-11
Fall 6: Falsche Reihenfolge indiziert
Sie verwenden eine nicht führende Index-Spalte
(Index auf ename, sal, hiredate)
Workaround:
Reihenfolge ändern oder Index Skip Scan Hint verwenden
(ab 11g kann der Optimizer das automatisch)
SELECT /*+ INDEX_SS(emp) */ FROM emp
WHERE hiredate=
to_date('17.12.1980','DD.MM.YYYY');
MuniQSoft GmbH
DOAG 2012
Index Nutzung-12
Fall 7: Sortierung soll über Index gehen
Index liegt auf SAL
SELECT * FROM emp
ORDER BY sal;
Die Spalte muss über ein
NOT NULL Constraint
verfügen
ALTER TABLE emp
MODIFY (sal NOT NULL);
SELECT /*+ INDEX(emp)*/ * FROM scott.emp
ORDER BY sal;
MuniQSoft GmbH
DOAG 2012
Index Nutzung-13
Fall 8: Stored Outline / SQL Profil
Es gibt eine Stored Outline oder ein SQL-Profil, dass der
Optimizer vorzieht
Workaround:
Prüfen Sie, ob Ihre Query als Stored Outline oder SQL
Profil existiert
SELECT * FROM dba_outlines;
SELECT * FROM dba_sql_profiles;
Löschen Sie ggf. die Stored Outline / SQL Profil
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>
'mein_sql_profile'); END;
DROP OUTLINE <meine_outline>;
MuniQSoft GmbH
DOAG 2012
Index Nutzung-14
Fall 9: Initialisierungsparameter
Sie haben Initialisierungsparameter verwendet, die eine IndexVerwendung für den Optimizer ungünstig erscheinen lassen.
Beispiele für Pro Full TABLE Scan
db_file_multiblock_read_count (hoch)
optimizer_index_cost_adj (hoch (>100))
optimizer_index_caching (niedrig)
optimizer_mode=all_rows
Hinweis: ca. 300 Initialisierungsparameter werden für einen
Ausführungsplan ausgewertet
Lösungsmöglichkeiten
Parameter abändern
Hints einbauen
MuniQSoft GmbH
DOAG 2012
Index Nutzung-15
Fall 10: Sie haben gar keinen Index
Spalte in der WHERE Bedingung ist gar nicht indiziert
Lösung:
Spalte indizieren
CREATE INDEX scott.my_ind
ON scott.emp(sal)
NOLOGGING ONLINE; -- ONLINE nur Enterprise Ed.
MuniQSoft GmbH
DOAG 2012
Index Nutzung-16
Mein Index will nicht …
1. Statistiken überprüfen oder gleich neu berechnen
2. Status des Index ist in der View dba_indexes unusable,
aber in dba_objects valid
3. Mit Hints den Index "erzwingen" oder andere Indizes
ausschalten
4. Ausführungsplan (Optimizer Trace) tracen
5. Statement umschreiben (z. B. Subquery in Join)
6. Fehlt ein Not Null oder Check Constraint auf der
Spalte?
MuniQSoft GmbH
DOAG 2012
Index Nutzung-17
Optmizer Trace
Einschalten mittels
ALTER SESSION SET EVENTS='10053 trace name
context forever, level 1';
SELECT/DML-Statement absetzen (Tabellen müssen
analysiert sein)
Bis 11.1 liegt die Trace Datei im Ordner
<ORACLE_BASE>/admin/<sid>/udump
Ab 11.1 liegt die Trace Datei im Ordner
<ORACLE_BASE>/diag/rdbms/<sid>/<inst_name>/trace
Ausschalten mittels:
ALTER SESSION SET EVENTS '10053 trace name
context off';
MuniQSoft GmbH
DOAG 2012
Index Nutzung-18
Auszug des Trace (ab ca. Zeile 1000)
MuniQSoft GmbH
DOAG 2012
Index Nutzung-19
Index Range Scan
Index auf (col)
SELECT * FROM tab
WHERE col BETWEEN
'J' AND 'K';
A:4
C:5
J:6
A:2
K:3
1
Ebene 1
(root)
K:7
S:8
W:9
2
3
Ebene 3
(leaf)
Range Scan
4
ADAMS AABJHGFDSA
ALLEN AABJJHTFGG
MuniQSoft GmbH
5
CLERK AADBGHZGH
CLODI AADFGHJKLK
6
JAMES AADFGHJGFD
JONES AAFGHJJKKHK
7
KING AAFDSADFDSSD
KONG AAFDDSHGFDS
DOAG 2012
Ebene 2
(branch)
8
SMITH AAASDFKLMH
STEVIE AAJHGFDSAL
9
WARD AAAFDSATHG
ZORRO AASSESSSH
Index Nutzung-20
Index Full Scan
Index auf (col)
SELECT col
FROM tab;
A:4
C:5
J:6
A:2
K:3
1
Ebene 1
(root)
K:7
S:8
W:9
2
3
Ebene 2
(branch)
Ebene 3
(leaf)
4
ADAMS AABJHGFDSA
ALLEN AABJJHTFGG
5
CLERK AADBGHZGH
CLODI AADFGHJKLK
6
JAMES AADFGHJGFD
JONES AAFGHJJKKHK
7
KING AAFDSADFDSSD
KONG AAFDDSHGFDS
8
SMITH AAASDFKLMH
STEVIE AAJHGFDSAL
9
WARD AAAFDSATHG
ZORRO AASSESSSH
Index Full Scan
MuniQSoft GmbH
DOAG 2012
Index Nutzung-21
Index Fast Full Scan
Index auf (col)
SELECT count(*)
FROM tab
WHERE col='A'
A:4
C:5
J:6
9
4
ADAMS AABJHGFDSA
ALLEN AABJJHTFGG
2
A:2
K:3
5
CLERK AADBGHZGH
CLODI AADFGHJKLK
Ebene 1
(root)
2
3
1
7
8
1
JAMES AADFGHJGFD
JONES AAFGHJJKKHK
3
7
KING AAFDSADFDSSD
KONG AAFDDSHGFDS
Ebene 2
(branch)
Ebene 3
4 (leaf)
6
5
6
K:7
S:8
W:9
8
SMITH AAASDFKLMH
STEVIE AAJHGFDSAL
9
WARD AAAFDSATHG
ZORRO AASSESSSH
Index Fast Full Scan: Einlesen der Blöcke von Platte in optimierter Reihenfolge
MuniQSoft GmbH
DOAG 2012
Index Nutzung-22
Index Skip Scan
Index auf (col1,col2)
SELECT * FROM tab
WHERE col2=2
A:1
A:3
B:1
A:1
B:3
1
Ebene 1
(root)
B:3
B:5
C:2
2
3
Ebene 2
(branch)
Ebene 3
(leaf)
4
A | 1 <ROWID>
A | 2 <ROWID>
5
A | 3 <ROWID>
A | 4 <ROWID>
6
B | 1 <ROWID>
B | 2 <ROWID>
7
B | 3 <ROWID>
B | 4 <ROWID>
8
B | 5 <ROWID>
C | 1 <ROWID>
9
C | 2 <ROWID>
D | 1 <ROWID>
Index Skip Scan: Überspringt Leaf Blöcke bei gleichem ersten Schlüssel
MuniQSoft GmbH
DOAG 2012
Index Nutzung-23
MuniQSoft GmbH
Tätigkeitsbereiche:
Oracle Support
Hotline: Mo-Fr 8.00 – 18.00 Uhr
Erweiterung um eine Rufbereitschaft auch am Wochenende
möglich
Oracle IT-Consulting & Services
Oracle Schulungen (SQL, PL/SQL, DBA, APEX, B&R, …)
Software-Lösungen
Oracle Lizenzen
MuniQSoft GmbH
Schulungszentrum
Grünwalder Weg 13a
82008 Unterhaching
Tel.: 089 / 679090 40
MuniQSoft GmbH
MuniQSoft GmbH
IT-Consulting, Support,
& Software-Lösungen
Witneystr. 1
82008 Unterhaching
Tel.: 089 / 6228 6789 0
DOAG 2012
Index Nutzung-24