RE: Shared Pool Size

Shuan - Thanks for explaining the Chinese character. Let me make it clear that I am only a novice where wait statistics are involved, although it solved 2 ugly problems for me yesterday very quickly. And the buffer hit ratios were really great during both situations.

   I would highly recommend the book Oracle Performance 101 by Gaja Krishna Vaidyanatha and Kirtikumar Deshpande. This book does an excellent job of explaining the fundamentals. The web sites http://www.hotsos.com and http://www.orapub.com have excellent white papers.

   As far as specific wait events and what they mean, besides the above resources, a friendly beginner's guide is http://www.interealm.com/roby/technotes/wait_events.html

   To see if someone installed STATSPACK on your system, I would SELECT USERNAME FROM DBA_USERS WHERE USERNAME = 'PERFSTAT'; I believe the spcreate.sql script (that is the 9i name) doesn't offer an option on the username it uses to create the STATSPACK user.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, December 12, 2002 9:00 PM To: Multiple recipients of list ORACLE-L

Dennis,

The Toad provides "Waits"...
but there's a lot of events like "buffer busy waits","control file parallel write","control file sequential read",..... Can you point me to the correct direction or suggest some informations about how to analyse those events?

For STATSPACK, how to tell whether it's installed? and which scripts should be run in order to use it?

Well....yes, i'm working for a taiwanese company, they insist to include Chinese name in signature. sorry for the inconvenience cause.
and thanks for the help.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, December 02, 2002 9:38 PM

> Shuan - Bumping up the memory for shared pool and database buffers may
well
> turn out to be the answer, but before you just jump in and make the
changes
> and hope for the best, why not dig into the root causes of the slowness
> first? Specifically, collect information on the top wait events. I'm not
> familiar with Toad, but perhaps it can provide these. My personal favorite
> is STATSPACK, which comes with Oracle, but you'll have to install it. Or
you
> can just directly query the V$ views. If the wait statistics confirm the
> need to increase these buffers, once you make the change you can measure
> these statistics again and confirm that you are making a positive
> improvement.
> Also, does your signature have some sort of foreign language font?
When
> I opened your email I got an error message about that.
>
>
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?big5?B?c2h1YW4udGF5XChQQ0m+R7hSs9RcKQ==?=
  INET: shuan.tay_at_pci.co.id

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).