/*
|| Oracle 11g Results Caching, Part 2: OCI and PL/SQL Results Caching
||
|| Demonstrates Oracle 11g OCI and PL/SQL Result Caching features, including:
|| - Using PL/SQL Results Cache in deterministic PL/SQL functions
|| - Setting up Oracle Call Interface (OCI) client-side Results Cache
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Results Cache, and they should be carefully proofread before being
|| executed against any existing Oracle database to avoid potential damage!
*/

/*  
|| Listing 2.1:
|| Preparing to demonstrate Results Cache in PL/SQL functions
*/

-----
-- Create new table (AP.CURRENCY_CONVERSIONS)
-----
DROP TABLE ap.currency_conversions PURGE;
CREATE table ap.currency_conversions (
     currency_from      VARCHAR2(3)     NOT NULL
    ,currency_to        VARCHAR2(3)     NOT NULL
    ,effect_dtm         TIMESTAMP       NOT NULL
    ,conversion_fctr    NUMBER(10,6)    DEFAULT 0
)
    TABLESPACE example;

ALTER TABLE ap.currency_conversions
    ADD CONSTRAINT currency_conversions_pk 
    PRIMARY KEY (currency_from, currency_to, effect_dtm)
    USING INDEX ( 
        CREATE INDEX ap.currency_conversions_pk_idx
            ON ap.currency_conversions (currency_from, currency_to, effect_dtm)
            TABLESPACE example
        );

-----
-- Populate currency conversion test data
-----
INSERT INTO ap.currency_conversions
VALUES ('USD', 'JPY', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 110.3600);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'EUR', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.6790);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'CDN', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.0581);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'GBP', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.5353);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'AUD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.1478);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'CHF', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.0940);
INSERT INTO ap.currency_conversions
VALUES ('JPY', 'USD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.009061);
INSERT INTO ap.currency_conversions
VALUES ('JPY', 'EUR', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.006152);
INSERT INTO ap.currency_conversions
VALUES ('JPY', 'CDN', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.009588);
INSERT INTO ap.currency_conversions
VALUES ('JPY', 'GBP', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.004850);
INSERT INTO ap.currency_conversions
VALUES ('JPY', 'AUD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.010400);
INSERT INTO ap.currency_conversions
VALUES ('JPY', 'CHF', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.009913);
INSERT INTO ap.currency_conversions
VALUES ('EUR', 'USD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.4728);
INSERT INTO ap.currency_conversions
VALUES ('EUR', 'JPY', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 162.5382);
INSERT INTO ap.currency_conversions
VALUES ('EUR', 'CDN', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.5584);
INSERT INTO ap.currency_conversions
VALUES ('EUR', 'GBP', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.7883);
INSERT INTO ap.currency_conversions
VALUES ('EUR', 'AUD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.6904);
INSERT INTO ap.currency_conversions
VALUES ('EUR', 'CHF', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.6112);
INSERT INTO ap.currency_conversions
VALUES ('CDN', 'USD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.9451);
INSERT INTO ap.currency_conversions
VALUES ('CDN', 'JPY', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 104.3002);
INSERT INTO ap.currency_conversions
VALUES ('CDN', 'EUR', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.6417);
INSERT INTO ap.currency_conversions
VALUES ('CDN', 'GBP', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.5059);
INSERT INTO ap.currency_conversions
VALUES ('CDN', 'AUD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.0848);
INSERT INTO ap.currency_conversions
VALUES ('CDN', 'CHF', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.0339);
INSERT INTO ap.currency_conversions
VALUES ('GBP', 'USD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.8682);
INSERT INTO ap.currency_conversions
VALUES ('GBP', 'JPY', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 206.1767);
INSERT INTO ap.currency_conversions
VALUES ('GBP', 'EUR', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.2685);
INSERT INTO ap.currency_conversions
VALUES ('GBP', 'CDN', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.9768);
INSERT INTO ap.currency_conversions
VALUES ('GBP', 'AUD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 2.1443);
INSERT INTO ap.currency_conversions
VALUES ('GBP', 'CHF', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 2.0438);
INSERT INTO ap.currency_conversions
VALUES ('AUD', 'USD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.8713);
INSERT INTO ap.currency_conversions
VALUES ('AUD', 'JPY', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 96.1512);
INSERT INTO ap.currency_conversions
VALUES ('AUD', 'EUR', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.5916);
INSERT INTO ap.currency_conversions
VALUES ('AUD', 'CDN', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.9219);
INSERT INTO ap.currency_conversions
VALUES ('AUD', 'GBP', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.4664);
INSERT INTO ap.currency_conversions
VALUES ('AUD', 'CHF', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.9531);
INSERT INTO ap.currency_conversions
VALUES ('CHF', 'USD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.9141);
INSERT INTO ap.currency_conversions
VALUES ('CHF', 'JPY', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 100.8775);
INSERT INTO ap.currency_conversions
VALUES ('CHF', 'EUR', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.6206);
INSERT INTO ap.currency_conversions
VALUES ('CHF', 'CDN', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.9672);
INSERT INTO ap.currency_conversions
VALUES ('CHF', 'GBP', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 0.4893);
INSERT INTO ap.currency_conversions
VALUES ('CHF', 'AUD', TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi'), 1.0492);

COMMIT;

/* 
|| Listing 2.2:
|| Create a deterministic PL/SQL function that uses PL/SQL Results Cache to retain
|| currency exchange rates
*/

-----
-- Create a function that returns a currency conversion factor based on 
-- supplied values for input arguments
-----

CREATE OR REPLACE FUNCTION ap.converted_amount (
     orig_cncy  IN  VARCHAR2    DEFAULT 'USD'
    ,xlat_cncy  IN  VARCHAR2    DEFAULT 'EUR'
    ,xlat_dtm   IN  DATE
)
    RETURN NUMBER
    RESULT_CACHE RELIES_ON (ap.currency_conversions)
IS
    result NUMBER(10,6);
BEGIN
    SELECT conversion_fctr 
      INTO result
      FROM ap.currency_conversions
     WHERE currency_from = orig_cncy
       AND currency_to = xlat_cncy
       AND effect_dtm = (SELECT MAX(effect_dtm)
                           FROM ap.currency_conversions
                          WHERE currency_from = orig_cncy
                            AND currency_to = xlat_cncy
                            AND effect_dtm <= xlat_dtm)
    ;
  RETURN result;
END;
/

GRANT EXECUTE ON oe.product_name TO PUBLIC;

/* 
|| Listing 2.3:
|| Demonstrating PL/SQL Function Result Cache
|| 1.) Flush entire current contents of the Results Cache
|| 2.) Trigger use of stored function
|| 3.) Demonstrate proof of results being cached
*/ 

-----
-- Flush the entire current contents of the Results Cache 
-----
BEGIN
    DBMS_RESULT_CACHE.FLUSH;
END;
/

-----
-- Invoke the stored function for three currency conversions as of August 15, 2008
-- at 15 minutes past midnight
-----
SELECT
     ap.converted_amount('USD', 'EUR', TO_DATE('2008/08/15 00:15','yyyy/mm/dd hh24:mi')) 
    AS "USD-EUR" 
    ,ap.converted_amount('EUR', 'AUD', TO_DATE('2008/08/15 00:15','yyyy/mm/dd hh24:mi')) 
    AS "EUR-AUD"
    ,ap.converted_amount('JPY', 'CDN', TO_DATE('2008/08/15 00:15','yyyy/mm/dd hh24:mi')) 
    AS "JPY-CDN"
  FROM DUAL;

>>> Query results (after initial data loading):

   USD-EUR    EUR-AUD    JPY-CDN
---------- ---------- ----------
      .679     1.6904    .009588

>>> After Initial Query:

                                                        Objects Currently Cached in SQL Results Cache
                                                                 (From V$RESULT_CACHE_OBJECTS)

                                                                                         Space
Cache                   Bckt                                                   Invali-    Over  Unused
Type        Status         # Hash Value   Cache Name               Created On  dations    Head   Space Cache ID                   Cache Key
----------- --------- ------ ------------ ------------------------ ----------- ------- ------- ------- -------------------------- --------------------------
Dependency  Published   3382 1139117366   AP.CURRENCY_CONVERSIONS  2008-08-24        0       0       0 AP.CURRENCY_CONVERSIONS    AP.CURRENCY_CONVERSIONS
                                                                   13:27:29

Dependency  Published   2063 2350327823   AP.CONVERTED_AMOUNT      2008-08-24        0       0       0 AP.CONVERTED_AMOUNT        AP.CONVERTED_AMOUNT
                                                                   13:27:29

Result      Published   1937 1873565585   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 2269dtssjbn6a88962kzfwvz60
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Published   1910 599246710    "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     766 80td9u53rr6pn5pd8w4yfyy04a 46v3q4s5jq7rw6w9jwrkvdp1y8
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Published   2322 2000877842   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 6cuc7t1g145zt6dw8m8qz6m1pg
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1


/* 
|| Listing 2.4:
|| Demonstrating PL/SQL Function Result Cache invalidation
|| as a result of DML against dependent table
*/ 

-----
-- Add some new values into result set. What happens?
-----
INSERT INTO ap.currency_conversions
VALUES ('USD', 'EUR', TO_TIMESTAMP('2008/08/19 00:00','yyyy/mm/dd hh24:mi'), 0.6719);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'EUR', TO_TIMESTAMP('2008/08/18 00:00','yyyy/mm/dd hh24:mi'), 0.6718);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'EUR', TO_TIMESTAMP('2008/08/17 00:00','yyyy/mm/dd hh24:mi'), 0.6717);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'EUR', TO_TIMESTAMP('2008/08/16 00:00','yyyy/mm/dd hh24:mi'), 0.6716);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'EUR', TO_TIMESTAMP('2008/08/15 00:00','yyyy/mm/dd hh24:mi'), 0.6715);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'EUR', TO_TIMESTAMP('2008/08/14 00:00','yyyy/mm/dd hh24:mi'), 0.6714);
INSERT INTO ap.currency_conversions
VALUES ('USD', 'EUR', TO_TIMESTAMP('2008/08/13 00:00','yyyy/mm/dd hh24:mi'), 0.6713);

COMMIT;

>>> Query results (after INSERTs):

   USD-EUR    EUR-AUD    JPY-CDN
---------- ---------- ----------
     .6715     1.6904    .009588

>>> After INSERTs (but before requerying):

                                                        Objects Currently Cached in SQL Results Cache
                                                                 (From V$RESULT_CACHE_OBJECTS)

                                                                                         Space
Cache                   Bckt                                                   Invali-    Over  Unused
Type        Status         # Hash Value   Cache Name               Created On  dations    Head   Space Cache ID                   Cache Key
----------- --------- ------ ------------ ------------------------ ----------- ------- ------- ------- -------------------------- --------------------------
Dependency  Published   3382 1139117366   AP.CURRENCY_CONVERSIONS  2008-08-24        1       0       0 AP.CURRENCY_CONVERSIONS    AP.CURRENCY_CONVERSIONS
                                                                   13:27:29

Dependency  Published   2063 2350327823   AP.CONVERTED_AMOUNT      2008-08-24        0       0       0 AP.CONVERTED_AMOUNT        AP.CONVERTED_AMOUNT
                                                                   13:27:29

Result      Invalid    2322 2000877842   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 6cuc7t1g145zt6dw8m8qz6m1pg
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Invalid    1910 599246710    "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     766 80td9u53rr6pn5pd8w4yfyy04a 46v3q4s5jq7rw6w9jwrkvdp1y8
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Invalid    1937 1873565585   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 2269dtssjbn6a88962kzfwvz60
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

>>> After First Requery:


                                                        Objects Currently Cached in SQL Results Cache
                                                                 (From V$RESULT_CACHE_OBJECTS)

                                                                                         Space
Cache                   Bckt                                                   Invali-    Over  Unused
Type        Status         # Hash Value   Cache Name               Created On  dations    Head   Space Cache ID                   Cache Key
----------- --------- ------ ------------ ------------------------ ----------- ------- ------- ------- -------------------------- --------------------------
Dependency  Published   3382 1139117366   AP.CURRENCY_CONVERSIONS  2008-08-24        1       0       0 AP.CURRENCY_CONVERSIONS    AP.CURRENCY_CONVERSIONS
                                                                   13:27:29

Dependency  Published   2063 2350327823   AP.CONVERTED_AMOUNT      2008-08-24        0       0       0 AP.CONVERTED_AMOUNT        AP.CONVERTED_AMOUNT
                                                                   13:27:29

Result      Published  1937 1873565585   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 2269dtssjbn6a88962kzfwvz60
                                          :8."CONVERTED_AMOUNT"#94 13:30:14
                                          c1b380508b067 #1

Result      Published  1910 599246710    "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     766 80td9u53rr6pn5pd8w4yfyy04a 46v3q4s5jq7rw6w9jwrkvdp1y8
                                          :8."CONVERTED_AMOUNT"#94 13:30:14
                                          c1b380508b067 #1

Result      Published  2322 2000877842   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 6cuc7t1g145zt6dw8m8qz6m1pg
                                          :8."CONVERTED_AMOUNT"#94 13:30:14
                                          c1b380508b067 #1

Result      Invalid     2322 2000877842   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 6cuc7t1g145zt6dw8m8qz6m1pg
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Invalid     1910 599246710    "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     766 80td9u53rr6pn5pd8w4yfyy04a 46v3q4s5jq7rw6w9jwrkvdp1y8
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Invalid     1937 1873565585   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 2269dtssjbn6a88962kzfwvz60
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

-----
-- Next, modify some values that have been already cached. What happens?
-----
UPDATE ap.currency_conversions
  SET conversion_fctr = 0.6899
 WHERE currency_from = 'USD'
   AND currency_to = 'EUR'
   AND effect_dtm = TO_TIMESTAMP('2008/08/19 00:00','yyyy/mm/dd hh24:mi')
;
UPDATE ap.currency_conversions
  SET conversion_fctr = 1.6015
 WHERE currency_from = 'EUR'
   AND currency_to = 'AUD'
   AND effect_dtm = TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi')
;
UPDATE ap.currency_conversions
  SET conversion_fctr = 0.009013
 WHERE currency_from = 'JPY'
   AND currency_to = 'CDN'
   AND effect_dtm = TO_TIMESTAMP('2008/08/12 00:00','yyyy/mm/dd hh24:mi')
;
 
COMMIT;

>>> After UPDATEs:

   USD-EUR    EUR-AUD    JPY-CDN
---------- ---------- ----------
     .6715     1.6015   .009013

                                                        Objects Currently Cached in SQL Results Cache
                                                                 (From V$RESULT_CACHE_OBJECTS)

                                                                                         Space
Cache                   Bckt                                                   Invali-    Over  Unused
Type        Status         # Hash Value   Cache Name               Created On  dations    Head   Space Cache ID                   Cache Key
----------- --------- ------ ------------ ------------------------ ----------- ------- ------- ------- -------------------------- --------------------------
Dependency  Published   3382 1139117366   AP.CURRENCY_CONVERSIONS  2008-08-24        2       0       0 AP.CURRENCY_CONVERSIONS    AP.CURRENCY_CONVERSIONS
                                                                   13:27:29

Dependency  Published   2063 2350327823   AP.CONVERTED_AMOUNT      2008-08-24        0       0       0 AP.CONVERTED_AMOUNT        AP.CONVERTED_AMOUNT
                                                                   13:27:29

Result      Invalid     2322 2000877842   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 6cuc7t1g145zt6dw8m8qz6m1pg
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Invalid     1910 599246710    "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     766 80td9u53rr6pn5pd8w4yfyy04a 46v3q4s5jq7rw6w9jwrkvdp1y8
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Invalid     1937 1873565585   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 2269dtssjbn6a88962kzfwvz60
                                          :8."CONVERTED_AMOUNT"#94 13:27:29
                                          c1b380508b067 #1

Result      Invalid     2322 2000877842   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 6cuc7t1g145zt6dw8m8qz6m1pg
                                          :8."CONVERTED_AMOUNT"#94 13:30:14
                                          c1b380508b067 #1

Result      Invalid     1910 599246710    "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     766 80td9u53rr6pn5pd8w4yfyy04a 46v3q4s5jq7rw6w9jwrkvdp1y8
                                          :8."CONVERTED_AMOUNT"#94 13:30:14
                                          c1b380508b067 #1

Result      Invalid     1937 1873565585   "AP"."CONVERTED_AMOUNT": 2008-08-24        0     252     767 80td9u53rr6pn5pd8w4yfyy04a 2269dtssjbn6a88962kzfwvz60
                                          :8."CONVERTED_AMOUNT"#94 13:30:14
                                          c1b380508b067 #1


/* 
|| Listing 2.5:
|| Viewing OCI Application Results Cache metadata
*/

-----
-- View:    V$CLIENT_RESULT_CACHE_STATS
-- Purpose: Monitors the state of application result set caches
-----
TTITLE 'Current Server-Side Cache Result Settings|(From V$CLIENT_RESULT_CACHE_STATS)'
COL "CLIENT REGID"  FORMAT 9999999  HEADING 'Client|Reg #'
COL block_size      FORMAT 9999999  HEADING 'Block|Size'
COL block_max       FORMAT 9999999  HEADING 'Max|Blocks|Alloc'
COL block_count     FORMAT 9999999  HEADING 'Curr|Block|Count'
COL create_succ     FORMAT 9999999  HEADING 'Result|Set|Create|Success'
COL create_fail     FORMAT 9999999  HEADING 'Result|Set|Create|Failure'
COL finds           FORMAT 9999999  HEADING 'Rslt|Set|Crt|Failure'
COL invalidations   FORMAT 9999999  HEADING 'Invali-|dations'
COL delete_invalids FORMAT 9999999  HEADING 'Inval|Results|Deltd'
COL delete_valids   FORMAT 9999999  HEADING 'Valid|Results|Deltd'
SELECT 
     "CLIENT REGID"
    ,block_size
    ,block_max
    ,block_count
    ,bucket_count
    ,create_succ
    ,create_fail
    ,finds
    ,invalidations
    ,delete_invalids
    ,delete_valids
  FROM v$client_result_cache_stats
;
TTITLE OFF

-----
-- View:    CLIENT_RESULT_CACHE_STATS$ 
-- Purpose: Monitors the state of application result set caches
-----
TTITLE 'Current Client-Side Cache Result Settings|(From CLIENT_RESULT_CACHE_STATS$)'
COL cache_id    FORMAT 999999   HEADING 'Cache|ID'
COL stat_id     FORMAT 999999   HEADING 'Stat|ID'
COL name        FORMAT A30      HEADING 'Name'
COL value       FORMAT 999999   HEADING 'Value'
SELECT 
     cache_id
    ,stat_id
    ,name
    ,value
  FROM client_result_cache_stats$
;
TTITLE OFF