advertisements
_____________________________________________________________________________________________________________________
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 ##########
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment