The one basic rule for tuning the shared pool is to avoid or minimize parses of any kind, whether they be soft parses or hard parses. Parses can only be avoided by your developers by efficiently coding the application. As a DBA you can only minimize parses. So, if you have lot have hard parses, Then you have to convert them to soft one's. And, if you have lot of soft parses then you have to minimize them. This article focuses on tuning your shared pool using AWR or statspack reports along with some init parameters. Load profile Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- DB Time(s): 0.8 2.5 DB CPU(s): 0.8 2.5 Redo size: 26,727.5 85,874.4 Logical reads: 14,003.8 44,993.8 Block changes: 53.2 171.0 Physical reads: 111.3 357.7 Physical writes: 11.2 36.0 User calls: 7.1 22.7 Parses: 820.8 2,637.1 Hard parses: 743.5 2,388.9 W/A MB processed: 828,525.2 2,662,020.7 Logons: 0.1 0.3 Executes: 1,025.7 3,295.4 Rollbacks: 0.0 0.0 Transactions: 0.3 The important values to check here are "Parses & Hard Parses". So, out of total 90% of the parses are hard parses (90.582% to be precise). Possible reason for that is maybe cursors are not being shared. The general rule is that In an OLTP system number of hard parses should be few. and In and DWH and DSS environments the percentage of hard parses is normally higher. Instance Efficiency Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.21 In-memory Sort %: 100.00 Library Hit %: 65.63 Soft Parse %: 9.41 Execute to Parse %: 19.98 Latch Hit %: 99.98 Parse CPU to Parse Elapsd %: 0.01 % Non-Parse CPU: 22.67 Indicators which mean that there is problem with shared pool. Library Hit% - Shows you in percentage the number of times a requested object was found in the shared pool(ideal range (95 to 100)%). Soft Parse % - Shows you in percentage the number of times a cursor was found and reused (ideal range (95 to 100)%). A low percentage means that cursors are not being reused. Execute to Parse% - The execute to parse ratio should be very high in a ideal database (ideal range (95 to 100)%). The execute to parse ratio is basically a measure between the number Of times a sql is executed versus the number of times it is parsed. The ratio will move higher as the number of executes go up, while The number of parses either go down or remain the same. The ratio will be close to zero if the number of executes and parses Are almost equal. The ratio will be negative executes are lower but the parses are higher. Parse CPU to Parse Elasped% - Parse CPU means amount of CPU time used for parsing. Parse Elapsed means amount of clock time used for parsing. So, if for example if this percentage is high say 87.88% then (1/.8788 = 1.13791534) that means for every 1 cpu second 1.13 seconds of wall clock time has elapsed in order to do parses. Low percentage in this ratio may be an indicator of latch problems. Ideal Range is between 95% and 100%. Top 5 Timed Events Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- DB CPU 67 98.21 db file sequential read 8,371 0 0 0.52 User I/O latch: row cache objects 16 0 8 0.19 Concurrency latch: shared pool 956 0 0 0.15 Concurrency log file sync 25 0 2 0.06 Commit ------------------------------------------------------------- Both latch: row cach objects & latch: shared pool indicate that there is some issue with shared pool (latch contention). So, according to the given slide (0.19 + 0.15) 0.34% of the CPU time is being eaten by latch contention. "Row cache objects" is a latch which is used to protect the access to data dictionary cache in the SGA. A high value for this may indicate the following things. 1) There is excessive use of data dictionary information. 2) There is lot of hard parsing. General solution to this problem is to increase the size of shared pool. Latch: shared pool Shared pool latch is used to protect memory allocation. shared pool latch contention may indicate the following: 1) There is excessive hard parsing because the application is using literals instead of bind variables. 2) Cursors are not being shared. Time Model Statistics Time related statistics presents the various operations which are consuming most of the database time. Statistic Name Time (s) % of DB Time ------------------------------------------ ------------------ ------------ DB CPU 67.39 98.21 sql execute elasped time 61.13 89.09 parse time elasped 56.36 82.14 hard parse elasped time 49.99 72.86 PL/SQL execution elasped time 4.30 6.26 PL/SQL compilation elasped time 0.76 1.10 connection management call elasped time 0.26 0.38 hard parse (sharing criteria) elasped time 0.15 0.22 hard parse (bind mismatch) elasped time 0.09 0.13 repeated bind elasped time 0.06 0.08 sequence load elasped time 0.03 0.04 DB time 68.62 background elasped time 1.50 background cpu time 0.12 ------------------------------------------------------------- According to oracle docs Parse time elapsed - Amount of elapsed time spent parsing SQL statements. It includes both soft and hard parse time. Hard parse elapsed time - Amount of elapsed time spent hard parsing SQL statements. So, according to the slide ((72.86*100)/82.14) 88.70% of DB time is spent on hard parses which is not good. Library Cache Activity. Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invalidations ---------- ------------ -------- ------------ -------- ------- ------------- BODY 26 30.77 354 6.50 15 0 CLUSTER 576 1.04 322 1.86 0 0 INDEX 35,320 0.07 53,320 0.07 0 0 SQL AREA 130,580 97.43 225,838 85.78 705 479 TABLE/PROCEDURE 405,501 0.35 313,834 1.31 908 0 TRIGGER 90 15.56 100 14.00 0 0 All the compiled cursors are stored in SQL Area. High Pct Miss can indicate the following things. 1) Cursors ar not being used. 2) Cursors are being aged out or reloaded frequently because of low memory. The number of reloads should not be more than 1% of the number of pins. In our case it is ((705*100)/225838) 0.31%. The reloads to pin ration can be in excess of 1% in the following scenarios. 1) Shared parsed areas have been aged-out because of lack of space. Main solution to this problem is to increase the shared pool size. 2) Shared parsed areas are invalidated. Possible solution for this is to do house keeping service like index creation & gathering statistics when there is low database activity. So, in order to tune shared pool we have to avoid hard parses. There are three main methods to do it. 1) Make sure your shared pool is sufficiently sized. 2) Tell your developers to use bind variables instead of literals. 3) The third method is to use cursor_sharing only if the application has already been developed and does not use bind variables. This method should be used as a last resort. Sharing your Cursors. Cursors can be shared by using the init parameter "cursor_sharing". It can be set to three values. 1) EXACT(default) - cursors can be shared only when the sql statements text are exactly similar. 2) Similar(Deprecated in 11g) - cursors are shared when sql statements are identical. The execution plan can be different depending on the literal value. 3) FORCE - SQL statements that are similar will share cursors and their is going to be only one execution plan for all the sql statements. Recommendations 1) cursor_sharing should be set to "EXACT" for DSS environments if complex queries are being used. 2) cursor_sharing should be set to "FORCE" for OLTP environments if bind variables are not being used. Adaptive Cursor sharing (11g) It is always recommended to use bind variables while developing applications in order to gain performance benefits. But sometimes instead of improving, The performance is degraded because of a combination of bind peeking and skewness of data in certain columns. In order to solve this problem oracle 11g has come up with a new feature called "adaptive cursor sharing". For this to work properly histograms should be created on columns where the skewness of data is high. They are collected automatically in 11g. Adaptive cursor sharing works by observing the execution plans of statements across various executions. If it finds any suboptimal plans, then it allows the bind variables to use different execution plans for the same statement in order to improve performance. Minimizing overhead of soft parses. SESSION_CACHED_CURSORS The overhead on soft parses and aging out of cursors can be avoided by using the parameter "session_cached_cursors". This works by keeping a cache of the closed cursors in the session memory. So when a query is executed, The session searches the session cache first and if the cursor is found, the soft parse is avoided. NOTE: Even if the cursor is found in the session cache, It will still require validation. It does that by validating that the opened cursor points to the right sql statement in the shared pool. So, in essence its more like a "softer" soft parse. SHARED_POOL_RESERVED_SIZE The SHARED_POOL_RESERVED_SIZE parameter specifies the shared pool space that is reserved for large contiguous requests for shared pool memory. This static parameter should be set high enough to avoid performance degradation in the shared pool from situations where pool fragmentation forces Oracle to search for free chunks of unused pool to satisfy the current request. Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. The default value is 5% of the shared pool size, while the maximum value is 50% of the shared pool size. For interMedia applications, a value at or close to the maximum can provide performance benefits. Recommended for databases with large chunks of data or LOB objects(audio, video, images). SQL query result cache (11g). SQL query result cache enables the database to store the query result sets in the shared pool. In brief it stores the result of a query for future reuse. This cache can be used by multiple statements from the same session as well as by multiple sessions. For example if the first session executes, after fething the data from the database it caches the result in the SQL query result. So, if any other instance executes the exact query, the query result will be fetched from the SQL query result caches instead of the datafiles. SQL query result cache in RAC. Each RAC node has it's own private SQL query result cache. One node cannot share the SQL query result cache of another node. Initialization parameters. RESULT_CACHE_MODE MANUAL: You have to specify the RESULT_CACHE hint in the SQL statement in order to store result in cache i.e "select /*+ RESULT_CACHE */ dept_id, emp_id, sal from employees;". FORCE: All the result sets are stored in the cache by default. if with setting you do not want a particular query to use this feature then mention the NO_RESULT_CACHE hint i.e "select /*+ NO_RESULT_CACHE */ dept_id, emp_id, sal from employees". RESULT_CACHE_MAX_SIZE This parameter defines the memory allocation of the result cache. Value of "0" means it is disabled. Maxlimit is 75% of shared pool. Default value is derived as following. 0.25% of memory_target or 0.5% of sga_target or 1% of shared_pool_size. RESULT_CACHE_MAX_RESULT Defines the max memory allocation for a single result. Default is 5% of RESULT_CACHE_MAX_SIZE DBMS_RESULT_CACHE package. To check the status of the cache. SQL> select dbms_result_cache.status from dual;STATUS -------------------------------------------------------------------------------- ENABLEDTo check the cache memory usage. SQL> set serveroutput on SQL> exec dbms_result_cache.memory_report(detailed => true);R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 672K bytes (672 blocks) Maximum Result Size = 33K bytes (33 blocks) [Memory] Total Memory = 107836 bytes [0.135% of the Shared Pool] ... Fixed Memory = 9440 bytes [0.012% of the Shared Pool] ....... Memory Mgr = 124 bytes ....... Bloom Fltr = 2K bytes ....... Cache Mgr = 4416 bytes ....... State Objs = 2852 bytes ... Dynamic Memory = 98396 bytes [0.123% of the Shared Pool] ....... Overhead = 65628 bytes ........... Hash Table = 32K bytes (4K buckets) ........... Chunk Ptrs = 12K bytes (3K slots) ........... Chunk Maps = 12K bytes ........... Miscellaneous = 8284 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 23 blocks ........... Used Memory = 9 blocks ............... Dependencies = 3 blocks (3 count) ............... Results = 6 blocks ................... SQL = 6 blocks (6 count) PL/SQL procedure successfully completed.To remove the contents from result cache. SQL> execute dbms_result_cache.flush; PL/SQL procedure successfully completed. NOTE: The contents from the query result cache will only be removed if the cache is not in use. To purge successfully set "RESULT_CACHE_MAX_SIZE=0". In order to reactivate the previous parameter you will have to restart the instance after giving it a non zero value. Things to consider for Query Result Cache. Result cache is disabled for queries containing: 1) Temporary or dictionary tables 2) Nondeterministic PL/SQL functions. 3) CURRVAL and NEXTVAL(sequences). 4) SQL functions i.e current_date,sysdate etc. Flashback queries can also be cached. Result cache grows until it reaches its maximum size. Objects or results in result cache are aged out based on the LRU algorithm. References: Oracle documentation.
Tuning shared pool cache
0 comments:
Post a Comment