RE: Oracle Performance Tuning steps
Thnx Mike , U saved at least 50-60 bucks of mine. Nywayscan anyone tell me few linkswhere I can find good documentation on Oracle Performance Tuning ? (Or books if they are worth byuing .) Thnx in advance -Chetan "Johnson, Michael " <[EMAIL PROTECTED]>wrote: I have seen no other books thatdeal withOraclewaits the way Gaja and Kirti's book does.After all, the waits are what you are trying todiagnose in a slow system. There are several web sites floating around that have some good technical papers on diagnosing slow systems. IMHO, I find that Oracle Books are like Stock Trading books. There are only a few good ones floating around, but alot of them offer advice that isdown right dangerous, so choose wisely. FWIW. Mike -Original Message-From: Chetan [mailto:[EMAIL PROTECTED]]Sent: Friday, June 28, 2002 4:13 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Oracle Performance Tuning steps Thanks , I looked into the database. There r some waits happening on the undo blocks (non-system) but could not figure out whether this could possibly cause such a slowdown of the system. Also there were some indexes newly created on some of the tables which are causing problems. What's the best approch anyways to hunt down the problem in a situation like this ? - Chetan BigP <[EMAIL PROTECTED]>wrote: cheten , find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics ? -Bigp - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 3:38 AM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World CupDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
Re: Oracle Performance Tuning steps
Hi, I'm working on a pidley sized db ( about 100mb) but sometimes when I try compiling a decent sized package i get a time out error saying the object is locked, while at other times the package compiles on the fly. I'm trying to figure out why i get this error randomly. Some suggestions maybe that the compiler is going into a loop in my code which i seriously doubt. Is there any way I can find out what is causing the compile to hang up yes i know it says the object is locked but when no one is using the db ( that's when I update) why should the object (i.e. my package) lock ? any help would be really appreciated, thank you, Gavin - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Monday, July 01, 2002 12:11 PM Subject: RE: Oracle Performance Tuning steps Thnx Mike , U saved at least 50-60 bucks of mine. Nywayscan anyone tell me few linkswhere I can find good documentation on Oracle Performance Tuning ? (Or books if they are worth byuing .) Thnx in advance -Chetan "Johnson, Michael " <[EMAIL PROTECTED]>wrote: I have seen no other books thatdeal withOraclewaits the way Gaja and Kirti's book does.After all, the waits are what you are trying todiagnose in a slow system. There are several web sites floating around that have some good technical papers on diagnosing slow systems. IMHO, I find that Oracle Books are like Stock Trading books. There are only a few good ones floating around, but alot of them offer advice that isdown right dangerous, so choose wisely. FWIW. Mike -Original Message-From: Chetan [mailto:[EMAIL PROTECTED]]Sent: Friday, June 28, 2002 4:13 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Oracle Performance Tuning steps Thanks , I looked into the database. There r some waits happening on the undo blocks (non-system) but could not figure out whether this could possibly cause such a slowdown of the system. Also there were some indexes newly created on some of the tables which are causing problems. What's the best approch anyways to hunt down the problem in a situation like this ? - Chetan BigP <[EMAIL PROTECTED]>wrote: cheten , find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics ? -Bigp - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 3:38 AM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup
RE: Oracle Performance Tuning steps
Gavin, You need to monitor who is locking the object. v$locked_object could be queried for this info. Long -Original Message-From: Gavin D'Mello [mailto:[EMAIL PROTECTED]]Sent: Monday, 1 July 2002 6:03 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Oracle Performance Tuning steps Hi, I'm working on a pidley sized db ( about 100mb) but sometimes when I try compiling a decent sized package i get a time out error saying the object is locked, while at other times the package compiles on the fly. I'm trying to figure out why i get this error randomly. Some suggestions maybe that the compiler is going into a loop in my code which i seriously doubt. Is there any way I can find out what is causing the compile to hang up yes i know it says the object is locked but when no one is using the db ( that's when I update) why should the object (i.e. my package) lock ? any help would be really appreciated, thank you, Gavin
RE: Oracle Performance Tuning steps[Scanned]
Gavin, I too faced a similar kind of problem in the past. Probably there are some uncommited data in your database (that are related to this package) or somebody might have closed a sql session manually whileexecuting this package. This will create a lock on some of the tables accessed by this package. So make sure you give an explixit commit before compiling this package and also make sure that nobody closes an sql session manually (by using Ctrl+Alt+Del) while this package is executed. Hope it helps. K. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Monday, July 01, 2002 3:03 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Oracle Performance Tuning steps[Scanned] Gavin, You need to monitor who is locking the object. v$locked_object could be queried for this info. Long -Original Message-From: Gavin D'Mello [mailto:[EMAIL PROTECTED]]Sent: Monday, 1 July 2002 6:03 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Oracle Performance Tuning steps Hi, I'm working on a pidley sized db ( about 100mb) but sometimes when I try compiling a decent sized package i get a time out error saying the object is locked, while at other times the package compiles on the fly. I'm trying to figure out why i get this error randomly. Some suggestions maybe that the compiler is going into a loop in my code which i seriously doubt. Is there any way I can find out what is causing the compile to hang up yes i know it says the object is locked but when no one is using the db ( that's when I update) why should the object (i.e. my package) lock ? any help would be really appreciated, thank you, Gavin
Re: Oracle Performance Tuning steps
Thanks , I looked into the database. There r some waits happening on the undo blocks (non-system) but could not figure out whether this could possibly cause such a slowdown of the system. Also there were some indexes newly created on some of the tables which are causing problems. What's the best approch anyways to hunt down the problem in a situation like this ? - Chetan BigP <[EMAIL PROTECTED]>wrote: cheten , find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics ? -Bigp - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 3:38 AM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World CupDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
Re: Oracle Performance Tuning steps
Please run STATSPACK - which will give systemwide database performance diagnostics - you could check the hitratios, top wait events and resource consuming queries once report taken - upload it to www.oraperf.com - for suggestions check metalink article NOTE.149121.1 Gathering a StatsPack snapshot - if you can log into metalink Thanks, Kavi Chetan wrote: Thanks , I looked into the database. There r some waits happening on the undo blocks (non-system) but could not figure out whether this could possibly cause such a slowdown of the system. Also there were some indexes newly created on some of the tables which are causing problems. What's the best approch anyways to hunt down the problem in a situation like this ? - Chetan BigP<[EMAIL PROTECTED]>wrote: cheten ,find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics ?-Bigp - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 3:38 AM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size - 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody worked with STATSPACK before ? Can anybody tell me what should accurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this database considering the fact that it's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup begin:vcard n:;Kavitha x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] fn:Kavitha Muthukumaren end:vcard
Re: Oracle Performance Tuning steps
Based on 30 years of working with developers, I think that you should check what changed in the application. Do not accept: We did not change anything, because more often then not it is: We did not change anything, EXCEPT Yechiel AdarMehish - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 12:18 PM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup
Oracle Performance Tuning steps
Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan ChindarkarDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
Oracle Performance Tuning steps
Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan ChindarkarDo You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
Re: Oracle Performance Tuning steps
Two words: wait events I'll forward, in private mail, a posting I made here on April 21, 2002 with some pointers to sites... (The subject line was Re: ioug-a question) Don Granaman [certifiable OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 26, 2002 5:18 AM Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size - 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody worked with STATSPACK before ? Can anybody tell me what should accurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this database considering the fact that it's a 24x7 system. Thanks in advance . Chetan Chindarkar - Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman 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).
Re: Oracle Performance Tuning steps
cheten , find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics ? -Bigp - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 3:38 AM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup
RE: Oracle Performance Tuning steps
For a 24 X 7 system I would incorporate a hot backup strategy which can be executed without taking the system down and allows for a point in time recovery. There are many scripts floating aroundyou could "borrow" to set this up. Your performance issue is going to require some time to understand and solve. it cannot be answered in a paragraph. Consider buying Gaja Krishna Vaidyanatha and Kirti Deshpande's book "Oracle Performance Tuning 101" as it has been a big help to me. Other things you might find helpful would be www.hotsos.com which goes into great detail on the 10046 event trace and they also have an excellent clinic I just went too. Good Luck and Peace ! Mike -Original Message-From: Chetan [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 26, 2002 3:39 AMTo: Multiple recipients of list ORACLE-LSubject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size- 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody workedwith STATSPACK before ? Can anybody tell me what shouldaccurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this databaseconsidering the fact thatit's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!?Sign-up for Video Highlights of 2002 FIFA World Cup