Home » RDBMS Server » Performance Tuning » DB_Cache_Hit Ratio less than 70
DB_Cache_Hit Ratio less than 70 [message #266626] Tue, 11 September 2007 04:36 Go to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
Hi

I m using oracle 9i release 2

O/s : windows 2000 server

After running a particular report my db_cahce_hit ratio goes down less than 50% sometime

I have increase the size of db_cache_size but the problem is still same

kindly suggest

Regards

TM
Re: DB_Cache_Hit Ratio less than 70 [message #266636 is a reply to message #266626] Tue, 11 September 2007 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forget this.
Ignore your cache hit ratio.
Why do you want to change its value?

Regards
Michel
Re: DB_Cache_Hit Ratio less than 70 [message #266648 is a reply to message #266636] Tue, 11 September 2007 05:23 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
actually after running that particular report my entire database get slow
Re: DB_Cache_Hit Ratio less than 70 [message #266651 is a reply to message #266648] Tue, 11 September 2007 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe database must have to reload data in cache.
This has nothing with cache size, first optimize the report, see what it invalidates the cache and so on.

Regards
Michel
Re: DB_Cache_Hit Ratio less than 70 [message #266654 is a reply to message #266651] Tue, 11 September 2007 05:36 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
what it invalidates the cache. How?

u mean to say invalidate objects
Re: DB_Cache_Hit Ratio less than 70 [message #266656 is a reply to message #266654] Tue, 11 September 2007 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use blocks that are not used by others.

Regards
Michel
Re: DB_Cache_Hit Ratio less than 70 [message #266661 is a reply to message #266656] Tue, 11 September 2007 05:58 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
how
Re: DB_Cache_Hit Ratio less than 70 [message #266664 is a reply to message #266661] Tue, 11 September 2007 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant the report uses blocks that are not used by others.

Regards
Michel
Re: DB_Cache_Hit Ratio less than 70 [message #266666 is a reply to message #266664] Tue, 11 September 2007 06:15 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
u mean i need to tune the sql query

how can i know the block is load in cache which is not used by any users
Re: DB_Cache_Hit Ratio less than 70 [message #266669 is a reply to message #266666] Tue, 11 September 2007 06:36 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
kindly suggest
Re: DB_Cache_Hit Ratio less than 70 [message #266682 is a reply to message #266669] Tue, 11 September 2007 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let me the time to lunch.

You take the problem by the wrong end.
Optimize your report without looking at cache.
When you will have optimized the report then you will have a look at concurrent processes.

Regards
Michel
Re: DB_Cache_Hit Ratio less than 70 [message #266689 is a reply to message #266682] Tue, 11 September 2007 07:30 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
i have already optmize the report

the query behind that report is

select a.VCH_VCH_#,a.VCH_VCH_DATE,b.VCH_GL_CODE,
b.VCH_SL_CODE,b.VCH_NARATN_LN,
b.vch_date_DB,b.vch_date_CR,b.VCH_DOC_#,VCH_BILL_# ,SL_TYPE_DETL,
b.VCH_DBCR,b.vch_vch_type
VCH_BILL_DATE,b.vch_srl_# ,b.vch_prod_type
from fac_vch_m a, fac_vch_d b
where a.vch_locn_code = b.vch_locn_code
and
a.VCH_VCH_type = b.VCH_VCH_type
and
a.VCH_VCH_# = b.VCH_VCH_#
and
a.vch_locn_code = :locn_code
and
a.VCH_VCH_type = :vch_type
and
a.VCH_VCH_# BETWEEN NVL(:FROM_VCH_NO,A.VCH_VCH_#) AND
NVL(:TO_VCH_NO,A.VCH_VCH_#)
ORDER BY vch_vch_#, VCH_SRL_#


how can i know the number of buffer read by this query in db cache

Re: DB_Cache_Hit Ratio less than 70 [message #266710 is a reply to message #266689] Tue, 11 September 2007 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

set autotrace on statistics
or activate sql trace and use tkprof.

Regards
Michel
Re: DB_Cache_Hit Ratio less than 70 [message #266993 is a reply to message #266710] Wed, 12 September 2007 03:41 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
Hi M

Explain Plan of that query is

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1313 | 44 |
| 1 | SORT ORDER BY | | 13 | 1313 | 44 |
| 2 | TABLE ACCESS BY INDEX ROWID | FAC_VCH_D | 2 | 152 | 3 |
| 3 | NESTED LOOPS | | 13 | 1313 | 40 |
| 4 | TABLE ACCESS BY INDEX ROWID| FAC_VCH_M | 8 | 200 | 16 |
| 5 | INDEX RANGE SCAN | CONS_VCH_PK | 8 | | 15 |
| 6 | INDEX RANGE SCAN | CONS_VCH_D_PK | 1 | | 2 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------

Plz Suggest
Re: DB_Cache_Hit Ratio less than 70 [message #266997 is a reply to message #266993] Wed, 12 September 2007 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I suggest to
1/ Format your post
2/ Don't use IM speak
3/ Read and follow the rules

Regards
Michel
Re: DB_Cache_Hit Ratio less than 70 [message #267011 is a reply to message #266993] Wed, 12 September 2007 04:34 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
[ PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1313 | 44 |
| 1 | SORT ORDER BY | | 13 | 1313 | 44 |
| 2 | TABLE ACCESS BY INDEX ROWID | FAC_VCH_D | 2 | 152 | 3 |
| 3 | NESTED LOOPS | | 13 | 1313 | 40 |
| 4 | TABLE ACCESS BY INDEX ROWID| FAC_VCH_M | 8 | 200 | 16 |
| 5 | INDEX RANGE SCAN | CONS_VCH_PK | 8 | | 15 |
| 6 | INDEX RANGE SCAN | CONS_VCH_D_PK | 1 | | 2 |
]

Plz suggest
Re: DB_Cache_Hit Ratio less than 70 [message #267044 is a reply to message #267011] Wed, 12 September 2007 06:15 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Failed!
Use "Preview Message" before clicking on "Submit Reply".

Regards
Michel
Previous Topic: What is the different between Oracle parse and application parse?
Next Topic: SMON TOP PROCESS
Goto Forum:
  


Current Time: Sat Dec 03 21:55:02 CST 2016

Total time taken to generate the page: 0.13808 seconds