Re: 66,000.00 times slower, please your opinion

  • From: Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx>
  • To: Karen Morton <Karen.Morton@xxxxxxxxxx>
  • Date: Thu, 17 Feb 2005 16:15:32 -0400

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@xxxxxxxxxx> 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@xxxxxxxxx]
> 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
> --
> //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
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2005