/*
|| 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