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: Babich , Sergey <SBabich_at_handexmail.com>
Date: Fri, 25 Jan 2002 05:37:59 -0800
Message-ID: <F001.003FB052.20020125051517@fatcity.com>

Thank you very much, Henry, I really appreciate the help. I'll post the results later.
Best,
Sergey

-----Original Message-----

Sent: Thursday, January 24, 2002 4:25 PM

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

Let me try again (using expand -t 3 as suggested by Jared)

#  Script for analyzing Oracle Trace files with WAIT statistics
#  Usage:      wait_scan.awk <filename>
#  Written:    Henry Poras
#              5/16/00
#  Modified:   12/3/01  Initially assumes all wait states for a cursor are
between
#                       parse statements.
#
#
nawk  '                                                        # need nawk,
not awk

   BEGIN {N=""

          PARSE_FLAG=0                                         # PARSE_FLAG
= 0 (normal state)
          printf("\n\n%-35s %-12s %-18s\n\n",                  # PARSE_FLAG
= 1 (previous line PARSING)
         "WAIT EVENT", "# OF TIMES", "ELAPSED TIME (sec)")     # print
column headers 
         }

         {if (PARSE_FLAG==1)                                   # if previous
line started
            {SQL[N]=$0                                         # with
PARSING, print 
             PARSE_FLAG=0                                      # the SQL. 
                N=""                
            }                    
         }                          
   /^PARSING/  {FS=" "
          N=$4
          sub("#","",N)
          if (N in SQL)
            prinfo(N)
          PARSE_FLAG=1
         }
   /^WAIT/     {FS="#| nam=|ela=|p1="
          N=$2
               sub(":","",N)
          PARSE_FLAG=2
          n_wait[N,$3] += 1
          ela_wait[N,$3] += $4
         }  
   END      {for (N in SQL)    {                               # Print Wait
statistics for final
            printf "\n\n\n%s\n\n", SQL[N]                      # SQL
statement in file
            for (k in n_wait) {
             split(k,arg,SUBSEP)
             if (arg[1]==N && n_wait[k]!=0) {
               printf "%-35s %-12s %12.2f\n",
               arg[2],n_wait[k],ela_wait[k]/100
               n_wait[k]=0
               ela_wait[k]=0
             }
            }     
          printf "\n\n"
         }
         for (k in n_wait) {
            split(k,arg,SUBSEP)
            if (n_wait[k] != 0) {
               printf "%-35s %-12s %12.2f\n",
               arg[2],n_wait[k],ela_wait[k]/100
               n_wait[k]=0
               ela_wait[k]=0
            }
         }
         }

   function prinfo(N,   k) {
         printf "\n\n\n%s\n\n", SQL[N]
         for (k in n_wait){
            split(k,arg,SUBSEP)
            if (arg[1]==N && n_wait[k]!=0) {
               printf "%-35s %-12s %12.2f\n",
               arg[2],n_wait[k],ela_wait[k]/100
               n_wait[k]=0
               ela_wait[k]=0
            }
         }  
      }

   ' $1

-----Original Message-----
Sent: Thursday, January 24, 2002 11:00 AM To: Multiple recipients of list ORACLE-L

#       Script for analyzing Oracle Trace files with WAIT statistics
#       Usage:          wait_scan.awk <filename>
#       Written:        Henry Poras

...
-----Original Message-----
Sent: Wednesday, January 23, 2002 1:56 PM To: Multiple recipients of list ORACLE-L

Hi, Henry,
Please, post it. I am terribly sorry for asking dumb questions, but hey! I've come a long way to be a part of this country, learned the language, become a DBA and proud of it! Time to learn! Thanks a bunch in advance!

Sincerely,
Sergey

-----Original Message-----

Sent: Wednesday, January 23, 2002 1:02 PM

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

Couldn't hurt. Also why not do some deltas of your session statistics (before and after snapshots)

Henry

PS: After you have the trace file, I put together an awk statement to sum up the number and elapsed time of the wait states for each SQL statement. I can post that if you would like.

-----Original Message-----
Sent: Wednesday, January 23, 2002 11:21 AM To: Multiple recipients of list ORACLE-L

Hi, Henry,
Since I didn't expect that to happen, I had just SQL trace turned on for that particular session. Do you suggest entering event="10046 trace name errorstack level 12" into initXXXX.ora? Thanks,
Regards,
Sergey

-----Original Message-----

Sent: Wednesday, January 23, 2002 9:50 AM

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

Sergey,
At what level did you trace?

Henry

-----Original Message-----
Sent: Wednesday, January 23, 2002 8:15 AM To: Multiple recipients of list ORACLE-L

Thank you, guys, for your help, just got back to work. Unfortunately, it's too late to run the query, but later on we'll run the OLTP again, and then I'll do it and post the output. Currently I'm analyzing the trace file which is about 130M (did not tkprof it yet), and that size does NOT sound healthy to me.
Best regards,
Sergey Babich

-----Original Message-----

Sent: Tuesday, January 22, 2002 5:19 PM

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

Sergey, please run this and post values. Just curious.

select
name, value
from
v$sesstat vs, v$statname sn
where
vs.statistic#=sn.statistic# and
value is NOT NULL and
value<>0 and
sid=11;

hth,

p.s. "shadow process" - your client connection's 'footprint' in the os.

    if a local host connect, look for "LOCAL=YES". If not, look for

        LOCAL=NO. Sort out all not in your instance name.

-----Original Message-----
Sent: Tuesday, January 22, 2002 4:57 PM
To: Multiple recipients of list ORACLE-L

Well, now it's finished, but the timing is terrible... I should've queried v$lock. RAID had a lot of activity while that was going on. File #10 is a "data" datafile. I am not sure what is meant by shadow process, sorry. I guess we'll repeat this tomorrow.
Thank you, guys, I really appreciate your help. Best regards,
Sergey

-----Original Message-----

Sent: Tuesday, January 22, 2002 4:36 PM

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

well, then it's an entirely different kettle of fish. Jeremiah is on track.

don't suppose you can query v$lock where sid=11 or block<>0 or lmode=6 while
this is going on, can you?

and...in the OS....what is going on w/disk? and with the shadow process?

and, lastly, what is file# 10? Probably a "data" datafile.......

-----Original Message-----
Sent: Tuesday, January 22, 2002 4:11 PM
To: Multiple recipients of list ORACLE-L

Thanks for your input, but P2 was not changing as u can c from the last one I caught:

SID       SEQ#  EVENT                      P1TEXT             P1  P1RAW
P2TEXT             P2  P2RAW     P3TEXT             P3  P3RAW     WAIT_TIME
SEC_WT STATE
-----  ---------  -------------------------  ----------  ---------  --------
---------- --------- -------- ---------- --------- -------- ---------
------  -------------------
   11      40019  db file sequential read    file#              10  0000000A
block#         221571  00036183  blocks              1  00000001         -1
1594 WAITED SHORT TIME
After that the SQL changed....
Regards,
Sergey Babich
-----Original Message-----

Sent: Tuesday, January 22, 2002 3:32 PM
To:     Multiple recipients of list ORACLE-L
Subject:        RE: Session_wait

it was reading blocks into SGA buffers. No big deal. As the wait time went up, so likely were the values of P2 changing.

A longish read by sid 11.

<shrug>

-----Original Message-----
Sent: Tuesday, January 22, 2002 3:19 PM
To: Multiple recipients of list ORACLE-L

Hi, listers,
One of the sessions seems to be a problem: SELECT * FROM V$SESSION_WAIT WHERE SID=&NUM /
Output:

SID       SEQ#  EVENT                      P1TEXT             P1  P1RAW
P2TEXT             P2  P2RAW     P3TEXT             P3  P3RAW     WAIT_TIME
SEC_WT STATE
-----  ---------  -------------------------  ----------  ---------  --------
---------- --------- -------- ---------- --------- -------- ---------
------  -------------------
   11      40019  db file sequential read    file#              10  0000000A
block#         221571  00036183  blocks              1  00000001         -1
335 WAITED SHORT TIME Then wait_time was 689, then 749 and higher and higher (the rest of the output being the same).
Any su

ggestions are appreciated.
Regards,
Sergey Babich

-- 
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).
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: Henry.Poras_at_ctp.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: Henry.Poras_at_ctp.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: Henry.Poras_at_ctp.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: Henry Poras
  INET: Henry.Poras_at_ctp.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).
Received on Fri Jan 25 2002 - 07:37:59 CST

Original text of this message

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