Oracle DB Memory Techniken für mehr Performance
Werbung
Oracle DB Memory Techniken für mehr Performance Buffer Cache, Result Cache, Big Table Caching, Column Store ... Ulrike Schwinn E-mail: [email protected] Business Unit Database Oracle Deutschland B.V. & Co KG Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Die Memory Nutzung in der Datenbank • Billigste Tuning Massnahme lautet: Vorhandenes Ressourcen richtig nutzen! ☺ • Fragen: – Wird das vorhandene Memory (sinnvoll) genutzt? (Beispielkonfiguration: 250G Memory für Server und 20G Memory für die Datenbank) • Bereiche: Innerhalb des Servers und innerhalb der Datenbank – Würden meine Anwendungen von mehr Memory profitieren? (Extrapolation) • Oracle Werkzeuge: AWR, ASH, statspack, V$Views für Segmentstatistiken, Advisors, Performance Hub, SQL Monitoring etc. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Database Buffer Plattformspezifisch (Engineered Systems) Shared Pool Memory für die Oracle Datenbank 2nd Level Cache Columnstore der In-Memory DB Optimierende Techniken Copyright © 2014 Oracle and/or its affiliates. All rights reserved. 3 Buffer Cache Default Cache Keep/Recycle Cache ABTC Full Database Caching Shared Pool Plattformspezifisch (Engineered Systems) Memory für die Oracle Datenbank 2nd Level Cache Columnstore der In-Memory DB Optimierende Techniken Copyright © 2014 Oracle and/or its affiliates. All rights reserved. 4 Standard System Global Area (SGA) Komponenten Shared Pool Buffer Cache Large Pool ABTC Flashback Redo Log Java Pool Fixed SGA Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Wann wird der Buffer Cache genutzt? • Abhängig von ‒ Segmentgröße (SMALL Table etc.) ‒ Zugriffsart ‒ Häufigkeit des Zugriffs (Hot, Cold) • Erweiterungen in 12c ABTC – Automatic Big Table Caching – Full Database Caching Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Buffer Cache - Konfiguration • Default Cache: Manuell über DB_CACHE_SIZE – Nutzung der Standard Blockgröße • Falls SGA_TARGET gesetzt ist, erfolgt automatische Konfiguration – Setzen von DB_CACHE_SIZE entspricht Minimalwert • Buffer Cache mit Nicht Standard Blockgröße über Parameter DB_CACHE_nK_SIZE – Voraussetzung für Tablespaces mit Nicht Standard Blockgrößen • Beispiele: Erhöhung des Komprimierungsfaktors ... • Memory Advisor - Extrapolation Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Buffer Cache Pools: KEEP und RECYCLE Pools • Häufig und weniger häufig genutzte Objekte in verschiedenen Pools – Über Parameter DB_KEEP_CACHE_SIZE und DB_RECYCLE_CACHE_SIZE – Alle Buffer-Pools funktionieren gleich - nach LRU Algorithmus! • Festlegung der Pools und danach Zuordnung der Objekte – Alle Segmenttypen unterstützt (LOB, INDEX, ...) • Gedacht für: – Objekte im KEEP Pool sind HOT und sollen nicht verdrängt werden. – Objekte im RECYCLE Pool sollen anderen „Hot“ Objekten keinen Platz wegnehmen. • Monitoring über V$DB_CACHE_ADVICE Copyright © 2014 Oracle and/or its affiliates. All rights reserved. KEEP-Pool: Vorgehensweise 1. Pool-Größe definieren: DB_KEEP_CACHE_SIZE 2. Objekte per Storage-Klausel dem KEEP-Pool zuweisen ALTER TABLE ... STORAGE (buffer_pool keep) ALTER INDEX ... STORAGE (buffer_pool keep) ALTER TABLE ... MODIFY LOB (lobcol) (STORAGE (buffer_pool keep)) 3. Bei Bedarf: "Pre-Load" der Objekte in den KEEP-Pool Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Sind die Objekte im Cache? • Analyse über ... – Ausführungsplan, spezielle Statistiken , V$ Views ... • Beispiel: V$BH listet die Blöcke auf, die im Moment im Buffer Cache sind. SELECT o.object_name, o.object_type, o.owner, COUNT(*) NUMBER_OF_BLOCKS FROM dba_objects o, v$bh bh WHERE o.data_object_id = bh.objd AND o.owner in ('SH') GROUP BY o.object_name, o.owner, o.object_type ORDER BY COUNT(*); OBJECT_NAME OBJECT_TYPE OWNER NUMBER_OF_BLOCKS ------------------------------ --------------- --------------- ---------------CUSTOMERS TABLE SH 3 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Wie funktioniert Pre-Loading von Objekten? • Auslösen eines Objekt-Scans über die Tabelle, den Index, das Lobsegment – SELECT-Anweisung (u.U mit Hint) SELECT /*+ FULL(T1) */ sum(numeric_column), min(txt_column) FROM tabelle T1; SELECT /*+ FULL(T2) */ dbms_lob.getlength(lob_column) FROM tabelle T2; • Achtung: Optimierungen des Buffer Cache beachten! – Parameter _small_table_threshhold setzen (MOS Note: 787373.1) ALTER SESSION SET "_small_table_threshold"= 2000; -- wobei Zahl die Blockgröße angibt, die größer als das Segment ist. -- danach kann der FTS ausgeführt werden – Beschreibung in DBA Community-Tipp: Pre-Loading von Tabellen in 11g https://apex.oracle.com/pls/apex/GERMAN_COMMUNITIES.SHOW_RESOURCE_BY_FNAME?P_TIPP_ID=362&P_FILE_NAME=index.html Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Automatic Big Table Cache • Neu in 12.1.0.2 • Neuer Bereich im Buffer Cache • Gedacht für Segmente, die parallel oder seriell ge-scannt werden ABTC • Einstellbar im Single Instanz oder RAC Umfeld • Temperatur basierende Methode für die Segment Platzierung (nicht nur Blöcke) – Beschreibung in DBA Community-Tipp: Automatic Big Table Caching in 12c https://apex.oracle.com/pls/apex/GERMAN_COMMUNITIES.SHOW_TIPP?P_ID=3841 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Vorher … SQL> select sum(prod_id) from sh.sales_copy; Execution Plan ---------------------------------------------------------Plan hash value: 2728018880 --------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 9629 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| SALES_COPY | 7350K| 28M | 9629 (1)| 00:00:01 | --------------------------------------------------------------------------------Statistics ---------------------------------------------------------0 recursive calls 0 db block gets 35346 consistent gets 35341 physical reads 0 redo size 550 bytes sent via SQL*Net to client …. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Automatic Big Table Cache - Setup • Im laufenden Betrieb – Im Single Instanz Umfeld • DB_BIG_TABLE_CACHE_PERCENT_TARGET auf Prozentanteil des Buffer Cache SQL> ALTER SYSTEM SET db_big_table_cache_percent_target=70; System altered – Zusätzlich im RAC Umfeld • PARALLEL_DEGREE_POLICY auf AUTO oder ADAPTIVE (neu in 12c) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Automatic Big Table Cache – In Aktion • Big Table Cache im Überblick SQL> SELECT bt_cache_alloc, bt_cache_target, object_count, memory_buf_alloc, min_cached_temp FROM v$bt_scan_cache; BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC -------------- --------------- ------------ ---------------.700002878 70 3 58009 • Objekte im Cache SQL> SELECT o.object_name, cached_in_mem, size_in_blks, policy, temperature FROM v$bt_scan_obj_temps bt, dba_objects o WHERE bt.dataobj#=o.object_id; OBJECT_NAME CACHED_IN_MEM SIZE_IN_BLKS POLICY TEMPERATURE -------------------- ------------- ------------ ---------- ----------FACT_PP_OUT_ITM_XXX 44878 44878 MEM_ONLY 186000 AB_ELEMENT_RELA 2644 2644 DISK 1000 SALES_COPY 35421 35421 MEM_ONLY 6000 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Nachher mit ABTC SQL> select sum(prod_id) from sh.sales_copy; Execution Plan ---------------------------------------------------------Plan hash value: 2728018880 -------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 9629 (1) | 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL| SALES_COPY | 7350K| 28M | 9629 (1) | 00:00:01 | -------------------------------------------------------------------------------Statistics ---------------------------------------------------------0 recursive calls 0 db block gets 35354 consistent gets 0 physical reads 0 redo size … Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Full Database Caching • Neu in 12.1.0.2 • Datenbank komplett in der SGA (Buffer Cache) halten • Force Full Caching Mode Die Datenbank lädt alle Objekte in den Buffer Cache und hält sie dort (keine Auslagerung mehr). Auch NOCACHE-Objekte (LOBs) sind betroffen=> Performance von FULL TABLE Scans und LOBs • Information in Dictionary View V$DATABASE SELECT force_full_db_caching FROM V$DATABASE; FORCE_FULL_DB_CACHING ------------------------NO • Objekte werden bei Zugriff (Access) geladen, nicht sofort Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Full Database Caching: Vorgehensweise • Sicherstellen, dass der Buffer Cache tatsächlich groß genug ist • Force Full Database Caching aktivieren mit ... STARTUP MOUNT; ALTER DATABASE FORCE FULL DATABASE CACHING; ALTER DATABASE OPEN; • Meldung in Alert Datei beachten Mon Nov 02 16:07:22 2015 Buffer Cache Force Full DB Caching mode on when DB does not fit in cache. Turning off Force Full DB Caching advisable • Full DB Caching wird auf Ebene der Container-Datenbank eingestellt – Alle Pluggable Databases betroffen Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Database Buffer Plattformspezifisch (Engineered Systems) Shared Pool Memory für die Oracle 2nd Level Cache Read-only Overflow für DB Block Buffers Datenbank Columnstore der In-Memory DB Optimierende Techniken Copyright © 2014 Oracle and/or its affiliates. All rights reserved. 19 Database Smart Flash Cache ("Flash Cache“) • Ab 11gR2 • Erweiterung des Datenbank Buffer Caches – SSD ist wie ein Level 2 Cache (SGA ist Level 1) SGA Memory (Level 1) Flash Cache (Level 2) • Schneller als Platte (100x faster for reads) • Günstiger als Memory • Größere Kapazität – Read-only Overflow für DB Block Buffers Disks (NAS/SAN) • Flash Disks werden (normalerweise) von einer Instanz exklusiv genutzt – Ausnahme ODA: „Shared Flash Cache“ • Verfügbar auf Linux und Solaris Plattformen Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Nutzung des Database Flash Cache • Init-Parameter einstellen – db_flash_cache_file = {OS-Pfad zur Flash Disk} – db_flash_cache_size = {Größe der Flash Disk} Flash Cache (Level 2) • Strategien zum Pinnen von Objekten im Flash Cache ALTER TABLE/INDEX name STORAGE (flash_cache KEEP)); • Zusätzlicher SGA-Bedarf für Metadaten-Verwaltung – Pro Datenbank-Block 100 Byte; auf RAC-Systemen 200 Byte • Optimizing Oracle Database Performance on Oracle Linux with Flash http://www.oracle.com/us/technologies/linux/oracle-linux-with-flash-2004731.pdf • How To Size the Database Smart Flash Cache (Doc ID 1317950.1) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Database Buffer Shared Pool Plattformspezifisch (Engineered Systems) Result Cache Server- /Clientside Memory für die Oracle Datenbank 2nd Level Cache Columnstore der In-Memory DB Optimierende Techniken Copyright © 2014 Oracle and/or its affiliates. All rights reserved. 22 Query und PL/SQL Result Caches • Eigener Cache im Shared Pool • Einfache Form des Tunings für – SQL-Abfragen – PL/SQL-Funktionen • Gedacht für: – Vorhersehbare Abfragen/sich wiederholende Abfragen – Abfragen mit kleinen Ergebnismengen – Langlaufende Abfragen mit teuren Berechnungen • Automatischer Refresh bei Daten-Änderungen ! • Einfaches Setup und Monitoring der Cache-Nutzung Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Das Setup – ganz einfach • Initialisierungsparameter RESULT_CACHE_MAX_RESULT RESULT_CACHE_MAX_SIZE RESULT_CACHE_MODE 5 (%) abh. von O/S MANUAL/FORCE RESULT_CACHE_REMOTE_EXPIRATION 0 (min) • Session Parameter RESULT_CACHE_MODE • Einstellung RESULT_CACHE in der Tabellendefinition ALTER TABLE tabelle RESULT_CACHE (MODE FORCE); • Über Hint RESULT_CACHE SELECT /*+ RESULT_CACHE*/ * FROM tabelle; Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Result Cache: Query Hints Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Result Cache-Informationen in den Statistiken Statistiken ---------------------------------------------------------0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 1304 bytes sent via SQL*Net to client 491 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Monitoring • Ausführungspläne, set autotrace, Views, Package DBMS_RESULT_CACHE SQL> SELECT name, type, row_count, status, invalidations, scan_count 2 FROM v$result_cache_objects order by 1; NAME -------------------------------------------------------------------------------TYPE ROW_COUNT STATUS INVALIDATIONS SCAN_COUNT ---------- ---------- --------- ------------- ---------"SH"."CACHED"::8."CACHED"#32fb3b6bdac49c05 #1 Result 1 Published 0 0 "SH"."CACHED"::8."CACHED"#32fb3b6bdac49c05 #1 Result 1 Invalid 0 6 SH.CACHED Dependency 0 Published 0 0 SH.CUSTOMERS Dependency 0 Published 0 0 SH.T Dependency 0 Published 1 0 select /*+ result_cache */ count(*) from customers Result 1 Published 0 1 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. PL/SQL Function Result Cache für Entwickler • Result Cache für (cross session) PL/SQL Funktionen – Häufige Ausführung – Abhängig von Informationen, die sich selten ändern • Ohne ... CREATE OR REPLACE FUNCTION customer_sales (cust_id_in IN number) RETURN number IS • Mit ... CREATE OR REPLACE FUNCTION customer_sales (cust_id_in IN number) RETURN number result_cache IS Copyright © 2014 Oracle and/or its affiliates. All rights reserved. PL/SQL Function Result Cache ... Bitte Beachten ... • Nicht möglich in anonymen Blöcken und keine Pipelined Table Function • Dictionary Tables, Temporary Tables, Sequences oder nondeterministic SQL Functions sind nicht referenzierbar • Enthält keine OUT oder IN OUT Parameter • IN Parameter sind keine LOBs, REF CURSOR, Collections, Objekte oder Recs • Der Return Typ ist kein LOB, REF CURSOR, Objekt oder Record • Neuigkeiten ab 11gR2: kein RELIES_ON erforderlich • Neuigkeit in 12c: Invoker Rights Funktionen für RESULT_CACHE Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Database Buffer Plattformspezifisch (Engineered Systems) Shared Pool Memory für die Oracle Datenbank 2nd Level Cache Columnstore Row und Column In-Memory Formate auf der selben Tabelle Simultan und transaktionskonsistent Optimierende Techniken Copyright © 2014 Oracle and/or its affiliates. All rights reserved. 30 Neue SGA Komponente: In-Memory Area Shared Pool Buffer Cache Large Pool ABTC In-Memory Area Flashback Redo Log Java Pool Fixed SGA Copyright © 2014 Oracle and/or its affiliates. All rights reserved. In-Memory Column Store – Kurzbeschreibung Pure In-Memory Columnar • Reines In-Memory Format • Nutzung unterschiedlicher Komprimierungsarten (2x - 20x) • Einschaltbar auf Objektebene => Entscheidung des Users • Automatisch Transaktionskonsistent SALES • Statisch und damit kein LRU • Verfügbar auf allen Plattformen • Ab 12.1.0.2 mit Option Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Das duale Format in der Datenbank Memory Memory SALES SALES Row Format Column Format • Beides: Row und Column InMemory Formate für die gleiche Tabelle • Real Time Analysen nutzen das neue Column Format • OLTP Applikationen nutzen das Row Format • Erhalt der Transaktionskonsistenz Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Konfiguration in 2 Schritten – ganz einfach 1. Memory Allokation in der SGA über neuen Initialisierungsparameter – INMEMORY_SIZE 2. Auswahl der Objekte (Spalten, Tabellen/Partitionen, Materialized Views, Tablespace) und Festlegung von Komprimierung und Priorität CREATE TABLE PARTITION BY (PARTITION (PARTITION customers … LIST p1 … INMEMORY FOR QUERY LOW, p2 … NO INMEMORY); Hinweise: - u.U. überflüssige analytische Indizes löschen! - Nicht alle Objekte müssen im Column Store vorliegen! Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Wann geeignet, wann nicht? • Abfragen können deutlich profitieren ... – Scannen von vielen Zeilen und Anwendung von Filtern (=, <, >, und IN etc.) – Abfragen mit Operationen (min, max, sum, distinct etc.) – Abfragen einer Teilmenge von Spalten einer Tabelle (5 von 100 Spalten) – Joins von kleinen Tabellen mit einer großen Tabelle mit Filterung von vielen Zeilen ⇒ Optimizer entscheidet mit zusätzlichen neuen Optimizerzugriffen • Kein Einfluß auf ... – Network round trips, logon/logoff, Parsing, PL/SQL … – Komplexe Datenberechnungen (processing) – Einmaliges Laden und Selektieren (Staging Tabellen, ETL, Temp Tables) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Welche Objekte sind überhaupt geeignet? • In-Memory Advisor (Download von MOS) • Analyse eines existierenden DB Workloads (AWR & ASH) • Liefert Liste von Objekten, die am meisten vom Columnstore profitieren können Copyright © 2014 Oracle and/or its affiliates. All rights reserved. Zusammenfassung • Verschiedene Memory Technologien und Caches der Oracle-Datenbank – Buffer Cache: KEEP und RECYCLE, Flash Cache, Result Cache, Column Store • Mehrwert liegt in der Kombination der Technologien – Aggregate auf normale, relationale Tabellen: In-Memory DB – LOBs, JSON, XML, Geodaten: Buffer Cache KEEP Pool – Query Result Cache • Unbedingt auch weitere Tuning Techniken mitberücksichtigen – Partitionierung, Statistikmanagement, SQL Plan Baseline etc. • Erweitungen in 12c Release 2 Alles mit minimalem Einfluß auf die Applikation! Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
