Oracle-DB: Active Session History - into the deep

Transcript

1. Active Session History: into the deep Peter Ramm, OSP Dresden Mai 2016
2. Otto Group Solution Provider Dresden GmbH wer sind wir? Seite 2 Bangkok Taipei Hamburg Altenkunstadt Dresden • Tochterfirma der Otto Group Hamburg • Gegründet 1991 in Dresden • 200 Mitarbeiter • Software für Versandhandel, Logistik, E-Commerce • http://www.osp.de Peter Ramm Teamleiter strategisch- technische Beratung
3. Active Session History • Für mich der größte Schritt in den Analyse-Funktionen der Oracle-DB Seite 3 • Eingeführt mit Oracle 10g, stark erweitert in 11g, noch etwas in 12c • Historisierung der Daten aller aktiven Sessions der DB aus V$Session ++ • Ablage je Sekunde in SGA-Memory, Abfrage per View V$Active_Session_History • Persistierung jeder 10. Sekunde im Zyklus der AWR-Snapshots in AWR-Tabelle, Abfrage per View DBA_Hist_Active_Sess_History • In AWR-Tabelle aufbewahrt analog der weiteren AWR-Daten, Default 7 Tage • Auf dieser Datenbasis lassen sich sehr exakt Betriebszustände der DB auch noch nach langer Zeit rekonstruieren Nutzung erfordert Enterprise Edition + Lizensierung der Option ‚Diagnostics Pack‘
4. Active Session History: Was wird aufgezeichnet? Seite 4 • Ausriss der wesentlichen Attribut der ASH für konkrete Auswertung SESSION_ID, SESSION_SERIAL# Session identifier USER_ID Oracle user identifier; maps to V$SESSION.USER# SQL_Id, SQL_CHILD_NUMBER SQL identifier of the SQL statement that the session was executing SQL_PLAN_HASH_VALUE Numerical representation of the SQL plan for the cursor SQL_PLAN_LINE_ID SQL plan line ID SQL_PLAN_OPERATION Plan operation name SQL_PLAN_OPTIONS Plan operation options SQL_EXEC_ID SQL execution identifier PLSQL_ENTRY_OBJECT_ID Object ID of the top-most PL/SQL subprogram on the stack; PLSQL_OBJECT_ID Object ID of the currently executing PL/SQL subprogram EVENT the event for which the session was waiting for. P1TEXT, P1, P2.. Additional wait parameter BLOCKING_SESSION Session identifier of the blocking session. CURRENT_OBJ# Object ID of the object that the session is referencing. XID Transaction ID that the session was working on at the time of sampling. SERVICE_HASH TNS-Service PROGRAM Name of the operating system program MODULE, ACTION Attributes set by the DBMS_APPLICATION_INFO.SET_MODULE MACHINE Client's operating system machine name PGA_ALLOCATED Amount of PGA memory (in bytes) consumed by this session TEMP_SPACE_ALLOCATED Amount of TEMP space allocated • Vollständige Liste der in ASH gesampelten Attribute: für Oracle 12.1
5. Werkzeuge für Analyse auf ASH-Daten: 1. Enterprise Manager Cloud Control (ab 12c) Seite 5 • Menüpunkt ‚Performance‘ / ‚ASH-Analyse‘ • Erfordert Nachinstallation von PL/SQL-Packages im Schema DBSNMP
6. Werkzeuge für Analyse auf ASH-Daten: 1. Enterprise Manager Cloud Control (ab 12c) Seite 6
7. Werkzeuge für Analyse auf ASH-Daten: 2. Panorama: freies Analyse-Tool Seite 7 Freies Werkzeug für Performance-Analyse von Oracle-DB Verfügbar unter: http://rammpeter.github.io • „Leichte“ Machbarkeit der Analysen mittels GUI-Workflow, auch für „Laien“ • Senken der Hemmschwelle, Problemen tatsächlich auf den Grund zu gehen • Identifikation der konkrete Ursachen für unzureichend Applikations-Performance • Folgend einige Beispiele für ASH-Analyse unter Nutzung von Panorama
8. Herausforderung bei der Analyse per ASH Seite 8 Äußerst hilfreich für Prozess-Analyse ist das Taggen von DB-Sessions mit fachlichen Kontextinformationen zum Prozess am Beginn einer Transaktion. Setzen der Kontext-Info durch Ausführung der PL/SQL-Funktion: DBMS_Application_Info.Set_Module(<Module>, <Action>) • Module, Action: Strings mit jeweils max. 64 Zeichen Standard-Anfrage: Prozess XY lief gestern Nacht 3 x so lange wie üblich! Warum? Die Applikation arbeitet mit Java JEE-Application-Server und Session-Pooling: • Für jede Transaktion wird erneut willkürlich eine DB-Session aus dem Pool geholt • Mit Rücksicht auf den OLTP-Charakter des Systems sind Transaktionen sehr kurz • Ein Prozess skaliert parallel und nutzt dabei eine variable Anzahl von DB-Sessions Wie soll ich hier die Spuren in der Active Session History dem Prozess zuordnen? • werden mit in die ASH gesampelt und erlauben die Zuordnung zum Prozess
9. Ermittlung Ursachen kritischer Prozess-Laufzeiten Workflow bis auf die Zeile des Execution-Plans Seite 9 Standard-Anfrage: Prozess XY lief gestern Nacht 3x so lange wie üblich! Warum? • Panorama: Menü „Session-Waits“ / „Historisch“ • Auswahl Zeitraum, Gruppierung nach „Module“ • Kontext-Menü in Tabelle, „Zeige Top 10 in Zeitleiste“ für Kurven der aktiven Sessions je Module • Drill-Down weiter z.B. über ‚SQL-ID‘, Identifikation der Top-SQLs des Modules • Klick auf SQL-ID, weiter zu Button „Execution Plan“ • Spalte „DB time“ des Execution-Plan zeigt Schwerpunkt-Operationen des SQL
10. Ermittlung Ursachen kritischer Prozess-Laufzeiten SQL für Auswertung ASH aus Panorama Seite 10 WI TH pr ocs AS (S ELE CT /*+ NO _ME RGE */ Ob jec t_I D, Su bPr ogr am_ ID, Ob jec t_T ype, Ow ner, Ob jec t_N ame, Pr oce dur e_n ame FR OM DB A_P roc edu res) SE LEC T / *+ ORD ERE D U SE_ HAS H(u sv f) Pa nor ama-To ol Ram m * / TR IM(s. Mod ule) Gr oup _Va lue, '' In fo, '' In fo_ Hin t, MI N(Sn ap_ ID) Mi n_S nap _ID, MAX (Sn ap_ ID) Ma x_S nap _ID, AV G(Wa it_ Tim e+T ime _Wa ite d)/ 100 0 Ti me_ Wai ted _Av g_m s, SU M(s. Sam ple _Cy cle) Ti me_ Wai ted _Se cs, -- Ge wic hte te Ze it in de r An nah me, da ss Wa it ak tiv fü r di e Da uer de s S amp les wa r ( und da her vo m Sn aps hot ge seh en wu rde) MA X(s. Sam ple _Cy cle) Ma x_S amp le_ Cyc le, -- Ma x. Ab sta nd de r S amp les al s Ko rre ktu rgr oes se fu er Be rec hnu ng LO AD SU M(TM _De lta _CP U_T ime _Se cs) TM _CP U_T ime _Se cs, /* CP U-Ti me in ner hal b de s S amp le-Cy cle */ SU M(TM _De lta _DB _Ti me_ Sec s) TM _DB _Ti me_ Sec s, SU M(De lta _Re ad_ IO_ Req ues ts) De lta _Re ad_ IO_ Req ues ts, SU M(De lta _Wr ite _IO _Re que sts) De lta _Wr ite _IO _Re que sts, SU M(De lta _Re ad_ IO_ kBy tes) De lta _Re ad_ IO_ kBy tes, SU M(De lta _Wr ite _IO _kB yte s) De lta _Wr ite _IO _kB yte s, SU M(De lta _In ter con nec t_I O_k Byt es) De lta _In ter con nec t_I O_k Byt es, MA X(PG A_A llo cat ed)/ (10 24* 102 4) Ma x_P GA_ MB, AV G(PG A_A llo cat ed)/ (10 24* 102 4) Av g_P GA_ MB, MA X(Te mp_ Spa ce_ All oca ted)/ (10 24* 102 4) Ma x_T emp _MB, AV G(Te mp_ Spa ce_ All oca ted)/ (10 24* 102 4) Av g_T emp _MB, CO UNT (1) Co unt _Sa mpl es, MI N(Sa mpl e_T ime) Fi rst _Oc cur ren ce, MA X(Sa mpl e_T ime) La st_ Occ urr enc e, -- So ko mis ch weg en Kon ver tie run g Ti ems tam p na ch Dat e f ür Sub tra kti on (T O_D ATE (TO _CH AR( MAX (Sa mpl e_T ime), 'Y YYY-MM-DD HH 24: MI: SS' ), 'YY YY-MM-DD HH 24: MI: SS' ) - TO _DA TE( TO_ CHA R(M IN(Sa mpl e_T ime), 'Y YYY-MM-DD HH 24: MI: SS' ), 'YY YY-MM-DD HH 24: MI: SS' ))* (24 *60 *60 ) Sa mpl e_D aue r_S ecs, CO UNT (DI STI NCT NV L(T O_C HAR (s. Ins tan ce_ Num ber), ' ')) in sta nce _nu mbe r_C nt, MI N(s. Ins tan ce_ Num ber) in sta nce _nu mbe r, CO UNT (DI STI NCT NV L(T O_C HAR (DE COD E(s. QC_ ins tan ce_ ID, NUL L, s. Ses sio n_I D|| ', '||s. Ses sio n_S eri al_ No, s. QC_ Ses sio n_I D|| ', '||s. QC_ Ses sio n_S eri al#) ), ' ' )) se ssi on_ sn_ Cnt, MI N(D ECO DE(s. QC_ ins tan ce_ ID, NUL L, s. Ses sio n_I D|| ', '||s. Ses sio n_S eri al_ No, s. QC_ Ses sio n_I D|| ', '||s. QC_ Ses sio n_S eri al#) ) se ssi on_ sn, CO UNT (DI STI NCT NV L(T O_C HAR (Ra wTo Hex(s. XID)) , ' ') ) tr ans act ion _Cn t, MI N(Ra wTo Hex(s. XID)) tr ans act ion, CO UNT (DI STI NCT NV L(T O_C HAR (u. Use rNa me), ' ')) us ern ame _Cn t, MI N(u. Use rNa me) us ern ame, CO UNT (DI STI NCT NV L(T O_C HAR (s. SQL _ID), ' ')) sq l_i d_C nt, MI N(s. SQL _ID) sq l_i d, CO UNT (DI STI NCT NV L(T O_C HAR (s. SQL _Ex ec_ ID), ' ')) sq l_e xec _id _Cn t, MI N(s. SQL _Ex ec_ ID) sq l_e xec _id, CO UNT (DI STI NCT NV L(T O_C HAR (RT RIM (s. SQL _Pl an_ Ope rat ion || ' ' ||s. SQL _Pl an_ Opt ion s)) , ' ') ) op era tio n_C nt, MI N(R TRI M(s. SQL _Pl an_ Ope rat ion|| ' ' ||s. SQL _Pl an_ Opt ion s)) op era tio n, CO UNT (DI STI NCT NV L(T O_C HAR (pe o.O bje ct_ Typ e|| CAS E W HEN pe o.O wne r IS NO T N ULL TH EN ' ' EN D||pe o.O wne r|| CAS E W HEN pe o.O bje ct_ Nam e IS NO T N ULL TH EN '. ' E ND| |pe o.O bje ct_ Nam e|| CAS E W HEN pe o.P roc edu re_ Nam e IS NO T N ULL TH EN '. ' E ND| |pe o.P roc edu re_ Nam e), ' ')) en try _pl sql _mo dul e_C nt, MI N(pe o.O bje ct_ Typ e|| CAS E W HEN pe o.O wne r IS NO T N ULL TH EN ' ' EN D||pe o.O wne r|| CAS E W HEN pe o.O bje ct_ Nam e IS NO T N ULL TH EN '. ' E ND| |pe o.O bje ct_ Nam e|| CAS E W HEN pe o.P roc edu re_ Nam e IS NO T N ULL TH EN '. ' E ND| |pe o.P roc edu re_ Nam e) en try _pl sql _mo dul e, CO UNT (DI STI NCT NV L(T O_C HAR (po .Ob jec t_T ype|| CAS E W HEN po .Ow ner IS NO T N ULL TH EN ' ' EN D||po .Ow ner|| CAS E W HEN po .Ob jec t_N ame IS NO T N ULL TH EN '. ' E ND| |po .Ob jec t_N ame|| CAS E W HEN po .Pr oce dur e_N ame IS NO T N ULL TH EN '. ' E ND| |po .Pr oce dur e_N ame), ' ')) pl sql _mo dul e_C nt, MI N(po .Ob jec t_T ype|| CAS E W HEN po .Ow ner IS NO T N ULL TH EN ' ' EN D||po .Ow ner|| CAS E W HEN po .Ob jec t_N ame IS NO T N ULL TH EN '. ' E ND| |po .Ob jec t_N ame|| CAS E W HEN po .Pr oce dur e_N ame IS NO T N ULL TH EN '. ' E ND| |po .Pr oce dur e_N ame) pl sql _mo dul e, CO UNT (DI STI NCT NV L(T O_C HAR (TR IM(s. Mod ule)) , ' ') ) mo dul e_C nt, MI N(T RIM (s. Mod ule)) mo dul e, CO UNT (DI STI NCT NV L(T O_C HAR (TR IM(s. Act ion)) , ' ') ) ac tio n_C nt, MI N(T RIM (s. Act ion)) ac tio n, CO UNT (DI STI NCT NV L(T O_C HAR (NV L(s. Eve nt, s. Ses sio n_S tat e)) , ' ') ) ev ent _Cn t, MI N(N VL(s. Eve nt, s. Ses sio n_S tat e)) ev ent, CO UNT (DI STI NCT NV L(T O_C HAR (NV L(s. Wai t_C las s, 'CP U') ), ' ' )) wa it_ cla ss_ Cnt, MI N(N VL(s. Wai t_C las s, 'CP U') ) wa it_ cla ss, CO UNT (DI STI NCT NV L(T O_C HAR (CA SE WHE N o. Obj ect _ID IS NO T N ULL TH EN LOW ER(o. Own er)| |'. '||o. Obj ect _Na me EL SE '[Un kno wn] TS= '|| NVL (f. Tab les pac e_N ame, 'no ne') EN D), ' ')) cu rre nt_ obj ect _Cn t, MI N(C ASE WH EN o. Obj ect _ID IS NO T N ULL TH EN LOW ER(o. Own er)| |'. '||o. Obj ect _Na me EL SE '[Un kno wn] TS= '|| NVL (f. Tab les pac e_N ame, 'no ne') EN D) cu rre nt_ obj ect, CO UNT (DI STI NCT NV L(T O_C HAR (CA SE WHE N o. Obj ect _ID IS NO T N ULL TH EN LOW ER(o. Own er)| |'. '||o. Obj ect _Na me|| CA SE WHE N o. Sub Obj ect _Na me IS NU LL THE N ' ' E LSE ' ('| |o. Sub Obj ect _Na me|| ')' EN D E LSE '[Un kno wn] TS= '|| NVL (f. Tab les pac e_N ame, 'no ne') EN D), ' ')) cu rre nt_ sub obj ect _Cn t, MI N(C ASE WH EN o. Obj ect _ID IS NO T N ULL TH EN LOW ER(o. Own er)| |'. '||o. Obj ect _Na me|| CA SE WHE N o. Sub Obj ect _Na me IS NU LL THE N ' ' E LSE ' ('| |o. Sub Obj ect _Na me|| ')' EN D E LSE '[Un kno wn] TS= '|| NVL (f. Tab les pac e_N ame, 'no ne') EN D) cu rre nt_ sub obj ect, CO UNT (DI STI NCT NV L(T O_C HAR (sv .Se rvi ce_ Nam e), ' ')) se rvi ce_ Cnt, MI N(sv .Se rvi ce_ Nam e) se rvi ce, CO UNT (DI STI NCT NV L(T O_C HAR (f. Tab leS pac e_N ame), ' ')) ts _na me_ Cnt, MI N(f. Tab leS pac e_N ame) ts _na me, CO UNT (DI STI NCT NV L(T O_C HAR (s. Cur ren t_F ile _No), ' ')) fi le_ no_ Cnt, MI N(s. Cur ren t_F ile _No) fi le_ no, CO UNT (DI STI NCT NV L(T O_C HAR (TR IM(s. Pro gra m)) , ' ') ) pr ogr am_ Cnt, MI N(T RIM (s. Pro gra m)) pr ogr am, CO UNT (DI STI NCT NV L(T O_C HAR (TR IM(s. Mac hin e)) , ' ') ) ma chi ne_ Cnt, MI N(T RIM (s. Mac hin e)) ma chi ne, CO UNT (DI STI NCT NV L(T O_C HAR (s. Mod us), ' ')) mo dus _Cn t, MI N(s. Mod us) mo dus, CO UNT (DI STI NCT NV L(T O_C HAR (DE COD E(s. QC_ Ins tan ce_ ID, NUL L, 'NO ', s. Ins tan ce_ Num ber|| ':' ||s. Ses sio n_I D|| ', '||s. Ses sio n_S eri al_ No)) , ' ') ) pq _Cn t, MI N(D ECO DE(s. QC_ Ins tan ce_ ID, NUL L, 'NO ', s. Ins tan ce_ Num ber|| ':' ||s. Ses sio n_I D|| ', '||s. Ses sio n_S eri al_ No)) pq, CO UNT (DI STI NCT NV L(T O_C HAR (SU BST R(s .Se ssi on_ Typ e,1 ,1) ), ' ' )) se ssi on_ typ e_C nt, MI N(S UBS TR( s.S ess ion _Ty pe, 1,1 )) se ssi on_ typ e FR OM ( SEL ECT /* + N O_M ERG E O RDE RED */ 10 Sa mpl e_C ycl e, DBI D, In sta nce _Nu mbe r, Sn ap_ ID, Sa mpl e_I D, Sa mpl e_T ime, Se ssi on_ id, Se ssi on_ Typ e, Se ssi on_ ser ial# Se ssi on_ Ser ial _No, Us er_ ID, SQ L_C hil d_N umb er, SQ L_P lan _Ha sh_ Val ue, SQ L_O pco de, Se ssi on_ Sta te, Bl ock ing _Se ssi on, Bl ock ing _se ssi on_ Sta tus, bl ock ing _se ssi on_ ser ial# Bl ock ing _se ssi on_ Ser ial _No, NVL (Ev ent , Se ssi on_ Sta te) Eve nt, Ev ent _ID, Se q# Se que nce, P1T ext , P 1, P2T ext , P 2, P3T ext , P 3, Wa it_ Cla ss, Wa it_ Tim e, Ti me_ wai ted, Pr ogr am, Mod ule , A cti on, Cl ien t_I D, Cu rre nt_ Obj# Cu rre nt_ Obj _No , Cu rre nt_ Fil e# Cu rre nt_ Fil e_N o, Cu rre nt_ Blo ck# Cu rre nt_ Blo ck_ No, Ra wTo Hex(X ID) XI D, PL SQL _En try _Ob jec t_I D, PL SQL _En try _Su bPr ogr am_ ID, PL SQL _Ob jec t_I D, PL SQL _Su bPr ogr am_ ID, Se rvi ce_ Has h, QC _Se ssi on_ ID, QC _In sta nce _ID , NVL (SQ L_I D, To p_L eve l_S QL_ ID) SQL _ID , /* Wen n k ein e S QL -ID , d ann we nig ste ns Top-Le vel SQ L-ID ze ige n * / QC _Se ssi on_ Ser ial#, Is _SQ LID _Cu rre nt, To p_L eve l_S QL_ ID, SQ L_P lan _Li ne_ ID, SQ L_P lan _Op era tio n, SQ L_P lan _Op tio ns, SQ L_E xec _ID, SQ L_E xec _St art, Bl ock ing _In st_ ID, Cu rre nt_ Row# Cu rre nt_ Row _No, Re mot e_I nst anc e# Re mot e_I nst anc e_N o, Ma chi ne, Por t, PG A_A llo cat ed, Te mp_ Spa ce_ All oca ted, TM _De lta _Ti me/1 000 000 TM _De lta _Ti me_ Sec s, TM _De lta _CP U_T ime/1 000 000 TM _De lta _CP U_T ime _Se cs, TM _De lta _DB _Ti me/1 000 000 TM _De lta _DB _Ti me_ Sec s, De lta _Ti me/1 000 000 De lta _Ti me_ Sec s, De lta _Re ad_ IO_ Req ues ts, De lta _Wr ite _IO _Re que sts, De lta _Re ad_ IO_ Byt es/1 024 De lta _Re ad_ IO_ kBy tes, De lta _Wr ite _IO _By tes/1 024 De lta _Wr ite _IO _kB yte s, De lta _In ter con nec t_I O_B yte s/1 024 De lta _In ter con nec t_I O_k Byt es, SU BST R(D ECO DE(In _Co nne cti on_ Mgm t, ' Y', ', co nne cti on ma nag eme nt') || DE COD E(In _Pa rse, 'Y' , ' , pa rse') || DE COD E(In _Ha rd_ Par se, 'Y ', ', har d pa rse') || DE COD E(In _SQ L_E xec uti on, 'Y ', ', SQL ex ec' ) | | DE COD E(In _PL SQL _Ex ecu tio n, ' Y', ', PL /SQ L e xec ') || DE COD E(In _PL SQL _RP C, ' Y', ', ex ec inb oun d P L/S QL RPC ca lls ') || DE COD E(In _PL SQL _Co mpi lat ion, 'Y' , ' , P L/S QL com pil e') || DE COD E(In _Ja va_ Exe cut ion, 'Y' , ' , J ava ex ec') || DE COD E(In _Bi nd, 'Y ', ', bin d') || DE COD E(In _Cu rso r_C los e, ' Y', ', cl ose cu rso r') || DE COD E(In _Se que nce _Lo ad, 'Y ', ', loa d s equ enc e') || DE COD E(Ca ptu re_ Ove rhe ad, 'Y ', ', cap tur e o ver hea d') || DE COD E(Re pla y_O ver hea d, ' Y', ', re pla y o ver hea d') || DE COD E(Is _Ca ptu red, 'Y' , ' , s ess ion ca ptu red ') || DE COD E(Is _Re pla yed, 'Y' , ' , s ess ion re pla yed '), 3) Mo dus FR OM D BA_ His t_A cti ve_ Ses s_H ist ory s LE FT OUT ER JOI N (S ELE CT In st_ ID, MIN (Sa mpl e_T ime) Mi n_S amp le_ Tim e FR OM gv $Ac tiv e_S ess ion _Hi sto ry GR OUP BY In st_ ID) v O N v. Ins t_I D = s. Ins tan ce_ Num ber WH ERE (v. Min _Sa mpl e_T ime IS NU LL OR s. Sam ple _Ti me < v. Min _Sa mpl e_T ime) -- Nu r Da ten le sen, die ni cht in gv $Ac tiv e_S ess ion _Hi sto ry vo rko mme n AN D s. DBI D = :A1 UN ION AL L SE LEC T 1 Sa mpl e_C ycl e, 170 413 496 6 D BID , In st_ ID In sta nce _Nu mbe r, NUL L Sn ap_ ID, Sa mpl e_I D, Sa mpl e_T ime, Se ssi on_ id, Se ssi on_ Typ e, Se ssi on_ ser ial# Se ssi on_ Ser ial _No, Us er_ ID, SQ L_C hil d_N umb er, SQ L_P lan _Ha sh_ Val ue, SQ L_O pco de, Se ssi on_ Sta te, Bl ock ing _Se ssi on, Bl ock ing _se ssi on_ Sta tus, bl ock ing _se ssi on_ ser ial# Bl ock ing _se ssi on_ Ser ial _No, NVL (Ev ent , Se ssi on_ Sta te) Eve nt, Ev ent _ID, Se q# Se que nce, P1T ext , P 1, P2T ext , P 2, P3T ext , P 3, Wa it_ Cla ss, Wa it_ Tim e, Ti me_ wai ted, Pr ogr am, Mod ule , A cti on, Cl ien t_I D, Cu rre nt_ Obj# Cu rre nt_ Obj _No , Cu rre nt_ Fil e# Cu rre nt_ Fil e_N o, Cu rre nt_ Blo ck# Cu rre nt_ Blo ck_ No, Ra wTo Hex(X ID) XI D, PL SQL _En try _Ob jec t_I D, PL SQL _En try _Su bPr ogr am_ ID, PL SQL _Ob jec t_I D, PL SQL _Su bPr ogr am_ ID, Se rvi ce_ Has h, QC _Se ssi on_ ID, QC _In sta nce _ID , NVL (SQ L_I D, To p_L eve l_S QL_ ID) SQL _ID , /* Wen n k ein e S QL -ID , d ann we nig ste ns Top-Le vel SQ L-ID ze ige n * / QC _Se ssi on_ Ser ial#, Is _SQ LID _Cu rre nt, To p_L eve l_S QL_ ID, SQ L_P lan _Li ne_ ID, SQ L_P lan _Op era tio n, SQ L_P lan _Op tio ns, SQ L_E xec _ID, SQ L_E xec _St art, Bl ock ing _In st_ ID, Cu rre nt_ Row# Cu rre nt_ Row _No, Re mot e_I nst anc e# Re mot e_I nst anc e_N o, Ma chi ne, Por t, PG A_A llo cat ed, Te mp_ Spa ce_ All oca ted, TM _De lta _Ti me/1 000 000 TM _De lta _Ti me_ Sec s, TM _De lta _CP U_T ime/1 000 000 TM _De lta _CP U_T ime _Se cs, TM _De lta _DB _Ti me/1 000 000 TM _De lta _DB _Ti me_ Sec s, De lta _Ti me/1 000 000 De lta _Ti me_ Sec s, De lta _Re ad_ IO_ Req ues ts, De lta _Wr ite _IO _Re que sts, De lta _Re ad_ IO_ Byt es/1 024 De lta _Re ad_ IO_ kBy tes, De lta _Wr ite _IO _By tes/1 024 De lta _Wr ite _IO _kB yte s, De lta _In ter con nec t_I O_B yte s/1 024 De lta _In ter con nec t_I O_k Byt es, SU BST R(D ECO DE(In _Co nne cti on_ Mgm t, ' Y', ', co nne cti on ma nag eme nt') || DE COD E(In _Pa rse, 'Y' , ' , pa rse') || DE COD E(In _Ha rd_ Par se, 'Y ', ', har d pa rse') || DE COD E(In _SQ L_E xec uti on, 'Y ', ', SQL ex ec' ) | | DE COD E(In _PL SQL _Ex ecu tio n, ' Y', ', PL /SQ L e xec ') || DE COD E(In _PL SQL _RP C, ' Y', ', ex ec inb oun d P L/S QL RPC ca lls ') || DE COD E(In _PL SQL _Co mpi lat ion, 'Y' , ' , P L/S QL com pil e') || DE COD E(In _Ja va_ Exe cut ion, 'Y' , ' , J ava ex ec') || DE COD E(In _Bi nd, 'Y ', ', bin d') || DE COD E(In _Cu rso r_C los e, ' Y', ', cl ose cu rso r') || DE COD E(In _Se que nce _Lo ad, 'Y ', ', loa d s equ enc e') || DE COD E(Ca ptu re_ Ove rhe ad, 'Y ', ', cap tur e o ver hea d') || DE COD E(Re pla y_O ver hea d, ' Y', ', re pla y o ver hea d') || DE COD E(Is _Ca ptu red, 'Y' , ' , s ess ion ca ptu red ') || DE COD E(Is _Re pla yed, 'Y' , ' , s ess ion re pla yed '), 3) Mo dus FR OM gv $Ac tiv e_S ess ion _Hi sto ry )s LE FT OUT ER JOI N DB A_O bje cts o O N o. Obj ect _ID = CAS E W HEN s. P2T ext = 'ob jec t #' TH EN /* Wa it ke nnt Ob jec t */ s. P2 ELS E s. Cur ren t_O bj_ No EN D LE FT OUT ER JOI N DB A_U ser s u ON u. Use r_I D = s. Use r_I D -- LE FT OUT ER JOI N v eru rsa cht Fe hle r LE FT OUT ER JOI N pr ocs pe o ON pe o.O bje ct_ ID = s. PLS QL_ Ent ry_ Obj ect _ID AN D pe o.S ubP rog ram _ID = s. PLS QL_ Ent ry_ Sub Pro gra m_I D LE FT OUT ER JOI N pr ocs po ON po .Ob jec t_I D = s. PLS QL_ Obj ect _ID AN D po .Su bPr ogr am_ ID = s. PLS QL_ Sub Pro gra m_I D LE FT OUT ER JOI N DB A_H ist _Se rvi ce_ Nam e sv ON sv .DB ID = s. DBI D AN D sv .Se rvi ce_ Nam e_H ash = Se rvi ce_ Has h LE FT OUT ER JOI N DB A_D ata _Fi les f O N f. Fil e_I D = s. Cur ren t_F ile _No WH ERE 1 =1 AN D s. Sam ple _Ti me >= TO _TI MES TAM P(: A2, 'Y YYY-MM-DD HH 24: MI' ) A ND s. Sam ple _Ti me < TO _TI MES TAM P(: A3, 'Y YYY-MM-DD HH 24: MI' ) GR OUP BY s. DBI D, TRI M(s. Mod ule) OR DER BY SU M(s. Sam ple _Cy cle) DES C • Zur Info das von Panorama verwendete SQL zur Ermittlung der Kennwerte je Module • Es wird hier die Historie aus DBA_Hist_Active_Sess_History mit den aktuellen sekündlichen Informationen aus GV$Active_Session_Historie überlagert, so dass Auswertungen unabhängig vom AWR-Snapshot bis zur aktuellen Sekunde möglich sind
11. Analyse von „Blocking Lock“-Situationen Rückwirkende Ermittlung des root cause Seite 11 Session-Analyse zeigt verbreitet „row lock contention“ mit mehrfacher Abhängigkeit. Was ist der Auslöser? • Panorama: Menü „DBA Allgemein“ / „DB-Locks“ / „Blocking Locks historisch“ • Auswahl Betrachtungs-Zeitraum, Tabelle zeigt Root-Blocker + geblockte Sessions • Tabelle ist sortiert nach „Total Wait (Sec.)“ aller vom Blocker geblockten Session • „Direct blocked“ und „Total blocked“ verlinken auf Kaskade geblockter Sessions • „Blocking SID“ verlinkt auf ASH-Historie des Root-Blockers • „Blocking Object“ der geblockten Session verlinkt auf RowID und Primary Key- Wert des gelockten Records (sofern es um Row-Lock geht)
12. Analyse von „Blocking Lock“-Situationen SQL für Auswertung ASH aus Panorama Seite 12 WI TH / * Pa no rama-To ol Ramm */ TSS el AS ( SEL ECT 10 Sa m p le _Cycle, /* au f 1 0 Sekun de n ge na u ge run de te Ze it */ TRUNC(h. Samp le _Ti me+ INTERV AL '5 ' SECOND, 'MI') + T RUNC(TO_ NUM B ER(TO_ CHAR(h. Samp le _Ti me+ INTERV AL '5 ' SECOND, 'SS '))/ 10 )/8 64 0 Rou nd ed _S ampl e_ Time, Sn ap _ID , h. In s t an c e _Nu mbe r, Se s sio n_ ID, Se s sio n_ Ser ial#, Blo ck i ng _Se s sio n, Blo c kin g_ Se s sio n_ Ser ial#, Blo ck i ng _Se s sio n_ Sta tu s, Cur ren t_ Fil e#, Cur ren t_ Blo c k#, Blo ck i ng _In st_I D, Cur ren t_ Row#, p2 , p2 Text, Wa it _Ti me, Ti m e _W ai te d, Cur ren t_ Obj#, SQL _I D, User_ ID, Ev e nt , Mo du le , Acti on FROM DBA_ Hist_ Active_ Se s s_Hi s t ory h LE FT OUTER JOIN ( SELE CT In s t _I D, M I N(Sa m p le _T ime) Min _Sa mple _T ime FROM gv$ Ac t ive_ Sessio n_ Histor y GROUP B Y In s t _I D) v ON v.In s t _I D = h. In s t an c e _Nu mbe r WHE RE ( v.Min _Sa mple _T ime IS NULL OR h. Samp le _Ti me < v.Min _Sa mple _T ime) /* Nu r Da te n lese n, di e n icht in gv$ Ac t ive_ Sessio n_ Histor y vorkomme n */ AND h. DBID = :A1 AND h. Sna p_ IDBET WEEN :A2 AND :A3 AND h. Samp le _Ti me BET WEEN TO_ DATE(: A4, 'YYYY-MM-DD HH2 4: M I ') AND T O_DAT E(:A5 , 'YYY Y-MM-DD HH2 4: M I ') AND h. Blo c kin g_ Se s sio n_ Sta tu s IN ('VA LID', 'GL OBAL') / * Se s sio n war ten d au f Blo c kin g-Se s sio n */ UNION ALL SEL ECT 1 Sa m p le _Cycle, CAST (Sa m p le _T ime + INTE RVAL '0 .5 ' S ECOND AS DATE) Rou nd ed _S ampl e_ Time, /* a uf ei ne Se k u nd e ge na u ge run de te Ze it */ NULL Sn ap _ID , h. In s t _ID In s t an c e _Nu mbe r, Se s sio n_ ID, Se s sio n_ Ser ial#, Blo ck i ng _Se s sio n, Blo c kin g_ Se s sio n_ Ser ial#, Blo ck i ng _Se s sio n_ Sta tu s, Cur ren t_ Fil e#, Cur ren t_ Blo c k#, Blo ck i ng _In st_I D, Cur ren t_ Row#, p2 , p2 Text, Wa it _Ti me, Ti m e _W ai te d, Cur ren t_ Obj#, SQL _I D, User_ ID, Ev e nt , Mo du le , Acti on FROM gv$ Ac t ive_ Sessio n_ Histor y h WHE RE Sa m p le _T ime BET WEEN TO_ DATE(: A6, 'YYYY-MM-DD HH2 4: M I ') AND T O_DAT E(:A7 , 'YYY Y-MM-DD HH2 4: M I ') AND h. Blo c kin g_ Se s sio n_ Sta tu s IN ('VA LID', 'GL OBAL') / * Se s sio n wart en d au f Blo ck i ng-Se s sio n */ ) SEL ECT /*+ USE_ NL(gr rh h) */ gr .* , NVL(NV L(NVL (rh h.E v e nt, rh h.S ess i on _St at e), NVL( rh a.E v e nt, rh a.S ess i on _St at e)), 'I NACTI VE') Roo t_ Blo c kin g_ Even t , NVL(rh h.Mo du le, rh a.Mo du le) Roo t_ Blo c kin g_ Mod ul e , NVL(rh h.A c t ion , rh a.A c t ion ) Roo t_ Blo c kin g_ Actio n , NVL(rh h.P rog ram , rh a.P rog ram ) Roo t_ Blo c kin g_ Pro gra m , NVL(rh h.S QL_ ID, rh a.S QL_ ID) Roo t_ Blo c kin g_ SQL _ID , (SEL ECT UserNa me FROM DBA_ Us e rs u WHE RE u. Us e r_ ID = NVL( rh h.Use r_I D, rh a.Use r_I D)) Roo t_ Blo c kin g_ UserNa me FROM ( SEL ECT Roo t_ Sna p_ ID, Roo t_ Rou nd ed _Sa mpl e_ Time, Roo t_ Blo c kin g_ Se s sio n, Roo t_ Blo c kin g_ Se s sio n_ Ser ial# Roo t_ Blo c kin g_ Se s sio n_ Ser ial No, Roo t_ Blo c kin g_ Se s sio n_ Sta tu s, Deco de(SUM(Is_Cycle), 0, NULL , 'Y') De ad lo c k, SUM(Is_Cycle) Is_Cycle _Su m, Roo t_ Blo c kin g_ In s t _I D, CASE WHEN COUNT(DI STINCT l. Curre nt _F ile#) = 1 THEN MIN(l. Curre nt _F ile#) EL SE NULL END Cur ren t_ Fil e_ No, CASE WHEN COUNT(DI STINCT l. Curre nt _Bl ock#) = 1 THEN MI N(l. Curre nt _Bl ock#) EL SE NUL L END Cur ren t_ Blo c k_No , CASE WHEN COUNT(DI STINCT l. Curre nt _Ro w#) = 1 T HEN MIN(l. Curre nt _Ro w#) ELSE NULL END Cur ren t_ Row_ No, CASE WHEN COUNT(DI STINCT l. Session _I D) > 1 THE N '< '|| COUNT(DI STINCT l. Session _I D)|| ' >' EL SE MIN( TO_CHA R(l. Session _I D)) END Blo ck e d_ Sessio ns_T ota l, CASE WHEN COUNT(DI STINCT l. Insta nce _Nu m b er ) > 1 THE N '< '|| COUNT(DI STINCT l. Insta nce _Nu m b er)|| ' >' EL SE MIN( TO_CHA R(l. Insta nce _Nu m b er )) END Wa it ing _I nsta nce, CASE WHEN COUNT(DI STINCT CASE WHE N cLe v e l=1 T HEN l. Session _I D EL SE NUL L END) > 1 THEN '< '||COUNT (DIST INCT CASE WHEN cLe v e l=1 T HEN l. Session _I D EL SE NUL L END)|| ' > ' EL SE MIN(CAS E W HEN cLe v e l=1 T HEN TO_ CHAR(l. Session _I D) E LSE NULL END) END Blo ck e d_ Sessio ns_Di rect, SUM(CASE WHE N l. Wa it_ Time +l .Ti m e _W ai te d > 0 THEN (l. Wa it_ Time +l .Ti m e _W ai te d)/1 00 00 00 EL SE l. Samp le_ Cyc l e /* We nn kein e Wa it-Ti m e a m Sa m p le , d an n s t at istisch a nn eh men d ass üb er ge s a mte n Cycle akti v */ END ) Se c o nd s _ in _W ai t_T ot al, CASE WHEN COUNT(DI STINCT o. Owne r||o. Obj ect_ Name) > 1 THE N /* Nuran zeig enwen n ei nd eu tig*/ '< '||COUNT (DIST INCT o. Owne r||o. Obj ect_ Name)|| ' >' EL SE MIN(L OWER( o. Obj ect_ Type)|| ' '||o. Owne r|| '.'||o. Obj ect_ Name|| CASE WHE N o. Obj ect_ Name LI KE 'SYS_ LOB %% ' THEN ' ('| |(SE LECT Obj ect_ Name FROM DBA_ Obj ects WHE RE Obj ect_ ID=T O_NUMBER( SUBSTR( o. Obj ect_ Name, 8, 10 )) ) ||')' WHE N o. Obj ect_ Name LI KE 'SYS_ IL %% ' THEN ' ('| |(SE LECT Obj ect_ NameFROM DBA_ Obj ectsWHE RE Obj ect_ ID=T O_NUMBER( SUBSTR( o. Obj ect_ Name, 7, 10 )) ) ||')' WHE N o. Sub Obj ect_ Name IS NOT NUL L THEN ' ('| |o. Sub Obj ect_ Name|| ')' EL SE NUL L END) END Roo t_ Blo c kin g_ Obj ect, CASE WHEN COUNT(DI STINCT o. Dat a_ Obje ct_I D) = 1 THE N MIN(o. Dat a_ Obje ct_I D) E LSE NULL END Dat a_ Obj ect_ ID, CASE WHEN COUNT(DI STINCT Roo t_ In s t an c e _Nu mbe r) > 1 THE N '< '||COUNT (DIST INCT Roo t_ In s t an c e _Nu mbe r)|| ' >' EL SE MIN( TO_CHA R(Roo t_ In s t an c e _Nu mbe r)) END Roo t_ In s t an c e _Nu mbe r, CASE WHEN COUNT(DI STINCT Roo t_ SQL_ ID) > 1 THE N '< '||COUNT (DIST INCT Roo t_ SQL_ ID) || ' >' EL SE MIN( Roo t_ SQL_ ID) E ND Roo t_ SQL_ ID, CASE WHEN COUNT(DI STINCT u. Us e rNa m e ) > 1 T HEN '< '|| COUNT(DI STINCT u. Us e rNa m e ) | |' >' EL SE M I N(u. Us e rNa m e ) END Roo t_ Us e rNa m e , CASE WHEN COUNT(DI STINCT Roo t_ Even t) > 1 T HEN '< '|| COUNT(DI STINCT Roo t_ Even t) | |' >' EL SE M I N(Roo t_ Even t) END Roo t_ Even t, CASE WHEN COUNT(DI STINCT Roo t_ Mod ule ) > 1 THE N '< '||COUNT (DIST INCT Roo t_ Mod ule ) || ' >' EL SE MIN(Roo t_ Mod ule ) E ND Roo t_ Mod ule , CASE WHEN COUNT(DI STINCT Roo t_ Actio n) > 1 THE N '< '||COUNT (DIST INCT Roo t_ Actio n) || ' >' EL SE MIN( Roo t_ Actio n) E ND Roo t_ Actio n FROM ( SEL ECT CONNECT_ BY_ROOT Rou nd ed _S ampl e_ Time Roo t_ Rou nd ed _Sa mpl e_ Time, CONNECT_ BY_ ROOT Blo ck i ng _Se s sio n Roo t_ Blo c kin g_ Se s sio n, CONNECT_ BY_ ROOT Blo ck i ng _Se s sio n_ Se ria l# Roo t_ Blo c kin g_ Se s sio n_ Ser ial#, CONNECT_ BY_ ROOT Blo ck i ng _Se s sio n_ Sta tu s Roo t_ Blo c kin g_ Se s sio n_ Sta tu s, CONNECT_ BY_ ROOT Blo ck i ng _In st_I D Roo t_ Blo c kin g_ In s t _I D, CONNECT_ BY_ ROOT ( CASE W HEN l .P2 Te x t = 'ob je c t #' T HEN /* Wa it ken nt Obj ect */ l.P2 EL SE l. Curre nt _Ob j# END) Roo t_ Rea l_ Curre nt _Ob je c t _No, CONNECT_ BY_ ROOT In s t an c e _Nu mbe r Roo t_ In s t an c e _Nu mbe r, CONNECT_ BY_ ROOT S QL_ ID Roo t_ SQL_ ID, CONNECT_ BY_ ROOT User_ ID Roo t_ Us e r_ ID, CONNECT_ BY_ ROOT E v e nt Roo t_ Even t, CONNECT_ BY_ ROOT Sn ap _ID Roo t_ Sna p_ ID, CONNECT_ BY_ ROOT Mo du le Roo t_ Mod ule , CONNECT_ BY_ ROOT A c t ion Roo t_ Actio n, l. *, Le vel cLe v e l, Con ne ct_B y _ IsCy cle Is_Cycle FROM TSS el l CONNECT BY NOCY CLE PRIOR Rou nd ed _S ampl e_ Time = Rou nd ed _S ampl e_ Time AND P RIOR Se s sio n_ ID = Blo ck i ng _Se s sio n AND P RIOR Se s sio n_ Ser ial# = Blo ck i ng _Se s sio n_ Se ria l# AND P RIOR In s t an c e _n umb er = Blo ck i ng _In st_I D ) l LE FT OUTER JOIN DBA_ Obj ects o ON o. Obj ect_ ID = l. Roo t_Re al _Cu rre nt_ Obj ect_ No LE FT OUTER JOIN DBA_ Us e rs u ON u. Us e r_ ID = l. Roo t_Use r_I D GROUP B Y Roo t_ Sna p_ ID, Roo t_ Rou nd ed _Sa mpl e_ Time, Roo t_ Blo c kin g_ Se s sio n, Roo t_ Blo c kin g_ Se s sio n_ Ser ial#, Roo t_ Blo c kin g_ Se s sio n_ Sta tu s , Roo t_ Blo c kin g_ In s t _I D ) gr CROSS J OI N (SEL ECT :A8 DBI D FROM DUA L) db LE FT OUTER JOIN DBA_ Hist_A c t ive_ Sess_Histo ry rh h ON rh h.DB ID = db .DBI D AND rh h.S na p_ ID = gr .Roo t_ Sna p_ ID /* Sna p-ID inn er ha lb v o n RAC-In s t an z e n ist i de nt isc h , d ie s e koe nn et von an de re r In stan z stamme n */ AND rh h.I nsta nce_ Numb er = gr .Roo t_ Blo c kin g_ In s t _I D AND T RUNC(rh h.S ampl e_ Time +I NTERVAL '5 ' SECOND, 'MI') + T RUNC(TO_ NUM B ER(TO_ CHAR( rh h.S ampl e_ Time +I NTERVAL '5 ' SECOND, 'SS '))/ 10 )/8 64 0 = gr .Roo t_ Rou nd ed _Sa mpl e_ Time /* au f 1 0 Sekun de n ge run de te Ze it */ AND rh h.S ess i on _I D = gr .Roo t_ Blo c kin g_ Se s sio n LE FT OUTER JOIN gv$ Ac t ive_ Sessio n_ Histor y rh a ON rh a.I nst_ ID = gr .Roo t_ Blo c kin g_ In s t _I D AND CA ST(rh a.S ampl e_ Time + INTE RVAL '0 .5 ' S ECOND AS DATE) = gr .Roo t_ Rou nd ed _Sa mpl e_ Time /* au f e in e S ekun de g eru nd et e Ze ot */ AND rh a.S ess i on _I D = gr .Roo t_ Blo c kin g_ Se s sio n WHE RE (NOT EXI STS (SEL ECT / *+ HASH_A J ) */ 1 F ROM TSS el i /* Nu r d ie Kn ote n oh ne Pa ren t-Blo ck e r d arste ll en */ WHE RE i. Rou nd ed _Sa mple _T ime = gr .Roo t_ Rou nd ed _Sa mpl e_ Time AND i. Session _I D = gr .Roo t_ Blo c kin g_ Se s sio n AND i. Session _S eri al# = gr .Roo t_ Blo c kin g_ Se s sio n_ Ser ial No AND i. Insta nce _n umbe r = gr .Roo t_ Blo c kin g_ In s t _I D ) OR Is_Cycle _Su m > 0 / * wen n cy cle, da nn Existe nz e ine s Sa m p le s mit weit ere r Re fe ren z au f Blo ck i ng Se s sio n tol eri ere n */ ) ORDER B Y Se c o nd s _ in _W ai t_T ot al DESC • Zur Info das von Panorama verwendete SQL zur Ermittlung der Blocking-Lock-Kaskade • Es wird hier die Historie aus DBA_Hist_Active_Sess_History mit den aktuellen sekündlichen Informationen aus GV$Active_Session_Historie überlagert, so dass Auswertungen unabhängig vom AWR-Snapshot bis zur aktuellen Sekunde möglich sind • Auswertung der Hierarchie der Lock-Abhängigkeiten über CONNECT BY
13. Überlauf des Temp-Tablespace Rückwirkende Ermittlung der größten Verbraucher Seite 13 Alert-log zeigt „ORA-1652: unable to extend temp segment“. Wer verbrauchte die TEMP-Ressourcen zu diesem Zeitpunkt? • Panorama: Menü „Schema / Storage“ / „Temp usage“ / „Historisch“ • Auswahl Betrachtungs-Zeitraum, Gruppierung auf „Minute“ • Minute der maximalen TEMP-Auslastung ermitteln“: – Nach Spalte „ Max. TEMP allocated MB“ sortieren oder – Auf Spalte „Max. TEMP allocated MB“ Kontext-Menü aufrufen, „Spalte in Diagramm einblenden“ auswählen • Link auf „Session / Sn.“ zerlegt die Instanz-Zeile nach DB-Sessions • Über Spalte „Total time waited“ link in „Session-Waits historisch“ nach Instanzen • Unschärfe der Lösung: nicht aktive Sessions mit TEMP sind nicht berücksichtigt • Sortierung nach „Max. Temp“ zeigt die Großverbraucher zu diesem Zeitpunkt • Execution-Plan des SQL zeigt konkrete Zeile im Plan mit Temp-Nutzung
14. Überlauf des Temp-Tablespaces SQL für Auswertung ASH aus Panorama Seite 14 SEL ECT /*+ ORDERED Panora ma-Tool Ramm */ MI N (s.Samp l e _Time) Start _Sa mple_Time, MAX(s.Samp l e _Time) End_Samp l e _Time, SUM(Sample_ C o unt) Sample_ C o unt, SUM(Ti m e_Waited_ Secs) Ti m e_Waited_ Secs, MAX(s.Sum_ PGA_Allocated)/ (1024 *102 4) Ma x_Su m_PGA_Allocat ed, MAX(s.M ax_PGA_ Allocate d_per _Se ssi o n)/ (1024 *102 4) Ma x_PG A_Alloc_Per_Session, SUM(s.Sum_ PGA_Allocated)/ SUM(s.Samp l e _Count)/ (1024 *102 4) Avg_PGA_Alloc_p er_Session, MAX(s.Sum_ Tem p_Space_Allocated)/ (1024 *102 4) Ma x_Su m_Temp_Space _Allocat ed , MAX(s.M ax_Te mp_Space_ Alloc_pe r_Sess)/ (1024 *102 4) Ma x_Tem p_Space_Alloc_per _Sess, SUM(s.Sum_ Tem p_Space_Allocated)/ SUM(s.Samp l e _Count)/ (1024 *102 4) Avg_Temp_Space _Alloc_p er_Sess FR O M ( SEL ECT CAST (Sample_ Time+ INTERVAL '0.5' SECOND AS DATE) Sample_ Time, COUNT( *) Sample_ C o unt, SUM(s.Samp l e _Cycl e) Ti m e_Waited_ Secs, -- Gewichte te Zeit in der Annahm e, dass Wait aktiv f ür die D a uer d es Samples war (un d dahe r vom Sn apshot gesehen wurde) SUM(s.PGA_Al loca ted) Sum_PGA_All o cated, MAX(s.PGA_Al loca ted) Ma x_PG A_Allocated _Pe r_Session, SUM(s.Temp_ Spa ce_Al loca ted) Sum_Temp _Sp ace_All o cated, -- eigen tli ch nichtssage nd, da Su mme über all e Sa mple-Zeiten hinweg , nur benut zt fue r AVG MAX(s.Temp_ Spa ce_Al loca ted) Ma x_Tem p_Space_Alloc_per _Sess FR O M ( SELECT / *+ N O _MERGE ORD ERED */ DBID, 10 Sample_ C ycle, Inst ance_Num ber, Sample_ ID, Sample_ Time, Session_id, Session_Type, Session_ser ial# Session_Serial_No, User_ ID, SQL_Child_Num ber, SQL_Plan_Hash_ Value, SQL_Op code, Session_State, Blocking_Session, Blocking_session_ Sta tus, blocking_ session_ser i a l# Blocking_session_ Ser ial_No, NVL(Event , Session_State) Even t, Event_I D, Seq# Sequen ce, P1Text, P1, P2Text, P2, P3 Text, P3 , Wait_Class, Wait_Time, Ti m e_waited, Progr am, M odule, Action , Client_ID, Curr ent_O bj# Curr ent_O bj_No, Curr ent_File# Curr ent_File_No, Curr ent_Block# Curr ent_Block_No, RawTo H e x(XID) XID, PLSQL_Entry_ Object_ID, PLSQL_Entry_ Sub Pr ogram _ID, PLSQL_Object _ID, PLSQL_SubProgr am_I D, Service_Hash, QC_Session_I D , QC_In stance_I D , NVL(SQL_ ID, Top_Leve l _ SQL _ID) SQL _ID, /* Wenn keine SQL-ID, d ann wenigst ens Top-Leve l SQL-ID zeige n */ QC_Session_Ser i a l#, Is_SQLI D _ C u rren t, Top_Leve l _ SQL _ID, SQL_Plan_Line _ID, SQL_Plan_Op eration, SQL_Plan_Op tions, SQL_Exec_ID, SQL_Exec_Star t, Blocking_Inst _ID, Curr ent_Row# Curr ent_Row_No, Remo te_Inst ance# Remo te_Inst ance_No, Ma chine, Por t, PGA_Allocat ed, Temp_Space _Allocat ed, TM_Delta_Time/10 00000 TM_Delta_Time_Secs, TM_Delta_CPU_Tim e/10 00000 TM_Delta_CPU_Tim e_Secs , TM_Delta_DB_Ti m e/10 00000 TM_Delta_DB_Ti m e_Secs, Delta_Time/10 00000 Delta_Time_Secs, Delta_Rea d_IO_Req uests, Delta_Wr i t e_IO_Req uests, Delta_Rea d_IO_Bytes/10 24 Delta_Rea d_IO_kBytes, Delta_Wr i t e_IO_Bytes/10 24 Delta_Wr i t e_IO_kBytes, Delta_I nterco nnect_I O_Bytes/10 24 Delta_I nterco nnect_I O_kBytes, SUBSTR( D ECODE(In_ C o nnection_ Mgmt, 'Y', ', conn ection ma nagem ent') || DEC O D E(In_ Par se, 'Y', ', par se') || DEC O D E(In_ H a rd_Parse, 'Y', ', h ard par se') || DEC O D E(In_ SQL _Exe cution, 'Y', ', SQL exec') || DEC O D E(In_ PLSQL_ Execu tion, 'Y', ', PL/ SQL exec') || DEC O D E(In_ PLSQL_ R PC, 'Y', ', exec inbou nd PL/SQL RPC calls') || DEC O D E(In_ PLSQL_ C o mpilation, 'Y', ', PL/SQL co mpile') || DEC O D E(In_ Java_Execution, 'Y', ', Java exec') || DEC O D E(In_ Bind, 'Y', ', b i n d') || DEC O D E(In_ C u rsor_ C lose, 'Y', ', close cu rsor') || DEC O D E(In_ Seq uence_L oad, 'Y', ', load se quence') || DEC O D E(Captu re_Ove rhead, 'Y', ', captu re ove rhead ') || DEC O D E(Replay_O verhea d, 'Y', ', re play over head') || DEC O D E(Is_Cap tured, 'Y', ', session cap tured ') || DEC O D E(Is_Rep l a yed, 'Y', ', session r eplayed'), 3) M odus FR O M DBA_Hi st _Act i ve _Se ss_Hi st ory s LEFT OUTER JOI N (SELECT Inst _ID, M IN(Sample_ Time) Min_ Sam ple_Ti m e FR O M gv$Active_Session_ H isto ry GROUP BY Inst _ID) v ON v.In st_ID = s.In stance_Num ber WHERE (v.M i n _Sa mple_Time IS NULL OR s.Samp l e _Time < v.M i n _Sa mple_Time) /* N u r Daten lesen, d i e nicht in gv$Active_Session_ H isto ry vor komme n */ AND s.DBID = :A1 UNION ALL SEL ECT 17041 34966 D BID, 1 Sample_ C ycle, Inst _ID Inst ance_Num ber,Sam ple_ID, Sample_ Time, Session_id, Session_Type, Session_ser ial# Session_Serial_No, User_ ID, SQL_Child_Num ber, SQL_Plan_Hash_ Value, SQL_Op code, Session_State, Blocking_Session, Blocking_session_ Sta tus, blocking_ session_ser i a l# Blocking_session_ Ser ial_No, NVL(Event , Session_State) Even t, Event_I D, Seq# Sequen ce, P1Text, P1, P2Text, P2, P3 Text, P3 , Wait_Class, Wait_Time, Ti m e_waited, Progr am, M odule, Action , Client_ID, Curr ent_O bj# Curr ent_O bj_No, Curr ent_File# Curr ent_File_No, Curr ent_Block# Curr ent_Block_No, RawTo H e x(XID) XID, PLSQL_Entry_ Object_ID, PLSQL_Entry_ Sub Pr ogram _ID, PLSQL_Object _ID, PLSQL_SubProgr am_I D, Service_Hash, QC_Session_I D , QC_In stance_I D , NVL(SQL_ ID, Top_Leve l _ SQL _ID) SQL _ID, /* Wenn keine SQL-ID, d ann wenigst ens Top-Leve l SQL-ID zeige n */ QC_Session_Ser i a l#, Is_SQLI D _ C u rren t, Top_Leve l _ SQL _ID, SQL_Plan_Line _ID, SQL_Plan_Op eration, SQL_Plan_Op tions, SQL_Exec_ID, SQL_Exec_Star t, Blocking_Inst _ID, Curr ent_Row# Curr ent_Row_No, Remo te_Inst ance# Remo te_Inst ance_No, Ma chine, Por t, PGA_Allocat ed, Temp_Space _Allocat ed, TM_Delta_Time/10 00000 TM_Delta_Time_Secs, TM_Delta_CPU_Tim e/10 00000 TM_Delta_CPU_Tim e_Secs , TM_Delta_DB_Ti m e/10 00000 TM_Delta_DB_Ti m e_Secs, Delta_Time/10 00000 Delta_Time_Secs, Delta_Rea d_IO_Req uests, Delta_Wr i t e_IO_Req uests, Delta_Rea d_IO_Bytes/10 24 Delta_Rea d_IO_kBytes, Delta_Wr i t e_IO_Bytes/10 24 Delta_Wr i t e_IO_kBytes, Delta_I nterco nnect_I O_Bytes/10 24 Delta_I nterco nnect_I O_kBytes, SUBSTR( D ECODE(In_ C o nnection_ Mgmt, 'Y', ', conn ection ma nagem ent') || DEC O D E(In_ Par se, 'Y', ', par se') || DEC O D E(In_ H a rd_Parse, 'Y', ', h ard par se') || DEC O D E(In_ SQL _Exe cution, 'Y', ', SQL exec') || DEC O D E(In_ PLSQL_ Execu tion, 'Y', ', PL/ SQL exec') || DEC O D E(In_ PLSQL_ R PC, 'Y', ', exec inbou nd PL/SQL RPC calls') || DEC O D E(In_ PLSQL_ C o mpilation, 'Y', ', PL/SQL co mpile') || DEC O D E(In_ Java_Execution, 'Y', ', Java exec') || DEC O D E(In_ Bind, 'Y', ', b i n d') || DEC O D E(In_ C u rsor_ C lose, 'Y', ', close cu rsor') || DEC O D E(In_ Seq uence_L oad, 'Y', ', load se quence') || DEC O D E(Captu re_Ove rhead, 'Y', ', captu re ove rhead ') || DEC O D E(Replay_O verhea d, 'Y', ', re play over head') || DEC O D E(Is_Cap tured, 'Y', ', session cap tured ') || DEC O D E(Is_Rep l a yed, 'Y', ', session r eplayed'), 3) M odus FR O M gv$Active_Session_ H isto ry )s WHERE NVL(s.Event, s.Session_Stat e) != :A2 AND s.Samp l e _Time >= TO _TIM ESTAM P( :A3 , 'YYYY-MM-DD HH24:MI ') AN D s.Samp l e _Time < TO_ TIM ESTAMP(: A4, 'YYYY-MM-DD HH24:MI ') GROUP BY CAST(Sample_ Time+ INTERVAL '0.5' SECOND AS DATE) -- Auf Ebene eines Samp l e s redu zi e ren ) s WHERE s.Samp l e _Time >= TO _TIM ESTAM P( :A5 , 'YYYY-MM-DD HH24:MI ') -- Nochm al Filter n nach der Run dung a uf gan ze Sekunden AND s.Samp l e _Time < TO_ TIM ESTAMP(: A6, 'YYYY-MM-DD HH24:MI ') -- Nochm al Filter n nach der Run dung a uf gan ze Sekunden GROUP BY TRUN C(s.Samp l e _Time, 'M I') ORDER BY TR UNC(s.Samp l e _Time, 'M I') • Zur Info das von Panorama verwendete SQL zur Ermittlung der Temp- und PGA • Es wird hier die Historie aus DBA_Hist_Active_Sess_History mit den aktuellen sekündlichen Informationen aus GV$Active_Session_Historie überlagert, so dass Auswertungen unabhängig vom AWR-Snapshot bis zur aktuellen Sekunde möglich sind • Gruppierung nach Zeiteinheit erlaubt Darstellung der zeitlichen Auslastung des Temp-Tablespace
15. Active Session History als Basis für systematische Rasterfahndung nach Performance-Antipattern Seite 15 ASH erlaubt vollständiges Scannen der DB-Historie nach Mustern, die z.B. auf problematische Implementierung hinweisen. • 1.9. TABLE ACCESS BY ROWID komplett ersetzbar durch INDEX SCAN (SGA) Für kleinere Tabellen mit wenig Spalten und exzessivem Zugriff ist es Wert, den Indexzugriff und nachfolgenden Tabellenzugriff per TABLE ACCESS BY ROWID zu ersetzen durch einen Index mit allen relevanten Spalten. • 2.5.1 Suboptimaler Zugriff auf Indizes mit nur teilweiser Nutzung des Index INDEX RANGE SCAN mit einer hohen Anzahl von Treffern und restriktiver Filter nach dem TABLE ACCESS BY ROWID führt zu unnötiger Last • 2.5.2 Exzessives Filtern nach TABLE ACCESS BY ROWID auf Grund unscharfem Kriterium im Index-Zugriff (aktuelle SGA) Nutzung von Index-Attributen als Filter statt als Access-Kriterium mit gleichzeitig signifikanter Last. Mögliche Ursachen: - Falscher Datentyp der Bindevariable - Nutzung von Funktionen auf der falschen Seite beim Zugriff auf Spalten des Index Panorama: Menü „Spez.. Erweiterungen“ / „Rasterfahndung“ Einige exemplarische Abfragen unter Nutzung ASH:
16. Seite 16 Ich danke für Ihre Aufmerksamkeit. p.s.: Viele weitere Funktionen von Panorama lassen sich auch auf dem Wege der Neugier erkunden. Es finden nur lesende Zugriffe auf die DB statt. Das maximale Risiko besteht in der Last durch lesende SQLs.