RE: sql trace - forward attribution
Thanks, Raj, but I don't think so. The whole reason behind enabling a db-wide trace was to capture everything sql trace is instrumented to capture. As you know sql_trace is a static parameter, so I got everything "from the begining", including: PARSING IN CURSOR #1 ALTER DATABASE MOUNT .. PARSING IN CURSOR #1 ALTER DATABASE OPEN PARSING IN CURSOR #2 create table bootstrap$ ( line# PARSING IN CURSOR #2 CREATE ROLLBACK SEGMENT SYSTEM .. but not the cursor #0 (Interestingly "alter database mount" went to one trace file, while "alter database open" and the rest to another - so to mount a DB Oracle spawns a different process on your behalf and than passes control back to your foreground to open it?) --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > you may not be seeing parse etc entries for cursor > #0 merely because maybe by design, cursor#0 gets > invoked before trace gets activated. This way, you > will never get cursor #0 info. > > You can tell, I am guessing but to get similar > experience, start trace in an already active session > and you'll see. > Raj > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly > personal. > QOTD: Any clod can have facts, having an opinion is > an art ! > > > -Original Message- > Sent: Tuesday, January 06, 2004 12:00 PM > To: Multiple recipients of list ORACLE-L > > > Thanks to Anjo, Cary, Tanel, and everybody who > provided feedback back channel. > > Just to rule out the possibility of a collection > error > (somebody suggested that cursor #0 is simply not > captured) I bounced the DB today, enabled a DB-wide > trace ... and as expected > > grep -i "cursor #0" * > > returned nothing, while "wait #0" gives plenty. So > it > is not a trace activation/termination error. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jamadagni, Rajendra > 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). __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
Thanks, Tanel. I did checked v$session_connect info, but it doesn't tell me much, except authentication_type='PROXY' which is less than useful as it doesn't tell to whom. v$session.client_identifier is empty. Thanks, Boris Dali. > Check V$SESSION_CONNECT_INFO view. > CLIENT_IDENTIFIER in V$SESSION might show something > as well, if mid-tier is > configured to pass client id to server. > > Tanel. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
Thanks to Anjo, Cary, Tanel, and everybody who
provided feedback back channel.
Just to rule out the possibility of a collection error
(somebody suggested that cursor #0 is simply not
captured) I bounced the DB today, enabled a DB-wide
trace ... and as expected
grep -i "cursor #0" *
returned nothing, while "wait #0" gives plenty. So it
is not a trace activation/termination error.
---
I think what we deal with here is a "variant" of what
Anjo described, but not exactly that as I don't see
*** SESSION ID:(sid.serial#) lines in the middle of
any trace file, only in the header, but I think it
still might be "session switching" of a kind.
What we use here is an n-tier proxy authentication and
I suspect these waits is the price we pay for it. Not
sure, but maybe if proxy attributes are "switched" sql
trace doesn't capture this properly, "forgeting" to
emit new session info? I would be interested to know
how to
1) confirm or refute this
2) since waits #0 appear only before the calls to a
stored code - I don't know if they deliberatly "switch
sessions" in the code that runs on the app server and
run the stored code as the schema owner (similar to
switching current schema as an alternative to using
synonyms) or it is a feature of Oracle's proxy
authentication implementation
3) how to check "proxy identity" of the user - i.e.
how to run something like sys_context('userenv',
'proxy_user') for sessions other than my own.
Thanks,
Boris Dali.
--- Anjo Kolk <[EMAIL PROTECTED]> wrote: > They write
all to the same trace file. So there
> should be different
> sid.serial# combinations.
______
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
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: sql trace - forward attribution
Tanel, What I see in the trace file header is something like the following: ... *** SESSION ID:(22.9304) 2003-12-29 15:04:45.743 ... Which is sid.serial# isn't it? If "session switching" occurs, handled by the same shadow process and the new session with a different sid.serial# continues to write to the **same** trace file... wouldn't you expect to see line similar to the above, but with a new sid.serial# in it? --- Tanel Poder <[EMAIL PROTECTED]> wrote: > Trace file has server process number in it's name, > not session number, thus > as long as the sessions are served by the same > server process, the contents > will be written into one single file. > > Tanel. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Monday, January 05, 2004 10:49 PM > > > > Right, but the new session (that inherits the sql > > trace attribute) - wouldn't it produce a > **separate** > > trace file? In my case there's only one trace file > > with sid.serial# clearly stated at the begining of > the > > trace file and WAIT #0 scattered all over the t > race === message truncated === ______ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
Right, but the new session (that inherits the sql trace attribute) - wouldn't it produce a **separate** trace file? In my case there's only one trace file with sid.serial# clearly stated at the begining of the trace file and WAIT #0 scattered all over the trace. ..Or am I missing something? --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > No, > > Each session will have its own sid and serail#, but > they all run in the > same process. Basically the client side tells > oracle, that it wants to > switch from session to session and oracle will keep > the state of the > switched out session. So you don't have to commit or > rollback on every > switch that you perform. SQL trace is inherited by > the process it you > set in a session, so other sessions that run in the > same process will > produce also trace output. > > Anjo. > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 7:34 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Anjo. > > When session switching occurs does the new session > get > the same sid and serial#? And what happens with the > session being "switched/replaced" - does the > transaction it was performing get > commited/rollbacked? > I don't see XCTEND markers before those pesky WAIT > #0 > in the trace file. > Also if session gets switched, wouldn't this > terminate > sql trace for the session (in my case it doesn't)? > > Thanks, > Boris Dali. > > --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > > > > Cursor 0 also happens in oracle due to session > > switching (multiple > > sessions in the same process), oracle apps uses > that > > but it also could > > happen with certain other application servers > > (haven't investigated it). > > > > Anjo. > > > > > > -Original Message- > > Boris Dali > > Sent: Monday, January 05, 2004 3:59 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Thanks, Cary. > > > > Could you elaborate what do you mean by "wait > events associated with > > COMMIT processing"? Why does Oracle need this > "exchange of messages" > > with the client (well, with the app server really > in my case of a > > 3-tier deployment) to perform a commit? > > > > > > In any event, as I described earlier in my case I > > think Cursor #0 doesn't fall in neither of the two > > uses you mentioned. > > > > Bug 2425312 is RPC related as I understand. I > don't > > work distributed (single DB) and app server (and > > clients - thin) don't have their own SQL engine, > so > > all SQL processing is happening strictly on the DB > > server. So this doesn't seem to apply to me. > > > > And I see Cursor #0 used with no commits/rollbacks > > as > > part of one Oracle transaction. > > > > > > I see these WAIT #0 flying back and forth between > DB > > and the app server sometimes 20 times just before > > stored procs are called and I can't figure out > why. > > Another bug? > > > > Thank you, > > Boris Dali. > > > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > Boris, > > > > > > Cursor #0 seems reserved for two special uses: > (1) > > > wait events > > > associated with COMMIT processing (also, of > > course, > > > ROLLBACK and > > > SAVEPOINT), and (2) wait events associated with > > > dbcalls not instrumented > > > because of bug 2425312. > > > > > > > > > Cary Millsap > > > Hotsos Enterprises, Ltd. > > > http://www.hotsos.com > > > > > > Upcoming events: > > > - Performance Diagnosis 101: 1/27 Atlanta > > > - SQL Optimization 101: 2/16 Dallas > > > - Hotsos Symposium 2004: March 7-10 Dallas > > > - Visit www.hotsos.com for schedule details... > > > > > > > > > -Original Message- > > > Boris Dali > > > Sent: Thursday, January 01, 2004 10:29 AM > > > To: Multiple recipients of list ORACLE-L > > > > > > Thanks a lot for your reply, Cary. > > > > > > One follow-up question. What would motivate "a > > chat" > > > of sometimes 5, sometimes 10-20 'SQL*Net message > > > to/from client' consecutive wait lines emitted > to > > > the > > > trace file in the following manner: > > > > > > WAIT #0: nam=
RE: sql trace - forward attribution
Anjo, I suppose your test-case involved more than just use of sqlplus. Probably some middle tier with connection/session pooling of some sort? --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > I actually build a testcase for this and it still > failed on 9.2 without > any patches. It is supposed to be fixed in some > later patch. I don't > have the patches > > -Original Message- > Anjo Kolk > Sent: Monday, January 05, 2004 6:49 PM > To: Multiple recipients of list ORACLE-L > > > > > Cursor 0 also happens in oracle due to session > switching (multiple > sessions in the same process), oracle apps uses that > but it also could > happen with certain other application servers > (haven't investigated it). > > Anjo. > > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 3:59 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Cary. > > Could you elaborate what do you mean by "wait events > associated with COMMIT processing"? Why does Oracle > need this "exchange of messages" with the client > (well, with the app server really in my case of a > 3-tier deployment) to perform a commit? > > > In any event, as I described earlier in my case I > think Cursor #0 doesn't fall in neither of the two > uses you mentioned. > > Bug 2425312 is RPC related as I understand. I don't > work distributed (single DB) and app server (and > clients - thin) don't have their own SQL engine, so > all SQL processing is happening strictly on the DB > server. So this doesn't seem to apply to me. > > And I see Cursor #0 used with no commits/rollbacks > as > part of one Oracle transaction. > > > I see these WAIT #0 flying back and forth between DB > and the app server sometimes 20 times just before > stored procs are called and I can't figure out why. > Another bug? > > Thank you, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > Boris, > > > > Cursor #0 seems reserved for two special uses: (1) > > wait events > > associated with COMMIT processing (also, of > course, > > ROLLBACK and > > SAVEPOINT), and (2) wait events associated with > > dbcalls not instrumented > > because of bug 2425312. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -Original Message- > > Boris Dali > > Sent: Thursday, January 01, 2004 10:29 AM > > To: Multiple recipients of list ORACLE-L > > > > Thanks a lot for your reply, Cary. > > > > One follow-up question. What would motivate "a > chat" > > of sometimes 5, sometimes 10-20 'SQL*Net message > > to/from client' consecutive wait lines emitted to > > the > > trace file in the following manner: > > > > WAIT #0: nam='SQL*Net message to client' ela= 2 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 678 p1=1413697536 p2=1 > > > p3=0 WAIT #0: nam='SQL*Net message to client' ela= > 1 p1=1413697536 > > p2=1 p3=0 WAIT #0: nam='SQL*Net message from > client' ela= 3463 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 3322 > > p1=1413697536 p2=1 p3=0 > > > > > > I see this pattern of "message exchanges" before > > calling a stored code from the app server (OCI), > so > > using forward attribution it is a call to a stored > > code that it to blame correct? > > I can't of course eliminate a call to a stored > code > > but is there something that can be done to > minimize > > amount of these 'SQL*Net message...' lines? While > > the > > latency of these waits is low, these 3-5 > > milliseconds > > get accumulated slowly, but surely. > > > > Also does cursor #0 has some special meaning in > > traces? I can't seem to create a test-case where I > > get > > cursor #0 emitted for me and yet tracing real > > applications I see it all over (like in the > excerpt > > above) > > > > > > I guess I have more than one follow-up question > :-( > > > &
RE: sql trace - forward attribution
Thanks, Anjo. When session switching occurs does the new session get the same sid and serial#? And what happens with the session being "switched/replaced" - does the transaction it was performing get commited/rollbacked? I don't see XCTEND markers before those pesky WAIT #0 in the trace file. Also if session gets switched, wouldn't this terminate sql trace for the session (in my case it doesn't)? Thanks, Boris Dali. --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > > Cursor 0 also happens in oracle due to session > switching (multiple > sessions in the same process), oracle apps uses that > but it also could > happen with certain other application servers > (haven't investigated it). > > Anjo. > > > -Original Message- > Boris Dali > Sent: Monday, January 05, 2004 3:59 PM > To: Multiple recipients of list ORACLE-L > > > Thanks, Cary. > > Could you elaborate what do you mean by "wait events > associated with COMMIT processing"? Why does Oracle > need this "exchange of messages" with the client > (well, with the app server really in my case of a > 3-tier deployment) to perform a commit? > > > In any event, as I described earlier in my case I > think Cursor #0 doesn't fall in neither of the two > uses you mentioned. > > Bug 2425312 is RPC related as I understand. I don't > work distributed (single DB) and app server (and > clients - thin) don't have their own SQL engine, so > all SQL processing is happening strictly on the DB > server. So this doesn't seem to apply to me. > > And I see Cursor #0 used with no commits/rollbacks > as > part of one Oracle transaction. > > > I see these WAIT #0 flying back and forth between DB > and the app server sometimes 20 times just before > stored procs are called and I can't figure out why. > Another bug? > > Thank you, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > Boris, > > > > Cursor #0 seems reserved for two special uses: (1) > > wait events > > associated with COMMIT processing (also, of > course, > > ROLLBACK and > > SAVEPOINT), and (2) wait events associated with > > dbcalls not instrumented > > because of bug 2425312. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization 101: 2/16 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -Original Message- > > Boris Dali > > Sent: Thursday, January 01, 2004 10:29 AM > > To: Multiple recipients of list ORACLE-L > > > > Thanks a lot for your reply, Cary. > > > > One follow-up question. What would motivate "a > chat" > > of sometimes 5, sometimes 10-20 'SQL*Net message > > to/from client' consecutive wait lines emitted to > > the > > trace file in the following manner: > > > > WAIT #0: nam='SQL*Net message to client' ela= 2 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 678 p1=1413697536 p2=1 > > > p3=0 WAIT #0: nam='SQL*Net message to client' ela= > 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 3463 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > WAIT #0: nam='SQL*Net message from client' ela= > 3322 > > p1=1413697536 p2=1 p3=0 > > > > > > I see this pattern of "message exchanges" before > > calling a stored code from the app server (OCI), > so > > using forward attribution it is a call to a stored > > code that it to blame correct? > > I can't of course eliminate a call to a stored > code > > but is there something that can be done to > minimize > > amount of these 'SQL*Net message...' lines? While > > the > > latency of these waits is low, these 3-5 > > milliseconds > > get accumulated slowly, but surely. > > > > Also does cursor #0 has some special meaning in > > traces? I can't seem to create a test-case where I > > get > > cursor #0 emitted for me and yet tracing real > > applications I see it all over (like in the > excerpt > > above) > > > > > > I guess I have more than one follow-up question > :-( > > > > Thanks, > > Boris Dali
Re: rewrite group by query
How about select * from (select tab1.*, count(a) over(partition by a) a_count from tab1) where a_count =1; ... would probably save you one pass over tab1. Thanks, Boris Dali. --- elain he <[EMAIL PROTECTED]> wrote: > Hi, > Does anyone have a better way of rewriting the > following query? I'm trying > to avoid querying the table, tab1 twice. > > select a, b from tab1 > where a in (select a from tab1 group by a having > count(*)=1); > > > Thanks. > > elain > > _ > Make your home warm and cozy this winter with tips > from MSN House & Home. > http://special.msn.com/home/warmhome.armx > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: elain he > 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). ______ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - forward attribution
Thanks, Cary. Could you elaborate what do you mean by "wait events associated with COMMIT processing"? Why does Oracle need this "exchange of messages" with the client (well, with the app server really in my case of a 3-tier deployment) to perform a commit? In any event, as I described earlier in my case I think Cursor #0 doesn't fall in neither of the two uses you mentioned. Bug 2425312 is RPC related as I understand. I don't work distributed (single DB) and app server (and clients - thin) don't have their own SQL engine, so all SQL processing is happening strictly on the DB server. So this doesn't seem to apply to me. And I see Cursor #0 used with no commits/rollbacks as part of one Oracle transaction. I see these WAIT #0 flying back and forth between DB and the app server sometimes 20 times just before stored procs are called and I can't figure out why. Another bug? Thank you, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > Boris, > > Cursor #0 seems reserved for two special uses: (1) > wait events > associated with COMMIT processing (also, of course, > ROLLBACK and > SAVEPOINT), and (2) wait events associated with > dbcalls not instrumented > because of bug 2425312. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Boris Dali > Sent: Thursday, January 01, 2004 10:29 AM > To: Multiple recipients of list ORACLE-L > > Thanks a lot for your reply, Cary. > > One follow-up question. What would motivate "a chat" > of sometimes 5, sometimes 10-20 'SQL*Net message > to/from client' consecutive wait lines emitted to > the > trace file in the following manner: > > WAIT #0: nam='SQL*Net message to client' ela= 2 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 678 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3463 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #0: nam='SQL*Net message from client' ela= 3322 > p1=1413697536 p2=1 p3=0 > > > I see this pattern of "message exchanges" before > calling a stored code from the app server (OCI), so > using forward attribution it is a call to a stored > code that it to blame correct? > I can't of course eliminate a call to a stored code > but is there something that can be done to minimize > amount of these 'SQL*Net message...' lines? While > the > latency of these waits is low, these 3-5 > milliseconds > get accumulated slowly, but surely. > > Also does cursor #0 has some special meaning in > traces? I can't seem to create a test-case where I > get > cursor #0 emitted for me and yet tracing real > applications I see it all over (like in the excerpt > above) > > > I guess I have more than one follow-up question :-( > > Thanks, > Boris Dali. > > --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 p3=0 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 692 p1=1413697536 p2=1 > > p3=0 > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > > p1=1413697536 p2=1 > > p3=0 >FETCH > > > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > > >WAIT #31: nam='SQL*Net message from client' ela= > > 2295 p1=1413697536 > > p2=1 p3=0 > > > > > > > Boris, "SQL*Net message..." events are > > "between-call" events. Their > > times are not included in the following dbcall's > > elapsed time. But it > > *is* appropriate to "blame" the dbcall that > follows > > for the time > > consumed by the event. That is, if you can > eliminate > > the dbcall that > > follows, then you can eliminate the between-call > > event (and its elapsed > > time). The "assignment of blame" is what "forward > > attribution" is about. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 1/27 Atlanta > > - SQL Optimization
RE: sql trace - forward attribution
Thanks a lot for your reply, Cary. One follow-up question. What would motivate "a chat" of sometimes 5, sometimes 10-20 'SQL*Net message to/from client' consecutive wait lines emitted to the trace file in the following manner: WAIT #0: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 678 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3463 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3322 p1=1413697536 p2=1 p3=0 I see this pattern of "message exchanges" before calling a stored code from the app server (OCI), so using forward attribution it is a call to a stored code that it to blame correct? I can't of course eliminate a call to a stored code but is there something that can be done to minimize amount of these 'SQL*Net message...' lines? While the latency of these waits is low, these 3-5 milliseconds get accumulated slowly, but surely. Also does cursor #0 has some special meaning in traces? I can't seem to create a test-case where I get cursor #0 emitted for me and yet tracing real applications I see it all over (like in the excerpt above) I guess I have more than one follow-up question :-( Thanks, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > > > >WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > >WAIT #31: nam='SQL*Net message from client' ela= > 692 p1=1413697536 p2=1 > p3=0 > >WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 > p3=0 >FETCH > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > >WAIT #31: nam='SQL*Net message from client' ela= > 2295 p1=1413697536 > p2=1 p3=0 > > > > Boris, "SQL*Net message..." events are > "between-call" events. Their > times are not included in the following dbcall's > elapsed time. But it > *is* appropriate to "blame" the dbcall that follows > for the time > consumed by the event. That is, if you can eliminate > the dbcall that > follows, then you can eliminate the between-call > event (and its elapsed > time). The "assignment of blame" is what "forward > attribution" is about. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Boris Dali > Sent: Monday, December 29, 2003 9:39 AM > To: Multiple recipients of list ORACLE-L > > I don't have the book with me right now, but I am > obviously missing something in the "forward > attribution" concept as it doesn't seem to help me > in > explanation of the following lines: > > > WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #31: nam='SQL*Net message from client' ela= 692 > p1=1413697536 p2=1 p3=0 > WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > FETCH > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > WAIT #31: nam='SQL*Net message from client' ela= > 2295 > p1=1413697536 p2=1 p3=0 > > > Shouldn't 1 + 692 + 1 (and possibly + 2295 ???) be > less than 261? > > Oracle 9.2.0.4.0 on HP-UX 11.11 > > Thanks, > Boris Dali. > > __ > > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Boris Dali > 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 > -- &
phys reads vs blocks read - statspack's segment stats
I am reviewing segment statistics taken in a 15 min snapshot (from stats$seg_stat) for a specific table, which resides in 2 datafiles. The stats for datafiles (stats$filestatxs) are as follows: file# blocks read physical_reads - --- -- 11 8,171960 12 6,149387 while the table stats are: liodb block changes phyrds -- --- 42,0640 10,821 One would expect stats$seg_stat.physical_reads for specific segment/snapshot to be <= stats$filestat.physrds for the datafiles, segment resides in, which doesn't seem to be the case. Number of reads from the table somewhat makes sense if compared against number of blocks read from the datafiles, but not against number of reads from these datafiles. I know v$filestat timing info is notoriously unreliable, but I thought i/o (number and blocks) stuff can be trusted to. Am I missing something obvious? Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: SQL CASE Statement
Version?
select case when substr(banner, instr(banner,
'Release')+length('Release')+1, 1) < 9 then
'tough luck. sql and pl/sql parsers are different'
else 'check the syntax' end
from v$version where banner like 'Oracle%';
Thanks,
Boris Dali.
--- "Pillai, Rajesh" <[EMAIL PROTECTED]>
wrote: > Hi All,
> Any clues on why does a CASE block does not work in
> an INSERT-APPEND-SELECT statement called in a sql
> block in an UNIX script? If I replace the case block
> with a decode function then it works Whereas in
> a sql command prompt, both DECODE and CASE yields
> results.
>
> TIA for all ur hints.
>
> Thanks,
> Rajesh
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Pillai, Rajesh
> 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).
__
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
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).
sql trace - forward attribution
I don't have the book with me right now, but I am obviously missing something in the "forward attribution" concept as it doesn't seem to help me in explanation of the following lines: WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #31: nam='SQL*Net message from client' ela= 692 p1=1413697536 p2=1 p3=0 WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 WAIT #31: nam='SQL*Net message from client' ela= 2295 p1=1413697536 p2=1 p3=0 Shouldn't 1 + 692 + 1 (and possibly + 2295 ???) be less than 261? Oracle 9.2.0.4.0 on HP-UX 11.11 Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: STATSPACK interpretation
Thanks, Jonathan. Of course you are right :-) Playing with this a little longer, I can get up to 4 versions (child_number from 0 to 3) of the same pl/sql cursor by changing bind variable sizes. It ceases being sharable when bv size changes from 32 to 33, from 128 to 129 and from 2000 to 2001: 1-32 33-128 129-2000 2001-4000 I wonder if this behavior can be changed by some init settings? Thanks, Boris Dali. --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > Notes 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 > > > One-day tutorials: > http://www.jlcomp.demon.co.uk/tutorial.html > > > Three-day seminar: > see http://www.jlcomp.demon.co.uk/seminar.html > UK___November > > > 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: Tuesday, December 23, 2003 8:44 PM > > > > Jonathan, > > > > Wouldn't bind variable issue that prevents cursor > from > > sharing be visible in bind_mismatch? > > I would certainly hope so - but I remember playing > around with v$sql_shared_cursor when it first came > out and find cases where un-shared cursors came up > with a full set of N's in the view. > > > How can one simulate this? > > > > var v varchar2(1) > > begin select count(5) into :v from dual; end; > > / > > select address, sql_text from v$sql where sql_text > > like '%count(5)%'; > > > > ADDRESS SQL_TEXT > > > > > -- > - > > 6DE92A74 SELECT count(5) from dual > > 6DE960D0 begin select count(5) into :v from dual; > end; > > > > -- Change a bind variable size: > > var v varchar2(30) > > begin select count(5) into :v from dual; end; > > / > > -- same output, no change, both sql and pl/sql > wrapper > > cursors are still shared > > > > Nicely done. I think I'd run event 10046 > at level 4 as well to get the bind variable dumps > and check if the the SQL (or pl/sql) environment > was ignoring the MAXLEN value for your > variables. There are a few places where 'special > optimisations' exist in Oracle's internal coding. > > You might also try it with the most extreme > case - it may be (for example) that Oracle > rounds up varchar2() variables to 32 bytes - > I'd go for 1 and 4000 - just in case. > > > > > -- Change a bind variable type: > > var v number > > begin select count(5) into :v from dual; end; > > / > > ADDRESS SQL_TEXT > > > > > -- > -- > > 6DE92A74 SELECT count(5) from dual > > 6DE960D0 begin select count(5) into :v from dual; > end; > > 6DE960D0 begin select count(5) into :v from dual; > end; > > > > -- ok, here pl/sql parent (dep=0) cursor is no > longer > > shared > > > > [EMAIL PROTECTED]> select * from v$sql_shared_cursor where > > kglhdpar = '6DE960D0'; > > > > ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D > L T > > R I I R L I O S M U T N F > > - - - - - - - - - - - - - - - - > - - > > - - - - - - - - - - - - - > > 6DE95B54 6DE960D0 N N N N N N N N N N N N N N N N > N N > > N N N N N N N N N N N N N > > 6DE86F94 6DE960D0 N N N N N N N N N N N N N N Y N > N N > > N N N N N N N N N N N N N > > > > 2 rows selected. > > > > -- yep, bind variables mismatch > > > > Thanks, > > Boris Dali. > > > > -- > 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: STATSPACK interpretation
Jonathan, Wouldn't bind variable issue that prevents cursor from sharing be visible in bind_mismatch? How can one simulate this? var v varchar2(1) begin select count(5) into :v from dual; end; / select address, sql_text from v$sql where sql_text like '%count(5)%'; ADDRESS SQL_TEXT --- 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; -- Change a bind variable size: var v varchar2(30) begin select count(5) into :v from dual; end; / -- same output, no change, both sql and pl/sql wrapper cursors are still shared -- Change a bind variable type: var v number begin select count(5) into :v from dual; end; / ADDRESS SQL_TEXT 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; 6DE960D0 begin select count(5) into :v from dual; end; -- ok, here pl/sql parent (dep=0) cursor is no longer shared [EMAIL PROTECTED]> select * from v$sql_shared_cursor where kglhdpar = '6DE960D0'; ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 6DE95B54 6DE960D0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 6DE86F94 6DE960D0 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N 2 rows selected. -- yep, bind variables mismatch Thanks, Boris Dali. --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > I recall James Morle saying something about > code not being sharable if the declared sizes > of the bind variables don't match. If Informatica > is using a 3GL to call anonymous pl/sql blocks > with different bind variables every time, perhaps > it is causing a bind variable mismatch. > > As for the 400MB - I've often noticed oddities where > a new entry is created, but "carries forward" a > report > of the memory requirements of earlier variants, so > if you > have 10 cursors, they don't report 10 units of > memory, but > 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 units. It is > possible that you are seeing some effect like this. > > > > > 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 > > > One-day tutorials: > http://www.jlcomp.demon.co.uk/tutorial.html > > > Three-day seminar: > see http://www.jlcomp.demon.co.uk/seminar.html > UK___November > > > 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: Tuesday, December 23, 2003 5:09 PM > > > > Thomas, > > > > The version count is the number of child cursors > > present in the cache for this SQL. The cursor is > > not being shared for some reason with 456 > versions. > > > > The 400m of memory seems a bit excessive. > > > > There is a script at Jonathan's site with some > info > > about v$sqlarea and a script you can run that > looks > > at the current memory requirements for a SQL > statement. > > > > http://www.jlcomp.demon.co.uk/sqlarea.html > > > > Does the output match what you see in statspack? > > > > Also, the number of executions is much lower than > > the version count, which is rather odd. There's a > bug > > in early 9i versions that would cause this, but > was > > supposed to be corrected by 9.2.0.2. > > > > In experimenting with this, I managed to get 4 > different > > sessions to create 2 versions of a cursor. I'm > not sure > > why as it was pl/sql and variables were used for > the calling > > parameters. > > > > A 'select * from v$sql_shared_cursor' did not > reveal any > > reason for it. > > > > After bouncing the database and trying this again, > I couldn't > > duplicate it. > > > > Maybe a couple of things to pursue here, but > perhaps not > > an abundance of help. :( > > > > Jared > > > > > > > > On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote: > > > Jared, > > > > > > Digging into it more, I found out that it's > called from an > > > Informatica client. Apparently, the gist of > the client-side > > > algorithim is as follows: > > > > > > For eac
Re: RE: connection pooling from an application server to oracle
I might be totatly off here, but I think it depends on the type of the pool. For example in OCI land there are two - connection pool and session pool. The former is stateful, while the latter is stateless. Again unless I am totally off base, with a session pool I think your session gets returned to a pool manager (for potential re-use) after _about_ every statement, effectively making tx:stmt relationship to become about 1:1. This is exactly what I am dealing with here and as a result when user navigates on the screen the app server instead of issuing say 30 selects, adds additional 30 implicit rollbacks, one after each select. This is also the reason I think I see pattern like this for example all over on DMLs: XCTEND rlbk=0, rd_only=0-- explicit commit here WAIT #0: nam='SQL*Net message to client'... WAIT #0: nam='SQL*Net message from client'... XCTEND rlbk=1, rd_only=1-- implicit rollback right after commit due to OCITransRollback() call Thanks, Boris Dali. --- [EMAIL PROTECTED] wrote: > doesnt this force you to commit after every single > DML statement? > > > > From: "Mercadante, Thomas F" > <[EMAIL PROTECTED]> > > Date: 2003/12/15 Mon AM 08:36:09 EST > > To: "'[EMAIL PROTECTED]'" > <[EMAIL PROTECTED]> > > CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > Subject: RE: connection pooling from an > application server to oracle > > > > Ryan, > > > > This is becoming for normal. There are a lot of > software pieces that do > > connection pooling - basically, everybody is > plaing in everbody else's > > space. > > > > I have a couple of projects where the app-server > does the connection > > pooling. One using Dcom and the other IBM > WebSphere. > > > > From your point of view, it's just one less thing > to worry about. The > > number of db connections will be relatively small. > The app server keeps > > track of transactions. As long as they say it > works, it's not your problem. > > > > Tom Mercadante > > Oracle Certified Professional > > > > > > -Original Message- > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > > Sent: Monday, December 15, 2003 7:59 AM > > To: Multiple recipients of list ORACLE-L > > Subject: connection pooling from an application > server to oracle > > > > > > The software engineers here are using an > application server with connection > > pooling to connect to our oracle instances. > > They are doing it with a dedicated connection to > Oracle. No MTS. > > > > they compartmentalize stuff here, so Im having > trouble figuring out exactly > > how this affects the database and how to monitor > performance. All I know is > > that I see a handful of constantly open dedicated > connections. I have been > > told that this is actually alot of users > connecting to the database. > > > > This concerns me. how do you handle transaction > control in this type of > > environment? in this type of environment do you > have to commit after every > > DML statement? since multiple users will access > the database with the same > > conneciton? > > > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > 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: <[EMAIL PROTECTED] > 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 >
RE: CPU Capacity Planning
Thanks a lot, Cary.
Yes, this sentence on p.248:
"As long the execution of each business function can
be expressed in terms of an LIO count, you can
translate the queueing model's output in terms of
business function response time and throughput"
was the one I marked as something to go back to, as I
didn't really understand it.
Thanks,
Boris Dali.
--- Cary Millsap <[EMAIL PROTECTED]> wrote: >
Boris,
>
> I think I covered this in my response to Ryan. It
> was the "two stages"
> part.
>
> Note that you can avoid even using queueing theory
> at all if you just
> make sure that utilization stays to the left of the
> knee in the
> performance curve for each resource on the system.
> You can learn the
> location of the knee for a given number of parallel
> service channels
> (for example, CPUs in your case) on Table 9-3 on
> p260 of "Optimizing
> Oracle Performance."
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Performance Diagnosis 101: 12/16 Detroit, 1/27
> Atlanta
> - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -Original Message-
> Boris Dali
> Sent: Sunday, December 07, 2003 12:39 PM
> To: Multiple recipients of list ORACLE-L
>
> Thanks for the clarifications, Cary.
>
> With regards to a hardware sizing - how do LIOs fit
> into queueing theory? Let's say I can come up with
> something like:
>
> #CPUs required = Sum( LIOs(Bus.Tx i)) /
> (10,000*clock rate/100)
>
> where i={Bus.Tx 1..n}
>
> [on a projected box that haven't been bought yet, it
> might be a little difficult to estimate the
> denominator, ... and on the existing one I guess I
> have to get hold of Jonathan's paper to learn how
> this
> can be done]
>
> ..but in any event for forecasting purposes, how
> queueing effect might be taken into account here?
> Let's say I measured Sum( LIOs(...)) for a 50 users
> in
> a unit testing environment and I am told that
> production would be 10 times more than that, what do
> I
> do?
>
> Thanks,
> Boris Dali.
>
> --- Cary Millsap <[EMAIL PROTECTED]> wrote: >
> My answers are in-line, preceded with "[Cary
> > Millsap]"...
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Performance Diagnosis 101: 12/16 Detroit, 1/27
> > Atlanta
> > - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
> > - Hotsos Symposium 2004: March 7-10 Dallas
> > - Visit www.hotsos.com for schedule details...
> >
> >
> > -Original Message-
> > Boris Dali
> > Sent: Sunday, December 07, 2003 9:54 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > Thanks a lot for the reply, Cary. Yes, your
> > explanation makes all the sense in the world even
> > though it is precisely the weighted average
> approach
> > that I've seen on some capacity planning
> > spreadsheets.
> >
> > Two additional questions if I may, Cary.
> > Would it be correct to say that when I throw
> > additional users on a system it is only queueing
> > component of a response time that climbs up, while
> > service time stays the same?
> >
> > [Cary Millsap] "Sort of," but not exactly. There
> are
> > lots of scalability
> > threats that begin to manifest in reality when you
> > crank up the load.
> > For example, you'll see "latch free" waiting on
> > applications that parse
> > too much, but only at higher user volumes (never
> in
> > unit test). You can
> > consider the new appearance of "latch free" events
> > to be a type of
> > queueing if you want, but it's really not queueing
> > in the sense of a
> > simple CPU queueing model.
> >
> > If that's true, than does
> > it matter how I measure service time of my Bus.Tx1
> -
> > on a loaded system where hundreds of users run
> this
> > operation or when nobody executes it all? Also is
> it
> > important to have the other two operations -
> Bus.Tx2
> > and Bus.Tx3 - running concurrently (as they would
> in
> > a
> > real life) for the c measurements?
> >
> > [Cary Millsap] You'll put yourself at risk if you
> > simply try to use a
> > queueing model to extrapolate big-system
> performance
> > from data collected
> > in a unit testing
RE: CPU Capacity Planning
Thanks for the clarifications, Cary.
With regards to a hardware sizing - how do LIOs fit
into queueing theory? Let's say I can come up with
something like:
#CPUs required = Sum( LIOs(Bus.Tx i)) /
(10,000*clock rate/100)
where i={Bus.Tx 1..n}
[on a projected box that haven't been bought yet, it
might be a little difficult to estimate the
denominator, ... and on the existing one I guess I
have to get hold of Jonathan's paper to learn how this
can be done]
..but in any event for forecasting purposes, how
queueing effect might be taken into account here?
Let's say I measured Sum( LIOs(...)) for a 50 users in
a unit testing environment and I am told that
production would be 10 times more than that, what do I
do?
Thanks,
Boris Dali.
--- Cary Millsap <[EMAIL PROTECTED]> wrote: >
My answers are in-line, preceded with “[Cary
> Millsap]”...
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Performance Diagnosis 101: 12/16 Detroit, 1/27
> Atlanta
> - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -Original Message-
> Boris Dali
> Sent: Sunday, December 07, 2003 9:54 AM
> To: Multiple recipients of list ORACLE-L
>
> Thanks a lot for the reply, Cary. Yes, your
> explanation makes all the sense in the world even
> though it is precisely the weighted average approach
> that I've seen on some capacity planning
> spreadsheets.
>
> Two additional questions if I may, Cary.
> Would it be correct to say that when I throw
> additional users on a system it is only queueing
> component of a response time that climbs up, while
> service time stays the same?
>
> [Cary Millsap] “Sort of,” but not exactly. There are
> lots of scalability
> threats that begin to manifest in reality when you
> crank up the load.
> For example, you’ll see “latch free” waiting on
> applications that parse
> too much, but only at higher user volumes (never in
> unit test). You can
> consider the new appearance of “latch free” events
> to be a type of
> queueing if you want, but itÂ’s really not queueing
> in the sense of a
> simple CPU queueing model.
>
> If that's true, than does
> it matter how I measure service time of my Bus.Tx1 -
> on a loaded system where hundreds of users run this
> operation or when nobody executes it all? Also is it
> important to have the other two operations - Bus.Tx2
> and Bus.Tx3 - running concurrently (as they would in
> a
> real life) for the c measurements?
>
> [Cary Millsap] YouÂ’ll put yourself at risk if you
> simply try to use a
> queueing model to extrapolate big-system performance
> from data collected
> in a unit testing environment. ItÂ’s because of the
> potentially
> out-of-model scalability threats.
>
> In other words assuming I have an identical replica
> of
> a production environment where I am the only user -
> would service time/rate measured there be applicable
> for a loaded system with heterogeneous workload?
>
> [Cary Millsap] ...Only if you your production
> environment doesnÂ’t
> trigger any new serialization issues that werenÂ’t
> visible on your unit
> test env.
>
> And another stupid question.
> Knowing individual business tx. characteristics
> (response time, number of CPUs required to comply
> with
> SLA requirements, average utilization per CPU, etc),
> how does one go about sizing the box in terms of the
> overall "system" required CPU capacity? Or put it
> another way - what do I tell a hardware vendor?
>
> That is, if what comes out of a queueuing exercise
> is:
>m pho
> ---
> Bus.Tx1 2-way70%
> Bus.Tx2 3-way50%
> Bus.Tx3 4-way80%
>
> What should be the optimistic (let's assume perfect
> liner CPU scalability for now) recommendation to
> decision makers in terms of the horsepower required
> to
> run this "system" on?
> After all, yes individual business transactions have
> their own SLA requirements (e.g. worst tolerated
> response time), but they all use the same resources,
> don't they? So even though a service time of Bus.Tx1
> might remain constant the queueing delay (and hence
> the response time) would likely to increase due to
> other concurrent activities on the system. Is there
> a
> way to account for this if capacity planning is done
> at the individual bus.tx level?
>
> [Cary Millsap] The hardest part about capacity
> planning is that thereÂ’s
> no useful industry-wide standard unit of CPU work to
> use. You canÂ’t use
> MHz, you canÂ’t
RE: CPU Capacity Planning
Thanks a lot for the reply, Cary. Yes, your explanation makes all the sense in the world even though it is precisely the weighted average approach that I've seen on some capacity planning spreadsheets. Two additional questions if I may, Cary. Would it be correct to say that when I throw additional users on a system it is only queueing component of a response time that climbs up, while service time stays the same? If that's true, than does it matter how I measure service time of my Bus.Tx1 - on a loaded system where hundreds of users run this operation or when nobody executes it all? Also is it important to have the other two operations - Bus.Tx2 and Bus.Tx3 - running concurrently (as they would in a real life) for the c measurements? In other words assuming I have an identical replica of a production environment where I am the only user - would service time/rate measured there be applicable for a loaded system with heterogeneous workload? And another stupid question. Knowing individual business tx. characteristics (response time, number of CPUs required to comply with SLA requirements, average utilization per CPU, etc), how does one go about sizing the box in terms of the overall "system" required CPU capacity? Or put it another way - what do I tell a hardware vendor? That is, if what comes out of a queueuing exercise is: m pho --- Bus.Tx1 2-way70% Bus.Tx2 3-way50% Bus.Tx3 4-way80% What should be the optimistic (let's assume perfect liner CPU scalability for now) recommendation to decision makers in terms of the horsepower required to run this "system" on? After all, yes individual business transactions have their own SLA requirements (e.g. worst tolerated response time), but they all use the same resources, don't they? So even though a service time of Bus.Tx1 might remain constant the queueing delay (and hence the response time) would likely to increase due to other concurrent activities on the system. Is there a way to account for this if capacity planning is done at the individual bus.tx level? Thanks, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > Boris, > > If you mean that some people on your system execute > Bus.Tx1, some others > execute Bus.Tx2, and some others (maybe with some > overlap) execute > Bus.Tx3, then my answer to your question is: > > No, I would strongly encourage you *not* to do > this! > > It was exercises like this that first led me to > discover the fact that > there's no such thing as a "system" in the sense > that most people use > the term (that is, as a big mishmash of different > transactions, in which > averages have any real meaning). > > Combining your three CDFs will hurt you in the way > described in "Why > understanding distribution is important" on > pp238-239 of the Optimizing > Oracle Performance book. Here's another example: > Imagine the following > "system"... > >avg. avg. > runs/day sec/run who uses it > Tx1 10,0001 Group A > Tx21,000 10 Group B > Tx3 100 100 Group C > > So, what's this "system's" average response time? A > naïve > "mathematician" might think it's the weighted > average of all the > response times: (1*1 + 1000*10 + 100*100) / > (1+1000+100) = 2.7 > sec. But what use is this figure? Nobody's response > time is "ever" > really 2.7 sec. I say "ever" here because > it's of course > possible that a program whose "avg. sec/run" is 1 > (or even 10) will > occasionally have a true response time of > 2.7. > > If you're *anybody* actually using the system, the > number "2.7 sec/run" > is just stupid! The 2.7s figure is especially > ludicrous if you're a > member of Group B or C, because your average > response time is either > really 3.7x that number (B) or 37x that number (C)! > The mathematical > explanation for the stupid-looking-ness is that, no > matter what you're > doing, this 2.7 number is an average influenced by > stuff that you're > *not* doing. > > There is no such thing as an "average user" (any > more than there's an > American family with 2.3 children); in this example, > there are only > members of Groups A, B, and C. What if you're a > member of two groups > simultaneously (e.g., you run different transaction > types in the same > day)? It's the same problem, because your > expectation of, for example, > Tx1 response time is completely different from your > expectation of Tx3 > response time. Clumping response times from Tx1 and > Tx2 in
CPU Capacity Planning
Let's say I have 3 business transactions (consisting of numerous Oracle transactions each) and I know total service time for each (from c readings off sql traces for the length of the bus.tx). Doing queuing theory exercise I can also get CDF(r max) for each. Let's say Bus.Tx1 - CPU time=5s CDF(r)=97% Bus.Tx2 - CPU time=8s CDF(r)=95% Bus.Tx3 - CPU time=10s CDF(r)=90% How can I combine these three together and make any conclusions as to what the overall CDF(r) would be for the whole system consisting of the above 3 business transactions? Is this doable? Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: raw traces - EXEC: c=10,000 e=40
Cary, thanks a lot for the explanation (and the other one [and especially the other one :-) ]). Much appreciated. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > You're on the right trail by experimenting with it. > I'm surprised that > the number of gettimeofday() calls is so small. The > times() call is the > SVR4 analog of the BSD getrusage() call. I guess I > shouldn't be > surprised to see both times() and getrusage() being > used. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 12/16 Detroit, 1/27 > Atlanta > - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... === message truncated === __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
Fwd: RE: raw traces - EXEC: c=10,000 e=40
Yes, turning time_statistics off (at the session level) gets rid of all the times() calls. Further turning off timed_os_statistics (from 5 to 0) gets rid of getrusage() as well, leaving only gettimeofday() calls Thanks, Boris Dali. --- Boris Dali <[EMAIL PROTECTED]> wrote: > Date: Wed, 03 Dec 2003 10:29:24 -0800 > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > From: Boris Dali <[EMAIL PROTECTED]> > Subject: RE: raw traces - EXEC: c=10,000 e=40 > > Is this true? strace-ing on linux shows gettimeofday > calls (for e) and getrusage (for c) - just like Cary > describes. On HP however tusc-ing a simple "select * > from dual" issued from sqlplus I see: > > calls sys call > - > 1 gettimeofday() > 1 getrusage() > 15times() > > Which sys call is used for what? > Guess I can turn timed_statistics off and see how it > affects the (amount of) sys calls issued > > Thanks, > Boris Dali. > > --- Niall Litchfield > <[EMAIL PROTECTED]> wrote: > > reads > Jonathan's description and blushes > > > > So it'll be a *feature* and not a bug then. IIRC e > > comes from POSIX > > gettimeofday calls on all platforms except Windows > > -- where it looks > > like GetTickCount. If this is true then the > accuracy > > of e will be > > limited by the accuracy of gettimeofday. > > > > Niall > > > > > -Original Message- > > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On > > > Behalf Of Niall Litchfield > > > Sent: 02 December 2003 22:24 > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: raw traces - EXEC: c=10,000 e=40 > > > > > > > > > I'd be highly, highly suspicious of the cpu time > > c. > > > > > > 1. You should be able to verify (roughly the > > elapsed time) of > > > the statement, if by no other means than running > > in sqlplus > > > with timing on. > > > 2. 3 statements with *exactly* the same cpu time > - > > all in > > > very very round numbers. Seems unlikely to me. > > > > > > Might be an hp-ux bug but as I don't have HP-UX > to > > play with > > > that is speculation of the first order. > > > > > > Niall > > > > > > > -Original Message- > > > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On > > > > Behalf Of Boris Dali > > > > Sent: 02 December 2003 20:49 > > > > To: Multiple recipients of list ORACLE-L > > > > Subject: raw traces - EXEC: c=10,000 e=40 > > > > > > > > > > > > Reading Cary's book I understand that c and e > > are > > > > measured via different system calls (haven't > > truss'ed > > > > [well tusc'ed] them yet - I am on HP-UX > 11.11), > > but > > > > would anybody know what the reasonable upper > > limit of > > > > c-e might be? > > > > > > > > I am looking at the trace file where c is more > > than > > > > two orders of magnitude greater than e, which > > make me > > > > wonder if I a have some anomaly on my system > > > > > > > > Some examples: > > > > > > > > EXEC > > > > > > > > > > #98:c=1,e=433,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=1777312113968 > > > > > > > > EXEC > > > > > > > #110:c=1,e=390,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,tim=1777 > > > > 312181650 > > > > > > > > PARSE > > > > > > > > > > #103:c=1,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1777314524922 > > > > > > > > Oracle 9.2.0.4.0 on HP-UX 11.11 > > > > > > > > Thanks, > > > > Boris Dali. > > > > > > > > > > > __ > > > > > > > > Post your free ad now! > http://personals.yahoo.ca > > > > -- > > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > > > -- > > > > Author: Boris Dali > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > > > San Diego, California-- Mailing list > and > > web > > > hostin
RE: raw traces - EXEC: c=10,000 e=40
Is this true? strace-ing on linux shows gettimeofday calls (for e) and getrusage (for c) - just like Cary describes. On HP however tusc-ing a simple "select * from dual" issued from sqlplus I see: calls sys call - 1 gettimeofday() 1 getrusage() 15times() Which sys call is used for what? Guess I can turn timed_statistics off and see how it affects the (amount of) sys calls issued Thanks, Boris Dali. --- Niall Litchfield <[EMAIL PROTECTED]> wrote: > reads Jonathan's description and blushes > > So it'll be a *feature* and not a bug then. IIRC e > comes from POSIX > gettimeofday calls on all platforms except Windows > -- where it looks > like GetTickCount. If this is true then the accuracy > of e will be > limited by the accuracy of gettimeofday. > > Niall > > > -Original Message- > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > > Behalf Of Niall Litchfield > > Sent: 02 December 2003 22:24 > > To: Multiple recipients of list ORACLE-L > > Subject: RE: raw traces - EXEC: c=10,000 e=40 > > > > > > I'd be highly, highly suspicious of the cpu time > c. > > > > 1. You should be able to verify (roughly the > elapsed time) of > > the statement, if by no other means than running > in sqlplus > > with timing on. > > 2. 3 statements with *exactly* the same cpu time - > all in > > very very round numbers. Seems unlikely to me. > > > > Might be an hp-ux bug but as I don't have HP-UX to > play with > > that is speculation of the first order. > > > > Niall > > > > > -Original Message- > > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > > > Behalf Of Boris Dali > > > Sent: 02 December 2003 20:49 > > > To: Multiple recipients of list ORACLE-L > > > Subject: raw traces - EXEC: c=10,000 e=40 > > > > > > > > > Reading Cary's book I understand that c and e > are > > > measured via different system calls (haven't > truss'ed > > > [well tusc'ed] them yet - I am on HP-UX 11.11), > but > > > would anybody know what the reasonable upper > limit of > > > c-e might be? > > > > > > I am looking at the trace file where c is more > than > > > two orders of magnitude greater than e, which > make me > > > wonder if I a have some anomaly on my system > > > > > > Some examples: > > > > > > EXEC > > > > > > #98:c=1,e=433,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=1777312113968 > > > > > > EXEC > > > > #110:c=1,e=390,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,tim=1777 > > > 312181650 > > > > > > PARSE > > > > > > #103:c=1,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1777314524922 > > > > > > Oracle 9.2.0.4.0 on HP-UX 11.11 > > > > > > Thanks, > > > Boris Dali. > > > > > > > __ > > > > > > Post your free ad now! http://personals.yahoo.ca > > > -- > > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > > -- > > > Author: Boris Dali > > > 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: 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
raw traces - EXEC: c=10,000 e=40
Reading Cary's book I understand that c and e are measured via different system calls (haven't truss'ed [well tusc'ed] them yet - I am on HP-UX 11.11), but would anybody know what the reasonable upper limit of c-e might be? I am looking at the trace file where c is more than two orders of magnitude greater than e, which make me wonder if I a have some anomaly on my system Some examples: EXEC #98:c=1,e=433,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=1777312113968 EXEC #110:c=1,e=390,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,tim=1777312181650 PARSE #103:c=1,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1777314524922 Oracle 9.2.0.4.0 on HP-UX 11.11 Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: migration sequence oci problem
Jeron, I don't know if it would help any, but if it is a DB issue, event 10217 might be useful: $ oerr ORA 10217 10217, 0, "debug sequence numbers" // *Cause: // *Action: I have never used it myself before, but if you push support a bit maybe they might provide details on how this can be used. I've just experimented with it a bit now, but to no avail - just setting it alone and/or combined with 10046 doesn't seem to make much difference. Thanks, Boris Dali. --- Jeroen van Sluisdam <[EMAIL PROTECTED]> wrote: > We could narrow it down to the value of the sequence > exceeding > 16777216 (2 to the power of 24) > This looks familiar as described in bug 2573172 > This bug describes to change the type in the odefin > call to type 3 but this > doesn't help. > I know this is not really a dba issue now anymore > but I'm desperate for any > advice because I don't have any developers > left with oci-knowledge > Tnx, > Jeroen > > > -Oorspronkelijk bericht- > Van: Jeroen van Sluisdam > Verzonden: Wednesday, November 26, 2003 16:40 > Aan: '[EMAIL PROTECTED]' > Onderwerp: migration sequence oci problem > > > Hi, > > We're testing an oracle 9.2.0.4 database with an > oracle 7 client. > This is a C++ client, using OCI to go to oracle. > We see strange behaviour when using a sequence which > worked > nicely before. The sequence is not incremented when > issueing > "select res_id.nextval from dual > > When I test this with an oracle 7 sqlplus client > this works also > as expected. > > Is there any known bug or issue known with migrating > oci-applications. > According to manuals it shouldn't be a problem and I > cannot find any > problems > on metalink about this. > > Thanks in advance, > > Jeroen > __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - recursive relationships
Thanks a lot, Cary. Indeed the indented notation seems rather convenient. I would be delighted to take your PD101 course, just not sure if people here dealing with the training budget would share the delight with me :-( Doesn't hurt to ask though... Thanks again, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - XCTEND rlbk=1, rd_only=1
Thanks, Jared. Yes, running select vs select+rollback in a loop of 1000 iterations I got similar results (average over 3 runs): STAT...user rollbacks 0 1,000 1,000 LATCH...enqueues 570 1,574 1,004 LATCH...shared pool7,434 9,063 1,629 STAT...recursive 7,754 10,264 2,510 LATCH...library cache 13,401 17,429 4,028 So, every select w/ a rollback is at least 1 enqueue and 4 latches (1 shared pool + 3 library cache) more expensive than select wo/ a rollback. Since latches are scalability inhibitors - would it be far away from the truth to say that an application that uses OCI session pooling (as opposed to connection pooling I suppose? - just reading chapter 9 of the OCI manual) doesn't scale well with respect to the number of concurrent users? Thanks, Boris Dali. --- Jared Still <[EMAIL PROTECTED]> wrote: > Using a slightly modified version of run_stats to > return > timings in 1/1 of a second, the timing of 1000 > iterations > of a loop executing noop vs. 1000 doing rollback: > > .0005 secs > .0354 secs > > Here are the stats that were different between the > two, > kind of what you would expect: > > LATCH.cache buffers chains 113 > 13320 > STAT...redo size 27160 > 27648 488 > STAT...user rollbacks 0 > 1000 1000 > LATCH.enqueues0 > 1001 1001 > LATCH.shared pool 3 > 1004 1001 > LATCH.session idle bit0 > 1001 1001 > STAT...execute count 3 > 1004 1001 > LATCH.library cache pin 13 > 2014 2001 > LATCH.session allocation 0 > 2002 2002 > STAT...recursive calls4 > 2008 2004 > LATCH.library cache 14 > 3018 3004 __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - recursive relationships
Thanks, Raj. So yes, as I said in my other email - the rule stated in the book seem to apply to EXEC db calls only (in case of SQL fired from PL/SQL). I guess I misinterpreted it the way that it applies to ALL db calls for recursive cursors. Thanks, Boris Dali. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Sorry about the last empty email ... > > Cary is right, the EXEC at dep=0 is the database > call you should be looking for, why? because until > #1 is parsed, db has no way of finding what needs to > do. And once it finds that "Oh I must run a SQL", > the dep increases. So, I'd look for a subsequent > EXEC instead of PARSE line. > > I'll take a stab at this ... lines with --> are > mine > > = > PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 > lid=83 tim=1617285502494 hv=1138148843 ad='605d0998' > --> Anonymous block > BEGIN nav_tree_pkg.get_nav_parent_node_id( >:p_nodeid, :p_parentnodeid ); > END; > END OF STMT > --> anon block gets parsed, it probably contains a > sql. > PARSE > #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 > --> Found the sql, so oracle opened another cursor > #1 which is dependent on cursor #1 so dep = 1 > PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 > lid=98 tim=1617285503241 hv=1778717541 ad='606795e8' > --> sql test > SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE > NAV_NODE_ID = :b1 > END OF STMT > --> Successful parsing of cursor #2 > PARSE > #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 > --> Executing cursor #2 > EXEC > #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 > --> Fetch cursor #2 > FETCH > #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 > --> Data returned to anon block > WAIT #1: nam='SQL*Net message to client' ela= 2 > p1=1413697536 p2=1 p3=0 > --> Now the anon block executes. the e time includes > the time for all actions of cursor #2 > EXEC > #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 > WAIT #1: nam='SQL*Net message from client' ela= 2470 > p1=1413697536 p2=1 p3=0 > > > Now, I'll just wait for Cary to come along and tell > me that I got it all wrong ... > > Happy Thanksgiving (or Turky Day) > Raj __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: sql trace - recursive relationships
Thanks for your reply, Daniel. Yes, it makes sense for the EXEC calls, but it doesn't explain the PARSEs, does it? PARSING IN CURSOR #1 len=94 dep=0... PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0... PARSING IN CURSOR #2 len=68 dep=1... PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1... Here I presume elapsed time of the PL/SQL call is 141-60=81 microsec, but it doesn't follow the same rule of parent db call following the recursive/child one - it is out of sequence. It is even more interesting with FETCHes. I can't seem to find FETCH call for the parent anywhere in the trace file. Maybe it makes sense to omit this call altogether as time tallied against PL/SQL proc call goes to EXEC anyway, but than again it breaks the rule. And no, I don't think it is a trace activation/data collection error as these parent-child cursors appear in the middle of the trace. Thanks again, Boris Dali. --- Daniel Fink <[EMAIL PROTECTED]> wrote: > Boris, > > Cary is correct. It gets a little confusing, > especially with pl/sql > involved. It also helps to remember to differentiate > between database > calls (parse/execute/fetch) and other events > (wait/stat). > > Using your example below, I'll attempt an > explanation inline. > > Daniel Fink > > Boris Dali wrote: > > > Reading Cary's "Optimizing Oracle Performance", > page > > 91 it says: > > "A database call with dep=n+1 is the recursive > child > > of the first SUBSEQUENT (empasis mine) dep=n > database > > call listed in the SQL trace data stream" > > > > Does this apply to the SQL issued from PL/SQL? > > > > I am looking at the simple packaged stored proc: > > > > PACKAGE BODY nav_tree_pkg is > > PROCEDURE GET_NAV_PARENT_NODE_ID > > ( p_NodeId IN NUMBER, > >p_ParentNodeId OUT NUMBER) > > IS > > BEGIN > > SELECT PARENT_NAV_NODE_ID INTO p_ParentNodeId > > FROM NAV_NODE > > WHERE NAV_NODE_ID = p_NodeId; > > EXCEPTION > > WHEN NO_DATA_FOUND THEN > > p_ParentNodeId := -1 ; > > END; -- Procedure > > END; > > > > ... and here's what I see in the trace (sorry the > > lines are probably wrapped): > > > > = > > PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 > lid=83 > > tim=1617285502494 hv=1138148843 ad='605d0998' > > BEGIN nav_tree_pkg.get_nav_parent_node_id( > >:p_nodeid, > >:p_parentnodeid > > ); > > END; > > > > END OF STMT > > PARSE > #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 > > > > BINDS #1: bind 0: dty=2 mxl=22(22) mal=00 scl=00 > pre=00 > > oacflg=01 oacfl2=0 size=48 offset=0 > >bfp=83fbc005ff80 bln=22 avl=01 flg=05 > >value=0 > > bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 > > oacflg=01 oacfl2=0 size=0 offset=24 > >bfp=83fbc005ff98 bln=22 avl=00 flg=01 > > = > > Here, the pl/sql block is parsed. The next step in > the statement process > is to execute the statement (parse/execute/fetch). > However, notice that > EXEC #1 is not the next database call. > > > > > PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 > lid=98 > > tim=1617285503241 hv=1778717541 ad='606795e8' > > SELECT PARENT_NAV_NODE_ID FROM NAV_NODE > > WHERE NAV_NODE_ID = :b1 > > END OF STMT > > PARSE > #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 > > BINDS #2: > > bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 > > oacflg=03 oacfl2=4001 size=24 offset=0 > >bfp=83fbc005f660 bln=22 avl=01 flg=05 > >value=0 > > Okay, here we parse the sql statement inside the > block. In the next > couple of db calls, we do the execute/fetch of > Cursor #2. > > > > > EXEC > #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 > > FETCH > > > #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 > > WAIT #1: nam='SQL*Net message to client' ela= 2 > > p1=1413697536 p2=1 p3=0 > > EXEC > #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 > > Here is the execute database call for the block. It > is the execution of > the block that motivates the parse/execute/fetch of > the inner statement. > > > > > WAIT #1: nam='SQL*Net message from client' ela= > 2470 > > p1=1413697536 p2=1 p3=0 > > = > > ... Totaly different calls > > = > > > > So here it looks like th
Re: sql trace - XCTEND rlbk=1, rd_only=1
Thanks, Jared, Tanel. I was a little supprised to see a combination rlbk=1,rd_only=1. Why read-only bit is set here if it rolls back anyway? So I thought may be they mark their tx explicitly as read-only (aka "set transaction read-only"). Reveiwing OCI fine manual there seemed to be an option of doing just that with the OCITransStart() call and setting some flags... But than simple test-case of tracing commit and rollback in SQL*Plus disproved the theory about read-only tx, as "normal" commit/rollback produce this combination regardless of the tx type: commit - XCTEND rlbk=0, rd_only=1 rollback - XCTEND rlbk=1, rd_only=1 set transaction read only commit - XCTEND rlbk=0, rd_only=1 rollback - XCTEND rlbk=1, rd_only=1 So much for my theory :-( Another question I had here is whether or not having so many (implicit?) rollbacks on about every SELECT statement all over my trace file bears any overhead and proves deficiency of a stateless architecture used by this application - but again reviewing v$sesstat before and after I see user rollbacks count incremented, but no additional redo vectors generated. I guess I can try Tom Kyte's test harness, but it just doesn't sit well with me that 30 selects are as cheap as 30 selects with 30 rollbacks. Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
sql trace - recursive relationships
Reading Cary's "Optimizing Oracle Performance", page 91 it says: "A database call with dep=n+1 is the recursive child of the first SUBSEQUENT (empasis mine) dep=n database call listed in the SQL trace data stream" Does this apply to the SQL issued from PL/SQL? I am looking at the simple packaged stored proc: PACKAGE BODY nav_tree_pkg is PROCEDURE GET_NAV_PARENT_NODE_ID ( p_NodeId IN NUMBER, p_ParentNodeId OUT NUMBER) IS BEGIN SELECT PARENT_NAV_NODE_ID INTO p_ParentNodeId FROM NAV_NODE WHERE NAV_NODE_ID = p_NodeId; EXCEPTION WHEN NO_DATA_FOUND THEN p_ParentNodeId := -1 ; END; -- Procedure END; ... and here's what I see in the trace (sorry the lines are probably wrapped): = PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1617285502494 hv=1138148843 ad='605d0998' BEGIN nav_tree_pkg.get_nav_parent_node_id( :p_nodeid, :p_parentnodeid ); END; END OF STMT PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1617285502483 BINDS #1: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=48 offset=0 bfp=83fbc005ff80 bln=22 avl=01 flg=05 value=0 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=0 offset=24 bfp=83fbc005ff98 bln=22 avl=00 flg=01 = PARSING IN CURSOR #2 len=68 dep=1 uid=98 oct=3 lid=98 tim=1617285503241 hv=1778717541 ad='606795e8' SELECT PARENT_NAV_NODE_ID FROM NAV_NODE WHERE NAV_NODE_ID = :b1 END OF STMT PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503230 BINDS #2: bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=4001 size=24 offset=0 bfp=83fbc005f660 bln=22 avl=01 flg=05 value=0 EXEC #2:c=0,e=151,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1617285503563 FETCH #2:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1617285503648 WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 EXEC #1:c=0,e=1037,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=1617285503786 WAIT #1: nam='SQL*Net message from client' ela= 2470 p1=1413697536 p2=1 p3=0 = ... Totaly different calls = So here it looks like the child CURSOR #2 with dep=1 is emitted AFTER the parent (CURSOR #1, dep=0) Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
sql trace - XCTEND rlbk=1, rd_only=1
I've got a third party package that connects to Oracle via OCI and works in HTTP-like (stateless) fashion. Reviewing raw SQL trace output I don't see a single commit or rollback there, but there are plenty of XCTEND tx markers with rlbk=1 (after about every SELECT statement). Is this normal? Does this mean that this app rollbacks (implicitly?) after each of those selects? Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: Redos gone crazy--a job for audit?
Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; --> Note the value here insert into t6 select obj# from sys.obj$ where rownum <= 1; commit; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; --> Compare the two. In my case it's almost 10-fold increase in the amount of redo generated. We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms Note: Bug above doesn't affect direct path load Thanks, Boris. --- Igor Neyman <[EMAIL PROTECTED]> wrote: > Why? > Did you have bad experiences with temp tables? > I thought, using temp tables should reduce amount of > redo. > > Igor Neyman, OCP DBA > [EMAIL PROTECTED] > > > > -Original Message- > Boris Dali > Sent: Friday, October 10, 2003 12:54 PM > To: Multiple recipients of list ORACLE-L > > Barbara, > > Shoot in the dark. Any chance last vendor upgrade > introduced global temporary tables? > __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: Redos gone crazy--a job for audit?
Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? --- Daniel Fink <[EMAIL PROTECTED]> wrote: > Barb, > > Even if you can't find the user, you can still find > the session info and > run a trace on the session. If it is consistent, you > should be able to > trace for a short amount of time and retrieve the > statements that are > generating redo. Then you can go back to the vendor > and say "This > statement (update emp set empno = empno) is > generating 3g of redo per > day and it is not performing any work. Please > consider this a P1 bug and > we need a fix in 10 days." It is especially valuable > if you can trace > the 'old-good' app and compare it with the 'new-bad' > app. > > Dan > > Barbara Baker wrote: > > > Dan: > > Thanks for this -- I'll definitely tuck this away > for > > future reference. > > > > Sadly, it's not going to help this time. I don't > have > > a user generating redo, I have an application > running > > amuck. > > > > The users (reporters) never log into the database. > > Some service (Solaris high availability service, I > > believe) logs a database user on 20 times, then > > buffers requests from the HA service to the > database. > > A minute or two later, it logs the 20 sessions > out > > and logs in 20 more. > > > > Between around 5:30 am and 3:00 am the following > day, > > the database is rolling a new redo log about every > 16 > > minutes. Pretty much new log file every 16 > minutes > > like clockwork. Between 3:00 and 5:30, the HA > > service is disabled and some kind of maintenance > is > > running. The entire database is about 4100 megs. > > We're generating more than 3 gigs of redo per day. > > > > I sure would like to know what's in those redo > logs. > > > > Thanks for the help! > > Looks like another beautiful weekend to hang out > on > > top of a mountain. Did you get to see the leaves > > turning this year?? > > > > Barb > > begin:vcard > n:Fink;Daniel > x-mozilla-html:FALSE > org:Sun Microsystems, Inc. > adr:;; > version:2.1 > title:Lead, Database Services > x-mozilla-cpt:;9168 > fn:Daniel W. Fink > end:vcard > __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: Copying statistics : used a lot ????
What a timely thread! We've been discussing this very issue back and forth for some time now in our organization. We are getting a new application, which presumably will run on something like lower-end Superdome 16-way, ~30GB RAM type of box. The question is whether 4-way, 4GB RAM Rp5470 (entry-level) will do as a test server or it should be a "close mirror" of the production one? The argument of those against a "similar to a Prod box" is simple: "Can't afford another one for this project. Don't you know how to use dbms_stats to convince CBO it's on Superdome with 16 CPUs and millions of rows of data and not on a 4-way, couple of thousands in row sources?" So if we "can't afford" what Raj describes - is a smaller server a viable solution for a test box? Or we have to convince damanagement that their "can't afford" is going to cost them more in the long run? (easlier said than done) --- As Cris mentioned I've read Tom's take on this, but it only confused me futher. Tom states: "Some people adopt the strategy of importing the prod statistics ... and think they can get optimizer to generate the plans that will be used in prod and test using that data ... That approach will work only if you can read a query plan and be 100% confident that the plan is good and will give subsecond response times ... I don't think I can make such a judgment call..." I don't follow. Does this imply that with importing stats we can't get 100% identical CBO decisions/executions plans in a DB on a smaller machine? Or is it that we have to be 100% confident that we "replicated" all the stats from Prod and it is not a simple task? Or something else? "... Most people are striving to get query plans that use indexes all of the time, without realizing that as you scale up, indexes may not be the best solution ..." This part I understood even less. After reading Cary's excellent paper on scalability I thought that O(n) type of scalability of FTS is worse than say O(log2, n) of IRS? Wouldn't it be correct to say than, that if today on a thousand row tables I get index access path delivering better response time that table scan, I can expect this to stay the same (or better) when my data gets to a million rows range? Is it the scalability of NL vs HJ Tom is taking about? Bitmap/Domain indexes? Or is it a "general" statement? "... This is not to say that ... import statistics is not very useful. Quite the contrary - I've seen people use (with great success) the ability to import/export statistics, but ***not to tune in test***. Instead they take the results of statistics gathering done in test and import into production! Quite the reverse of what most people initially consider using dbms_stats for..." The last remark certainly applies to me. With all due respect to Tom, I got only more confused ater reading the above. Can somebody enlighten me? TIA, Boris Dali. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Our production and test systems are same ... test > lags 24 hours behind > production that's all. But I have successfully used > dbms_stats to copy over > stats from production to test on a table by table > basis to verify explain > plans. > > My opinion WAD - Works as designed ... remember to > take a backup of existing > stats on test in a separate table so you can reload > them when needed > quickly. > > My experience is on 9ir2 only for this feature. > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly > personal. > QOTD: Any clod can have facts, having an opinion is > an art ! > > > -Original Message- > Sent: Friday, September 12, 2003 10:29 AM > To: Multiple recipients of list ORACLE-L > > > Well no, I have bought the Expert one on one book. > I check on his web site and I found one reference > where he addresses the use > of changing the stats. > > Usually you can find what is its opinion just by the > tone, but this time I > was not able to see if he's against or not on this. > > Can you share more of what's in the book ? > > > Stephane Paquette > Administrateur de bases de donnees > Database Administrator > Standard Life > www.standardlife.ca > Tel. (514) 499-7999 7470 and (514) 925-7187 > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > > > -Original Message- > Grabowy, Chris > Sent: Thursday, September 11, 2003 5:44 PM > To: Multiple recipients of list ORACLE-L > > > By chance, do you have Tom Kyte's latest book? > Effective Oracle by Desig
RE: Copying statistics : used a lot ????
What a timely thread! We've been discussing this very issue back and forth for some time now in our organization. We are getting a new application, which presumably will run on something like lower-end Superdome 16-way, ~30GB RAM type of box. The question is whether 4-way, 4GB RAM Rp5470 (entry-level) will do as a test server or it should be a "close mirror" of the production one? The argument of those against a "similar to a Prod box" is simple: "Can't afford another one for this project. Don't you know how to use dbms_stats to convince CBO it's on Superdome with 16 CPUs and millions of rows of data and not on a 4-way, couple of thousands in row sources?" So if we "can't afford" what Raj describes - is a smaller server a viable solution for a test box? Or we have to convince damanagement that their "can't afford" is going to cost them more in the long run? (easlier said than done) --- As Cris mentioned I've read Tom's take on this, but it only confused me futher. Tom states: "Some people adopt the strategy of importing the prod statistics ... and think they can get optimizer to generate the plans that will be used in prod and test using that data ... That approach will work only if you can read a query plan and be 100% confident that the plan is good and will give subsecond response times ... I don't think I can make such a judgment call..." I don't follow. Does this imply that with importing stats we can't get 100% identical CBO decisions/executions plans in a DB on a smaller machine? Or is it that we have to be 100% confident that we "replicated" all the stats from Prod and it is not a simple task? Or something else? "... Most people are striving to get query plans that use indexes all of the time, without realizing that as you scale up, indexes may not be the best solution .." This part I understood even less. After reading Cary's excellent paper on scalability I thought that O(n) type of scalability of FTS is worse than say O(log2, n) of IRS? Wouldn't it be correct to say than, that if today on a thousand row tables I get index access path delivering better response time that table scan, I can expect this to stay the same (or better) when my data gets to a million rows range? Is it the scalability of NL vs HJ Tom is taking about? Bitmap/Domain indexes? Or is it a "general" statement? "... This is not to say that ... import statistics is not very useful. Quite the contrary - I've seen people use (with great success) the ability to import/export statistics, but ***not to tune in test***. Instead they take the results of statistics gathering done in test and import into production! Quite the reverse of what most people initially consider using dbms_stats for..." The last remark certainly applies to me. With all due respect to Tom, I got only more confused ater reading the above. Can somebody enlighten me? TIA, Boris Dali. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Our production and test systems are same ... test > lags 24 hours behind > production that's all. But I have successfully used > dbms_stats to copy over > stats from production to test on a table by table > basis to verify explain > plans. > > My opinion WAD - Works as designed ... remember to > take a backup of existing > stats on test in a separate table so you can reload > them when needed > quickly. > > My experience is on 9ir2 only for this feature. > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly > personal. > QOTD: Any clod can have facts, having an opinion is > an art ! > > > -Original Message- > Sent: Friday, September 12, 2003 10:29 AM > To: Multiple recipients of list ORACLE-L > > > Well no, I have bought the Expert one on one book. > I check on his web site and I found one reference > where he addresses the use > of changing the stats. > > Usually you can find what is its opinion just by the > tone, but this time I > was not able to see if he's against or not on this. > > Can you share more of what's in the book ? > > > Stephane Paquette > Administrateur de bases de donnees > Database Administrator > Standard Life > www.standardlife.ca > Tel. (514) 499-7999 7470 and (514) 925-7187 > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > > > -Original Message- > Grabowy, Chris > Sent: Thursday, September 11, 2003 5:44 PM > To: Multiple recipients of list ORACLE-L > > > By chance, do you have Tom Kyte's latest book? > Effective Oracle by Design?? &
RE: selectivity of predicates with LIKE - diff between 8i and 9i
Aha, just come accross note 94051.1 explaining _LIKE_WITH_BIND_AS_EQUALITY... but it's probably not relevant in my case as my query doesn't make use of bind variables. And yes I tried it (at the session level) just for kicks with no effect on the execution plan Thanks anyway, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: selectivity of predicates with LIKE - diff between 8i and 9i
Thanks, KG No, CURSOR_SHARING is not set (meaning it defaults to EXACT on both 8i and 9i) No bind variables. The query given with ... LIKE 'LOVE%' is the real one Yes, I can try setting _LIKE_WITH_BIND_AS_EQUALITY. One additional piece of info - we don't have OPTIMIZER_INDEX_* parameter set on eigher one (I should re-read Tim's excellent paper). It would probably be a safer bet to change "supported" parameters first (after understanding what is different in 9i that is), but I am not sure what effect it might have on the rest of the app. Cheers, Boris. --- K Gopalakrishnan <[EMAIL PROTECTED]> wrote: > By any chance are you using CURSOR_SHARING parameter > in your 8i version? > I think the default selectivity of 5% is used while > costing the like > operator and with the binds (and with an underscore > parameter which > I think defaults TRUE) it is treated as equality . > > If not you can set the underscore parameter > _like_with_bind_as_equality to get the index > costing. > > KG __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
selectivity of predicates with LIKE - diff between 8i and 9i
Dear List, Is there any difference between 8i and 9i in how selectivity of the predicates with LIKE are estimated by CBO? We are migrating some apps running on 8.1.7.4 on HP-UX 11.0 into 9.2.0.3 on the same box and some queries choose completely different execution plans - HJ with FTS vs original NL with IRS. After simplifying the real query to a primitive one-liner it looks like predicates with LIKE are estimated differently in 9i: [EMAIL PROTECTED]> @target COUNT(1) -- 291 [EMAIL PROTECTED]> l 1* select count(1) from DIS_TAB_ALBUM_TITRE ALT where ALT.ait_ds_titre LIKE 'LOVE%' -- 8i: [EMAIL PROTECTED]> @explain8 Id ParCSTCDN Plan -- -- - 0 3 1 SELECT STATEMENT (choose) Cost (3,1,20) 10 1 SORT (aggregate) 21 3 2 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) Cost (3,2,40) -- 9i: [EMAIL PROTECTED]> @explain8 Id ParCSTCDN Plan -- -- --- 0 39 1 SELECT STATEMENT (choose) Cost (39,1,19) 10 1 SORT (aggregate) 21 39 8415 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) (Columns 1 Cost (39,8415,159885) -- 8i: Access path: index (index-only) INDEX#: 307169 TABLE: DIS_TAB_ALBUM_TITRE (obj_id=307169 -> DIS_IND_ALBUM_TITRE_1) CST: 3 IXSEL: 6.2017e-06 TBSEL: 6.2017e-06 ... BEST_CST: 3.00 PATH: 4 Degree: 1 -- 9i: Access path: index (index-only) Index: DIS_IND_ALBUM_TITRE_1 TABLE: DIS_TAB_ALBUM_TITRE RSC_CPU: 0 RSC_IO: 39 IX_SEL: 3.4877e-02 TB_SEL: 3.4877e-02 ... BEST_CST: 39.00 PATH: 4 Degree: 1 In 8i assuming a filter factor to be simply 1/NDV, CST is understandably equals to 3 (given the data below): INDEX#: 307169 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204303 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 161254NULLS: 0 DENS: 6.2014e-06 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 CMPTD CDN: 2 IRS CST= blevel+ff*lb+ff*cf=2 + 6.2*10^-6 * (1035 + 204303) ~ 3.3 -> 3 But in 9i CBO probably uses something else as a FF for this predicate with LIKE, since CST becomes 39: INDEX NAME: DIS_IND_ALBUM_TITRE_1 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204338 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 157906NULLS: 0 DENS: 6.3329e-06 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 ROUNDED CDN: 8415 CMPTD CDN: 8415 IRS CST= ??? = 39 Questions: 1) Does anybody know what CBO uses for a FF calcualation for predicates with LIKE in 9i? How does it get 39? 2) Is there a simple way to get it "back on track" to CST=2 without hints or stored outlines - some spfile parameter would be ideal? 3) Both computed cardinalities seem to be way off (2 in 8i, 8415 in 9i - while the real number of rows returned is 291). Would histograms be the right way to get CMPTD CDN closer to the reality in this case? Not sure if it's important, but we are using automatic PGA management here (worksize_policy_area is TRUE, pga_aggreagate_target is a 100M) Thanks for any help, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: statspack snapshots cause 3-4 sec of 100% CPU utilization
Mogens, Dennis, I know I am going to catch flames for this, but here goes: It seems to be quite fashionable to bash statspack, but help me out in understanding the alternatives to the StatsPack in the following cases: Case1: - At the client I am currently with, they have some 50+ applications. Performance is generally not an issue. Can things be optimized? Oh yeah. No doubt about it. Users don't complain though and 3-person DBA team focuses on operational/prod. support stuff. 3 weeks ago applcation support person took one of the applications "offline" and submitted a job to rebuild the indexes (this functionality is built into the app). DBA team wasn't obviously consulted as to whether or not it's of any benefit. In fact DBA team wasn't even aware that there's a maintenance activity going on :-( Job dutifully dropped the indexes (as it doesn't seem to be aware of rebuilding, not to mention rebuilding online etc), but got stuck somewhere on re-creating them... so next morning app support person realized that there's a problem and recalled that there is a DBA team "that probably messed up his DB" (from his response to the ticket). For DBA it didn't take long to see that what used to be a quick index lookup returning a single row, turned out to be a FTS on a 3mln row table as index wasn't re-created there. But... If OS and StatsPack monitoring was in place DBA would know that long before app support person showed up as iostat -nmxzP on Solaris (or even iostat -d -x on Linux) with StatsPack data (or something as simple as query comparing current v$filestat with baseline one) would be self evident that things changed. Utility to check execution plans against the baseline ones would be probably useful in this case, but I haven't heard about it before this thread :-) Case2: -- Application benchmarking. Last summer while with another client, DBA team was asked to monitor the newly purchased system in stress testing/sizing exercise. While not explicitely stated two additional objectves were to verify the scalability of the app with respect to the number of concurrent users as well as the data volumes. Mercury tools were used in colloboration with the StatsPack on the back-end. We couldn't get more than some 120 concurrent users and StatsPack clearly indicated the load profile w/ ~500,000 LIOs/sec, only ~50 KB/sec redo generation, 300:1 read:write ratio, with latch free being by far the most prominent wait event. This case is probably an extreme, but when the support engineers of the product received our Mercury/StatsPack report there was no doubt that "SQL needs to be revisited" The point I am trying to make is that I think there's monitoring for the sake of tuning (and than I agree - system wide data shouldn't probably drive the tuning effort). But there's also other types of monitoring where StatsPack seems to be quite useful to me. It all depends on the objectives. Cheers, Boris Dali. --- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > Couldn't agree more. We need to stop using StatsPack > for gathering lots > and lots of stats we can't use for anything anyway. > When two experts can > look at the same summary data and get to different > conclusions you're > not gathering data at the correct level. It's sort > of like the > economists that will study the reams of data about > GDP, GDI, money > supply 1 thru 6, and what have you - and arrive at > complete opposite > conclusions. Same with bstat/estat, StatsPack, our > own MirMon, etc. > > Mogens > > DENNIS WILLIAMS wrote: > >Boris - I'm not surprised in your results ______ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: statspack snapshots cause 3-4 sec of 100% CPU utilization
Nice one, John!
And quite portable. Runs on Solaris and Linux alike.
On HP-UX 11.0 I had to modify it slightly, but it
looks good too:
if [ $# -eq 1 ] ;then
UNIX95= ps -eo pid,pcpu,ruser,time,etime,args | grep
$1 | sort -nr +1 | awk '{if (NR <= 20) print
substr($0,1,80)}'
else
same as above wo/ grep
fi
I usually used BSD stile of ps on Linux. Something
like:
ps aufx | egrep '(USER|oracle)'
with exporting COLUMNS=200 prior to running this and
stiking the puppy into an alias. But sorting by %CPU
looks cool. (Solaris doesn't seem to understand the f
flag and it should be /usr/ucb/ps, not default
/usr/bin/ps)
Ok, back to the original issue :-)
Since snapshot causes 100% CPU util only for some 3
sec it was quite a challenge to catch it. The best I
got so far is 72% for the shadow process taking the
snapshot (after some 20 tries). I should probably put
it in an infinite loop and "spool" (or tee) it to a
file. Might as well try to catch /proc/pid/status (as
I don't have the luxury of pmap here on Linux) for
some memory stuff.
One thing, John. Since it doesn't split CPU
utilization into user and kernel buckets - how does
this help me? Wouldn't it be nice to get output
similar to ptime [or at least time(x)] in "ps" output?
I am thinking of taking Dennis's advice and see what
happens with level=0 or simply start commenting out
code in the package and see when high CPU utilization
drops (and I was hopping to go with level=7 to get
some segement stats - he-he)
Thanks, John.
Cheers,
Boris Dali.
--- John Kanagaraj <[EMAIL PROTECTED]> wrote: >
Boris,
>
> Nice to discuss this with someone who understands
> the numerous (and various)
> options of 'sar' :) I use 'sar -r' to cross verify
> the 'rate of need for
> swap' - a sudden increase may mean either bursts of
> I/O (eating up File
> buffer space), memory leaks or a sudden rush of
> programs Could you take
> a quick snapshot of the top 20 CPU consumers using
> the script below when the
> snapshot runs? It takes the SID as a parameter to
> grep out only Oracle
> processes for that SID. The interesting part is that
> the CPUTIME *and*
> ELAPSED time is shown - you should run the snapshot
> as a script (as in
> sqlplus perfstat/ @snapshot.sql) where
> snapshot.sql has an execute,
> followed by an exit. This way, one has a crude set
> of CPU and Elapsed time
> for that process as it runs...
>
> I use this to quickly point out processes that are
> heavy and consistent CPU
> consumers, allowing me to rap some knuckles ;-)
>
> #!/bin/ksh
> #
> # Name: top20.ksh
> # Purpose: Display the top 20 CPU consumers.
> Specify a SID to collect
> # only those top procs related to that
> SID in a multi-db system
> # Author:John Kanagaraj, DBSoft Inc/ Aug 2001
> # Notes: Tested and works on Solaris - may need
> adjustment for other OS
> #
> uptime
> echo "PID %CPURUSER CPUTIME ELAPSED
> COMMAND"
> if [ $# == 1 ]; then
> ps -eo pid,pcpu,ruser,time,etime,args | grep
> $1 | sort -nr +1 |
> head -20 | awk '{print substr($0,1,80)}'
> else
> ps -eo pid,pcpu,ruser,time,etime,args | sort
> -nr +1 | head -20 |
> awk '{print substr($0,1,80)}'
> fi
>
> John Kanagaraj
> Oracle Applications DBA
> DBSoft Inc
> (W): 408-970-7002
>
> Great, uplifting music - http://www.klove.com
>
> ** The opinions and statements above are entirely my
> own and not those of my
> employer or clients **
__
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
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: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, John. No there's no paging/swapping going on (1GB real memory for a single 200MB SGA and just a couple of users). Out of curiousity, John. I usually measure paging via vmstat (si/so columns on Linux and pi/po everywhere else - "everywhere else" being HP-UX, Solaris and AIX), as well as via sar -w (swpin/s, swpot/s) on HP-UX/Solaris and sar -W on Linux (pswpin/s, pswpot/s). Is sar -r a better way? Quick check shows that on Linux it seems to report memory and swap utilization (but not in terms of rates, rather absolute numbers). On HP-UX it doesn't seem to be covered by man pages, but effectively the output is the same as -w. On Solaris it shows "unused memory pages and disk blocks". And I don't currently have any IBM boxes around As for the wrong bucket... well, I'll be able to verify it in the next couple of weeks on Solaris and for sure on HP-UX. One thing I know is that both vmstat and sar -u agree here on Mandrake that it is the kernel-mode that chews up most of the CPU for this 3-4 sec snapshot time. Thanks, Boris Dali. --- John Kanagaraj <[EMAIL PROTECTED]> wrote: > Boris, > > I missed the second part of your question... > apologies. If your SGA/Shared > pool was partly swapped out, I would assume that you > might see an increased > 'system' utilization. Did you check 'sar -q' and > 'sar -r' at the same time > to check? I haven't used mandrake - just wondering > if the CPU cycles used > for memory access are being counted against the > wrong pigeonhole.. > > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > Grace - Getting something we don't deserve; Mercy - > NOT getting something we > deserve > Click on 'http://www.needhim.org' for Grace and > Mercy that is freely > available! > > ** The opinions and statements above are entirely my > own and not those of my > employer or clients ** ______ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, Dennis. I am with you on your point about GUI tools... --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Boris - I think John has an excellent point (as > always). I just remembered > that one vendor (can't recall which) has some sort > of "stealth" method to > directly sample the SQL buffer. They make a big deal > about how it doesn't > impact your system, so I would infer as John says > that with a large shared > pool this might be significant. >My point is to just collect the information that > is of value to you. If a > level 0 gets you everything you need, go with that. > When I suggest writing > your own routines, I'm not proposing that you could > collect all the > information STATSPACK collects more efficiently, but > if you only use one or > two pieces of information and you need a level 5 > snapshot to get it, then > you might consider a quick script to collect just > what you need. Also if you > need frequent snapshots to capture certain critical > data, you can avoid some > snapshots. >CPU cycles are meant to be used, so if snapshots > aren't affecting your > overall system, then what is the problem? Well, > unfortunately you would like > to collect statistics when the system is the > busiest. >I find STATSPACK to be the most useful when the > system appears "hung". > With the GUI tools you are still clicking screens > when the problem clears > itself up. You can take a couple of STATSPACK > snapshots and do damage > control on the people side in between. But man are > those snapshots SLW > when the system is about belly up. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] ______ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, John. Any insight as to why is it primarily the system mode CPU? I've seen high system mode CPU utilization in non-Oracle stuff (like NFS), but for Oracle I thought it should be primarily user mode? Does Oracle's "CPU used by this session" represents user-, kernel-mode or both? And what about "c" in the raw traces? Thanks John, Boris Dali. --- John Kanagaraj <[EMAIL PROTECTED]> wrote: > Boris, > > The default statspack snapshot is at level 5, which > collects Top SQL (by > buffer and Phys reads, etc.) from the Shared pool, > and that would cause > significant latching for a large shared pool which > in turn results in a high > CPU usage. You could try a level 0 snapshot and look > at the CPU utilization > at that time... > > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > Disappointment is inevitable, but Discouragement is > optional! > > ** The opinions and statements above are entirely my > own and not those of my > employer or clients ** > > > > -Original Message- > > From: Boris Dali [mailto:[EMAIL PROTECTED] > > Sent: Thursday, June 05, 2003 2:05 PM > > To: Multiple recipients of list ORACLE-L > > Subject: statspack snapshots cause 3-4 sec of 100% > CPU utilization > > > > > > As subject line indicates standard (level 5) > snapshots > > make "vmstat 1" or "sar -u 1 100" show 100% CPU > > utilization (75% system mode) for about 3 seconds. > > > > Is this normal? Is statspack that brutal on CPU? > And > > why would that be a system mode primarily? > > > > Environment: > > > > Oracle 9.2.0.2 on Mandrake 9.0 > > (2.4.19-16mdkenterprise) > > 2.4GHz uniprocessor P4 box, 1GB SDRAM > > > > TIA, > > Boris Dali. > > > > > > > __ > > > > Post your free ad now! http://personals.yahoo.ca > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Boris Dali > > 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: John Kanagaraj > 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). __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
statspack snapshots cause 3-4 sec of 100% CPU utilization
As subject line indicates standard (level 5) snapshots make "vmstat 1" or "sar -u 1 100" show 100% CPU utilization (75% system mode) for about 3 seconds. Is this normal? Is statspack that brutal on CPU? And why would that be a system mode primarily? Environment: Oracle 9.2.0.2 on Mandrake 9.0 (2.4.19-16mdkenterprise) 2.4GHz uniprocessor P4 box, 1GB SDRAM TIA, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, Dennis. I've been using statspack for quite some time now, but I've never bothered to ask myself an obvious question, namely what overhead does statspack impose on the system (taking about Heisenberg's principle of uncertainty, he-he) I guess part of the reason is the fact that statspack.snap returns prompt almost immediately so I sort of subconsciously assumed that it's ... "light" in terms of resource consumption. Thanks for the suggestion to write my own routines, but I don't think I'll go down this route. It's true that it's probably not too difficult as the statspack schema is pretty much self explanatory with RI constarints in place and besides (supprisingly) statspack package is not wrapped, but ... With every new release/feature you'll need to keep pace, which doesn't sound like fun to me. With standard out-of-the-box statspack you get it for free (stuff like segment stats in 9i statspack) Thanks again, Boris Dali. --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Boris - I'm not surprised in your results. I > wouldn't describe STATSPACK as > "brutal", but it is a significant hit, so you > wouldn't want to start doing > snaps at 1 second intervals. STATSPACK does collect > a LOT of data, and you > can adjust the amount of data collected with the > level if you feel the need > to reduce the brutality. If you find you only need a > few pieces of > information, you could write your own routines to > collect just what you > need. >I have no idea why your system mode sees an > impact. Perhaps someone who > has more systems experience can venture a guess. You > might try several > measurements just in case you caught the system at a > bad moment. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Thursday, June 05, 2003 4:05 PM > To: Multiple recipients of list ORACLE-L > > > As subject line indicates standard (level 5) > snapshots > make "vmstat 1" or "sar -u 1 100" show 100% CPU > utilization (75% system mode) for about 3 seconds. > > Is this normal? Is statspack that brutal on CPU? And > why would that be a system mode primarily? > > Environment: > > Oracle 9.2.0.2 on Mandrake 9.0 > (2.4.19-16mdkenterprise) > 2.4GHz uniprocessor P4 box, 1GB SDRAM > > TIA, > Boris Dali. > > > __ > > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Boris Dali > 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: DENNIS WILLIAMS > 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). __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: DB character set
In case anybody is interested in the character set business: I asked this question (best char set for mixed environment of Unix and MS DBs with primarily MS clients and C/S applications) Gilles Briard, the author of article in OraMag on accomodating Euro symbol in a DB (http://www.oracle.com/oramag/webcolumns/2001/index.html?euro.html). After couple of emails I realized that the basic question I should've asked in the first place is much simpler: Q: If DB charater set is WE8ISO8859P1 1-st user on MS Win with client side char set (part of NLS_LANG) WE8MSWIN1252 2-nd user on Unix with client side char set WE8ISO8859P1 Would there be any difference in performance (elapsed time) if both clients (one on Unix, one on MS Win) are located on the same segment with the same network bandwith and latency and run the same simple query (say select * from sys.source$; in SQL* Plus) ? I always thought that the one on MS Win might be slower (in the example above) because Oracle has to go through a char set conversion. The following URL from Oracle Globalization Guide seemed to confirm that: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch2.htm#100796 Gilles response was that if all other factors are the same and the only difference is the NLS_LANG value on the client, than user on Unix and MS should expirience the same performance (if I understood it correctly). The reason for this is the following steps that Oracle goes through in any case: 1 - DB server: translate char set P15 data to binary 2 - network transmission of binary data 3 - Client: translate binary to P15 back (in case of Unix) OR to WIN1252 (in case of MS Win) 4 - Client: graphical transformation So translation in step 3 or translation with conversion (or is it conversion with translation?) cost the same? I'll be setting up a test maybe next week (not only for performance impications, but also to check if symbols like 1/2 and 1/4 we use here can be stored and retrieved back), but I would appreciate if anybody can comment on the above. Resources used - 1. Globalization Guide 2. 144192.1 Whether or not to change the DB char set to support the Euro sign 3. 225938.1 Do I have incorrect char in the DB and how to recover 4. 140014.1 RDBMS support for the Euro Currency symbol 5. 15095.1 Exp/Imp and NLS considerations 6. 119119.1 UTF8 DB char set implications 7. 181508.1 Choosing the DB char set 8. 137127.1 Char set, Code Pages, Fonts and the NLS_LANG value 9. 158577.1 NLS_LANG explained (how does client-server char converison works?) 10. http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch2.htm#100796 Thanks, Boris Dali. --- Boris Dali <[EMAIL PROTECTED]> wrote: > Dear List, > > Life was pretty easy here before they came up with > this euro symbol :-( We had WE8ISO8859P1 (the > default > I bevieve) as a char set on all Unix and MS (sorry > we > have those as well) DBs accross the board. > That was also the client side char set (part of > NLS_LANG) - most clients here are on MS (code page > 1252), some browser based. > > Well, with euro it looks like they went different > directions: > - On MS: it's recommended to have WE8MSWIN1252 > (super > set of good old WE8ISO8859P1) > - On Unix: it's WE8ISO8859P15 > > My question is: > 1. if I want to stick with one char set accross the > board (as we have now) for all DBs on Unix and MS > AND > 2. avoid (automatic, but not free) conversion on > DB-Client Net communication AND > 3. we need euro symbol > > is there a painless solution out there? > > Thanks, > Boris Dali > > __ > > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Boris Dali > 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). > __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing l
DB character set
Dear List, Life was pretty easy here before they came up with this euro symbol :-( We had WE8ISO8859P1 (the default I bevieve) as a char set on all Unix and MS (sorry we have those as well) DBs accross the board. That was also the client side char set (part of NLS_LANG) - most clients here are on MS (code page 1252), some browser based. Well, with euro it looks like they went different directions: - On MS: it's recommended to have WE8MSWIN1252 (super set of good old WE8ISO8859P1) - On Unix: it's WE8ISO8859P15 My question is: 1. if I want to stick with one char set accross the board (as we have now) for all DBs on Unix and MS AND 2. avoid (automatic, but not free) conversion on DB-Client Net communication AND 3. we need euro symbol is there a painless solution out there? Thanks, Boris Dali __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: Recipe for application design to run on RAC
Thanks, Raj. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Boris, > > the example I gave you is 9012 RAC and I have 5 > other production systems > that are 9202 RAC. BTW remember if you have > cluster_database is true, then > no matter how many instances, you will see GC > traffic and boy those numbers > are crooked .. it is a generic problem hopefully > there will be patch for it > to fix the GC timing.. > > I think that advise should have been prefixed with > something like ... > "Following statement is issued so that in case your > application fails to > scale contrary to our well publicized claim that RAC > is extensible and > scalable, we can always blame on your > not-so-well-thought-rac-incompatible > design. This way we will be safe and no one in media > can blame us." > > I am pretty sure it is hidden somewhere ... > > BTW 9202 ... make sure you get all the patches ... > this upgrade is a painful > story (at-least for us). > Raj > __ > Rajendra JamadagniMIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't > reflect that of ESPN Inc. > > QOTD: Any clod can have facts, but having an opinion > is an art! > > > -Original Message- > Sent: Wednesday, December 04, 2002 9:19 PM > To: Multiple recipients of list ORACLE-L > > > On a more serious note the following guidelines look > interesting: > http://download-west.oracle.com/docs/cd/A97630_01/rac.920/a96600/migrate.htm > #1013313 > > "Migrate to RAC ... unless your application was > specifically designed to not use cluster database > processing". > I wonder why would somebody do that? > > This > e-mail message is confidential, intended only for > the named recipient(s) above and may contain > information that is privileged, attorney work > product or exempt from disclosure under applicable > law. If you have received this message in error, or > are not the named recipient(s), please immediately > notify corporate MIS at (860) 766-2000 and delete > this e-mail message from your computer, Thank > you.*****2 > __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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: Recipe for application design to run on RAC
Thanks a lot, Tim. Enjoyable reading. Much appreciated. Just to clarify what data modeling vendor might mean by "RAC-aware" model: In one of the draft suggestions they had "activity" class tables that would record all major steps in the system like registration, signing a contract, inventory replenishment etc. This class of tables would be inevitably DMLed from different nodes in active/active deployment and it would probably present "bottleneck" much like AOL or FND schemas in your example with Oracle Apps Another thing that I saw on the draft ERD was "event" entity with exclusive arc to 7 (seven!) others. Same thing. On the final one it's broken down into two event_sales and event_inventory. While it's easily can be abstracted to a single entity (similar attributes) they separated them into two presumably to minimize data shuffles over interconnect. Isn't it kind of "segregation" (if I am not mistaken the term Oracle doco seems to use is "application segmentation" http://download-west.oracle.com/docs/cd/A97630_01/rac.920/a96598/ebizapps.htm#22163)? Tim, I appreciate your "mutual failover" suggestion, but I am afraid it wouldn't work here as these 30+ apps would be totally merged and at these point cease to exist as separate schemas. Now to the cool stuff :) Your idea of the middle tier "data-routing" seems very interesting. Is it like app server having two data sources defined (one for each node) and issuing DMLs based on some sort of criteria? Would it be too much to ask you elaborate on this? Is it "one table gets rows inserted from one node with even ids (assuming sequence based artificial PK) and rows with odd ids from another node" type of scenario (or something similar like based on type/group/code/whatever but the same table DMLed from different nodes based on data ranges)? Or am I totally off base here? Tim, thanks again for your help! --- Tim Gorman <[EMAIL PROTECTED]> wrote: > comments inline... > __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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: Recipe for application design to run on RAC
Thanks, Raj. >> H... it is probably not an good example ... Why not? On the contrary. I am sure damanagement here would love to here this. Besides it fully supports Oracle's statement that application can be migrated to RAC "as is" (as I think Hemant mentioned). Wait... did you say 9i? release 2? recently? - may be it's a Christmas magic that made it happen for you? (although those sceptical might say that it had to do with your careful app "partitioning"/segmentation rather than a festive season) On a more serious note the following guidelines look interesting: http://download-west.oracle.com/docs/cd/A97630_01/rac.920/a96600/migrate.htm#1013313 "Migrate to RAC ... unless your application was specifically designed to not use cluster database processing". I wonder why would somebody do that? --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > H... it is probably not an good example but we > too have a (couple of) > mission critical app (affects on air production) > running on 9i RAC. One of > which has two major schema. We logically partitioned > the application such > that, for two groups of people 9accessing one schema > each) we gave them a > preference. > > Schema1 users have tns entry for db1 and fail over > to db2 > Schema2 users have a preference for db2 with a fail > over to db1 > > This effectively allows us to do load balance, they > don't share too much > data, so traffic through interconnect is manageable. > If need be, we just > shutoff listener on one side, and everyone fails > over to the other side > while we can perform maintenance. All their > applications are written in VB, > JAVA so they handle fail over from within > application. > > None of the people involved in the design worried > about which side of RAC > they will be on and how the DML activity affects etc > etc. They designed a > plain application with a good design and it is > working fine. > > Like I mentioned this is not a good example ... but > this is how we did it in > one of our major application. > Raj > __ > Rajendra JamadagniMIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't > reflect that of ESPN Inc. > > QOTD: Any clod can have facts, but having an opinion > is an art! > > *This > e-mail message is confidential, intended only for > the named recipient(s) above and may contain > information that is privileged, attorney work > product or exempt from disclosure under applicable > law. If you have received this message in error, or > are not the named recipient(s), please immediately > notify corporate MIS at (860) 766-2000 and delete > this e-mail message from your computer, Thank > you.*****1 > __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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: Are Oracle courses required for Oracle Certification now?
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.com > >-- > >Author: Lyndon Tiu > > 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.com > -- > Author: Alan Davey > 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 > === message truncated === __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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: Recipe for application design to run on RAC
gt; application somehow. Whether it is by major > application module (i.e. "sales > and marketing" versus "order entry and inventory" > versus "general ledger", > etc) as Boris had illustrated, or by some other > mechanism (i.e. all > customers whose names start with A-M on one node, > all whose names start with > N-Z on the other node, etc), the application must be > able to partition. > > --- > > In Oracle7 OPS and Oracle8 OPS and Oracle8i OPS, the > mechanism (i.e. > "pinging" performed through the I/O subsystem) was > quite slow on most > platforms, resulting in huge latencies. The major > exception to this rule > was DEC/Compaq/HP OpenVMS, where the performance of > the "pinging" mechanism > is so fast as to be quite unnoticeable. Not > surprising if one considers the > history of VMS and OpenVMS... > > Beginning with pieces of the cache-coherency > mechanisms in Oracle8i OPS and > fully implemented in Oracle9i RAC, the > "cache-fusion" mechanism still > performs the same locking and data-transfer of > database block buffers > between instances, only faster. How much faster is > dependent on the OS and > configuration. But the additional latency is still > there. Obviously, if > the inter-connect mechanism between nodes is not > fast or misconfigured, then > "cache-fusion" cannot be fast either... > > --- > > When Oracle states that "applications can be > migrated to RAC without > modification", they are saying so in the faith that > the reduced latencies in > the cache-fusion mechanism and other improvements in > the > sharing/modification of global enqueues will result > in almost-zero latency, > or at least latency that is within the tolerance of > the end-users. As the > old saying goes, "your mileage may vary" or YMMV. > As OpenVMS and its near > zero-latency "pinging" mechanism shows, the choice > and configuration of > platform really matters also! > > --- > > In order to assess if an application is likely to > scale effectively when > migrating from non-RAC to RAC, I would pay close > attention the nature, > frequency, and volume of UPDATE, SELECT ... FOR > UPDATE, and DELETE > statements generated by the application. While > still in its non-RAC > implementation, I would recommend collecting and > examining such SQL > statements generated by application and > understanding what program modules > are generating each statement and why. I would then > prioritize these > statements by their volume and the business > criticality of the generating > program module. Last, according to this > prioritization, I would examine how > the WHERE clauses and the data values used in them > can be controlled by > application logic. For example, if a > business-critical online form is > generating lots of UPDATE and SELECT ... FOR UPDATE > statements, is it > possible to determine whether those statements are > generated against rows > previously INSERTed by the same session? Or, does > that online form perform > UPDATE and SELECT ... FOR UPDATE operations against > any data in the database > at all? > > Some applications are really quite "partitionable" > under the covers, and > only a small amount of such analysis can assure you > that RAC is feasible. > Other applications are so dreadfully complex that > only by load-testing with > real-world data values can the scalability be > determined... > > Oracle has correctly identified all of the major > bottlenecks in > inter-instance contention and has improved each of > these areas in RAC since > OPS. The question is whether the improvements are > sufficient for your > requirements... > > --- > > Relevant wisdom from "The Meaning of Life" by Monty > Python (1983): > > Mother (going through labor pains, alert and > panicky): What's that for? > > Obstetrician #1: That's the machine that goes > "ping". << PING! >> You > see? That means your > baby is still alive! > > Obstetrician #2: And it's the most expensive > machine in the whole > hospital! > > Obstetrician #1: Yes, it cost over three > quarters of a million pounds! > > Obstetrician #2 (slowly and condescendingly): > AREN'T YOU LUCKY? > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Wednesday, November
RE: Recipe for application design to run on RAC
Thanks for taking time to reply, Cary. Much appreciated. Did I understand it correctly that in active/active setup it would be beneficial to give each node "it's own virtual empire" so to speak. Like one node to service say marketing and sales, while the other to deal with say inventory and automation and minimize interdependencies between the two? I was thinking more along the lines of equally distributing/balancing the utilization across the nodes (which presumably makes it easier to re-route db calls to surviving node in case of instance/node failure after remastering, since all nodes are "peers") I obviously need to do some serious RTFMing here. So if the key is to have application partitioned (by probably functional/business areas?), is it at the logical design stage that this needs to be accounted for? Assuming enterprise framework in place, like Zachman's (http://www.zifa.com/framework.html) would it be at the system model/logical level (or using Oracle Designer terminology I guess at the system analysis stage) that "design for RAC" comes to the picture for a first time? Thanks again. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > If two or more RAC instances will be trying to cache > the same data > blocks, then this causes the performance problems > that you'll see show > up as lots of time spent on the event called "global > cache cr request". > If you can partition your application so that RAC > nodes don't have to > share blocks very often through the cache fusion > mechanism, then your > system will scale a lot better. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic, Dec 9-11 Honolulu > - Hotsos Clinic 101, Jan 7-9 Knoxville > - Steve Adams's Miracle Master Class, Jan 13-15 > Copenhagen > - 2003 Hotsos Symposium, Feb 9-12 Dallas > > > -Original Message- > Sent: Tuesday, November 26, 2002 3:34 PM > To: Multiple recipients of list ORACLE-L > > Dear List, > > Number of times I've seen that one of prerequsites > for > switching from single node DB to OPS/RAC is to have > an > application specifically designed / architectured to > run on RAC. > Can somebody elaborate? Is it something "visible" on > > ERD? That is by looking at the model can RAC guru > tell > that it wouldn't work well on RAC? > Or put it another way can one conclude based on the > ERD that app was modeled to run on RAC? > > What's the recepie for app design for RAC? > > TIA > > __ > > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Boris Dali > 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.com > -- > Author: Cary Millsap > 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). > __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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).
Recipe for application design to run on RAC
Dear List, Number of times I've seen that one of prerequsites for switching from single node DB to OPS/RAC is to have an application specifically designed / architectured to run on RAC. Can somebody elaborate? Is it something "visible" on ERD? That is by looking at the model can RAC guru tell that it wouldn't work well on RAC? Or put it another way can one conclude based on the ERD that app was modeled to run on RAC? What's the recepie for app design for RAC? TIA __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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: Oracle 9.2.0.1/2 on Mandrake 9 - solved
--- Markus Reger <[EMAIL PROTECTED]> wrote: > out of curiosity : did you ever check the filesizes > in bytes of yr first corrupt downloads with the > bytes of the files on the download site? I did. They bear some resemblace, but figures are not exactly the same. I also compared sizes of files I got on disk (unpacked) after my first and second downloads and they are different for all 3 files, even though only Disk2 was... well, not good. Math doesn't seem to rule here. Go figure > i'm running O9i too, now wondering if this test might suffice to > tell whether the download was successful or not. i > didn't experience any probs so far until recently > when i changed/patched to 9.2.0.2.0 - the > oem/console dosn't display the XML database. the > flaw is, that it doesn't have some index ... > > created a totally new -userdefined,NOT preconfigured > - database and could successfully display the > XML-stuff. > > installation is on a rh 7.2 - was a rh 8.0 - but on > rh8.0 the console didn't work - unproperly > terminated connect string when attempting to connect > even to a local database. dumped the whole rh 8.0, > reinstalled the 7.2. > > lfw to a reply > mr > >>> [EMAIL PROTECTED] 11/14/02 17:32 PM >>> > Ray, right on target! > > After looking for disk errors in dmesg output, > /var/log/messages to no avail, I finally uninstalled > the whole 9.2 OH last night, > re-downloaded/re-burned/re-... and this time > installation of both 9.2.0.1 and a patch went with > no > glitches. > > Apparently Disk2 was corrupted/missing files/??? on > downloading/burning/unpacking, but what surprised me > the most is that I didn't get any errors at > gunzip/cpio stage, which I always thought of as a > good > indication that software on CDs is valid. This > assumption cost me week and a half of hopeless tries > to make it work... > > On the bright side, some .o, .so etc. files I see in > OUI during installation look painfully familiar... > > Thanks a lot for you help. > > --- Ray Stell <[EMAIL PROTECTED]> wrote: > On Tue, > Nov 12, 2002 at 01:50:52PM -0800 > > __ > > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Boris Dali > 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.com > -- > Author: Markus Reger > 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). __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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: Oracle 9.2.0.1/2 on Mandrake 9 - solved
Ray, right on target! After looking for disk errors in dmesg output, /var/log/messages to no avail, I finally uninstalled the whole 9.2 OH last night, re-downloaded/re-burned/re-... and this time installation of both 9.2.0.1 and a patch went with no glitches. Apparently Disk2 was corrupted/missing files/??? on downloading/burning/unpacking, but what surprised me the most is that I didn't get any errors at gunzip/cpio stage, which I always thought of as a good indication that software on CDs is valid. This assumption cost me week and a half of hopeless tries to make it work... On the bright side, some .o, .so etc. files I see in OUI during installation look painfully familiar... Thanks a lot for you help. --- Ray Stell <[EMAIL PROTECTED]> wrote: > On Tue, Nov 12, 2002 at 01:50:52PM -0800 __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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: Oracle 9.2.0.1/2 on Mandrake 9
Thanks for reply, Ray. I guess disk explanation is the most logical one to assume, but I've no idea how to confirm and overcome it. As I mentioned I re-installed it in different combinations multiple times. Would this file always go the same place on disk (say same bad sector)? One interesting thing I've noticed after comparing your ls -l output with mine is that all other 4 image files in this directory are dated way back to 2000 timeframe, whereas credits.gif has a time stamp of yestarday (day I tried the installation last time). I can view the file with the browser, however. What does it tell me? --- Ray Stell <[EMAIL PROTECTED]> wrote: > On Tue, Nov 12, 2002 at 10:53:37AM -0800, Boris Dali > wrote: > > > > Hope not everybody is off to OOW :( > > > > Did anybody get the above combo working? I know > that > > > > I have installed Oracle 9.2.0.1 on Mandrake 9 twice > on marginal machines without any such behavior. > Could > you have gotten an i/o error on the disk? > > # pwd > /db03/app/oracle/product/9.2.0/wwg/admin/images > [root@haglid images]# ls -l credits.gif > -rw-r--r--1 oracle oinstall 181222 Jul 12 > 2000 credits.gif > > I was just about to patch them, so I am nervous > about > getting to 9.2.0.2, hope to do that today. > > === > Ray Stell [EMAIL PROTECTED] (540) 231-4109 > KE4TJC28^D > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Ray Stell > 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). ______ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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).
Oracle 9.2.0.1/2 on Mandrake 9
Hope not everybody is off to OOW :( Did anybody get the above combo working? I know that Oracle is not certified on Mandrake and I don't care... as long as I can make it work that is Here are the facts: 1) HW: One way 2.4 GH box with 1GB DDR RAM, plenty of disk space 2) SW/OS: Mandrake 9 / 2.4.19-16mdk enterprise w/ the latest security patches applied 3) Plenty of swap (as per free, swapon -s, cat /proc/swaps) and /temp Here's `rpm -q gcc cpp glibc-devel kernel-headers binutils` output: gcc-3.2-1mdk package cpp is not installed glibc-devel-2.2.5-16mdk kernel-headres-2.4.18-41mdk binutils-2.12.90.0.15-1mdk OUI came up smoothly (had some troubles to convince it to come up with 8.1.7 on the same box - previous install, first OH). I went with Enterprise DB/SoftwareOnly option Problem: 1) at 61% on error pops up: "Error in writting to $OH/wwg/admin/images/credits.gif" - fuser on credits.gif doesn't show that somebody is using it - deleteting it (and/or the whole $OH/wwg directory) doesn't help as OUI simply re-creates them and gets stuck on the same error message - oraInventory log doesn't add any additional info - tried to re-install it I think a dosen times by now in diff. variations (Custom/w seed DB/ etc.) with the same result - applying 9.2.0.2 patch and re-install of the same component didn't help eigther (not sure why I expected it to help, but I seemed to exaust all the other options)... Is it a bad disk sector (no idea how to check disk for bad sectors on Linux)? Is it Oracle's corrupted jar file (no idea how to find name of the jar and test it for corruptions)? 2) The above error wouldn't bother me too much (no other errors at the install phase), but I also got stuck at the relinking phase which seem to be related to the error above. The reason is that my $OH/rdbms/lib is missing config.c, opimai.o, ssoraed.o, ttcsoi.o and may be others. So cancelling/ignoring PL/SQL Embedded Gateway component (what is it anyway? Sounds like Transparent gateways to Bill/Informix/DB2 etc. to me) seem to cancel installation of some critical files needed for relinking... Coping config.c from 8.1.7.4 (different binary tree/OH, even diff OS acount) doesn't seem to help: make -f ins_rdbms.mk config fails with infamous "Error 1" error. Coping config.o and the rest 3 o files missing and relinking via or gets a little futher, but fails latter with the same "Error 1" Reasons? Others seem to indicate 2 prime reasons: "-z defs" issue and binutils version: a- "-z defs" in LD_SELF_CONTAINED inside $OH/genclntsh doesn't seem to be an issue in my case. Oracle automatically comments it out, but even if I uncomment it and re-run genclntsh I get the same result: basename: too few arguments Try 'basename --help' cp: missing file arguments Try 'cp --help' ... Created $OH/lib/libclntst9.a So not everything is 100%, but at least I got libclntst9.a generated eigther way b- binutils? Not sure if it's still an issue in 9.2. Mine seems to be way higher than the one Oracle was ready to swallow in 9.1. I know for sure I didn't have a problem with 9.0.1 on Mandrake 8.0... Net result so far: I can confirm that Oracle Client 9.2 works like a charm on Mandrake9 :(. As far as the rest, so far I fail to get oracle, orapwd and others in my $OH/bin... Any help would be appreciated. TIA, Boris ______ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boris Dali 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).
