1 / 38

Result Cache Internals

Result Cache Internals. Julian Dyke Independent Consultant. Web Version - November 2007. juliandyke.com. © 2007 Julian Dyke. Introduction. Investigating Oracle Introduction. This presentation describes the Server-side Result Cache which was introduced in Oracle 11.1

lavi
Download Presentation

Result Cache Internals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Result CacheInternals Julian Dyke Independent Consultant Web Version - November 2007 juliandyke.com ©2007 Julian Dyke

  2. Introduction

  3. Investigating OracleIntroduction • This presentation describes the Server-side Result Cache which was introduced in Oracle 11.1 • Tests performed on Oracle 11.1.0.6.0 on Linux 32-bit

  4. Result CacheIntroduction • Introduced in Oracle 11.1 • Allows results of a query to be cached in SGA • When subsequent queries execute requiring the same result set, results are retrieved from shared pool • Potentially reduces • amount of physical I/O • amount of logical I/O • number of sorts • amount of CPU

  5. Result CacheResult Cache Types • In Oracle 11.1 there are two types of result cache • Server • uses shared memory (SGA) • available to all sessions • Client • uses client memory • requires OCI • Server result cache includes: • SQL query result cache • PL/SQL function result cache • PL/SQL client-side result cache exists in Oracle 10.2 • DETERMINISTIC functions only

  6. Result CacheSQL Query Result Cache • For query results to be reusable: • Read consistent query used to build result set must retrieve most current committed state of data • or • Query must point to an explicit point in time using flashback query • Cached results become invalid when data in underlying tables is modified • Includes ongoing transactions • In RAC, result cache is instance specific • Can be different size in each instance

  7. Result CacheRESULT_CACHE_MODE Parameter • Can be • AUTO • MANUAL • FORCE • MANUAL (default) • Results are only cached if query includes RESULT_CACHE hint • FORCE • Results are cached unless query includesNO_RESULT_CACHE hint

  8. Result CacheRestrictions • SQL query result sets cannot include rows from • dictionary tables • temporary tables • sequence CURRVAL and NEXTVAL pseudocolumns • SQL functions • CURRENT_DATE • CURRENT_TIMESTAMP • LOCAL_TIMESTAMP • SYS_CONTEXT (with non-constant variables) • SYS_GUID • SYSDATE • SYSTIMESTAMP • USERENV (with non-constant variables) • Non-deterministic PL/SQL functions

  9. Result CacheParameterization • Cached results are parameterized in the result cache if they include • Bind variables • SQL functions • DBTIMEZONE • SESSIONTIMEZONE • SYS_CONTEXT (with constant variables) • UID • USER • USERENV (with constant variables) • NLS parameters • Parameterized results can be reused if: • query is equivalent • parameter values are similar

  10. Result CacheSubqueries versus Views • Subquery result sets cannot be cached • Views and inline views can be cached • Adding RESULT_CACHE hint to an inline view disables optimizations between outer query and inline view to maximize reusability of cached result

  11. SQL Query Result Cache Example Statement • Example query SELECT /*+ RESULT_CACHE */ t.team_name, SUM (c.team_points) FROM car c, team tWHERE c.team_key = t.team_keyAND c.season_key = '2006'GROUP BY t.team_nameORDER BY SUM (c.team_points) DESC;

  12. SQL Query Result Cache Example Execution Plan 0 SELECT STATEMENT1 0 RESULT CACHE dbzqh4r21zn0wc8zwfcvg00sqw2 1 SORT (ORDER BY)3 2 HASH (GROUP BY)4 3 MERGE JOIN5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TEAM'6 5 INDEX (FULL SCAN) OF 'TEAM_PK'7 4 SORT (JOIN)8 7 TABLE ACCESS (BY INDEX ROWID) OF 'CAR'9 8 INDEX (RANGE SCAN) OF 'CAR_PK' Result Cache Information (identified by operation id): 1 - column count=2; dependencies=(GP.CAR, GP.TEAM); parameters=(nls); name = "SELECT /*+ RESULT_CACHE */ t.team_name, SUM (c.team_points) FROM car c, team tWHERE c.team_key = t.team_keyAND c.season_key = "

  13. SQL Query Result Cache Sample Auto Trace Output • Without result cache SET AUTOTRACE ON Statistics 0 recursive calls 0 db block gets 55 consistent gets 0 physical reads 0 redo sizeetc... • With result cache Statistics 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo sizeetc...

  14. PL/SQL Function Result CacheExample Function • The function declaration must include the RESULT_CACHE clause • The RELIES_ON clause is optional, but recommended CREATE OR REPLACE FUNCTION get_team_name (p_team_key NUMBER)RETURN VARCHAR2RESULT_CACHE RELIES_ON (team)IS l_team_name VARCHAR2(50);BEGIN SELECT team_name INTO l_team_name FROM team WHERE team_key = p_team_key; RETURN l_team_name;END;/ SELECT get_team_name ('FER') FROM dual;

  15. Result CacheDBMS_RESULT_CACHE Package • Supplied PL/SQL package which contains subroutines to manage result cache including: • BYPASS Specifies that all subsequent statements will bypass the result cache. Required when hot patching PL/SQL • FLUSH Specifies that all objects will be flushed from cache. Can also release memory and/or reset statistics • INVALIDATE Invalidates all result sets depending on specified object • INVALIDATE_OBJECT Invalidates specified result set • MEMORY_REPORT Prints summary or detailed memory report for result cache. Requires SERVEROUTPUT ON • STATUS Returns current status of result cache

  16. Result CacheDBMS_RESULT_CACHE Package • To print a summary memory report use SET SERVEROUTPUT ONEXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT; R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 2080K bytes (2080 blocks) Maximum Result Size = 104K bytes (104 blocks) [Memory] Total Memory = 103528 bytes [0.048% of the Shared Pool] ... Fixed Memory = 5132 bytes [0.002% of the Shared Pool] ... Dynamic Memory = 98396 bytes [0.046% of the Shared Pool] ....... Overhead = 65628 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 7 blocks ........... Used Memory = 25 blocks ............... Dependencies = 6 blocks (6 count) ............... Results = 19 blocks ................... SQL = 19 blocks (7 count)

  17. Result CacheDBMS_RESULT_CACHE Package • To print a detailed memory report use SET SERVEROUTPUT ONEXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT (TRUE); • Includes more detail for: • Fixed Memory • Dynamic Memory ... Fixed Memory = 5132 bytes [0.002% of the Shared Pool] ....... Cache Mgr = 108 bytes ....... Memory Mgr = 124 bytes ....... Bloom Fltr = 2K bytes ....... State Objs = 2852 bytes ... Dynamic Memory = 98396 bytes [0.046% of the Shared Pool] ....... Overhead = 65628 bytes ........... Hash Table = 32K bytes (4K buckets) ........... Chunk Ptrs = 12K bytes (3K slots) ........... Chunk Maps = 12K bytes ........... Miscellaneous = 8284 bytes

  18. Result CacheSupported Parameters • result_cache_max_result • maximum result size as percent of cache size • defaults to 5% • result_cache_max_size • maximum amount of memory to be used by the cache • set to 0 to disable result cache • result_cache_mode • result cache operator usage mode • can be AUTO, MANUAL, FORCE • result_cache_remote_expiration • maximum life time (min) for any result using a remote object • defaults to 0

  19. Result CacheUnsupported Parameters • _result_cache_auto_execution_threshold • result cache auto execution threshold • _result_cache_auto_size_threshold • result cache auto max size allowed • _result_cache_auto_time_distance • result cache auto time distance • _result_cache_auto_time_threshold • result cache auto time threshold • _result_cache_block_size • result cache block size • defaults to 1024 bytes • _result_cache_timeout • maximum time (sec) a session waits for a result • defaults to 60 seconds

  20. Result Cache Dynamic Performance Views • V$RESULT_CACHE_STATISTICS • Based on X$QESRCSTA • One row for each statistic • V$RESULT_CACHE_OBJECTS • Based on X$QESRCOBJ • One row for each object (result set or dependency object) in result cache • V$RESULT_CACHE_MEMORY • Based on X$QESRCMEM • One row for each block in result cache • V$RESULT_CACHE_DEPENDENCY • Based on X$QESRCDEP • One row for each dependency between a result set and a data dictionary object • Each V$ view has an equivalent GV$ view

  21. Result CacheStatistics • Reported in V$RESULT_CACHE_STATISTICS • In Oracle 11.1 reported statistics include: • Block Size (Bytes) • Block Count Maximum • Block Count Current • Result Size Maximum (Blocks) • Create Count Success • Create Count Failure • Find Count • Invalidation Count • Delete Count Invalid • Delete Count Valid

  22. Result CacheBloom Filter • Invented by Burton H Bloom in 1970 • Space-efficient probabilistic data structure that is used to test whether an element is a member of a set. • False positives are possible, but false negatives are not. • Elements can be added to the set, but not removed • The more elements that are added to the set, the larger the probability of false positives • Probably used by Oracle to check if dependency objects already exist in result cache

  23. gkeo4j3k Result CacheBloom Filter • Use a contiguous array of bits in memory • Initially all bits set to zero • Hash function applied to incoming keys determines location of bit in array • If bit is already set then bloom filter returns true • If bit is not set then bloom filter sets bit and returns false 0 0 0 0 1 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 1 0

  24. Result CacheGlobal Area (Fixed SGA) Dump ALTER SESSION SET EVENTS 'immediate trace name global_area level 2' • Search dump file for qesrc ksllt qesrcLca_ [20019A3C, 20019AA0) = .... // Latch - Result Cache: Latch ksllt qesrcLso_ [20019AA0, 20019B04) = .... // Latch - Result Cache: SO Latch ub4 qesrceq_ [20019B04, 20019B08) = 0000007B // Enqueue - RC - Result Cache: Enqueue ub4 qesrceq1_ [20019B08, 20019B0C) = 00000090// sword qesrceq1_e_ [20019B0C, 20019B10) = 00000113// Wait Event - enq: RC - Result Cache: Contention word qesrcsot_ [20019B10, 20019B14) = 0000005A// qesrcSGAt qescrSGA_ [20019B14, 20019B18) = 36FC1EE0// Address of Result Cache SGA Memory area ksdbp qesrcbg_ [20019B18, 20019B50) = ........// Background Process - RCBG CacheManager

  25. Result Cache Heap Dump ALTER SESSION SET EVENTS 'immediate trace name heapdump level 2' • Search dump file for Result Cache Chunk 2c119910 sz= 32816 freeable "Result Cache" ds=0x36fc1e78....Chunk 2c1254a4 sz= 32816 freeable "Result Cache" ds=0x36fc1e78....Chunk 31db6448 sz= 32816 freeable "Result Cache" ds=0x36fc1e78Chunk 31dbe478 sz= 32816 freeable "Result Cache" ds=0x36fc1e78Chunk 31dc64b8 sz= 32816 recreate "Result Cache" latch=(nil) ds 36fc1e78 sz= 164080 ct= 5 2c119910 sz= 32816 2c1254a4 sz= 32816 31db6448 sz= 32816 31dbe478 sz= 32816 • In the above example the Result Cache data segment location is 0x36fc1e78

  26. Result Cache Subheap Dump • Result Cache sub heap can be dumped using: ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2, addr 0x36fc1e78'; • For example: HEAP DUMP heap name="Result Cache" desc=0x36fc1e78 extent sz=0x8024 alt=32767 het=32767 rec=0 flg=2 opc=2 parent=0x200010b4 owner=(nil) nex=(nil) xsz=0x8024 heap=(nil) fl2=0x20, nex=(nil)EXTENT 0 addr=0x31db6448 Chunk 31db6450 sz= 32796 perm "perm " alo=32784Dump of memory from 0x31DB6450 to 0x31DBE46C31DB6450 5000801D 00000000 31DBE480 00008010 [...P.......1....]31DB6460 00000000 31DB6460 00000000 00000002 [....`d.1........]31DB6470 31DBFF40 31DBFF40 36FC1F38 36FC1F38 [@..1@..18..68..6]31DB6480 00000002 76C9A356 AD55D9D8 0015838E [....V..v..U.....]31DB6490 00000000 00000000 00000053 11096B78 [........S...xk..]31DB64A0 000B1711 0001132E 00000000 00070000 [................]31DB64B0 00000001 31DB6860 00000000 00000000 [....`h.1........]31DB64C0 00000000 00000000 00000000 00000000 [................] Repeat 57 timesetc...

  27. Result CacheEvent 43905 Level 1 SQL> ALTER SESSION SET EVENTS '43905 trace name context forever, level 1'; Top level query block Objects for this node : 70428 70436 ------------------------------------- Query[len=191]: SELECT /*+ RESULT_CACHE */ d.driver_key,d.driver_name,SUM(c.driver_points)FROM driver d, car cWHERE d.driver_key = c.driver_keyAND c.season_key = '2005'GROUP BY d.driver_key,d.driver_name Normalized Query[len=304]: 172 1?"D" 226 1?"DRIVER_KEY" 219 1?"D" 226 1?"DRIVER_NAME" 219 1?"SUM" 225 1?"C" 226 1?"DRIVER_POINTS" 229 70 1?"DRIVER" 1?"D" 219 1?"CAR" 1?"C" 213 1?"D" 226 1?"DRIVER_KEY" 221 1?"C" 226 1?"DRIVER_KEY" 8 1?"C" 226 1?"SEASON_KEY" 221 3&'2005' 75 18 1?"D" 226 1?"DRIVER_KEY" 219 1?"D" 226 1?"DRIVER_NAME" Cache id1: cg920w9wu3z462qp8dxbbudprq Cache id2: cg920w9wu3z462qp8dxbbudprq Column count: 3 NLS Dependent: YES User Referenced: NO Ordered: NO Auto: NODependencies: (70428 - GP.CAR) (70436 - GP.DRIVER)

  28. ResultSet ResultSet ResultSet ResultSet ResultSet Dependency Object Dependency Object Dependency Object Dependency Object Result CacheResult Sets versus Dependency Objects Table Table Function Table

  29. Extent 0 Result Sets / Dependencies Result Sets / Dependencies Extent 1 Hash Table Hash Table Extent 2 Chunk PointersChunk Maps Chunk PointersChunk Maps Result Sets / Dependencies Result Sets / Dependencies Result CachePhysical Structure ResultCache:SubheapHeader ResultCache:CacheManager ResultCache:MemoryManager Extent 3 Extent 4

  30. Dependency 0 Dependency 1 Dependency 2 Result Cache 0 Result Cache 1 Result Cache 2 Overflow Overflow Overflow Result CacheLogical Structure V$RESULT_CACHE_OBJECTS ResultCacheManager

  31. Result Set 0 Result Set 2 Result Set 1 Dependency 0 Dependency 2 Result CacheHash Chains Result Cache Hash Chain Headers There are 8192 hash buckets Each bucket contains a double linked list Most buckets will be empty Collisions will be extremely rare

  32. Result CacheDependency Objects Block is 1024 bytes Dependency Header Header includes object_id Dependent ResultsAddress Array Dependent Results Address Array is 132 bytes 1 x 4 bytes - bitmap32 x 4 bytes - addresses of dependent result sets Dependent ResultsAddress Array Up to 7 address arrays (minimum 1) Unused space

  33. Result CacheResult Sets Block is 1024 bytes • Header includes • number of rows • number of columns • number of dependencies • length of name Result Set Header Dependency Array • Dependency array = 8 bytes per row • 1 x 4 bytes - object ID • 1 x 4 bytes - address of dependency Object Name Object name = SQL statement text Results (rows) Results = row data Unused space

  34. Result CacheResults - Example SELECT team_key, team_name FROM team; TEAM_KEYTEAM_NAME FER FERRARIHON HONDA TOY TOYOTA 00 03 F E R 00 07 F E R R A R I 00 00 03 H O N 00 05 H O N D A 00 00 03 T O Y 00 06 T O Y O T A 00 Each row is terminated by a zero byte Each field has a 2-byte length indicator Number of rows and number of columns defined in result set header

  35. 16 6B 2B C1 Result CacheResults - More Examples Numbers - represented using Oracle internal format e.g 42 decimal 00 02 Dates - represented using an internal format e.g: 22nd October 2007 00 07 78 0A 01 01 01 NULLs - represented by 2 byte length indicator 00 00 No compression of NULL values No compression of CHAR values

  36. Result CacheResult Set Block Overflow • Both name and results can overflow. For example Result Set Header ObjectName Result Set Header Dependency Array Dependency Array Results Results ObjectName ObjectName Results • Number of overflow blocks is theoretically unlimited

  37. Result CacheConclusions • Server side result cache has potential to eliminate repeated logical I/Os • Improved response times • Lower CPU • Potentially lower physical I/O • Consider modifying code to use [in-line] views instead of sub queries • Some questions over scalability • Locking algorithm appears to have been improved between 11.1 beta and 11.1 production • V$LOCK_TYPE parameters for RC enqueue incorrect in 11.1 production • Impact of using result cache can easily be assessed when using RESULT_CACHE hint (MANUAL mode) • Impact of FORCE mode much harder to assess

  38. Thank you for your interest info@juliandyke.com

More Related