Oracle 11g RESULT_CACHE Hint : Faster Performance Queries, Advantages, Benefits

RESULT CACHE: Prior to oracle 11g, database stated with caching the data block in memory and which used to construct the result set for user queries. Even though the data blocks are cached in SGA, it needs to be reconstructed for the query output which takes time and resource.

In Oracle 11g, Oracle has gone upto next level of memory caching. Oracle can store the reset sets of both SQL & PL/SQL.  Using this feature, the database engine need not to re-execute the query to serve the similar query. It improves the performance lot for the repetitive queries.

Here are the parameters related to the result cache.

SQL> show parameter result_cache_m

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result              integer     5
result_cache_max_size                big integer 8M
result_cache_mode                    string      MANUAL

Let us brief about the parameters

RESULT_CACHE_MODE: RESULT_CACHE_MODE specifies when a Result Cache operator is spliced into a query's execution plan. If set to FORCE, all the queries are cached if they are qualified and fit in cache. The default is MANUAL, which indicates that only queries with the hint will be cached. 

RESULT_CACHE_MAX_SIZE: Specified maximum size of the result cache. Remember, result cache is part of shared pool and maximum can be of 75% of shared pool size. Also query result.

RESULT_CACHE_MAX_RESULT: Specifies percentage of RESULT_CACHE_MAX_SIZE that any single query result set can occupy. (Just to prevent the big result set from throwing out all other small result set).

Please find the example below.

Creating the table with necessary rows.
21:26:13 SQL> create table test as select * from dba_segments;

Table created.

Elapsed: 00:00:03.74
21:26:36 SQL> select count(*) from dba_segments;

  COUNT(*)
----------
     17594

Elapsed: 00:00:01.82
21:26:46 SQL> insert into test (select * from test);

17593 rows created.

Elapsed: 00:00:00.41
21:26:58 SQL> /

35186 rows created.

Elapsed: 00:00:00.66
21:27:02 SQL> /

70372 rows created.

Elapsed: 00:00:01.34
21:27:04 SQL> /

140744 rows created.

Elapsed: 00:00:02.62
21:27:08 SQL> /

281488 rows created.

Elapsed: 00:00:05.34
21:27:14 SQL> /

562976 rows created.

Elapsed: 00:00:11.61
21:27:26 SQL> select bytes/1024/1024 from user_segments where segment_name ='TEST';

BYTES/1024/1024
---------------
            152

Elapsed: 00:00:00.10
21:27:39 SQL> insert into test (select * from test);

1125952 rows created.

Elapsed: 00:00:22.37

21:28:08 SQL> select bytes/1024/1024 from user_segments where segment_name ='TEST';

BYTES/1024/1024
---------------
            304 == Now the table has 304 MB data.

21:29:26 SQL> select segment_type, count(*) from test group by segment_type;

SEGMENT_TYPE         COUNT(*)
------------------ ----------
CLUSTER                  1280
INDEX PARTITION        235392
NESTED TABLE             2560
LOBINDEX               101248
TABLE PARTITION        168704
TYPE2 UNDO               6784
LOB PARTITION            7296
LOBSEGMENT             101248
INDEX                  897408
TABLE                  729856
ROLLBACK                  128

11 rows selected.

Elapsed: 00:00:07.33
SQL> show parameter result_cache_m

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result              integer     5
result_cache_max_size                big integer 8M
result_cache_mode                    string      MANUAL

21:35:57 SQL> show parameter RESULT_CACHE_MODE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      MANUAL

How to change the RESULT CACHE MODE?
It can be session/system level. These are the syntax for the mode change

ALTER SYSTEM SET RESULT_CACHE_MODE =FORCE;
ALTER SESSION SET RESULT_CACHE_MODE =FORCE;

21:36:25 SQL> ALTER SESSION SET RESULT_CACHE_MODE =FORCE;

Session altered.

Elapsed: 00:00:00.00
21:37:14 SQL>  show parameter RESULT_CACHE_MODE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      FORCE

Oracle 11g R2 has new command for table wise result_cache mode change.

SQL> alter table sales result_cache (mode force);
Table altered.

Now, Let us try with RESULT_CACHE hint. The first execution will take same amount of time similar to the execution with no hint.

  1* select /*+ RESULT_CACHE */ segment_type, count(*) from test group by segment_type
21:31:01 SQL> /

SEGMENT_TYPE         COUNT(*)
------------------ ----------
CLUSTER                  1280
INDEX PARTITION        235392
NESTED TABLE             2560
LOBINDEX               101248
TABLE PARTITION        168704
TYPE2 UNDO               6784
LOB PARTITION            7296
LOBSEGMENT             101248
INDEX                  897408
TABLE                  729856
ROLLBACK                  128

11 rows selected.

Elapsed: 00:00:07.12

In the subsequent execution the result comes quickly. See below.

  1*  select /*+ RESULT_CACHE */ segment_type, count(*) from test group by segment_type
21:50:34 SQL> /

SEGMENT_TYPE         COUNT(*)
------------------ ----------
LOBSEGMENT             101248
CLUSTER                  1280
LOB PARTITION            7296
TYPE2 UNDO               6784
LOBINDEX               101248
TABLE                  729855
INDEX PARTITION        235392
INDEX                  897408
ROLLBACK                  128
TABLE PARTITION        168704
NESTED TABLE             2560

11 rows selected.

Elapsed: 00:00:00.02
Now, Let us try to delete one row from the test table and see the execution time. Again the very next execution will take time to load the blocks into cache.

21:59:31 SQL> delete from test where rownum<2;

1 row deleted.

Elapsed: 00:00:00.00
21:59:41 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
21:59:44 SQL> select /*+ RESULT_CACHE */ segment_type, count(*) from test group by segment_type;

SEGMENT_TYPE         COUNT(*)
------------------ ----------
LOBSEGMENT             101248
CLUSTER                  1280
TYPE2 UNDO               6784
LOBINDEX               101248
INDEX PARTITION        235392
INDEX                  897407
LOB PARTITION            7296
TABLE                  729855
ROLLBACK                  128
TABLE PARTITION        168704
NESTED TABLE             2560

11 rows selected.

Elapsed: 00:00:06.39
21:59:57 SQL> /

SEGMENT_TYPE         COUNT(*)
------------------ ----------
LOBSEGMENT             101248
CLUSTER                  1280
TYPE2 UNDO               6784
LOBINDEX               101248
INDEX PARTITION        235392
INDEX                  897407
LOB PARTITION            7296
TABLE                  729855
ROLLBACK                  128
TABLE PARTITION        168704
NESTED TABLE             2560

11 rows selected.

Elapsed: 00:00:00.00

22:48:55 SQL> set autotrace traceonly explain
22:49:10 SQL> /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2408827628

-------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    99 |  9453  (11)| 00:01:54 |
|   1 |  RESULT CACHE       | cxw6xc0a3vcd7100s1q1a00bu1 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    99 |  9453  (11)| 00:01:54 |
|   3 |    TABLE ACCESS FULL| TEST                       |  2251K|    19M|  8718   (3)| 00:01:45 |
-------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(SCOTT.TEST); parameters=(nls); name="select /*+ RESULT_CACHE */ segment_type, coun
t(*) from test group by segment_type"

Now let us check the difference in the execution statistics. You can see the difference in consistent gets and physical reads values in the following results.

22:49:13 SQL> set autotrace off
22:52:04 SQL> delete from test where rownum<2;

1 row deleted.

Elapsed: 00:00:00.00
22:52:28 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
22:52:31 SQL> set autotrace traceonly statistics;
22:52:40 SQL> select /*+ RESULT_CACHE */ segment_type, count(*) from test group by segment_type;

11 rows selected.

Elapsed: 00:00:08.56

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      38670  consistent gets
      29272  physical reads
          0  redo size
        855  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

22:52:58 SQL> /

11 rows selected.

Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        855  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

How to monitor the result cache using dynamic views?

v$result_cache_objects is the view to moitor the result cahce usage.

22:58:38 SQL>  desc v$result_cache_objects
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER
 TYPE                                   VARCHAR2(10)
 STATUS                                 VARCHAR2(9)
 BUCKET_NO                              NUMBER
 HASH                                   NUMBER
 NAME                                   VARCHAR2(128)
 NAMESPACE                              VARCHAR2(5)
 CREATION_TIMESTAMP                     DATE
 CREATOR_UID                            NUMBER
 DEPEND_COUNT                           NUMBER
 BLOCK_COUNT                            NUMBER
 SCN                                    NUMBER
 COLUMN_COUNT                           NUMBER
 PIN_COUNT                              NUMBER
 SCAN_COUNT                             NUMBER
 ROW_COUNT                              NUMBER
 ROW_SIZE_MAX                           NUMBER
 ROW_SIZE_MIN                           NUMBER
 ROW_SIZE_AVG                           NUMBER
 BUILD_TIME                             NUMBER
 LRU_NUMBER                             NUMBER
 OBJECT_NO                              NUMBER
 INVALIDATIONS                          NUMBER
 SPACE_OVERHEAD                         NUMBER
 SPACE_UNUSED                           NUMBER
 CACHE_ID                               VARCHAR2(93)
 CACHE_KEY                              VARCHAR2(93)

23:02:01 SQL> l
  1  select id, name from v$result_cache_objects
  2* order by creation_timestamp desc
23:02:06 SQL> /

        ID NAME
---------- --------------------------------------------------
         6 select /*+ RESULT_CACHE */ segment_type, count(*)
           from test group by segment_type

         5 SELECT DECODE('A','A','1','2') FROM DUAL
         4 select /*+ RESULT_CACHE */ segment_type, count(*)
           from test group by segment_type

         3 select /*+ RESULT_CACHE */ segment_type, count(*)
           from test group by segment_type

         2 select segment_type, count(*) from test group by s
           egment_type

         0 SCOTT.TEST
         1 select /*+RESULT_CACHE */ segment_type, count(*) f
           rom test where SEGMENT_TYPE='ROLLBACK' group by se
           gment_type


7 rows selected.

How to monitor SQL result cache Statistics?
V$result_cache_statistics is the dynamic view to see the statistics of the sql result cache.

23:03:02 SQL> desc v$result_cache_statistics
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(128)
 VALUE                                              NUMBER

23:02:07 SQL> select name, value from v$result_cache_statistics order by id;

NAME                                                    VALUE
-------------------------------------------------- ----------
Block Size (Bytes)                                       1024
Block Count Maximum                                      8192
Block Count Current                                        32
Result Size Maximum (Blocks)                              409
Create Count Success                                        6
Create Count Failure                                        0
Find Count                                                 13
Invalidation Count                                          4
Delete Count Invalid                                        0
Delete Count Valid                                          0

10 rows selected.

How to pull Result Cache Memory report?
Using the DBMS_RESULT_CACHE package you can pull the report.

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jul 1 05:51:39 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on size 99999
SQL> exec  DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE")
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  = 8M bytes (8K blocks)
Maximum Result Size = 409K bytes (409 blocks)
[Memory]
Total Memory = 169352 bytes [0.019% of the Shared Pool]
... Fixed Memory = 5296 bytes [0.001% of the Shared Pool]
....... Cache Mgr  = 152 bytes
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 164056 bytes [0.018% of the Shared Pool]
....... Overhead = 131288 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 28888 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 25 blocks
........... Used Memory = 7 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 6 blocks
................... SQL     = 2 blocks (2 count)
................... Invalid = 4 blocks (4 count)

PL/SQL procedure successfully completed.

How to flush Result Cache?
Using DBMS_RESULT_CACHE.flush package you can flush the result cache. See the example below with execution time.

SQL> select /*+ RESULT_CACHE */ segment_type, count(*) from test group by segment_type;

SEGMENT_TYPE         COUNT(*)
------------------ ----------
LOBSEGMENT             101248
CLUSTER                  1279
TYPE2 UNDO               6784
LOBINDEX               101248
INDEX PARTITION        235392
INDEX                  897406
LOB PARTITION            7296
TABLE                  729855
ROLLBACK                  128
TABLE PARTITION        168704
NESTED TABLE             2560

11 rows selected.

Elapsed: 00:00:10.00 == It took 10 seconds
SQL> /

SEGMENT_TYPE         COUNT(*)
------------------ ----------
LOBSEGMENT             101248
CLUSTER                  1279
TYPE2 UNDO               6784
LOBINDEX               101248
INDEX PARTITION        235392
INDEX                  897406
LOB PARTITION            7296
TABLE                  729855
ROLLBACK                  128
TABLE PARTITION        168704
NESTED TABLE             2560

11 rows selected.

Elapsed: 00:00:00.01 == It took only .01 second to execute
SQL> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01 == Result cache flush performed
SQL> select /*+ RESULT_CACHE */ segment_type, count(*) from test group by segment_type;

SEGMENT_TYPE         COUNT(*)
------------------ ----------
LOBSEGMENT             101248
CLUSTER                  1279
TYPE2 UNDO               6784
LOBINDEX               101248
INDEX PARTITION        235392
INDEX                  897406
LOB PARTITION            7296
TABLE                  729855
ROLLBACK                  128
TABLE PARTITION        168704
NESTED TABLE             2560

11 rows selected.

Elapsed: 00:00:06.45 == Again execution time came upto 6 seconds

What is the background process running for Result Cache?

RCBG is the oracle background process to monitor the result Cache.

SQL> select *  from V$BGPROCESS where name like 'RC%';

PADDR              PSERIAL# NAME
---------------- ---------- -----
DESCRIPTION                                                           ERROR
---------------------------------------------------------------- ----------
00                        0 RCBG
Result Cache: Background                                         ##########
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google