Überwachung problematischer Abfragen im Data Warehouse

Transcrição

Überwachung problematischer Abfragen im Data Warehouse-Umfeld
Überwachung problematischer
Abfragen im
Data Warehouse-Umfeld
Autoren
Dr. Alexander Kick, Credit Suisse,
[email protected]
Daniel Steiger, Trivadis AG,
[email protected]
Keywords
Adhoc-Abfragen, DWH, Überwachung
Summary
Adhoc-Abfragen im Data Warehouse-Umfeld
brauchen häufig unnötig viele Ressourcen. Wir
beschreiben in diesem Artikel, wie solche
Abfragen gezielt überwacht werden können.
Datum
Mai 2005
INHALTSVERZEICHNIS
1
EINFÜHRUNG UND PROBLEMATIK
2
ÜBERWACHUNG DURCH INFORMATIONSREICHE ALERTS
3
BEISPIEL-ALERT
4
IMPLEMENTIERUNG
5
ZUSAMMENFASSUNG
6
LITERATUR
Dr. Alexander Kick, Credit Suisse/Daniel Steiger, Trivadis AG
Seite 1/9
Überwachung problematischer
Abfragen im
Data Warehouse-Umfeld
1 Einführung und Problematik
Ein Hauptproblem im Data Warehouse- (DWH) bzw. im Data Mart-Umfeld ist, dass Abfragen
auftreten können, die unnötigerweise sehr viele Ressourcen verbrauchen. Dies liegt einerseits
daran, dass im Gegensatz zu den meisten OLTP-Systemen Abfragen und Reports durch
sogenannte Power-User (z.B. über ein reines Query-Tool) selbst zusammengestellt werden
können, und sich diese fehlerhaften Abfragen aufgrund der grossen Datenmenge verheerend
auswirken können. Diese schlechten Adhoc-Abfragen entstehen oft durch falsche
Klammerung, mangelnde Einschränkungen (Join-Bedingungen) oder sie sind einfach
umständlich und zu kompliziert formuliert - vom User oder gar einem Code-generierenden
Query-Tool. Häufig laufen derartige Statements extrem lange, benötigen zu viel Temp-Space
oder liefern gar kein Ergebnis.
Obwohl Benutzer stundenlang auf ein Ergebnis warten müssen oder mit Fehlermeldungen
konfrontiert werden, melden sich diese nur gelegentlich oder gar nicht beim DBA oder den
Entwicklern. Stattdessen werden die gleichen Abfragen mehrmals gestartet. Dadurch werden
zusätzlich Ressourcen (CPU, Space) gebunden und stehen den anderen Benutzern nicht
mehr zur Verfügung. Die korrekten Abfragen und Batch-Jobs werden dadurch beeinträchtigt.
Oft werden darüber hinaus Meldungen, wie z. B. "cannot extend temp segment in temp
tablespace", "cannot extend temp segment in normal tablespace" im Alert-Log generiert, mit
denen der DBA nicht viel anfangen kann. Dies liegt daran, dass man rückblickend meist nicht
mehr feststellen kann, welche Abfrage diese Probleme verursacht hat. Oft tritt die oben
beschriebene Situation jedoch auf, ohne dass der Entwickler bzw. der DBA davon etwas
merkt.
2 Überwachung durch informationsreiche Alerts
Diese Problematik kann durch eine gezielte Überwachung derartiger Abfragen entschärft
werden. Dadurch können problematische Statements rechtzeitig gestoppt und das Problem
mit den Benutzern/Entwicklern besprochen werden. Da sowohl die in der Folge korrigierten
Statements sehr viel schneller Ergebnisse liefern als auch andere Benutzer deutlich weniger
beeinträchtigt werden, verbessert sich die Zufriedenheit aller DB-Benutzer. Darüber hinaus
können obengenannte Meldungen vermieden oder zumindest besser beurteilt werden. Mittels
Resource Manager liesse sich nur der Ressourcenverbrauch bestimmter Benutzergruppen
einschränken. Die beschriebene Problematik liesse sich damit im wesentlichen jedoch nicht
lösen.
Seite 2/9
Im Data Warehouse sollte also automatisch und regelmässig geprüft werden, ob gerade
problematische Statements ausgeführt werden und der DBA sollte durch Alerts (z. B. E-MailVersand) informiert werden. Damit er die Ursache für die schlechte Performance und den
hohen Ressourcenbedarf erkennen kann, sollten die Alerts genügend Informationen enthalten.
Diese Informationen, wie z.B. welcher User, welches Statement, bisherige Zeitdauer,
bisheriger Temp-Space-Verbrauch, benutzter Ausführungsplan, lassen sich aus v$session,
v$sqltext und v$sql_plan extrahieren. Statement und Ausführunsplan sind vor allem deswegen
von Bedeutung, weil der Benutzer oft gar nicht das entsprechende SQL-Statement zur
Verfügung hat (z.B. durch ein Query-Tool erzeugt) und insbesondere wegen der Row-LevelSecurity (Virtual Private Database), wodurch Abfragen durch zusätzliche Where-Clauses
angereichert werden. In v$sql_plan sind derartige Anreicherungen dagegen schon
berücksichtigt. Darüber hinaus lässt sich aus dem Ausführungsplan recht schnell das Problem
erkennen und beheben.
3 Beispiel-Alert
Durch die im nächsten Abschnitt beschriebene implementierte Überwachung wurde z.B.
folgender Alert verschickt:
Long running queries on PDM01
User
Sid
SERIAL#
Program
Status
Logon Time
Duration
-----
-------
-------------------------
--------
----------------
--------------
644
17350
sas$
ACTIVE
26.04.2005 12:08
0:40:40
[hh:mm:ss]
-----------M533659
select distinct TIME.DATE_RANK_NUM as PER, CIF.CIF_BU_ID as BUID, CIF.CIF_NBR as CIF,
CIF.CUSTOMER_SEGMENT_DESC as CIFSEG, CIF.CUSTOMER_TYPE_CD as CIFTYPE, DELK.DELC_RESSORT,
DELK.DELC_SECTOR, DELK.DELC_DEPARTMENT, DELK.DELC_GROUP, DELK.DELC_OFFICE, KUV.KUV_RESSORT,
KUV.KUV_SECTOR,
KUV.KUV_DEPARTMENT, KUV.KUV_GROUP, KUV.KUV_OFFICE
from CRDM.CRDM_V_EU_DIM_TIME TIME,
CRDM.CRDM_V_EU_DIM_CIF_LE_CROSS CIF,
CRDM.CRDM_V_EU_FCT_LE_CROSS FCT,
CRDM.CRDM_V_EU_DIM_CREDIT_LE_CROSS CRD,
CRDM.CRDM_V_EU_DIM_ACCOUNT_LE_CROSS ACT,
CRDM.CRDM_V_EU_DIM_OU_DELC DELK,
CRDM.CRDM_V_EU_DIM_OU_KUV KUV
where TIME.VALID_FROM_DATE in ('31MAR2005','28FEB2005')
and CIF.VALID_FROM_DATE in ('31MAR2005','28FEB2005')
and CIF.CIF_BU_ID in ('0011','0012')
and CIF.IC_BUSINESS_FLAG = 'N'
and CIF.CUSTOMER_TYPE_CD not in ('119','219','319','419','439')
and DELK.VALID_FROM_DATE in ('31MAR2005','28FEB2005')
and DELK.OU_DELC_ID = CIF.OU_DELC_ID
and KUV.VALID_FROM_DATE in ('31MAR2005','28FEB2005') and KUV.OU_KUV_ID = CIF.OU_KUV_ID
and DELK.DELC_RESSORT in ('CKP')
Seite 3/9
order by CIF.CIF_NBR
-----------------------------------------------------------------------------------------------------------------| Id
| Operation
|
Name
| Rows
| Bytes |TempSpc| Cost
(%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
|
1 |
(0)|
|*
2 |
|
SORT UNIQUE
|
(0)|
|
|
|
|
|
|
9223P|
8191P|
8191P|
9223P
|
|
9223P|
8191P|
|
9223P
|
|
|
|
|
| CRDM_DIM_OU
| 45484 |
2442K|
|
|
|
9223P|
8191P|
|
|
|
|
|
|
| CRDM_DIM_CIF
| 38598 |
2374K|
|
7940
|
|
9223P|
8191P|
|
9223P
|
|
9223P|
8191P|
|
242P
|
|
724T|
7248T|
|
12G
|
|
32M|
338M|
|
3332
| CRDM_DIM_TIME
|
1 |
11 |
|
2
|
|
32M|
|
|
3330
|
|
|
|
|
|
32M|
|
|
|
|
22M|
|
|
|
|
|
|
|
|
22M|
|
|
722
|
|
19M|
|
|
242P
|
|
|
|
|
|
HASH JOIN
|
3 |
|
|
|
PARTITION RANGE INLIST
| KEY(I | KEY(I |
|*
4 |
TABLE ACCESS FULL
71
(20)| KEY(I | KEY(I |
|*
5 |
(0)|
|
HASH JOIN
|
6 |
9223P
|
| KEY(I | KEY(I |
|*
7 |
TABLE ACCESS FULL
(25)| KEY(I | KEY(I |
|
8 |
(0)|
|
MERGE JOIN CARTESIAN
|
10 |
(66)|
|
|
9 |
(66)|
|
|
|
11 |
(58)|
(50)|
|
INDEX FAST FULL SCAN| CRDM_FCT_NK1
1 |
16 |
BUFFER SORT
17 |
41 |
18 |
INDEX FAST FULL SCAN | CRDM_DIM_ACCOUNT_PK
1 |
19 |
41 |
BUFFER SORT
|
20 |
1 |
12G
|
PARTITION RANGE ALL
1 |
937
41 |
|
(66)|
|
41 |
15 |
(28)|
|
|
PARTITION RANGE ALL
1 |
|
|
BUFFER SORT
14 |
(66)|
|
|
|
(31)|
|
TABLE ACCESS FULL
13 |
|
|
|
|
(58)|
|
|
|
|* 12 |
|
|
|
PARTITION RANGE ALL
41 |
Seite 4/9
|
21 |
(37)|
|
22 |
(0)|
|
INDEX FAST FULL SCAN
1 |
23 |
| CRDM_DIM_CREDIT_NK2
|
19M|
|
|
498
|
|
38 |
2090 |
|
9223P
|
|
|
|
|
| CRDM_DIM_OU
|
38 |
2090 |
|
41 |
BUFFER SORT
|
|
| KEY(I | KEY(I |
|* 24 |
TABLE ACCESS FULL
77
(26)| KEY(I | KEY(I |
-----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------2 - access("OU_ID"="OU_KUV_ID")
4 - filter("VALID_FROM_DATE"='31MAR2005' OR "VALID_FROM_DATE"='28FEB2005')
5 - access("OU_ID"="OU_DELC_ID")
7 - filter(("CIF_BU_ID"='0011' OR "CIF_BU_ID"='0012') AND "IC_BUSINESS_FLAG"='N' AND
("VALID_FROM_DATE"='31MAR2005' OR "VALID_FROM_DATE"='28FEB2005') AND
"CUSTOMER_TYPE_CD"<>'119' AND
"CUSTOMER_TYPE_CD"<>'219' AND "CUSTOMER_TYPE_CD"<>'319' AND
"CUSTOMER_TYPE_CD"<>'419' AND
"CUSTOMER_TYPE_CD"<>'439')
12 - filter("VALID_FROM_DATE"='31MAR2005' OR "VALID_FROM_DATE"='28FEB2005')
24 - filter("RESSORT"='CKP' AND ("VALID_FROM_DATE"='31MAR2005' OR
"VALID_FROM_DATE"='28FEB2005'))
Aus dem Ausführungsplan ist ersichtlich, dass die Abfrage fehlerhaft ist (mehrere "MERGE
JOIN CARTESIAN", 9223P Rows als Ergebnismenge). Man kann daher sehr schnell den
unter 'User' ersichtlichen Benutzer kontaktieren und auf das Problem (in diesem Fall keine
Join-Einschränkung in der Where-Clause) hinweisen.
4 Implementierung
Im folgenden zeigen wir die wichtigsten Ausschnitte der implementierten Monitoring-PL/SQLPackages.
Durch Ausführung der entsprechenden Prozeduren werden lang laufende Abfragen und
Abfragen mit zu hohem Sortier-Bedarf in regelmässigen Abständen überwacht, z.B. so:
exec monitoring.monitor_longruns(7200, 24);
exec monitoring.monitor_hightmp(15000, 48);
Diese Prozeduren starten einen Job, der die Prozedur longruns oder hightmp aufruft.
Dabei wird die Schwelle bei hightmp in MB und bei longruns in Sekunden angegeben. Die
beiden Prozeduren werden also jede Stunde/halbe Stunde mit den Schwellwerten 7200
Sekunden/15000 MB aufgerufen. In der folgenden Implementierung dieser beiden Prozeduren
werden entsprechende Cursor geöffnet und zusammen mit anderen Parametern an die
Seite 5/9
Prozedur show_problem_stmts weitergereicht. Dabei sorgt program not like
'%(P%)' dafür, dass nur die Hauptprozesse (und nicht die parallelen Slave-Prozesse)
ausgewählt werden. Die verwendete Hilfsfunktion to_hhmiss(a.last_call_et) formatiert
die Zeitdauer in Stunden, Minuten u. Sekunden.
procedure hightmp(threshold number, notify boolean) is
tc sys_refcursor;
blksize sys.v_$parameter.value%type;
subject varchar2(50);
header varchar2(200);
underl varchar2(200);
begin
select value into blksize from sys.v_$parameter where name='db_block_size';
open tc for
select distinct rpad(a.username,10) || rpad(a.sid,7) || rpad(a.serial#,9) ||
rpad(a.program,27) || rpad(a.status,10) ||
rpad(to_char(a.logon_time,'dd.mm.yyyy hh24:mi'),18) || rpad(to_hhmiss(a.last_call_et),20)
||rpad(b.mb,14) || rpad(a.audsid,8)
who, a.sql_address, a.sql_hash_value
from
sys.v_$session a,
(select audsid, sum(mb) mb from
(select sqlhash, sum(blocks)*blksize/1024/1024 mb from sys.v_$sort_usage group by
sqlhash) u,
(select distinct audsid, sql_hash_value from sys.v_$session) v
where u.sqlhash = v.sql_hash_value
group by audsid) b
where a.audsid = b.audsid and a.program not like '%(P%)'
and a.username not in ('SYS','SYSTEM') and b.mb > threshold;
subject:= 'Queries with high temp usage on ';
header := 'User
Sid
SERIAL#
Duration [hh:mm:ss] TmpSpace [MB] Audsid
underl := '--------
-----
-------
Program
Status
Logon Time
--------
----------------
';
-------------------------
------------------- ------------- --------';
show_problem_stmts(threshold, notify, tc, subject, header, underl);
end;
procedure longruns(threshold number, notify boolean) is
tc sys_refcursor;
header varchar2(200);
underl varchar2(200);
begin
open tc for
SELECT rpad(username,10) || rpad(sid,7) || rpad(serial#,9) || rpad(program,27) ||
rpad(status,10) ||
rpad(to_char(logon_time,'dd.mm.yyyy hh24:mi'),18) || rpad(to_hhmiss(last_call_et),21)
who, sql_address, sql_hash_value
Seite 6/9
FROM sys.v_$session
WHERE username IS NOT NULL AND status = 'ACTIVE' AND program NOT LIKE '% (P%)'
AND username not like 'SYS%' AND last_call_et > threshold;
subject:= 'Long running queries on ' ;
header := 'User
Sid
SERIAL#
Program
Status
Logon Time
-----
-------
-------------------------
--------
----------------
Duration [hh:mm:ss]';
underl := '--------------------------';
show_problem_stmts(threshold, notify, tc, subject, header, underl);
end;
Im folgenden Ausschnitt sind die wichtigeren Hilfsfunktionen zu sehen. exec_plan retourniert
den Ausführungsplan, sql_query liefert den Text der problematischen Abfrage, der mittels
fold_str (aus Platzgründen nicht gezeigt) in brauchbare Zeilen umformatiert wird
(Zeilenumbruch nach weniger als 100 Zeichen bei einem Leerzeichen).
show_problem_stmts sucht die entsprechenden problematischen Statements und fügt die
dazugehörigen Informationen (User, Laufzeit, SQL-Text, Ausführungsplan, etc.) in ein StringArray ein, das dann an eine E-Mail-Prozedur (ähnlich wie in [1] dokumentiert) übergeben und
so verschickt wird.
function exec_plan(p_address in sys.v_$sqltext.address%type, p_hash_value in
sys.v_$sqltext.hash_value%type) return plan_line is
plan_output plan_line;
begin
delete from plan_table;
insert into plan_table
select ADDRESS, sysdate, 'REMARKS', OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER,
OBJECT_NAME, 0,
'OBJECT_TYPE', OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, POSITION, COST, CARDINALITY,
BYTES,
OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST,
IO_COST,
TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES
from sys.v_$sql_plan
where address = p_address and hash_value = p_hash_value;
select plan_table_output
bulk collect into plan_output
from table(dbms_xplan.display('plan_table',null,'serial'));
return plan_output;
end;
function sql_query(p_address in sys.v_$sqltext.address%type, p_hash_value in
sys.v_$sqltext.hash_value%type) return varchar2 as
l_text varchar2(4000) := '';
begin
Seite 7/9
for x in (select sql_text from sys.v_$sqltext where address = p_address and hash_value =
p_hash_value order by piece) loop
l_text := l_text || x.sql_text;
exit when length(l_text) > 3930;
end loop;
return l_text;
end;
procedure show_problem_stmts(threshold number, notify boolean, cur sys_refcursor, subj
varchar2, header varchar2, underl varchar2) is
type stmt_info is record (who varchar(500), sql_address sys.v_$sqltext.address%type,
sql_hash_value sys.v_$sqltext.hash_value%type);
c stmt_info;
notes notification.msgs;
plan_output plan_line;
dbname varchar2(10);
begin
select name into dbname from sys.v_$database;
subject:= subj || dbname;
loop
fetch cur into c;
exit when cur%notfound;
if notify then
notes.delete;
notes(1):= chr(10) || subject || chr(10);
notes(2):= header;
notes(3):= underl;
notes(4):= c.who || chr(10);
if (c.sql_hash_value != 0) then
notes(5) := fold_str(sql_query(c.sql_address, c.sql_hash_value));
plan_output:= exec_plan(c.sql_address, c.sql_hash_value);
for i in 1.. plan_output.last loop
notes(5 + i):= plan_output(i);
end loop;
end if;
notification.notify(dbname, notes, subject, null, true, false, recipl);
else
…
end if;
end loop;
end;
5 Zusammenfassung
Weil Data Warehouses bzw. Data Marts fast immer auch für Adhoc-Anforderungen benutzt
werden, können problematische Abfragen auftreten, die das System unnötigerweise belasten.
Durch eine gezielte Überwachung derartiger Abfragen und gleichzeitiger Extraktion wichtiger
Seite 8/9
Informationen (SQL-Text, Ausführungsplan) sind der DBA und der Entwickler in der Lage,
effektiv geeignete Massnahmen in die Wege zu leiten. Durch die beschriebene Überwachung
konnte im Data Mart der Credit Suisse darüber hinaus auch nachgewiesen werden, dass die
sehr hohen Spitzenwerte beim Gesamt-Temp-Space-Verbrauch mit fehlerhaften Abfragen
korrespondierten und infolgedessen der Temporary Tablespace von vorher ca. 500 GB auf
weniger als 100 GB reduziert werden konnte.
6 Literatur
[1] Connor McDonald. Mastering Oracle PL/SQL: Practical Solutions. 2004. ISBN 1-59059217-4.
Seite 9/9

Überwachung problematischer Abfragen im Data Warehouse

Transcrição

Documentos relacionados

Imagebroschüre Groupe CIF

4 MATLAB: Control System Toolbox I

Umstellen der MTU (mss) im zyxel Router

Überblick zu den Editionen von SQL Server 2008 R2

Ausstellerkatalog Productronica 2015

Quick Start Package für Microsoft Dynamics CRM

gewinner elke fritzsche fritzsche

Datenbankprogrammierung

Modernisierung von PL/SQL und Forms Anwendungen

IBM DB2 V8

CANopen Booklet

SQL Client für relationale Datenbanken - SQuirreL SQL

Beam me up! OWB Migration nach 11gR2

- RIB Software

Vortrag - Hochschule für Technik und Wirtschaft Dresden

SQL – eine Einführung - Benutzer-Homepage

Benutzerhandbuch

Datenbanken und SQL

Incoterms 2010 – Ein Beispiel für die „neue lex mercatoria“

August-Ausgabe