Re: ORACLE-L Digest -- Volume 2004, Number 005 (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).
Diff. execution plans
Hello to everyone Info: - oracle 9.0.1.4 64bit on Solaris 8 - Sun Fire 15000 Here is the query select * from glcomponents where (glorder = 1 and compvalue in ( select glcomp02 from chartofaccounts where glaccount like '01-_-__-__-__-___-___%' and (disabled is null or disabled = 'N' ) and orgid = 'KNPC' ) ); When I execute this query within SQL*Plus, it is finished for less than secon. This is execution plan Elapsed: 00:00:04.01 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=617 Card=374 Bytes=4 3010) 10 HASH JOIN (SEMI) (Cost=617 Card=374 Bytes=43010) 21 TABLE ACCESS (FULL) OF 'GLCOMPONENTS' (Cost=16 Card=374 Bytes=26928) 31 TABLE ACCESS (FULL) OF 'CHARTOFACCOUNTS' (Cost=596 Card= 19589 Bytes=842327) When that query goes trough JDBC Thin Client, this is the execution plan which takes 4 minutes!!! (output from Ent. Manager) 5 SELECT STATEMENT 4 NESTED LOOPS [SEMI] 1 MAXIMO.GLCOMPONENTS TABLE ACCESS [FULL] 3 MAXIMO.CHARTOFACCOUNTS TABLE ACCESS [BY INDEX ROWID] 2 MAXIMO.COA_NDX1 INDEX [RANGE SCAN] Why do we see different exec. plans - one for SQL Plus (and Sql Navigator, also) and another for JDBC connection? Any hints, please? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Barac 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: freebie. Summarize Oracle Listener logs
Steve,
I have an awk script which does something similar - see code and example below
However I can put your file on my site where I have already stored your Perl script
for getting the DDL out of an export file ( www.hcresources.co.uk) if you wish.
Cheers
John
Service :Host :User :tcp:ip address
===
WMBT_DEV:CHALK :PARKR :tcp:10.33.33.215 13
WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.38 11
WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.43 1734
echo Service :Host :User :tcp:ip address
echo ===
grep CONNECT b2tperf.log |\
awk -F= '{print $3 :1: $6 :2: $7 :3: $9 :4: $10 :5: $11}' |\
sed 's/).*:1:/:/' | \
sed 's/).*:2:/:/' | \
sed 's/).*:3:/:/' | \
sed 's/).*:4:/:/' | \
sed 's/).*:5:/:/' | \
sed 's/).*$//' | \
awk -F: '{printf(%-8.8s:%-16.16s:%-16.16s:%-3.3s:%-10.15s\n, \
$1,$2,$3,$4,$5)}' | sort /tmp/j.lis
grep -v PROGRAM /tmp/j.lis /tmp/j1.lis
cat /tmp/j1.lis | awk '{if (data[$0]++ == 0)lines[++count] = $0}\
END{for (i = 1; i = count; i++)print lines[i],\tdata[lines[i]]}'\
|sort -n +4
rm /tmp/j.lis
rm /tmp/j1.lis
-Original Message-
Sent: 05 January 2004 03:29
To: Multiple recipients of list ORACLE-L
I wanted to summarize our listener logs to see who's connecting with what tools. I
checked google and didn't see anything, so I decided to write one. It may be useful to
somebody else.
It summarizes the lines of a listener log. the output is similar to
Ora Listener Log Summary V0.5
Oracle SID: ODP Log File: TSOORDDB.listener.log
Listener Started at: 06-DEC-2003 07:53:26
User Host Program Total
PA1DBO TSOORA49 DMSERVER_V4.EXE 6,594
PW1DBO TSOORA50 DLLHOST.EXE 1,514
IWAM_TSOORA50TSOORA50 DLLHOST.EXE 600
PW2DBO TSOORA56 DLLHOST.EXE 362
ITMDBO TSOORA52 SQLPLUS.EXE 166
SYSTEM TSOORA56 MAPS_BIZ 88
PW3DBO TSOORA57 TD2000.EXE 72
ASPNET TSOORA56 ASPNET_WP.EXE 57
ORACLE __JDBC__41
ARCDBO SATSAP09 SQLPLUS.EXE12
2KNUPPS SKNUPP-1 TOAD.EXE7
PERRYSM SAIT02547SQLPLUSW.EXE6
2RILEYJ GLEFIO01210 TOAD.EXE4
2MASHUA GLEFIO01262 TOAD.EXE3
PA1DBO TSOORA49 DMFILESCAN.EXE 3
PA1DBO TSOORA49 DMCLEAN.EXE 3
LDQDBO TSOORA24 JREW.EXE2
2SHURTN GLEFIO01261 TOAD.EXE1
PW3DBO TSOORA57 DBDBOIN.EXE 1
PA1DBO TSOORA49 SQLPLUSW.EXE1
ODDDBO TSOORD08 EXP.EXE 1
2KNUPPS SKNUPP-1 SQLPLUSW.EXE1
PW2DBO TSOORA56 SQLPLUSW.EXE1
notes:
It sorts in descending order by the connect attempts.
all connect lines are counted. It doesn't take into account if the connection was made
- only that it was attempted. seperating the failed connections may be a future
enhancement.
I just wrote it (and some cutting/pasting from the net), so it hasn't been burned in
yet.
It will process 100 meg of logs in few minutes.
It was written for windows only. sorry, I work in a MicroSlop env...
I have a cmd file that I use to run it so it can process our current logs or older
stuff.
Rather than pasting both of them inline, email me and I'll send them out.
if you want it bad :), email me at work mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]
otherwise use [EMAIL PROTECTED] and I'll get to it when I get home.
if somebody optimizes it, cleans it up,finds a flaw..., let me know so I can update it.
Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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: freebie. Summarize Oracle Listener logs
Sorry - meant to send just to Steve
Please ignore
-Original Message-
Hallas, John, Tech Dev
Sent: 05 January 2004 11:19
To: Multiple recipients of list ORACLE-L
Steve,
I have an awk script which does something similar - see code and example below
However I can put your file on my site where I have already stored your Perl script
for getting the DDL out of an export file ( www.hcresources.co.uk) if you wish.
Cheers
John
Service :Host :User :tcp:ip address
===
WMBT_DEV:CHALK :PARKR :tcp:10.33.33.215 13
WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.38 11
WMBT_DEV:__jdbc__ :oracle :tcp:10.33.15.43 1734
echo Service :Host :User :tcp:ip address
echo ===
grep CONNECT b2tperf.log |\
awk -F= '{print $3 :1: $6 :2: $7 :3: $9 :4: $10 :5: $11}' |\
sed 's/).*:1:/:/' | \
sed 's/).*:2:/:/' | \
sed 's/).*:3:/:/' | \
sed 's/).*:4:/:/' | \
sed 's/).*:5:/:/' | \
sed 's/).*$//' | \
awk -F: '{printf(%-8.8s:%-16.16s:%-16.16s:%-3.3s:%-10.15s\n, \
$1,$2,$3,$4,$5)}' | sort /tmp/j.lis
grep -v PROGRAM /tmp/j.lis /tmp/j1.lis
cat /tmp/j1.lis | awk '{if (data[$0]++ == 0)lines[++count] = $0}\
END{for (i = 1; i = count; i++)print lines[i],\tdata[lines[i]]}'\
|sort -n +4
rm /tmp/j.lis
rm /tmp/j1.lis
-Original Message-
Sent: 05 January 2004 03:29
To: Multiple recipients of list ORACLE-L
I wanted to summarize our listener logs to see who's connecting with what tools. I
checked google and didn't see anything, so I decided to write one. It may be useful to
somebody else.
It summarizes the lines of a listener log. the output is similar to
Ora Listener Log Summary V0.5
Oracle SID: ODP Log File: TSOORDDB.listener.log
Listener Started at: 06-DEC-2003 07:53:26
User Host Program Total
PA1DBO TSOORA49 DMSERVER_V4.EXE 6,594
PW1DBO TSOORA50 DLLHOST.EXE 1,514
IWAM_TSOORA50TSOORA50 DLLHOST.EXE 600
PW2DBO TSOORA56 DLLHOST.EXE 362
ITMDBO TSOORA52 SQLPLUS.EXE 166
SYSTEM TSOORA56 MAPS_BIZ 88
PW3DBO TSOORA57 TD2000.EXE 72
ASPNET TSOORA56 ASPNET_WP.EXE 57
ORACLE __JDBC__41
ARCDBO SATSAP09 SQLPLUS.EXE12
2KNUPPS SKNUPP-1 TOAD.EXE7
PERRYSM SAIT02547SQLPLUSW.EXE6
2RILEYJ GLEFIO01210 TOAD.EXE4
2MASHUA GLEFIO01262 TOAD.EXE3
PA1DBO TSOORA49 DMFILESCAN.EXE 3
PA1DBO TSOORA49 DMCLEAN.EXE 3
LDQDBO TSOORA24 JREW.EXE2
2SHURTN GLEFIO01261 TOAD.EXE1
PW3DBO TSOORA57 DBDBOIN.EXE 1
PA1DBO TSOORA49 SQLPLUSW.EXE1
ODDDBO TSOORD08 EXP.EXE 1
2KNUPPS SKNUPP-1 SQLPLUSW.EXE1
PW2DBO TSOORA56 SQLPLUSW.EXE1
notes:
It sorts in descending order by the connect attempts.
all connect lines are counted. It doesn't take into account if the connection was made
- only that it was attempted. seperating the failed connections may be a future
enhancement.
I just wrote it (and some cutting/pasting from the net), so it hasn't been burned in
yet.
It will process 100 meg of logs in few minutes.
It was written for windows only. sorry, I work in a MicroSlop env...
I have a cmd file that I use to run it so it can process our current logs or older
stuff.
Rather than pasting both of them inline, email me and I'll send them out.
if you want it bad :), email me at work mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]
otherwise use [EMAIL PROTECTED] and I'll get to it when I get home.
if somebody optimizes it, cleans it up,finds a flaw..., let me know so I can update it.
Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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
RMAN options (was: Deleting database)
Title: Re: Deleting database Yechiel, I am letting RMAN do that for me. I have several scripts that help me accomplish this. The first one does a full exportof production. This logical backup is just in case that I need to recreate my production database from scratch. The second one (using RMAN) resyncs and backs up production plus archivelogs and right after that it also backs up my recovery catalog database. The third one (which I only run once a week), shuts down test, starts it with nomount option and then dups production into test. This way I have a new test database with fresh data. I realize that test will contain data 'a week old' from that of production. But that is not critical to my environment. As you pointed out, schemas and tablespaces as well as datafile names in test are the same as in production. Oracles' site suggests to use 'REDUNDANCY TO' a value greater than 1. I see that some other people use the 'RETENTION WINDOW TO. I notice that the REDUNDANCY TO parameter does not show up after Ive set RETENTION WINDOW TO. There is also the option to 'AUTOBACKUP CONTROLFILE'. The question I have is: What option(s) or (combination of them for that matter) do I need to useto be able to do apoint-in-time recovery? Wouldntit beenough to just backup plus archivelogs? As always, grateful for your input! Julio -Original Message- From: [EMAIL PROTECTED]on behalf ofYechiel Adar Sent: Sun 1/4/2004 5:34 AM To: Multiple recipients of list ORACLE-L Cc: Subject: Re: Deleting database I think that if you use exactly the same file names and db structure in both of your database, you can simply ( if it is possible) bring down the production database and copy the files to the test database. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 3:39 PM Carel-Jan, Thanks for your insight in the difference between export/import and copying databases. Two factors had me initially thinking of doing export/import: 1) The tables in production are not big and 2) tables are not subject to heavy changes. As it was pointed out before and although I am not in favor of analyzing stats in a regular basis, the need for analyzing statistics will be determined by the circumstances of one's environment. My test database is more like a test to my logical backups and to have an instance for me to study the tables of this application. Thanks Branimir, Ron, and Vaidya too for your ideas. Now I have a broader view of the various ways to delete a database. Regards, Julio Cesar Quijada-Reina Programmer Analyst Computer Services at Alfred State College -Original Message- Carel-Jan Engel Sent: Tuesday, December 30, 2003 3:39 PM To: Multiple recipients of list ORACLE-L As far as I can understand your question you are copying your production environment to test. So, test should be a copy, and not an export/import logical represantation of prod. Otherwise your tables/indexes will be reorganized every time you create the new test database. This means re-analyzing statistics (see another HOT topic today), and ending up with a non-represntative version of your production database, at least from the SQL-tuning/optimizing point of view. So, I would suggest to take rman, or the old-fashioned well working alter tablespace begein/end backup scenario, and clone your production database. This will give you the physical copy. I've done this many times on unix flavours, never on M$ OS's I like to keep it that way ;-). I guess you might find some nice articles about this on metalink, searching with the keywords clone database. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 12:14 30-12-03 -0800, you wrote: That's right - you would have to drop all schema owners. In my opinion it is simpler and easier task to automate dropping of all owners followed by one full import compared to task of automating database deletions followed by database creations then doing full import in very last step. DOS environment offers weak (better word is miserable) error handling, so unless you resort to non M$ scripting languages to do this task for you I believe you'd be better off to stick with simpler of two approaches. Another reason to try avoiding tearing/re-creating databases lies in the fact that when you create new database on WinXYZ platform, newly created data files are always fragmented at file level (which may not be overly fact important for your test database but it is an ugly site to look at nevertheless). Branimir Branimir, Correct me if I am wrong, but if I used your approach of dropping schema owner then if I have 25 schemas on my test db, I would have to drop ALL of them? I would think that
RE: stress testing
Jared, Are you talking about yapppack? I've been using that for a while (nice display. Though like statspack it is system wide so I usually just look for high level stuff and changes). Not aware of a patch though. With most peoplesoft applications I have seen, the bottlenecks aren't database related, though I still need to get all appropriate data. That means application server, OS (NTfor app server, Sun for Oracle), web server,... stuff too. I'm still trying to find what numbers the tool itself gathers, and if/how it analyzes the stuff. In the meantime, I've been reviewing some of the papers on orapub (i.e. Ratio Modeling, Predicting Computing System Capacity and Throughput). Thanks. Henry -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]Sent: Friday, January 02, 2004 2:19 PMTo: Multiple recipients of list ORACLE-LSubject: Re: stress testingAs the ultimate indicator of performance is response time, you might like to investigate YAPP at http://www.miracleas.dk/. The data generated gives a good indicator of response time from a database perspective. If you use it, ask me for the patch. Jared "Poras, Henry R." [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/02/2004 10:54 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:stress testingWe are planning on running some stress tests on a PeopleSoft/Oracle/Solarissystem starting next week (using LoadRunner). I have never gone through aformalized stress test before (most of my stress is brought about informally).So far I am planning to gather statspack information, and periodically getvmstat from the OS. Is there anything else that I should collect? Thanks for thehelp.Henry-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Poras, Henry R.INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: stress testing
John, Thanks for the tip. I've used sar and vmstat, but not in enough depth to have any preferences. So far I don't have permissions for sar at this site, but I should be able to get that. Henry -Original Message- John Kanagaraj Sent: Friday, January 02, 2004 2:29 PM To: Multiple recipients of list ORACLE-L Henry, Sar is a better tool than vmstat/iostat as it collects a broad range of information. Specifically, sar -q should show up CPU queueing and swapping, and sar -v will show up file/process table overflow issues that may occur during stress testing. IMHO, sar is quite underutilized ( had a paper on this last IOUG, but couldn't go and present it :( John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Poras, Henry R. [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 10:54 AM To: Multiple recipients of list ORACLE-L Subject: stress testing We are planning on running some stress tests on a PeopleSoft/Oracle/Solaris system starting next week (using LoadRunner). I have never gone through a formalized stress test before (most of my stress is brought about informally). So far I am planning to gather statspack information, and periodically get vmstat from the OS. Is there anything else that I should collect? Thanks for the help. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Poras, Henry R. 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Poras, Henry R. 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).
test - please ignore
Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
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 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
rewrite group by query
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).
Re: Job question
I can answer that for you, as I had a discussion with them 2+ years ago. 2 reasons: * They don't pay nearly enough for a senior DBA. The job requirement is really for a junior, and the pay is probably OK for that position. * They lost 50% of their business last year, and unless they do something innovative, or get really lucky, they won't recover from it. The person they are replacing is a former co-worker: he retired and is now in Hawaii. :) Jared On Sun, 2004-01-04 at 09:54, Don wrote: Metro One Telecommunications (Beaverton, Oregon) keeps advertising for an Oracle DBA. Any idea what is going on there that they can't seem to hang on to folks? I see that their stock price has has dropped to almost worthless. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Don 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: stress testing
The patch I refer to is one I made that didn't make it into the most recent version of yapppack. YP uses an array as internal storage, and walks through it with a for i in 1..n loop. Since arrays are sparsely populated there is a fair chance of hitting an array element that does not exist. The patch consists of rewriting the loop with array.first/next/last in a while loop to avoid the problem. Yes, it is high level, but it can pinpoint time periods that you may want to investigate. Jared On Mon, 2004-01-05 at 06:19, Poras, Henry R. wrote: Jared, Are you talking about yapppack? I've been using that for a while (nice display. Though like statspack it is system wide so I usually just look for high level stuff and changes). Not aware of a patch though. With most peoplesoft applications I have seen, the bottlenecks aren't database related, though I still need to get all appropriate data. That means application server, OS (NT for app server, Sun for Oracle), web server, ... stuff too. I'm still trying to find what numbers the tool itself gathers, and if/how it analyzes the stuff. In the meantime, I've been reviewing some of the papers on orapub (i.e. Ratio Modeling, Predicting Computing System Capacity and Throughput). Thanks. Henry -Original Message- [EMAIL PROTECTED] Sent: Friday, January 02, 2004 2:19 PM To: Multiple recipients of list ORACLE-L As the ultimate indicator of performance is response time, you might like to investigate YAPP at http://www.miracleas.dk/. The data generated gives a good indicator of response time from a database perspective. If you use it, ask me for the patch. Jared Poras, Henry R. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/02/2004 10:54 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:stress testing We are planning on running some stress tests on a PeopleSoft/Oracle/Solaris system starting next week (using LoadRunner). I have never gone through a formalized stress test before (most of my stress is brought about informally). So far I am planning to gather statspack information, and periodically get vmstat from the OS. Is there anything else that I should collect? Thanks for the help. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Poras, Henry R. 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
In-line... 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, January 05, 2004 8:59 AM 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? [Cary Millsap] The event most often associated with COMMIT processing that is attributed to cursor #0 is 'log file sync'. 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. [Cary Millsap] Oracle Forms and one of Oracle's report writers (I forget the name) trigger this bug). 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? [Cary Millsap] I'm very curious, too. I don't know the answer. Can you produce a minimal test case that reproduces the behavior? 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 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
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: Bug with automatic undo management?
Ryan, I agree with OTS. If you had dropped the rollback segment(s) before creating the object then you'd probably never have seen the error. I have found that SCN's do get cached by your session and sometimes don't get updated correctly. Therefore even though you had switched to undo management before creating the object it is possible that the block header on the object has an scn that predated your change. I don't know if it's delayed block cleanout or delayed session cleanout, but one of them is definitely the culprit. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, December 31, 2003 10:29 AM To: Multiple recipients of list ORACLE-L I have a TAR open on this and Im arguing with the Oracle tech support guy. Here is what happened. We upgraded an instance to 9i. Switched to automatic undo management. Set our undo parameters to point to a newly created undo tablespace. 1. took our old rollback tablespace(with rollback segments in it) offline. 2. I created some new objects. Fine. 3. Then I started creating indexes and doing selects. I would periodically get the following error: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: 'path/rbs_01.dbf' 4. This is becaus that is the old rollback tablespace that was taken off line and is NOT indicated in the undo parameter as the undo tablespace. 5. Oracle support said the following. 'Most likely what happened is that when you went to create the index it encountered some information in the table in one of the block headers that needed to be retrieved/verified from the rollback segment due to delayed block cleanout. If we see that the rollback segment still exists we try to access it. (It doesn't matter whether we are using auto ot manual at this point.) If we can't access it then we throw an error. If we see that the rollback segment has been dropped then we know for sure that the information in the block header is old because we never drop rollback segments until all active transactions have completed.' 6. Not possible in my opinion. Since the object in question was created AFTER this rollback segment was taken offline. 7. We dropped the old rollback segment and it works fine now. Is this a bug? -- 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: Goulet, Dick 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).
XP SP2 beta is out
See www.arstechnica.com for more info. Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J 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: stress testing
Good catch on the array. I never noticed that. Henry -Original Message- Jared Still Sent: Monday, January 05, 2004 10:59 AM To: Multiple recipients of list ORACLE-L The patch I refer to is one I made that didn't make it into the most recent version of yapppack. YP uses an array as internal storage, and walks through it with a for i in 1..n loop. Since arrays are sparsely populated there is a fair chance of hitting an array element that does not exist. The patch consists of rewriting the loop with array.first/next/last in a while loop to avoid the problem. Yes, it is high level, but it can pinpoint time periods that you may want to investigate. Jared On Mon, 2004-01-05 at 06:19, Poras, Henry R. wrote: Jared, Are you talking about yapppack? I've been using that for a while (nice display. Though like statspack it is system wide so I usually just look for high level stuff and changes). Not aware of a patch though. With most peoplesoft applications I have seen, the bottlenecks aren't database related, though I still need to get all appropriate data. That means application server, OS (NT for app server, Sun for Oracle), web server, ... stuff too. I'm still trying to find what numbers the tool itself gathers, and if/how it analyzes the stuff. In the meantime, I've been reviewing some of the papers on orapub (i.e. Ratio Modeling, Predicting Computing System Capacity and Throughput). Thanks. Henry -Original Message- [EMAIL PROTECTED] Sent: Friday, January 02, 2004 2:19 PM To: Multiple recipients of list ORACLE-L As the ultimate indicator of performance is response time, you might like to investigate YAPP at http://www.miracleas.dk/. The data generated gives a good indicator of response time from a database perspective. If you use it, ask me for the patch. Jared Poras, Henry R. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/02/2004 10:54 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:stress testing We are planning on running some stress tests on a PeopleSoft/Oracle/Solaris system starting next week (using LoadRunner). I have never gone through a formalized stress test before (most of my stress is brought about informally). So far I am planning to gather statspack information, and periodically get vmstat from the OS. Is there anything else that I should collect? Thanks for the help. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Poras, Henry R. 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: Poras, Henry R. 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).
Upgrade your OCP
Awhile back on this list someone asked whether someone with an Oracle 7 or 8 OCP could upgrade their certification directly to 9i. I don't recall that question receiving an answer. If you are interested, the Jan/Feb issue of ORACLE magazine (otn.oracle.com/oraclemagazine) has an article named Upgrading Your OCP that explains how you can do this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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).
Upgrade
Dear List, I want to upgrade from 8.1.5 to 8.1.7 on windows 2000 what's the safest way to do this upgrade. If any body knows any url or have any kind of document it would be very helpful. Thanks happy new year to all of you. Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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: Upgrade
Look on the Documentation CD for the upgrade manual. Safest way would be to 1) get a Linux box, 2) do a FULL DB export. 3) Create new DB on the Linux server Import the data there. 4) Use the old NT box as a boat anchor or upgrade it to Linux. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, January 05, 2004 12:24 PM To: Multiple recipients of list ORACLE-L Dear List, I want to upgrade from 8.1.5 to 8.1.7 on windows 2000 what's the safest way to do this upgrade. If any body knows any url or have any kind of document it would be very helpful. Thanks happy new year to all of you. Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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: Goulet, Dick 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: **BLOCKED-BY-IHATESPAM** Upgrade
Alavi, It amazes me Please read the Oracle8i Upgrade manual and you will know all you need to know. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 1/5/2004 11:24 AM Dear List, I want to upgrade from 8.1.5 to 8.1.7 on windows 2000 what's the safest way to do this upgrade. If any body knows any url or have any kind of document it would be very helpful. Thanks happy new year to all of you. Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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: Freeman Robert - IL 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: Upgrade
patch guide that comes with the patch set, for extended reading check out the migration guide, oracle docs or on technet.oracle.com joe Hamid Alavi wrote: Dear List, I want to upgrade from 8.1.5 to 8.1.7 on windows 2000 what's the safest way to do this upgrade. If any body knows any url or have any kind of document it would be very helpful. Thanks happy new year to all of you. Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- 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: sql trace - forward attribution
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. --- 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=
Re: undotbs01.dbf just keeps growing
Jared, It sure is nice to be missed. I'll make sure my secretary calls you about my future vacation plans...:) You've nailed the problem. Autoextend, automatic undo and high undo retention is a recipe for high disk usage. The aum algorithm is such that preference is given to extending over reuse (especially since expire time propogation is a problem). In order to find the length of the longest transaction, reference the v$undostat.maxquerylen value. Beware as there are known bugs with this view, so examine the output carefully to make sure it makes sense. Daniel Fink Jared Still wrote: The data file(s) for your undo tablespace is likely set as autoextend with an unlimited size. Run the attached script to check it. If so, you can use this to put a limit on it: alter database datafile 'your file name' autoextend on next 200m maxsize 2000m; Adjust the numbers for your system. You should probably investigate why it continues to grow so large. I haven't yet converted our production databases to UNDO, having only recently migrated to 9i, so I don't have any useful advice past this. There are others that will be able to offer more for this. ( Dan Fink, where are you? This might even get Kirti to take a break from his book for a few minutes ) HTH Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink 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).
Problem with changing Dedicated Server to Shared Server
Hello everybody I’m trying to change the Oracle 8i Dedicated Server to Shared (I don’t have problems with memory, soI ‘ve configured the large pool well). There are two databases on the same server (NT Server) , when I change one to shared server, the users can’t connect to the other one. If I change both to shared server, the users can’t connect to the second I changed. I proved with Oracle 9i on Windows NT and the same problem exists. How can I resolve this? Regards Mauricio Vélez Do you Yahoo!? Find out what made the Top Yahoo! Searches of 2003
Re: sql trace - forward attribution
Oracle Portal uses session switching as well (and Apps 11i uses Portal...) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 05, 2004 7:49 PM 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. --- 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
RE: rman restore question
If you don't set the redundancey policy to a recovery window of N days, than the record of the backup will be kept indefinitely. You should be able to restore the backups from tape and restore from a previous backup. You may need to do a set until time if there was corruption or another problem. HTH, Ruth -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Joan Hsieh Sent: Tuesday, December 30, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Subject: rman restore question Hi Listers, I have a question about rman restore. Right now, I configured RETENTION POLICY TO REDUNDANCY=1 and deleted the obsolete backupset on the disk after a new rman full backup is done. The old backupset will be backup-ed to tape by system group. In case of the newly backupset on disk is corrupted and need to restore the 2 days old backupset from tape. Is there any way or command to restore the database using a already deleted obsoleted backupset? (from rman catalog point of view) I could find any command and example to restore a obsoleted backupset. Any comments will be appreciated. Many many thanks! Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh 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: Ruth Gramolini 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: Problem with changing Dedicated Server to Shared Server
Try ensuring that they have different port numbers. One port number for the shared servers and a different one for the dedicated server. If they are going to use the same port number for dedicated, then it will be listed in the listener.ora file for reference. If each database is using a shared server, then they will each have different port numbers. Then change the entries in the tns names files or name server to accomodate the change. RWB Reginald W. Bailey IBM Global Services JPMC Account - DCI ETS Database Management Your Friendly Neighborhood DBA oraclemavelgo@ yahoo.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Problem with changing Dedicated Server to Shared Server ity.com 01/05/2004 11:59 AM Please respond to ORACLE-L Hello everybody ?xml:namespace prefix = o ns = urn:schemas-microsoft-com:office:office / I'm trying to change the Oracle 8i Dedicated Server to Shared (I don't have problems with memory, so I 've configured the large pool well). There are two databases on the same server (NT Server) , when I change one to shared server, the users can't connect to the other one. If I change both to shared server, the users can't connect to the second I changed. I proved with Oracle 9i on Windows NT and the same problem exists. How can I resolve this? Regards Mauricio Vélez Do you Yahoo!? Find out what made the Top Yahoo! Searches of 2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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, 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. --- 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:
Re: Price is right
www.bestbookbuys.com everyone should have that link. beware the 'used' books. Make sure they have atleast 1000 reviews with 99% positive. Its easy to create your own reviews. Look at what people say negative. Alot of people complain about silly stuff like 'book came 3 days late' or a page was wrinkled. However, if its 'i never got the book and noone responded' or 'the book was not in stock but noone told me that for 6 weeks', stay away. also beware versions and if your looking at textbooks, beware the 'illegal asian version'. They are 'supposed' to only be sold in asia. They are paperback versions, in black and white, and often have smudged print. They are fine if thats what you want. Same material. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, January 04, 2004 5:14 PM Since this link states currently out of stock I can definitely recommend my favorite online bookshop bookpool.com which has Carry's book for $19.75 brand new! See URL http://www.bookpool.com/.x/tg654o1w56/ss/1?qs=059600527XGo.x=13Go.y=3 for more details. Happy New Year to you all! Faan - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 03, 2004 11:34 PM http://www.halfpricecomputerbooks.com/book/059600527X+ I just noticed the price of Cary/Jeff's new book is FREE. Of course this must be a mistake. But, its funny. There should be no excuses now. ;-) __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Thomas 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: Faan DeSwardt INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql trace - forward attribution
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='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
RE: sql trace - forward attribution
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 :-( 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
ora 01548
Hi. I getting ora-01548 error trying to drop an old undo tablespace under oracle 9204. I have shut down the database and brought it back (as metalink recommended), but I still see some segments created in the old undo tablespace and therefore I can't drop it. What could be the reason, does anyone have an idea? thanks Gene __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gene Gurevich 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: Apps 11.5.9 D.R. Site -- MetaLink Note 216212.1 ??
Looks fairly close. I'll be doing this over the next couple of weeks. If interested,
I'll let you
know how it all turns out.
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan
[EMAIL PROTECTED]
com.sg To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: Re: Apps 11.5.9 D.R. Site --
MetaLink Note 216212.1 ??
.com
01/04/2004 02:29
AM
Please respond to
ORACLE-L
There seems to be Note 216212.1 titled Disaster Recovery and the
E-business Suite
Has anyone used this note ?
{Although I am familiar with 11.5.3, the last time I worked on 11i was more
than two years ago,
so I am not in touch with 11i , although I do manage an 11.0.3 instance now}
Hemant
At 03:34 PM 30-12-03 -0800, you wrote:
I've been charged with bringing up a disaster recovery site, so time to
hit the books again as a lot
has changed since the last time I did this. Looking for resource
recommendations (FM to read, white
papers, etc).
Sticky part of this is it is an Applications 11.5.9 installation. The
database end of it should not
be too difficult (8.1.7.4, soon to be 9.2.0.4), but the applications file
system is modified by the
adpatch utility which adpatch requires a database connection to
function. I can think of 2 ways to
get around this requirement.
1. set the two_task to point to a live test system, and run adpatch force
using the c and g drivers.
The d driver would not need to be run since the changes will come over via
the archive logs.
2. ignore adpatch utility completely and use rsync.
Suggestion, comments?
Thanks,.
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
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
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 :-( 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
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='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
RE: Upgrade
Dick, What kind of a nautical person are you??? the NT box will not even make a good anchor because the sides are flat and it will drag on the bottom during a small wind or current. Ron [EMAIL PROTECTED] 01/05/2004 12:39:36 PM Look on the Documentation CD for the upgrade manual. Safest way would be to 1) get a Linux box, 2) do a FULL DB export. 3) Create new DB on the Linux server Import the data there. 4) Use the old NT box as a boat anchor or upgrade it to Linux. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, January 05, 2004 12:24 PM To: Multiple recipients of list ORACLE-L Dear List, I want to upgrade from 8.1.5 to 8.1.7 on windows 2000 what's the safest way to do this upgrade. If any body knows any url or have any kind of document it would be very helpful. Thanks happy new year to all of you. Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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: Goulet, Dick 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: Ron Rogers 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
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 [EMAIL PROTECTED],Eachsessionw illhaveitsownsidandserail#,buttheyallruninthesameprocess.Basicallytheclients idetellsoracle,thatitwantstoswitchfromsessiontosession 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='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?
Re: stress testing
Hi Tim Tony Jambu here. Saw your posting to Oracle-l with regards to your sp_vmstat.sh script. I am not sure if know but I write a regular hints tips column for Select Journal. I read your article and would like to mention your script and point people to the script. Do you mind if I mention it and also to point them to somewhere where they are able to get a copy of it? Dont want them hassling you and sending emails requesting it. Let me know what you think ta tony At 09:14 AM 03/01/2004 -0800, Tim Gorman wrote: Henry, I use the attached shell script to gather and store VMSTAT information in a custom table within the PERFSTAT schema (i.e. schema belonging to STATSPACK). Allows for some nice reporting over time, rather than anecdotal here-and-there observations. Should work OK on Solaris, HP, and Linux. Be aware: the script expects to use the standard oraenv and dbhome scripts to set up the Oracle environment variables, and expects a hidden file in the $HOME directory of the owner's UNIX account for storing Oracle passwords, and also has optional functionality to email/page in the event of trouble. Hope this helps... -Tim on 1/2/04 11:54 AM, Poras, Henry R. at [EMAIL PROTECTED] wrote: We are planning on running some stress tests on a PeopleSoft/Oracle/Solaris system starting next week (using LoadRunner). I have never gone through a formalized stress test before (most of my stress is brought about informally). So far I am planning to gather statspack information, and periodically get vmstat from the OS. Is there anything else that I should collect? Thanks for the help. Henry _ / |Tony Jambu, DatabaseWeb Consultant /_ _/_ __ / |Wizard Consulting Pty Ltd /(_)/ )(_/ \_/(///(/_)/_( |IOUG's Select Asia-Pacific Tech. Editor \___/ |EMAIL: TJambu @ wizard.cx (REMOVE Spaces from email ) |PHONE: +61-419-TJAMBU(852628) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
oaktable people
Conner McDonald's book just came out and it looks to be pretty good. Any more books in the pipeline? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Convert to Locally-Managed Tablespaces
Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This smalldatabase is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Re: Convert to Locally-Managed Tablespaces
On a gig of data, you could easily export the data and re-import into new locally managed tablespaces. An alternative is to use the dbms_space_admin package to convert DD managed tablespaces to locally managed. This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. The DD data for the existing extents is simply converted to bitmaps. The advantage of getting extent mgt out of the DD should outweigh that IMO. There may be other drawbacks, I haven't started on this project yet. I'm sure someone else on the list can respond with some experiences. For 1 gig of data though, I personally would just go the export/import route. HTH Jared Paula Winkler [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 02:39 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Convert to Locally-Managed Tablespaces Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Re: oaktable people
I've heard that Steven Feuerstein has a new book on the way, though I have been unable to find any reference to it. Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 02:34 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oaktable people Conner McDonald's book just came out and it looks to be pretty good. Any more books in the pipeline? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: oaktable people
when did fuerstein join oaktable? http://www.oaktable.net/pageServer.jsp?body=members.jsp btw, are the only Americans members of Oracle or former members of Oracle? - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Monday, January 05, 2004 6:49 PM Subject: Re: oaktable people I've heard that Steven Feuerstein has a new book on the way, though I have been unable to find any reference to it. Jared "Ryan" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 02:34 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oaktable peopleConner McDonald's book just came out and it looks to be pretty good. Anymore books in the pipeline?-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: RyanINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: oaktable people
oh yeah. James Morle sent me an email today and said he may expand his really good book to 2 volumes... - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Monday, January 05, 2004 6:49 PM Subject: Re: oaktable people I've heard that Steven Feuerstein has a new book on the way, though I have been unable to find any reference to it. Jared "Ryan" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 02:34 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oaktable peopleConner McDonald's book just came out and it looks to be pretty good. Anymore books in the pipeline?-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: RyanINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: oaktable people
That would be nice. It's already indispensible. Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 04:19 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: oaktable people oh yeah. James Morle sent me an email today and said he may expand his really good book to 2 volumes... - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Monday, January 05, 2004 6:49 PM Subject: Re: oaktable people I've heard that Steven Feuerstein has a new book on the way, though I have been unable to find any reference to it. Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 02:34 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oaktable people Conner McDonald's book just came out and it looks to be pretty good. Any more books in the pipeline? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: oaktable people
He didn't, but nonetheless I thought it was something that you and others might be interested in. Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 04:19 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: oaktable people when did fuerstein join oaktable? http://www.oaktable.net/pageServer.jsp?body=members.jsp btw, are the only Americans members of Oracle or former members of Oracle? - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Monday, January 05, 2004 6:49 PM Subject: Re: oaktable people I've heard that Steven Feuerstein has a new book on the way, though I have been unable to find any reference to it. Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 02:34 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oaktable people Conner McDonald's book just came out and it looks to be pretty good. Any more books in the pipeline? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
A small addition, PK/UQ constraints have to be disabled in order to drop their indexes (and index definitions should be exported before disabling constraints, because implicitly created indexes will be automatically dropped if a constraint is disabled (without keep indexes option)). Tanel. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql trace - forward attribution
They write all to the same trace file. So there should be different sid.serial# combinations. -Original Message- Boris Dali Sent: Monday, January 05, 2004 9:49 PM To: Multiple recipients of list ORACLE-L 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='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
HOTSOS Conference
While perusing the HOTSOS site, I noticed that the deadline for the discounted registration for the HOTSOS conferences ends after tomorrow. If you're thinking of going, you may want to check it out. Along those same lines, how many listers will be there? We could get together on Tuesday evening for dinner/drinks if any are interested. Possibly some of you with Dallas connection can recommend a suitable location. Jared -- 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: Convert to Locally-Managed Tablespaces
Hi, Think what benefit can you get via such reorg before doing this reorg. Since only a small database, I won't change it as performance benefit via reorg will be small. As others said, exp/imp is the most easy way, as movetable/rebuild index has trouble sometimes when you have long column/iot table with overflow segment etc. Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 06, 2004 6:39 AM Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao 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: Convert to Locally-Managed Tablespaces
Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Convert to Locally-Managed Tablespaces
I can see you everywhere :D Hi, Think what benefit can you get via such reorg before doing this reorg. Since only a small database, I won't change it as performance benefit via reorg will be small. As others said, exp/imp is the most easy way, as movetable/rebuild index has trouble sometimes when you have long column/iot table with overflow segment etc. Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 06, 2004 6:39 AM Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao 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). Best regards, Fenng [EMAIL PROTECTED] 2004-01-06 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fenng 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: Convert to Locally-Managed Tablespaces
Hi, Tanel: If you have plenty of downtime, everything will be ok. If you want to further limit the downtime, here is some suggestions: 1. I think manual parallel will always be better than oracle parallel. So I always use script, that means, I split the move table nologging script to 10 scripts and let them run concurrently. I call it manual parallel:).So does the index rebuild. 2. To avoid that huge table move again, we can consider using transportable_tablespace feature. I did test on my linux 920 box, but not in production. It did work. Steps like: set tablespace read only; check self constrainted. exp the metadata. edit the metadata dump, replace tablespace with new_tablespacename; drop the tablespace. imp back the metadata back. (The above is from oracle metalink ,but I forget about the noteid). regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 8:49 AM Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao 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: oaktable people
No, I'm an American, a member of the OakTable, and I do not now, nor have I ever worked for Oracle. Also, I'm pretty sure the same is true of Mark Powell. Also, WRT to James Morle, he's got a new white paper out, Brewing Becnchmarks. It's available at http://www.oaktable.net/ -Mark -Original Message- From: Ryan [mailto:[EMAIL PROTECTED] Sent: Mon 1/5/2004 7:19 PM To: Multiple recipients of list ORACLE-L Cc: Subject:Re: oaktable people when did fuerstein join oaktable? http://www.oaktable.net/pageServer.jsp?body=members.jsp btw, are the only Americans members of Oracle or former members of Oracle? - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Monday, January 05, 2004 6:49 PM I've heard that Steven Feuerstein has a new book on the way, though I have been unable to find any reference to it. Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/05/2004 02:34 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oaktable people Conner McDonald's book just came out and it looks to be pretty good. Any more books in the pipeline? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HOTSOS Conference
I will be there. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED] Sent: Mon 1/5/2004 8:59 PM To: Multiple recipients of list ORACLE-L Cc: Subject:HOTSOS Conference While perusing the HOTSOS site, I noticed that the deadline for the discounted registration for the HOTSOS conferences ends after tomorrow. If you're thinking of going, you may want to check it out. Along those same lines, how many listers will be there? We could get together on Tuesday evening for dinner/drinks if any are interested. Possibly some of you with Dallas connection can recommend a suitable location. Jared -- 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: Bobak, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: HOTSOS Conference
I'll be there as well, listening, speaking, also attending the 1-day Steve Adams class. A list evening/dinner would be great. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 3:59 AM While perusing the HOTSOS site, I noticed that the deadline for the discounted registration for the HOTSOS conferences ends after tomorrow. If you're thinking of going, you may want to check it out. Along those same lines, how many listers will be there? We could get together on Tuesday evening for dinner/drinks if any are interested. Possibly some of you with Dallas connection can recommend a suitable location. Jared -- 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
