subject:"Suggestions Needed\: Latch free \- library cache"

Steve,

By distinct user do you mean distinct username? Or sid/serial#
combination? In my case, we use connection pooling, while there may be
up to 300 sessions, they are all the same named user.

Rachel

--- Steve Adams [EMAIL PROTECTED] wrote:
 Hi All,
 
 Someone has alerted me to this thread, and asked for a comment.
 On a quick scan, and it seems to me that you've mostly got it right.
 
 The problem is that when an SQL statement that refers to its base
 objects via public synonyms is shared by multiple distinct Oracle
 users,
 then name resolution and permission checking need to repeated for
 each
 distinct user, and because the results of these actions are cached on
 the shared cursor, they increase the cost of subsequent such
 operations.
 That is, public synonyms cause extended latch retention as well as
 additional latching.
 
 For example, if 500 distinct users share 200 SQL statements that
 refer
 300 times to 100 base tables via public synonyms. Then there will
 also
 be 100 * 500 non-existent objects in both the dictionary cache and
 the
 library cache; 200 * 500 cursor authorization structures; and 300 *
 500
 negative dependency records in the library cache. These last two
 things
 are cached as segmented arrays that are scanned linearly - thus the
 increased latch retention.
 
 If your application doesn't have hundreds of distinct Oracle users,
 or if you can afford the extra latch gets and longer latch retention,
 then you will probably not notice all of this unless you start doing
 library cache dumps.
 
 That is, the use of public synonyms is a major scalability threat,
 but
 does not normally cause performance problems.
 
 @   Regards,
 @   Steve Adams
 @   http://www.ixora.com.au/ - For DBAs
 @   http://www.christianity.net.au/  - For all 
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Steve Adams
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Ryan

how do you feel about connection pooling? Our software engineers implemented
that here? Am I wrong to be concerned about large numbers of users using the
same named user?


- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, January 10, 2004 6:39 AM


 Steve,

 By distinct user do you mean distinct username? Or sid/serial#
 combination? In my case, we use connection pooling, while there may be
 up to 300 sessions, they are all the same named user.

 Rachel

 --- Steve Adams [EMAIL PROTECTED] wrote:
  Hi All,
 
  Someone has alerted me to this thread, and asked for a comment.
  On a quick scan, and it seems to me that you've mostly got it right.
 
  The problem is that when an SQL statement that refers to its base
  objects via public synonyms is shared by multiple distinct Oracle
  users,
  then name resolution and permission checking need to repeated for
  each
  distinct user, and because the results of these actions are cached on
  the shared cursor, they increase the cost of subsequent such
  operations.
  That is, public synonyms cause extended latch retention as well as
  additional latching.
 
  For example, if 500 distinct users share 200 SQL statements that
  refer
  300 times to 100 base tables via public synonyms. Then there will
  also
  be 100 * 500 non-existent objects in both the dictionary cache and
  the
  library cache; 200 * 500 cursor authorization structures; and 300 *
  500
  negative dependency records in the library cache. These last two
  things
  are cached as segmented arrays that are scanned linearly - thus the
  increased latch retention.
 
  If your application doesn't have hundreds of distinct Oracle users,
  or if you can afford the extra latch gets and longer latch retention,
  then you will probably not notice all of this unless you start doing
  library cache dumps.
 
  That is, the use of public synonyms is a major scalability threat,
  but
  does not normally cause performance problems.
 
  @   Regards,
  @   Steve Adams
  @   http://www.ixora.com.au/ - For DBAs
  @   http://www.christianity.net.au/  - For all
 
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Steve Adams
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).


 __
 Do you Yahoo!?
 Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
 http://hotjobs.sweepstakes.yahoo.com/signingbonus
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Rachel Carmichael
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Jonathan Lewis


It's always a little hard to tell from a low-concurrency
experiment how bad things can be at high concurrency.
(If it were easy, Cary wouldn't have had to have written
his book).

I have an example where a collision rate of 0.25%
results in an increase in response time of 8% at
relatively low concurrency.  One of the problems
of contention is that the back-off time after a collision
may be unsuitably large.  (Which is one reason why
there was an argument for changing the spin_count
on latches - the backoff of 1/100 sec has not changed
since 'fast' CPUs clocked 100MHz.)

Regards

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

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


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


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, January 09, 2004 5:29 PM


 I remember readign that article and I thought the results that the
 contention was very minor? Steve, are you monitoring?
 
 It seemed like one of those things that its so minor its not really
 something to worry about?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Jonathan Lewis


Note in-line

Regards

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


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


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, January 09, 2004 5:09 PM


  I'm not sure that's right.  If everyone uses a public synonym, then
  you get one sql text, and one cursor.  I think the contention appears
  because everyone has to have a 'non-existent' reference in memory
  to say that they don't own an object with the same name as the public
  synonym - consequently if you have lots of users who have to check
  long chains of  'non-existent' then the latches get held for longer
  periods of time.

 Hi Jonathan,

 I don't see how your statement contradicts the claim that heavy use of
public
 synonyms causes contention for not only library cache latches but also row
 cache objects latches. What I had in mind is Steve Adams' test. Here's the
URL
 http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I understand
right,
 the additional row cache objects latch gets are for synonym translations,
 particularly public synonym translations.


Given that Steve has replied, I don't suppose there is much need to
say anything more.  However, you will note that Steve's experiment covers
the authentication call to an existing cursor.  Subsequent uses result in
no further access to the rowcache - hence my comment that it is not
the rowcache, just the length of what Steve has pointed out is a 'segmented
array' that lurks in the library cache that causes the problem by pushing up
library cache latch hold times.

However, if parsing is so extreme that lots of statements never get
beyond the slightly softer authentication only parse call, then the
presence of public synonyms makes a bad problem worse, and a
big chunk of the latch impact is in the rowcache objects latch.

(I haven't check v9 closely yet to see how much this has changed,
but there are extra latches for the library cache the row cache which
may mean a different strategy now exists).

 Yong Huang

 __
 Do you Yahoo!?
 Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
 http://hotjobs.sweepstakes.yahoo.com/signingbonus
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Yong Huang
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread K Gopalakrishnan

Ryan:

Same named user with large number of connections is not a problem.
Things will become bad  only IFF the large number of different users
using the same set of public synonymns.

KG

--- Ryan [EMAIL PROTECTED] wrote:
 how do you feel about connection pooling? Our software engineers
 implemented
 that here? Am I wrong to be concerned about large numbers of users
 using the
 same named user?
 
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Saturday, January 10, 2004 6:39 AM
 
 
  Steve,
 
  By distinct user do you mean distinct username? Or sid/serial#
  combination? In my case, we use connection pooling, while there may
 be
  up to 300 sessions, they are all the same named user.
 
  Rachel
 
  --- Steve Adams [EMAIL PROTECTED] wrote:
   Hi All,
  
   Someone has alerted me to this thread, and asked for a comment.
   On a quick scan, and it seems to me that you've mostly got it
 right.
  
   The problem is that when an SQL statement that refers to its
 base
   objects via public synonyms is shared by multiple distinct Oracle
   users,
   then name resolution and permission checking need to repeated for
   each
   distinct user, and because the results of these actions are
 cached on
   the shared cursor, they increase the cost of subsequent such
   operations.
   That is, public synonyms cause extended latch retention as well
 as
   additional latching.
  
   For example, if 500 distinct users share 200 SQL statements that
   refer
   300 times to 100 base tables via public synonyms. Then there will
   also
   be 100 * 500 non-existent objects in both the dictionary cache
 and
   the
   library cache; 200 * 500 cursor authorization structures; and 300
 *
   500
   negative dependency records in the library cache. These last two
   things
   are cached as segmented arrays that are scanned linearly - thus
 the
   increased latch retention.
  
   If your application doesn't have hundreds of distinct Oracle
 users,
   or if you can afford the extra latch gets and longer latch
 retention,
   then you will probably not notice all of this unless you start
 doing
   library cache dumps.
  
   That is, the use of public synonyms is a major scalability
 threat,
   but
   does not normally cause performance problems.
  
   @   Regards,
   @   Steve Adams
   @   http://www.ixora.com.au/ - For DBAs
   @   http://www.christianity.net.au/  - For all
  
  
   --
   Please see the official ORACLE-L FAQ: http://www.orafaq.net
   --
   Author: Steve Adams
 INET: [EMAIL PROTECTED]
  
   Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
   San Diego, California-- Mailing list and web hosting
 services
  
 -
   To REMOVE yourself from this mailing list, send an E-Mail message
   to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
 in
   the message BODY, include a line containing: UNSUB ORACLE-L
   (or the name of mailing list you want to be removed from).  You
 may
   also send the HELP command for other information (like
 subscribing).
 
 
  __
  Do you Yahoo!?
  Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
  http://hotjobs.sweepstakes.yahoo.com/signingbonus
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting
 services
 
 -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like
 subscribing).
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Ryan
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


=
Have a nice day !!

Best Regards,
K Gopalakrishnan,
Bangalore, INDIA.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Ryan

I'm concerned about other problems. We may have 30,000 concurrent users,
sharing 5 or so named users. My big concern is maintenance and tracing.

Has anyone worked with this type of environment? How do you build tracing
into the front end so I can tell which sid, serial# is experience problems?
The middle tier is complete, so any changes I recommend need to be moderate
since we have strict deadlines.

Anyone do anything like this? Front end is complete. However, we signed a
new client who has radically more users than we have had in the past. This
limits my ability to add features that help maintain the database.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, January 10, 2004 11:14 AM


 Ryan:

 Same named user with large number of connections is not a problem.
 Things will become bad  only IFF the large number of different users
 using the same set of public synonymns.

 KG

 --- Ryan [EMAIL PROTECTED] wrote:
  how do you feel about connection pooling? Our software engineers
  implemented
  that here? Am I wrong to be concerned about large numbers of users
  using the
  same named user?
 
 
  - Original Message -
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Sent: Saturday, January 10, 2004 6:39 AM
 
 
   Steve,
  
   By distinct user do you mean distinct username? Or sid/serial#
   combination? In my case, we use connection pooling, while there may
  be
   up to 300 sessions, they are all the same named user.
  
   Rachel
  
   --- Steve Adams [EMAIL PROTECTED] wrote:
Hi All,
   
Someone has alerted me to this thread, and asked for a comment.
On a quick scan, and it seems to me that you've mostly got it
  right.
   
The problem is that when an SQL statement that refers to its
  base
objects via public synonyms is shared by multiple distinct Oracle
users,
then name resolution and permission checking need to repeated for
each
distinct user, and because the results of these actions are
  cached on
the shared cursor, they increase the cost of subsequent such
operations.
That is, public synonyms cause extended latch retention as well
  as
additional latching.
   
For example, if 500 distinct users share 200 SQL statements that
refer
300 times to 100 base tables via public synonyms. Then there will
also
be 100 * 500 non-existent objects in both the dictionary cache
  and
the
library cache; 200 * 500 cursor authorization structures; and 300
  *
500
negative dependency records in the library cache. These last two
things
are cached as segmented arrays that are scanned linearly - thus
  the
increased latch retention.
   
If your application doesn't have hundreds of distinct Oracle
  users,
or if you can afford the extra latch gets and longer latch
  retention,
then you will probably not notice all of this unless you start
  doing
library cache dumps.
   
That is, the use of public synonyms is a major scalability
  threat,
but
does not normally cause performance problems.
   
@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/ - For DBAs
@   http://www.christianity.net.au/  - For all
   
   
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Steve Adams
  INET: [EMAIL PROTECTED]
   
Fat City Network Services-- 858-538-5051
  http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
  services
   
  -
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
  in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You
  may
also send the HELP command for other information (like
  subscribing).
  
  
   __
   Do you Yahoo!?
   Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
   http://hotjobs.sweepstakes.yahoo.com/signingbonus
   --
   Please see the official ORACLE-L FAQ: http://www.orafaq.net
   --
   Author: Rachel Carmichael
 INET: [EMAIL PROTECTED]
  
   Fat City Network Services-- 858-538-5051 http://www.fatcity.com
   San Diego, California-- Mailing list and web hosting
  services
  
  -
   To REMOVE yourself from this mailing list, send an E-Mail message
   to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
   the message BODY, include a line containing: UNSUB ORACLE-L
   (or the name of mailing list you want to be removed from).  You may
   also send the HELP command for other information (like
  subscribing).
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Ryan
INET: [EMAIL PROTECTED]
 
  Fat City

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Arup Nanda

If you are on Oracle 9i, try connection identifier using
DBMS_SESSION.SET_IDENTIFIER for each of the client sessions. Even if teh
USERNAME in V$SESSION shows your named user, the field CLIENT_IDENTIFIER
will show the actual user (say, the application userid). The trace files
will show that, even auditing will, making it as useful as the USERNAME
value.

You may find an article of mine on OTN
http://otn.oracle.com/pub/articles/nanda_fga_pt2.html which deals with Fine
Grtained Auditing, but talks about the issues you have mentioned.

HTH.

Arup


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, January 10, 2004 12:04 PM


 I'm concerned about other problems. We may have 30,000 concurrent users,
 sharing 5 or so named users. My big concern is maintenance and tracing.

 Has anyone worked with this type of environment? How do you build tracing
 into the front end so I can tell which sid, serial# is experience
problems?
 The middle tier is complete, so any changes I recommend need to be
moderate
 since we have strict deadlines.

 Anyone do anything like this? Front end is complete. However, we signed a
 new client who has radically more users than we have had in the past. This
 limits my ability to add features that help maintain the database.
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Saturday, January 10, 2004 11:14 AM


  Ryan:
 
  Same named user with large number of connections is not a problem.
  Things will become bad  only IFF the large number of different users
  using the same set of public synonymns.
 
  KG
 
  --- Ryan [EMAIL PROTECTED] wrote:
   how do you feel about connection pooling? Our software engineers
   implemented
   that here? Am I wrong to be concerned about large numbers of users
   using the
   same named user?
  
  
   - Original Message -
   To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
   Sent: Saturday, January 10, 2004 6:39 AM
  
  
Steve,
   
By distinct user do you mean distinct username? Or sid/serial#
combination? In my case, we use connection pooling, while there may
   be
up to 300 sessions, they are all the same named user.
   
Rachel
   
--- Steve Adams [EMAIL PROTECTED] wrote:
 Hi All,

 Someone has alerted me to this thread, and asked for a comment.
 On a quick scan, and it seems to me that you've mostly got it
   right.

 The problem is that when an SQL statement that refers to its
   base
 objects via public synonyms is shared by multiple distinct Oracle
 users,
 then name resolution and permission checking need to repeated for
 each
 distinct user, and because the results of these actions are
   cached on
 the shared cursor, they increase the cost of subsequent such
 operations.
 That is, public synonyms cause extended latch retention as well
   as
 additional latching.

 For example, if 500 distinct users share 200 SQL statements that
 refer
 300 times to 100 base tables via public synonyms. Then there will
 also
 be 100 * 500 non-existent objects in both the dictionary cache
   and
 the
 library cache; 200 * 500 cursor authorization structures; and 300
   *
 500
 negative dependency records in the library cache. These last two
 things
 are cached as segmented arrays that are scanned linearly - thus
   the
 increased latch retention.

 If your application doesn't have hundreds of distinct Oracle
   users,
 or if you can afford the extra latch gets and longer latch
   retention,
 then you will probably not notice all of this unless you start
   doing
 library cache dumps.

 That is, the use of public synonyms is a major scalability
   threat,
 but
 does not normally cause performance problems.

 @   Regards,
 @   Steve Adams
 @   http://www.ixora.com.au/ - For DBAs
 @   http://www.christianity.net.au/  - For all


 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Steve Adams
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051
   http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting
   services

   -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
   in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You
   may
 also send the HELP command for other information (like
   subscribing).
   
   
__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
Please see the

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Tanel Poder

Hi!

 If you intend to use roles to simplify privilege management, you are
 almost forced to use public synonyms, as you cannot create a private
 synonym owned by a role. Your other alternative is to hard-code the

How can you create a public synonym OWNED by a role?
This is new to me, despite the knowledge that roles and usernames are kept
in the same base table...

Tanel.




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Jared Still

Tanel,

I'm fairly sure that Rachel was not implying that a role
could own a synonym, public or private.

The point was that using role based privilege management,
you either create private synonyms for each user, or create
public synonyms.

Another alternative is a logon trigger that does an 
'alter session set current_schema=schema', though that
might become unwieldy with a large number of users, or
when forced to work with an app that connects/disconnects
repeatedly.

Jared

On Sat, 2004-01-10 at 10:24, Tanel Poder wrote:
 Hi!
 
  If you intend to use roles to simplify privilege management, you are
  almost forced to use public synonyms, as you cannot create a private
  synonym owned by a role. Your other alternative is to hard-code the
 
 How can you create a public synonym OWNED by a role?
 This is new to me, despite the knowledge that roles and usernames are kept
 in the same base table...
 
 Tanel.
 
 
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Tanel Poder
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

Having worked in an environment where every user was a distinct named
user, and I therefore could (and at points did) have something 1700
distinct connections (yes, I said one thousand seven hundred).

I like connection pooling. It limits the stress on the database,
because the number of sessions is limited to the maximum of the
connections. And I do work with them on that.

It has its drawbacks. Most notable, and one I did not get a good answer
to at the Server Technology Forum at UKOUG, is that you cannot really
do a 10046 trace on a session, as you can't distinguish sessions. Gaja
says you can do that in 10g but telling me to upgrade to a
not-yet-released piece of software doesn't help me now.

I'm working on a way around that, I *may* have one, but it's untested
for the moment (I have to bully the programmers into changing code,
which is as likely to happen as it is for me to win the lottery without
buying a ticket). 

In any case, connection pooling appears to be the way of the future for
forward-facing web applications, so we have to deal with it

Rachel
--- Ryan [EMAIL PROTECTED] wrote:
 how do you feel about connection pooling? Our software engineers
 implemented
 that here? Am I wrong to be concerned about large numbers of users
 using the
 same named user?
 
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Saturday, January 10, 2004 6:39 AM
 
 
  Steve,
 
  By distinct user do you mean distinct username? Or sid/serial#
  combination? In my case, we use connection pooling, while there may
 be
  up to 300 sessions, they are all the same named user.
 
  Rachel
 
  --- Steve Adams [EMAIL PROTECTED] wrote:
   Hi All,
  
   Someone has alerted me to this thread, and asked for a comment.
   On a quick scan, and it seems to me that you've mostly got it
 right.
  
   The problem is that when an SQL statement that refers to its
 base
   objects via public synonyms is shared by multiple distinct Oracle
   users,
   then name resolution and permission checking need to repeated for
   each
   distinct user, and because the results of these actions are
 cached on
   the shared cursor, they increase the cost of subsequent such
   operations.
   That is, public synonyms cause extended latch retention as well
 as
   additional latching.
  
   For example, if 500 distinct users share 200 SQL statements that
   refer
   300 times to 100 base tables via public synonyms. Then there will
   also
   be 100 * 500 non-existent objects in both the dictionary cache
 and
   the
   library cache; 200 * 500 cursor authorization structures; and 300
 *
   500
   negative dependency records in the library cache. These last two
   things
   are cached as segmented arrays that are scanned linearly - thus
 the
   increased latch retention.
  
   If your application doesn't have hundreds of distinct Oracle
 users,
   or if you can afford the extra latch gets and longer latch
 retention,
   then you will probably not notice all of this unless you start
 doing
   library cache dumps.
  
   That is, the use of public synonyms is a major scalability
 threat,
   but
   does not normally cause performance problems.
  
   @   Regards,
   @   Steve Adams
   @   http://www.ixora.com.au/ - For DBAs
   @   http://www.christianity.net.au/  - For all
  
  
   --
   Please see the official ORACLE-L FAQ: http://www.orafaq.net
   --
   Author: Steve Adams
 INET: [EMAIL PROTECTED]
  
   Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
   San Diego, California-- Mailing list and web hosting
 services
  
 -
   To REMOVE yourself from this mailing list, send an E-Mail message
   to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
 in
   the message BODY, include a line containing: UNSUB ORACLE-L
   (or the name of mailing list you want to be removed from).  You
 may
   also send the HELP command for other information (like
 subscribing).
 
 
  __
  Do you Yahoo!?
  Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
  http://hotjobs.sweepstakes.yahoo.com/signingbonus
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting
 services
 
 -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like
 subscribing).
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Ryan
   INET: [EMAIL PROTECTED]

Re: Suggestions Needed: Latch free - library cache

Tanel,

 you can't create a public synonym owned by a role, sorry if I wasn't
clear. But public synonyms are available to all users, regardless of
the role you assign to the user. So you have to use public synonyms
when you use roles, unless you either specify the object owner name in
all references or, as I was gently reminded, you use set current_schema
to the object owner. 

Because I have applications that use data from multiple owners, I have
to use public synonyms. However, I am going to see if I can use set
current_schema wherever possible in the future.

Rachel
--- Tanel Poder [EMAIL PROTECTED] wrote:
 Hi!
 
  If you intend to use roles to simplify privilege management, you
 are
  almost forced to use public synonyms, as you cannot create a
 private
  synonym owned by a role. Your other alternative is to hard-code the
 
 How can you create a public synonym OWNED by a role?
 This is new to me, despite the knowledge that roles and usernames are
 kept
 in the same base table...
 
 Tanel.
 
 
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Tanel Poder
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

yep, that's what I meant :)

additionally, if you decide to create private synonyms for each user,
you still have the potential problem of forgetting a user when you add
a new synonym. Yes, I use SQL to generate the SQL I need but even so,
it's a lot easier to include create public synonym and grant xyz to
role in the object creation script


--- Jared Still [EMAIL PROTECTED] wrote:
 Tanel,
 
 I'm fairly sure that Rachel was not implying that a role
 could own a synonym, public or private.
 
 The point was that using role based privilege management,
 you either create private synonyms for each user, or create
 public synonyms.
 
 Another alternative is a logon trigger that does an 
 'alter session set current_schema=schema', though that
 might become unwieldy with a large number of users, or
 when forced to work with an app that connects/disconnects
 repeatedly.
 
 Jared
 
 On Sat, 2004-01-10 at 10:24, Tanel Poder wrote:
  Hi!
  
   If you intend to use roles to simplify privilege management, you
 are
   almost forced to use public synonyms, as you cannot create a
 private
   synonym owned by a role. Your other alternative is to hard-code
 the
  
  How can you create a public synonym OWNED by a role?
  This is new to me, despite the knowledge that roles and usernames
 are kept
  in the same base table...
  
  Tanel.
  
  
  
  
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Tanel Poder
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting
 services
 
 -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like
 subscribing).
  
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Jared Still
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Murali Vallath

Rachel,

I think we had a similar situation in my previous life, when we had to get the programmers to modify their code to trace specific areas of the application. The way we went about doing this (in this case they had a concept of using a catalog.xml file where all the SQL queries got stored and the Java called specific queries identified by TAGS) is define the 10046 event in the catalog and called the on off routines using specific TAG's to trace.

query name="traceTurnOn"
sql![CDATA[
alter session set events '10046 trace name context forever, level 12'
]]/sql
/query

query name="traceTurnOff"
sql![CDATA[
alter session set events '10046 trace name context off'
]]/sql
/query
Java code snippet to invoke the trace queries:
stmt.executeUpdate(sql);
where sql is the 'traceTurnOn' or 'traceTurnOff' queries retrieved from the catalog,xml file mentioned earlier.
Rachel Carmichael [EMAIL PROTECTED] wrote:
Having worked in an environment where every user was a distinct nameduser, and I therefore could (and at points did) have something 1700distinct connections (yes, I said one thousand seven hundred).I like connection pooling. It limits the stress on the database,because the number of sessions is limited to the maximum of theconnections. And I do work with them on that.It has its drawbacks. Most notable, and one I did not get a good answerto at the Server Technology Forum at UKOUG, is that you cannot reallydo a 10046 trace on a session, as you can't distinguish sessions. Gajasays you can do that in 10g but telling me to upgrade to anot-yet-released piece of software doesn't help me now.I'm working on a way around that, I *may* have one, but it's untestedfor the moment (I have to bully the programmers into changing
code,which is as likely to happen as it is for me to win the lottery withoutbuying a ticket). In any case, connection pooling appears to be the way of the future forforward-facing web applications, so we have to deal with itRachel--- Ryan <[EMAIL PROTECTED]>wrote: how do you feel about connection pooling? Our software engineers implemented that here? Am I wrong to be concerned about large numbers of users using the same named user? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, January 10, 2004 6:39 AMSteve, By "distinct user" do you mean distinct username? Or sid/serial# combination? In my case, we use connection pooling, while there may be up to 300 sessions, they are all the same named user. !
;
Rachel --- Steve Adams <[EMAIL PROTECTED]>wrote: Hi All, Someone has alerted me to this thread, and asked for a comment. On a quick scan, and it seems to me that you've mostly got it right. The "problem" is that when an SQL statement that refers to its base objects via public synonyms is shared by multiple distinct Oracle users, then name resolution and permission checking need to repeated for each distinct user, and because the results of these actions are cached on the shared cursor, they increase the cost of subsequent such operations. That is, public synonyms cause extended latch retention as well as additional latching.
For example, if 500 distinct users share 200 SQL statements that refer 300 times to 100 base tables via public synonyms. Then there will also be 100 * 500 non-existent objects in both the dictionary cache and the library cache; 200 * 500 cursor authorization structures; and 300 * 500 negative dependency records in the library cache. These last two things are cached as segmented arrays that are scanned linearly - thus the increased latch retention. If your application doesn't have hundreds of distinct Oracle users, or if you can afford the extra latch gets and longer latch retention, then you will probably not notice all of this unless you start
doing library cache dumps. That is, the use of public synonyms is a major scalability threat, but does not normally cause performance problems. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - !
To
REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).__ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus --

Re: Suggestions Needed: Latch free - library cache

2004-01-10 Thread Tanel Poder

Ok, thanks, this makes sense.

Tanel.

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, January 10, 2004 10:34 PM


 yep, that's what I meant :)
 
 additionally, if you decide to create private synonyms for each user,
 you still have the potential problem of forgetting a user when you add
 a new synonym. Yes, I use SQL to generate the SQL I need but even so,
 it's a lot easier to include create public synonym and grant xyz to
 role in the object creation script
 
 
 --- Jared Still [EMAIL PROTECTED] wrote:
  Tanel,
  
  I'm fairly sure that Rachel was not implying that a role
  could own a synonym, public or private.
  
  The point was that using role based privilege management,
  you either create private synonyms for each user, or create
  public synonyms.
  
  Another alternative is a logon trigger that does an 
  'alter session set current_schema=schema', though that
  might become unwieldy with a large number of users, or
  when forced to work with an app that connects/disconnects
  repeatedly.
  
  Jared
  
  On Sat, 2004-01-10 at 10:24, Tanel Poder wrote:
   Hi!
   
If you intend to use roles to simplify privilege management, you
  are
almost forced to use public synonyms, as you cannot create a
  private
synonym owned by a role. Your other alternative is to hard-code
  the
   
   How can you create a public synonym OWNED by a role?
   This is new to me, despite the knowledge that roles and usernames
  are kept
   in the same base table...
   
   Tanel.
   
   
   
   
   -- 
   Please see the official ORACLE-L FAQ: http://www.orafaq.net
   -- 
   Author: Tanel Poder
 INET: [EMAIL PROTECTED]
   
   Fat City Network Services-- 858-538-5051 http://www.fatcity.com
   San Diego, California-- Mailing list and web hosting
  services
  
  -
   To REMOVE yourself from this mailing list, send an E-Mail message
   to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
   the message BODY, include a line containing: UNSUB ORACLE-L
   (or the name of mailing list you want to be removed from).  You may
   also send the HELP command for other information (like
  subscribing).
   
  
  
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  -- 
  Author: Jared Still
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
 
 
 __
 Do you Yahoo!?
 Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
 http://hotjobs.sweepstakes.yahoo.com/signingbonus
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Rachel Carmichael
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Suggestions Needed: Latch free - library cache

2004-01-09 Thread Tracy Rahmlow

During the Hotsos course I thought I remember hearing that the pool could be too large and that could have a negative impact on the library cache latch. Am I confusing this with something else (maybe the buffer busy event)?  If true how do you go about determining the optimal size? 

From:Cary Millsap [EMAIL PROTECTED]@fatcity.com on 01/07/2004 07:44 PM PST
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: Suggestions Needed: Latch free - library cache

One way is by writing applications that use persistent connections. A mid-tier program ignites at 8amJan 1, connects to the Oracle instance, and then parses all the SQL it will use for the remainder of the year. The interface to this program from the user side is via "services," like "hire an employee," "fire an employee," "look up a salary," or whatever. Thousands of users throughout the year use the services, but the only parse calls the application makes all year occurred during the first few minutes of the morning on New Year's Day.

Advantages are huge if you eliminate what would otherwise have been thousands of connections/disconnections and parse calls per day. All the db gets are binds, executes, and fetches except for the first few minutes after instance start-up.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Performance Diagnosis101: 1/27 Atlanta
- SQL Optimization101: 2/16 Dallas
- Hotsos Symposium 2004: March 710 Dallas
- Visit www.hotsos.com for schedule details...

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ryan
Sent: Wednesday, January 07, 2004 6:15 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Suggestions Needed: Latch free - library cache

how do you limit excessive soft parsing? 

- Original Message - 

From:Bobak, Mark 

To:Multiple recipients of list ORACLE-L 

Sent:Wednesday, January 07, 2004 5:59 PM

Subject:RE: Suggestions Needed: Latch free - library cache

Tracy,

What Oracle version? If you're not patched up to the latest patchset for your release, it's always a good idea to do so, as library cache bugs seem to invariably appear in every release.

Has your code changed recently? Has your usage increased recently?

Finally, library cache latch contention can be a sign of excessive soft parsing (hard parsing usually causes more shared pool latch contention). Do you have session_cached_cursors set?

Just some thoughts

-Mark

Mark J. Bobak 
Oracle DBA 
ProQuest Company 
Ann Arbor, MI 
Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown

-Original Message-
From: Tracy Rahmlow [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 07, 2004 5:40 PM
To: Multiple recipients of list ORACLE-L
Subject: Suggestions Needed: Latch free - library cache

We have experienced intermittent problems (slow response time) with our oltp database today. There appears to be a large number of latch free events and the p2 parameter is indicating an issue with the library cache. Any thoughts on where to go next? 

American Express made the following
annotations on 01/07/2004 03:36:25 PM
--
**

This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you.

**

==

American Express made the following
 annotations on 01/09/2004 07:39:30 AM
--
**

 "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited.  If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments.  Thank you."

**

==

RE: Suggestions Needed: Latch free - library cache

Title: Message

Tracy,

That's
true, though it's less true in 8i and later than it was in 8.0 and
earlier. This is due to the way that Oracle organizes free memory
chunks.

As to
determining optimal size, well, if you run into ORA-4031 errors, then, short of
a very poorly written application (that does lots of literal SQL), or a bug in
your version of Oracle, the shared pool is probably too small. If
you have lots of spare free memory after the system has been up and stable for a
while, then your shared pool is probably too large. Steve Adams has the
shared_pool_spare_free.sql script on his website, http://www.ixora.com.au/.

Hope
that helps,

-Mark

Mark J.
Bobak Oracle DBA ProQuest Company
Ann Arbor,
MI "Imagination
was given to man to compensate him for what he is not, and a sense of humor was
provided to console him for what he is." --Unknown

-Original Message-From: Tracy Rahmlow
[mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 9:44
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
Suggestions Needed: Latch free - library cacheDuring the Hotsos course I thought I remember hearing
that the pool could be too large and that could have a negative impact on the
library cache latch. Am I confusing this with something else (maybe the
buffer busy event)? If true how do you go about determining the optimal
size?
From:
"Cary Millsap" [EMAIL PROTECTED]@fatcity.com on 01/07/2004
07:44 PM PST
Please respond to
[EMAIL PROTECTED]
Sent by:
[EMAIL PROTECTED]
To:
"Multiple recipients of list
ORACLE-L" [EMAIL PROTECTED] cc: Subject:
RE: Suggestions Needed: Latch free -
library cache One way is by writing
applications that use persistent connections. A mid-tier program ignites at
8amJan 1, connects to the Oracle instance, and then parses all the SQL it will
use for the remainder of the year. The interface to this program from the user
side is via "services," like "hire an employee," "fire an employee," "look up
a salary," or whatever. Thousands of users throughout the year use the
services, but the only parse calls the application makes all year occurred
during the first few minutes of the morning on New Year's Day.
Advantages are huge if
you eliminate what would otherwise have been thousands of
connections/disconnections and parse calls per day. All the db gets are binds,
executes, and fetches except for the first few minutes after instance
start-up. Cary Millsap Hotsos Enterprises,
Ltd. http://www.hotsos.com Upcoming
events: - Performance Diagnosis101: 1/27
Atlanta - SQL Optimization101: 2/16
Dallas - Hotsos Symposium 2004: March 710
Dallas - Visit www.hotsos.com for schedule
details... -Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ryan Sent: Wednesday, January 07, 2004 6:15 PM To: Multiple recipients of list ORACLE-L Subject: Re: Suggestions Needed: Latch free - library
cache how do
you limit excessive soft parsing? - Original
Message - From:Bobak, Mark To:Multiple recipients of list ORACLE-L Sent:Wednesday, January 07, 2004 5:59
PM Subject:RE:
Suggestions Needed: Latch free - library cache Tracy, What Oracle version? If you're
not patched up to the latest patchset for your release, it's always a good
idea to do so, as library cache bugs seem to invariably appear in every
release. Has
your code changed recently? Has your usage increased recently?
Finally, library cache
latch contention can be a sign of excessive soft parsing (hard parsing usually
causes more shared pool latch contention). Do you have
session_cached_cursors set?
Just some thoughts -Mark Mark J. Bobak Oracle
DBA ProQuest Company Ann Arbor, MI
"Imagination was given to man to compensate him for
what he is not, and a sense of humor was provided to console him for what he
is." --Unknown -Original
Message- From: Tracy Rahmlow
[mailto:[EMAIL PROTECTED] Sent:
Wednesday, January 07, 2004 5:40 PM To:
Multiple recipients of list ORACLE-L Subject:
Suggestions Needed: Latch free - library cache We have experienced intermittent problems (slow response time) with our
oltp database today. There appears to be a large number of latch free
events and the p2 parameter is indicating an issue with the library cache.
Any thoughts on where to go next? American
Express made the following annotations on 01/07/2004
03:36:25 PM --
**
"This message and any attachments are solely for the
intended recipient and may contain confidential or privileged information. If
you are not the intended re

Re: Suggestions Needed: Latch free - library cache

2004-01-09 Thread Yong Huang

  To the OP: Other people point out common reasons for library cache latch
  contention. A less common reason is extensive use of public synonyms.
  If that's the reason, you also see row cache objects latch contention.

 I'm not sure that's right.  If everyone uses a public synonym, then
 you get one sql text, and one cursor.  I think the contention appears
 because everyone has to have a 'non-existent' reference in memory
 to say that they don't own an object with the same name as the public
 synonym - consequently if you have lots of users who have to check
 long chains of  'non-existent' then the latches get held for longer
 periods of time.

Hi Jonathan,

I don't see how your statement contradicts the claim that heavy use of public
synonyms causes contention for not only library cache latches but also row
cache objects latches. What I had in mind is Steve Adams' test. Here's the URL
http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I understand right,
the additional row cache objects latch gets are for synonym translations,
particularly public synonym translations.

Yong Huang

__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Suggestions Needed: Latch free - library cache

2004-01-09 Thread Tanel Poder

 During the Hotsos course I thought I remember hearing that the pool
 could be too large and that could have a negative impact on the
 library cache latch.  Am I confusing this with something else (maybe
 the buffer busy event)?   If true how do you go about determining the
 optimal size?  

Having too large shared pool may cause contention on shared pool latch, especially 
when shared pool's LRU lists get very long, causing searching for unpinned recreatable 
chunks to take long time (using CPU and preventing others from scanning the list). 

Before 9i there was only one shared pool latch protecting operations on whole shared 
pool. But starting from 9i, this issue can be relieved somewhat, but splitting the 
shared pool heap to several smaller heaps and assigning one shared pool child latch 
for each of them.
This can be controlled using _kghdsidx_count parameter and the max in 9.2 seems to be 
7, since there are 7 shared pool child latches (at least on W2K and Linux).

However, when you split your shared pool to several parts, you might hit ORA-4031 
error even when some shared pool heaps do have enough usable space, but the current 
one where allocation was tried, doesn't.

Tanel.


Tanel.

Re: Suggestions Needed: Latch free - library cache

2004-01-09 Thread Ryan

I remember readign that article and I thought the results that the
contention was very minor? Steve, are you monitoring?

It seemed like one of those things that its so minor its not really
something to worry about?
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, January 09, 2004 12:09 PM


   To the OP: Other people point out common reasons for library cache
latch
   contention. A less common reason is extensive use of public synonyms.
   If that's the reason, you also see row cache objects latch contention.
 
  I'm not sure that's right.  If everyone uses a public synonym, then
  you get one sql text, and one cursor.  I think the contention appears
  because everyone has to have a 'non-existent' reference in memory
  to say that they don't own an object with the same name as the public
  synonym - consequently if you have lots of users who have to check
  long chains of  'non-existent' then the latches get held for longer
  periods of time.

 Hi Jonathan,

 I don't see how your statement contradicts the claim that heavy use of
public
 synonyms causes contention for not only library cache latches but also row
 cache objects latches. What I had in mind is Steve Adams' test. Here's the
URL
 http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I understand
right,
 the additional row cache objects latch gets are for synonym translations,
 particularly public synonym translations.

 Yong Huang

 __
 Do you Yahoo!?
 Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
 http://hotjobs.sweepstakes.yahoo.com/signingbonus
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Yong Huang
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

2004-01-09 Thread K Gopalakrishnan

Yong:

I have not followed the thread completely. So I may be missing
something obvious ;)

BEGIN-NON TECHNICAL

Many applications (for example Oracle Applications) use public synonyms
heavily and running with better (or acceptable) performance.  We should
not really worry about the milli second performance improvements
comparing with the coding/application development flexibilities offered
by public synonymns. 

If you look at Steve's test carefully, the improvement or over head in
the public synonym to private synonym is around 10% of the CPU time and
latch gets. How much performance improvement you can expect in
practical systems with the additiona  10% of latch gets/CPU times
comparing with the overall system performance. I would expect less than
(LESS THAN) 1-2% in total response time.

END NON TECHNICAL

The actual over head is coming from the negative dependency tracking or
high version count in the Library cache. But this will be an issue only
 when too many users are connected and accssing the database  with too
many accounts (different parsing user_id). But this is rarely a
situation in many of the packaged application as most of the
application connects to the database using a single account (like
'APPS' user in Oracle eBusiness suite) and no negative dependency or
multi version of SQLs are  an issue here (at least in a single instance
oracle). The over head can be little higher in RAC environment as the
Library Cache and Row cache is globally co-ordinated. 

However I have not really seen major problem (may be I have not
observed them too keen) with the public synonyms as most of the
packaged applications I have worked are using a single oracle account
to connect to the database. It is just my personal observation, and may
be Jonathan can have a different opinion.

Regards,
KG

PS : Aplogies if some one has already addressed this issue, I have been
traveling and didn;t have enough free time to read all the posts. Seen
lots of posts flooding on this topic and jumped in out of curiosity.  





--- Yong Huang [EMAIL PROTECTED] wrote:
   To the OP: Other people point out common reasons for library
 cache latch
   contention. A less common reason is extensive use of public
 synonyms.
   If that's the reason, you also see row cache objects latch
 contention.
 
  I'm not sure that's right.  If everyone uses a public synonym, then
  you get one sql text, and one cursor.  I think the contention
 appears
  because everyone has to have a 'non-existent' reference in memory
  to say that they don't own an object with the same name as the
 public
  synonym - consequently if you have lots of users who have to check
  long chains of  'non-existent' then the latches get held for longer
  periods of time.
 
 Hi Jonathan,
 
 I don't see how your statement contradicts the claim that heavy use
 of public
 synonyms causes contention for not only library cache latches but
 also row
 cache objects latches. What I had in mind is Steve Adams' test.
 Here's the URL
 http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I
 understand right,
 the additional row cache objects latch gets are for synonym
 translations,
 particularly public synonym translations.
 
 Yong Huang
 


=
Have a nice day !!

Best Regards,
K Gopalakrishnan,
Bangalore, INDIA.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

just to weigh in on the side of use of public synonyms.

If you intend to use roles to simplify privilege management, you are
almost forced to use public synonyms, as you cannot create a private
synonym owned by a role. Your other alternative is to hard-code the
schema owner name in every object access, which somewhat invalidates
the idea of portability and security between dev/test/production
environments, as you would have to maintain the same schema owner name
in all environments.

As a general practice, I create a schema owner, a schema user and a
schema proc_owner  account. I use public synonyms throughout and have
never seen a performance hit because of them.

My (practical) $0.02 

Rachel

--- K Gopalakrishnan [EMAIL PROTECTED] wrote:
 Yong:
 
 I have not followed the thread completely. So I may be missing
 something obvious ;)
 
 BEGIN-NON TECHNICAL
 
 Many applications (for example Oracle Applications) use public
 synonyms
 heavily and running with better (or acceptable) performance.  We
 should
 not really worry about the milli second performance improvements
 comparing with the coding/application development flexibilities
 offered
 by public synonymns. 
 
 If you look at Steve's test carefully, the improvement or over head
 in
 the public synonym to private synonym is around 10% of the CPU time
 and
 latch gets. How much performance improvement you can expect in
 practical systems with the additiona  10% of latch gets/CPU times
 comparing with the overall system performance. I would expect less
 than
 (LESS THAN) 1-2% in total response time.
 
 END NON TECHNICAL
 
 The actual over head is coming from the negative dependency tracking
 or
 high version count in the Library cache. But this will be an issue
 only
  when too many users are connected and accssing the database  with
 too
 many accounts (different parsing user_id). But this is rarely a
 situation in many of the packaged application as most of the
 application connects to the database using a single account (like
 'APPS' user in Oracle eBusiness suite) and no negative dependency or
 multi version of SQLs are  an issue here (at least in a single
 instance
 oracle). The over head can be little higher in RAC environment as the
 Library Cache and Row cache is globally co-ordinated. 
 
 However I have not really seen major problem (may be I have not
 observed them too keen) with the public synonyms as most of the
 packaged applications I have worked are using a single oracle account
 to connect to the database. It is just my personal observation, and
 may
 be Jonathan can have a different opinion.
 
 Regards,
 KG
 
 PS : Aplogies if some one has already addressed this issue, I have
 been
 traveling and didn;t have enough free time to read all the posts.
 Seen
 lots of posts flooding on this topic and jumped in out of curiosity. 
 
 
 
 
 
 
 --- Yong Huang [EMAIL PROTECTED] wrote:
To the OP: Other people point out common reasons for library
  cache latch
contention. A less common reason is extensive use of public
  synonyms.
If that's the reason, you also see row cache objects latch
  contention.
  
   I'm not sure that's right.  If everyone uses a public synonym,
 then
   you get one sql text, and one cursor.  I think the contention
  appears
   because everyone has to have a 'non-existent' reference in memory
   to say that they don't own an object with the same name as the
  public
   synonym - consequently if you have lots of users who have to
 check
   long chains of  'non-existent' then the latches get held for
 longer
   periods of time.
  
  Hi Jonathan,
  
  I don't see how your statement contradicts the claim that heavy use
  of public
  synonyms causes contention for not only library cache latches but
  also row
  cache objects latches. What I had in mind is Steve Adams' test.
  Here's the URL
  http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I
  understand right,
  the additional row cache objects latch gets are for synonym
  translations,
  particularly public synonym translations.
  
  Yong Huang
  
 
 
 =
 Have a nice day !!
 
 Best Regards,
 K Gopalakrishnan,
 Bangalore, INDIA.
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: K Gopalakrishnan
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes

RE: Suggestions Needed: Latch free - library cache

The other option is a user logon trigger that does execute immediate
'alter session set current_schema=appowner';


Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is.  --Unknown


-Original Message-
Sent: Friday, January 09, 2004 2:44 PM
To: Multiple recipients of list ORACLE-L


just to weigh in on the side of use of public synonyms.

If you intend to use roles to simplify privilege management, you are
almost forced to use public synonyms, as you cannot create a private
synonym owned by a role. Your other alternative is to hard-code the
schema owner name in every object access, which somewhat invalidates
the idea of portability and security between dev/test/production
environments, as you would have to maintain the same schema owner name
in all environments.

As a general practice, I create a schema owner, a schema user and a
schema proc_owner  account. I use public synonyms throughout and have
never seen a performance hit because of them.

My (practical) $0.02 

Rachel

--- K Gopalakrishnan [EMAIL PROTECTED] wrote:
 Yong:
 
 I have not followed the thread completely. So I may be missing
 something obvious ;)
 
 BEGIN-NON TECHNICAL
 
 Many applications (for example Oracle Applications) use public
 synonyms
 heavily and running with better (or acceptable) performance.  We
 should
 not really worry about the milli second performance improvements
 comparing with the coding/application development flexibilities
 offered
 by public synonymns. 
 
 If you look at Steve's test carefully, the improvement or over head
 in
 the public synonym to private synonym is around 10% of the CPU time
 and
 latch gets. How much performance improvement you can expect in
 practical systems with the additiona  10% of latch gets/CPU times
 comparing with the overall system performance. I would expect less
 than
 (LESS THAN) 1-2% in total response time.
 
 END NON TECHNICAL
 
 The actual over head is coming from the negative dependency tracking
 or
 high version count in the Library cache. But this will be an issue
 only
  when too many users are connected and accssing the database  with
 too
 many accounts (different parsing user_id). But this is rarely a
 situation in many of the packaged application as most of the
 application connects to the database using a single account (like
 'APPS' user in Oracle eBusiness suite) and no negative dependency or
 multi version of SQLs are  an issue here (at least in a single
 instance
 oracle). The over head can be little higher in RAC environment as the
 Library Cache and Row cache is globally co-ordinated. 
 
 However I have not really seen major problem (may be I have not
 observed them too keen) with the public synonyms as most of the
 packaged applications I have worked are using a single oracle account
 to connect to the database. It is just my personal observation, and
 may
 be Jonathan can have a different opinion.
 
 Regards,
 KG
 
 PS : Aplogies if some one has already addressed this issue, I have
 been
 traveling and didn;t have enough free time to read all the posts.
 Seen
 lots of posts flooding on this topic and jumped in out of curiosity. 
 
 
 
 
 
 
 --- Yong Huang [EMAIL PROTECTED] wrote:
To the OP: Other people point out common reasons for library
  cache latch
contention. A less common reason is extensive use of public
  synonyms.
If that's the reason, you also see row cache objects latch
  contention.
  
   I'm not sure that's right.  If everyone uses a public synonym,
 then
   you get one sql text, and one cursor.  I think the contention
  appears
   because everyone has to have a 'non-existent' reference in memory
   to say that they don't own an object with the same name as the
  public
   synonym - consequently if you have lots of users who have to
 check
   long chains of  'non-existent' then the latches get held for
 longer
   periods of time.
  
  Hi Jonathan,
  
  I don't see how your statement contradicts the claim that heavy use
  of public
  synonyms causes contention for not only library cache latches but
  also row
  cache objects latches. What I had in mind is Steve Adams' test.
  Here's the URL
  http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I
  understand right,
  the additional row cache objects latch gets are for synonym
  translations,
  particularly public synonym translations.
  
  Yong Huang
  
 
 
 =
 Have a nice day !!
 
 Best Regards,
 K Gopalakrishnan,
 Bangalore, INDIA.
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: K Gopalakrishnan
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE

RE: Suggestions Needed: Latch free - library cache

2004-01-09 Thread Niall Litchfield

Hi Rachel

My understanding of the negative aspects of public synonyms is that they
kick in as the number of distinct Oracle Users you have increases. My
*experience* is much the same as yours - namely I almost couldn't care,
however this is predicated on applications tracking users and not using a
different Oracle account for each user. (typically we have 1-2 accounts per
app). The archetype of application development on Oracle says that you have
a different oracle account for each user (albeit probably OS authenticated).
Mind you that same archetype says you can change the sql. I think that the
drawbacks that steve has in mind don't apply to the majority of 3rd party
apps.

Niall 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Suggestions Needed: Latch free - library cache

How would you handle the case where there are many sets of privileges,
depending on which user you log in as?

The trigger would give everyone the right to do anything the owner
could to a table. There are times when I want create a read-only
account in addition to an app user.

we do allow sqlplus access to production by developers -- in a
read-only state so they can investigate end user complaints.


--- Bobak, Mark [EMAIL PROTECTED] wrote:
 The other option is a user logon trigger that does execute immediate
 'alter session set current_schema=appowner';
 
 
 Mark J. Bobak
 Oracle DBA
 ProQuest Company
 Ann Arbor, MI
 Imagination was given to man to compensate him for what he is not,
 and
 a sense of humor was provided to console him for what he is. 
 --Unknown
 
 
 -Original Message-
 Sent: Friday, January 09, 2004 2:44 PM
 To: Multiple recipients of list ORACLE-L
 
 
 just to weigh in on the side of use of public synonyms.
 
 If you intend to use roles to simplify privilege management, you are
 almost forced to use public synonyms, as you cannot create a private
 synonym owned by a role. Your other alternative is to hard-code the
 schema owner name in every object access, which somewhat invalidates
 the idea of portability and security between dev/test/production
 environments, as you would have to maintain the same schema owner
 name
 in all environments.
 
 As a general practice, I create a schema owner, a schema user and
 a
 schema proc_owner  account. I use public synonyms throughout and
 have
 never seen a performance hit because of them.
 
 My (practical) $0.02 
 
 Rachel
 
 --- K Gopalakrishnan [EMAIL PROTECTED] wrote:
  Yong:
  
  I have not followed the thread completely. So I may be missing
  something obvious ;)
  
  BEGIN-NON TECHNICAL
  
  Many applications (for example Oracle Applications) use public
  synonyms
  heavily and running with better (or acceptable) performance.  We
  should
  not really worry about the milli second performance improvements
  comparing with the coding/application development flexibilities
  offered
  by public synonymns. 
  
  If you look at Steve's test carefully, the improvement or over head
  in
  the public synonym to private synonym is around 10% of the CPU time
  and
  latch gets. How much performance improvement you can expect in
  practical systems with the additiona  10% of latch gets/CPU times
  comparing with the overall system performance. I would expect less
  than
  (LESS THAN) 1-2% in total response time.
  
  END NON TECHNICAL
  
  The actual over head is coming from the negative dependency
 tracking
  or
  high version count in the Library cache. But this will be an issue
  only
   when too many users are connected and accssing the database  with
  too
  many accounts (different parsing user_id). But this is rarely a
  situation in many of the packaged application as most of the
  application connects to the database using a single account (like
  'APPS' user in Oracle eBusiness suite) and no negative dependency
 or
  multi version of SQLs are  an issue here (at least in a single
  instance
  oracle). The over head can be little higher in RAC environment as
 the
  Library Cache and Row cache is globally co-ordinated. 
  
  However I have not really seen major problem (may be I have not
  observed them too keen) with the public synonyms as most of the
  packaged applications I have worked are using a single oracle
 account
  to connect to the database. It is just my personal observation, and
  may
  be Jonathan can have a different opinion.
  
  Regards,
  KG
  
  PS : Aplogies if some one has already addressed this issue, I have
  been
  traveling and didn;t have enough free time to read all the posts.
  Seen
  lots of posts flooding on this topic and jumped in out of
 curiosity. 
  
  
  
  
  
  
  --- Yong Huang [EMAIL PROTECTED] wrote:
 To the OP: Other people point out common reasons for library
   cache latch
 contention. A less common reason is extensive use of public
   synonyms.
 If that's the reason, you also see row cache objects latch
   contention.
   
I'm not sure that's right.  If everyone uses a public synonym,
  then
you get one sql text, and one cursor.  I think the contention
   appears
because everyone has to have a 'non-existent' reference in
 memory
to say that they don't own an object with the same name as the
   public
synonym - consequently if you have lots of users who have to
  check
long chains of  'non-existent' then the latches get held for
  longer
periods of time.
   
   Hi Jonathan,
   
   I don't see how your statement contradicts the claim that heavy
 use
   of public
   synonyms causes contention for not only library cache latches but
   also row
   cache objects latches. What I had in mind is Steve Adams' test.
   Here's the URL
   http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I
   understand right,
   the additional row cache objects latch gets

Re: Suggestions Needed: Latch free - library cache

2004-01-09 Thread Ryan

that method is limiting. Lets say you publish data from various sources
using transportable tablespaces... Its much easier to manage this
publication by putting each transported tablespace in its own user.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, January 09, 2004 2:59 PM


 The other option is a user logon trigger that does execute immediate
 'alter session set current_schema=appowner';


 Mark J. Bobak
 Oracle DBA
 ProQuest Company
 Ann Arbor, MI
 Imagination was given to man to compensate him for what he is not, and
 a sense of humor was provided to console him for what he is.  --Unknown


 -Original Message-
 Sent: Friday, January 09, 2004 2:44 PM
 To: Multiple recipients of list ORACLE-L


 just to weigh in on the side of use of public synonyms.

 If you intend to use roles to simplify privilege management, you are
 almost forced to use public synonyms, as you cannot create a private
 synonym owned by a role. Your other alternative is to hard-code the
 schema owner name in every object access, which somewhat invalidates
 the idea of portability and security between dev/test/production
 environments, as you would have to maintain the same schema owner name
 in all environments.

 As a general practice, I create a schema owner, a schema user and a
 schema proc_owner  account. I use public synonyms throughout and have
 never seen a performance hit because of them.

 My (practical) $0.02

 Rachel

 --- K Gopalakrishnan [EMAIL PROTECTED] wrote:
  Yong:
 
  I have not followed the thread completely. So I may be missing
  something obvious ;)
 
  BEGIN-NON TECHNICAL
 
  Many applications (for example Oracle Applications) use public
  synonyms
  heavily and running with better (or acceptable) performance.  We
  should
  not really worry about the milli second performance improvements
  comparing with the coding/application development flexibilities
  offered
  by public synonymns.
 
  If you look at Steve's test carefully, the improvement or over head
  in
  the public synonym to private synonym is around 10% of the CPU time
  and
  latch gets. How much performance improvement you can expect in
  practical systems with the additiona  10% of latch gets/CPU times
  comparing with the overall system performance. I would expect less
  than
  (LESS THAN) 1-2% in total response time.
 
  END NON TECHNICAL
 
  The actual over head is coming from the negative dependency tracking
  or
  high version count in the Library cache. But this will be an issue
  only
   when too many users are connected and accssing the database  with
  too
  many accounts (different parsing user_id). But this is rarely a
  situation in many of the packaged application as most of the
  application connects to the database using a single account (like
  'APPS' user in Oracle eBusiness suite) and no negative dependency or
  multi version of SQLs are  an issue here (at least in a single
  instance
  oracle). The over head can be little higher in RAC environment as the
  Library Cache and Row cache is globally co-ordinated.
 
  However I have not really seen major problem (may be I have not
  observed them too keen) with the public synonyms as most of the
  packaged applications I have worked are using a single oracle account
  to connect to the database. It is just my personal observation, and
  may
  be Jonathan can have a different opinion.
 
  Regards,
  KG
 
  PS : Aplogies if some one has already addressed this issue, I have
  been
  traveling and didn;t have enough free time to read all the posts.
  Seen
  lots of posts flooding on this topic and jumped in out of curiosity.
 
 
 
 
 
 
  --- Yong Huang [EMAIL PROTECTED] wrote:
 To the OP: Other people point out common reasons for library
   cache latch
 contention. A less common reason is extensive use of public
   synonyms.
 If that's the reason, you also see row cache objects latch
   contention.
   
I'm not sure that's right.  If everyone uses a public synonym,
  then
you get one sql text, and one cursor.  I think the contention
   appears
because everyone has to have a 'non-existent' reference in memory
to say that they don't own an object with the same name as the
   public
synonym - consequently if you have lots of users who have to
  check
long chains of  'non-existent' then the latches get held for
  longer
periods of time.
  
   Hi Jonathan,
  
   I don't see how your statement contradicts the claim that heavy use
   of public
   synonyms causes contention for not only library cache latches but
   also row
   cache objects latches. What I had in mind is Steve Adams' test.
   Here's the URL
   http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I
   understand right,
   the additional row cache objects latch gets are for synonym
   translations,
   particularly public synonym translations.
  
   Yong Huang
  
  
 
  =
  Have a nice day !!

RE: Suggestions Needed: Latch free - library cache

Um, roles, privileges, etc are administered however you'd like.  

The only suggestion I'm making is that rather than having public
synonyms for all objects in your app_owner schema, each user which needs
default access to the objects in the app_owner schema, gets access to
that schema via the logon trigger that sets current_schema.  This does
(should) not have any effect on how you manage roles and permissions,
just how Oracle does default object resolution.

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is.  --Unknown


-Original Message-
Sent: Friday, January 09, 2004 3:35 PM
To: Multiple recipients of list ORACLE-L


How would you handle the case where there are many sets of privileges,
depending on which user you log in as?

The trigger would give everyone the right to do anything the owner
could to a table. There are times when I want create a read-only
account in addition to an app user.

we do allow sqlplus access to production by developers -- in a
read-only state so they can investigate end user complaints.


--- Bobak, Mark [EMAIL PROTECTED] wrote:
 The other option is a user logon trigger that does execute immediate
 'alter session set current_schema=appowner';
 
 
 Mark J. Bobak
 Oracle DBA
 ProQuest Company
 Ann Arbor, MI
 Imagination was given to man to compensate him for what he is not,
 and
 a sense of humor was provided to console him for what he is. 
 --Unknown
 
 
 -Original Message-
 Sent: Friday, January 09, 2004 2:44 PM
 To: Multiple recipients of list ORACLE-L
 
 
 just to weigh in on the side of use of public synonyms.
 
 If you intend to use roles to simplify privilege management, you are
 almost forced to use public synonyms, as you cannot create a private
 synonym owned by a role. Your other alternative is to hard-code the
 schema owner name in every object access, which somewhat invalidates
 the idea of portability and security between dev/test/production
 environments, as you would have to maintain the same schema owner
 name
 in all environments.
 
 As a general practice, I create a schema owner, a schema user and
 a
 schema proc_owner  account. I use public synonyms throughout and
 have
 never seen a performance hit because of them.
 
 My (practical) $0.02 
 
 Rachel
 
 --- K Gopalakrishnan [EMAIL PROTECTED] wrote:
  Yong:
  
  I have not followed the thread completely. So I may be missing
  something obvious ;)
  
  BEGIN-NON TECHNICAL
  
  Many applications (for example Oracle Applications) use public
  synonyms
  heavily and running with better (or acceptable) performance.  We
  should
  not really worry about the milli second performance improvements
  comparing with the coding/application development flexibilities
  offered
  by public synonymns. 
  
  If you look at Steve's test carefully, the improvement or over head
  in
  the public synonym to private synonym is around 10% of the CPU time
  and
  latch gets. How much performance improvement you can expect in
  practical systems with the additiona  10% of latch gets/CPU times
  comparing with the overall system performance. I would expect less
  than
  (LESS THAN) 1-2% in total response time.
  
  END NON TECHNICAL
  
  The actual over head is coming from the negative dependency
 tracking
  or
  high version count in the Library cache. But this will be an issue
  only
   when too many users are connected and accssing the database  with
  too
  many accounts (different parsing user_id). But this is rarely a
  situation in many of the packaged application as most of the
  application connects to the database using a single account (like
  'APPS' user in Oracle eBusiness suite) and no negative dependency
 or
  multi version of SQLs are  an issue here (at least in a single
  instance
  oracle). The over head can be little higher in RAC environment as
 the
  Library Cache and Row cache is globally co-ordinated. 
  
  However I have not really seen major problem (may be I have not
  observed them too keen) with the public synonyms as most of the
  packaged applications I have worked are using a single oracle
 account
  to connect to the database. It is just my personal observation, and
  may
  be Jonathan can have a different opinion.
  
  Regards,
  KG
  
  PS : Aplogies if some one has already addressed this issue, I have
  been
  traveling and didn;t have enough free time to read all the posts.
  Seen
  lots of posts flooding on this topic and jumped in out of
 curiosity. 
  
  
  
  
  
  
  --- Yong Huang [EMAIL PROTECTED] wrote:
 To the OP: Other people point out common reasons for library
   cache latch
 contention. A less common reason is extensive use of public
   synonyms.
 If that's the reason, you also see row cache objects latch
   contention.
   
I'm not sure that's right.  If everyone uses a public synonym,
  then
you get one sql

RE: Suggestions Needed: Latch free - library cache

Well, any solution will require consideration of the application design
and implementation.  I'm just offering a possible suggestion. ;-)

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is.  --Unknown


-Original Message-
Sent: Friday, January 09, 2004 4:00 PM
To: Multiple recipients of list ORACLE-L


that method is limiting. Lets say you publish data from various sources
using transportable tablespaces... Its much easier to manage this
publication by putting each transported tablespace in its own user.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, January 09, 2004 2:59 PM


 The other option is a user logon trigger that does execute immediate
 'alter session set current_schema=appowner';


 Mark J. Bobak
 Oracle DBA
 ProQuest Company
 Ann Arbor, MI
 Imagination was given to man to compensate him for what he is not,
and
 a sense of humor was provided to console him for what he is.
--Unknown


 -Original Message-
 Sent: Friday, January 09, 2004 2:44 PM
 To: Multiple recipients of list ORACLE-L


 just to weigh in on the side of use of public synonyms.

 If you intend to use roles to simplify privilege management, you are
 almost forced to use public synonyms, as you cannot create a private
 synonym owned by a role. Your other alternative is to hard-code the
 schema owner name in every object access, which somewhat invalidates
 the idea of portability and security between dev/test/production
 environments, as you would have to maintain the same schema owner name
 in all environments.

 As a general practice, I create a schema owner, a schema user and
a
 schema proc_owner  account. I use public synonyms throughout and
have
 never seen a performance hit because of them.

 My (practical) $0.02

 Rachel

 --- K Gopalakrishnan [EMAIL PROTECTED] wrote:
  Yong:
 
  I have not followed the thread completely. So I may be missing
  something obvious ;)
 
  BEGIN-NON TECHNICAL
 
  Many applications (for example Oracle Applications) use public
  synonyms
  heavily and running with better (or acceptable) performance.  We
  should
  not really worry about the milli second performance improvements
  comparing with the coding/application development flexibilities
  offered
  by public synonymns.
 
  If you look at Steve's test carefully, the improvement or over head
  in
  the public synonym to private synonym is around 10% of the CPU time
  and
  latch gets. How much performance improvement you can expect in
  practical systems with the additiona  10% of latch gets/CPU times
  comparing with the overall system performance. I would expect less
  than
  (LESS THAN) 1-2% in total response time.
 
  END NON TECHNICAL
 
  The actual over head is coming from the negative dependency tracking
  or
  high version count in the Library cache. But this will be an issue
  only
   when too many users are connected and accssing the database  with
  too
  many accounts (different parsing user_id). But this is rarely a
  situation in many of the packaged application as most of the
  application connects to the database using a single account (like
  'APPS' user in Oracle eBusiness suite) and no negative dependency or
  multi version of SQLs are  an issue here (at least in a single
  instance
  oracle). The over head can be little higher in RAC environment as
the
  Library Cache and Row cache is globally co-ordinated.
 
  However I have not really seen major problem (may be I have not
  observed them too keen) with the public synonyms as most of the
  packaged applications I have worked are using a single oracle
account
  to connect to the database. It is just my personal observation, and
  may
  be Jonathan can have a different opinion.
 
  Regards,
  KG
 
  PS : Aplogies if some one has already addressed this issue, I have
  been
  traveling and didn;t have enough free time to read all the posts.
  Seen
  lots of posts flooding on this topic and jumped in out of curiosity.
 
 
 
 
 
 
  --- Yong Huang [EMAIL PROTECTED] wrote:
 To the OP: Other people point out common reasons for library
   cache latch
 contention. A less common reason is extensive use of public
   synonyms.
 If that's the reason, you also see row cache objects latch
   contention.
   
I'm not sure that's right.  If everyone uses a public synonym,
  then
you get one sql text, and one cursor.  I think the contention
   appears
because everyone has to have a 'non-existent' reference in
memory
to say that they don't own an object with the same name as the
   public
synonym - consequently if you have lots of users who have to
  check
long chains of  'non-existent' then the latches get held for
  longer
periods of time.
  
   Hi Jonathan,
  
   I don't see how your statement contradicts the claim that heavy
use
   of public

RE: Suggestions Needed: Latch free - library cache

Niall,

I think you're right... and as my databases, even those with multiple
applications in them, rarely have more than 20 named users, that's
likely the reason I see no degradation, even on custom apps.

Nice to know it's not just me :)

Rachel

--- Niall Litchfield [EMAIL PROTECTED] wrote:
 Hi Rachel
 
 My understanding of the negative aspects of public synonyms is that
 they
 kick in as the number of distinct Oracle Users you have increases. My
 *experience* is much the same as yours - namely I almost couldn't
 care,
 however this is predicated on applications tracking users and not
 using a
 different Oracle account for each user. (typically we have 1-2
 accounts per
 app). The archetype of application development on Oracle says that
 you have
 a different oracle account for each user (albeit probably OS
 authenticated).
 Mind you that same archetype says you can change the sql. I think
 that the
 drawbacks that steve has in mind don't apply to the majority of 3rd
 party
 apps.
 
 Niall 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Niall Litchfield
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Suggestions Needed: Latch free - library cache

oops. my bad. I really should double-check in the docs before I
pronounce things :)

you are, of course, right. it merely removes the necessity to preface
an object reference with the owner.


--- Bobak, Mark [EMAIL PROTECTED] wrote:
 Um, roles, privileges, etc are administered however you'd like.  
 
 The only suggestion I'm making is that rather than having public
 synonyms for all objects in your app_owner schema, each user which
 needs
 default access to the objects in the app_owner schema, gets access to
 that schema via the logon trigger that sets current_schema.  This
 does
 (should) not have any effect on how you manage roles and permissions,
 just how Oracle does default object resolution.
 
 -Mark
 
 Mark J. Bobak
 Oracle DBA
 ProQuest Company
 Ann Arbor, MI
 Imagination was given to man to compensate him for what he is not,
 and
 a sense of humor was provided to console him for what he is. 
 --Unknown
 
 
 -Original Message-
 Sent: Friday, January 09, 2004 3:35 PM
 To: Multiple recipients of list ORACLE-L
 
 
 How would you handle the case where there are many sets of
 privileges,
 depending on which user you log in as?
 
 The trigger would give everyone the right to do anything the owner
 could to a table. There are times when I want create a read-only
 account in addition to an app user.
 
 we do allow sqlplus access to production by developers -- in a
 read-only state so they can investigate end user complaints.
 
 
 --- Bobak, Mark [EMAIL PROTECTED] wrote:
  The other option is a user logon trigger that does execute
 immediate
  'alter session set current_schema=appowner';
  
  
  Mark J. Bobak
  Oracle DBA
  ProQuest Company
  Ann Arbor, MI
  Imagination was given to man to compensate him for what he is not,
  and
  a sense of humor was provided to console him for what he is. 
  --Unknown
  
  
  -Original Message-
  Sent: Friday, January 09, 2004 2:44 PM
  To: Multiple recipients of list ORACLE-L
  
  
  just to weigh in on the side of use of public synonyms.
  
  If you intend to use roles to simplify privilege management, you
 are
  almost forced to use public synonyms, as you cannot create a
 private
  synonym owned by a role. Your other alternative is to hard-code the
  schema owner name in every object access, which somewhat
 invalidates
  the idea of portability and security between dev/test/production
  environments, as you would have to maintain the same schema owner
  name
  in all environments.
  
  As a general practice, I create a schema owner, a schema user
 and
  a
  schema proc_owner  account. I use public synonyms throughout and
  have
  never seen a performance hit because of them.
  
  My (practical) $0.02 
  
  Rachel
  
  --- K Gopalakrishnan [EMAIL PROTECTED] wrote:
   Yong:
   
   I have not followed the thread completely. So I may be missing
   something obvious ;)
   
   BEGIN-NON TECHNICAL
   
   Many applications (for example Oracle Applications) use public
   synonyms
   heavily and running with better (or acceptable) performance.  We
   should
   not really worry about the milli second performance improvements
   comparing with the coding/application development flexibilities
   offered
   by public synonymns. 
   
   If you look at Steve's test carefully, the improvement or over
 head
   in
   the public synonym to private synonym is around 10% of the CPU
 time
   and
   latch gets. How much performance improvement you can expect in
   practical systems with the additiona  10% of latch gets/CPU times
   comparing with the overall system performance. I would expect
 less
   than
   (LESS THAN) 1-2% in total response time.
   
   END NON TECHNICAL
   
   The actual over head is coming from the negative dependency
  tracking
   or
   high version count in the Library cache. But this will be an
 issue
   only
when too many users are connected and accssing the database 
 with
   too
   many accounts (different parsing user_id). But this is rarely a
   situation in many of the packaged application as most of the
   application connects to the database using a single account (like
   'APPS' user in Oracle eBusiness suite) and no negative dependency
  or
   multi version of SQLs are  an issue here (at least in a single
   instance
   oracle). The over head can be little higher in RAC environment as
  the
   Library Cache and Row cache is globally co-ordinated. 
   
   However I have not really seen major problem (may be I have not
   observed them too keen) with the public synonyms as most of the
   packaged applications I have worked are using a single oracle
  account
   to connect to the database. It is just my personal observation,
 and
   may
   be Jonathan can have a different opinion.
   
   Regards,
   KG
   
   PS : Aplogies if some one has already addressed this issue, I
 have
   been
   traveling and didn;t have enough free time to read all the posts.
   Seen
   lots of posts flooding on this topic and jumped in out of
  curiosity.

RE: Suggestions Needed: Latch free - library cache

2004-01-09 Thread Steve Adams

Hi All,

Someone has alerted me to this thread, and asked for a comment.
On a quick scan, and it seems to me that you've mostly got it right.

The problem is that when an SQL statement that refers to its base
objects via public synonyms is shared by multiple distinct Oracle users,
then name resolution and permission checking need to repeated for each
distinct user, and because the results of these actions are cached on
the shared cursor, they increase the cost of subsequent such operations.
That is, public synonyms cause extended latch retention as well as
additional latching.

For example, if 500 distinct users share 200 SQL statements that refer
300 times to 100 base tables via public synonyms. Then there will also
be 100 * 500 non-existent objects in both the dictionary cache and the
library cache; 200 * 500 cursor authorization structures; and 300 * 500
negative dependency records in the library cache. These last two things
are cached as segmented arrays that are scanned linearly - thus the
increased latch retention.

If your application doesn't have hundreds of distinct Oracle users,
or if you can afford the extra latch gets and longer latch retention,
then you will probably not notice all of this unless you start doing
library cache dumps.

That is, the use of public synonyms is a major scalability threat, but
does not normally cause performance problems.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/ - For DBAs
@   http://www.christianity.net.au/  - For all 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve Adams
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache


There's a failure in the statistics at this point.
As far as I can tell, there are at least four different
reasons why the stats can report a
parse count (total)
without recording a
parse count (hard)
and it would be nice if we could see them as four
different statistics.

Code that explicit holds a cursor open need
not issue a parse call at all.

Code that issues a parse call may:
Invoke the whole parse/optimize cycle
Invoke a permissions cycle on an existing statement
Invoke a search and execute cycle on an existing statement with valid
permission
Invoke a 'this is where it is and I know I've got permission, so just do
it' cycle

The last option can appear when you set up
session_cached_cursors != 0.

The front-end code is still issuing an explicit parse call,
which is why you see the parse count go up, but the
work done is kept to near minimum.


NOTE: This description is probably not complete
and I'd welcome any corrections and refinements
that anyone can supply.



Regards

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


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


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, January 08, 2004 1:34 AM


 if your caching the cursors, why does soft parsing still happen?
 - Original Message - 
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Wednesday, January 07, 2004 7:24 PM


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Suggestions Needed: Latch free - library cache

Jonathan,

At least the last case you mention is accounted for by the 'session
cache cursor hits' statistic.  And, though it's not entirely clear to
me, based on the description in the Reference manual, it seems to me
that the 'cursor authentications' statistic may reflect the second case
in your list?

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is.  --Unknown


-Original Message-
Sent: Thursday, January 08, 2004 4:54 AM
To: Multiple recipients of list ORACLE-L



There's a failure in the statistics at this point.
As far as I can tell, there are at least four different
reasons why the stats can report a
parse count (total)
without recording a
parse count (hard)
and it would be nice if we could see them as four
different statistics.

Code that explicit holds a cursor open need
not issue a parse call at all.

Code that issues a parse call may:
Invoke the whole parse/optimize cycle
Invoke a permissions cycle on an existing statement
Invoke a search and execute cycle on an existing statement with
valid
permission
Invoke a 'this is where it is and I know I've got permission, so
just do
it' cycle

The last option can appear when you set up
session_cached_cursors != 0.

The front-end code is still issuing an explicit parse call,
which is why you see the parse count go up, but the
work done is kept to near minimum.


NOTE: This description is probably not complete
and I'd welcome any corrections and refinements
that anyone can supply.



Regards

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


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


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, January 08, 2004 1:34 AM


 if your caching the cursors, why does soft parsing still happen?
 - Original Message - 
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Wednesday, January 07, 2004 7:24 PM


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bobak, Mark
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Suggestions Needed: Latch free - library cache

Ok, I did a little experiment.  Here are my results:

In session A, I do:
I did 'select sid from v$mystat where rownum=1;'
I did 'alter session set session_cached_cursors=100;'
I did 'alter system flush shared_pool;'

In session B, I ran the following:
select my.statistic#, sn.name, my.value from v$sesstat my, v$statname sn
 where sn.statistic#=my.statistic#
   and sn.statistic# in(179,180,191,193)
   and my.sid=62;

Which yielded the baseline stats:
STATISTIC# NAMEVALUE
-- -- --
   179 parse count (total)60
   180 parse count (hard)  9
   191 session cursor cache hits   6
   193 cursor authentications  6

Now, session A:
Select /* this is my unique sql */ * from dual;

Session B shows:
STATISTIC# NAMEVALUE
-- -- --
   179 parse count (total)62
   180 parse count (hard) 10
   191 session cursor cache hits   6
   193 cursor authentications  6

Two more total parses, one hard.  (The extra soft parse due to recursive
sql?)

Now, session A:
/
(Re-execute query)

Session B:
STATISTIC# NAMEVALUE
-- -- --
   179 parse count (total)63
   180 parse count (hard) 10
   191 session cursor cache hits   6
   193 cursor authentications  7

Hmm...no hard parse, soft parse and 'cursor authentication'.

Session A:
/
(execute a third time)

Session B:
STATISTIC# NAMEVALUE
-- -- --
   179 parse count (total)64
   180 parse count (hard) 10
   191 session cursor cache hits   6
   193 cursor authentications  7

Hmm...soft parse, NO cursor authentication.  This is just the third
exec, so no session cursor cache hit, but we should be in the session
cursor cache now.

Session A:
/
(Fourth execution)

Session B:
STATISTIC# NAMEVALUE
-- -- --
   179 parse count (total)65
   180 parse count (hard) 10
   191 session cursor cache hits   7
   193 cursor authentications  7

There's our session cursor cache hit!

So, it seems that this is happening:

1.)  Hard parse.
2.)  Soft parse w/ 'cursor authentication'.
3.)  Soft parse w/o 'cursor authentication'.
4.)  Soft parse w/ session cursor cache hit.
All subsequent executions are same as #4.

Two more quick tests:
I tried connecting another session, as the same user, and there was no
'cursor authentication'.  So, I tried ano connection as a *different*
user, and, voila!, cursor authentication was done on the first
execution!

So, I'll think about this a bit more, but I think one can distinguish
between all the cases you mentioned.  What do you think, Jonathan?

-Mark


Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is.  --Unknown


-Original Message-
Sent: Thursday, January 08, 2004 10:10 AM
To: Multiple recipients of list ORACLE-L


Jonathan,

At least the last case you mention is accounted for by the 'session
cache cursor hits' statistic.  And, though it's not entirely clear to
me, based on the description in the Reference manual, it seems to me
that the 'cursor authentications' statistic may reflect the second case
in your list?

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is.  --Unknown


-Original Message-
Sent: Thursday, January 08, 2004 4:54 AM
To: Multiple recipients of list ORACLE-L



There's a failure in the statistics at this point.
As far as I can tell, there are at least four different
reasons why the stats can report a
parse count (total)
without recording a
parse count (hard)
and it would be nice if

Re: Suggestions Needed: Latch free - library cache


Excellent !!

I've been demonstrating in the past using v$latch
that the latch costs of parsing are different on 
the first, second, and third parse - and I've assumed
that that's why the cursor goes into the cache on
the third parse.  I've never thought that the
'cursor authentication' statistic might be relevant.

If you go to the other session 
a)  Where the user is the same - do you see a 
session cache cursor hit on the second execution,
or does it still not appear until the fourth
Rationale - maybe the cursor is put into the cache
on the first hit after full authentication.

b)Where the user is different - do you see a
session cache cursor hit on the THIRD execution,
or does it still not appear until the fourth.
Rationale - the first execution generates the in-memory
permissions; the second execution finds the cursor
authenticated, therefore causes a cache load.

The manual says the cursor is cached on the third execution -
but maybe that's the obvious result from the simplest test.


Regards

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

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


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


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, January 08, 2004 4:34 PM


 Ok, I did a little experiment.  Here are my results:
 
 In session A, I do:
 I did 'select sid from v$mystat where rownum=1;'
 I did 'alter session set session_cached_cursors=100;'
 I did 'alter system flush shared_pool;'
 
 In session B, I ran the following:
 select my.statistic#, sn.name, my.value from v$sesstat my, v$statname sn
  where sn.statistic#=my.statistic#
and sn.statistic# in(179,180,191,193)
and my.sid=62;
 
 Which yielded the baseline stats:
 STATISTIC# NAMEVALUE
 -- -- --
179 parse count (total)60
180 parse count (hard)  9
191 session cursor cache hits   6
193 cursor authentications  6
 
 Now, session A:
 Select /* this is my unique sql */ * from dual;
 
 Session B shows:
 STATISTIC# NAMEVALUE
 -- -- --
179 parse count (total)62
180 parse count (hard) 10
191 session cursor cache hits   6
193 cursor authentications  6
 
 Two more total parses, one hard.  (The extra soft parse due to recursive
 sql?)
 
 Now, session A:
 /
 (Re-execute query)
 
 Session B:
 STATISTIC# NAMEVALUE
 -- -- --
179 parse count (total)63
180 parse count (hard) 10
191 session cursor cache hits   6
193 cursor authentications  7
 
 Hmm...no hard parse, soft parse and 'cursor authentication'.
 
 Session A:
 /
 (execute a third time)
 
 Session B:
 STATISTIC# NAMEVALUE
 -- -- --
179 parse count (total)64
180 parse count (hard) 10
191 session cursor cache hits   6
193 cursor authentications  7
 
 Hmm...soft parse, NO cursor authentication.  This is just the third
 exec, so no session cursor cache hit, but we should be in the session
 cursor cache now.
 
 Session A:
 /
 (Fourth execution)
 
 Session B:
 STATISTIC# NAMEVALUE
 -- -- --
179 parse count (total)65
180 parse count (hard) 10
191 session cursor cache hits   7
193 cursor authentications

RE: Suggestions Needed: Latch free - library cache

Jonathan,

Second session, same user:  first is soft w/o authentication.  Second is
session cursor cache hit.
Second session, different user: first is soft w/ authentication.  Second
is session cursor cache hit.

So, once everything is cached, the same user case is as expected, and
the different user case does even better than you predicted.

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is.  --Unknown


-Original Message-
Sent: Thursday, January 08, 2004 11:50 AM
To: Multiple recipients of list ORACLE-L



Excellent !!

I've been demonstrating in the past using v$latch
that the latch costs of parsing are different on 
the first, second, and third parse - and I've assumed
that that's why the cursor goes into the cache on
the third parse.  I've never thought that the
'cursor authentication' statistic might be relevant.

If you go to the other session 
a)  Where the user is the same - do you see a 
session cache cursor hit on the second execution,
or does it still not appear until the fourth
Rationale - maybe the cursor is put into the cache
on the first hit after full authentication.

b)Where the user is different - do you see a
session cache cursor hit on the THIRD execution,
or does it still not appear until the fourth.
Rationale - the first execution generates the in-memory
permissions; the second execution finds the cursor
authenticated, therefore causes a cache load.

The manual says the cursor is cached on the third execution -
but maybe that's the obvious result from the simplest test.


Regards

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

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


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


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, January 08, 2004 4:34 PM


 Ok, I did a little experiment.  Here are my results:
 
 In session A, I do:
 I did 'select sid from v$mystat where rownum=1;'
 I did 'alter session set session_cached_cursors=100;'
 I did 'alter system flush shared_pool;'
 
 In session B, I ran the following:
 select my.statistic#, sn.name, my.value from v$sesstat my, v$statname
sn
  where sn.statistic#=my.statistic#
and sn.statistic# in(179,180,191,193)
and my.sid=62;
 
 Which yielded the baseline stats:
 STATISTIC# NAME
VALUE
 -- --
--
179 parse count (total)
60
180 parse count (hard)
9
191 session cursor cache hits
6
193 cursor authentications
6
 
 Now, session A:
 Select /* this is my unique sql */ * from dual;
 
 Session B shows:
 STATISTIC# NAME
VALUE
 -- --
--
179 parse count (total)
62
180 parse count (hard)
10
191 session cursor cache hits
6
193 cursor authentications
6
 
 Two more total parses, one hard.  (The extra soft parse due to
recursive
 sql?)
 
 Now, session A:
 /
 (Re-execute query)
 
 Session B:
 STATISTIC# NAME
VALUE
 -- --
--
179 parse count (total)
63
180 parse count (hard)
10
191 session cursor cache hits
6
193 cursor authentications
7
 
 Hmm...no hard parse, soft parse and 'cursor authentication'.
 
 Session A:
 /
 (execute a third time)
 
 Session B:
 STATISTIC# NAME
VALUE
 -- --
--
179 parse count (total)
64
180 parse count (hard)
10
191 session cursor cache hits
6
193 cursor authentications
7
 
 Hmm...soft parse, NO cursor authentication.  This is just the third
 exec, so no session cursor cache hit, but we should be in the session
 cursor cache now.
 
 Session A:
 /
 (Fourth execution)
 
 Session B:
 STATISTIC# NAME
VALUE
 -- --
--
179 parse count (total)
65
180 parse count (hard)
10
191 session cursor cache hits
7
193 cursor authentications
7
 
 There's our session cursor cache hit!
 
 So, it seems that this is happening:
 
 1.)  Hard parse.
 2.)  Soft parse w/ 'cursor authentication'.
 3.)  Soft parse w/o 'cursor authentication'.
 4.)  Soft parse w/ session cursor cache hit.
 All subsequent executions are same as #4.
 
 Two more quick tests:
 I tried

Re: Suggestions Needed: Latch free - library cache


Thanks,

Regards

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

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


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


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, January 08, 2004 5:19 PM


 Jonathan,
 
 Second session, same user:  first is soft w/o authentication.  Second is
 session cursor cache hit.
 Second session, different user: first is soft w/ authentication.  Second
 is session cursor cache hit.
 
 So, once everything is cached, the same user case is as expected, and
 the different user case does even better than you predicted.
 
 -Mark
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache

2004-01-08 Thread Yong Huang

It would be good if Oracle could break SQL parse down into not just hard and
soft, not just hard-soft-softer (Tom Kyte's wording), but different levels.
Oracle may have to work slightly harder to update these new statistics but the
benefit for OLTP databases is huge.

Other than the four parse invocations in your message, I think we can add one
between your first and second: Invoke a parse to create a new version of the
same cursor (same in the sense of same address and hash) due to either bind
threshold change or execution plan change. In fact, these two types of changes
may be broken down to two statistics. Looking at the columns in
v$sql_shared_cursor, I'm afraid we may need much more statistics?

To the OP: Other people point out common reasons for library cache latch
contention. A less common reason is extensive use of public synonyms. If that's
the reason, you also see row cache objects latch contention.

Yong Huang

Jonathan Lewis wrote:
...
Code that issues a parse call may:
Invoke the whole parse/optimize cycle
Invoke a permissions cycle on an existing statement
Invoke a search and execute cycle on an existing statement with valid
permission
Invoke a 'this is where it is and I know I've got permission, so just do
it' cycle
...
NOTE: This description is probably not complete
and I'd welcome any corrections and refinements
that anyone can supply.

__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Suggestions Needed: Latch free - library cache

Yong,

In case you missed it, see my previous reply to Jonathan's mail.  I'll
expand my test case and see what I can come up with for the other cases
you motion.

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is.  --Unknown


-Original Message-
Sent: Thursday, January 08, 2004 1:09 PM
To: Multiple recipients of list ORACLE-L


It would be good if Oracle could break SQL parse down into not just hard
and
soft, not just hard-soft-softer (Tom Kyte's wording), but different
levels.
Oracle may have to work slightly harder to update these new statistics
but the
benefit for OLTP databases is huge.

Other than the four parse invocations in your message, I think we can
add one
between your first and second: Invoke a parse to create a new version of
the
same cursor (same in the sense of same address and hash) due to either
bind
threshold change or execution plan change. In fact, these two types of
changes
may be broken down to two statistics. Looking at the columns in
v$sql_shared_cursor, I'm afraid we may need much more statistics?

To the OP: Other people point out common reasons for library cache latch
contention. A less common reason is extensive use of public synonyms. If
that's
the reason, you also see row cache objects latch contention.

Yong Huang

Jonathan Lewis wrote:
...
Code that issues a parse call may:
Invoke the whole parse/optimize cycle
Invoke a permissions cycle on an existing statement
Invoke a search and execute cycle on an existing statement with
valid
permission
Invoke a 'this is where it is and I know I've got permission, so
just do
it' cycle
...
NOTE: This description is probably not complete
and I'd welcome any corrections and refinements
that anyone can supply.

__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bobak, Mark
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Suggestions Needed: Latch free - library cache