RE: RAC recomended books
Vladimir, The official manuals do a good job. Besides from that The O'Reilly Oracle Parallel Processing book is quite OK, but it repeats a lot of the info in the manuals. There happens to be a book called: Tru64 Unix Oracle9i CLuster quick reference (Digital Press) written by Tim Donar. It does a good job on the Tru64 specifics: Advfs, LSM and SAN part. Check out James Morle's Scaling Oracle8i: although 8i, it also contains good OPS info applicable to 9i too. Also Steve Adam's Oracle Internals has a very good (very 'dense', hard to read: it reads like there's enough info to fill a book crammed in 1 chapter..) part on OPS (7-8), but a lot is still very applicable. Again: don't miss the manuals.. regards, Mario -Original Message-From: Vladimir Barac [mailto:[EMAIL PROTECTED]]Sent: donderdag 20 februari 2003 15:04To: Multiple recipients of list ORACLE-LSubject: RAC recomended books What books are "recomended reading" for RAC? Especially Tru64 based RAC? Beside paper books, are there any good web pages (beside metalink, of course) that are dealing with RAC? Thanks, Vladimir Barac
RE: Know 1 database, know them all?
Funny..the last time I spoke with the guys in Redmond, they told me: You don't need that... And OK, I must admit there are a few undocumented wait thingies (viewable with dbcc perfmon according to my old notes..), but they are too cryptic too understand.. I like your R=S+W by the way..You should write a SQLserver paper on that! Mario -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent: dinsdag 18 februari 2003 21:19To: Multiple recipients of list ORACLE-LSubject: Re: Know 1 database, know them all?Not quite true, as far as I know There's wait stuff in there, although not enough to my taste. There's cpu in there, and the start and stop time, which makes it possible to at least make a crude R = S + W, where the difficult part is breaking down the W into meaningful stuff. A long way to go, but I think they're aware of it. The guys from SQL Server Development I spoke to about it were very interested in the method and liked the whole idea.But don't forget that I'm always wrong.MogensBroodbakker, Mario wrote: Mogens, the only problem with your statement about 'oracle myths & king of the new world' is that the only way of looking at SQLServer performance is probably looking at ratio's: there are no wait statistics, there is one(1) latch wait counter though! for the complete system :( (apart from some other almost useless perfmon counters, taht is..) regards, Mario -Original Message- Sent: maandag 17 februari 2003 23:29 To: Multiple recipients of list ORACLE-L I see it from a slightly different (and probably wrong) angel, at least regarding the performance of things and databases: If you've worked with Oracle databases for some time (and have real experience), and know about the myths and their anti-thesis (use the wait interface instead of the ¤&#% ratio crap, know about RAID-5, don't have too many indexes, concentrate on LIO instead of PIO, etc.,etc.) you'll do quite fine. As Peter Gram once said to me: It's all about getting a database to perform on a platform. You can take your old presentations regarding Oracle myths and change it into a SQL Server or mySQL presentation, change a few details, and be king in the new world. Mogens Robert Eskridge wrote: Curiously, the basics are common across styles of cooking. You have to learn to coax the flavors out of the fresh ingredients and transform them into the proper texture and finish. Once you've mastered Italian cooking, you may not be a top notch German cook, but you're probably just a recipe or two away from being able to produce a very nice German meal... Databases have a certain similarity. If heading an Oracle project and I was given the choice between two people to work on my project, one having been the lead architect for a top notch product based on Sybase, and the other being an OCP that had worked on lack luster products, it would be hard not to pick the former. F> Following the same logic. if I learn to cook a good Italian dish, then I F> must automatically be an expert in preparing top-class Chinese, German, F> Malay, Hungarian and French cuisine Yeah, right ! F> Ferenc Mantfeld
RE: Know 1 database, know them all?
Mogens, the only problem with your statement about 'oracle myths & king of the new world' is that the only way of looking at SQLServer performance is probably looking at ratio's: there are no wait statistics, there is one(1) latch wait counter though! for the complete system :( (apart from some other almost useless perfmon counters, taht is..) regards, Mario -Original Message- Sent: maandag 17 februari 2003 23:29 To: Multiple recipients of list ORACLE-L I see it from a slightly different (and probably wrong) angel, at least regarding the performance of things and databases: If you've worked with Oracle databases for some time (and have real experience), and know about the myths and their anti-thesis (use the wait interface instead of the ¤&#% ratio crap, know about RAID-5, don't have too many indexes, concentrate on LIO instead of PIO, etc.,etc.) you'll do quite fine. As Peter Gram once said to me: It's all about getting a database to perform on a platform. You can take your old presentations regarding Oracle myths and change it into a SQL Server or mySQL presentation, change a few details, and be king in the new world. Mogens Robert Eskridge wrote: >Curiously, the basics are common across styles of cooking. You have >to learn to coax the flavors out of the fresh ingredients and transform >them into the proper texture and finish. Once you've mastered Italian >cooking, you may not be a top notch German cook, but you're probably >just a recipe or two away from being able to produce a very nice >German meal... > >Databases have a certain similarity. If heading an Oracle project and >I was given the choice between two people to work on my project, one >having been the lead architect for a top notch product based on >Sybase, and the other being an OCP that had worked on lack luster >products, it would be hard not to pick the former. > > > >F> Following the same logic. if I learn to cook a good Italian dish, then I >F> must automatically be an expert in preparing top-class Chinese, German, >F> Malay, Hungarian and French cuisine Yeah, right ! > >F> Ferenc Mantfeld > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Broodbakker, Mario 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: Deep OPS tuning info
Rui, I'm an HP (ex CPQ) collegue of yours, I spend a lot of time optimizing OPS and RAC (with a lot of help from Anjo Kolk, especially when I just started with OPS). Can you detail your problems, can you send me statspack report(s) and explain a little more about the app? Maybe it's better to leave this outside the list, and post again when we found a/some solution(s)? regards, Mario Broodbakker, HP The Netherlands -Original Message- Sent: donderdag 13 februari 2003 17:25 To: Multiple recipients of list ORACLE-L Hi all: I'm tuning an OPS env. and the things are getting quite deep, DLM latch issues and so on (e.g. lots of waits on "dlm resource hash list"). So I was wondering if any of you know of any site or doc. which could help me on this (OPS tuning specific and very deep information). Of course the first steps in the process was to submit the statspack report to oraperf and so on , but right now we are dealing with some issues that surpass the obvious things. So if any you could provide any tip or site to look for a tip I´ll be very glad. thanks in advance Rui Galamba Marreiros Solution Consultant - HP Services, Consulting & Integration Quinta da Fonte , Edificio D. Sancho I, 2780 Porto Salvo, Portugal Telf: +351 214828500 Fax: +351 21 4838431 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MARREIROS,RUI (HP-Portugal,ex1) 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: Broodbakker, Mario 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: Count(*) last 30 seconds
Title: Message That's not so bad: 14644 physical reads in 30 seconds..that's about 500 I/O sec. Depending on your disk layout that's pretty optimal, I think. Mario -Original Message-From: Ramon E. Estevez [mailto:[EMAIL PROTECTED]]Sent: woensdag 12 februari 2003 14:19To: Multiple recipients of list ORACLE-LSubject: RE: Count(*) last 30 seconds Hermant, Sergey The table has 13 columns, the PK is formed for the first 11. There is no deletion nor update, just inserts in the table. I had truncated the tables sometimes testing the procedure that load the rows. This is the result with an auto trace. COUNT(*)-- 1466196 Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=896 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196) Statistics-- 0 recursive calls 0 db block gets 14677 consistent gets 14644 physical reads 0 redo size 386 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: Tuesday, February 11, 2003 10:24 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Count(*) last 30 secondsYou are doing Full-Table-Scans.1. What's the average row length ? How many columns does the table have ?2. How many "consistent gets" does the count(*) cause ? [ie, how many blocks does it actually have to read ?]3. Are all these Physical Reads ? Is the DB_CACHE_SIZE large enough to hold most of theblocks ? What is the query-run-time if you re-run the query immediately again ?HemantAt 08:19 AM 11-02-03 -0800, you wrote: Hi list, I issue a select count(*) from mytable and last 30 seconds. The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space. I consider that time exagerated. The TBS is LMT with a Uniform size of 128 MB. The block size is 8MB, version 9.2.0.1.0 in Windows 2000. Where should I start looking ??? TIA Ramon E. Estevez[EMAIL PROTECTED]809-565-3121 Hemant K ChitaleMy web site page 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 removed from). You may also send the HELP command for other information (like subscribing).
RE: strange behaviour of sequence
Probably you use the (default) 'cache 20' and shut down the database between the
'BREAD4' and 'BREAD21' insert.
Or you use OPS/RAC, which cause each instance to cache this '20' numbers.
This can be avoided by using the 'nocache' option, but then you serialize access to
the sequence number, which can be a very bad idea from the performance perspective..
regards,
Mario
-Original Message-
Sent: woensdag 29 januari 2003 11:34
To: Multiple recipients of list ORACLE-L
Guys,
one of my developers is using sequence to auto-increment the value
of a column while inserting.
he has created a sequence like this.
SQL > create sequence testseq start with 1;
and then uses a INSERT statement as below in a JSP.
insert into testtab values ('BREAD'||testseq.nextval);
after some inserts .when he does SELECT from TESTTAB...he
finds
the values as :
BREAD1
BREAD2
BREAD3
BREAD4
BREAD21
BREAD22
it should increment by 1.but it is not so ?
any hint/clue
Regards,
Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: oraora oraora
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: Broodbakker, Mario
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: Windows 2000 Cluster on oracle
Seema, Yes, there is. On NT4 it was called MS Cluster Service, not so much cluster 'scalability-wise', but it allowed for failover. Oracle supplied some software on top of that: Oracle Fail Safe. If you only want failover, it's sufficient to use those product, you don't need RAC or OPS. It worked OK for many shops: so I'm convinced it's W2K successor(s) will do the job also. I'm exclusively involved with Unix the last few years, so I don't now the exact current status. I do know though, that HP(!) Trucluster, as Mladen suggested, is a (Tru64) Unix product and has nothing to do with W2K. regards, Mario Broodbakker(HP) -Original Message- Sent: dinsdag 28 januari 2003 18:40 To: Multiple recipients of list ORACLE-L Hi I wanted to migrate my database from SUN solaris to WINDOWS 2000 platform.Curetly I am having sun cluster as failover with shared disk. I wanted to setup similar kind of setup with windows 2000. Is any failover option available in Windows2000? Let me know if anyone does such kind of setup earlier ? Thx -Seema _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seema Singh 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: Broodbakker, Mario 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: tim column in trace output
Djordje, It's hsecs from v$timer, this is what the 'db reference' says: V$TIMER This view lists the elapsed time in hundredths of seconds. Time is measured since the beginning of the epoch, which is operating system specific, and wraps around to 0 again whenever the value overflows four bytes (roughly 497 days). regards, Mario -Original Message- Sent: vrijdag 24 januari 2003 13:55 To: Multiple recipients of list ORACLE-L Anybody knows what is the reference point for the timing used in the "tim" column in the trace output, like in: PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=832261739 EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=832261739 Apparently the scale is 100 per second for 8i and 976,562.5 (1,000,000,000/1024) for 9, but I am not quite clear what could be the reference time (the time when counting of tim starts). In different databases I tried it, it is usually few months to a year back. BTW, this column can be used if one needs to find out the exact time when a query from the trace was run. Thanks. Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Djordje Jankovic 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: Broodbakker, Mario 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).
RO enqueue
Hi all, Does anybody know what an RO enqueue is? I just found it on a Linux RAC cluster and have never seen this one before.. Thanks, Mario Broodbakker -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Broodbakker, Mario 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).
Recall: Oracle 9.2.0.2 performance problem
Broodbakker, Mario would like to recall the message, "Oracle 9.2.0.2 performance problem". -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Broodbakker, Mario INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9.2.0.2 performance problem
1708.72 1554 1675 23 > >445919 > >--- -- -- -- -- > -- - > - > > > >total 445922 1748.661708.72 1554 1675 23 > >445919 > > > >Misses in library cache during parse: 1 > >Optimizer goal: CHOOSE > >Parsing user id: 90 (recursive depth: 1) > > > > > >Execution Plan > >-- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 > Card=464215 Byt > > es=32495050) > > > > 10 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) > > 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 > Card=464215 > > Bytes=32495050) > > > >Statistics > >-- > > 0 recursive calls > > 31 db block gets > > 1675 consistent gets > > 1577 physical reads > > 0 redo size > >9012743 bytes sent via SQL*Net to client > > 208363 bytes received via SQL*Net from client > > 29729 SQL*Net roundtrips to/from client > > 0 sorts (memory) > > 1 sorts (disk) > > 445919 rows processed > > = = = = = = = = = = = = = = = = = = = = > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: chao_ping > 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: Juan Miranda > 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: Gogala, Mladen 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: Broodbakker, Mario INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle SPID vs. NT PIDs (was :100% CPU utilization, urgent)
Frank, Your query shows the Thread_id of the oracle foreground thread of the oracle.exe running on your database server. The 'program' column shows the program name of the client program used on the client machine v$session.process would (will) show the NTprocess_id:thread_id pair of the client program (on the client machine if running c/s) or if the client runs on unix, just the process_id of the client program. example: SID PROGRAM PROCESS -- - 12 [EMAIL PROTECTED] (TNS V1-V3)615736 ... 21 sqlplus.exe 780:1068 The (sid=21) sqlplus.exe is an NT version, the sid=12 sqlplus runs on the unix server. Both are connected to a unix server, but the process column does show the NT pid:tid combination, as it does on an NT machine, as I showed in my last mail. This is 'pstat' output on my NT client: pid:30c pri: 8 Hnd: 113 Pf: 1724 Ws: 6796K sqlplus.exe tid pri Ctx Swtch StrtAddrUser Time Kernel Time State 42c 8 484 77E99264 0:00:00.110 0:00:00.360 Wait:LpcReply 6c0 8 4 77E83775 0:00:00.000 0:00:00.000 Wait:UserRequest 5d4 8 2 77E83775 0:00:00.000 0:00:00.000 Wait:DelayExecution Where pid=30c, tid=42c matches the above v$session.process column. I hope this clears it up. regards, Mario Broodbakker -Original Message- Sent: maandag 20 januari 2003 14:50 To: Multiple recipients of list ORACLE-L Mario so how comes, that I am not able to find the corresponding SPID to my NT-processes ??? I tried the following statement : # select substr(a.spid,1,5) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#, machine box, substr(b.username,1,10) username, -- b.server, substr(b.osuser,1,8) os_user, substr(b.program,1,30) program from v$session b, v$process a where b.paddr = a.addr and type='USER' order by spid; ## and I got : PID SID SER# BOX USERNAME OS_USER PROGRAM - - - --- -- - 00111 5810121 networkname xx xxx C:\myexe.exe ### (beware of wordwrap here) If find the process myexe.exe on networkname in the taskmanager. It's PID is : 478 (HEX 1DE). The database is on a separate server in the network. None of the processes, running on the client could pointed to a SPID on the server. ??? > Frank < >-Ursprüngliche Nachricht- >Von: Broodbakker, Mario [mailto:[EMAIL PROTECTED]] >Gesendet am: Montag, 20. Januar 2003 13:59 >An: Multiple recipients of list ORACLE-L >Betreff: RE: 100% CPU utilization, urgent > >Frank, > >I'm pretty sure they do: > >SQL> select spid,program from v$process; > >SPID PROGRAM >- -- > PSEUDO >892 ORACLE.EXE >896 ORACLE.EXE >1044 ORACLE.EXE >528 ORACLE.EXE >616 ORACLE.EXE >792 ORACLE.EXE >300 ORACLE.EXE > >From Pstat: > >pid:6a8 pri: 8 Hnd: 206 Pf: 43673 Ws: 17828K oracle.exe > tid pri Ctx Swtch StrtAddrUser Time Kernel Time State > 424 8 937 77E99264 0:00:00.020 0:00:01.281 Wait:Executive > 690 851 77E83775 0:00:00.000 0:00:00.020 Wait:UserRequest > 6f8 8 2 77E83775 0:00:00.000 0:00:00.000 Wait:UserRequest > 510 9 7 77E83775 0:00:00.000 0:00:00.000 Wait:UserRequest > 558 8 4 77E83775 0:00:00.000 0:00:00.010 >Wait:DelayExecution > 450 971 77E83775 0:00:00.000 0:00:00.000 Wait:EventPairLow > 37c 8 8158 77E83775 0:00:00.220 0:00:00.861 Wait:UserRequest > 380 8 926 77E83775 0:00:00.020 0:00:00.090 Wait:UserRequest > 414 8 1040 77E83775 0:00:00.010 0:00:00.270 Wait:UserRequest > 210 9 1837 77E83775 0:00:00.040 0:00:00.080 Wait:UserRequest > 268 8 237 77E83775 0:00:00.420 0:00:00.150 Wait:UserRequest > 318 965 77E83775 0:00:00.010 0:00:00.040 Wait:UserRequest > 12c 9 6347 77E83775 0:02:30.826 0:00:00.821 Wait:UserRequest > >The last tid (12c hex) equals to 300: that's my thread after >running Jonathans world famous kill_cpu script. >You can checkout (after converting to dec) a few of the others too. >This was the case on NT4 and I just showed this on W2K >In perfm
RE: 100% CPU utilization, urgent
...and this: SQL> select sid,process from v$session; SID PROCESS -- - 1 892 2 896 3 1044 4 528 5 616 6 792 7 1676:932 (my sqlplus sid=7) the 1676:932 pair appears to be the 'process_id:thread_id' from the sqlplus.exe client program: pid:68c pri: 8 Hnd: 78 Pf: 58549 Ws: 2068K sqlplus.exe tid pri Ctx Swtch StrtAddrUser Time Kernel Time State 3a4 8 55171 77E99264 0:00:14.350 0:00:15.302 Wait:LpcReply regards, Mario Broodbakker -Original Message- Sent: maandag 20 januari 2003 12:39 To: Multiple recipients of list ORACLE-L >If I remember correctly (from a previous NT-life): >v$process.spid maps to the NT thread_id. no, they don't !!! (at least NT4 with a SQLNet connection to a DB Server) (see my question I posted a few days ago) > Frank < >Von: Broodbakker, Mario [mailto:[EMAIL PROTECTED]] >Gesendet am: Montag, 20. Januar 2003 11:34 >An: Multiple recipients of list ORACLE-L >Betreff: RE: 100% CPU utilization, urgent > >If I remember correctly (from a previous NT-life): >v$process.spid maps to the NT thread_id. The thread(s) causing >this can be found probably by looking at pstat or perfmon: >here you can see the cpu consumption. Also you can probably >deduce it from v$sesstat's 'cpu used by this session': it will >be high compared to others (if it's just 1 runaway thread).. >regards, >Mario Broodbakker > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank 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: Broodbakker, Mario 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: 100% CPU utilization, urgent
Frank, I'm pretty sure they do: SQL> select spid,program from v$process; SPID PROGRAM - -- PSEUDO 892 ORACLE.EXE 896 ORACLE.EXE 1044 ORACLE.EXE 528 ORACLE.EXE 616 ORACLE.EXE 792 ORACLE.EXE 300 ORACLE.EXE >From Pstat: pid:6a8 pri: 8 Hnd: 206 Pf: 43673 Ws: 17828K oracle.exe tid pri Ctx Swtch StrtAddrUser Time Kernel Time State 424 8 937 77E99264 0:00:00.020 0:00:01.281 Wait:Executive 690 851 77E83775 0:00:00.000 0:00:00.020 Wait:UserRequest 6f8 8 2 77E83775 0:00:00.000 0:00:00.000 Wait:UserRequest 510 9 7 77E83775 0:00:00.000 0:00:00.000 Wait:UserRequest 558 8 4 77E83775 0:00:00.000 0:00:00.010 Wait:DelayExecution 450 971 77E83775 0:00:00.000 0:00:00.000 Wait:EventPairLow 37c 8 8158 77E83775 0:00:00.220 0:00:00.861 Wait:UserRequest 380 8 926 77E83775 0:00:00.020 0:00:00.090 Wait:UserRequest 414 8 1040 77E83775 0:00:00.010 0:00:00.270 Wait:UserRequest 210 9 1837 77E83775 0:00:00.040 0:00:00.080 Wait:UserRequest 268 8 237 77E83775 0:00:00.420 0:00:00.150 Wait:UserRequest 318 965 77E83775 0:00:00.010 0:00:00.040 Wait:UserRequest 12c 9 6347 77E83775 0:02:30.826 0:00:00.821 Wait:UserRequest The last tid (12c hex) equals to 300: that's my thread after running Jonathans world famous kill_cpu script. You can checkout (after converting to dec) a few of the others too. This was the case on NT4 and I just showed this on W2K In perfmon you can find the thread_id in the Thread Object (don't confuse it with the perfmon's object_id!), and off course the cpu usage of the corresponding thread. regards, Mario Btw I didn't see your earlier question, since I joined the list a few days ago, please send it to me if you want a more specific answer (or correct me if I'm wrong) -Original Message- Sent: maandag 20 januari 2003 12:39 To: Multiple recipients of list ORACLE-L >If I remember correctly (from a previous NT-life): >v$process.spid maps to the NT thread_id. no, they don't !!! (at least NT4 with a SQLNet connection to a DB Server) (see my question I posted a few days ago) > Frank < >Von: Broodbakker, Mario [mailto:[EMAIL PROTECTED]] >Gesendet am: Montag, 20. Januar 2003 11:34 >An: Multiple recipients of list ORACLE-L >Betreff: RE: 100% CPU utilization, urgent > >If I remember correctly (from a previous NT-life): >v$process.spid maps to the NT thread_id. The thread(s) causing >this can be found probably by looking at pstat or perfmon: >here you can see the cpu consumption. Also you can probably >deduce it from v$sesstat's 'cpu used by this session': it will >be high compared to others (if it's just 1 runaway thread).. >regards, >Mario Broodbakker > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank 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: Broodbakker, Mario 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: 100% CPU utilization, urgent
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: Broodbakker, Mario 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).
