Re: Please Advice on Performance Tuning
NJ, For each SQL statement in each instance, run a trace, then run tkprof against it with the explain option. When you compare the two, it should tell you what they are doing differently and what you're biggest time waster is. Regards, David A. Barbour Oracle DBA, OCP N J Neog wrote: Hi all, We have got a Payroll Application develeoped in-house. It takes 30-34 Minutes to Run this Batch Application. General feeling is - it should be able to process it within 10-15 Minutes may be less than that. I am to look into this problem and give a solution to it. Steps I had followed as 1. Import the payroll user to another Oracle User in the same database. 2. Removed unnecessary Index , Put parallelism into few tables by Alter table tab1 Parallel(DEGREE 5) 3. Analyze the tables with compute statistics; 4. Run the payroll without checking any inefficient SQL or wrong programming logic in the those Packages( It has only two Package in it no other stand alone Procedure or function). Payroll under this new Oracle User runs in 22-24 Minutes, 6-9 Minutes less. So, I asked Payroll Person to remove those unnecessary Index, asked him to put parallelism into those tables which I had done. He runs the Payroll with no Improvement at all. I asked again to drop those table and recreate it with new storage parameter same as the one created at New User. Still no visible Improvement. At this stage every table in both user has same storage parameter,same index ,and also analyzed. The question is why in One User it runs in 22-24 Min and in another 30-34 Min ? Now Please advice me what do I check or to do, so that the it time takes to run comes down to 22-24 Min, same as the new user. Oracle 8.1.4 Optimize goal : choose Biggest table haiving record less than 6 rows. Thanks in Advance Naba -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David A. Barbour 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: Please Advice on Performance Tuning
Hi David, Thanks for your suggestion. I thought TRACE,TKPROF, EXPAIN PLAN will be the last thing thing I will do. Because There may be lot of SQL Statement I have to select from these two Package for probable degradation. Moreover in both user same two package was recompiled. If inefficient SQL are there in Package then it is there in both schema, so if in one user takes 22-24 Min. then the same should happen in other user also or otherway round. The question is what to check for the degradation ? To add to it. Our Oracle Server is in RAID. Both Schema does not have any auditing or Profile (Except default) Thanks once again. Regards. NJN. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, April 27, 2001 11:55 AM NJ, For each SQL statement in each instance, run a trace, then run tkprof against it with the explain option. When you compare the two, it should tell you what they are doing differently and what you're biggest time waster is. Regards, David A. Barbour Oracle DBA, OCP N J Neog wrote: Hi all, We have got a Payroll Application develeoped in-house. It takes 30-34 Minutes to Run this Batch Application. General feeling is - it should be able to process it within 10-15 Minutes may be less than that. I am to look into this problem and give a solution to it. Steps I had followed as 1. Import the payroll user to another Oracle User in the same database. 2. Removed unnecessary Index , Put parallelism into few tables by Alter table tab1 Parallel(DEGREE 5) 3. Analyze the tables with compute statistics; 4. Run the payroll without checking any inefficient SQL or wrong programming logic in the those Packages( It has only two Package in it no other stand alone Procedure or function). Payroll under this new Oracle User runs in 22-24 Minutes, 6-9 Minutes less. So, I asked Payroll Person to remove those unnecessary Index, asked him to put parallelism into those tables which I had done. He runs the Payroll with no Improvement at all. I asked again to drop those table and recreate it with new storage parameter same as the one created at New User. Still no visible Improvement. At this stage every table in both user has same storage parameter,same index ,and also analyzed. The question is why in One User it runs in 22-24 Min and in another 30-34 Min ? Now Please advice me what do I check or to do, so that the it time takes to run comes down to 22-24 Min, same as the new user. Oracle 8.1.4 Optimize goal : choose Biggest table haiving record less than 6 rows. Thanks in Advance Naba -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David A. Barbour 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: N J Neog 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: Please Advice on Performance Tuning
FOR YOUR INFORMATION ESIS and EPFAL are now part of Logica. The Internet email addresses of the staff has changed to the following - [EMAIL PROTECTED] eg [EMAIL PROTECTED] Emails using the old format will continue to be delivered until 30th June 2001. Naba, Whilst you state that both schemas have the same initial and next etc you don't so whether they have the same number of extents. One thing I have seen a couple of times is a table that has a HWM due to rows being added and deleted. A FTS reads upto the HWM despite the fact there may be only a few rows in then table. Just a thought. As David Barbour suggests, tracing is the best option to identify where the time is going. Perhaps an easier method of putting trace on each piece of code is to put the sql_trace = true parameter into your init.ora, restart the database and run the 30 minutes worth of code. Analyze the large trace file that will be produced (tkprof) and focus on the piece(s) of code with largest elapsed times . HTH John -Original Message- Sent: 27 April 2001 06:25 To: Multiple recipients of list ORACLE-L Hi all, We have got a Payroll Application develeoped in-house. It takes 30-34 Minutes to Run this Batch Application. General feeling is - it should be able to process it within 10-15 Minutes may be less than that. I am to look into this problem and give a solution to it. Steps I had followed as 1. Import the payroll user to another Oracle User in the same database. 2. Removed unnecessary Index , Put parallelism into few tables by Alter table tab1 Parallel(DEGREE 5) 3. Analyze the tables with compute statistics; 4. Run the payroll without checking any inefficient SQL or wrong programming logic in the those Packages( It has only two Package in it no other stand alone Procedure or function). Payroll under this new Oracle User runs in 22-24 Minutes, 6-9 Minutes less. So, I asked Payroll Person to remove those unnecessary Index, asked him to put parallelism into those tables which I had done. He runs the Payroll with no Improvement at all. I asked again to drop those table and recreate it with new storage parameter same as the one created at New User. Still no visible Improvement. At this stage every table in both user has same storage parameter,same index ,and also analyzed. The question is why in One User it runs in 22-24 Min and in another 30-34 Min ? Now Please advice me what do I check or to do, so that the it time takes to run comes down to 22-24 Min, same as the new user. Oracle 8.1.4 Optimize goal : choose Biggest table haiving record less than 6 rows. Thanks in Advance Naba This e-mail and any files transmitted with it, are confidential to Logica and are intended solely for the use of the individual or entity to whom they are addressed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hallas, John 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: Please Advice on Performance Tuning
NJ, From what you've outlined here, everything should be the same except in creating the new user, did you use import/export on the tables? If you did, then I'd check to see if row chaining might not be a problem. Have you tried forcing the application to use rule-based optimizer and seeing if you get different results? Regards, David A. BArbour Oracle DBA, OCP N J Neog wrote: Hi David, Thanks for your suggestion. I thought TRACE,TKPROF, EXPAIN PLAN will be the last thing thing I will do. Because There may be lot of SQL Statement I have to select from these two Package for probable degradation. Moreover in both user same two package was recompiled. If inefficient SQL are there in Package then it is there in both schema, so if in one user takes 22-24 Min. then the same should happen in other user also or otherway round. The question is what to check for the degradation ? To add to it. Our Oracle Server is in RAID. Both Schema does not have any auditing or Profile (Except default) Thanks once again. Regards. NJN. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, April 27, 2001 11:55 AM NJ, For each SQL statement in each instance, run a trace, then run tkprof against it with the explain option. When you compare the two, it should tell you what they are doing differently and what you're biggest time waster is. Regards, David A. Barbour Oracle DBA, OCP N J Neog wrote: Hi all, We have got a Payroll Application develeoped in-house. It takes 30-34 Minutes to Run this Batch Application. General feeling is - it should be able to process it within 10-15 Minutes may be less than that. I am to look into this problem and give a solution to it. Steps I had followed as 1. Import the payroll user to another Oracle User in the same database. 2. Removed unnecessary Index , Put parallelism into few tables by Alter table tab1 Parallel(DEGREE 5) 3. Analyze the tables with compute statistics; 4. Run the payroll without checking any inefficient SQL or wrong programming logic in the those Packages( It has only two Package in it no other stand alone Procedure or function). Payroll under this new Oracle User runs in 22-24 Minutes, 6-9 Minutes less. So, I asked Payroll Person to remove those unnecessary Index, asked him to put parallelism into those tables which I had done. He runs the Payroll with no Improvement at all. I asked again to drop those table and recreate it with new storage parameter same as the one created at New User. Still no visible Improvement. At this stage every table in both user has same storage parameter,same index ,and also analyzed. The question is why in One User it runs in 22-24 Min and in another 30-34 Min ? Now Please advice me what do I check or to do, so that the it time takes to run comes down to 22-24 Min, same as the new user. Oracle 8.1.4 Optimize goal : choose Biggest table haiving record less than 6 rows. Thanks in Advance Naba -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David A. Barbour 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: N J Neog 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: David A. Barbour 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
Re: Please Advice on Performance Tuning
Trace each process with timed statistics = true and run tkprof on the trace files, then compare. [EMAIL PROTECTED] 04/27/01 01:25AM Hi all, We have got a Payroll Application develeoped in-house. It takes 30-34 Minutes to Run this Batch Application. General feeling is - it should be able to process it within 10-15 Minutes may be less than that. I am to look into this problem and give a solution to it. Steps I had followed as 1. Import the payroll user to another Oracle User in the same database. 2. Removed unnecessary Index , Put parallelism into few tables by Alter table tab1 Parallel(DEGREE 5) 3. Analyze the tables with compute statistics; 4. Run the payroll without checking any inefficient SQL or wrong programming logic in the those Packages( It has only two Package in it no other stand alone Procedure or function). Payroll under this new Oracle User runs in 22-24 Minutes, 6-9 Minutes less. So, I asked Payroll Person to remove those unnecessary Index, asked him to put parallelism into those tables which I had done. He runs the Payroll with no Improvement at all. I asked again to drop those table and recreate it with new storage parameter same as the one created at New User. Still no visible Improvement. At this stage every table in both user has same storage parameter,same index ,and also analyzed. The question is why in One User it runs in 22-24 Min and in another 30-34 Min ? Now Please advice me what do I check or to do, so that the it time takes to run comes down to 22-24 Min, same as the new user. Oracle 8.1.4 Optimize goal : choose Biggest table haiving record less than 6 rows. Thanks in Advance Naba -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Sawmiller 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 Advice on Performance Tuning
Hi all, We have got a Payroll Application develeoped in-house. It takes 30-34 Minutes to Run this Batch Application.General feeling is - it should be able to process itwithin 10-15 Minutes may be less than that. I am to look into this problem and give a solution to it. Steps I had followed as 1. Import the payroll user to another Oracle User in thesame database. 2. Removed unnecessary Index , Put parallelism into few tables by Alter table tab1 Parallel(DEGREE 5) 3. Analyze the tables with compute statistics; 4. Run the payroll without checking any inefficient SQL or wrong programming logic in the those Packages( It has only two Package in it no other stand alone Procedure or function). Payroll under this new Oracle User runs in 22-24 Minutes, 6-9 Minutes less. So, I asked Payroll Person to remove those unnecessary Index,asked him to put parallelism into those tables which I had done.He runs the Payroll with no Improvement at all. I asked again to drop those table and recreate it with new storage parameter same as the one created at New User. Still no visible Improvement. At this stage every table in both user has same storage parameter,sameindex ,and also analyzed. The question is why in One User it runs in 22-24 Min and in another 30-34 Min ? Now Please advice me what do I check or to do, so that the it time takesto runcomes down to 22-24 Min, same as the new user. Oracle 8.1.4Optimize goal : chooseBiggest table haiving record less than 6 rows. Thanks in Advance Naba
