Oracle 11g allows the results of sql queries to be cached in the sga and reused to improve performance. There are three new result caching features in 11g. I will be talking here about the standard query result cache the plsql result cache seems to be in general more tricky see for example here. Lets prepare simple function that is using result cache functionality. I clear out the shared pool to get rid of cached sql explain plans and buffer cache to get rid of cached data by running the following commands. You can configure the oracle bi server to maintain a local, diskbased cache of query result sets query cache. Oracle database 11g offers result set caching to provide this functionality. That is, result cache should perform faster and scale better.
Instead of actually executing the query or function, the results stored in the cache are returned to the user. Concurrency is essentially made possible through serialization of shared resources. That being said, i would expect result cache to beat oracles buffer cache on readonly workloads, since that is what rc was designed for. The first query will create the result set and subsequent queries will use the cached result set instead of creating their own result set. Finally, the version of runstats used in this article can be downloaded here.
Sql result cache is another cache in the sga, just like buffer cache or program global area. Query result cache in oracle database 11g release 1. Function result cache in oracle 11g oracle apps tech blog. Oracle 11gr2 query result cache incremental update. Following on from my earlier posting of problems with temporary table and the plsql result cache a combination which the manuals warn you against heres another problem again, to a large degree, selfinflicted imagine you have a complex report involving a large number of financial transactions with a need to include calculations about current exchange rates. The following oracle 11g database initialization parameters affect the operation and function of the result cache for oracle 11g. There are many amazing new features in the 11g version, one of them is the possibility to cache the result sets of statements, that. The possible parameter values are manual and force. Another brilliant 11g new feature i have just finished an oracle database 11g new features course in vienna. The plsql function result cache minimizes the amount of memory needed to cache and share this data across all sessions. This can be especially useful when querying very large tables in cases where the user is only interested in the first so many rows from the table. Oracle 11gs query result cache introduceing the rc enqueue.
First call of the function takes 5 seconds and result of the function is cached. Articles oracle 8i oracle 9i oracle 10g oracle 11g oracle 12c oracle c oracle 18c oracle 19c miscellaneous plsql sql oracle rac oracle apps weblogic linux mysql. Oracle recommends result caching for queries of readonly or readmostly database objects. But in oracle 11g, results from definer right plsql function can be cached in the result cache component of shared pool on the server. This memory pool consists of the sql query result cachewhich stores results of sql queriesand the. 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 2m bytes 2k blocks maximum result size 102k bytes 102 blocks memory total memory 103528 bytes 0. A sql query result set will be cached within a subsection of a database instances shared pool in the shared global area sga depending on the setting for several new initialization parameters. Otherwise, the database executes the query and returns the result as output and stores it in the result cache.
Now with the 11g we all can start the new release at least on linux x86 so naturally, i was curious to see whether i was just throwing sparks without having a fire. Using oracle database 11g release 2 result cache in an oracle. New in oracle 11g, improved in 11gr2 sql query results local and distributed plsql function results oci client results. Oracle 12c lifts the restriction by allowing the results from invokers rights plsql function to be cached in the server cache by adding the invoking user identity to the result. I have following function in a package and it will be invoked within the package for many times. The query cache allows the oracle bi server to satisfy many subsequent query requests without having. On the plsql function result cache oracle magazine. Open a sql plus session as the sys user and execute the following command. A client process is one that gets created by the operating system.
The result cache is new to oracle 11g and provides enhanced query performance for sql and plsql applications by caching the results of sql queries into memory. It is usually helpful when we have a select query which is frequently used and data is being modified rarely. Managing performance tuning and query caching oracle. This article will describe and demonstrate the query result cache only. In 11g, oracle has extended this further by adding result caching to both its server. Oracle has introduced three new caching features in its oracle 11g release. I would conclude that the data is being written to the result cache, a fourth step repeating the sql should show zero consistent gets and that would be the results being read. Instead, i want to maintain function result cacheavailable with 11g and i will look up in cache first before executing the query. A new feature in the oracle 11g database is the function result cache. In summary, client query result cache furnishes the following benefits by. Oracle 11g and above allows the sql queries to be cached in the sga and reused to improve performance. The database does not cache sql statements contained in your function. With the sql result cache, the plsql function result cache and the client result cache in 11g, the need for extra moving bits and pieces goes away in most all cases.
The function result cache and query result cache share the same. Amis summary of oracle openworld 20 is available for download 60page white paper. If you dont, you cant expect oracle surely invalidates the result cache, since it is not necessarily able to track dependencies. High latch free waits with cont ention on result cache. Cached data is shared across executions automatically marked stale if underlying data is changed can. Query result cache in oracle database 11g release 1 oracle 11g allows the results of sql queries to be cached in the sga and reused to improve performance. The result cache is always enabled by default, and its size depends on the memory the database allocates to the shared pool. Sql result cache 11g dbaora oracle expert presents. Oracle 11g attempts to address the cost of calling plsql functions in two key ways. Choosing between multiple indexes on a table use the most selective. Oracle database 11g delivers economies of scale on easily managed lowcost grids, making it easier to reduce cost of downtime with maximum availability architecture, change it systems faster using real application testing, partition and compress data to run queries faster using less disks, securely protect and audit data, and enable total recall of data, and make productive use of standby. Ora client cache parameters are optional and they override server settings. Result cache is a new feature in oracle 11g and it does exactly what its name implies, it caches the results of queries and puts it into a slice of the shared pool. Oracle database 11g provides client query result cache for that purpose.
Query result cache in oracle 11g blog dbi services. Result cache and rac tuning oracle consulting, oracle. The purview of this article is to describe the feature client result cache specific to 11gr2. The oracle database contains a special pseudocolumn named rownum. The query cache allows the oracle bi server to satisfy many subsequent query requests without having to access backend data sources such as oracle or db2. Unlike the buffer pools, the server result cache holds result sets and not data blocks.
How to use result cache for queries in 11g dbaclass. Setup set up the following schema objects to see how the sql query cache works. It queries the data from cache rather than database. Oracle 11g results cache dean richards senior dba, confio. The server result cache contains the sql query result cache and plsql function result cache, which share the same infrastructure. The client result cache is a memory area in the client process that stores sql query result sets of outer most query of oci applications.
Ppt oracle 11g results cache dean richards senior dba, confio software powerpoint presentation free to download id. The result cache is a powerful new feature that caches a result set in the sga for a specified period of time. As of oracle database 11g, the function result cache has entered the caching fray. Oracle 11g introduced the result cache to significantly speed up frequently executed queries or plsql functions. A client cache is configured at the application level and is located in client memory, not in database memory. A client result cache differs from the server result cache. What query you are trying to run may be you added some column with the same name to final result query. This low memory profile, plus the automatic purge of cached results whenever changes are committed, makes this feature of oracle database 11g very practical for optimizing performance in plsql applications. This column can be used in sql select queries to limit the results of oracle queries. The pl sql function result cache feature shares much of the same architecture, but will be the subject of a future article. Our update uses sysdate, while the select does an order by the datecolumn we update using that sysdate.
The source code for the examples in this article can be downloaded from here. Subsequent execution of same query is faster because now the data is being pulled from cache. The dba can turn the result cache option on or off. Improving application performance with result cache oracle. Its job is to keep the result set of a query into a subset of the shared pool. Advanced plsql dear all, in this post, we are posting the set of questions, asked in the 1z0146 exam. I want to ensure that all cached items are cleared before running each query in order to prevent misleading performance results. High latch free waits with contention on result cache. Query result cache was the first new feature that i put to the test, only to discover that you can spend more than two minutes waiting on guess what. Query result cache in oracle database 11g release 1 oracle base. If you have a query that is executed often and reads data that rarely changes, this feature can increase performance significantly. The amazing and elegant plsql function result cache nocoug.
1415 134 488 685 11 624 460 1412 1467 129 170 59 808 1115 660 28 615 369 149 1481 972 1010 607 1074 972 1287 1314 1397 139 811 175 236 1352 176 671