I am an IT professional with 7 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community
http://forums.oracle.com/forums/search.jspa?q=khurram&objID=f61&dateRange=all&forumID=61&rankBy=9&start=0
If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com or call me at +923333568520

Friday, May 9, 2008

how it can improve the performance of db if i make open_cursor to 500 ?

> how it can improve the performance of db if i make open_cursor to 500 ?

It will not  increase the performance by increasing open_cursor value rather it shows how yours application  can open the cursor within a session,there is no relation of open_cursor parameter to performance directly.Nevertheless If you enable the session 
cached_cursor then it will let you show the cached cursor which will remain throught yours session and will imporve 
performance.

SQL>  SHOW PARAMETER session_cached_cursors

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------------------------
session_cached_cursors               integer                          0

SQL> SELECT sql_text
  2   FROM v$open_cursor
  3  WHERE sql_text LIKE '%KHURRAM%'
  4  /

no rows selected

SQL> DECLARE
  2    v  NUMBER:=7566;
  3    CURSOR c1 IS SELECT * FROM emp KHURRAM;
  4    c2     c1%ROWTYPE;
  5  BEGIN
  6   OPEN c1;
  7    LOOP
  8     FETCH c1 INTO c2;
  9     EXIT WHEN c1%NOTFOUND;
 10    END LOOP;
 11   CLOSE c1;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT sql_text
  2   FROM v$open_cursor
  3  WHERE sql_text LIKE '%KHURRAM%'
  4  /

no rows selected


SQL> ALTER SESSION SET session_cached_cursors=100
  2  /

Session altered.

SQL> SHOW PARAMETER session_cached_cursors

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------------------------
session_cached_cursors               integer                          100
SQL> SELECT sql_text
  2   FROM v$open_cursor
  3  WHERE sql_text LIKE '%KHURRAM%'
  4  /

no rows selected

I am running the 3 times cause oracle checks library cache to  determine whether more than 3 parse requests have been 
issued on a given statement.if so,then oracle assumes that the session cursor associated with the statement should be 
cached and moves the cursor into session cursor cache.

SQL> DECLARE
  2    v  NUMBER:=7566;
  3    CURSOR c1 IS SELECT * FROM emp KHURRAM;
  4    c2     c1%ROWTYPE;
  5  BEGIN
  6   OPEN c1;
  7    LOOP
  8     FETCH c1 INTO c2;
  9     EXIT WHEN c1%NOTFOUND;
 10    END LOOP;
 11   CLOSE c1;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT sql_text
  2   FROM v$open_cursor
  3  WHERE sql_text LIKE '%KHURRAM%'
  4  /

SQL_TEXT
------------------------------------------------------------
SELECT * FROM EMP KHURRAM

SQL> 

0 comments:

Followers

About Me

My Photo
Khurram Siddiqui
Karachi, Sindh, Pakistan
View my complete profile