In the article, Jonathan Lewis mentioned that tab_stats$ could be checked.
Test database on 64 bit Windows:

SELECT
*
FROM
SYS.TAB_STATS$

The above returned about 582 rows after running:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE);

I then checked a production database, and found no rows returned. Using the suggestion from the Metalink article:

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);

The stats collection completed much more quickly, as it did not analyze the full SYS schema, and there were 582 rows returned by the query on SYS.TAB_STATS$. It looks like this simple query may be used to determine if fixed object statistics need to be collected.

The first line shows: PARSING IN CURSOR #1 … hv=3250939240 ad=’8135a590′. The value that follows hv= is the hash value for the SQL statement. I just noticed that the blog article implies that the hash value is found on the WAIT lines, but that was not the intention of this comment (I worded this poorly) “On the waits, P1 is 13985, P2 is 214, P3 is 0, Buf is 99, SQL Hash is 3250939240”.

Hi, Charles!
Thank you for reply!
Link, you gave me, was very usefull!
Let me ask you a question that I’d realy like to now. From time to time I see in my ASH reports ”latch: library cache” and “latch: shared pool”. Is it possible to track cause of such events using P1 from ASH report?
I played with “raw 10046”, dump library cache, but can’t find the way I can use P1 to find out “library cache object” waited for. Where I’ve lost ?
From your example:
WAIT #3: nam=’latch: library cache’ ela= 8566 address=1398554576 number=214 tries=1 obj#=-1 tim=5204643696
…so,
P1 = address = 1398554576
What is it use of that “address”? How can I found the root of problem?

I believe that you will find that if you convert the number 1398554576 found in the wait event to a hex number, it will be the same number as is what is displayed in the ADDR column of the above SQL statement. So, that probably did not help much. The wait time on that latch seems to be a little long at 0.008566 seconds – is it possible that the CPU utilization was atypically high on the server?

My notes on library cache and shared pool latches follow. The notes are mostly paraphrases from the Oracle documentation, for instance from the following page:http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref787
p1 (address) is the address of the latch, p2 (number) is the latch number, p3 (tries) is the number of times the process has slept waiting for the latch
A main cause of shared pool or library cache latch contention is parsing. For shared pool and library cache latches, possible causes include not using bind variables, insufficient size of application cursor cache, cursors closed explicitly after each execution, frequent log on/off, shared pool too small.

Examine the resource usage for related resources. For example, if the library cache latch is heavily contended for, then examine the hard and soft parse rates.
Examine the SQL statements for the sessions experiencing latch contention to see if there is any commonality.

OK, all those rules like decreasing parsing, using proper binds etc. it’s common investigation.
We can dig in it any time on running database, shooting SQLs using literals, or searching through v$sql_shared_cursors to find SQLs with non-shareable binds (due to changes in session’s nls settings etc)…
But the question, I’ve tried to understand is Why ASH reports shows us “latch: library cache” with P1, P2, P3. What use of those ones?
We are talking about events occurred in past time. How P1 could be useful for investigation? The only way I found to use it is query like:

select distinct sql_id from V$ACTIVE_SESSION_HISTORY
where sample_time between
to_timestamp(…..) and to_timestamp(…..) and
event = ‘latch: shared pool’ and
p1 = &P1 and
sql_id is not null;

But, again, what all those queries (we’ve found with above query) waiting for? Are they waiting just for allocating memory piece by P1 address? Why for this address? Short of shared memory?…

I believe that the P1 and P2 values (p1 (address) is the address of the latch, p2 (number) is the latch number) were retained with identical meaning when the “latch free” wait event was broken into multiple wait events in Oracle Database 10g. The p1 and p2 values of the “latch free” wait event were used to identify specifically which type of latch is causing delays. From the Oracle Database 9.2 Performance Tuning Guide, a SQL statement demonstrating the use of p2:http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/instance_tune.htm#12946

With the consideration of the above, the only meaningful piece of information provided, other than the specific latch name, is the number of sleeps while waiting for the latch to become available (p3 (tries) is the number of times the process has slept waiting for the latch).

—-
I am sure that you are aware, but I thought that it would good to mention for anyone else reading this article, that access to V$ACTIVE_SESSION_HISTORY and/or access to ASH and/or access to AWR and/or access to ADDM requires an Enterprise Edition license plus a Diagnostic Pack license.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: