Scribd
Upload
2 views1 page

Query Result Cache Practice Guide

This document outlines a practice exercise for using the Query Result Cache in Oracle's SGA. It includes steps for setting up the cache, executing queries, observing execution plans, and analyzing the cache's content. The practice also involves inserting data, generating reports, and clearing the cache to understand its functionality and performance.

Uploaded by

sdfaql94
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views1 page

Query Result Cache Practice Guide

This document outlines a practice exercise for using the Query Result Cache in Oracle's SGA. It includes steps for setting up the cache, executing queries, observing execution plans, and analyzing the cache's content. The practice also involves inserting data, generating reports, and clearing the cache to understand its functionality and performance.

Uploaded by

sdfaql94
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Here’s the text from the image you provided:

Practice 5-6: Using Result Cache

In this practice, you explore the various possibilities to cache query results in the SGA. Perform
the following steps to understand the use of Query Result Cache.

1. Change to the $HOME/labs directory and execute the result_cache_setup.sh script.


2. In your terminal window log in to SQL*Plus as the QRC user. From now on, do not
disconnect from this session. Determine the current content of the query cache by using
the following statement:
3. select type, status, name, object_no, row_count, row_size_avg
4. from v$result_cache_objects order by 1;
5. Set timing on and execute the following query. You can use the rc_query1.sql script.
Note the time that it takes to execute
6. select /*+ result_cache q_name(Q1) */ count(*)
7. from cachejf v1, cachejfv c2, cachejfv c3, cachejfv c4, cachejfv c5
8. where c1.c='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b';
9. Determine the execution plan of the previous by using explain_rc_query1.sql. What
do you observe?
10. Determine the current content of the query cache. What do you observe?
11. Flush the buffer cache of your instance and rerun the query executed at step 3. What do
you observe?
12. Insert a new row into the CACHEJFV table by using the following statement:
13. insert into cachejfv values('c');
14. commit;

What do you observe?

15. Execute your first query again and check the result cache. What do you observe?
16. Generate a detailed result cache memory report.
17. Drop the cachejfv table again. What do you observe?
18. Clear the result cache. Query V$RESULT_CACHE_OBJECTS to verify the clear operation.

You might also like