Re: What would happen if..?

On Mar 14, 6:32 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Mar 14, 12:49 pm, Ben <benal..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Mar 14, 12:32 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
> > > On Mar 14, 12:23 pm, Ben <benal..._at_yahoo.com> wrote:
>
> > > > 10.2.0.2 EE, aix 5.2 64bit
>
> > > > I have seen the metric reported in Enterprise Manager several times
> > > > for "Current Open Cursors Count" on one of our database's home page/
> > > > screen. It's reporting it at 7828. This particular system has about
> > > > 1200 individual sessions logged on during peak hours and drops down to
> > > > about 500 during off hours. OPEN_CURSORS is set to 500 in this
> > > > database.
>
> > > > What I don't understand is exactly why this metric is being reported
> > > > to me and if it is just a default value that it has decided once there
> > > > are x number of open cursors the threshold has been crossed and I'm
> > > > going to raise a warning.
>
> > > > I guess what I'm really asking is, is there some magic number of open
> > > > cursors that I should be worried about, or is it just reporting this
> > > > open cursor count because by default there is a threshold set at 1200
> > > > open cursors that it is at warning status.
>
> > > > Is there some maximum number of open cursors for the entire instance
> > > > that if we reach that number it's going to blow up shoot sparks out of
> > > > the back of the server....
>
> > > Do you understand what an open cursor is and why it has been set to
> > > 500 for the instance you are ( apparently ) supporting?
>
> > honestly there are a lot of settings in our system that I look back on
> > and couldn't begin to explain accurately why they were set to what
> > they are or what the previous dba was thinking when he did something.
> > I do know what cursors are though, but why 500 was decided on the
> > "correct" number, nope, no idea, I wasn't part of that decision making
> > process. We had layoffs a few years back and our sole dba was one of
> > them, so now I'm sole dba and the past dba didn't really keep notes.
>
> > > Since there are no silver bullets there also are no magic numbers
> > > here.
>
> > didn't think so. I'm assuming 1200 was the default number set for the
> > metric in oem. It doesn't hurt to ask for others input though.
>
> > > Well except for if you have a session that needs more open cursors
> > > than your instance is configured to support well that can be something
> > > that needs looked at and adjusted.  That's similar to the
> > > consideration for how many processes your instance needs to support.- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> Well I guess the one shot in the dark approach might be to take 75 or
> 80 percent of your limit of open curors ( say 400 then ) and multiply
> it by a reasonable number of maximum open sessions ( did you sy
> 1200? ) ... so perhaps make it 400 * 1200 which is substantially above
> the number you noted (  seven thousand odd if I remember ).
>
> Is that way high for a number to be worried about?  Maybe ... how big
> is your SGA ans shared pool?
>
> Most sessions hopefully won't use anywhere close to the maximum number
> of open cursors or even 75 percent of them.  So maybe you use a 50
> percent threshold ...- Hide quoted text -
>
> - Show quoted text -

we have a 10.5 G sga, of which about 25% is made up of the shared pool. Thanks for the pointers on trying to come up with a reasonable alert limit. Received on Mon Mar 17 2008 - 07:13:01 CDT