top of page
Search

Can the Explain Plan of an SQL Query be accurate in Oracle Database

I have created a table "CUSTOMERS" with 1 million rows and an ID column as a primary key.

SQL> SELECT COUNT(*) FROM CUSTOMERS;

  COUNT(*)
----------
  10000000

I have run the Explain Plan for the below query without gathering object statistics.

If you see below, where it is saying 'SELECT STATEMENT' getting only 1 row whereas actual rows are 9.

SQL> EXPLAIN PLAN FOR SELECT ID FROM CUSTOMERS WHERE ID <10;

EXPLAINED.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 379541792

--------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     6 |     3   (0)| 00:00:01|
|*  1 |  INDEX RANGE SCAN| CUSTOMERS_PK |     1 |     6 |     3   (0)| 00:00:01|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):

   1 - access("ID"<10)
SQL> select count(*) as total_id from customers where id <10;

  TOTAL_ID
----------
         9

Please observe the Explain Plan after running gather statistics for the table CUSTOMERS

SQL> EXEC DBMS_STATS.gather_table_stats('HR','CUSTOMERS');

PL/SQL procedure successfully completed.
SQL> explain plan for select id from customers where id <10;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 379541792
--------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     9 |    54 |     3   (0)| 00:00:01|
|*  1 |  INDEX RANGE SCAN| CUSTOMERS_PK |     9 |    54 |     3   (0)| 00:00:01|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):

   1 - access("ID"<10)

So it is necessary to maintain the statistics for the objects in the database.


Conclusion:

Whenever there is a long-running SQL, the first thing we have to check the statistics of the objects that SQL is using.


26 views0 comments

Recent Posts

See All

Commenti


Contact Me

Tel: 7989359581

Lakshminarayana0071@gmail.com

  • Facebook Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon

Thanks for submitting!

© 2023 by Phil Steer . Proudly created with Wix.com

bottom of page