Home » RDBMS Server » Performance Tuning » sql library miss rate (2 Merged) (oracle database 10.2.0.4)
sql library miss rate (2 Merged) [message #500263] Fri, 18 March 2011 11:30 Go to next message
karioookey
Messages: 17
Registered: February 2008
Location: egypt
Junior Member

hi

i'm facing a trouble
in spotlight toad tool
alarm appears about sql library miss rate 61,2%

can any one help me in solving that issue ?
urgently


regards
Re: sql library miss rate [message #500265 is a reply to message #500263] Fri, 18 March 2011 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 22508
Registered: January 2009
Senior Member
application must use bind variables.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: sql library miss rate (2 Merged) [message #500269 is a reply to message #500263] Fri, 18 March 2011 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Urgent => I wait 6 hours before reading the topics.
The only thing urgent in a forum is to read the OraFAQ Forum Guide.
We owe you nothing urgently or not.
Everyone thinks his problem is urgent.
I think no problem but mine is.

Regards
Michel



CM: fixed url tags

[Updated on: Fri, 18 March 2011 13:08] by Moderator

Report message to a moderator

Re: sql library miss rate (2 Merged) [message #500283 is a reply to message #500263] Fri, 18 March 2011 16:23 Go to previous messageGo to next message
John Watson
Messages: 4385
Registered: January 2010
Location: Global Village
Senior Member
Are you sure that there is actually an issue that needs to be addressed? This is a major failing of tools such as Spotlight: they don't tell you about problems, they only report on certain metrics crossing thresholds. No user has ever telephoned the DBA to say "we have a problem: the library cache miss rate is too high". Have you had any complaints? If you haven't, then you have no problem (and certainly not an "urgent" one.)
Re: sql library miss rate (2 Merged) [message #500284 is a reply to message #500283] Fri, 18 March 2011 16:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you see such a question there is only one possible answer: throw away TOAD, it is a f... awful tool!

Regards
Michel
Re: sql library miss rate (2 Merged) [message #500285 is a reply to message #500284] Fri, 18 March 2011 16:51 Go to previous messageGo to next message
John Watson
Messages: 4385
Registered: January 2010
Location: Global Village
Senior Member
Quote:
throw away TOAD

Not much chance of persuading Quest to buy advertising space here now!
Re: sql library miss rate (2 Merged) [message #500303 is a reply to message #500285] Sat, 19 March 2011 01:52 Go to previous messageGo to next message
Littlefoot
Messages: 19315
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I *think* (which means I'm not sure) that TOAD is not to be blamed here. Quest made both TOAD and Spotlight, but these pieces of software are not the same. It means that Michel should have said "Throw Spotlight away".

I saw Spotlight. It looks very interesting, all those colors changing from green to yellow to red, displaying different information ... cool (if I take it as a screensaver, as I'm not the one to judge whether it is useful or not).

As of TOAD, well, I find it useful and I wouldn't want to throw it away.
Re: sql library miss rate (2 Merged) [message #500307 is a reply to message #500303] Sat, 19 March 2011 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I said TOAD because OP said toad spotlight, as I don't these tools, I just thought it is a feature of TOAD but it does not change my opionion.

Is it useful?
For those who could understood this "sql library miss rate" it would be useful ONLY if the underlying query is given. Who has not the same kind of stuff in his scripts box?
For those who can't understand it anyway, it is just harmful, generating useless mail traffic in entreprise and more work for overloaded DBA.

Good idea to make it as screensaver, maybe I will have a look. Wink

Regards
Michel

[Updated on: Sat, 19 March 2011 10:28]

Report message to a moderator

Re: sql library miss rate (2 Merged) [message #500323 is a reply to message #500307] Sat, 19 March 2011 08:43 Go to previous messageGo to next message
Littlefoot
Messages: 19315
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe that it is far too expensive to use it just as a screensaver ./fa/1600/0/
Re: sql library miss rate (2 Merged) [message #500476 is a reply to message #500323] Mon, 21 March 2011 10:41 Go to previous messageGo to next message
odbtools
Messages: 3
Registered: March 2011
Location: Dallas, Tx
Junior Member
It will be useful to know how the rate is calculated.

If it is related to the library cache hit ratio then use the following query to calculate the ratio:

select 100 * (1- (sum(pins - reloads)) / sum(pins))) from v$librarycache

In this case the ratio should be less than 1%, otherwise increase your shared pool size.

Michel

[Updated on: Mon, 21 March 2011 11:20] by Moderator

Report message to a moderator

Re: sql library miss rate (2 Merged) [message #500482 is a reply to message #500476] Mon, 21 March 2011 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In this case the ratio should be less than 1%, otherwise increase your shared pool size.

Laughing
Back 15 years behind, in the good old V7 years.

Regards
Michel
icon7.gif  Re: sql library miss rate (2 Merged) [message #500484 is a reply to message #500482] Mon, 21 March 2011 13:03 Go to previous messageGo to next message
odbtools
Messages: 3
Registered: March 2011
Location: Dallas, Tx
Junior Member
I am glad, someone remember old version 7. The truth is nothing really have changed that much in Oracle, except the envelop. Yes, I used it in Oracle 8 and I am using it in Oracle 11, still works.
The guy needs an "urgent" help, so let help him. Laughing

Michel

[Updated on: Mon, 21 March 2011 13:05] by Moderator

Report message to a moderator

Re: sql library miss rate (2 Merged) [message #500485 is a reply to message #500484] Mon, 21 March 2011 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The truth is nothing really have changed that much in Oracle, except the envelop.

The truth is that you know nothing about Oracle internals to say that.

Quote:
I am using it in Oracle 11, still works.

What works? The query? Of course, the syntax is correct, the object still exist, you get some values, that's all.

Quote:
The guy needs an "urgent" help, so let help him.

This can of help does not help, it is just misleading.

Regards
Michel




Correction: the syntax is not correct:
SQL> l
  1* select 100 * (1- (sum(pins - reloads)) / sum(pins))) from v$librarycache
SQL> /
select 100 * (1- (sum(pins - reloads)) / sum(pins))) from v$librarycache
                                                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected



[Updated on: Mon, 21 March 2011 13:11]

Report message to a moderator

Re: sql library miss rate (2 Merged) [message #500487 is a reply to message #500485] Mon, 21 March 2011 13:42 Go to previous messageGo to next message
odbtools
Messages: 3
Registered: March 2011
Location: Dallas, Tx
Junior Member
It seems your knowledge is more based on reading books and googling than real life experience.

I know your are the moderator for many years, It doesn't give the right to be rude.

[Edit MC: Advert to his tool, removed, please post your advert in Marletplace forum]

What do you have to show for?

And be brave and keep it posted. .....


Syntax: select 100 * (1- (sum(pins - reloads)) / sum(pins)) from v$librarycache;

Thanks

Michel.

[Updated on: Mon, 21 March 2011 13:45] by Moderator

Report message to a moderator

Re: sql library miss rate (2 Merged) [message #500488 is a reply to message #500487] Mon, 21 March 2011 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It seems your knowledge is more based on reading books and googling than real life experience.

I don't think so, this is why I know that internals are very different in 10g/11g than in 7.
And this is why I know that giving "here's a query if result is below/above then do XXX" is almost certainly wrong.
The meaning of the value depends on so many things there is no "if value > then" silver bullet.
If your tool is based on such very old myths then it is better for people to stay away.
When in version 7, we only had these statistics, the first thing we did was to use UTLBSTAT/ESTAT on meaningful periods and not take the values from the beginning of the instance.

Regards
Michel

[Updated on: Mon, 21 March 2011 13:57]

Report message to a moderator

Re: sql library miss rate (2 Merged) [message #500673 is a reply to message #500488] Tue, 22 March 2011 14:29 Go to previous message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
odbtools wrote on Mon, 21 March 2011 19:42
...
And be brave and keep it posted. .....
...

Previous Topic: SQL Tuning ...
Next Topic: why db need kill session instantly
Goto Forum:
  


Current Time: Sat Jul 26 12:29:44 CDT 2014

Total time taken to generate the page: 0.19975 seconds