ORACLE EXPLAIN PLAN, wrong values?

Question

I’m using the explain plan of Oracle to raise some statistics and the following problem is happening:

When I created the database, I created an X table with 50 Inserts. Soon after, I inserted about 2000 Inserts in the same table X, and now when I use explain for example:

EXPLAIN PLAN FOR SELECT * FROM X;

The explain goes through the 2000 lines, showing them on the screen, but shows the number of old lines covered in the explain table. It traverses 2000 lines but column "ROWS" of explain shows 50 (the old amount of Inserts).

Can someone explain to me why this is happening and how to fix it?

Answer 1

It is important to realize that the number 50 represents the estimate that the operator calculated based on the statistics it has at the time of execution.

First of all you can try, if you have a version of 10g or more, run the following:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM   X

This option (clue/hint) tells Oracle to collect execution statistics for a certain SQL statement. If you are using the function

(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'))

these results will then be presented side by side with the statistics that the Optimizer estimated in the Execution plan.

The original statistics estimated by the Optimizer are shown in the E-rows column while the current statistics captured during execution are shown in the A-Rows column. For example as in the image below:

Differences in results can be explained in my experience by the following::

• Outdated statistics the moment EXPLAIN PLAN is running. The version 11g has made improvements to the automatic updating and collection of statistics. If you do not have this version and see that the statistics are not updated when there are substantial changes to the table data (TRUNCATE, BULK INSERT, ...) you can always update the statistics manually and then re-analyze the results:

  EXEC dbms_stats.gather_table_stats('Esquemabasedados','X',Scade=>TRUE);

• Another reason may have to do with own contents of the table. Imagine the next case:

    SELECT * 
    FROM   X 
    WHERE  Y = 'ABC'

Table X could have 2000 records but there are only about 40 distinct values for column Y. In this case 2000/40 = 50 and the Optimizer could estimate that a usual query by Y would return 50 records. By this I just mean that sometimes the particularities of the data influence the statistics.

You can always, if you feel it necessary, use a histogram to improve your statistics. But like everything related to Oracle, in case of doubt ask Tom

As a final comment, I would simply say that, even if the estimate is not correct, this does not necessarily pose a problem. If a query is using a relatively updated Execution plan with good performance then there should be no problem. Now if the statistics are completely on the side then yes, that could mean that it is not as optimized as it should be.