 |
|
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. |
