Thanks a lot. Regards, Sergey
-----Original Message----- Sent: Thursday, January 24, 2002 11:00 AM To: Multiple recipients of list ORACLE-L Subject: RE: Session_wait # 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: 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, - Ross 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: [EMAIL PROTECTED] 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: [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.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] 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: [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.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] 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: [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.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] 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: [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.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] 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: [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.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] 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: [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.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] 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: [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.com -- Author: Henry Poras INET: [EMAIL PROTECTED] 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: [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.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] 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: [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.com -- Author: Henry Poras INET: [EMAIL PROTECTED] 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: [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.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] 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: [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.com -- Author: Henry Poras INET: [EMAIL PROTECTED] 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: [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.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] 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: [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).