Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to monitor the activities of library cache". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to monitor the activities of library cache".

By looking at the v$librarycache view, you can monitor the activity of the librarycache and further measure whether the share pool setting is reasonable. The RELOADS column indicates the number of times the object has been reloaded. In a reasonably set system, this value should be close to zero. In addition, the INVALIDATIONS column represents the number of times the object fails, which means that the sql must be reparsed.

The following sql queries the performance of librarycache:

The output of SELECT NAMESPACE, PINS, PINHITS, RELOADS and INVALIDATIONS FROM V$LIBRARYCACHE ORDER BY NAMESPACE; is as follows:

NAMESPACE PINS PINHITS RELOADS INVALIDATIONS--BODY 8870 8819 0 0CLUSTER 393 3800 0INDEX 29 0 0 0OBJECT 0 0 0 0PIPE 55265 55263 0 0SQL AREA 21536413 21520516 11204 2TABLE/PROCEDURE 10775684 10774401 0 0TRIGGER 18521844 000 pass the above query The hit rate of library cache can be calculated: Library Cache Hit Ratio = sum (pinhits) / sum (pins) SUM (PINHITS) / SUM (PINS) -. 999466248 in addition, for the above query, the explanation is as follows: 1. For SQL AREA, it was executed 21536413 times. two。 11204 of these executions resulted in library cache miss. This requires reparsing these sql because they have already been age out. 3.sql failed twice, which also led to library cache miss. 4. The hit rate is 99.94%, which means that only 0.06% of the sql needs to be parsed repeatedly. Another question: under what circumstances do you need to resize share pool? According to the explanation on performance tuning, from my own point of view, the conclusions are as follows: (1) when the value of V$LIBRARYCACHE.RELOADS is large and the application has made good use of binding variables, you can consider increasing the value of share pool. (2) when the value of V$LIBRARYCACHE.RELOADS is very small and the value of free in share pool is large, you can consider reducing the value of share pool. Get the free of share pool through the following query: SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL =' shared pool' POOL NAME BYTES--shared pool free memory 4928280 Thank you for your reading. This is the content of "how to monitor the activities of library cache". After the study of this article, I believe that you have a deeper understanding of how to monitor the activities of library cache, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.