Actual execution time of query is different from that shown in explain plan
869411Jun 16 2011 — edited Jun 16 2011The explain plan of a query is:
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 49657 (3) | 00:09:56 |
|* 1 | TABLE ACCESS FULL | TEST_TABLE | 1 | 54 | 49657 (3) | 00:09:56 |
---------------------------------------------------------------------------------
The explain plan is showing that query should take only 10 secs to execute.
Whereas, when I run this query from SQL developer, it takes 40 secs to execute. I executed it multiple times to make sure the higher time is not because the query was not loaded into memory the first time. The 40 secs time is consistent no matter how frequent I execute the query from SQL developer.
I am not able to understand why the query is taking higher time in reality but explain plan is showing a lower value.
Any insights into this issue would be a great help.