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,
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
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
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 -
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
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?
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
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
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
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
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
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
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
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
: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
: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 pers
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
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?
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
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
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
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
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
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
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]
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
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
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
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.
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
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
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
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
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
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
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
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
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
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 -
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
Title: Message
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,
PM
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
Title: Message
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
MessageHi!
how do you limit excessive soft parsing?
You don't close reopen cursors in a loop.
Also, when you set session_cached_cursors parameter, soft parses do still
happen for reusable cursors but parsing is much cheaper both in CPU usage
and latching.
Tanel.
--
Please see the official
04
7:15 PMTo: Multiple recipients of list ORACLE-LSubject:
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,
Title: Message
Tracy,
This
is a very cursory answer...If this is the 'library cache' latch, then
there *should* be a number of entries in V$LATCH_CHILDREN.Are the figures
therein skewed in some wayamong the child latches? If so, you *may* have
an issue where a particular application or SQL
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
MessageHi!
how do you limit excessive soft parsing?
You don't close reopen cursors in a
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
48 matches
Mail list logo
