Re: 66,000.00 times slower, please your opinion

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" <juancarlosreyesp_at_gmail.com> To: "Karen Morton" <Karen.Morton_at_hotsos.com> Cc: <Oracle-L_at_freelists.org> 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 <Karen.Morton_at_hotsos.com> 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 <table list> 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_at_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