Re: 66,000.00 times slower, please your opinion

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

Juan,

I haven't looked at your links yet, but the first thing that would come to
mind is type conversion.  If you have a query whose predicate was "where PK
= 12345", and you've changed the PK to a varchar2 column, you'll want the
query to change to "where PK = '12345' ".  Otherwise you won't use the
index.

--Terry

----- Original Message ----- 
From: "Juan Carlos Reyes Pacheco" <juancarlosreyesp@xxxxxxxxx>
To: "oracle-l" <Oracle-L@xxxxxxxxxxxxx>
Sent: Thursday, February 17, 2005 12:01 PM
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


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

Other related posts:

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