I am an IT professional with 10 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 ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

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> 

No comments:

Followers

About Me

My photo
Melbourne, Victoria, Australia