Re: are there any extensive docs on 10g wait events?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 20 Jun 2006 07:32:34 +0100
Message-ID: <00b701c69433$510569b0$0300a8c0@Primary>

For waits of type 'Enq ??' you can always take a quick look at v$lock_type.

SQL> select * from v$Lock_type where type = 'SQ';

TYPE

---

NAME

---

ID1_TAG

---

ID2_TAG                                                          IS_


---------------------------------------------------------------- ---

DESCRIPTION

---

---

SQ
Sequence Cache
object #

0                                                                NO

Lock to ensure that only one process can replenish the sequence cache

Wait time on this usually means heavy usage of a sequence with a small, or zero, cache definition. You can search v$sql (or v$sql_stats in 10.2) for lower(sql_text) like '%nextval%, checking column executions to see if this allows you to spot the critical sequence.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 20 2006 - 01:32:34 CDT