Databases Reference
In-Depth Information
Caching results with the client-side
result cache
In this recipe we will see how to enable and use the client-side result cache to reach
significant improvement in repeatedly executing complex queries.
Getting ready
To enable the client result cache feature in a database, we need to alter the
CLIENT_
RESULT_CACHE_SIZE
parameter and set a size for caching larger than zero (the default
value). This parameter specifies the size in bytes used by all the client processes as the
maximum size of the client per-process result set cache.
To do so we need to execute the following commands, querying for the actual value of the
parameter:
CONNNECT / AS SYSDBA
SHOW PARAMETER CLIENT_RESULT_CACHE_SIZE
If we need to alter the size, because it is set to zero, or if we want to change the actual
size—we can use the following commands. Once we set the size for the client result cache
to 5 MB, we restart the instance to enable the modifications:
ALTER SYSTEM SET CLIENT_RESULT_CACHE_SIZE=5M SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP OPEN
We are now ready to experiment with the client side result cache.
How to do it...
The following steps will demonstrate the client-side result cache:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Enable the automatic explain plan and execute a query that joins
CUSTOMERS
and
COUNTRIES
tables:
SET AUTOT TRACE EXP
SELECT COUNTRY_NAME, CUST_LAST_NAME, COUNT(*)
FROM CUSTOMERS C, COUNTRIES CT
WHERE C.COUNTRY_ID = CT.COUNTRY_ID
GROUP BY COUNTRY_NAME, CUST_LAST_NAME;
