Home » RDBMS Server » Performance Tuning » SQL AREA HIT Ratio
SQL AREA HIT Ratio [message #109299] Wed, 23 February 2005 11:31 Go to next message
timgb
Messages: 3
Registered: February 2005
Location: Dublin
Junior Member
Hi all,

I ran the following sql statement
select namespace, gethitratio*100 from v$librarycache;
The result shows that gethitratio*100 for sqlarea is 45.32, and I had a monitoring tool which reports that this is extremely low. Does anyone know what I could to improve this. I have increased the value of the shared_pool_size but it doesn't help.

Thanks.

Tim

Report message to a moderator

Re: SQL AREA HIT Ratio [message #109309 is a reply to message #109299] Wed, 23 February 2005 13:03 Go to previous messageGo to next message
Mack Sundar
Messages: 20
Registered: July 2002
Junior Member
If the database was only recently restarted and you have not done too much work with that database, the LIBRARY CACHE HIT ratio will be low because there haven't been too many queries that can be shared.

Wouldn't worry too much about library cache hit ratio unless it is degrading application performance.

Report message to a moderator

Re: SQL AREA HIT Ratio [message #109381 is a reply to message #109309] Thu, 24 February 2005 04:50 Go to previous messageGo to next message
timgb
Messages: 3
Registered: February 2005
Location: Dublin
Junior Member
Hi Mack,

Thanks for your response.

The database is shutdown and restart everyday around mid-night. The amazing thing here is that when I check the gethitratio value for SQL AREA in the view v$librarycache in the norning it is always around 0.74 (74%) but it keep decreasing as time goes by. Although, there hadn't been any complain from the users of the database but I would love to sort it out before it gets to that point.

Thanks for your help.

Tim

Report message to a moderator

Re: SQL AREA HIT Ratio [message #109504 is a reply to message #109299] Fri, 25 February 2005 01:34 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Check whether the statements in your application use bind-variables

hth

Report message to a moderator

Re: SQL AREA HIT Ratio [message #109887 is a reply to message #109299] Tue, 01 March 2005 13:32 Go to previous messageGo to next message
pgongloo
Messages: 8
Registered: February 2005
Junior Member
Besides, bind variables, if you can investigate cursor sharing =force. Some early releases had some issues, but over time the feature is stable enough. Also, check for invalidations, it might be that, the tables or dropped or re-analyzed causing it the prior SQL to be invalidated and hence not reused.

-GP

Report message to a moderator

Re: SQL AREA HIT Ratio [message #109937 is a reply to message #109299] Wed, 02 March 2005 01:54 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
cursor_sharing=force is a band-aid, to be used between the moment you notice you 'forgot' to use bind-variables and the moment you deliver your patched application, using bind-variables.
It is not a definite solution.

hth

Report message to a moderator

Re: SQL AREA HIT Ratio [message #110034 is a reply to message #109299] Wed, 02 March 2005 21:56 Go to previous messageGo to next message
pgongloo
Messages: 8
Registered: February 2005
Junior Member
Very true, first you should try fixing the application code to use bind variables. In the interim, you can use the "band-aid" mechanism. If you have legacy code, cursor_sharing=force might be the only option.... I have seen performance scalability of around 3X at least with cursor sharing=force, of course really depends on how much the system is bottlenecked on the shared pool/lib. cache latches. -GP

Report message to a moderator

Re: SQL AREA HIT Ratio [message #110247 is a reply to message #110034] Fri, 04 March 2005 10:58 Go to previous messageGo to next message
timgb
Messages: 3
Registered: February 2005
Location: Dublin
Junior Member
Hi All,

Many thanks to Pgongloo, Frank and Mack Sundar. I have tried your suggestion by setting cursor_sharing=force in the init parameter file. While this resolved the problem it wouldn't allow the application running against the database connect or work properly. I have investigated further and I found out that the application sql is not using bind variable so much. Unfortunately I have no control over the application, it was not developed in house.

Once again thanks for your help.

Tim

Report message to a moderator

Re: SQL AREA HIT Ratio [message #110249 is a reply to message #109299] Fri, 04 March 2005 11:24 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Quote:

I have no control over the application, it was not developed in house

If the application was developed as a custom-application, and it was done not too long ago, you could point the company that developed it to it's error in the code, because that's what it is. It makes the application not scalable, so it should be fixed.

hth

Report message to a moderator

Previous Topic: ORA-04030
Next Topic: Full table scan with varying consistent gets
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 22:44:34 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.