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

SQL > select name, parameter1, parameter2, parameter3 from v$event_name where name like'& event'

Enter value for event: latch: shared pool

Old 1: select name, parameter1, parameter2, parameter3 from v$event_name where name like'& event'

New 1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'latch: shared pool'

NAME PARAMETER1 PARAMETER2 PARAMETER3

Latch: shared pool address number tries

SQL >

Problem Confirmation: problem location

Significant waits on "latch: shared pool"

Other waits related to shared pool such as library cache waits may also be seen

Overall database performance may be significant

There may be high number of hard parsing

"latch: shared pool" ("latch: shared pool"):

The shared pool is part of the SGA and it's main components are the library cache and the dictionary cache. The shared pool holds objects such as optimized queries, parsed sqls, security checks, and packages in memory to allow sessions fast access. When the shared pool space is exhausted old entries are aged out to allow new entries. Shared pool space may become exhausted due to small shared pool, or sqls that are not shared, or high impact on the data dictionary. Activity in the shared pool is protected by the shared pool latch which, when held by a session, prevents changes being made to it.

Shared pools are part of SGA, and its main components are library caching and dictionary caching. The shared pool contains objects such as optimized queries, parsed sqls, security checks and in-memory packages to allow session fast access. When the shared pool runs out of space, the old entry times out to allow new entries. Shared pool space may be depleted due to small shared pools or unshared sqls, or have a significant impact on data dictionaries. Activity in the shared pool is protected by the shared pool latch, and the shared pool locker prevents changes to it during the session.

Resolve:

1.Tuning the Shared Pool Latch (adjust shared pool latch)

Contention for Lock: shared Pool is usually attributed to one or more of the following:

The shared pool is too small.

SQL is not shared (usually hard parsing)

Heavy use of data dictionaries (row cache contention)

1.1 To reduce waits, shared pool activity needs to be tuned as outlined in the following article: to reduce waiting, shared pool activity needs to be adjusted, as described below

Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention

Eliminate Literal SQL

If you have an existing application, you may not be able to eliminate all literal SQL, but you still have to try to eliminate some of the problematic statements. From the V$SQLAREA view, you may find a statement that is suitable for switching to binding variables. The following query lists the SQL with a large number of similar statements in SGA:

SELECT substr (sql_text, 1,40) "SQL", count (*), sum (executions) "TotExecs"

FROM v$sqlarea

WHERE executions

thirty

ORDER BY 2

Versions above 10g can use the following statement:

SET pages 10000

SET linesize 250

Column FORCE_MATCHING_SIGNATURE format 99999999999999999999999

WITH c AS

(SELECT FORCE_MATCHING_SIGNATURE, COUNT (*) cnt

FROM v$sqlarea

WHERE FORCE_MATCHING_SIGNATURE! = 0

GROUP BY FORCE_MATCHING_SIGNATURE

HAVING COUNT (*) > 20)

Sq AS

(SELECT sql_text

FORCE_MATCHING_SIGNATURE

Row_number () over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p

FROM v$sqlarea s

WHERE FORCE_MATCHING_SIGNATURE IN

(SELECT FORCE_MATCHING_SIGNATURE FROM c))

SELECT sq.sql_text, sq.FORCE_MATCHING_SIGNATURE, c.cnt "unshared count"

FROM c, sq

WHERE sq.FORCE_MATCHING_SIGNATURE = c.FORCE_MATCHING_SIGNATURE

AND sq.p = 1

ORDER BY c.cnt DESC

Check the high version:

SELECT address

Hash_value

Version_count

Users_opening

Users_executing

Substr (sql_text, 1,40) "SQL"

FROM v$sqlarea

WHERE version_count > 10

Find the statement that takes up a lot of shared pool memory:

SELECT substr (sql_text, 1,40) "Stmt"

Count (*)

Sum (sharable_mem) "Mem"

Sum (users_opening) "Open"

Sum (executions) "Exec"

FROM v$sql

GROUP BY substr (sql_text, 1,40)

HAVING sum (sharable_mem) > & MEMSIZE

1.2 For row cache wait, review following note: for line cache wait, review the following considerations:

Document 1476670.1 Resolving Issues With Latch Row Cache Object

The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock ona data dictionary cache is required. Waits on this event usually indicate some form of DDL occuring, or possibly recursive operations such as storage management and incrementing sequence numbers.

The shared pool contains the row cache in the data dictionary, which helps to reduce physical I / O on the data dictionary table. Row cache locks are primarily used to serialize changes to the data dictionary and wait when a lock on the data dictionary cache is needed. Waiting for this event usually indicates that some form of DDL occurs, or it may be a recursive operation, such as storage management and incrementing sequence numbers.

Row Cache Lock

When DDLs execute, they must acquire locks on the row cache in order to access and change the Data Dictionary information. Once the locks are taken then they can be allowed to modify individual rows in the data dictionary. When DDL executes, they must acquire locks on the row cache in order to access and change data dictionary information. Once the lock is obtained, you can allow individual rows in the data dictionary to be modified.

Reducing Waits reduces waiting:

A.The data dictionary resides in the shared pool. If the shared pool is not sized correctly then the data dictionary might not be fully cached. This should be handled automatically with the automatic shared memory tuning feature. The following documents provide more details: the data dictionary resides in the shared pool. If the size of the shared pool is incorrect, the data dictionary may not be fully cached. This should be handled automatically by the automatic shared memory adjustment function.

b. Find the waiting cache

SQL > select name, parameter1, parameter2, parameter3 from v$event_name where name like'& event'

Enter value for event: row cache lock

Old 1: select name, parameter1, parameter2, parameter3 from v$event_name where name like'& event'

New 1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'row cache lock'

NAME PARAMETER1 PARAMETER2 PARAMETER3

Row cache lock cache id mode request

Select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=&cache_id

C.Take cache dependent actions: take caching-related actions

DC_SEQUENCES

For DC_SEQUENCES, consider caching sequences using the cache option.

For DC_SEQUENCES, consider caching sequences using the caching option.

DC_OBJECTS

Look for any object compilation activity which might require an exclusive lock, blocking other activities

Find any object compilation activities that may require exclusive locking to block other activities

DC_SEGMENTS

Contention here is most likely to be due to segment allocation. Investigate what segments are being created at the time.

The controversy here is probably due to division distribution. Investigate which segment was being created at that time.

DC_USERS

This may occur if a session issues a GRANT to a user and that user is in the process of logging on to the database. Investigate why grants are being made while the users are active. This can happen if the session issues a GRANT to the user and the user is logging in to the database. Investigate why funding is under way and users are active

DC_TABLESPACES

The most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents. The most likely reason is to allocate a new scope. If the extent size is set low, the application may constantly request new extents and cause contention. Do you have small-sized objects that are growing rapidly? (you can view objects with a large range of objects by looking for them.) Check the track of the insert / update activity, check the number of objects inserted into the range

2.Application Logic application logic

Typically, contention for the shared pool arises when many sessions are hard parsing and looking for space in the library cache (held in the shared pool since Oracle attempts to reuse application code if it has been executed before). The library cache holds the executables of SQL cursors, PL/SQL programs, and Java classes-essentially the application code. If a parsed representation is in the library cache and it can be shared, Oracle will reuse the code (soft parsing). If there is no parsed representation of the sql in the library cache, then Oracle will need to hard parse the sql which means that latches will be held for longer. Thus high waits for "latch: shared pool" can be due to excessive hard parsing and if that is occurring, review why the application is encouraging so many hard parses.

Typically, contention for shared pools occurs when many sessions are difficult to parse and look for space in the library cache (because Oracle is trying to reuse application code if it has been executed before). The library cache contains executables for SQL cursors, PL / SQL programs, and Java classes-essentially application code. If the parsed representation is in the library cache and can be shared, Oracle will reuse the code (soft parsing). If there is no parsed sql representation in the library cache, then Oracle will need to hard parse the sql, which means that the latch will remain longer. Therefore, the high wait for "locking: shared pools" may be due to excessive hard parsing, and if this happens, see why the application is encouraging so much hard parsing.

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.