Check execution plan is on the right estimate in Oracle

Check execution plan is working on correct estimate like no of rows in Oracle

Check the estimated row on which the execution plan is created in Oracle:

SQL> Explain plan for select * from employees;

Explained.

SQL> set line 200 pages 200
SQL> select * from table(dbms_xplan.display(null,null,'iostats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445457117

------------------------------------------------
| Id  | Operation         | Name      | E-Rows |
------------------------------------------------
|   0 | SELECT STATEMENT  |           |    107 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |    107 |
------------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

14 rows selected.

SQL>

E-ROWs column Explains Plan gives the estimated row value on which your execution plan is working. If it matches the table row count then it’s good. If not match then we have to run the gatherstats for the table to update its stats.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.