Trouble-shooting

A recent item on one of the Oracle forums posed a question about copying statistics from one database to another (typically that would be development to test) to check the sanity of executions on a database that didn’t really have the proper scale of data.

In this case the poster was looking at a test system which had 150% more data than a development system.  After copying the statistics from one system to the other, a simple query showed different execution plans on the two systems, as follows (I’ve trimmed the bytes, cost, and time columns at the right hand to get a better page fit).

Test

------------------------------------------------------------------------------- 
| Id | Operation                | Name          | Rows | Cost | Pstart| Pstop | 
------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT         |               |   30 |    2 |       |       | 
|  1 |  PARTITION RANGE ITERATOR|               |   30 |    2 |    12 | 14    | 
|* 2 |   TABLE ACCESS FULL      | TBL_TX_REPORT |   30 |    2 |    12 | 14    | 
-------------------------------------------------------------------------------    

Development

 
-------------------------------------------------------------------------------------------------- 
| Id | Operation                            | Name                 | Rows | Cost | Pstart| Pstop | 
-------------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT                     |                      |    1 |    1 |       |       | 
|  1 |  PARTITION RANGE ITERATOR            |                      |    1 |    1 |    12 |    14 | 
|* 2 |   TABLE ACCESS BY LOCAL INDEX ROWID  | TBL_TX_REPORT        |    1 |    1 |    12 |    14 | 
|* 3 |    INDEX RANGE SCAN                  | IX_TBL_TX_LONDONTIME |    1 |    1 |    12 |    14 | 
--------------------------------------------------------------------------------------------------    

The question is, what else (apart from the object statistics) could the plans depend on.

The first thing to note is that the cardinality (rows) estimates are different – so the difference in plans is not inherently to do with costing, it’s to do with the calculation of selectivity. So we need to find out why the selectivity can be different when the object statistics are supposed to be the same.

Investigation point one – we don’t have the query and we don’t have the full execution plan; so we are a little deprived of clues. The execution plan should always be a proper report from dbms_xplan, showing the access_predicates and filter_predicates. In this case, we may find that (for reasons as yet unknown) the predicates reported in the plans are different – and don’t necessarily match the predicates in the original query.

It’s possible that there is a difference in (say) some of the NLS parameters that causes a concealed implicit conversion that blocks an index.  (You can’t help noticing that the index used has the words “London Time” in the name – maybe there’s a funny time zone conversion going on that has been hidden.

Investigation point one and a half – run the query that does a table scan with a hint to use the target index: maybe it simply can’t use it at all.

Investigation point two – assuming the predicates are identical, are all optimizer-related parameters the same (and don’t forget the Oracle version as a starting point). The simplest option is to enable event 10053 and run a simple query like “select ‘rubbish’ from dual”.  There is a section in the trace file that lists all the optimizer environment details. If you find a difference check if the name (and description) make it look like the guilty parameter.

Investigation point three – tedious, but if the predicates are the same and the environments are the same, you have to query the statistics and check that they really are the same. Maybe the export/import simply didn’t work properly. This is really tedious because it means querying user_tables, user_indexes, user_tab_partitions, user_ind_partitions, user_tab_cols, user_part_col_statistics, user_tab_histograms and user_part_histograms for the table and all its indexes and any columns in the “where clause” to see that all the statistical information is identical.

If you get to the end of point three and still can’t spot the issue, you might want to look at the 10053 trace which, in this case, is likely to be fairly short, and see if you can spot where the difference first appears.