Same Oracle Server, 2 schemas, one fast, one slow
We are in the process of updating a database at our company to co-exist with a more recent release of third-party software. Only a few tables are changing.
We decided to create two schemas: PLVT and PLV2T respectively
PLVT contains the original version of the database
PLV2T is the next version of the database
Both schemas are complete databases, each schema is unaware of the other.
We also have the same user defined in each schema: PLVT
Both schemas use the same tablespaces so we wouldn't have to create two set of create scripts.
Both schemas have all the proper indexes created.
Unfortunately, performance is good on PLVT, but not good at all on PLV2T
I run the following test script for each schema (I realize this script isn't optimized the best, but bear with me):
select count(*) from time_reported tr, user_period up where
to_char(up.period_finish-1,'YYYY') = '2001'
and tr.period_number = up.period_number
and tr.integrate_status in ('I', 'R', 'P')
and tr.reported > 0;
On the PLVT schema, the script takes 4 minutes to run
On the PLV2T schema, the script takes 7 minutes to run
Had I run the real script, which is much larger, the PLVT schema would have completed the script in 8 minutes while the PLV2T schema would run overnight and still not finish by morning. The EXPLAIN PLAN for the full-size script under both schemas is nearly identical.
Both schemas are on the same development server, so one would think that they would run the same. They don't.
What could have possibly gone wrong when the PLV2T schema was created?