Shulou(Shulou.com)06/01 Report--

Execute to Parse% in AWR report: percentage is too low As follows: Instance Efficiency Percentages (Target 100%) Buffer Nowait%: 97.33Redo NoWait%: 100.00Buffer Hit%: 96.59In-memory Sort%: 100.00Library Hit%: 84.65Soft Parse%: 93.10Execute to Parse%: 2.60Latch Hit%: 99.32Parse CPU to Parse Elapsd%: 75.73% Non-Parse CPU:99.03Execute to Parse% means that the SQL statement is parsed and the hit rate is repeated. Formula = 100* (1-Parses/Executions) if the value is too small It shows that the proportion of analysis (hard analysis and soft analysis) is larger, and that of fast analysis (soft and soft analysis) is less. About the adjustment of the session_cached_cursors parameter: open_cursors: this parameter means the number of cursors that are most used by the same session at the same time. The default is 300 in the Oracle10.2.0.1.0 version. Session_cached_cursors:SESSION_CACHED_CURSORS, that is, how many cursor can be cached by a session, so that subsequent identical SQL statements no longer open cursors, thus avoiding the process of soft parsing to improve performance. Binding variables solves the problem of hard parsing. Soft parsing consumes resources as well as hard parsing. So this parameter is very important. The default is 20 in the Oracle10.2.0.1.0 version. Now you need to increase this parameter to facilitate more soft parsing, which can save the resources and time consumed by a new session cursor for open and an existing session cursor for close. Check and verify that the utilization rate of session_cached_cursors is indeed 100% through the following statement, (this is what I checked and verified at that time) SQL > Select 'session_cached_cursors' Parameter, Lpad (Value, 5) Value, Decode (Value, 0,' n Value, To_Char (100 * Used / Value, '990') | |'%') Usage From (Select Max (s.Value) Used From V$statname n, V$sesstat 's Where n.Name = 'session cursor cache count' And s.Statistic# = n.Statistic#) (Select Value From V$parameter Where Name = 'session_cached_cursors') Union All Select' open_cursors', Lpad (Value, 5), To_Char (100 * Used / Value, '990') | |%' From (Select Max (Sum (s.Value)) Used From V$statname n, V$sesstat 's Where n.Name In ('opened cursors current',' session cursor cache count') And s.Statistic# = n.Statistic# Group By s.Sid), (Select Value From V$parameter Where Name = 'open_cursors') PARAMETER VALUE USAGE-- session_cached_cursors 50% open_cursors 300 22% once again verify whether session_cached_cursors is reasonable: the value of session_cached_cursors is not as large as possible. We can further verify whether this parameter is reasonable through the following two statements: SQL > SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE'% cursor%' NAME VALUE-opened cursors cumulative 1075158364 opened cursors current 1578 pinned cursors current 458 session cursor cache hits 140287938 session cursor cache count 20425458 cursor authentications 18472351 SQL > SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE'% parse%' NAME VALUE-ADG parselock X get attempts 0 ADG parselock X get successes 0 parse time cpu 13211356 parse time elapsed 19331036 parse count (total) 1020611015 parse count (hard) 83024992 parse count (failures) 504137 parse count (describe) 20927 Session cursor cache hits is the number of times the system found the corresponding cursors in the cache Parse count (total) is the total number of parses, and the higher the ratio, the better the performance. If the proportion is low and there is more memory left, you can consider increasing this parameter. The ratio of the two is relatively low as shown below, SQL > select 140289159ax 10206110155100 from dual; 14028915910101510100-13.745605 determine whether the size of the open_cursors is reasonable by the following statement, as shown below, mine is reasonable. SQL > SELECT MAX (A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P WHERE A.STATISTIC# = B.STATISTIC# AND B.NAME = 'opened cursors current' AND P.NAME =' open_cursors' GROUP BY P.VALUE HIGHEST_OPEN_CUR MAX_OPEN_CUR-34300

To sum up, we can determine the need to increase the parameter session_cached_cursors to improve the performance of the oracle database, but the parameter session_cached_cursors is not the larger the better, too large will cause pga cache fragmentation, memory consumption, and then the management of session cursor cache is also using LRU.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.