Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to cache query results in Oracle?
Problem Statement:
You want to improve the performance of frequently used queries.
Solution:
We have to use Oracle’s result cache to store the query results of frequently used SQL, so they can be retrieved quickly for future use when the same query has been executed.
The result cache is new additon to Oracle 11g, which will allow us store results from often-used queries in memory for quick and easy retrieval.
Example
SELECT /*+ result_cache */ e.class_id, min_fees, max_fees FROM students e ,jobs j WHERE e.class_id = j.class_id GROUP BY e.class_id, min_fees, max_fees;
To demonstrate how it is used, we will check the explain_plan for the above sql.
Example
EXPLAIN PLAN FOR SELECT /*+ result_cache */ e.class_id, min_fees, max_fees FROM students e ,jobs j WHERE e.class_id = j.class_id GROUP BY e.class_id, min_fees, max_fees; SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL'));
Output
--------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 475 | 4 (25)| 00:00:01 | | 1 | RESULT CACHE | a1pfq5gpbag4wgj0zth2xasdj1 | 19 | 475 | 4 (25)| 00:00:01 | | 2 | HASH GROUP BY | | 19 | 475 | 4 (25)| 00:00:01 | | 3 | NESTED LOOPS SEMI | | 19 | 475 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| JOBS | 19 | 304 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | stu_JOB_IX | 107 | 963 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
We will then query the V$RESULT_CACHE_OBJECTS view, to validate whether the results of a query are stored in the result cache by looking at the cache ID value from the explain plan.
Example
SELECT ID, type, status, creation_timestamp, column_count AS cols, row_count AS "rows" FROM V$RESULT_CACHE_OBJECTS WHERE CACHE_ID = 'a1pfq5gpbag4wgj0zth2xasdj1';
Example
1947 Result Published 11/NOV/20 3 19
The result cache hint, when added to a SQL, will override any database, table, or session level result cache settings. Before adding the hints to your SQL’s, you need to validate the configuration of the result cache on your database.
Query time will be improved significantly with result cache, as query results are checked within the result cache first, and if the results exist, they are simply pulled from memory, and the query is not executed. The result cache is most appropriately used for frequently executed queries that produce the same results.
To show the performance improvement above SQL is run twice once without hint and second time with the hint.
Example
SELECT /*+ no_result_cache */ e.class_id, min_fees, max_fees FROM students e ,jobs j WHERE e.class_id = j.class_id GROUP BY e.class_id, min_fees, max_fees;
Output
19 rows selected. Elapsed: 00:00:17.20
Example
SELECT /*+ result_cache */ e.class_id, min_fees, max_fees FROM students e ,jobs j WHERE e.class_id = j.class_id GROUP BY e.class_id, min_fees, max_fees;
Output
19 rows selected. Elapsed: 00:00:01.03
Data Used for above problem:
Example
DROP TABLE students; COMMIT; CREATE TABLE students ( student_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) , email VARCHAR2(40) , phone_number VARCHAR2(20) , join_date DATE , class_id VARCHAR2(20) , fees NUMBER(8,2) , professor_id NUMBER(6) , department_id NUMBER(4) ) ;
Example
CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);
COMMIT;
2K+ Views
