RE: Hotsos' Classes
Cary, So, I don't need to be completely proficient in SQL Optimization prior to enrolling in Performance Diagnosis 101? When if it all do you plan to offer these classes in New York, NJ area? Venu Potluri -Original Message- Cary Millsap Sent: Wednesday, January 07, 2004 1:04 AM To: Multiple recipients of list ORACLE-L Venu, I can't give a nonpartisan answer, but I can provide some information. I hope this helps: Performance Diagnosis 101 (formerly the Hotsos Clinic) - This is a 3-day course dedicated to teaching a method for diagnosing performance problems on production (and integration-testing) systems. It's an instructor-led course with about 70% lecture and 30% exercises and discussion. More information, including FAQs, at www.hotsos.com/education/PD101.php. SQL Optimization 101 - This is a 5-day course, presently in beta development, dedicated to teaching developers and DBAs how to optimize inefficient SQL statements in both stock and custom applications. More information at www.hotsos.com/education/OP101.php. Hotsos Symposium 2004 - This is a 3-day conference that showcases lots of speakers from all over the world. Presentations range from basic to advanced, but everything at the event focuses on issues of Oracle system performance. More information at www.hotsos.com/appearances/sym2004.php. See www.hotsos.com/appearances/sym2004-papers.php for the draft of the presentation abstract list. Steve Adams seminar - We've invited Steve to present a one-day follow-up seminar after our Symposium. His subject matter will be very focused and very deep. I saw 3 days of his material last year in Denmark, and it was phenomenal. Steve comes rarely to the USA, so we're fortunate to have convinced him to present in conjunction with our event in Dallas. More information at www.hotsos.com/appearances/adams2004.03.php. 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- Potluri, Venu (CT Appl Suppt) Sent: Tuesday, January 06, 2004 1:24 PM To: Multiple recipients of list ORACLE-L Is there a proper sequence of classes to take, among those offered by Hotsos? While I do use Wait events to monitor tune my Oracle Financials databases, I need more training in Performance tuning. I am just dipping my toes into the world of Oracle Performance Tuning. I am reading Cary's book and I have used Ixora's treasure of information, quite a bit in the past. I see there are a number of classes now as opposed to a clinic in the past. Performance Diagnosis 101 SQL Optimization 101 Hotsos Symposium 2004 Steve Adams Seminar While I would love to take all of them, time work schedule permitting, I wouldn't want to waste time either if any of the subject matter goes above my head and I spend time scratching my head. God knows I might even find SQL Tuning class useful to brush up little that I know. Would the Hotsos Symposium be not suitable for poeple like myself who haven't taken the Performance Diagnostics 101 class and aren't well versed with Performance Tuning? What is expected of the audience in terms of prior knowledge? Mind you, I am not an expert like most of you guys. Haven't written a book or even a paper! Venu Potluri Oracle Financials DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Potluri, Venu (CT Appl Suppt) 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: Cary Millsap 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: Potluri, Venu (CT Appl Suppt) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list
RE: ORA-01578 data block corrupted
Title: ORA-01578 data block corrupted Run dbv on the datafile. Also there is a document on Metalink which describes how to handle data block corruption. Use the folliwng query to get segment name SELECT TABLESPACE_NAME, SEGMENT_TYPE, OWNER, SEGMENT_NAMEFROM DBA_EXTENTSWHERE FILE_ID = 'fileid'AND 'blockno' BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1; -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Nguyen, David MSent: Tuesday, January 06, 2004 11:05 AMTo: Multiple recipients of list ORACLE-LSubject: ORA-01578 data block corrupted I got ORA-01578 error while querrying info for below table. How do I fix this error? SQLSelect count(*) from GATEWAYCALLSTATS; * ERROR at line 2: ORA-01578: ORACLE data block corrupted (file # 3, block # 2683299) ORA-01110: data file 3: '/apps/oracle/oradata/SIDB/rtesvr01.dbf' Thanks, David
Hotsos' Classes
Is there a proper sequence of classes to take, among those offered by Hotsos? While I do use Wait events to monitor tune my Oracle Financials databases, I need more training in Performance tuning. I am just dipping my toes into the world of Oracle Performance Tuning. I am reading Cary's book and I have used Ixora's treasure of information, quite a bit in the past. I see there are a number of classes now as opposed to a clinic in the past. Performance Diagnosis 101 SQL Optimization 101 Hotsos Symposium 2004 Steve Adams Seminar While I would love to take all of them, time work schedule permitting, I wouldn't want to waste time either if any of the subject matter goes above my head and I spend time scratching my head. God knows I might even find SQL Tuning class useful to brush up little that I know. Would the Hotsos Symposium be not suitable for poeple like myself who haven't taken the Performance Diagnostics 101 class and aren't well versed with Performance Tuning? What is expected of the audience in terms of prior knowledge? Mind you, I am not an expert like most of you guys. Haven't written a book or even a paper! Venu Potluri Oracle Financials DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Potluri, Venu (CT Appl Suppt) 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).
A performance problem
I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on. The Top5 Wait events were: Top 5 Wait Events ~ Wait Event Waits Time (cs) % Total Wt Time --- db file sequential read 15,978,336 5,809,277 57.28 SQL*Net message from dblink 3,868 1,960,168 19.33 db file scattered read 2,460,279 943,252 9.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,850 2.06 - - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event WaitsTimeouts Time (cs)(ms) /txn -- --- -- - db file sequential read 15,978,336 0 5,809,277 4 970.3 SQL*Net message from dblink 3,868 0 1,960,168 5068 0.2 db file scattered read 2,460,279 0 943,252 4149.4 control file sequential read907,1480300,572 355.1 pipe put2,033 2,032208,850 1027 0.1 Breakdown of Wait time Event TimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read 5809277 60.16% 0.360.68 8.228762.11 SQL*Net message from dblink 1960168 20.30% 506.77 0.23 2.772956.51 db file scattered read 943252 9.77% 0.380.11 1.341422.70 control file sequential read 300572 3.11% 0.330.04 0.43453.35 pipe put208850 2.16% 102.73 0.02 0.30315.01 Here are the top SQL statements ordered by physical reads per execute: (these two happen to belong to this long running job) Statement ExecutesPhysical Reads Reads/Execute Hashs Value % of Total INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) */SUBSTR(GLCC.SEGMENT3,1,6) CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5) ACCT,SUBSTR(GLCC.SEGMENT2,1,10) NEW10,SUBSTR(GLCC.SEGMENT6,1,6) PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5 13 9737644 749049.54 1419451399 30.18 SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,0 BAL,(ABS(NVL(MTD_TRANSACTION_DR_AMOUNT 30 5839191 194639.70 2733501134 48.27 I am not sure on how to interpret the SQL*Net message from dblink wait event. Obviously we have a db link on this database pointing to another production database into which the data is being fed. Does this wait event indicate a network issue more so than a database issue? What else jumps out here? Thanks. Venu Potluri Oracle Financials DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Potluri, Venu (CT Appl Suppt) 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
RE: A performance problem
The other database in on a different server. I looked at the statspack report for the other database, for the time period in question. Top 5 Timed Events ~~ % Total Event Waits Time (s)Ela Time --- --- db file sequential read 5,802,489 48,722 44.14 free buffer waits 31,015 26,670 24.16 db file parallel write 9,81712,298 11.14 CPU time7,020 6.36 write complete waits 6,3015,584 5.06 We do have increase in amount of data but not enought to account for a 20-hour run. I am looking at the statspack report during the times this job previoulsy ran. How do I enable 10046 trace for sql executed by a concurrent job? I do have a trace file for this job but it was obtained by turning trace on in Oracle Apps for this job and doesn't contain any wait event information. -Original Message- [EMAIL PROTECTED] Sent: Monday, December 29, 2003 12:09 PM To: Multiple recipients of list ORACLE-L the sqlnet is a network issue. talk to your SAs. is the other database on a different server? work from there. your big one is your read. could mean your SGA is too small. is anything else running at this time? are you sure there is an equivalent amount of work to do? are you sure there isnt more data involved? do you have a previous statspack report to compare it to? you also need to run a 10046 trace on the queries involved and see what they are doing. maybe the plan changed do to a change in data or you dont have accurate statistics or a parameter setting changed? From: Potluri, Venu (CT Appl Suppt) [EMAIL PROTECTED] Date: 2003/12/29 Mon AM 11:44:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: A performance problem I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on. The Top5 Wait events were: Top 5 Wait Events ~ Wait EventWaits Time (cs) % Total Wt Time --- db file sequential read 15,978,336 5,809,277 57.28 SQL*Net message from dblink 3,868 1,960,168 19.33 db file scattered read 2,460,279 943,252 9.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,850 2.06 - - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event WaitsTimeouts Time (cs)(ms) /txn -- --- -- - db file sequential read 15,978,336 0 5,809,277 4970.3 SQL*Net message from dblink 3,868 0 1,960,168 5068 0.2 db file scattered read2,460,279 0 943,252 4149.4 control file sequential read 907,1480300,572 355.1 pipe put 2,033 2,032208,850 1027 0.1 Breakdown of Wait time Event TimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read 5809277 60.16% 0.360.68 8.228762.11 SQL*Net message from dblink 1960168 20.30% 506.77 0.23 2.772956.51 db file scattered read943252 9.77% 0.380.11 1.341422.70 control file sequential read 300572 3.11% 0.33
RE: A performance problem
John, I can run this in our development environment and trace the job. But, the data is quite a bit larger in production. I can't really take on a refresh/clone now and the prodcution database is over 600GB in size. We do have trace for the job which was available because the program definition for this custom feed job has trace enabled in Apps. That trace file doesn't have any wait event information. This job does use db link. We know that for sure. I advised the developer who wrote this custom feed job to tune it but that is never a satisfactory answer for them. Venu Potluri -Original Message- John Kanagaraj Sent: Monday, December 29, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Venu, Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!] 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: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Subject: A performance problem I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on. The Top5 Wait events were: Top 5 Wait Events ~ Wait Event Waits Time (cs) % Total Wt Time --- db file sequential read15,978,336 5,809,277 57.28 SQL*Net message from dblink3,868 1,960,168 19.33 db file scattered read 2,460,279 943,252 9.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,850 2.06 - - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Waitwait Waits Event WaitsTimeouts Time (cs)(ms) /txn -- --- -- - db file sequential read15,978,336 0 5,809,277 4970.3 SQL*Net message from dblink 3,868 0 1,960,168 5068 0.2 db file scattered read 2,460,279 0 943,2524149.4 control file sequential read 907,1480 300,572355.1 pipe put 2,033 2,032 208,850 1027 0.1 Breakdown of Wait time Event TimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read5809277 60.16% 0.36 0.688.228762.11 SQL*Net message from dblink 196016820.30% 506.77 0.232.772956.51 db file scattered read 943252 9.77% 0.38 0.111.341422.70 control file sequential read 3005723.11% 0.33 0.040.43453.35 pipe put 208850 2.16% 102.73 0.020.30315.01 Here are the top SQL statements ordered
RE: A performance problem
,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,0 BAL,(ABS(NVL(MTD_TRANSACTION_DR_AMOUNT 30 5839191 194639.70 2733501134 48.27 I am not sure on how to interpret the SQL*Net message from dblink wait event. Obviously we have a db link on this database pointing to another production database into which the data is being fed. Does this wait event indicate a network issue more so than a database issue? What else jumps out here? Thanks. Venu Potluri Oracle Financials DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Potluri, Venu (CT Appl Suppt) 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Potluri, Venu (CT Appl Suppt) 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: A performance problem
Dennis, Good advice. I will compare the explain plans. I was only half kidding about my head. As you may know some developers would blame the DBAs for anything they can think of such as snow, rain, poorly performing sql they wrote, etc Thanks, Venu -Original Message- DENNIS WILLIAMS Sent: Monday, December 29, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Venu You are getting some good advice, but here is a different idea for you that nobody has mentioned. You say that the job formerly took 1 hour and now takes 20 hours. You also mention that you have a development environment. If you can locate the main SQL statement(s), you could run an EXPLAIN PLAN in both your production and development environments. This is not nearly as good a way to diagnose performance problems as the other advice you are receiving, but it has the advantage of being quick (Oracle doesn't actually execute the statement), and may put you on the track of what has changed with the execution plan. When they are after your head, quick is good. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, December 29, 2003 12:15 PM To: Multiple recipients of list ORACLE-L John, I can run this in our development environment and trace the job. But, the data is quite a bit larger in production. I can't really take on a refresh/clone now and the prodcution database is over 600GB in size. We do have trace for the job which was available because the program definition for this custom feed job has trace enabled in Apps. That trace file doesn't have any wait event information. This job does use db link. We know that for sure. I advised the developer who wrote this custom feed job to tune it but that is never a satisfactory answer for them. Venu Potluri -Original Message- John Kanagaraj Sent: Monday, December 29, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Venu, Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!] 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: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Subject: A performance problem I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on. The Top5 Wait events were: Top 5 Wait Events ~ Wait Event Waits Time (cs) % Total Wt Time --- db file sequential read15,978,336 5,809,277 57.28 SQL*Net message from dblink3,868 1,960,168 19.33 db file scattered read 2,460,279 943,252 9.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,850 2.06 - - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Waitwait Waits Event WaitsTimeouts Time (cs)(ms) /txn -- --- -- - db file sequential read
