66,000.00 times slower, please your opinion

From: Juan Carlos Reyes Pacheco <juancarlosreyesp_at_gmail.com>
Date: Thu, 17 Feb 2005 16:01:47 -0400
Message-ID: <cd4305c105021712015f2c9bb7@mail.gmail.com>

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