[Oracle] Performanceprobleme einer Abfrage

[xnicnacx]

Hi liebes Forum

ich habe folgende Abfrage:
SELECT count( * ) FROM SUBSCRIBER WHERE PRESENCE_STATUS !=0 AND SESSIONTIME<sysdate-(0.00115740);

Subscriber -> tabelle mit momentan ~100.000 Einträgen
presence_status: number(6)-Spalte mit wenigen verschiedenen Werten und häufigen Updates
sessiontime: timestamp(6)-Spalte, ebenfall sehr häufige Updates

Problem ist dass die Abfrage >1sec dauert, was viel zu viel ist. Ich habe schon versucht presence_status zu indexieren(sowohl normal als auch bitmap-index). -> Keine nennenswerte Verbesserung. Indexierversuche an sessiontime liefen ebenfalls ins Leere.
Hat jemand einen Ansatz wie ich der Query auf die Sprünge helfen könnte?

 

[MPr]

ein Bitmap-Index wäre bei einer Tabelle mit vielen Updates extrem ungünstig (wegen der bei Updates erforderlichen Locks - vgl. http://www.dbazine.com/oracle/or-articles/jlewis3; obwohl er für count-Statements an sich sehr effektiv sein kann).

Andererseits liefert die Query für einen B*Tree-Index kein wirklich brauchbares Selektionskriterium (Prüfungen auf Ungleichheit machen einen Index-Zugriff in der Regel uninteressant - und einen einseitig beschränkten Datums-Range wird der cbo auch nicht sehr interessant finden - jedenfalls nicht wenn die Verteilung in der Spalte nicht a) bekannt und b) sehr aussagekräftig ist).

Ich würde im gegebenen Fall versuchen, einen B*Tree-Index auf SUBSCRIBER(SESSIONTIME, PRESENCE_STATUS) anzulegen, so dass der Optimizer den Index als komprimierte Version der Tabelle scannen kann. Möglicherweise müsste dazu das != noch in zwei OR-verknüpfte Ranges aufgelöst werden. Dann würde ich für die Query ein sql-Trace erstellen (nach vorherigen Aktualisierung der Statistiken), um zu sehen, wie der Execution Plan ausfällt. Falls der cbo den Index ignoriert, könnte man dann noch versuchen, einen Index-Hint einzubauen, um die Verwendung des neuen Index zu erzwingen.

Noch besser wäre es natürlich, zuerst zu tracen, dann den Index anzulegen, dann dbms_stats aufzurufen und dann noch einmal zu tracen - denn nur so kann man sehen, was sich ändert. Das Trace wäre dabei auch deshalb interessant, weil es ja auch sein könnte, dass ein Index verwendet wird, ohne dass sich die Performance verbessert - das passiert gar nicht selten ...

Gruß

MP

 

[Exceptionfault]

Lass auf jeden Fall die Finger weg von Bitmap Indizes bei Spalten mit häufigen Updates. MPr hat da vollkommen recht, die legen dir durch die Locks das ganze System lahm.

Wenn die Abfrage PRESENCE_STATUS!=0 fix bleibt, und sich die 0 nicht ändert, könnte man es mit einem Function Based Index verwenden. Du müsstest dann zwar deine Abfrage auch umbauen, aber das Ergebnis wäre das Gleiche. Z.B. so:

Ziel wäre es den Optimizer auf einen "=" Vergleich und nicht auf einen "!=" zu bringen.

CREATE INDEX fbi_SUBSCRIBER ON SUBSCRIBER ( 
	case when PRESENCE_STATUS != 0 then 1
		  else NULL
   end
);

CREATE VIEW subscriber_view AS
	SELECT 	
		case when PRESENCE_STATUS != 0 then 1
		     else NULL
      end as PRESENCE_STATUS,
		SESSIONTIME
	FROM subscriber;

SELECT COUNT(*) 
FROM   subscriber_view 
WHERE  PRESENCE_STATUS = 1 
AND    SESSIONTIME < sysdate - (0.00115740);

Hab dazu schonmal an anderer Stelle was geschrieben, vielleicht hilft das weiter: http://mericet.de/index.php?/archives/6-Selektive-Indizierung.html

Ganz davon abgesehen kommt es natürich drauf an, wieviel Sätze der Optimizer erwartet. Wenn du also mehr als, nunja 3-10% aller Sätze aus der Tabelle lesen musst, entscheidet sich der Optimizer eher für einen Full Table Scan, solange die Daten nicht komplett aus einem Index zu lesen sind.
Ein Explain Plan wäre mal ganz interessant, mit aktuellen Statistiken wohlgemerkt.

 

[xnicnacx]

Vielen Dank für eure Ideen, werd mich damit jetzt in Ruhe auseinandersetzen...
Ich sag Bescheid was dabei rauskam
NicNac

edit: der Index über beide Felder (erster Vorschlag von MPr) hat die Query mal eben von 1,2sec auf 0,15sec gebracht, ihr seid meine Helden
Muss nur noch schauen ob die DB mit der Indexpflege nicht zu sehr gestresst wird wenn massenweise Updates kommen...