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

0 comments:
Post a Comment