Re: What would happen if..?

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 ... Received on Fri Mar 14 2008 - 17:32:54 CDT