Transcript
Performance-Optimierung mit Memory-Techniken 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. |
... in eigener Sache ... • Deutschsprachiger Community Blog – http://blogs.oracle.com/dbacommunity_deutsch
• News für den DBA und Developer in 30 Minuten (Webcast) – http://tinyurl.com/oradba-monthly – http://tinyurl.com/oradev-monthly
• Alle Events auf einen Blick – http://tinyurl.com/oraclebudb
• Alle Dojos bestellen oder Herunterladen – http://tinyurl.com/dojoonline
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Result Cache
Database Buffer Default Cache Keep/Recycle Cache LRU-Algorithmus Automatic Big Table Cache
Shared Pool oder Client Transparent für Anwendungen
In-Memory
Exadata X4 Hybrid Columnar Compression Smart Scan Storage Index Smart Flash Cache (Read/Write) Smart Flash Logging (Redo-Write)
Techniken der Flash Cache
Datenbank
Data Buffer Erweiterung 2nd level Cache Auch für Datafiles
Memory optimierende Techniken Compression Partitioning Parallelisierung OLAP
In-Memory Option 12c Row und Column In-Memory Formate auf der selben Tabelle Simultan und transaktionskonsistent Column: Analytics & Reporting Row : OLTP
In-Memory Techniken der TimesTen IMDB
Middle-Tier
TimesTen Application-Tier Database Cache TimesTen for Exalytics
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Coherence Data Grid Object Cache / Data Grid für verteilte Anwendungen
3
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.
Der Buffer Cache • Manuelle Konfiguration des (Default) Buffer Cache über Parameter 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 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.
Pre-Loading der Objekte • Auslösen eines Objekt-Scans über die Tabelle, den Index, das Lobsegment • Prinzipiell eine 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 – 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.
1
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.
2
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 ab 12c)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
• Monitoren
2
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.
2
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.
2
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 sind betroffen.
• 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 • Empfehlung von Oracle: http://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT89659 Oracle recommends that you enable force full database caching mode only when the buffer cache size of each individual instance is greater than the database size. This guideline applies to both single-instance and Oracle RAC databases. However, when Oracle RAC applications are well partitioned, you can enable force full database caching mode when the combined buffer cache of all instances, with extra space to handle duplicate cached blocks between instances, is greater than the database size.
• Force Full Database Caching aktivieren mit ... ALTER DATABASE MOUNT; ALTER DATABASE FORCE FULL DATABASE CACHING;
• Full DB Caching wird auf Ebene der Container-Datenbank eingestellt – Alle Pluggable Databases betroffen Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Exkurs: Sogar PL/SQL Objekte "In-Memory" • PL/SQL-Objekte werden bei Nutzung in die SGA (Library Cache) geladen – Packages, Procedures, Functions, Type Bodys, Trigger; nicht für Tabellen und Views – Auch hier LRU Algorithmus – Einfluß auf PL/SQL Performance
• Möglichkeiten, Empfehlungen, Tipps ... – Packages anstelle von einzelnen Procedures und Functions – Library Cache groß genug dimensionieren – Einzelne Objekte/Cursor pinnen mit DBMS_SHARED_POOL.KEEP execute dbms_shared_pool.keep(name=> 'scott.hispackage', flag => 'P'); execute dbms_shared_pool.keep(name=> '4158E358 2329752635', flag=> 'C');
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Result Cache
Database Buffer Default Cache Keep/Recycle Cache LRU-Algorithmus Automatic Big Table Cache
Shared Pool oder Client Transparent für Anwendungen
In-Memory
Exadata X4 Hybrid Columnar Compression Smart Scan Storage Index Smart Flash Cache (Read/Write) Smart Flash Logging (Redo-Write)
Techniken der Flash Cache
Datenbank
Data Buffer Erweiterung 2nd level Cache Auch für Datafiles
Memory optimierende Techniken Compression Partitioning Parallelisierung OLAP
In-Memory Option 12c Row und Column In-Memory Formate auf der selben Tabelle Simultan und transaktionskonsistent Column: Analytics & Reporting Row : OLTP
In-Memory Techniken der TimesTen IMDB
Middle-Tier
TimesTen Application-Tier Database Cache TimesTen for Exalytics
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Coherence Data Grid Object Cache / Data Grid für verteilte Anwendungen
18
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.
Result Cache
Database Buffer Default Cache Keep/Recycle Cache LRU-Algorithmus Automatic Big Table Cache
Shared Pool oder Client Transparent für Anwendungen
In-Memory
Exadata X4 Hybrid Columnar Compression Smart Scan Storage Index Smart Flash Cache (Read/Write) Smart Flash Logging (Redo-Write)
Techniken der Flash Cache
Datenbank
Data Buffer Erweiterung 2nd level Cache Auch für Datafiles
Memory optimierende Techniken Compression Partitioning Parallelisierung OLAP
In-Memory Option 12c Row und Column In-Memory Formate auf der selben Tabelle Simultan und transaktionskonsistent Column: Analytics & Reporting Row : OLTP
In-Memory Techniken der TimesTen IMDB
Middle-Tier
TimesTen Application-Tier Database Cache TimesTen for Exalytics
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Coherence Data Grid Object Cache / Data Grid für verteilte Anwendungen
21
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 RESULT_CACHE_REMOTE_EXPIRATION
5 (%) abh. von O/S MANUAL/FORCE 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.
Query Result Cache: Ein Beispiel SELECT a.department_id "Department", a.num_emp/b.total_count "%_Employees", a.sal_sum/b.total_sal "%_Salary" FROM ( SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id ) a, ( SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees ) b ORDER BY a.department_id;
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.
Result Cache: Query Hints
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Result Cache-Informationen im Ausführungsplan Result Cache Information (identified by operation id): -----------------------------------------------------1 - column-count=3; dependencies=(HR.EMPLOYEES); name="SELECT /*+ result_cache */ a.department_id "Department",a.num_emp/b.total_count "%_Employees", a.sal_sum/b.total_sal "%_S" 5 - column-count=2; dependencies=(HR.EMPLOYEES); attributes=(single-row); name="SELECT /*+ result_cache */ COUNT(*) total_count, SUM(salary) total_sal FROM employees" 9 - column-count=3; dependencies=(HR.EMPLOYEES); name="SELECT /*+ result_cache */ department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
PL/SQL Result Cache • Result Cache für PL/SQL Function Results – Aktiv, wenn PL/SQL-Funktionen von PL/SQL aus aufgerufen werden CREATE OR REPLACE FUNCTION get_datum ( p_id NUMBER, p_format VARCHAR2 ) RETURN VARCHAR2 RESULT_CACHE IS v_datum DATE; BEGIN select hiredate into v_datum from emp where empno = p_id; RETURN TO_CHAR(v_datum, p_format); END;
http://www.oracle.com/global/de/community/tipps/resultcache/index.html Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Result Cache
Database Buffer Default Cache Keep/Recycle Cache LRU-Algorithmus Automatic Big Table Cache
Shared Pool oder Client Transparent für Anwendungen
In-Memory
Exadata X4 Hybrid Columnar Compression Smart Scan Storage Index Smart Flash Cache (Read/Write) Smart Flash Logging (Redo-Write)
Techniken der Flash Cache
Datenbank
Data Buffer Erweiterung 2nd level Cache Auch für Datafiles
Memory optimierende Techniken Compression Partitioning Parallelisierung OLAP
In-Memory Option 12c Row und Column In-Memory Formate auf der selben Tabelle Simultan und transaktionskonsistent Column: Analytics & Reporting Row : OLTP
In-Memory Techniken der TimesTen IMDB
Middle-Tier
TimesTen Application-Tier Database Cache TimesTen for Exalytics
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Coherence Data Grid Object Cache / Data Grid für verteilte Anwendungen
29
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 • Verfügbar auf allen Plattformen • Voraussetzungen SALES
– Datenbank Release 12.1.0.2 – Manuelle Aktivierung – Gebunden an In-Memory Option
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Buffer Cache versus In-Memory Column Store? SELECT column4 FROM mytable; RESULT RESULT
Mit Buffer Cache
Buffer Cache
Mit Column Store
IM Column Store
X X X X X
Row Format
Column Format Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Prinzipielle Unterschiede zum (traditionellen) Buffer Cache • Der Column Store ist statisch und folgt nicht LRU (Least Recently Used) A. • Der Column Store ist geeignet für einheitliche Zugriffe (alle Zeilen einer Tabelle) => typisch für analytische Abfragen – Scannen von vielen Zeilen und Anwendung von Filtern wie z.B. =, <, >, und IN 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 – Abfragen mit Operationen wie min, max und sum, group by, distinct etc.
• Der Buffer Cache hingegen ist vorgesehen für nicht einheitliche Zugriffe (z.B. einige Zeilen einer Tabelle) => typisch für OLTP • Der Column Store ist mit zusätzlichen neuen Optimizerzugriffen und Komprimierungsalgorithmen ausgestattet 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 In-Memory 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 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.
Monitoring von In-Memory Scans im Ausführungsplan • Beispiel: Schlüsselwort INMEMORY FULL SQL> select * from table(dbms_xplan.display_cursor(format=>'BASIC')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------EXPLAINED SQL STATEMENT: -----------------------select count(*) from customers_mem where cust_gender='F' Plan hash value: 1973973873 ----------------------------------------------------| Id | Operation | Name | ----------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS INMEMORY FULL| CUSTOMERS_MEM | -----------------------------------------------------
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Public
In-Memory Heat Map im Enterprise Manager
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Fazit • 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, Plan Baseline, Result Cache etc.
Alles mit minimalem Einfluß auf die Applikation! Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
