Re: different explain plan on production & test DB

From: Mark <simmons_mark_at_yahoo.com>
Date: 22 Mar 2004 15:23:27 -0800
Message-ID: <5366fb41.0403221523.509af5f2@posting.google.com>

Joe,

Here's what I always do if all of the objects are the same...

/* check for differences in parameters */ 1. create a database link from test to prod 2. select a.name, a.value, b.value

    from v$parameter_at_production a, v$parameter b     where a.name=b.name
    and a.value<>b.value;
3. reset test settings to match production

/* copy production stats to test */
1. Be VERY careful not to move test to prod! 2. Login to production and take a snapshot of the stats (PL/SQL below) begin

        DBMS_STATS.CREATE_STAT_TABLE (
           ownname  => '&schema_owner',
           stattab  => 'STATTAB',
           tblspace => '&tablespace'
        );
        DBMS_STATS.EXPORT_SCHEMA_STATS (
           ownname       => '&schema_owner',
           stattab       => 'STATTAB'
        );

end;
/
3. Use the Oracle export utility and export the stattab table from production.
4. Use the Oracle import utility and import the stattab table into test.
5. Login to test and import the production statistics into the catalog from the stattab table. (PL/SQL below) begin

        DBMS_STATS.IMPORT_SCHEMA_STATS (
           ownname       => '&schema_owner',
           stattab       => 'STATTAB'
        );

end;
/

/* here's a quick check for the indexes */ 1. select index_name, column_name, column_position

    from dba_ind_columns_at_production
    minus
    select index_name, column_name, column_position     from dba_ind_columns;

If you follow these steps you'll probably see the same executions plans on both machines.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

joenix_1_at_hotmail.com (Joe) wrote in message news:<ebfcac26.0403220459.62214b49_at_posting.google.com>...
> Hi,
>
> Our production database has been copied to our test database a while
> ago, so the contents should be rather similar regarding to number of
> rows etc.
>
> For a heavy SQL I get a completely different explain plan on test than
> on production. What are possible reasons??
>
> - indexes seem to be identicatal
> - statistics are up to date
> ...
>
>
> thanks in advance,
> Joe
Received on Mon Mar 22 2004 - 17:23:27 CST