Re: 66,000.00 times slower, please your opinion

  • From: "Terry Sutton" <terrysutton@xxxxxxx>
  • To: <Oracle-L@xxxxxxxxxxxxx>
  • Date: Thu, 17 Feb 2005 12:42:11 -0800

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@xxxxxxxxx>
To: "Karen Morton" <Karen.Morton@xxxxxxxxxx>
Cc: <Oracle-L@xxxxxxxxxxxxx>
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@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


--
//www.freelists.org/webpage/oracle-l

Other related posts:

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