Re: query plans and clustering

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 5 Sep 2005 13:21:13 -0700
Message-ID: <1125951673.577359.95120@g44g2000cwa.googlegroups.com>

Column level statistics are available for viewing in dba_tab_columns. Number of distinct values, number of nulls, etc...

Check on the presence or absense of histograms on the data in both databases.

>From version 8.1 to 9 more than a dozen hidden parameters that affect
the CBO were changed from defaulting to off in 8.1 to on in 9+. These parameters do things like control when predicates get pushed to sub-queries and so on.

You need to analyze the SQL statement using dbms_xplan which will show information on when filters are applied. Look for a difference if the histogram setup is equal. Based on the differences you can search metalink to see if you are htting one of the know issues. Several of which are fixed by 9.2.0.6 which is why I asked. You do not want to be on 9.2.0 as sub-versions .4, .5, and .6 are all more stable.

HTH -- Mark D Powell -- Received on Mon Sep 05 2005 - 15:21:13 CDT