SQL unter Oracle 11g Monitoring, Tuning, Troubleshooting Tuning
Contents
1. Erzeugung Plan Baseline 11 2007 verified by execution CBO neuer Plan keine Aus f hrung Markierung Database Consult GmbH Jachenau erneutes Parse Plan Verifizierung BLL e980 13 UN JOS Folie 23 von XX J g BLL ajoe09 13 UN JOS wiederholt Plan History Plan Ausf hrung Folie 24 von XX d d 12 SQL Tuning 119g SQL Plan Management e SQL Plan Baselines f r wiederholt ausgef hrte Statements keine ad hoc St gespeichert in SMB SQL management Base SYSAUX in Form von plan baselines e SQL text outline bind variables und compilation environment automatisch Uber optimizer_capture_sql_plan_baselines default FALSE View DBA_SQL_PLAN_ BASELINES oder explizit geladen SQL Sets AWR Cursor Cache Staging Table nutzbar Uber optimizer_use_sql_plan_baselines Schnittstellen EM und DBMS_SPM API BLL ajob10 Aalun JOS 11 2007 Folie 25 von XX d SQL Plan Management e Verifizieren und Akzeptieren von neuen Ausf hrungspl nen Plan Evolution e Varianten Aufruf von DBMS SPM Scheduler Job erstellen Starten von SQL Tuning Advisor Aktivieren von automatischer SQL Tuning Task e Baseline Attribute enabled disabled Accepted not accepted fixed not fixed kein automatisches Hinzufugen BLL ajoe109 134UN JOS 11 2007 Folie 26 von XX he 4 Database Consult GmbH Jachenau 13 SQL Tuning 119 SQL Plan Managemen2. 17d9e6 Enabled YES Accepted Plan hash value 1459632612 Bytes Cost CPU SELECT STATEMENT 00 00 04 TABLE ACCESS BY INDEX ROWID CUSTOMERS 00 00 04 INDEX RANGE SCAN CUSTOMER_STATE 00 00 01 2 access CUST_ STATE PROVINCE STA 11 2007 Folie 29 von XX 4 SQL Plan Management SYS_SOL_PLAN b5429522ee05ab0e Plan was verified Time used 3 9 seconds Failed performance criterion Compound improvement ratio lt 1 4 BLL S 9e10 Aolun JOS Baseline Plan Test Plan Improv Ratio Execution Status COMPLETE COMPLETE Rows Processed 1 1 Elapsed Time ns CPU Time ms Buffer Gets Disk Reads Direct Writes Fetches Executions 11 2007 Folie 30 von XX Ia d Database Consult GmbH Jachenau SQL Tuning 119g SQL Plan Management e Migrations Szenario optimizer_features_enabled auf 10g capture SQL Plans e optimizer_features_enabled auf 11g e Statements entwickeln 11 2007 Teil 4 Real Time SQL Monitoring 11 2007 Database Consult GmbH Jachenau BLL e980 13 UN JOS Folie 31 von XX d g W 9 Ee c e i 2 D Folie 32 von XX d q 16 SQL Tuning 11g SQL Real Time Monitoring Beobachtung von SQL zur Laufzeit Statistiken fur einen einzigen SQL Lauf nicht kumuliert Kriterien parallel queries parallel DML or parallel DDL serielle AusfUhrung gt 5s CPU oder IO Zeit Globale SQL Statistiken per V SQL_MONITOR P
3. Bl cken gt Speichereffizienz just in time materialized view Teil des Shared Pool Instanz G ltig bis abh ngige Objekte modifiziert werden e 3 Ebenen implementiert Server Side Client Side OCI Calls PL SQL function results Parameter result_cache_max_size result_cache_max_result Prozentsatz eines Results result_cache_mode MANUAL FORCE select result_cache no_result_cahce Function Cache CREATE FUNCTION x p_x IN NUMBER RETURN NUMBER RESULT_CACHE RELIES_ON table_t AS SELECT dbms_result_cache status FROM dual Diverses VSRESULT CACHE STATISTICS DBMS_RESULT_CACHE MEMORY_ REPORT DBMS _ RESULT CACHE FLUSH BLL e980 13 UN JOS 11 2007 Folie 38 von XX d Server Result Cache e Einschr nkungen Gleiche Syntax Dictionary und temporary tables CURRVAL NEXTVAL SQL functions current_date current_timestamp local_timestamp userenv sys_context mit non constant Variablen sys_guid sysdate sys_timestamp Non deterministic PL SQL Funktionen keine Subqueries e Parametrierte Results Bindevariablen als Wert BLL j9L10 49 UN JOS 11 2007 Folie 39 von XX d d Database Consult GmbH Jachenau SQL Tuning 11g Teil 6 Automatic SQL Tuning BLL ajob10 Ja UN JOS 11 2007 Folie 40 von XX d SQL Tuning Automation in 11g e Automatisierter Aufruf des Tuning Advisors e Automatisierte Tuning task Autotask Framework Maintenance W
4. SQL Tuning 11g CONSULT SQL unter Oracle 119g Monitoring Tuning Troubleshooting Dr GUnter Unbescheid Database Consult GmbH Jachenau Tuning Features und Tools 11 2007 Database Consult GmbH Jachenau BLL ajoe109 49 UN JOS SQL Tuning 11g Tuning Potenziale Hardware Schema Design Parametrierung 11 2007 SQL Lifecycle und Optimierung 11 2007 Database Consult GmbH Jachenau BLL aj9e10 49 UN JOS BLL ajoe19 49 UN JOS SQL Tuning 11g Statistiken fur den Optimizer Adaptive Cursor SQL Plan Management Real Time SQL Monitoring Result Cache Automatic SQL Tuning SQL Performance Analyzer SQL Repair Advisor BLL aj9e10 49 UN JOS 11 2007 Teil 1 Statistiken fur den Optimizer Sammlung und Vergleich Einstellbare Pr ferenzen Anstehende Statistiken pending Inkrementelle Statistiken Erweiterte Statistiken extended on 9 m Ss e a iy 2 Q 11 2007 Database Consult GmbH Jachenau SQL Tuning 119g Automatische Statistik Sammlung 10g MAINTENANCE _WINDOW_GROUP AUTO_TASKS_JOB_CLASS BLL ajob10 13 UN JOS GATHER_STATS_JOB GATHER STATS PROG _ Action MO FR jeweils 22 Uhr 8 Stunden SA jeweils 0 Uhr 2 Tage Ein und Ausschalten Uber Job Scheduler init ora _optimizer_autostats_Jjob 11 2007 Folie 7 von XX d Automatische Statistik Sammlung 119 Window Group oon Autotask ORASAT_WGRP_OS auto optimizer
5. Tages Fenster stats collection BLL ajoe9 49 UN JOS ORASAT_OS_OPT_SY_ lt n gt GATHER STATS PROG Action MO FR jeweils 22 Uhr 4 Stunden API Uber SA jeweils 6 Uhr 20 Stunden DBMS_AUTO_TASK_ADMIN 11 2007 Folie 8 von XX j d Database Consult GmbH Jachenau SQL Tuning 11g Automatische Statistik Sammlung 119g BEGIN dbms_auto task _admin disable client_name gt auto optimizer stats collection operation gt NULL window_name gt NULL END BLL ajoe10 Aajun JOS BEGIN dbms_auto task _admin disable client_name gt auto optimizer stats collection operation gt NULL window_name gt SUNDAY_WINDOW END BEGIN dbms_auto_task_immediate gather_optimizer_stats END 11 2007 Folie 9 von XX d Sampling sample size BLL j9 10 49 UN JOS e Effiziente Bestimmung einer guten sample Gr sse Manuell aufwendig unflexibel auto schwierig bei stark variierender Selektivitat e Neues Verfahren value sampling grdBere Genauigkeit bei schnellerer Generierung genutzt wenn estimate_percent DBMS STATS AUTO_SAMPLE SIZE keine Sorts und keine Temp Space Nutzung 11 2007 Folie 10 von XX he 4 Database Consult GmbH Jachenau SQL Tuning 11g Sampling Vorgabe Rows Zo 2 00 1 71 14 5 Tab Ind a Spalten ci Number eindeutig Sample Sze 50141 200000 Faktor 4 c2 varchar mit 30 distinktiven Werten c3 varchar per num_ro
6. X d Pr ferenzen BLL ajoeu9 13 UN JOS lt entfallen gt AUTOSTATS_TARGET ESTIMATE_PERCENT Z neu gt METHOD_OPT Nutzung von Variable PUBLISH NO_INVALIDATE Defaults hierarchische INCREMENTAL GRANULARITY Vorgaben STALE PERCENT AUTOSTATS TARGET ALL ORACLE AUTO pauschal fur Datenbank 11 2007 Folie 14 von XX d 4 Database Consult GmbH Jachenau SQL Tuning 11g Pending Statistics Verhalten gesteuert ber Pr ferenzen PUBLISH true default oder false Abfragen per DBMS_STATS GET_PREFS Andern per SET_ lt level gt _PREFS Kontrolliertes session Verhalten gesteuert per Testen BLL ajob10 13 UN JOS optimizer_use_pending_statistics Default false Prufen per View dba_ lt x gt _pending_stats lt x gt TAB IND COL TAB_HISTGRM Allgemeine Aktionen Nutzung publizieren exportieren l schen z B DBMS_STATS PUBLISH_PENDING_STATS 11 2007 Folie 15 von XX d Inkrementelle Statistiken e Interessant fur partitionierte Tabellen 10g separate Statistikgenerierung f r einzelne Partitions aber globale Statistiken immer ber Gesamttabelle generiert full scan 11g Generierung nur f r touched partitions globale Statistiken erzeugt aus Partitionsstatistiken kein Scan auf non touched partitions Zahlen Quelle Oracle Lineitem TPC H 1 8 M rows 230GB 84 partitions 16 cols 2002 Folie 16 von XX d q BLL ajoe09 49 UN JOS Databa
7. e 20 von XX I d Database Consult GmbH Jachenau SQL Tuning 119 Adaptive Cursors e 10g bind variable peeking bei hard parse bad luck bei soft parse e 11g auch extended cursor sharing Beobachtung der Werte von Bind Variablen bind sensitive cursor erstellt bind profiles mit Selektivit tsbereichen Bei unterschiedlicher Selektivit t Makierung bind aware cursor matching Neues Parsing und ggf neuer Cursor wenn Buffer Gets besser sind ansonsten ausf hren des alten Cursors ggf mehrere Ausf hrungspl ne pro Statement eingeschaltet per Default BLL e980 13 UN JOS 11 2007 Folie 21 von XX 4 Adaptive Cursors Views v sql is_bind_sensitive is_bind_aware is_obsolete 3 neue Spalten BLL S 9e10 Aalun JOS V SSOL_CS_HISTOGRAM Anzahl der Ausf hrungen pro Bucket des Werte Histograms V SSOL_CS_SELECTIVITY Pr dikate und Wertebereiche der Bindevariablen V SOL_CS_STATISTICS Executions rows_processed buffer gets und CPU T1me e Parameter _optimizer_adaptive_cursor_sharing true false _optimizer_extended_cursor_sharing_rel none simple 11 2007 Folie 22 von XX I d Database Consult GmbH Jachenau 11 SQL Tuning 11g Teil 3 SQL Plan Management 11 2007 SQL Plan Management e Ver nderte SQL Zugriffspl ne kommen nur zum Einsatz wenn sie besser werden und dann akzeptiert werden Capture SQL Statements Statement Parse
8. indow CPU resource controlled on off switch SQL Top 10 Liste e nicht parallel queries DML DDL recursive ad hoc nicht wiederholt e 4 buckets past week any day in the past week Top in any hour single snapshot Top by average single execution aktiviert SQL Tuning Advisor e Optionale Implementierung von SQL Profilen nur bei signifikaten Verbesserungen gt 3x R ckkopplung mit existierenden SQL Baselines BLL S 9e10 49 UN JOS 11 2007 Folie 41 von XX I d Database Consult GmbH Jachenau SQL Tuning 119g Teil 7 SQL Performance Analyzer BLL ajob19 4a UN JOS 11 2007 Folie 42 von XX d e capture work flow STS mit komplettem Ausf hrungskontext Filterung m glich bertragen auf Test System staging table pack Transport staging table datapump db link etc unpack staging table Erster Lauf SQL workload performance baseline captured SQL execution plan execution statistics serielle Ausf hrung kein DDL und DML nderungen durchf hren Zweiter Lauf mit Erfassung der Statistiken Auswertung mit einstellbaren Metriken buffer get CPU etc Berichte improved regression no change 11 2007 Folie 43 von XX d d BLL ajoe09 134UN JOS Database Consult GmbH Jachenau 21 SQL Tuning 119 SQL Repair Advisor e Fehler bei der Ausf hrung von SQL e Incident im ADR e Generierung von Vorschl gen SQL Patch f r ein Statement ggf I
9. lan level statistics rows memory temp space start end date per VSSQL_PLAN MONITOR Nach Laufzeitende werden Statistiken noch wenige Minuten erhalten ca 5 Bericht per DBMS_SQLTUNE REPORT_SQL_MONITOR BLL e980 J9 UN JOS 11 2007 Folie 33 von XX d SQL Real Time Monitoring Global Information Status EXECUTING Instance ID 1 Session ID 980 S L ID br4m75c20p97h SQL Execution ID 16777219 Plan Hash Value 2992965678 Execution Started 06 07 2007 08 36 42 First Refresh Time 06 07 2007 08 36 46 Last Refresh Time 06 07 2007 08 40 02 BLL ajoe109 Aalun JOS Elapsed Cpu IO Application Other Buffer Reads Writes Time s Time s Waits s Waits s Waits s Gets 11 2007 Folie 34 von XX d Database Consult GmbH Jachenau SQL Tuning 119g Active Session History v active_session_history Bessere Identifizierung von ausgef hrten SQL Statements und wiederholten Ausf hrungen SQL_EXEC_ID SQL_EXEC_START join mit V SQL_MONITOR Identifizierung einzelner Operationen SQL_PLAN_LINE_ID SQL_PLAN_OPERATION aktuelle Operationen IN_PARSE IN_PLSQL_EXECUTION etc BLL ajob0 Aajun JOS 11 2007 Folie 35 von XX d d Teil 5 Result Cache 2 9 Ee c e 2 D Q 11 2007 Folie 37 von XX d d Database Consult GmbH Jachenau 18 SQL Tuning 119 Result Cache Chaching von Result Sets nicht von
10. nfo Pakete f r Support vorbereiten BLL e980 13 UN JOS 11 2007 Folie 45 von XX d Invisible Indexes e Index ist physisch vorhanden jedoch unsichtbar f r Optimizer Ausnahme Index Hint e DML Operation aktualisieren Index e Sinnvoll f r Performance Tests vor dem L schen eines Index Nach dem Anlegen und vor dem Sichtbarmachen BLL S 9e10 134UN JOS create index in_res_guest on res guest_id Index ist f r alle nutzbar alter index in_res_guest invisible Index wird per Default nicht genutzt umschalten ber alter session set optimizer_use_invisible_indexes true Index neu als invisible anlegen create index invisible 11 2007 Folie 47 von XX d d Database Consult GmbH Jachenau SQL Tuning 119g BLL ajob10 13 UN JOS Danke f r s Zuh ren interes Barf Teri Lied Perle werde Larder Linked Oracle 11 g fiir den DBA Eifient konfigurieren optimieren und vervadten ft ADDISON WESLEY 11 2007 Folie 48 von XX d Database Consult GmbH Jachenau 23
11. se Consult GmbH Jachenau SQL Tuning 119g Inkrementelle Statistiken Voraussetzungen AND Praferenz incremental auf true Default false Praferenz publish auf true estimate_percent gt dbms_stats auto_sample_size BLL ajob10 13 UN JOS granularity gt AUTO 11 2007 Folie 17 von XX d Extended Statistics Erlaubt die Erzeugung von Statistiken f r Spaltengruppen Ausdr cke Statistiken NDV number nulls Histogramme Verbesserte Berechnung der Selektivit t Spaltengruppen Mehrfach Pr dikate auf einer Tabelle Semantischen Beziehungen der Spalten untereinander z B Bundesstaat und Nationalstaat Ausdr cke 10g Default Selektivit t 11g Histogramm Nutzung ber virtuelle Spalten BLL ajoe09 134UN JOS 11 2007 Folie 18 von XX he d Database Consult GmbH Jachenau SQL Tuning 119 Extended Statistics Erzeugen select dbms_stats create_extended_stats ownname gt SCOTT tabname gt T1 extension gt C2 C3 from dual BLL ajoe10 Aolun JOS Anschauen SELECT extension_name extension FROM dba_stat_extensions WHERE owner SCOTT AND table_name T1 EXTENSION_NAME EXTENSION SYS_STUOXVZ1C2WGW4DRVBD89VDEO_ C2 C3 Nach dem Generieren der Statistiken die Views DBA TAB COL STATISTICS und DBA HISTOGRAMS 11 2007 Folie 19 von XX d Teil 2 Adaptive Cursor W 9 Ee c e i 2 11 2007 Foli
12. t e Neue Plane werden als non accepted der Baseline hinzugef gt und als accepted eingestuft wenn sie erfolgreich verifiziert werden k nnen e Konfiguration Anteil von SYSAUX Purge Periode nicht genutzter Pl ne plan retention BLL e980 13 UN JOS SET SERVEROUTPUT ON SET LONG 10000 DECLARE report clob BEGIN report DBMS _SPM EVOLVE_SQL PLAN BASELINE sql_handle gt SYS_SQL 593bc74fca8e6738 DBMS_OUTPUT PUT_LINE report END 11 2007 Folie 27 von XX d SQL Plan Management SELECT parameter_name parameter_value FROM DBA _ SQL MANAGEMENT CONFIG Konfigurieren init ora inkl underscores BEGIN DBMS_SPM CONFIGURE space_budget_percent 20 default 10 von SYSAUX g ltige Werte 1 50 ALERT LOG plan_retention_weeks gt default 53 gt Werte 5 523 gilt fur nicht genutzte Pl ne END SELECT sql_ handle plan_name enabled accepted fixed from DBA SQL PLAN BASELINES select from table dbms_xplan display sql plan_baseline sql_handle gt SYS_SQL_209d10fabbedc741 format gt basic BLL ajoe109 4a UN JOS 11 2007 Folie 28 von XX aa d Database Consult GmbH Jachenau 14 SQL Tuning 11g SQL Plan Management SQL handle SYS_SQL_38813422915fb3f1 SQL text select gather_plan_statistics CUST_FIRST_NAME CUST_LAST_NAME CUST_GENDER from customers where CUST_STATE PROVINCE BLL e980 13 UN JOS Plan name SYS _ SQL PLAN 915fb3 19
13. ws 200564 200000 0 33 Random nachtr glich 200 Rows mit gleichem Indizes auf alle Spalten Bunk eines 30 30 0 Statistiken per auto sampling 11 2007 Folie 11 von XX d BLL 9 10 13 UN JOS Vergleichs Report e Zahlenvergleich von Statistiken in folgenden Kontexten verschiedene Stat Tabellen historische Statistiken inerhalb Retention Periode Pending Statisken e Historie ber DBA_TAB_STATS_HISTORY BLL j9 10 134UN JOS table function select from table dbms_stats diff_table_stats_in_history ownname SYS tabname gt T1 timel gt 09 NOV 07 11 13 03 904173 AM 01 00 pctthreshold gt 1 2002 Folie 12 von XX d q Database Consult GmbH Jachenau SQL Tuning 119g Vergleichs Report aaa HE FE FE FE FE HH E E EH FE FE FE FE FE 2 22 2 2 2 2 2 2 2 2 2 2 2 22222 FE FE FE E HE FE FE FE FE FE FE FE FE FE FE FE E FE FE FE FE FE EERE EHR FE EERE STATISTICS DIFFERENCE REPORT FOR BLL aj9e10 49 UN JOS OWNER SYS SOURCE A Statistics as of 09 NOV 07 11 13 03 904173 AM 01 00 SOURCE B Current Statistics in dictionary PCTTHRESHOLD 1 UI OO OD OD OD OB OD OB ODE OB OD OD OD OD OD OD OD OD OD OD OD ODE OD ODE OD ODE OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OD OB OD OD OD OD OD OD OD OD OD OD OD OD OD OB OD OB OD OB OD OD SRC NDV DENSITY HIST NULLS LEN MIN SAMPSIZ NO_STATS 200000 000005 C102 C315 200000 11 2007 Folie 13 von X
Download Pdf Manuals
Related Search