BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Hint Cache Result Set

Oracle 11g New Features Tips by Burleson Consulting
June 27, 2008

Oracle 11g SQL New Features Tips

Following this, each subsequent execution of the query with the hint will use the caches results set.  This can be seen in both the execution plan, which includes the execution plan for the result set, and in the statistics, which shows that there were no block gets.

select /*+ RESULT_CACHE */
  initcap(store_name)  c1,
  total                c2
from
  (select
     store_name,
     sum(quantity) total
   from store join sales using (store_key)
   group by store_name)
where
  total > (select avg(total)
           from (select
                   sum(quantity) total
                 from store join sales using (store_key)
                 group by store_name))

Store Name                        Total
------------------------------ --------
Borders                          21,860
Books For Dummies                13,000
Wee Bee Books                    13,700
Wild And Lively Books            24,700
Eaton Books                      12,120 

-------------------------------------------
| Id  | Operation                         |
-------------------------------------------
|   0 | SELECT STATEMENT                  |
|   1 |  RESULT CACHE                     |
|*  2 |   FILTER                          |
|   3 |    HASH GROUP BY                  |
|   4 |     MERGE JOIN                    |
|   5 |      TABLE ACCESS BY INDEX ROWID  |
|   6 |       INDEX FULL SCAN             |
|*  7 |      SORT JOIN                    |
|   8 |       TABLE ACCESS FULL           |
|   9 |    SORT AGGREGATE                 |
|  10 |     VIEW                          |
|  11 |      SORT GROUP BY                |
|  12 |       MERGE JOIN                  |
|  13 |        TABLE ACCESS BY INDEX ROWID|
|  14 |         INDEX FULL SCAN           |
|* 15 |        SORT JOIN                  |
|  16 |         TABLE ACCESS FULL         |
-------------------------------------------

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

The example above illustrates how powerful this feature is.  But the hint does not have to only be in the root of the SQL statement.   In the next example, the subqueries are removed and placed in a view.  Though it is common to find poorly performing SQL statements based on views, this view will really benefit from the performance of Result Cache.

To begin, the view should be created and the query needs to be run to get a base line.

create view v1 as
select
     store_name,
     sum(quantity) total
   from store join sales using (store_key)
   group by store_name;

select
  initcap(store_name)  c1,
  total                c2
from v1
where
  total > (select avg(total)
           from v1); 

Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        608  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          5  rows processed

Here, there are 22 consistent gets.  Next, the current view must be dropped, and replaced with a view containing the hint.

drop view v1;

create view v1 as
select /*+ RESULT_CACHE */
     store_name,
     sum(quantity) total
   from store join sales using (store_key)
   group by store_name;
 

The query next must be run twice to show the result cache impact:

select
  initcap(store_name)  c1,
  total                c2
from v1
where
  total > (select avg(total)
           from v1);
 

Statistics
----------------------------------------------------------
        135  recursive calls
          8  db block gets
         68  consistent gets
          0  physical reads
          0  redo size
        608  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          5  rows processed

select
  initcap(store_name)  c1,
  total                c2
from v1
where
  total > (select avg(total)
           from v1);
 

-------------------------------------------
 | Id  | Operation                         |
 -------------------------------------------
 |   0 | SELECT STATEMENT                  |
 |*  1 |  VIEW                             |
 |   2 |   RESULT CACHE                    |
 |   3 |    HASH GROUP BY                  |
 |   4 |     MERGE JOIN                    |
 |   5 |      TABLE ACCESS BY INDEX ROWID  |
 |   6 |       INDEX FULL SCAN             |
 |*  7 |      SORT JOIN                    |
 |   8 |       TABLE ACCESS FULL           |
 |   9 |   SORT AGGREGATE                  |
 |  10 |    VIEW                           |
 |  11 |     RESULT CACHE                  |
 |  12 |      SORT GROUP BY                |
 |  13 |       MERGE JOIN                  |
 |  14 |        TABLE ACCESS BY INDEX ROWID|
 |  15 |         INDEX FULL SCAN           |
 |* 16 |        SORT JOIN                  |
 |  17 |         TABLE ACCESS FULL         |
 -------------------------------------------

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

Notice in the execution plan that the cached result set was utilized twice, and the statistics show no consistent gets.

Oracle provides a number of views and packages to look into the cached and cleared objects in the Result Cache for optimal monitoring.  To list the objects in the result cache v$result_cache_objects is used.

select cache_id,name from v$result_cache_objects; 

CACHE_ID                     NAME
------------------------------------------------------------
PUBS.STORE                   PUBS.STORE
PUBS.SALES                   PUBS.SALES

cqdxahmy5120z7s0wjs4wwuvfy   PUBS.V1

5qbrt9zvmpruh3an1mwq1wg9zf   select /*+ RESULT_CACHE */
                              initcap(store_name)  c1,
                              total                c2
                             from
                              (select
                                 store_name,
                                 sum(quantity

Invalidating the cached objects can be done by flushing the Result Cache, as seen below:

select count(*) from v$result_cache_objects;

  COUNT(*)
----------
         5

exec dbms_result_cache.flush;

select count(*) from v$result_cache_objects;

  COUNT(*)
----------
         0

Additional packages used to maintain and invalidate the Result Cache will be covered in the PL/SQL Chapter.

From the examples covered, the impact of using the Result Cache hint with SQL can be seen.  Being able to cache the results of expensive SQL, like constantly used views, can have a dramatic impact on SQL performance.  However, it must be kept in mind that there is a price to be paid in the additional memory, contention and latching in the SGA.

 

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter