OraFAQ Forum: Performance Tuning » sql result cache for improving the performance (merged 4)

Home » RDBMS Server » Performance Tuning » sql result cache for improving the performance (merged 4) (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql result cache for improving the performance (merged 4) [message #610629]

Sat, 22 March 2014 03:05

kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India

Member

How effective sql result cache would be if we set that for entire db,How long it would take to collect and store the data in cache and how much cache would be consumed by having this turned on,how can we estimate the performance impact?can someone shed some light on this

Report message to a moderator

Re: sql result cache for improving the performance [message #610630 is a reply to message #610629]

Sat, 22 March 2014 03:36

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

If you read the docs on the Result Cache, you will see that the size is limited by whatever value you give the RESULT_CACHE_SIZE instance parameter. As is populated by running queries, it is not possible to say how long this will take. Estimating the impact is easy, just do a few tests:

orclz>
orclz> alter session set result_cache_mode=force;

Session altered.

orclz> set timing on;
orclz> select count(*) from oe.PRODUCT_DESCRIPTIONS,oe.PRODUCT_DESCRIPTIONS;

  COUNT(*)
----------
  74649600

Elapsed: 00:00:02.26
orclz> select count(*) from oe.PRODUCT_DESCRIPTIONS,oe.PRODUCT_DESCRIPTIONS;

  COUNT(*)
----------
  74649600

Elapsed: 00:00:00.01
orclz>

Report message to a moderator

Re: sql result cache for improving the performance [message #610631 is a reply to message #610629]

Sat, 22 March 2014 03:39

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You misunderstood the purpose and scope of result cache, please read:

Database Concepts
Chapter 14 Memory Architecture
Section Server Result Cache

Database Performance Tuning Guide
Chapter 7 Configuring and Using Memory[
Section 7.6 Managing the Server and Client Result Caches

Report message to a moderator

Re: sql result cache for improving the performance [message #610654 is a reply to message #610631]

Sat, 22 March 2014 08:54

kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India

Member

what is a query fragment

Report message to a moderator

Re: sql result cache for improving the performance [message #610655 is a reply to message #610654]

Sat, 22 March 2014 09:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

>what is a query fragment
I have no idea to what above means.
post URL which refers to "query fragment"

Report message to a moderator

What is your experience with Oracle's result caching feature? [message #610711 is a reply to message #610629]

Sun, 23 March 2014 08:28

kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India

Member

What is your experience with Oracle's result caching feature?

Report message to a moderator

Re: What is your experience with Oracle's result caching feature? [message #610717 is a reply to message #610711]

Sun, 23 March 2014 09:48

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please answer BlackSwan's question.

Report message to a moderator

Re: What is your experience with Oracle's result caching feature? [message #610718 is a reply to message #610717]

Sun, 23 March 2014 09:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What is your experience with Oracle's result caching feature?
It works fine & lasts a long time.
It is transparent to all users.

What problem are you trying to solve?
How will you (or I) know when it has been solved?

Report message to a moderator

Re: What is your experience with Oracle's result caching feature? [message #610719 is a reply to message #610718]

Sun, 23 March 2014 10:04

kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India

Member

I am trying to set it up for my entire db?and learning its pros and cons

Report message to a moderator

Re: What is your experience with Oracle's result caching feature? [message #610720 is a reply to message #610719]

Sun, 23 March 2014 10:07

kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India

Member

how to interpret the memory report

 exec  DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE")
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 8M bytes (8K blocks)
Maximum Result Size = 409K bytes (409 blocks)
[Memory]
Total Memory = 169352 bytes [0.019% of the Shared Pool]
... Fixed Memory = 5296 bytes [0.001% of the Shared Pool]
....... Cache Mgr  = 152 bytes
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 164056 bytes [0.018% of the Shared Pool]
....... Overhead = 131288 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 28888 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 25 blocks
........... Used Memory = 7 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 6 blocks
................... SQL     = 2 blocks (2 count)
................... Invalid = 4 blocks (4 count)

Report message to a moderator

Re: What is your experience with Oracle's result caching feature? [message #610721 is a reply to message #610720]

Sun, 23 March 2014 10:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>how to interpret the memory report
It appears you DB is as small & idle as mine.

SQL>  exec  DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE");
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 1120K bytes (1120 blocks)
Maximum Result Size = 56K bytes (56 blocks)
[Memory]
Total Memory = 103532 bytes [0.060% of the Shared Pool]
... Fixed Memory = 5180 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 128 bytes
....... Cache Mgr  = 152 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 98352 bytes [0.057% of the Shared Pool]
....... Overhead = 65584 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 8240 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 27 blocks
........... Used Memory = 5 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 4 blocks
................... SQL     = 4 blocks (4 count)

PL/SQL procedure successfully completed.

SQL>

--moderator update: added [code] tags, please do so yourself in future.

[Updated on: Sun, 23 March 2014 11:54] by Moderator

Report message to a moderator

no improvement using result cache [message #610764 is a reply to message #610629]

Mon, 24 March 2014 03:34

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

Usage of result cache hint for the subsequent invocation shows negligible or no improvement in time

select /*+result_cache*/ status from demand_plan2 where dp_id=309;

can someone what could be the reason.why this happening?

[EDITED by LF: fixed topic title typo; was "cahce"]

Attachment: first exec.png
(Size: 20.57KB, Downloaded 1825 times)

[Updated on: Mon, 24 March 2014 05:06] by Moderator

Report message to a moderator

Re: no improvement using result cahce [message #610765 is a reply to message #610764]

Mon, 24 March 2014 03:43

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

here is the screenshot for the second execution

Attachment: second execution.png
(Size: 20.05KB, Downloaded 1831 times)

Report message to a moderator

Re: no improvement using result cahce [message #610770 is a reply to message #610765]

Mon, 24 March 2014 03:56

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Please do not post images like that, use copy/paste from your SQL*Plus session and enclose it in [code] tags, as requested in our OraFAQ Forum Guide and described here How to use [code] tags and make your code easier to read

Have you looked the execution statistics? How much I/O each execution needed?

Report message to a moderator

Re: no improvement using result cahce [message #610771 is a reply to message #610770]

Mon, 24 March 2014 04:02

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

the phyical reads,consistent gets returned zero during the second execution.i had a look at the execution stats. How do i determine how much I/O needed for the execution

[Updated on: Mon, 24 March 2014 04:08]

Report message to a moderator

Re: no improvement using result cahce [message #610774 is a reply to message #610771]

Mon, 24 March 2014 04:44

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

the phyical reads,consistent gets returned zero during the second execution.

Maybe because you are using the result cache.

Report message to a moderator

Re: no improvement using result cahce [message #610776 is a reply to message #610774]

Mon, 24 March 2014 04:59

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

yes,its because of result cache tats y the consistent reads,phyical gets are 0.but there should be a drop in time during the subsequent executions.i dont see that.what could be wrong

[Updated on: Mon, 24 March 2014 06:38]

Report message to a moderator

why isn't my result cache not working? [message #610835 is a reply to message #610629]

Tue, 25 March 2014 01:42

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

why isn't my result cache not working?

1) without result cache-19s
2) with result cache-19s-physical read 0,consistent gets 0
3) with result cache-19s-physical read 0,consistent gets 0
4) with result cache-20s-physical read 0,consistent gets 0

show paramter result_cache

NAME                             TYPE        VALUE
------------------------------------               -----------      ----------
client_result_cache_lag          big integer 3000
client_result_cache_size         big integer 0
result_cache_max_result          integer     5
result_cache_max_size            big integer 52448K
result_cache_mode                string      MANUAL
result_cache_remote_expiration   integer     0

sql> SELECT DBMS_RESULT_CACHE.status() FROM dual;

 ORA-00904: : invalid identifier

Report message to a moderator

Re: why isn't my result cache not working? [message #610837 is a reply to message #610835]

Tue, 25 March 2014 01:57

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

In your previous topic, you posted information showing that the result cache was working, but you said that it wasn't. In this topic, you have posted some parameter values but nothing else: no query, no exec plan, no figures for execution time or I/O.
Very difficult to assist in such circumstances.

As for your query, it works for me:

london> SELECT DBMS_RESULT_CACHE.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
-------------------------------------------------------
ENABLED

london>

Report message to a moderator

Re: why isn't my result cache not working? [message #610847 is a reply to message #610837]

Tue, 25 March 2014 02:52

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

Execution plan :

without /*+ result_cahe */ hint

SQL> SELECT FCST.Lev_RP_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_GBURECON_TYPE.Member_
Name, FCST.Lev_RP_GBURECON_TYPE.Member_Description, FCST.Lev_RP_GBURECON_TYPE.Sequence FROM FCST.Hry_RP_GBURECON_TYPE_SE
LECT,FCST.Lev_RP_GBURECON_TYPE WHERE FCST.Lev_RP_GBURECON_TYPE.Member_Key=FCST.Hry_RP_GBURECON_TYPE_SELECT.Member_Key AN
D FCST.Hry_RP_GBURECON_TYPE_SELECT.Level_Key=1312010 ORDER BY FCST.Lev_RP_GBURECON_TYPE.Member_ID;

Elapsed: 00:00:02.11

Execution Plan
----------------------------------------------------------
Plan hash value: 1484964662

--------------------------------------------------------------------------------
-------------------

| Id  | Operation           | Name                        | Rows  | Bytes | Cost
 (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------

|   0 | SELECT STATEMENT    |                             |     4 |   288 |
8  (25)| 00:00:01 |

|   1 |  SORT ORDER BY      |                             |     4 |   288 |
8  (25)| 00:00:01 |

|*  2 |   HASH JOIN         |                             |     4 |   288 |
7  (15)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL| HRY_RP_GBURECON_TYPE_SELECT |     4 |    48 |
3   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| LEV_RP_GBURECON_TYPE        |     4 |   240 |
3   (0)| 00:00:01 |

--------------------------------------------------------------------------------
-------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LEV_RP_GBURECON_TYPE"."MEMBER_KEY"="HRY_RP_GBURECON_TYPE_SELECT".
"MEMBER_KEY

              ")
   3 - filter("HRY_RP_GBURECON_TYPE_SELECT"."LEVEL_KEY"=1312010)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        629  bytes sent via SQL*Net to client
        248  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed



with /*+ result_cahe */ hint


SQL> SELECT /*+ result_cache */ FCST.Lev_RP_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_G
BURECON_TYPE.Member_Name, FCST.Lev_RP_GBURECON_TYPE.Member_Description, FCST.Lev_RP_GBURECON_TYPE.Sequence FROM FCST.Hry
_RP_GBURECON_TYPE_SELECT,FCST.Lev_RP_GBURECON_TYPE WHERE FCST.Lev_RP_GBURECON_TYPE.Member_Key=FCST.Hry_RP_GBURECON_TYPE_
SELECT.Member_Key AND FCST.Hry_RP_GBURECON_TYPE_SELECT.Level_Key=1312010 ORDER BY FCST.Lev_RP_GBURECON_TYPE.Member_ID;

Elapsed: 00:00:02.16

Execution Plan
----------------------------------------------------------
Plan hash value: 1484964662

--------------------------------------------------------------------------------
--------------------

| Id  | Operation            | Name                        | Rows  | Bytes | Cos
t (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------

|   0 | SELECT STATEMENT     |                             |     4 |   288 |
 8  (25)| 00:00:01 |

|   1 |  RESULT CACHE        | 5auhmbpa5j4x789bfdp5mu3m3u  |       |       |
        |          |

|   2 |   SORT ORDER BY      |                             |     4 |   288 |
 8  (25)| 00:00:01 |

|*  3 |    HASH JOIN         |                             |     4 |   288 |
 7  (15)| 00:00:01 |

|*  4 |     TABLE ACCESS FULL| HRY_RP_GBURECON_TYPE_SELECT |     4 |    48 |
 3   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| LEV_RP_GBURECON_TYPE        |     4 |   240 |
 3   (0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("LEV_RP_GBURECON_TYPE"."MEMBER_KEY"="HRY_RP_GBURECON_TYPE_SELECT".
"MEMBER_KEY"

              )
   4 - filter("HRY_RP_GBURECON_TYPE_SELECT"."LEVEL_KEY"=1312010)

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=5; dependencies=(FCST.LEV_RP_GBURECON_TYPE, FCST.HRY_RP_GBUR
ECON_TYPE_SELECT); parameters=(nls); name="SELECT /*+ result_cache */ FCST.Lev_R
P_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_GBU
RECON_TYPE."



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        630  bytes sent via SQL*Net to client
        248  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed
          
          
        again  with /*+ result_cahe */ hint 
          
          
          
	  SQL> SELECT /*+ result_cache */ FCST.Lev_RP_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_
	  BURECON_TYPE.Member_Name, FCST.Lev_RP_GBURECON_TYPE.Member_Description, FCST.Lev_RP_GBURECON_TYPE.Sequence FROM FCST.Hr
	  _RP_GBURECON_TYPE_SELECT,FCST.Lev_RP_GBURECON_TYPE WHERE FCST.Lev_RP_GBURECON_TYPE.Member_Key=FCST.Hry_RP_GBURECON_TYPE
	  SELECT.Member_Key AND FCST.Hry_RP_GBURECON_TYPE_SELECT.Level_Key=1312010 ORDER BY FCST.Lev_RP_GBURECON_TYPE.Member_ID;
	  
	  Elapsed: 00:00:02.16
	  
	  Execution Plan
	  ----------------------------------------------------------
	  Plan hash value: 1484964662
	  
	  --------------------------------------------------------------------------------
	  --------------------
	  
	  | Id  | Operation            | Name                        | Rows  | Bytes | Cos
	  t (%CPU)| Time     |
	  
	  --------------------------------------------------------------------------------
	  --------------------
	  
	  |   0 | SELECT STATEMENT     |                             |     4 |   288 |
	   8  (25)| 00:00:01 |
	  
	  |   1 |  RESULT CACHE        | 5auhmbpa5j4x789bfdp5mu3m3u  |       |       |
	          |          |
	  
	  |   2 |   SORT ORDER BY      |                             |     4 |   288 |
	   8  (25)| 00:00:01 |
	  
	  |*  3 |    HASH JOIN         |                             |     4 |   288 |
	   7  (15)| 00:00:01 |
	  
	  |*  4 |     TABLE ACCESS FULL| HRY_RP_GBURECON_TYPE_SELECT |     4 |    48 |
	   3   (0)| 00:00:01 |
	  
	  |   5 |     TABLE ACCESS FULL| LEV_RP_GBURECON_TYPE        |     4 |   240 |
	   3   (0)| 00:00:01 |
	  
	  --------------------------------------------------------------------------------
	  --------------------
	  
	  
	  Predicate Information (identified by operation id):
	  ---------------------------------------------------
	  
	     3 - access("LEV_RP_GBURECON_TYPE"."MEMBER_KEY"="HRY_RP_GBURECON_TYPE_SELECT".
	  "MEMBER_KEY"
	  
	                )
	     4 - filter("HRY_RP_GBURECON_TYPE_SELECT"."LEVEL_KEY"=1312010)
	  
	  Result Cache Information (identified by operation id):
	  ------------------------------------------------------
	  
	     1 - column-count=5; dependencies=(FCST.LEV_RP_GBURECON_TYPE, FCST.HRY_RP_GBUR
	  ECON_TYPE_SELECT); parameters=(nls); name="SELECT /*+ result_cache */ FCST.Lev_R
	  P_GBURECON_TYPE.Member_Key, FCST.Lev_RP_GBURECON_TYPE.Member_ID, FCST.Lev_RP_GBU
	  RECON_TYPE."
	  
	  
	  
	  Statistics
	  ----------------------------------------------------------
	            0  recursive calls
	            0  db block gets
	            0  consistent gets
	            0  physical reads
	            0  redo size
	          630  bytes sent via SQL*Net to client
	          248  bytes received via SQL*Net from client
	            2  SQL*Net roundtrips to/from client
	            0  sorts (memory)
	            0  sorts (disk)
	            4  rows processed

my question is why there is no drop in time during subsequent execution.the time is supposed to drop.

Report message to a moderator

Re: why isn't my result cache not working? [message #610849 is a reply to message #610847]

Tue, 25 March 2014 03:03

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

The result cache is working perfectly. You'll have to trace the statement to determine the wait events on which the time is spent.

Report message to a moderator

Re: why isn't my result cache not working? [message #610867 is a reply to message #610849]

Tue, 25 March 2014 05:15

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

does this parameter have any effect on delay?

client_result_cache_size big integer 0

Report message to a moderator

Previous Topic:	Use of Hints in Oracle 11g to get desired Execution Plan
Next Topic:	Best Design for batch processing using BLOB Types

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 06:28:52 CST 2024