Re: 66,000.00 times slower, please your opinion

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_at_gmail.com> To: "oracle-l" <Oracle-L_at_freelists.org> 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
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Feb 17 2005 - 15:38:39 CST