1

I have two databases with the same data, running on the same physical server.

They are performing differently (one is twice as fast as the other). I've compared the Initialization Parameters and updated the SPFILE parameters so that they are identical. The only difference is the slower one is running on an install with a slightly newer patch level.

Before I roll back the patch on the slower one, is there some tool out there that will tell me all the differences in the configuration between the two databases? I'm under the impression that not everything can be found in the Initialization Parameters/SPFile.

BIBD
  • 1,826
  • 10
  • 29
  • 44

1 Answers1

1

Check your database statistics on the instance that is having issues. If the statistics are stale, then the optimizer will often come up with a different execution plan, including not using indexes where it normally would if it had current stats. One way to quickly check is to compare the explain plan output for the same query on both databases. Obviously, check a query that performs poorly on one instance and fine on the other.

Also, have a look at the dba_tables.last_analyzed column to make sure you're getting some tables analyzed recently. If not, check out dba_scheduler_jobs for job_name GATHER_STATS_JOB to make sure it is running without issue - pay attention to LAST_START_DATE and FAILURE_COUNT.

DCookie
  • 2,098
  • 17
  • 18