> 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>
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 ?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment