From oracle-l-bounce@freelists.org Thu Feb 17 15:45:49 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j1HLjn0W011958 for ; Thu, 17 Feb 2005 15:45:49 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j1HLjkem011953 for ; Thu, 17 Feb 2005 15:45:47 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4A74D71309; Thu, 17 Feb 2005 15:44:17 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15855-07; Thu, 17 Feb 2005 15:44:17 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B8D0271CE6; Thu, 17 Feb 2005 15:44:16 -0500 (EST) Message-ID: <049101c51531$359d28c0$6401a8c0@TerrySutton> From: "Terry Sutton" To: References: Subject: Re: 66,000.00 times slower, please your opinion Date: Thu, 17 Feb 2005 12:42:11 -0800 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-ELNK-Trace: 5830af7b3b91928f1aa676d7e74259b7b3291a7d08dfec7983909d4c9dae808c3bd3b667737bfa1a350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 66.245.19.32 X-archive-position: 16346 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: terrysutton@usa.net Precedence: normal Reply-To: terrysutton@usa.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL,HTML_MESSAGE, HTML_TAG_BALANCE_TABLE,RCVD_IN_SORBS autolearn=no version=2.60 X-Spam-Level: OK, now that I've said basically that Karen said before me (I gotta jump on these emails faster if I want the low-hanging fruit). I looked at your tkprof file. While the Hotsos Profiler is a great tool, you don't need it to find your problem, nor do you need Oraperf. The file says you have a total of 18,660 seconds of elapsed time. The first 2 queries in the file show a total elapsed time of 10,884 seconds. Both are doing full table scans. Find out why they're doing full scans and change that, and you should be on the road to fixing your problem. --Terry ----- Original Message ----- From: "Juan Carlos Reyes Pacheco" To: "Karen Morton" Cc: Sent: Thursday, February 17, 2005 12:15 PM Subject: Re: 66,000.00 times slower, please your opinion Thanks Karen I checked, I run in sql*plus with autotrace on, and it was using the index, anyway if you want I can send you the full trace file, if you can give the great hotsos hand. :) program to analyze the trace file. On Thu, 17 Feb 2005 14:07:50 -0600, Karen Morton wrote: > If you changed from a NUMBER to a VARCHAR datatype, you may have problems now with datatype mismatches in your code. > > For example, if a query is SELECT * FROM WHERE table_a.column = 9999, this would have been fine previously. But after the datatype change to the column to make it a string datatype, you now have a problem with the column datatype being a string and the value being a number. In this case, the column will be implicitly converted to a number ( WHERE TO_NUMBER(table_a.column) = 9999 ). This will cause the index not to be used and result in full table scans when you were previously using the index. > > I'd take a look at that possibility and see what you find. > > > Karen Morton > Hotsos Enterprises, Ltd. > http://www.hotsos.com > Upcoming events at http://www.hotsos.com/education/schedule.html > > > -----Original Message----- > From: Juan Carlos Reyes Pacheco [mailto:juancarlosreyesp@gmail.com] > Sent: Thursday, February 17, 2005 12:02 PM > To: oracle-l > Subject: 66,000.00 times slower, please your opinion > > Hi, oraperf is "down" so I need some advice, > I have a group of small tables 13,000 records 310,000 records, etc. A process took 5 minutos to complete and now takes 3:00 > > The only think changed was a primary key on several tables > The column was NUMBER(10) and we changed to VARCHAR2(13) > To simplify in most tables this was the procedure > 1) create nuew varchar2 column > 2) copiy data from number to varchar2 column > 3) drop number column > 4) recreate constraints and index when necesasary > > And that was all. > I know who to optimize this old code, but I want > 1) Understand the problem > 2) optimize without changing the code. > > I don't have previous information but here are the > > trace resume http://juancarlosreyesp.bravehost.com/TuningProblem/trace.TXT > statspack report http://juancarlosreyesp.bravehost.com/TuningProblem/statspack.txt > > Based on this waits > db file scattered read 2,588,531 0 4,836 2 ######## > db file sequential read 1,847,717 0 441 0 ######## > I think recreating the table will solve the problem, What do you think? > > Thank you in advance > > -- > Oracle Certified Profesional 9i 10g > Orace Certified Professional Developer 6i > > 8 years of experience in Oracle 7,8i,9i,10g and developer 6i > -- > http://www.freelists.org/webpage/oracle-l > -- Oracle Certified Profesional 9i 10g Orace Certified Professional Developer 6i 8 years of experience in Oracle 7,8i,9i,10g and developer 6i -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l