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
Received on Thu Feb 17 2005 - 15:24:27 CST
