Skip to Main Content
  • Questions

  • Explan plan guess is way beyong the reality...

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Firdous.

Asked: February 19, 2016 - 3:07 pm UTC

Last updated: February 20, 2016 - 4:16 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,

I have read many of your post how explan plan misleads the reality.

My explan plan is doing same thing Just guessing so wrong..gives time 71 secs to execute this query. In reality - it took an hour

Here is my problem..Oracle says if the explain plan guess is less than 10 seconds then a query will never be a candidate for parallel execution. 99% of my queries have a time estimate equal to 1 second which is not true though. My question is why?

I can accept a guess of 1 minute, with a real run time 6 minutes. Here the guess of 70 secs turned to be 60 minutes in reality.

I need to understand why the estimate is so wrong most of the times. I checked statistics etc all seems ok. One more thing, when I use PARALLEL HINT it executes faster but normally optimizer fails to execute my query in parallel why?


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_time_threshold string AUTO


Regards,
Shah

and Chris said...

There are many reasons why the explain plan estimates incorrectly:

- Missing, out of date or otherwise inaccurate stats
- Bind variables against columns with skewed data (those with a handful of values that occur significantly more often than other values)
- Optimizer limitations and bugs
- and so on

If you want to know why it's happening in your case then you'll need to provide us with the explain and execution plans, details of your queries, tables, stats, etc.

You can always reduce the parallel threshold if you want by setting:

alter (system|session) parallel_min_time_threshold = <seconds>

"but normally optimizer fails to execute my query in parallel why?"

How are you verifying whether or not Oracle uses parallel? Again, we need concrete details to attempt to explain why it happened in your case.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

As usual very informative

Shah Firdous, February 19, 2016 - 5:59 pm UTC

Thank you so much for helping the best way.

You gave me perfect solution for my Ist question, but

Here is something I wanted to clarify:

My Table stats are upto mark no stale stats.
I am not using bind variable in this test case
It might be something related to BUG or optimizer problem.
---------------------------------------------------
Optimizer seems to be dumb, When I run query, it doesn't execute it parallel, but when I add HINT parallel, the optimiser determines the a DOP of 2 is most efficient.

Why it didn't do it first place, why should it think differently now when I use HINT Parallel 2.

Just to make a point

SQL> explain plan for
2 select * from int_transactions;
Explained

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('Plan_table','','ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3491809280
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 231M| 116G| 1179K (1)| 00:01:11 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 231M| 116G| 1179K (1)| 00:01:11 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 231M| 116G| 1179K (1)| 00:01:11 | 1 | 80 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| INT_TRANSACTIONS | 231M| 116G| 1179K (1)| 00:01:11 | 1 | 80 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------

This query table scans a table that is about 116GB, it would take about an hour in our environment. The optimiser thinks it will run in 71 seconds. As far as I can see, the DB thinks the I/O is MUCH faster than it actually is….

My Question here is : What should I do with optimizer bugs or optimizer problem, Can we really do something about bad optimizer?

*Please don't mind this query is just an example to prove optimizer issue* else y should one use select * for such a big table*


Thanks for helping.

Regards,
Shah
Connor McDonald
February 20, 2016 - 4:16 am UTC

Check out your *system* stats as well. They give a measure of how good we think your I/O system is. If it really *isnt* that good...then the optimizer will be obviously less accurate.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.