[One Moss per day]-Shared Pool optimization and Library Cache Latch conflict optimization (1523934.1)-Series

[One Moss per day]-Shared Pool optimization and Library Cache Latch conflict optimization (1523934.1)-Series 6 [one Moss per day]-Shared Pool optimization and Library Cache Latch conflict optimization (1523934.1) -Series 5 http://www.bkjia.com/database/201309/240773.html Use SQL to view Shared Pool issues. Note: These statements may aggravate latch competition. in the above section, we use V $ view (V $ SQL and V $ SQLAREA) "above. search for literal SQL select substr (SQL _text,) "SQL", count (*), sum (executions) "TotExecs" FROM v $ sqlareaWHERE executions <5 group by substr (SQL _text, 1, 40) HAVING count (*)> 30 order by 2; this statement helps you find frequently used literal SQL to retrieve Library Cache hit ratioSELECT SUM (PINS) "EXECUTIONS ", SUM (RELOADS) "cache misses while executing" FROM V $ LIBRARYCACHE; If misses/executions is higher than 1%, you need to try to reduce the occurrence of library cache miss. Transformation: select sum (PINS) "EXECUTIONS", SUM (RELOADS) "cache misses while executing", SUM (RELOADS)/SUM (PINS) from v $ LIBRARYCACHE; check the length of the hash chain: SELECT hash_value, count (*) FROM v $ sqlarea group by hash_valueHAVING count (*)> 5; this statement should normally return 0 rows. If any HASH_VALUES has a high count (two digits), you need to check whether it is affected by a bug or the literal SQL statement is in an abnormal format. We recommend that you further list all statements with the same HASH_VALUE. For example, SELECT SQL _text FROM v $ sqlarea WHERE hash_value =; if these statements look the same, query V $ SQLTEXT to find the complete statement. Different SQL texts may be mapped to the same hash value. For example, in 7.3, if a value appears twice in the statement and the interval is exactly 32 bytes, the two statements map the same hash value. Check SELECT address, hash_value, version_count, users_opening, users_executing, substr (SQL _text,) "SQL" FROM v $ sqlareaWHERE version_count> 10; in the "Sharable SQL" section above, we have already described that different "versions" of a statement are caused by inconsistent characters of the statement but objects to be accessed or bound variables. In different Oracle8i versions, a higher version is also generated due to progress monitoring issues. As described earlier in this document, we can set _ SQLEXEC_PROGRESSION_COST to '0' to disable progress monitoring from generating a higher version. SELECT substr (SQL _text, 1, 40) "Stmt", count (*), sum (sharable_mem) "Mem", sum (users_opening) "Open ", sum (executions) "Exec" FROM v $ sqlGROUP BY substr (SQL _text, 10%) HAVING sum (sharable_mem)> & MEMSIZE; here, the MEMSIZE value is of the shared pool size, the Unit is byte. This statement can identify the SQL statements that occupy a large memory of the shared pool. These SQL statements can be similar literal statements or different versions of a statement. As a result, the memory of the shared pool memory 'aged' out is allocated SELECT * FROM x $ ksmlruWHERE ksmlrnum> 0. Note: because the content of X $ KSMLRU is eliminated after a query returns no more than 10 rows of records, use SPOOL to save the output content. The X $ KSMLRU table shows which memory allocation operations have caused the most memory blocks to be cleared out of the shared pool since the last query. In some cases, this will help you find sessions or statements that continuously request space allocation. If a system performs well and the shared SQL is used well, but occasionally slows down, this statement can help you find the cause. For more information about X $ KSMLRU, see Note: 43600.1. Problems may occur in different Oracle Releases. Some common problems in different release may affect the performance of the shared pool:> increasing the processing capability of each CPU reduces the latch holding time, which helps reduce the shared pool competition on each Oracle release. A faster CPU is generally better than a slower CPU increase.> If you have set an EVENT for whatever reason, ask Oracle support to check whether this event affects the performance of the shared pool.> Ensure that the Oracle instance has enough memory to avoid the risk that the SGA memory is exchanged by the OS swap. For example, incorrect operating system settings on AIX may cause shared pool problems-