Oracle Logbook

July 15, 2008

How Adjust Open_cursors on Oracle Database

Filed under: Uncategorized — heliodias @ 7:56 pm

First you need to know how many cursors are you session using at maximum

select max(value) from V$sesstat natural join v$statname
where name = ‘opened cursors current’;
Then you can give some safe e.g. 30% and do an
alter system set open_cursors=&New_value_plus_20pct;
Select amount,Opened_cursos_rounded*10+10 from
(select trunc(value/10) Opened_cursos_rounded,count(*) Amount from V$sesstat natural join v$statname
where name = ‘opened cursors current’
group by trunc(value/10) order by 1);

    AMOUNT OPENED_CURSOS_ROUNDED*10
———- ————————
        35                        0
        59                       10
        10                       20
         3                       30
         2                       40
         1                       60
         1                       70
         1                       80
         1                      130
         1                      180
         1                      190
         1                      200
         1                      220
         2                      230

14 rows selected.
On my example you can see that most of sessions are using just 10 cursors, but my max are at 230.
So put a limit on 300 is sounds good.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: