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

i. Symptoms:

The feedback from the business side is slow, and we find that the database is slow, accompanied by LATCH:ROW CACHE OBJECTS waiting events, and the database CPU is too high.

ii. Solution:

Through the following analysis, because there are a large number of hard parsing during the fault period, hard parsing needs to obtain data dictionary resources, which requires to obtain latch. If the amount of hard parsing is too large, it will cause serious latch contention and occupy a lot of resources, resulting in excessive CPU usage, which shows the database ROW CACHE OBJECT waiting event, and finally leads to the slowness of the database system.

According to the analysis, we have the following two suggestions:

1. Carry out SQL rectification on the business side, change unused binding variables into binding variables, become soft parsing, reduce the number of times to obtain data dictionaries, thus reduce the number of LATCH contention, and fundamentally solve the problem (recommended)

two。 Adjusting the database parameter cursor_sharing can partially alleviate this problem, but it can not solve the problem fundamentally.

iii. Analysis process:

1. There is a LATCH waiting situation in the database. Seeing these waiting events, the memory of this database is very large. I suspect it is related to hard parsing or hot block contention.

2.SGA utilization, Shared pool also has 17G, excluding insufficient memory select POOL,BYTES/1024/1024 FREE_MB from v$sgastat a where a.NAME like 'free%'

3. Through AWR, it is found that the Parse CPU to Parse Elapsed is 42%, and the CPU is 57%, which is more than 98% under normal circumstances. This value indicates that the parsing time of SQL is in proportion. The higher the better, the better. If it is too low, it means that the waiting time for resources in parsing is too long. The proportion of soft analysis is also too low, normally more than 98%. It should be a problem of hard parsing.

4. Through the analysis of the internal resources of the database, it is found that the LATCH contention of the data dictionary is too high. (GETS represents the number of requests for this resource, MISSES indicates the number of re-requests when the request failed, and SLEEPS indicates that the request failed to enter the sleep queue). The success rate is at least 12%, and the normal rate should be 98%.

Query the GET quantity and completion rate of LATCH

Col LATCH_NAME for a20

SELECT a. Addr LATCH_NAME,a.gets,a.misses,round a. Latchbook SUCESS_PCT,a.sleeps a. Childbirth department. A. Levelling department. Name of a.

FROM v$latch_children a

WHERE a.name='row cache objects' AND a.gets 0

ORDER BY a.gets desc

4. Query the quantity and hit rate of GET in ROW CACHE SELECT r. Cacheology direction r. Parameter name,r.TYPE,r.subordinate#,r.gets,r.GETMISSES,round ((1-r.GETMISSES/r.gets) * 100 ORDER BY 2) SUC_PCT FROM v$rowcache r where r.gets 0 ORDER BY 5 desc; contention is the most common

5. Count the SQL statements of hard parsing to check the FORCE_MATCHING_SIGNATURE of more than 1000 times of SQL hard parsing. If you need to get SQL statements, you can associate them with FORCE_MATCHING_SIGNATURE:

Select to_char (FORCE_MATCHING_SIGNATURE) FORCE_MATCHING_SIGNATURE, count (1) counts from v$sql where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE and EXECUTIONS 1000

Order by 2 desc

Official v$SQL view: EXACT_MATCHING_SIGNATURENUMBERSignature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.FORCE_MATCHING_SIGNATURENUMBERThe signature used when the CURSOR_SHARING parameter is set to FORCE hardcodes variables into cursors in SQL, where FORCE_MATCHING_ signature values are exactly the same, while EXACT_MATCHING_SIGNATURE values are different. The same FORCE_MATCHING_ signature value indicates that in cursor sharing FORCE mode, these cursors meet the conditions of CURSOR SHARING.

Which step of 6.SQL parsing accesses ROWCACHE and which step competes for latch? I have been querying this question for a long time. I thought it was only needed for semantic checking to row cache, but in fact, when generating the execution plan, I needed to access more data dictionaries and compete for latch more frequently, so this is the slowest place. Http://docs.oracle.com/cd/E11882_01/server.112/e40540/sqllangu.htm#CNCPT015 SQL execution flow

Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:

Hard parse

If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as ahard parse, or a library cache miss. The database always perform a hard parse of DDL.

During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change (see "Latches"). Latch contention increases statement execution time and decreases concurrency. During hard parsing, the database needs to access library cache and data dictionary cache very many times to check the data dictionary, and when the database accesses these areas, it uses a serialized device to call a latch to latch the object so that its definition will not be changed. Contention for latch increases statement execution time and reduces concurrency.

Soft parse

A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit.

Soft parses can vary in the amount of work they perform. For example, configuring the session shared SQL area can sometimes reduce the amount of latching in the soft parses, making them "softer."

In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

Of course, sometimes hot block contention will also cause latch. I will analyze it later.

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.