Re: Orace Performance Question

spam_at_spam.com wrote:

> Hi
>
> I am fairly new to oracle. About three months a go we test our app on
> a oracle 8i running on Sparc Solaris 8. Every thing was fine.. it the
> standard suit of report we run were very fast.
>
> So we moved the data onto a live system and stated using it. But now
> we cannot get the same performance. I know I am asking how is a pice
> of string question, but would like some recommendation.. This is some
> of the difference between the test and live system
>
> 1 The main table on the test system had 26 extents but on the live
> system it is on 222... Would that have a baring on the performance. It
> has it own table space

> 2 The secondary tables also have more extents the test system, and the
> tablespace they live in has lot more large tables in it then the test
> system. Would that cause the HI IO we are seeing when the secondary
> tables are accessed.
> The other tables are archive data, they are very rarely accessed.
>
> If any one can comment on if this has a baring on the performance ?
>
> Thanks

The number of extents is irrelevant to the problem. Here's a checklist.

1. Compare the hardware as Bricklin suggests (especially disk and RAM)
2. Compare the indexes ... are they all there?
3. Look in the init.ora ... is the optimizer set to CHOOSE for both?
4. Also in the init.ora ... look for other differences ...
5. If CHOOSE then by default Oracle is going to try to use the Cost Based Optimizer (CBO) which requires statistics in the data dictionary.

Run the following queries in the schema with the tables (not as SYS or SYSTEM): SELECT DISTINCT TRUNC(last_analyzed)
FROM user_tables;

If you get no return date, or it is current, that statistics were never run or are old. Rerun using DBMS_STATS.GATHER_SCHEMA_STATS. For the exact syntax go to http://tahiti.oracle.com.

Finally ... run EXPLAIN PLAN on a couple of your queries and see what is different.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)