Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Session_wait

RE: Session_wait

From: SARKAR, Samir <Samir.SARKAR_at_nottingham.sema.slb.com>
Date: Wed, 30 Jan 2002 09:00:06 -0800
Message-ID: <F001.0040088C.20020130084026@fatcity.com>

Sergey,

There is no LEVEL clause associated with the ERRORSTACK. When u use ERRORSTACK, Oracle creates a stack to store the information relating to a particular error. Your syntax should have been :

 10046 trace name errorstack forever

Hope this helps,

Samir

Samir Sarkar
Oracle DBA - Lennon Team
SchlumbergerSema
Email : samir.sarkar_at_nottingham.sema.slb.com

            samir.sarkar_at_sema.co.uk
Phone : +44 (0) 115 - 957 6217
EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018

-----Original Message-----
Sent: 30 January 2002 15:50
To: Multiple recipients of list ORACLE-L

Hi, everybody,
However, I made the following entry into initXXXX.ora file: Event=" 10046 trace name errorstack level 12" and it rebooted over the weekend. Then I got a call from my boss on Monday morning (I was home sick) letting me know the system had come to a crawl and was producing a trace file every minute or so. Eventually, after some speculations, I told him to rem out that entry and reboot. That resolved the issue, but left me puzzled. If anyone got any insight, it is appreciated, as always. Best,
Sergey
P.S.
Trace files have the same header (different SIDs) and sizes between .5M and 3M
Dump file c:\oracle\ora81\UDUMP\ORA00144.TRC Mon Jan 28 07:35:16 2002
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 2000 Version 5.0 Service Pack 1, CPU type 586 Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Windows 2000 Version 5.0 Service Pack 1, CPU type 586 Instance name: prod

Redo thread mounted by this instance: 1

Oracle process number: 10

Windows thread id: 144, image: ORACLE.EXE

 -----Original Message-----
Sent: Monday, January 28, 2002 10:06 AM
To:     Multiple recipients of list ORACLE-L
Subject:        RE: Session_wait

You're most welcome! Sounds like you've dug well into it and identified problem areas. ( Note John's comments below, too. Some echo my own, and other point to deeper issues in the SQL. )

You might be able to use this info to slap the vendor around in a public forum, creating embarassment and rapid system evolution.

Yours in Slapping Developers Around,

ross

-----Original Message-----
Sent: Friday, January 25, 2002 1:32 PM
To: Multiple recipients of list ORACLE-L

Hi, Ross,
This has been running for about 4 hours now. I got an SQL trace file, and looked at the execution plans. So here's the deal. There's an interesting join condition "...where A.col1=B.col1.....". However, A has a few hundred distinct values in that column, none of them being NULL, and B, which has a few hundred thousand rows, has ALL NULLs in the corresponding column, and that column is not indexed, too. That's the query where it sits for a couple of hours. Guess what the optimizer is doing (8i)? I think internal effects are secondary in this scenario. It is the production (including the database) designed by the company named DELTEK, so nobody can change the code. Anyway, I reported my findings...
Thank you very much for your help, it's always appreciated. Best,
Sergey

 -----Original Message-----
Sent: Friday, January 25, 2002 12:56 PM

To:     Multiple recipients of list ORACLE-L
Subject:        RE: Session_wait

This is a busy little beaver...how long you say this runs?

opened cursors cumulative                                              1072
session logical reads                                              77233609
db block gets                                                       2642119
consistent gets                                                    74591490
physical reads                                                       148822
db block changes                                                    3005410
consistent changes                                                      141
no work - consistent read gets                                     72058049
table scans (short tables)                                           210918
table scans (long tables)                                                36
table scan rows gotten                                            798264962
table scan blocks gotten                                           71788386
table fetch by rowid                                                1074164
leaf node splits                                                       4018
execute count                                                         74445
bytes sent via SQL*Net to client                                      59650
bytes received via SQL*Net from client                                84233
SQL*Net roundtrips to/from client                                       342
buffer is not pinned count                                         73513922

I see some updates (or inserts) and heavy reads...at first glance, though, you don't appear to be I/O throttled but likely have inefficiencies in buffer cache. Pin some small tables? Reexamine access paths for fts, even if on "small" tables (generally defined to be around 5% in blocks of buffer cache size?)and consider seeking out and destroying nested loops joins in favor of hash joins. There's more CPU, but less buffer cache splashing around....don't forget to review init.ora settings for hash, buffer pools, and query planning. (...be happy to do a flyover of those as well, if you like.)

That's about all i can get in a one minute glance, but there are alot of people on the list who'll see more. Look to them for longer posts with more explanation.

Good Luck!

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Babich , Sergey
  INET: SBabich_at_handexmail.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mohan, Ross
  INET: MohanR_at_STARS-SMI.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Babich , Sergey
  INET: SBabich_at_handexmail.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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).

This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema.
If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited.

If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.


--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: SARKAR, Samir
  INET: Samir.SARKAR_at_nottingham.sema.slb.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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). Received on Wed Jan 30 2002 - 11:00:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US