Tuning open cursors

Open cursors
Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.
OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.
The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. web applications that have dozens to hundreds of users sharing a pool of sessions. Tom Kyte recommends setting it around 1000.

Session cached cursors
There are two main initialization parameters that affect cursors, and many folks get them confused. One is OPEN_CURSORS, and the other is SESSION_CACHED_CURSORS.
SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. You can set SESSION_CACHED_CURSORS to higher than OPEN_CURSORS, lower than OPEN_CURSORS, or anywhere in between. This parameter has no effect on ora-1000's or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached. There's no relationship between the two parameters.
If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.)
In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.

Why cache cursors?
The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times. This is especially so for applications like Oracle Forms applications, where switching from one form to another will close all the session cursors opened for the first form. Switching back then opens identical cursors. So caching cursors by session really cuts down on reparsing.
There's another advantage, though. Since a session doesn't have to go looking in the library cache for previously parsed SQL, caching cursors by session results in less use of the library cache and shared pool latches. These are often points of contention for busy OLTP systems. Cutting down on latch use cuts down on latch waits, providing not only an increase in speed but an increase in scalability.

Monitoring open cursors
I believe a lot of the confusion about open cursors vs. cached cursors comes from the names of the Oracle dynamic performance views used to monitor them. v$open_cursor shows cached cursors, not currently open cursors, by session. If you're wondering how many cursors a session has open, don't look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.
To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session:--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
If you're running several N-tiered applications with multiple webservers, you may find it useful to monitor open cursors by username and machine:
--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;

Tuning OPEN_CURSORS
The best advice for tuning OPEN_CURSORS is not to tune it. Set it high enough that you won't have to worry about it. If your sessions are running close to the limit you've set for OPEN_CURSORS, raise it. Your goal in tuning this parameter is to set it high enough that you never get an ora-1000 during normal operations.
If you set OPEN_CURSORS to a high value, this doesn't mean that every session will have that number of cursors open. Cursors are opened on an as-needed basis. And if one of your applications has a cursor leak, it will eventually show up even with OPEN_CURSORS set high.
To see if you've set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS.
SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
2> from v$sesstat a, v$statname b, v$parameter p
3> where a.statistic# = b.statistic#
4> and b.name = 'opened cursors current'
5> and p.name= 'open_cursors'
6> group by p.value;
HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
1953 2500
After you've increased the value of OPEN_CURSORS, keep an eye on v$sesstat to see if opened cursors current keeps increasing for any of your sessions. If you have an application session whose opened cursors current always increases to catch up with OPEN_CURSORS, then you've likely got a cursor leak in your application code: your application is opening cursors and not closing them when it's done.
There is nothing you, as a DBA, can do to fix a cursor leak. The application developers need to go through the code, find the cursors that are being left open, and close them. As a stopgap, the most you can do is raise OPEN_CURSORS very high and schedule times when all the application sessions will be closed and reopened (eg. by kicking the webserver).

Turkish ı/İ problem with Oracle DB

In the last blog dated april 24, 2008 http://techsouls.blogspot.com/2008/04/turkish-capital-i-i-with-dot-is-without.html, I wrote about the Turkish ı/İ problem and solution with MSSQL DB. Same problem on oracle DB is solved in a little different way. Take a look at the following statement.
NLS_UPPER(NAME, 'NLS_SORT=XTURKISH') like UPPER('iword%', NLS_SORT=XTURKISH')
Solution is to use NLS_UPPER for oracle DB, oracle has provided this api which works just fine provided that a sorting order is provided (XTURKISH in above case).

Collecting crash dump on windows

Steps to capture Microsoft crash dump.

Pre-requisite
1) Machine should have ‘Debugging tools for windows’ installed, In this is not installed please download and install from following url: http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx
PS: The above package will install windbg, please note the path of windbg.exe we will use this to capture the crash dump.
Steps
1) Open registry using regedit.
2) Open following key “HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\WINDOWS NT\AeDebug\”
3) Take the backup of the AeDebug registry key to restore your original settings once crash dump is captured.
4) To take backup right click on AeDebug and select ‘Export’ Menu option, specify the file name where this key will be exported.
5) Set the AeDebug\Auto key value to 1.
6) Set the Debugger key value to "<>" -p %ld -e %ld -g -c ".dump /mfa c:\\dump%ld.dmp;q".
PS: Replace the angular bracket value with your actual windbg path.
7) On crash windbg will start and generate the crash dump file at c:\ with dump*.dmp name.
8) Restore your original settings by double clicking the file which we exported earlier.

Turkish capital İ (İ with dot) is ı (ı without dot) characters

Problem: To support Turkish capital İ (İ with dot) is ı (ı without dot) characters while fetching the strings with those two characters from MSSQL database while doing case insensitive query. Problem comes because Turkish has got different meaning altogether to the i and I. Upper case of i is İ and upper case of ı is I. So, the normal upper functions doesnt work correctly. The correct collation should be used as explained below.

Solution: Idea is to use TURKISH_CI_AI collation to get the expected results. Queries run on a standard MSSQL database are as below. What I have done is created a TEST table and inserted the different string with two english i/I and two turkish ı/İ using following commands and then ran few select queries to get the correct results.

create table dbo.TEST(row int, f1 nvarchar(10));
insert into dbo.TEST values(1,N'AIr');
insert into dbo.TEST values(2,N'aIr');
insert into dbo.TEST values(3,N'air');
insert into dbo.TEST values(4,N'Air');
insert into dbo.TEST values(5,N'Aİr');
insert into dbo.TEST values(6,N'aİr');
insert into dbo.TEST values(7,N'aır');
insert into dbo.TEST values(8,N'Aır');

select * from dbo.TEST where f1 like (N'aı%' collate Turkish_CI_AI)
1 AIr
2 aIr
7 aır
8 Aır
select * from dbo.TEST where f1 like (N'aI%' collate Turkish_CI_AI)
1 AIr
2 aIr
7 aır
8 Aır
select * from dbo.TEST where f1 like (N'ai%' collate Turkish_CI_AI)
3 air
4 Air
5 Aİr
6 aİr
select * from dbo.TEST where f1 like (N'aİ%' collate Turkish_CI_AI)
3 air
4 Air
5 Aİr
6 aİr

Please note that the character ‘N’ has to be used on the value side while doing a select so that we get correct results while doing query on (ı and İ). If ‘N’ is not appended, the results are not inline with expectation.

e.g.
select * from dbo.TEST where f1 like ('aı%' collate Turkish_CI_AI)
3 air
4 Air
5 Aİr
6 aİr
select * from dbo.TEST where f1 like ('aİ%' collate Turkish_CI_AI)
1 AIr
2 aIr
7 aır
8 Aır


Now, considering the case of a database which has entries containing all four I’s (two Turkish and two English)
select * from dbo.TEST where (upper(f1) like upper(N'ai%'))
1 AIr
2 aIr
3 air
4 Air
7 aır
8 Aır
‘aır’ and ‘Aır‘ in above results are not expected ones. To resolve the above issue SQL_Latin1_General_CP1_CI_AS collation need to be used even for non Turkish OMs when the database have all the four types of I’s.
e.g.
select * from dbo.TEST where (f1 like upper(N'ai%' collate SQL_Latin1_General_CP1_CI_AS))
1 AIr
2 aIr
3 air
4 Air

Alternate Solutions Considered and Dropped:
There were other solutions as well which were proposed and thought not to be implemented for the reasons mentioned below each solution.
Solution 1 -
select * from dbo.TEST where (f1) like ('aı%' COLLATE SQL_Latin1_General_CP1_CI_AI)
1 AIr
2 aIr
3 air
4 Air
5 Aİr
6 aİr
7 aır
8 Aır
Above queries are returning all the four db values. This solution is not good if customer has millions of records with all types of I(Turkish and English). This will give lot of unwanted results.


Solution 2 –
select * from dbo.TEST where (f1 like UPPER(N'aİ%')) OR (UPPER(f1) like UPPER(N'aı%'))
1 AIr
2 aIr
3 air
4 Air
5 Aİr
7 aır
8 Aır

select * from dbo.TEST where (UPPER(f1) like UPPER(N'ai%')) OR
(UPPER(f1) like UPPER(N'aı%'))OR (UPPER(f1) like UPPER(N'aI%'))
1 AIr
2 aIr
3 air
4 Air
7 aır
8 Aır

UPPER() does support Unicode but it doesn’t work for Tukish I’s because the sorting is done as per the English (i and I). So, the results are returned as per English. e.g. if ‘i’ is used in the query, it gets converted to English upper case I (upper case I without dot) and NOT into Turkish upper case İ with dot. So the results are as per ‘I’ and NOT ‘İ’. For more information, visit http://msdn2.microsoft.com/en-us/library/aa214408(SQL.80).aspx.

Solution 3 –
C++ approach: use IBM ICU library, a generic C++ internationalization library which we already license and ship that can be used by OM to do upper function on the query string. This solution is not feasible because problem is not only on client side but also on server side. So fixing it on client side might still mean the characters get converted at the db side.

I am still trying to find a solution for Oracle DB. Once I get it I'll update it here.

Write to me at anurag.gupta@marketfry.com if you have any ques or feedback.