PLSQL functions enable us to modularise and encapsulate our business logic, following generalised programming best practices. However, there has always been a performance penalty of varying degrees when using our own PLSQL functions. The biggest penalties are borne when we embed SQL lookups inside PLSQL functions or when we simply call PLSQL functions repeatedly from SQL. Context switching and excessive IO can degrade performance of even the most simple queries. There is even an overhead in calling the most simple PLSQL only functions from within PLSQL. Oracle developers have tended to address this performance issue in different ways avoidance hard code business logic repeatedly in SQL statements avoidance join to lookup tables in SQL statements optimisation cache lookup tables in user managed PLSQL arrays and expose them via a function acceptance pay the price for following good programming practice. JDeveloper_11g_R2_Developing_Rich_Web_Applications_With_Oracle_ADF_Rus/Part3/t30204.gif' alt='Pl/Sql Cursor Sample Programs' title='Pl/Sql Cursor Sample Programs' />The database recognizes 1,746,000 software titles and delivers updates for your software including minor upgrades. Gregorian calendar monthly. Virtual columns in 11g. Ar Rahman Mp3 Song. Oracle has supported stored expressions for many years, in views and functionbased indexes. Most commonly, views enable us to store and. Latest, Top, Free, Best PLSQL Interview Questions and Answers, Job FAQs, Queries, Tips, Sample Papers, Exam Papers PLSQL What, Why, When, Where, How PLSQL. Oracle 1. 1g attempts to address the cost of calling PLSQL functions in two key ways. For the most marginal gains, there is subprogram inlining which removes the overhead of function calls by re organising the source code during compilation. General SQL Scripts. Sample SQL matrix report Lookup Oracle error messages Display Database version, installed options and port string Who am I script. For potentially greater and more widespread gains, Oracle has introduced the Cross Session PLSQL Function Result Cache to give it its full name and we will see how this feature works in this article. Note that for the remainder of this article, we will refer to the Cross Session PLSQL Function Result Cache as the Function Result Cache. The principle behind PLSQL function result caching is simple. We specify through syntax that a PLSQL function is to be cached or rather, its results are to be cached. Each time we call the function with a new set of parameters, Oracle executes the function, adds the results to the result cache and returns the results to us. When we repeat the function call, Oracle retrieves the results from the cache rather than re execute the function. Under certain circumstances, this caching behaviour can result in significant performance gains. Many developers will recognise this behaviour as being similar to DIY caching using associative arrays formerly known as PLSQL Tables. It is quite common for developers to cache lookup tables in integer or string indexed PLSQL arrays. The Function Result Cache differs from this technique in two important ways memory usage associative array data is held in PGA memory. This means that cached results are available to a single session and cannot be shared. Memory usage can sometimes increase to unacceptable levels as more sessions initialise and use associative array caches. The new Function Result Cache, however, uses a global memory allocation from the shared pool managed by Oracle and the cached results are available across sessions cache invalidation cached reference data is dependant on its underlying data sources. It is difficult to maintain data invalidations when using associative array caches, despite attempts by Oracle to expose notification technologies in recent versions. With the Function Result Cache, however, cached results can be directly coupled to their underlying data sources. This means that cached results are invalidated and regenerated when transactions occur against the underlying data. This protects the application from using potentially stale reference data. The Function Result Cache is one of three caching initiatives added to 1. Query Result Cache and client side OCI caching. The Function Result Cache and Query Result Cache share the same components such as memory allocation and parameters. For this reason, this article on the Query Result Cache is recommended background reading. It includes details of many of the shared components, the result cache architecture, how it is allocated and managed, what it contains and how to investigate it. This information is not repeated below. For the investigations in this article, the resultcachemaxsize is set to 2. M. All other parameters are set to default details of which are in the recommended reading. All examples are created in the SH supplied schema. We will begin by creating a simple cache enabled PLSQL function. We will base this on a Steven Feuerstein best practice example and create a function to encapsulate the business rule for formatting a full customer name, as follows. SQL CREATE FUNCTION formatcustomername. IN VARCHAR2. 3 plastname IN VARCHAR2. RETURN VARCHAR2 RESULTCACHE IS. RETURN pfirstname plastname. END formatcustomername. Function created. Note the RESULTCACHE syntax. This is new to 1. This is the minimum syntax required to make use of the new Function Result Cache. In terms of the function we have created, it is standalone for demonstration purposes only we would use a package for real application code. In addition, we have included a call to a COUNTER package. We will use this throughout this article to keep a count of how many times our function is executed. We are ready to test our cache enabled function. As stated, we are going to keep track of how many times this is executed, so we will initialise a counter as follows. SQL exec counter. PLSQL procedure successfully completed. In the following SQL statement, we will call our function to format the names of a small sample of CUSTOMERS. SQL SELECT c. AS custname. FROM customers c. WHERE ROWNUM lt 1. CUSTID CUSTNAME. Benjamin Taylor. Benjamin Taylor. 4. Benjamin Taylor. 1. Abel Aaron. 2. 18. Abel Aaron. 2. 54. Abel Aaron. 2. 90. Abel Aaron. 3. 25. Abel Aaron. 3. 70. Abel Embrey. 4. 05. Abel Embrey. 1. 0 rows selected. To complete this simple example, we will see how many times the function was executed by printing the current counter, as follows. SQL exec counter. Function calls. Function calls 3. PLSQL procedure successfully completed. The counter was incremented just three times, which suggests that the result cache was used for the remainder of the rows in our query. We can verify this anecdotally by examining our query output above. We have just three customer names in our output although these account for ten rows between them. On the first instance of each of the customer names, Oracle executed the FORMATCUSTOMERNAME function and added the return value to the result cache. On subsequent instances of the customer name parameters, Oracle retrieved the formatted customer name directly from the result cache without executing the function at all. The recommended background reading describes a range of dynamic information available about the result cache. We can use some of this information to validate our assumptions about the Function Result Cache behaviour that we observed above. The VRESULTCACHESTATISTICS view keeps an instance wide record of result cache usage, which we can query as follows. SQL SELECT name, value. FROM vresultcachestatistics. WHERE name IN Create Count Success,Find Count. NAME VALUE. Create Count Success 3. Find Count 7. Given that we started with a flushed empty result cache, these statistics state that we have made three entries to the result cache so far and have used these results a further seven times. This tallies exactly with what we surmised above. We know from the background reading that the VRESULTCACHEOBJECTS view exposes the cached queries and their dependencies. PLSQL functions with result cache entries are also exposed by this view.