Dears,
I have two identical databases PRODUCTION and COPY.
I have a query that is dramatically running slowly in PRODUCTION because of a wrong NESTED LOOP choice having a dramatic outer row source driving a huge amount of executions of the inner operation.
This wrong NESTED LOOP choice is due to a wrong estimation done by the CBO.
However, the same query in the COPY database has been honored with an execution plan using a HASH JOIN operation which has allowed the query to complete in an acceptable response time.
The database release is: TNS for Linux: Version 11.2.0.3.0 – Production
The statistics are exactly the same for the concerned tables and columns. Exactly the same.
The difference comes from the Estimation done when accessing the outer table. In the COPY database the estimation is approximatively good while in PRODUCTION database the estimation is equal to the always suspicious cardinality 1.
After several checks I decided to get both 10053 trace files and here what I have isolated in those trace files:
10053 of the Copy database
*** 2015-06-03 11:05:43.701
** Executed dynamic sampling query:
level : 4
sample pct. : 0.000489
actual sample size : 837
filtered sample card. : 1
orig. card. : 220161278
block cnt. table stat. : 6272290
block cnt. for sampling: 6345946
max. sample block cnt. : 32
sample block cnt. : 31
min. sel. est. : 0.00000000
** Using single table dynamic sel. est. : 0.00119474
Table: TABLEXXX Alias: TABLEXXX
Card: Original: 220161278.000000 Rounded: 263036 Computed: 263036.17 Non Adjusted: 263036.17
The estimation in the COPY is 236036 which is quite reasonable
10053 of the PRODUCTION database
*** 2015-06-03 13:39:03.992
** Executed dynamic sampling query:
level : 4
sample pct. : 0.000482
actual sample size : 1151
filtered sample card. : 0
orig. card. : 220161278
block cnt. table stat. : 6272290
block cnt. for sampling: 6435970
max. sample block cnt. : 32
sample block cnt. : 31
min. sel. est. : 0.00000000
** Not using dynamic sampling for single table sel. or cardinality.
DS Failed for : ----- Current SQL Statement for this session (sql_id=82x3mm8jqn5ah) -----
Table: TABLEXXX Alias: TABLEXXX
Card: Original: 220161278.000000 Rounded: 1 Computed: 0.72 Non Adjusted: 0.72
While in PRODUCTION the Dynamic Sampling Failed without showing the reason of this failure. And consequently, I ended up by having that 1 Cardinality estimation which has produced the NESTED LOOP plan.
Have you any idea why Oracle decided to ignore Dynamic Sampling? Is this zero filtered sample card a clue?
Best regards
Mohamed Houri