Question - dissimilar Plans in 'identical' environments

Hi there.

I've been tasked with tuning a particularly offensive query (8.7.0.4 on AIX 5.2). We're still using the RBO in all cases. CBO is also on my task list. ;-)

We have a Production environment, and a test environment, which is periodically refreshed with production data. Basically, the entire Prod db is shutdown, the files are copied over to the test server, renamed, a new control file is generated, and the db is brought up.

The most recent refresh happened only a week ago.

I fired the offensive query into TOAD and got an overall cost of 12000 in Prod. I put the same query against the Test environment, and the cost was half that - 6500.

The weird bit is that in the Prod environment, the plan indicates full table scans - but in Test, indexes are being used - which ALSO EXIST in Prod. I've checked the build scripts, and the indexes are the same. They list as VALID in dba_indexes.

I'd love some suggestions as to how I can establish what is causing these different plans to be used. So far as I can see, the databases are identical. I'm at a loss. ;)

Thanks for any suggestions,

BD Received on Wed Jul 20 2005 - 17:02:09 CDT