Friday, June 6, 2008

Look for cursors which are open

When you perform tests, there is a basic query you can use which will look for cursors which are open. You will want to use this:

select s.SQL_TEXT , count(*) from v$sql s, v$open_cursor oc
where oc.hash_value = s.hash_value
group by s.SQL_TEXT having count(*) > 1

select s.SQL_TEXT , USER_NAME, count(*) from v$sql s, v$open_cursor oc
where oc.hash_value = s.hash_value and USER_NAME='EPSO'
group by s.SQL_TEXT,USER_NAME having count(*) > 1

The query will tell you exactly which cursors are open. If certain cursors are not closed, the count for these queries will continue to increase. After you've found the query, fixing the code will be simple. The next thing you will want to pay attention to is CPU queries. One feature you can use to look for CPU queries is called the V$sqlarea view. It features a number of columns which will allow you to find queries which are memory intensive. The CPU_TIME feature will determine the number of microseconds that the SQL uses for parsing and execution.

Don't remember original source of this info :-(, I hope author will forgive me