Author: Burleson
Page:
1
2
3
Oracle 11g has provided a number of new enhancements. Oracle result_cache is an important feature amongst them. In this article we will reveal the many different shades of Oracle result_cache feature.
Creating Oracle result_cache:
Creating a result cache is conceptually similar to other existing Oracle data passing tools such as a shared PL/SQL collection or a materialized view. However it should be noted that result_cache is a shared array and it is stored in SGA RAM.
Enabling Oracle result_cache functionality:
Oracle result_cache functionality can be enabled in below ways.
- You can issue the command " alter session cache results; " to cache your session data.
- You can create a PL/SQL function using the result_cache keyword to store the result, and use the new relies_on keyword to tell Oracle what tables will trigger invalidation.
- You can add the /*+ result_cache */ hint to any SQL statement, and invoking the result_cache hint is far easier than creating a PL/SQL array or materialized view because the syntax is very straightforward.
select /*+ result_cache */
stuff
from tab1 natural join tab2;
Configuring the database for Oracle result_cache:
Below 11g parameters are required to enable the result_cache feature.
| result_cache_max_size |
This parameter specifies the maximum amount of SGA memory (in bytes) that the Result Cache can use. The defaylt is zero, but you can use the " alter system set result_cache_max_size " to enable the feature at runtime. |
| result_cache_max_result |
This parameter specifies the maximum percentage of the Result Cache that any single result can use. |
| result_cache_mode |
This parameter can be set to "manual" or "force". It specifies when a ResultCache operator is spliced into a query's execution plan. |
| result_cache_remote_expiration |
This parameter is the value, in minutes that a result cache will be alive. |
Oracle result_cache Views:
The Oracle result_cache views include
- v$result_cache_statistics
- v$result_cache_memory
- v$result_cache_objects,
- v$result_cache_dependency
The equivalent RAC views include
- gv$result_cache_statistics
- gv$result_cache_memory
- gv$result_cache_objects
- gv$result_cache_dependency
Each RAC instance will have a private area in each SGA for result set caching.
Continued...
Page:
1
2
3
More Oracle Articles, Database Articles and DBA Tips
Database Security: Step by step guideline
Security Alert - Database Worms
Inside default Oracle users!!
Useful Tips for Designing Oracle Data warehouse
Beware of Oracle LOG_ARCHIVE_MAX_PROCESSES Parameter!!
|
