Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
Hi! Low _row_cache_cursors might be causing someof softparsesyou have, especially with Apps where we have lots of complex PL/SQL and really lots of different objects. Maybe you should increase your _row_cache_cursors parameter, but check http://www.ixora.com.au/tips/tuning/row_cache_cursors.htmfirst. Also, have you thought about pinning frequently used-objects. This script: $AD_TOP/sql/ADXCKPIN.sql should give you a list of objects you should pin (You can use $AD_TOP/sql/ADXGNPIN.sql and ADXSPPNS.sql for generating the pinning scripts afterwards). Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Monday, December 01, 2003 5:14 PM Subject: Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute CURSOR_SPACE_FOR_TIME is FALSE.This is an Oracle Apps R11 install.HemantAt 05:29 AM 30-11-03 -0800, you wrote: What's the value for your cursor_space_for_time parameter?Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 Running StatsPack during a PEAK period and then analyzing the output at oraperf.com, I get : 33409 parses (673 hard parses), 498516 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.31 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 508 sessions logged on and at the end of the timing interval 0 more sessions where active. The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted in reducing the parse count from 32736 to 22550 During parsing 276280 msec of CPU were used and 1134430 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch 96% of the latch wait time is on the Library Cache Latch [85% of the Response Time was Wait Time, 71% of the Wait Time was Latch Wait time and 96% of the Latch Wait Time was Library Cache Latch, . this Wait Time analysis really does make sense !] Hemant At 10:14 PM 29-11-03 -0800, you wrote: I thought the session_cached_cursors is dynamic and scope is session? This is on 8.1.7. I have used: alter session set session_cached_cursors=500; -Original Message- Sent: Sunday, November 30, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Sami, 'cached_cursors' is not a valid hint, at least not in 9i. Or at least, I can find no reference to it. And 'cached cursors' as it appears in the SQL is not a valid hint syntax. You need to set the session_cached_cursors value in the init.ora, and bounce the database. This parameter cannot be set dynamically, at least as of 9i. Jared On Sat, 2003-11-29 at 14:44, Sami wrote: Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
Jonathan, I've understood that when cursor_space_for_time is true, then unpin is only done when cursor is closed, thus there's no need for pinning/unpinning for every execution of a cursor. This should reduce hits on library cache latches since pinning is not done so often? Hermant, I've sometimes seen this parameter recommended when having library cache latching issues in large Apps installations, I have not used it myself in Apps though. Also note, that cursor_space_for_time requires 50-100% larger shared_pool (and some more private SQL area in PGA, shared_pool or large_pool, depending on configuration), since shared cursor's frames can't be aged out from library cache until all corresponding cursors are closed (normally if there's not enough free memory in shared pool when parsing a new statement, some unpinned, but open cursors can be thrown out, but with cursor_space_for_time they can't be). So, if you don't find any better cure and decide to use this parameter, you should first increase your shared pool quite much to avoid ORA-4031 errors and then start reducing in small amounts, based on v$librarycache, v$rowcache, x$kghlu and shared pool/library cache latch wait statistics. It's not good idea to leave shared pool too large, otherwise your memory allocations from there (hard parses for example) will get slow (shared pool latch (or latches in 9i) are kept too long when searching for free/recreatable chunks). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 9:34 PM You still have to hit the library cache to execute a statement as it needs to be pinned in share mode, and unpinned when you finish with it. Library cache latch waits can be a symptom of excessive executions. Have you checked the library cache latch children to see if the load is evenly balanced, or whether there is a single library cache latch that is suffering most of the sleeps. Good news for 9.2 - v$sql, and a couple of others include the library cache child latch number, so you can see which objects are protected by the hot latch without having to use Steve's algorithm. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 1:29 PM What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
Hmm. Yes, I think I need to look at _row_cache_cursors. I do have a number of objects being pinned but rather than using the $AD_TOP scripts I use queries on V$DB_OBJECT_CACHE to identify frequenty executed procedures. Hemant At 12:54 AM 02-12-03 -0800, you wrote: Hi! Low _row_cache_cursors might be causing some of soft parses you have, especially with Apps where we have lots of complex PL/SQL and really lots of different objects. Maybe you should increase your _row_cache_cursors parameter, but check http://www.ixora.com.au/tips/tuning/row_cache_cursors.htm first. Also, have you thought about pinning frequently used-objects. This script: $AD_TOP/sql/ADXCKPIN.sql should give you a list of objects you should pin (You can use $AD_TOP/sql/ADXGNPIN.sql and ADXSPPNS.sql for generating the pinning scripts afterwards). Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Monday, December 01, 2003 5:14 PM Subject: Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute CURSOR_SPACE_FOR_TIME is FALSE. This is an Oracle Apps R11 install. Hemant At 05:29 AM 30-11-03 -0800, you wrote: What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 Running StatsPack during a PEAK period and then analyzing the output at oraperf.com, I get : 33409 parses (673 hard parses), 498516 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.31 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 508 sessions logged on and at the end of the timing interval 0 more sessions where active. The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted in reducing the parse count from 32736 to 22550 During parsing 276280 msec of CPU were used and 1134430 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch 96% of the latch wait time is on the Library Cache Latch [85% of the Response Time was Wait Time, 71% of the Wait Time was Latch Wait time and 96% of the Latch Wait Time was Library Cache Latch, . this Wait Time analysis really does make sense !] Hemant At 10:14 PM 29-11-03 -0800, you wrote: I thought the session_cached_cursors is dynamic and scope is session? This is on 8.1.7. I have used: alter session set session_cached_cursors=500; -Original Message- Sent: Sunday, November 30, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Sami, 'cached_cursors' is not a valid hint, at least not in 9i. Or at least, I can find no reference to it. And 'cached cursors' as it appears in the SQL is not a valid hint syntax. You need to set the session_cached_cursors value in the init.ora, and bounce the database. This parameter cannot be set dynamically, at least as of 9i. Jared On Sat, 2003-11-29 at 14:44, Sami wrote: Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives better response time. But the same time w.r.t latch, session_cached_cursor=100 is giving positive impact. 1) session_cached_cursor=0 - more latches but good response time(2.60) 2) session_cached_cursor=100 - less # of latches but higher response time(2.87) Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME
RE: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
Title: Message Tanel, Where can one get the $AD_TOP scripts? Thanks. Abraham Guerra -Original Message-From: Hemant K Chitale [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 8:54 AMTo: Multiple recipients of list ORACLE-LSubject: Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute Hmm. Yes, I think I need to look at _row_cache_cursors. I do have a number of objects being pinned but rather than using the $AD_TOP scripts I use querieson V$DB_OBJECT_CACHE to identify frequenty executed procedures.HemantAt 12:54 AM 02-12-03 -0800, you wrote: Hi!Low _row_cache_cursors might be causing some of soft parses you have, especially with Apps where we have lots of complex PL/SQL and really lots of different objects.Maybe you should increase your _row_cache_cursors parameter, but check http://www.ixora.com.au/tips/tuning/row_cache_cursors.htm first.Also, have you thought about pinning frequently used-objects. This script: $AD_TOP/sql/ADXCKPIN.sql should give you a list of objects you should pin (You can use $AD_TOP/sql/ADXGNPIN.sql and ADXSPPNS.sql for generating the pinning scripts afterwards).Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Monday, December 01, 2003 5:14 PM Subject: Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute CURSOR_SPACE_FOR_TIME is FALSE. This is an Oracle Apps R11 install. Hemant At 05:29 AM 30-11-03 -0800, you wrote: What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 Running StatsPack during a PEAK period and then analyzing the output at oraperf.com, I get : 33409 parses (673 hard parses), 498516 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.31 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 508 sessions logged on and at the end of the timing interval 0 more sessions where active. The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted in reducing the parse count from 32736 to 22550 During parsing 276280 msec of CPU were used and 1134430 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch 96% of the latch wait time is on the Library Cache Latch [85% of the Response Time was Wait Time, 71% of the Wait Time was Latch Wait time and 96% of the Latch Wait Time was Library Cache Latch, . this Wait Time analysis really does make sense !] Hemant At 10:14 PM 29-11-03 -0800, you wrote: I thought the session_cached_cursors is dynamic and scope is session? This is on 8.1.7. I have used: alter session set session_cached_cursors=500; -Original Message- Sent: Sunday, November 30, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Sami, 'cached_cursors' is not a valid hint, at least not in 9i. Or at least, I can find no reference to it. And 'cached cursors
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
That was my understanding, too. The problem with unpinning only at the specific close is that smon cannot free shared pool memory belonging to the cursor if the cursor is pinned, so the shared pool usage skyrockets. The only way to circumvent the problem is to set CURSOR_SHARING to FORCE. That is also fraught with danger, but what the heck, we are the DBAs, we want to live dangerously. On 12/02/2003 04:59:33 AM, Tanel Poder wrote: Jonathan, I've understood that when cursor_space_for_time is true, then unpin is only done when cursor is closed, thus there's no need for pinning/unpinning for every execution of a cursor. This should reduce hits on library cache latches since pinning is not done so often? Hermant, I've sometimes seen this parameter recommended when having library cache latching issues in large Apps installations, I have not used it myself in Apps though. Also note, that cursor_space_for_time requires 50-100% larger shared_pool (and some more private SQL area in PGA, shared_pool or large_pool, depending on configuration), since shared cursor's frames can't be aged out from library cache until all corresponding cursors are closed (normally if there's not enough free memory in shared pool when parsing a new statement, some unpinned, but open cursors can be thrown out, but with cursor_space_for_time they can't be). So, if you don't find any better cure and decide to use this parameter, you should first increase your shared pool quite much to avoid ORA-4031 errors and then start reducing in small amounts, based on v$librarycache, v$rowcache, x$kghlu and shared pool/library cache latch wait statistics. It's not good idea to leave shared pool too large, otherwise your memory allocations from there (hard parses for example) will get slow (shared pool latch (or latches in 9i) are kept too long when searching for free/recreatable chunks). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 9:34 PM You still have to hit the library cache to execute a statement as it needs to be pinned in share mode, and unpinned when you finish with it. Library cache latch waits can be a symptom of excessive executions. Have you checked the library cache latch children to see if the load is evenly balanced, or whether there is a single library cache latch that is suffering most of the sleeps. Good news for 9.2 - v$sql, and a couple of others include the library cache child latch number, so you can see which objects are protected by the hot latch without having to use Steve's algorithm. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 1:29 PM What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
Mladen, I don't think it's SMON who is coalescing free memory extents. I'm not entirely sure here, but I think if any server process explicitly frees a freeable chunk, then the 16-byte header of immediate next chunk is checked, if this is also free both chunks are coalesced and header of next chunk is checked and so on. When no more adjacent free chunks are found, shared pool freelists are updated. This is called forward coalescing (not to be confused with on-disk segment extent forward coalescing), Ixora also mentions a bit about them. This all is done by the server process who is freeing the chunk, not SMON (SMONs sleep interval is too long for this kind of critical operation anyway). Also, when a process tries to allocate memory from shared pool and there are no sufficiently large free chunks left, then the process goes to shared pool LRU list to find unpinned recreatable chunks and uses callback through the kernel stack to find the owner of the chunk and free it appropriately. When freeing chunk for new allocation like that, here we might also have forward coalescing going on (adjacent free space is coalesced before allocated to new process). Actually, I'm not sure whether this callback is real callback up the kernel stack or is a separate context estabilished for it like Steve Adams describes for data and transaction layer in the beginning of his book. Estabilishing a separate call context for such a low level operation seems quite expensive. If anyone knows about this, please let us know ;) Mladen, another way for circumventing excessive memory usage in shared pool, in addition to cursor_sharing, is to tell TFDs to use bind variables appropriately ;) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 5:49 PM That was my understanding, too. The problem with unpinning only at the specific close is that smon cannot free shared pool memory belonging to the cursor if the cursor is pinned, so the shared pool usage skyrockets. The only way to circumvent the problem is to set CURSOR_SHARING to FORCE. That is also fraught with danger, but what the heck, we are the DBAs, we want to live dangerously. On 12/02/2003 04:59:33 AM, Tanel Poder wrote: Jonathan, I've understood that when cursor_space_for_time is true, then unpin is only done when cursor is closed, thus there's no need for pinning/unpinning for every execution of a cursor. This should reduce hits on library cache latches since pinning is not done so often? Hermant, I've sometimes seen this parameter recommended when having library cache latching issues in large Apps installations, I have not used it myself in Apps though. Also note, that cursor_space_for_time requires 50-100% larger shared_pool (and some more private SQL area in PGA, shared_pool or large_pool, depending on configuration), since shared cursor's frames can't be aged out from library cache until all corresponding cursors are closed (normally if there's not enough free memory in shared pool when parsing a new statement, some unpinned, but open cursors can be thrown out, but with cursor_space_for_time they can't be). So, if you don't find any better cure and decide to use this parameter, you should first increase your shared pool quite much to avoid ORA-4031 errors and then start reducing in small amounts, based on v$librarycache, v$rowcache, x$kghlu and shared pool/library cache latch wait statistics. It's not good idea to leave shared pool too large, otherwise your memory allocations from there (hard parses for example) will get slow (shared pool latch (or latches in 9i) are kept too long when searching for free/recreatable chunks). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 9:34 PM You still have to hit the library cache to execute a statement as it needs to be pinned in share mode, and unpinned when you finish with it. Library cache latch waits can be a symptom of excessive executions. Have you checked the library cache latch children to see if the load is evenly balanced, or whether there is a single library cache latch that is suffering most of the sleeps. Good news for 9.2 - v$sql, and a couple of others include the library cache child latch number, so you can see which objects are protected by the hot latch without having to use Steve's algorithm. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ
Re: Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
what causes memory fragmentation errors? should oracle be able to go to the LRU and start kicking stuff out of memory if there isnt enough space? From: Tanel Poder [EMAIL PROTECTED] Date: 2003/12/02 Tue PM 12:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute Mladen, I don't think it's SMON who is coalescing free memory extents. I'm not entirely sure here, but I think if any server process explicitly frees a freeable chunk, then the 16-byte header of immediate next chunk is checked, if this is also free both chunks are coalesced and header of next chunk is checked and so on. When no more adjacent free chunks are found, shared pool freelists are updated. This is called forward coalescing (not to be confused with on-disk segment extent forward coalescing), Ixora also mentions a bit about them. This all is done by the server process who is freeing the chunk, not SMON (SMONs sleep interval is too long for this kind of critical operation anyway). Also, when a process tries to allocate memory from shared pool and there are no sufficiently large free chunks left, then the process goes to shared pool LRU list to find unpinned recreatable chunks and uses callback through the kernel stack to find the owner of the chunk and free it appropriately. When freeing chunk for new allocation like that, here we might also have forward coalescing going on (adjacent free space is coalesced before allocated to new process). Actually, I'm not sure whether this callback is real callback up the kernel stack or is a separate context estabilished for it like Steve Adams describes for data and transaction layer in the beginning of his book. Estabilishing a separate call context for such a low level operation seems quite expensive. If anyone knows about this, please let us know ;) Mladen, another way for circumventing excessive memory usage in shared pool, in addition to cursor_sharing, is to tell TFDs to use bind variables appropriately ;) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 5:49 PM That was my understanding, too. The problem with unpinning only at the specific close is that smon cannot free shared pool memory belonging to the cursor if the cursor is pinned, so the shared pool usage skyrockets. The only way to circumvent the problem is to set CURSOR_SHARING to FORCE. That is also fraught with danger, but what the heck, we are the DBAs, we want to live dangerously. On 12/02/2003 04:59:33 AM, Tanel Poder wrote: Jonathan, I've understood that when cursor_space_for_time is true, then unpin is only done when cursor is closed, thus there's no need for pinning/unpinning for every execution of a cursor. This should reduce hits on library cache latches since pinning is not done so often? Hermant, I've sometimes seen this parameter recommended when having library cache latching issues in large Apps installations, I have not used it myself in Apps though. Also note, that cursor_space_for_time requires 50-100% larger shared_pool (and some more private SQL area in PGA, shared_pool or large_pool, depending on configuration), since shared cursor's frames can't be aged out from library cache until all corresponding cursors are closed (normally if there's not enough free memory in shared pool when parsing a new statement, some unpinned, but open cursors can be thrown out, but with cursor_space_for_time they can't be). So, if you don't find any better cure and decide to use this parameter, you should first increase your shared pool quite much to avoid ORA-4031 errors and then start reducing in small amounts, based on v$librarycache, v$rowcache, x$kghlu and shared pool/library cache latch wait statistics. It's not good idea to leave shared pool too large, otherwise your memory allocations from there (hard parses for example) will get slow (shared pool latch (or latches in 9i) are kept too long when searching for free/recreatable chunks). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 9:34 PM You still have to hit the library cache to execute a statement as it needs to be pinned in share mode, and unpinned when you finish with it. Library cache latch waits can be a symptom of excessive executions. Have you checked the library cache latch children to see if the load is evenly balanced, or whether there is a single library cache latch that is suffering most of the sleeps. Good news for 9.2 - v$sql, and a couple of others include the library cache child latch number, so you can see which objects are protected by the hot latch without having to use Steve's
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
Steve Adams talks about AST's which are blast from the VMS past. More exactly, AST used to stand for Asynchronous System Trap, and was processed in a similar way like signals on the unix. The CPU IPL was elevated to AST delivery level (IPL 2) and AST was queued on the target process entry. When the process was selected by the scheduler to run again, AST was executed on an elevated IPL, just before the control was passed back to the process. My VMS Internals books (4.4 and 5.2) are buried deep in the cellar and I can no longer vouch for correctness of this. I'm not going to start digging for those books even if you point a gun at me. My question is how does oracle implement AST's? Normal mechanism would be to use signals, but that would make the code vulnerable to all kinds of attacks, because someone could simply queue signals by using sigqueue and see what happens. The only two signals that are user defined are SIGUSR1 and SIGUSR2, which means that if they were used, their delivery should be clearly visible by looking at strace. It's not being the case. I would really, really like to know how does Oracle implement AST's? On 12/02/2003 12:39:26 PM, Tanel Poder wrote: Mladen, I don't think it's SMON who is coalescing free memory extents. I'm not entirely sure here, but I think if any server process explicitly frees a freeable chunk, then the 16-byte header of immediate next chunk is checked, if this is also free both chunks are coalesced and header of next chunk is checked and so on. When no more adjacent free chunks are found, shared pool freelists are updated. This is called forward coalescing (not to be confused with on-disk segment extent forward coalescing), Ixora also mentions a bit about them. This all is done by the server process who is freeing the chunk, not SMON (SMONs sleep interval is too long for this kind of critical operation anyway). Also, when a process tries to allocate memory from shared pool and there are no sufficiently large free chunks left, then the process goes to shared pool LRU list to find unpinned recreatable chunks and uses callback through the kernel stack to find the owner of the chunk and free it appropriately. When freeing chunk for new allocation like that, here we might also have forward coalescing going on (adjacent free space is coalesced before allocated to new process). Actually, I'm not sure whether this callback is real callback up the kernel stack or is a separate context estabilished for it like Steve Adams describes for data and transaction layer in the beginning of his book. Estabilishing a separate call context for such a low level operation seems quite expensive. If anyone knows about this, please let us know ;) Mladen, another way for circumventing excessive memory usage in shared pool, in addition to cursor_sharing, is to tell TFDs to use bind variables appropriately ;) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 5:49 PM That was my understanding, too. The problem with unpinning only at the specific close is that smon cannot free shared pool memory belonging to the cursor if the cursor is pinned, so the shared pool usage skyrockets. The only way to circumvent the problem is to set CURSOR_SHARING to FORCE. That is also fraught with danger, but what the heck, we are the DBAs, we want to live dangerously. On 12/02/2003 04:59:33 AM, Tanel Poder wrote: Jonathan, I've understood that when cursor_space_for_time is true, then unpin is only done when cursor is closed, thus there's no need for pinning/unpinning for every execution of a cursor. This should reduce hits on library cache latches since pinning is not done so often? Hermant, I've sometimes seen this parameter recommended when having library cache latching issues in large Apps installations, I have not used it myself in Apps though. Also note, that cursor_space_for_time requires 50-100% larger shared_pool (and some more private SQL area in PGA, shared_pool or large_pool, depending on configuration), since shared cursor's frames can't be aged out from library cache until all corresponding cursors are closed (normally if there's not enough free memory in shared pool when parsing a new statement, some unpinned, but open cursors can be thrown out, but with cursor_space_for_time they can't be). So, if you don't find any better cure and decide to use this parameter, you should first increase your shared pool quite much to avoid ORA-4031 errors and then start reducing in small amounts, based on v$librarycache, v$rowcache, x$kghlu and shared pool/library cache latch wait statistics. It's not good idea to leave shared pool too large, otherwise your memory allocations from there (hard parses for example) will get slow (shared pool latch (or latches in 9i) are
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
It's not being the case. I would really, really like to know how does Oracle implement AST's? There's no such thing you won't find from Ixora: http://www.ixora.com.au/q+a/misc.htm Search for AST :) Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
I was the guy who asked that question long time ago, but I'm not sure how exactly are sockets used. Socket is, essentially, a pipe. You must have someone reading and someone writing it. That is not exactly what I'd call an AST. On 12/02/2003 01:39:28 PM, Tanel Poder wrote: It's not being the case. I would really, really like to know how does Oracle implement AST's? There's no such thing you won't find from Ixora: http://www.ixora.com.au/q+a/misc.htm Search for AST :) Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
I have to admit that I wasn't thinking about replying to your comment when I sent this email. However, I think you are correct - there is an effect of extra items not being releasable from the shared pool when cursor_space_for_time is true. (From memory of one of Steve's seminars, it is the Heap 6 that ceases to be freeable). Whether this eliminates the creation and dropping of an x$kglpn entry I haven't yet checked. Just as a quick test of what sorts of benefits could be achieved on latches by setting this parameter, I ran up a quick pl/sql loop and got the following results - (included in-line in case attachments get rejected, so the formatting is probably rubbish). (Version 9.2.0.3) Comments on cursor_space_for_time = true Setting this parameter does change the latching in the library cache, but does not achieve total elimination. Significantly, more benefit comes from session_cached_cursors than cursor_space_for_time. Tested: --- declare m_junk varchar2(20); begin for i in 1..100 loop execute immediate 'select ''abc'' from dual' into m_junk; end loop; end; / Environment changes: session_cached_cursors = 0 / 100 cursor_space_for_time = true / false Counts taken from: -- v$latch v$library_cache Latchcs4t = true cs4t = false --- (Sess cache = 0) shared pool 379 492 library cache9251035 library cache pin466 674 library cache pin alloca 440 440 (Sess cache != 0) shared pool 102 203 library cache310 416 library cache pin209 415 library cache pin alloca0 0 CacheGetsHits PinsHits - cs4t=true - SQL Area 108 107 225 223 (sess cache = 0) SQL Area1 1 101 101 (sess cache != 0) cs4t=false - SQL Area 108 107 333 331 (sess cache = 0) SQL Area1 1 208 208 (sess cache != 0) == The execute immediate is deliberate to emulate a common coding paradigm, with explicit parse calls. You'll notice that the most significant fraction of the reduction in latch costs comes from the switch to using session cached cursors. But there is a further benefit from the cursor_space_for_time - and the most obvious change is in the 'library cache pin' - but it doesn't drop to zero. The point I was trying to make earlier, though, was about the general issues regarding latching in this area - even when you have a fantastically perfectly written application that only ever does 'parse once execute many' you can still get library cache (etc.) latch contention purely on extreme numbers and concurrency of execution. For those who haven't found it yet, there is a paper by Bjorn Ensig on OTN about (a.o) the cursor_space_for_time parameter and what it's doing to the shared memory. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:59 AM Jonathan, I've understood that when cursor_space_for_time is true, then unpin is only done when cursor is closed, thus there's no need for pinning/unpinning for every execution of a cursor. This should reduce hits on library cache latches since pinning is not done so often? Hermant, I've sometimes seen this parameter recommended when having library cache latching issues in large Apps installations, I have not used it myself in Apps though. Also note, that cursor_space_for_time requires 50-100% larger shared_pool (and some more private SQL area in PGA, shared_pool or large_pool, depending on configuration), since shared cursor's frames can't be aged out from library cache until all corresponding cursors are closed (normally if there's not enough free memory in shared pool when parsing a new statement, some unpinned, but open cursors can be thrown out, but with cursor_space_for_time they can't be). So, if you don't find any better cure and decide to use this parameter, you should first increase your shared pool quite much to avoid ORA-4031 errors and then start reducing in small amounts, based on v$librarycache, v$rowcache, x$kghlu and shared
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
CURSOR_SPACE_FOR_TIME is FALSE. This is an Oracle Apps R11 install. Hemant At 05:29 AM 30-11-03 -0800, you wrote: What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 Running StatsPack during a PEAK period and then analyzing the output at oraperf.com, I get : 33409 parses (673 hard parses), 498516 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.31 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 508 sessions logged on and at the end of the timing interval 0 more sessions where active. The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted in reducing the parse count from 32736 to 22550 During parsing 276280 msec of CPU were used and 1134430 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch 96% of the latch wait time is on the Library Cache Latch [85% of the Response Time was Wait Time, 71% of the Wait Time was Latch Wait time and 96% of the Latch Wait Time was Library Cache Latch, . this Wait Time analysis really does make sense !] Hemant At 10:14 PM 29-11-03 -0800, you wrote: I thought the session_cached_cursors is dynamic and scope is session? This is on 8.1.7. I have used: alter session set session_cached_cursors=500; -Original Message- Sent: Sunday, November 30, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Sami, 'cached_cursors' is not a valid hint, at least not in 9i. Or at least, I can find no reference to it. And 'cached cursors' as it appears in the SQL is not a valid hint syntax. You need to set the session_cached_cursors value in the init.ora, and bounce the database. This parameter cannot be set dynamically, at least as of 9i. Jared On Sat, 2003-11-29 at 14:44, Sami wrote: Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives better response time. But the same time w.r.t latch, session_cached_cursor=100 is giving positive impact. 1) session_cached_cursor=0 - more latches but good response time(2.60) 2) session_cached_cursor=100 - less # of latches but higher response time(2.87) Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 2000 1.76 1.77 0 0 0 0 Execute 2000 0.84 0.74 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.60 2.51 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0 TABLE ACCESS FULL T2 0 TABLE ACCESS FULL T1 SELECT /*+ cached cursors 100 */FIRST_NAME,LAST_NAME,CUSTOMERID, COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still seevery high LIBRARY CACHE LATCH contention and am considering upping the value again.Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggestedto the application team to put a custom ALTER SESSION trigger into the specific firstresponsibility form for users who do navigate between forms a lot and where we seehigh contention.Running Steve Adams's query, I getSQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE- - -session_cached_cursors 400 50%open_cursors 1024 36%CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES- --- --- 35.10% 63.09% 1.81%MAX_CACHEABLE_CURSORS- 5227Running StatsPack during a PEAK period and then analyzing the output at oraperf.com, I get :33409 parses (673 hard parses), 498516 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.31 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 508 sessions logged on and at the end of the timing interval 0 more sessions where active. The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted in reducing the parse count from 32736 to 22550During parsing 276280 msec of CPU were used and 1134430 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch96% of the latch wait time is on the Library Cache Latch [85% of the Response Time was Wait Time, 71% of the Wait Timewas Latch Wait time and 96% of the Latch Wait Time was Library Cache Latch, . this Wait Time analysis reallydoes make sense !]HemantAt 10:14 PM 29-11-03 -0800, you wrote: I thought the session_cached_cursors is dynamic and scope issession? This is on 8.1.7. I have used:alter session set session_cached_cursors=500;-Original Message-Sent: Sunday, November 30, 2003 12:24 AMTo: Multiple recipients of list ORACLE-LSami,'cached_cursors' is not a valid hint, at least not in 9i.Or at least, I can find no reference to it.And 'cached cursors' as it appears in the SQL is not avalid hint syntax.You need to set the session_cached_cursors value in theinit.ora, and bounce the database. This parameter cannotbe set dynamically, at least as of 9i.JaredOn Sat, 2003-11-29 at 14:44, Sami wrote: Dear Jonathan Lewis, Many thanks for your response. Using session_cached_cursor parameter I am not getting better response time. I did run this testcases multiple times but always session_cached_cursor=0 gives better response time. But the same time w.r.t latch, session_cached_cursor=100 is giving positive impact. 1) session_cached_cursor=0 - more latches but good response time(2.60) 2) session_cached_cursor=100 - less # of latches but higher response time(2.87) Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 2000 1.76 1.77 0 0 0 0 Execute 2000 0.84 0.74 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.60 2.51 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0 TABLE ACCESS FULL T2 0 TABLE ACCESS FULL T1 SELECT /*+ cached cursors 100
Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
You still have to hit the library cache to execute a statement as it needs to be pinned in share mode, and unpinned when you finish with it. Library cache latch waits can be a symptom of excessive executions. Have you checked the library cache latch children to see if the load is evenly balanced, or whether there is a single library cache latch that is suffering most of the sleeps. Good news for 9.2 - v$sql, and a couple of others include the library cache child latch number, so you can see which objects are protected by the hot latch without having to use Steve's algorithm. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 1:29 PM What's the value for your cursor_space_for_time parameter? Tanel. - Original Message - From: Hemant K Chitale To: Multiple recipients of list ORACLE-L Sent: Sunday, November 30, 2003 8:54 AM Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On occassion I still see very high LIBRARY CACHE LATCH contention and am considering upping the value again. Currently, I set it at the Instance level. Since I am running Oracle Apps, I have suggested to the application team to put a custom ALTER SESSION trigger into the specific first responsibility form for users who do navigate between forms a lot and where we see high contention. Running Steve Adams's query, I get SQL @Session_Cursor_Cache.sql PARAMETER VALUE USAGE - - - session_cached_cursors 400 50% open_cursors 1024 36% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES - --- --- 35.10% 63.09% 1.81% MAX_CACHEABLE_CURSORS - 5227 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
