Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Sunday, June 19, 2011

Oracle 11g New Feature "SQL Result Cache"

I want to demonstrate one of the new features of Oracle 11g called "SQL Result Cache" which will directly effect the database performance .

I am going to use the same sample schemas that I created in my previous blog post.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 13:13:02 2011

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


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

SQL> alter user u0001 quota 2G on users;

User altered.

SQL> conn u0001/u0001
Connected.
SQL> create table t0002 as select * from all_objects;

Table created.

SQL> select count(*) from t0002;

COUNT(*)
----------
56100

Populate the records in table t0002
SQL> begin
for c in (select rownum from t0002 where rownum <= 5) loop
    insert into t0002 select * from t0002;
    commit;
  end loop;
end;
/  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SQL> select count(*) from t0002;

COUNT(*)
----------
1795200

SQL> 

Run a GROUP BY query on this table
SQL> set timing on
SQL> select object_type, count(*) from t0002 group by object_type order by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:03.11
SQL> 

Query took 03.11 seconds
Now run the query so that results of the query will be cached in "SQL Result Cache", we accomplish this by using the "/*+ RESULT_CACHE */" hint in the query.

SQL> select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:02.87
SQL>

First run of the query took 02.87 seconds

SQL> r
1* select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:00.05
SQL> 

Second run of the query took only 00.05 seconds because the query results has been fetched from "SQL Result Cache" in database server memory without going to disk to execute the SQL.

We can also see from the explain plan that the results of the query is read from SQL Result Cache.
SQL> set autotrace traceonly explain
SQL> r
1* select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4234960643

--------------------------------------------------------------------------------
------------------

| Id  | Operation           | Name                       | Rows  | Bytes | Cost
(%CPU)| Time     |

--------------------------------------------------------------------------------
------------------

|   0 | SELECT STATEMENT    |                            |    57M|   603M|  9677
(26)| 00:01:57 |

|   1 |  RESULT CACHE       | 44uuyvrukrh7w7w2m48aqb12tk |       |       |
|          |

|   2 |   SORT GROUP BY     |                            |    57M|   603M|  9677
(26)| 00:01:57 |

|   3 |    TABLE ACCESS FULL| T0002                      |    57M|   603M|  7612
(6)| 00:01:32 |

--------------------------------------------------------------------------------
------------------


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

1 - column-count=2; dependencies=(U0001.T0002); parameters=(nls); name="selec
t /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type orde
r by object_type"


Note
-----
- dynamic sampling used for this statement (level=2)

SQL> 

We can also compare the SQL statisticts and see that SQL with RESULT_CAHCHE hint has no "consistent gets" or "physical reads".
SQL> set autotrace traceonly statistics;
SQL> select object_type, count(*) from t0002 group by object_type order by object_type;

23 rows selected.

Elapsed: 00:00:01.83

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
37542  consistent gets
25909  physical reads
0  redo size
1041  bytes sent via SQL*Net to client
430  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
23  rows processed

SQL> select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type;

23 rows selected.

Elapsed: 00:00:00.04

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

SQL> 

Lets now monitor the SQL Result Cache by using related dynamic views.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 13:41:23 2011

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


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

SQL> SELECT name
,      type
,      cache_id
,      row_count
FROM   v$result_cache_objects
ORDER  BY
creation_timestamp;    

NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------
U0001.T0002
Dependency
U0001.T0002
0


NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------
select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type
order by object_type
Result
44uuyvrukrh7w7w2m48aqb12tk
23

NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------


When there will be any DML operation on the dependent object u0001.t0002, the result cache is invalidated and generated again with the next run of SQL.

Monitor the SQL Result Cache statistics
SQL> column name format a30
SQL> column value format a30
SQL> select name, value from v$result_cache_statistics order by id;

NAME                           VALUE
------------------------------ ------------------------------
Block Size (Bytes)             1024
Block Count Maximum            1120
Block Count Current            32
Result Size Maximum (Blocks)   56
Create Count Success           1
Create Count Failure           0
Find Count                     2
Invalidation Count             0
Delete Count Invalid           0
Delete Count Valid             0
Hash Chain Length              1

NAME                           VALUE
------------------------------ ------------------------------
Find Copy Count                2

12 rows selected.

SQL> 

2 comments:

mahakk01 said...

Oracle 11g introduces new feature which is known as "SQL Result Cache". This feature will enhance the performance. You can see the code for the same in this post. The code is easy to learn and implement. I am going to apply it in my previous application.You can also try it.
sap support pack stacks

Ural Ural said...

Thanks for your comment.
Cheers,
Ural