Re: order by
List can you please explain to me why select empno from emp order by empno ; is the same as select empno from emp order by sqrt(3.14); but not the same as select empno from emp order by dbms_random.value; What does sort by a random value do ? and why isn't dbms_random.value in the documentation for 9i Release 2 ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 03:34 [EMAIL PROTECTED] wrote: Hello list, Can someone please explain to me why the following order by clauses are valid and yield the same results : select empno, deptno from emp order by sqrt (1) ; and select empno, deptno from emp order by sqrt ( 3.14234 ) ; The docs say that in the order by clause you could specify only (a) column names or (b) positional parameters or (c) expressions involving the columns A constant falls under the c) category. It's an expression, which involves anything you want. That said, I fail to see any practical use . -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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 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).
sequence
Hello list and a happy new year to everyone, why does : insert into test values (mysequence.nextval, mysequence.currval ) ; have the same effect as insert into test values (mysequence.currval, mysequence.nextval ) ; where 1. mysequence is create sequence mysequence increment by 1 start with 1 maxvalue 1000 nocycle nocache ; and 2. test is Name Null?Type ID NUMBER(7) ID2NUMBER -- 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).
Re: ORACLE-L Digest -- Volume 2004, Number 001 (Out of Office
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tony Miller 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: IBM Workload Manager (WLM)
Thank you for answering, Babette. Your use of WLM is as I have it in mind. However, a year ago, or so, before I was hired, the Infrastructure department once switched it on, and overall performance dropped drastically. Of course they did something wrong. But, nothing was logged/documented or whatsoever, and now anyone is scared by the unknown. That's why I like to hear a 'successtory' so I can convince them that we at least should try it again in better controlled conditions, and see what WLM can/can't do for us. A failure story is as welcome, of course. I may have to look for another option then. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 06:04 30-12-03 -0800, you wrote: We are using Oracle on OS/390 and WLM. If you are using AIX instead of MVS you will have a different flavour of WLM. Basically each of our databases on the mainframe runs within a service (think of services on Windows NT). Each service is associated with a WLM class. Originally, we capped each class. This gave lousy performance. Then we decided to change priorities so all classes can compete equally with legacy applications and raised the cap on the machine itself. This has helped a lot. It is the Performance Group that does all the configuration of WLM. On more than one occasion they misstated the configuration to us, when we asked how it was configured. It only kicks in when there is a resource shortage. If you are using less resources than on the machine, WLM does nothing. It is when everything is requesting more resources than available in total, that resource allocation comes into effect. - Babette -Original Message- Sent: 2003-12-29 4:14 AM To: Multiple recipients of list ORACLE-L Hi List, Does anyone have experience in using IBM's Workload Manager together with Oracle? I'm with a consulting client, where server-consolidation is intended. This involves appr. 180 Oracle databases. Some of them 1 instance/1 server, max. is now 22 instances/server. appr. No OPS is used. Versions: 7.3.4, 8.1.x. 60 servers are used now. Goal is to reduce the # of servers with 40-60%. Replacement of the server farm by a reduced number of high-end servers is one of the options, but starting with the consolidation process within the current range of servers is considered as well. All databases will be migrated to 8.1.7 before consolidation takes place. HW/OS is RS6000/AIX, both 4.3.3 and 5.2. Oracle 9i is still under investigation. Applications vvary from Peoplesoft to Siebel to tailor-made software. There is an in-house development department, so there are development, test and production databases. Servers have mixed use: I've seen servers running development, test AND production instances, not necessarily of the same application! Storage is EMC. One of the ideas is using IBM's WLM to prevent the instances on 1 server damaging each others performance. Not to slice too small HW among too much instances, but to prevent one instance from grabbing too much recources on the cost of other instances. From IBM's doc's I got the following information: As from maintenance level 8 on AIX 4.3.3, and on 5.2, WLM allows manual assignment of processes to classes. Before this feature classes could only be assigned based on program-name or username, which is not too useful for oracle. Explicit oracle examples are mentioned in the doc. Nice to know, but does this actually work? Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel 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 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: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services
Re: sequence
Sounds like you need to read the docs on sequences, happy new year and happy reading. joe [EMAIL PROTECTED] wrote: Hello list and a happy new year to everyone, why does : insert into test values (mysequence.nextval, mysequence.currval ) ; have the same effect as insert into test values (mysequence.currval, mysequence.nextval ) ; where 1. mysequence is create sequence mysequence increment by 1 start with 1 maxvalue 1000 nocycle nocache ; and 2. test is Name Null?Type ID NUMBER(7) ID2NUMBER -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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: order by
It is for the same reason that 'select empno from emp' without and order by, also returns the same results. Take a look at $ORACLE_HOME/sqlplus/demo/demobld.sql Didn't you ask this same question earlier this week? Jared On Thu, 2004-01-01 at 01:14, [EMAIL PROTECTED] wrote: List can you please explain to me why select empno from emp order by empno ; is the same as select empno from emp order by sqrt(3.14); but not the same as select empno from emp order by dbms_random.value; What does sort by a random value do ? and why isn't dbms_random.value in the documentation for 9i Release 2 ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 03:34 [EMAIL PROTECTED] wrote: Hello list, Can someone please explain to me why the following order by clauses are valid and yield the same results : select empno, deptno from emp order by sqrt (1) ; and select empno, deptno from emp order by sqrt ( 3.14234 ) ; The docs say that in the order by clause you could specify only (a) column names or (b) positional parameters or (c) expressions involving the columns A constant falls under the c) category. It's an expression, which involves anything you want. That said, I fail to see any practical use . -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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 ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 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 -- 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
Re: order by
I was curious to find out why all the other sorts work the same but order by dbms_random.value ( which also returns a number like sqrt (3.14) ) behaves totally different. Any ideas ? Also could you please tell me what does it mean when someone says order by non integer some_constant . An integer constant would refer to the positional parameter but what about a real value which is not an integer ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 01, 2004 21:34 It is for the same reason that 'select empno from emp' without and order by, also returns the same results. Take a look at $ORACLE_HOME/sqlplus/demo/demobld.sql Didn't you ask this same question earlier this week? Jared On Thu, 2004-01-01 at 01:14, [EMAIL PROTECTED] wrote: List can you please explain to me why select empno from emp order by empno ; is the same as select empno from emp order by sqrt(3.14); but not the same as select empno from emp order by dbms_random.value; What does sort by a random value do ? and why isn't dbms_random.value in the documentation for 9i Release 2 ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 03:34 [EMAIL PROTECTED] wrote: Hello list, Can someone please explain to me why the following order by clauses are valid and yield the same results : select empno, deptno from emp order by sqrt (1) ; and select empno, deptno from emp order by sqrt ( 3.14234 ) ; The docs say that in the order by clause you could specify only (a) column names or (b) positional parameters or (c) expressions involving the columns A constant falls under the c) category. It's an expression, which involves anything you want. That said, I fail to see any practical use . -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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 ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
Re: order by
At 10:44 AM 1/1/2004, you wrote: I was curious to find out why all the other sorts work the same but order by dbms_random.value ( which also returns a number like sqrt (3.14) ) behaves totally different. Any ideas ? When you order by dbms_random, Oracle will generate a random number for each row and sort on those random numbers. When you order by a constant real value, Oracle has no way to order the results, so they will be returned in whatever order they are retrieved from the database. Also could you please tell me what does it mean when someone says order by non integer some_constant . An integer constant would refer to the positional parameter but what about a real value which is not an integer ? Aside from SQL brain-teasers, I cannot imagine a practical use for ORDER BY constant real number. Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Justin Cave 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: sequence
Joe you are absolutely right
From the sql reference : Where to use currval and nextval If
any of these locations contains references to both CURRVAL and NEXTVAL, then
Oracle increments the sequence and returns the same value for both CURRVAL
and
NEXTVAL.
Sorry for the rtfm question. I am really lucky to learn from you all.
Thanks list and a happy new year to you too.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, January 01, 2004 18:54
Sounds like you need to read the docs on sequences, happy new year and
happy reading.
joe
[EMAIL PROTECTED] wrote:
Hello list and a happy new year to everyone, why does :
insert into test values (mysequence.nextval, mysequence.currval ) ;
have the same effect as
insert into test values (mysequence.currval, mysequence.nextval ) ;
where
1. mysequence is
create sequence mysequence increment by 1 start with 1 maxvalue 1000
nocycle nocache ;
and
2. test is
Name Null?Type
ID NUMBER(7)
ID2NUMBER
--
Joseph S Testa
Chief Technology Officer
Data Management Consulting
614-791-9000
It's all about the CACHE
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joe Testa
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 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: order by
Thanks Justin - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 02, 2004 00:39 At 10:44 AM 1/1/2004, you wrote: I was curious to find out why all the other sorts work the same but order by dbms_random.value ( which also returns a number like sqrt (3.14) ) behaves totally different. Any ideas ? When you order by dbms_random, Oracle will generate a random number for each row and sort on those random numbers. When you order by a constant real value, Oracle has no way to order the results, so they will be returned in whatever order they are retrieved from the database. Also could you please tell me what does it mean when someone says order by non integer some_constant . An integer constant would refer to the positional parameter but what about a real value which is not an integer ? Aside from SQL brain-teasers, I cannot imagine a practical use for ORDER BY constant real number. Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Justin Cave 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 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
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 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 -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
