How to cache query results in Oracle?

OracleSoftware & CodingProgramming

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;
raja
Published on 04-Dec-2020 10:49:31
Advertisements