Re: Huge number of DBA_DDL

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 4 Mar 2004 20:17:17 -0000
Message-ID: <006701c40225$b6b5a6a0$7002a8c0@Primary>

The most significant thing in dba_ddl_locks is the x$kgllk structure, which is the thing that associates sessions with the objects in the library cache that they are interested in. Technically I think these are the things called 'breakable parse locks'.

dba_ddl_locks hides the cursors, but exposes everything else. If you have about 100 sessions, which had all executed procedures from about 100 packages, then I think you would have about 20,000 rows in dba_ddl_locks. (One lock per session for the package, one for the package body).

The number looks big, but isn't necessarily indicative of anything meaningful unless we have some context about your system.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Jay" <jaysingh1_at_optonline.net> To: <oracle-l_at_freelists.org> Sent: Thursday, March 04, 2004 4:20 AM Subject: Huge number of DBA_DDL_LOCKS

> Dear All,
>
> Why these many DDL locks? Is something wrong in this environment? Or it is
> normal to have 20k+ locks?
>
> If it is not okay where should I start investigating this issue?
>
> Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
> With the Partitioning and Parallel Server options
> JServer Release 8.1.7.3.0 - Production
>
> SQL> select count(*) from dba_ddl_locks;
>
> COUNT(*)
> ----------
> 21318
>
> SQL> select count(*) from dba_dml_locks;
>
> COUNT(*)
> ----------
> 3
>
> SQL>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Mar 04 2004 - 14:43:24 CST

Re: Huge number of DBA_DDL_LOCKS