When you upgrade from 9i to 10g (or higher) you may find lots of execution plans suddenly “go wrong”. There are three main reasons for this. The first is that 10g automatically enables CPU costing, and if you weren’t using it in 9i this is likely to make a difference – often an improvement but you may be unlucky. The second is that there are numerous new optimisation strategies in 10g, and some of them may not be good for your particular data patterns, or your particular types of query.
The other big reason is that the dbms_stats packages keeps changing its default behaviour. Apart from the fact that there is an automatic job (see view dba_scheduler_jobs) created to gather stale stats every day, the code that you used to run in your own stats collection routines may be doing something completely different.
Here’s the definition of the procedure to gather table stats in its Oracle 9i form:
procedure gather_table_stats( ownname varchar2, tabname varchar2, partname varchar2 default null, estimate_percent number default null, block_sample boolean default FALSE, method_opt varchar2 default 'FOR ALL COLUMNS SIZE 1', degree number default null, granularity varchar2 default 'DEFAULT', cascade boolean default FALSE, stattab varchar2 default null, statid varchar2 default null, statown varchar2 default null, no_invalidate boolean default FALSE );
And here’s the same procedure from 10g:
procedure gather_table_stats ( ownname varchar2, tabname varchar2, partname varchar2 default null, estimate_percent number default to_estimate_percent_type(get_param('ESTIMATE_PERCENT')), block_sample boolean default FALSE, method_opt varchar2 default get_param('METHOD_OPT'), degree number default to_degree_type(get_param('DEGREE')), granularity varchar2 default get_param('GRANULARITY'), cascade boolean default to_cascade_type(get_param('CASCADE')), stattab varchar2 default null, statid varchar2 default null, statown varchar2 default null, no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')), stattype varchar2 default 'DATA', force boolean default FALSE );
Of course, it’s not easy to get a direct view of all the changes because of those calls to the function dbms_stats.get_param(), but the changes are a threat. Here’s how you can see what’s happened to the “method_opt”:
select dbms_stats.get_param('METHOD_OPT') from dual; DBMS_STATS.GET_PARAM('METHOD_OPT') ---------------------------------- FOR ALL COLUMNS SIZE AUTO
Because of this particular change, if you didn’t specify a method_opt in your scripts under 9i you were not generating histograms; under 10g you will be collecting histograms on any columns that Oracle thinks might be suitable candidates. The effect could be dire.
So, before you start trouble-shooting specific SQL statements after the upgrade, go back to your stats collection scripts and modify the code to set any default values to the actual value they would have had in 9i. Then re-run the collection and see if most of your problems have gone away.
As a quick check (before thrashing the system to death rebuilding all the statistics) you might like to see how many histograms you actually have. The following query will identify columns with (real) histograms.
select owner, table_name, column_name, count(*) from dba_tab_histograms group by owner, table_name, column_name having count(*) > 2 ;
For further details on the dbms_stats package, check the script $ORACLE_HOME/rdbms/admin/dbmsstat.sql.