OraFAQ Forum: Performance Tuning » DB_Cache_Hit Ratio less than 70

Home » RDBMS Server » Performance Tuning » DB_Cache_Hit Ratio less than 70

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

DB_Cache_Hit Ratio less than 70 [message #266626]

Tue, 11 September 2007 04:36

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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266636 is a reply to message #266626]

Tue, 11 September 2007 04:49

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266648 is a reply to message #266636]

Tue, 11 September 2007 05:23

tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi

Senior Member

actually after running that particular report my entire database get slow

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266651 is a reply to message #266648]

Tue, 11 September 2007 05:33

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266654 is a reply to message #266651]

Tue, 11 September 2007 05:36

tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi

Senior Member

what it invalidates the cache. How?

u mean to say invalidate objects

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266656 is a reply to message #266654]

Tue, 11 September 2007 05:44

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use blocks that are not used by others.

Regards
Michel

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266661 is a reply to message #266656]

Tue, 11 September 2007 05:58

tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi

Senior Member

how

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266664 is a reply to message #266661]

Tue, 11 September 2007 06:11

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266666 is a reply to message #266664]

Tue, 11 September 2007 06:15

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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266669 is a reply to message #266666]

Tue, 11 September 2007 06:36

tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi

Senior Member

kindly suggest

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266682 is a reply to message #266669]

Tue, 11 September 2007 07:08

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266689 is a reply to message #266682]

Tue, 11 September 2007 07:30

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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266710 is a reply to message #266689]

Tue, 11 September 2007 08:20

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266993 is a reply to message #266710]

Wed, 12 September 2007 03:41

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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #266997 is a reply to message #266993]

Wed, 12 September 2007 03:52

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #267011 is a reply to message #266993]

Wed, 12 September 2007 04:34

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

Report message to a moderator

Re: DB_Cache_Hit Ratio less than 70 [message #267044 is a reply to message #267011]

Wed, 12 September 2007 06:15

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Failed!
Use "Preview Message" before clicking on "Submit Reply".

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 05:55:52 CDT 2024