|
|
|
|
|
|
| Re: sql library miss rate (2 Merged) [message #500283 is a reply to message #500263] |
Fri, 18 March 2011 16:23   |
John Watson
Messages: 8930
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 #500307 is a reply to message #500303] |
Sat, 19 March 2011 02:04 |
 |
Michel Cadot
Messages: 68643
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. 
Regards
Michel
[Updated on: Sat, 19 March 2011 10:28] Report message to a moderator |
|
|
|
|
|
| Re: sql library miss rate (2 Merged) [message #500476 is a reply to message #500323] |
Mon, 21 March 2011 10:41 |
 |
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 #500485 is a reply to message #500484] |
Mon, 21 March 2011 13:10 |
|
Michel Cadot
Messages: 68643
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 |
|
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 |
|
Michel Cadot
Messages: 68643
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
