[ORACLE] Abfrage dauert ewig - Tuning?

C

crazyPower

Mitglied
Hallo,

ich habe folgende Abfrage die ich mittels PHP triggere:
Code: 
  select item.Itemnum Artikelnummer, 
  	   item.description Bezeichnung, 
  	   item.IN1 Detailbeschreibung, 
  	   sum(quantity * -1) Menge, 
  	   sum(linecost) Kosten 
  		 from matusetrans,item 
  		  where matusetrans.itemnum=item.itemnum 
  		  and storeloc = 'Lager12' 
  		  and to_char(transdate,'YYYYMM')='2005' || '02' 
  		  and item.Description like '%Schraube 12mm%' 
  		  and substr(GLDEBITACCT,12,4) = '2267' 
 				group by item.description, item.Itemnum, item.IN1 
  				having sum(linecost) >= 0

Die Tabelle matusetrans hat ca 1 Million Datensätze, item ca 150.000. Db ist eine Oracle 8.1.7.
Die obige Abfrage dauert damit ca. 20 Sekunden, und dass ist mir zu lange ;).

1. Frage: Kann man da nochwas tunen?

2. Frage: ich würde gerne neben der Spalte "Kosten" eine Spalte mit den Kosten des Vorjahresmonats haben, also quasi die gleiche Abfrage nochmals da dran bauen. Nur scheiterten bisher alle Versuche das zu tun. Wer kann helfen?

Danke

cP
 
Also, zum tunen wäre es ganz hilfreich noch zu wissen wie die Tabellen aussehen (DESCRIBE) und wo welche Indizes drauf liegen. Eventuell auch einen EXPLAIN PLAN vom Statement falls dir das was sagt ?!

Grundsätzlich sind folgende 3 Zeilen nicht sonderlich gut:
to_char(transdate,'YYYYMM')='2005' || '02'
Ich nehme an, dass transdate vom Typ Date ist. Oracle muss nun um den passenden Eintrag zu finden, jedes Datum in einen Text wandeln was durchaus schonmal etwas dauern kann. Ein Index auf dem Feld würde nix bringen, da du ja nach Text und nicht nach dem Datum suchst.
Lösung:
transdate BETWEEN TO_DATE( '01.02.2005', 'DD.MM.YYYY') AND TO_DATE( '01.03.2005', 'DD.MM.YYYY')

item.Description like '%Schraube 12mm%'
Nunja, hier kann ebenfalls kein Index genutzt werden, da der Anfang des Strings unbekannt ist. Wenn du sicher wärst, dass Schraube ganz vorne steht und du das % vorne weglassen könntest wäre es wesentlich besser und man könnte auch hier einen Index nutzen. Ansonsten so Statements ganz nach hinten in der WHERE Klausel, dass die Ergebnismenge schon möglichst klein ist durch die vorherigen Einschränkungen (die Reihenfolge ist bei Oracle 8 noch relevant)


substr(GLDEBITACCT,12,4) = '2267'
Auch hier kann kein Index genutzt werden da nicht der volle Text verglichen wird. Es gibt in Oracle sog. FBI (Function Based Indizes). Die bringen immer dann was, wenn ein Feld über eine Funktion (wie SUBSTR) geändert wird und nach dem Wert gesucht wird. Voraussetzung ist, dass die FUNKTION IMMER gleich aufgerufen wird. Suchst du also mal nach "12,4" und mal nach "10,3" bringt ein FBI nix. Da würde nur noch ein Redundantes Feld in der Tabelle mit dem Suchtext helfen.

Wg. der 2 Frage.. meinst du die Werte von 02/2004 ?
 
Hallo,
Lösung:
transdate BETWEEN TO_DATE( '01.02.2005', 'DD.MM.YYYY') AND TO_DATE( '01.03.2005', 'DD.MM.YYYY')
Zum Vergrößern anklicken....
Danke für diesen Hinweis. Ich werd ihn morgen wenn ich wieder im Büro bin gleich ausprobieren!

Ansonsten so Statements ganz nach hinten in der WHERE Klausel, dass die Ergebnismenge schon möglichst klein ist durch die vorherigen Einschränkungen (die Reihenfolge ist bei Oracle 8 noch relevant)
Zum Vergrößern anklicken....
Auch dafür Danke! Auch dieser Tipp wird sicherlich eingebaut!

substr(GLDEBITACCT,12,4) = '2267'
Auch hier kann kein Index genutzt werden da nicht der volle Text verglichen wird. Es gibt in Oracle sog. FBI (Function Based Indizes). Die bringen immer dann was, wenn ein Feld über eine Funktion (wie SUBSTR) geändert wird und nach dem Wert gesucht wird. Voraussetzung ist, dass die FUNKTION IMMER gleich aufgerufen wird.
Zum Vergrößern anklicken....
Also es ist tatsächlich immer ab der 12. Stelle 4 Zeichen. Aber FBI ist ein Fremdwort für mich. Hast du da ein Beispiel parat?

Wg. der 2 Frage.. meinst du die Werte von 02/2004 ?
Zum Vergrößern anklicken....
Ja.

Wie gesagt ich werde deine Tipps beherzigen, und mich morgen mal melden, und mitteile wie lange der Select nun dauert!
So lange lass ich das Thema "nicht erledigt"

Nochmal Dickes Dankeschön!

cu

cp
 
Aus gegebenem Anlass ein kleines Demo zu Function Based Indizes (FBI). Die Vorbereitungen sind recht einfach. Zuerst eine kleine überschauliche Tabelle, dann ca. 1 Mio Datensätze und zum Schluss der Primary Key.

Code: 
CREATE TABLE demo
(
	ID		NUMBER( 10 ) 	NOT NULL,
	ORDERDATE	DATE		NOT NULL
)
/

BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT /*+APPEND*/ INTO demo VALUES ( i, SYSDATE - i/24 );
  END LOOP;
  COMMIT;
END;
/

ALTER TABLE demo ADD
  CONSTRAINT PK_DEMO
  PRIMARY KEY ( ID )
  USING INDEX TABLESPACE USERS
/

Um auch faire Messwerte zu bekommen sammeln wir erstmal Statistiken für den Optimizer, denn nur so kann Oracle einen guten Execution Plan für die Ausführung unserer Statements ermittlen. Außerdem bereinigen wir den internen Speicher um gleiche Bedingungen für alle Statements zu bekommen.

Code: 
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( 'EXCEPTION', cascade=>true );
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SET AUTOTRACE ON
SELECT * FROM DEMO WHERE ID = 345678;
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    12 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEMO    |     1 |    12 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEMO |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Wie zu erwarten war entscheidet sich Oracle bei der Abfrage für den Index PK_DEMO. Er ist ja unser Primary Key. Wie sieht es nun aber aus wenn wir alle Datensätze eines bestimmten Tages haben wollen ?
Code: 
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT * FROM DEMO WHERE TO_CHAR( ORDERDATE, 'YYYY-MM-DD' ) = '2003-04-10';
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9990 |   117K|   787  (14)| 00:00:10 |
|*  1 |  TABLE ACCESS FULL| DEMO |  9990 |   117K|   787  (14)| 00:00:10 |
--------------------------------------------------------------------------
Ein Full Table Scan, das ist nicht überraschend, und von der Zeit her sehr unbefriedigend. Also probieren wir es mit einem Index über das Datumsfeld:
Code: 
CREATE INDEX idx_orderdate
 ON DEMO ( orderdate )
 TABLESPACE USERS
/

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT * FROM DEMO WHERE TO_CHAR( ORDERDATE, 'YYYY-MM-DD' )= '2003-04-10';
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9990 |   117K|   787  (14)| 00:00:10 |
|*  1 |  TABLE ACCESS FULL| DEMO |  9990 |   117K|   787  (14)| 00:00:10 |
--------------------------------------------------------------------------
Das Ergebnis ist jedoch das gleiche. Logisch, denn wir fragen ja nicht nach einem Datum ab, sondern müssen erst jedes Datum in einen Text wandeln, bevor wir es vergleichen können. Also ist unser Index, der ja nur die reinen Datumswerte enthält sinnlos.
Besser wäre die Abfrage also folgendermassen:
Code: 
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT * FROM DEMO WHERE ORDERDATE = TO_DATE( '2003-04-10', 'YYYY-MM-DD');
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    12 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEMO          |     1 |    12 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ORDERDATE |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Schon besser. Manchmal gibt es aber auch Statements, die man nicht so einfach umbauen kann, z.B. wie bei der SUBSTR Funktion. Dann helfen FBI. Droppen wir unseren "unnötigen" Index also wieder weg und legen einen FBI an.
Code: 
DROP INDEX idx_orderdate
/
CREATE INDEX idx_fbi_orderdate
 ON demo (  TO_CHAR( ORDERDATE, 'YYYY-MM-DD' ) )
 TABLESPACE USERS
/
Und nun das gleiche "schlechte" Statements wie oben, und siehe da... er nimmt unseren Function Based Index :
Code: 
SELECT * FROM DEMO WHERE TO_CHAR( ORDERDATE, 'YYYY-MM-DD' )= '2003-04-10';
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    24 |   312 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEMO              |    24 |   312 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_FBI_ORDERDATE |    24 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Gute Anwendungsfälle für FBI sind z.B. auch Textfelder in denen unabhängig von der Groß- und Kleinschreibung gesucht werden soll (UPPER(FELD)).
Nagelt mich aber bitte nicht an die Wand, FBI sind ein tolles Feature, ich bin mir aber nicht sicher ob Oracle in Version 8.1.x das schon kann ;-(
 
Hi,

also ich hab deine Tipps mal befolgt, und muss sagen: perfekt...

als erstes hab ich die to_Date-Funktion benutzt. Das hat schonmal einen vorteil von ca. 5 Sek gebracht. Dann habe ich die Stringsuche nach ganz hinten verschoben. Die hat sich so gut wie garnicht auf die Geschwindigkeit ausgewirkt (mal ne sekunde schneller, mal wieder genauso schnell wie vorher).
Dann hab ich mir mal die DB angeschaut, und festgestellt, dass für die Suchabfrage die % wegbleiben können, da das Descriptionfield soetwas wie Kategorien darstellt. D.h. derjenige, der diese Auswertung startet, weiss wie die Kategorien heissen. somit kann er auch das Suchwort komplett eingeben! Das war dann der "Boost" schlecht hin. Von 15 sek runter auf 680 msek. Hervorragend!

Zum Thema FBI:

In meinem Fall lege ich also den FBI über das Feld
GLDEBITACCT, weil ich ja immer ab der 12. Stelle suche.

Korrigier mich bitte fall ich falsch liege:
Code: 
  CREATE INDEX idx_fbi_orderglebitacct
     ON matusetrans ( substr( GLDEBITACCT, 12, 4 ) )
  TABLESPACE tblspace

Jetzt noch eine Frage betreffend des FBI bzw. Tablespace: Dieser Index kann der auch ausserhalb des Tablespace der Ursprungstabelle liegen?
Wichtig ist für mich, dass die Datenstruktur bzw. das Datenmodell nicht verändert werden, und sich keine Veränderungen an der Tabelle bemerkbar machen, da es sich um eine Produktivdatenbank handelt.

Und nochmal zum Ursprungstopic:
Wie kann ich den Vorjahresmonat in die Abfrage einbauen?

Nochmal riesen Dank!

cu

cP
 
Freut mich, wenn die Tuning Tips was gebracht haben.
Den Index kannst du natürlich auch in einen anderen Tablespace legen. Es ist letztendlich ein gewöhnlicher BTree Index für den die übliche Storage Klausel für Indizes gilt. Die Syntax ist so vollkommen richtig!

Nun zu dem Vorjahresmonat fällt mir leider nur ein, das Statement zweimal aufzurufen und per Join zu verknüpfen.
Macht die sache aber leider nicht schneller ;-(

Code: 
SELECT	T1.ARTIKELNUMMER,
	T1.BEZEICHNUNG,
	T1.DETAILBESCHREIBUNG,
	T1.MENGE  AS MENGE_LJ,
	T2.MENGE  AS MENGE_VJ,
	T1.KOSTEN AS KOSTEN_LJ,
	T2.KOSTEN AS KOSTEN_VJ
FROM    (
	SELECT	ITEM.ITEMNUM ARTIKELNUMMER, 
		ITEM.DESCRIPTION BEZEICHNUNG, 
		ITEM.IN1 DETAILBESCHREIBUNG, 
		SUM(QUANTITY * -1) MENGE, 
		SUM(LINECOST) KOSTEN 
	FROM 	MATUSETRANS, ITEM 
	WHERE 	MATUSETRANS.ITEMNUM=ITEM.ITEMNUM 
	AND	STORELOC = 'LAGER12' 
	AND 	TRANSDATE BETWEEN TO_DATE('01022005', 'DDMMYYYY') AND TO_DATE('01032005', 'DDMMYYYY')
	AND 	ITEM.DESCRIPTION = 'SCHRAUBE 12MM' 
	AND 	SUBSTR( GLDEBITACCT,12,4 ) = '2267' 
	GROUP BY ITEM.DESCRIPTION, ITEM.ITEMNUM, ITEM.IN1 
	HAVING SUM(LINECOST) >= 0
	) T1,
	(
	SELECT	ITEM.ITEMNUM ARTIKELNUMMER, 
		ITEM.DESCRIPTION BEZEICHNUNG, 
		ITEM.IN1 DETAILBESCHREIBUNG, 
		SUM(QUANTITY * -1) MENGE, 
		SUM(LINECOST) KOSTEN 
	FROM 	MATUSETRANS, ITEM 
	WHERE 	MATUSETRANS.ITEMNUM=ITEM.ITEMNUM 
	AND	STORELOC = 'LAGER12' 
	AND 	TRANSDATE BETWEEN TO_DATE('01022004', 'DDMMYYYY') AND TO_DATE('01032004', 'DDMMYYYY')
	AND 	ITEM.DESCRIPTION = 'SCHRAUBE 12MM' 
	AND 	SUBSTR( GLDEBITACCT,12,4 ) = '2267' 
	GROUP BY ITEM.DESCRIPTION, ITEM.ITEMNUM, ITEM.IN1 
	HAVING SUM(LINECOST) >= 0
	) T2
WHERE	T1.ARTIKELNUMMER = T2.ARTIKELNUMMER
AND	T1.BEZEICHNUNG = T2.BEZEICHNUNG
AND	T1.DETAILBESCHREIBUNG = T2.DETAILBESCHREIBUNG
/
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück