Logo Oracle Deutschland   DBA Community  -  August 2012
Pre-Loading von Tabellen in 11g
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

Query Performance ist ein wichtiger Bestandteil der DBA bzw. der Entwickler-Tätigkeiten. Um gute Performance zu erzielen, spielen verschiedene Anforderungen eine gewisse Rolle. Gutes SQL Design, optimale Ausführungspläne, ausreichende Zugriffsstrukturen, aber auch Caching von Segmenten oder von Resultaten sind dabei wichtige Faktoren. Tabellen und Indizes in den Cache zu laden, damit möglichst wenig I/O durchgeführt wird, ist eine häufig anzutreffende Anforderung. Diese Technik nennt man auch Pre-Loading oder Pre-Caching von Datenbank Objekten. Die Durchführung ist dabei sehr einfach. Gleich zu Beginn werden spezielle SQL Statements wie SELECT Statements mit Full Table Scan oder Index Scan durchgeführt, damit die entsprechenden Objekte vollständig in den Cache geladen werden können. Besonders interessant ist dieser Aspekt auch im Zusammenhang mit der Erstellung von Testumgebungen. Falls beispielsweise kein Warmup möglich ist, kann man bevor der eigentliche Test durchgeführt wird, bestimmte Tabellen und Indizes mit dieser Technik vorab in den Buffer Cache laden.

Über das einmalige Laden von Segmenten im Buffer Cache hinaus, kann zur optimierten Cache Speicherung von Segmenten auch die Nutzung von unterschiedlichen Pools im Datenbank Cache in Betracht gezogen werden. Es könnte zum Beispiel sinnvoll sein, Objekte, auf die sehr häufig zugegriffen wird (z.B. Lookup-Tabellen), dem sogenannten KEEP Pool Cache zuzuordnen. Dabei ist der KEEP Cache ein eigener Bereich im Datenbank Cache, der mit dem Parameter DB_KEEP_CACHE_SIZE konfiguriert wird. Standardmässig ist der Wert dabei auf 0 gesetzt. Drei Schritte sind zur Nutzung des KEEP Pools nötig:

  • die Bestimmung der Größe des KEEP Pools und Setzen von DB_KEEP_CACHE_SIZE
  • das Setzen der KEEP Storage Option im Index- und Tabellen-Segment
  • das Laden der Objekte in den Cache mit den entsprechenden SQL-Kommandos
Unabhängig von der Nutzung des KEEP Pools beschreibt der folgende Artikel ein Tabellen Pre-Loading in 11g und gibt Tipps zur Durchführung. Im ersten Schritt wird die Größe des zu ladenden Objekts (hier die Tabelle CUSTOMERS) bestimmt. Dazu ist eine einfache Abfrage auf DBA_SEGMENTS ausreichend.
show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 500M

SELECT blocks, bytes/1024/1024 FROM dba_segments 
WHERE segment_name ='CUSTOMERS' and OWNER='SH';

    BLOCKS BYTES/1024/1024
---------- ---------------
       1536              72
Nun wird eine Full Table Scan Operation auf der Tabelle CUSTOMERS durchgeführt, um die Tabelle zu cachen - zur Sicherheit wird noch ein Hint mitgegeben. Zur Verifizierung schalten wir zusätzlich Autotracing ein und führen folgendes Statement mehrfach durch.
set autotrace on
select /*+ full (cust) */ sum(cust_id) from sh.customers cust;

SUM(CUST_ID)
------------
  1760652750


Execution Plan
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     5 |   270   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 55500 |   270K|   270   (1)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1473  consistent gets
       1468  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
Es wird zwar ein Full Table Scan durchgeführt allerdings mit 1468 physical reads. Zur Sicherheit überprüfen wir das Verhalten über V$BH. Zur Erinnerung: V$BH listet diejenigen Blöcke mit DATA_OBJECT_ID auf, die im Moment im Buffer Cache sind. In unserem Fall liegen genau 3 Blöcke im Cache (siehe Spalte NUMBER_OF_BLOCKS).
set trimspool on
set linesize 180
COL object_name FORMAT A30
COL object_type FORMAT A15
COL format FORMAT A15
COL number_of_blocks FORMAT 999999999999
col owner FORMAT a25
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
Wie erklärt sich dieses Verhalten? Bei den Full Table Scan (FTS) Operationen muss seit 11g berücksichtigt werden, dass ein optimiertes Verfahren verwendet wird, um Objekte in den Cache zu laden. Falls die Größe der Tabelle 2% des Buffer Cache übersteigt - was in unserem Beispiel der Fall ist - wird über direct load gelesen und nicht über den Buffer Cache. Möchte man das Laden über den Buffer Cache erzwingen, ist der Einsatz des Underscore Parameters "_small_table_threshold" notwendig, der innerhalb einer Session aktiviert werden kann. Um ein Beispiel zu geben: Der Default in meiner Umgebung lag bei 992 Blöcken.

Folgender Ausschnitt aus MOS Note Doc ID 787373.1 beschreibt das Verhalten:
"When loading objects and data into the buffer cache of the instance there is a functionality which determines the best way to load for full scans. This functionality is called smallness logic and is driven by a hidden parameter called: _small_table_threshold. If the number of blocks to be read is lower than or equal to the setting of the parameter Oracle will load the object via the buffer cache as this is more efficient than doing a direct read operation. However, the parameter does not need to be set explicitly as there is also a dependency function calculating the cut over threshold if the parameter is unset. This calculation is roughly 2% of the buffer cache size to be used as the cut over threshold."

Im Folgenden wird der Parameter "_small_table_threshold" mit ALTER SESSION auf 2000 Blöcke gesetzt und das Statement noch einmal ausgeführt.
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
set autotrace on
select /*+ full (cust) */ sum(cust_id) from sh.customers cust;
SUM(CUST_ID)
------------
  1760652750

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1475  consistent gets
          0  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
Offensichtlich sind keine physical reads mehr erforderlich. Nun erfolgt erneut die Abfrage auf V$BH.
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

OBJECT_NAME                    OBJECT_TYPE     OWNER           NUMBER_OF_BLOCKS
------------------------------ --------------- --------------- ----------------
CUSTOMERS                      TABLE           SH                         1,471
Fast alle Blöcke sind nun offensichtlich im Cache (siehe Spalte NUMBER_OF_BLOCKS). Die fehlenden Blöcke sind wahrscheinlich header blocks bzw. unused blocks. Man könnte eine weitere Überprüfung mit DBMS_SPACE.SPACE_USAGE anschliessen, um genau zu untersuchen, welche Blöcke nicht genutzt werden. Um die Artikellänge nicht zu sprengen, werden wir dieses Thema in einem gesonderten Artikel beschreiben.

Möchte man ein Skript verwenden, mit dem man auch die Subobjekte und den prozentualen Anteil sehen kann, kann man folgende Abfrage (Autor Bernhard Atzenberger) nutzen.
set trimspool on
set linesize 180
col segment_name format a30
col segment_type format a20
col object_name format a30
col subobject_name format a30
col object_type format a20
col owner format a25
col blocks format 9999999999999
col blocks_in_cache format 99999999999999

select x.owner,x.object_name,x.subobject_name,x.object_type
,x.blocks_in_cache,s.blocks
, round(x.blocks_in_cache/s.blocks*100) pct_in_cache
from
(
 SELECT o.object_name,o.SUBOBJECT_NAME,o.object_type,o.owner,count(*) blocks_in_cache
 FROM dba_objects o,x$bh x
 WHERE x.obj=o.DATA_OBJECT_ID
 AND o.owner like 'SH'
 group by o.object_name,o.subobject_name,o.object_type,o.owner
 ) x, dba_segments s
 where x.owner=s.owner and x.object_name=s.segment_name
 and  (x.subobject_name is null or x.subobject_name=s.partition_name)
 order by x.owner,x.object_name,x.subobject_name;


OWNER           OBJECT_NAME                    SUBOBJECT_NAME
--------------- ------------------------------ ------------------------------
OBJECT_TYPE     BLOCKS_IN_CACHE     BLOCKS PCT_IN_CACHE
--------------- --------------- ---------- ------------
SH              CUSTOMERS
TABLE                      1471       1536           96


Im Anschluss noch ein paar Links zm Thema ...
Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...



Zurück zur Community-Seite