library cache miss ratio very high - how to find out the rot cause
Frank Zimmer
we are driving a Documentum based ECM system on a big sun machine (12
CPU) (i know that Documentum is creating very bad SQL)
Right now sometimes (quite often) the system is totally slow.
With some monitoring we can see that at that point the miss ratio
for the sql library cache goes over 60 %.
At that time the shared SQL area of the SGA is used only by 60 % and
the parse to execute ratio is nearly one.
How can i get deeper into that ?
Best regards
Frank
Vladimir M. Zakharychev
What kind of activity goes on in the application when you observe
these symptoms? This may give a better clue on how to approach the
problem.
It's quite likely that the application floods the server with SQL
statements with literals, and it most probably does not use array
interface (for example, they insert 10 rows using 10 separate
statements, each with unique set of literal arguments - this results
in 10 hard parses and 10 new statements in the shared pool that will
never be reused. The most efficient way to do this would be to prepare
single insert statement with bind variables, bind array of arguments
to it and execute this 10 row insert in one call. Less efficient (but
sometimes the only available, like with Thin JDBC,) approach would be
to execute the prepared statement 10 times with different variable
values.)
You can try to set CURSOR_SHARING=SIMILAR to let Oracle attempt to
"auto-bind" statements with literals and reduce the pressure on the
shared pool and CPUs, but do this with caution and extensively test
this feature before implementing it in production as it was known to
produce bizarre side effects sometimes.
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Mladen Gogala
First and foremost, is your performance suffering, are your users
complaining? If they are, address the problem. To do that, see what
are the user processes waiting for. In my experience, high library cache
miss ratio is a sign of intense parsing. On the other hand, I haven't seen
any statspack or AWR report, I haven't seen any logs and I have had no
insight into your database so I can't really give you an answer. I don't
even know the version of your database.
--
Mladen Gogala
http://mgogala.freehostia.com
Frank Zimmer
Hi,
we are running 10.2.0.3 on Solaris 10
But my question was more related to what kind of possibilities
i have to find out the root cause.
I still have seen heavy parsing but i'd like to know what sql statements
there are running inside a given timeframe to see how to make them better.
Also the AWR does not show some useful details, or is there a way to
make this report more detailed ?
Regards
Frank
Helma
Hi Frank,
You can check the statspack (and TOP SQL) if you want to know more
about the sql in play during slow periods.
Be careful to see the hitratio as a representative of the performance
problem. I have seen cases where the hitratio can be improved
considerably, but only at the expense of *worse* performance. So you
need to look for the root causes of your slow system, not of your low
hitratio. There may be a connection between these two.
As Vladimir pointed out, it's likely that bind variables aren't used.
To Vladimir: What are the 'bizarre side effects' you have seen from
the CURSOR_SHARING=SIMILAR ? I haven't seen anything yet from my
experience.
Mark D Powell
>
> - Show quoted text -
Frank,
1 - Use of the AWR requires a tuning pack license.
2 - Statspack and the traditional v$ dynamic performance views are
free and can show you all current SQL. In the case of statspack it
will save the "top" sql which you can query directly or see in the
reports. Look at the statspack levels available to you.
Failure to use bind variables and heavy flushing of the SGA to make
room for more SQL can lead to the problem identified.
HTH -- Mark D Powell --
joel garry
> Hi
>
> we are driving a Documentum based ECM system on a big sun machine (12
> CPU) (i know that Documentum is creating very bad SQL)
> Right now sometimes (quite often) the system is totally slow.
> With some monitoring we can see that at that point the miss ratio
> for the sql library cache goes over 60 %.
> At that time the shared SQL area of the SGA is used only by 60 % and
> the parse to execute ratio is nearly one.
>
> How can i get deeper into that ?
>
> Frank
Just out of curiosity I put the following query into google:
documentum oracle queries
I notice a couple of informative links, seems this Robin East fellow
knows some things, including SQL profiles and getting sets results.
jg
--
@home.com is bogus.
Oracle v. SAP: http://www.itbusiness.ca/it/client/en/home/news.asp?id=48054
Vladimir M. Zakharychev
We've hit a couple of bugs associated with ancillary operators (like
CONTAINS()/SCORE() in Oracle Text, where mandatory literals were being
replaced with bind variables and the whole thing stopped working,) but
that was in our early 9.2 days and those particular bugs are long time
fixed. Also, SIMILAR assumes the histograms are available and current,
so if you blindly set it and don't gather all needed stats, you may
not get what you expect.
Regards,
Helma
Bolshoi spasibo, Vladimir :)
Mladen Gogala
> 1 - Use of the AWR requires a tuning pack license.
That is true. Unfortunately, statspack is slowly going out
and AWR is not free. I hope that Oracle is making enough money
from the diagnostic pack to offset the ill will and annoyance
created by charging for something that used to be free. You
see, AWR is just the next release of statspack. Oracle saw that
it was good and popular and decided to start charging for it.
In essence, they took the good free product away. This sort of
thing used to be happening with DEC, too.
When DEC went overboard with DECStation and Alpha chips, I
started recommending ISC (an old brand of Intel Unix, no longer
available) and 486.
Now, I am recommending people to use PgSQL for all small databases
which do not need high availability and 7x24 availability. That is
probably the best way to save some money for the diagnostic pack.
PgSQL can do everything that Oracle 7.3 was capable of and more.
It is perfectly sufficient for office databases with up to 200
simultaneous active users. PgSQL 8.1 has tablespaces, bitmap indexes,
good statistics optimizer and quite a nice subset of dynamic performance
tables. It's not nearly as complete as Oracle's but it's getting there.
And it costs infinitely less then Oracle.
DA Morgan
> On Tue, 22 Apr 2008 07:27:05 -0700, Mark D Powell wrote:
>
>
>> 1 - Use of the AWR requires a tuning pack license.
>
> That is true. Unfortunately, statspack is slowly going out
> and AWR is not free. I hope that Oracle is making enough money
> from the diagnostic pack to offset the ill will and annoyance
> created by charging for something that used to be free. You
> see, AWR is just the next release of statspack.
StatsPack is no more going away than ANALYZE TABLE, DBMS_JOB,
and the LONG RAW data type.
Nor do I detect any ill will or annoyance anywhere other than
in this group. Those that want to you StatsPack will likely be
doing so until the day they retire from our industry.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Mladen Gogala
> Nor do I detect any ill will or annoyance anywhere other than in this
> group.
As a consultant, I do detect annoyance with the fact that AWR is
not free. And yes, it still works. One has to delete snapshots manually
and there is no nice way of getting them displayed by the browser.
However, it is not my intention to start a flame war or advertize PgSQL.
We're all adults and can judge for ourselves.
In my opinion, the best way to deal with the issue would be to extend
the Statspack package to be able to clean up itself and to allow for
an easy browsing. Believe it or not, I am writing just such a package.
I'll put it on my web page when I am done. I already resolved cleanup,
it will be done by a job. I am still in the planning phase for the
pipelined function to display the results in the web browser, but I will
get there relatively soon. Essentially, all that I need to do is to copy
the output from spreport to a PL/SQL table and display the results.
Statspack will ride again, that much I can promise.
joel garry
> Mladen Gogala wrote:
> > On Tue, 22 Apr 2008 07:27:05 -0700, Mark D Powell wrote:
>
> >> 1 - Use of the AWR requires a tuning pack license.
>
> > That is true. Unfortunately, statspack is slowly going out
> > and AWR is not free. I hope that Oracle is making enough money
> > from the diagnostic pack to offset the ill will and annoyance
> > created by charging for something that used to be free. You
> > see, AWR is just the next release of statspack.
>
> StatsPack is no more going away than ANALYZE TABLE, DBMS_JOB,
> and the LONG RAW data type.
>
> Nor do I detect any ill will or annoyance anywhere other than
> in this group. Those that want to you StatsPack will likely be
> doing so until the day they retire from our industry.
You haven't looked. I am not the only one who thinks AWR should be
free, commie-pinko-socialist as that may sound, and many more think it
should be available to SE customers, some to the point of writing open
letters to Larry and starting online petitions. I can't think of any
commercial soft wares that have not been obsolete when declared
"stable." (I'm not using any funny economics definition of obsolete, I
mean the one generally used in this context).
jg
--
@home.com is bogus.
Mladen Gogala
> You haven't looked. I am not the only one who thinks AWR should be
> free, commie-pinko-socialist as that may sound, and many more think it
> should be available to SE customers, some to the point of writing open
> letters to Larry and starting online petitions. I can't think of any
> commercial soft wares that have not been obsolete when declared
> "stable." (I'm not using any funny economics definition of obsolete, I
> mean the one generally used in this context).
Well, that's going to change. Nobody has commented on the elephant in
the room and that would be the fact that Google, as one of the largest and
most aggressive high-tech companies in the world, is not using Oracle.
So, there is life without Oracle. I learned PgSQL and I am not sorry. I
can even sell consulting for conversion of small databases from Oracle to
PgSQL.
With a problem like Google, Oracle doesn't need disloyalty and ill will
among the consultants who used to be its loudest proponents and the most
effective sales force. I only hope that Google will adopt PgSQL and renew
the sense of competition among the databases.
joel garry
Funny about that google. I was poking about last night on the Brad
Templeton remembrances about spam (30th anniversary Thursday!) and the
early days of usenet. I was kind of bummed that some of the links he
posted were bad. Eventually I landed on the google page that has
links to memorable early posts they had posted when they first
absorbed dejanews, and was a bit surprised to find they didn't work
either. Trying them several times, I would get different error
messages like page does not exist, message such-and-such doesn't
exist, then suddenly they would. I'm guessing they have a gradation
of off-line-ed-ness, and don't really handle the error messages of the
various changing states correctly. Or maybe they're just stupid.
Since they are giving the messages, the messages change, it's their
links, and it sometimes doesn't work and then does (without me
emptying cache or anything), I can't see how it possibly can be
something I'm doing wrong.
Remember that the next time someone wants to do something important
with some competition-enhancing db. Someone posted on comp.risks
about getting their neighbors tax info from some online tax software,
and I've posted somewhere about getting and changing someone else's
online Oracle DBA resume (not a domain/IP sharing issue, they were on
a different ISP). Un-freaking-believable is all I can say.
Doesn't anybody have any definitive knowledge on what google uses for
their financials? Could it be... http://storagemojo.com/2007/07/27/508/
the cheapest Oracle implementation ever?
jg
--
@home.com is bogus.
Matthias Hoys
>
> Well, that's going to change. Nobody has commented on the elephant in
> the room and that would be the fact that Google, as one of the largest and
> most aggressive high-tech companies in the world, is not using Oracle.
> So, there is life without Oracle. I learned PgSQL and I am not sorry. I
> can even sell consulting for conversion of small databases from Oracle to
> PgSQL.
> With a problem like Google, Oracle doesn't need disloyalty and ill will
> among the consultants who used to be its loudest proponents and the most
> effective sales force. I only hope that Google will adopt PgSQL and renew
> the sense of competition among the databases.
>
> --
> Mladen Gogala
> http://mgogala.freehostia.com
I think MySQL and EnterpriseDB will be slowly taking Oracle database's
market share in the following years ...Both are become vey mature products.