CBO estimations

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Fri, 5 Dec 2014 10:34:40 +0100
Message-ID: <CAJu8R6i+0RG2OCsf7zQ_Pq3PVzo-=_-EwHvU-uBsPSFOrP0bEw_at_mail.gmail.com>

Dears,

 I have a query against a list partitioned table resembling to this

explain plan for

SELECT     count(1)

FROM smho_log_entry

WHERE event_type_code = 'Tracking'

AND log_type_code IN (0, 20 )

AND day_in_year =
to_number(TO_CHAR(to_date(:from_dt_DDMMYYYY_HH24MI,'DD/MM/YYYY hh24:mi:ss'),'DDD'))

;

---

| Id | Operation | Name | Rows | Pstart| Pstop |

---

| 0 | SELECT STATEMENT | | 1 | | |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | PARTITION LIST SINGLE| | 206K| KEY | KEY |
|* 3 | TABLE ACCESS FULL | SMHO_LOG_ENTRY | 206K| KEY | KEY |

---

Predicate Information (identified by operation id):

---

   3 - filter("EVENT_TYPE_CODE"='Tracking' AND ("LOG_TYPE_CODE"=0 OR "LOG_TYPE_CODE"=20) AND "DAY_IN_YEAR"=TO_NUMBER(TO_CHAR(TO_DATE(:FROM_DT_DDMMYYYY_HH24MI,'DD/MM/YYYY               hh24:mi:ss'),'DDD')))

The partition key (day_in_year) is a virtual column defined as follows

day_in_year = generated always as (TO_NUMBER(TO_CHAR ("MSG_TIMESTAMP",'DDD'))) VIRTUAL The above query was performing very well until one of the developers realises that it might return wrong results if the table contains more than 1 year of data. He decided to add an extra where clause which will not change the result at all as shown below (in yellow):

explain plan for

SELECT     count(1)

FROM smho_log_entry

WHERE event_type_code = 'Tracking'

AND log_type_code IN (0, 20 )

AND day_in_year =
to_number(TO_CHAR(to_date(:from_dt_DDMMYYYY_HH24MI,'DD/MM/YYYY hh24:mi:ss'),'DDD'))

AND msg_timestamp BETWEEN to_date(:from_dt_DDMMYYYY_HH24MI,'DD/MM/YYYY hh24:mi:ss')

                   AND     to_date(:to_dt_DDMMYYYY_HH24MI,'DD/MM/YYYY

hh24:mi:ss')

;

---

| Id | Operation | Name |
Rows | Pstart| Pstop |

---

| 0 | SELECT STATEMENT | |
1 | | |

| 1 | SORT AGGREGATE | |
1 | | |

|*  2 |   FILTER                             |
 |       |       |       |



|   3 |    PARTITION LIST SINGLE             |                       |

517 | KEY | KEY | |* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| SMHO_LOG_ENTRY | 517 | KEY | KEY |

|*  5 |      INDEX RANGE SCAN                | SMHO_LGEN_MSG_TIME_NI |

11 | KEY | KEY |

---

Predicate Information (identified by operation id):

---

   2 - filter(TO_DATE(:FROM_DT_DDMMYYYY_HH24MI,'DD/MM/YYYY hh24:mi:ss')<=TO_DATE(:TO_DT_DDMMYYYY_HH24MI,'DD/MM/YYYY

              hh24:mi:ss'))

   4 - filter("EVENT_TYPE_CODE"='Tracking' AND ("LOG_TYPE_CODE"=0 OR "LOG_TYPE_CODE"=20))    5 - access("MSG_TIMESTAMP">=TO_DATE(:FROM_DT_DDMMYYYY_HH24MI,'DD/MM/YYYY hh24:mi:ss') AND

              "MSG_TIMESTAMP"<=TO_DATE(:TO_DT_DDMMYYYY_HH24MI,'DD/MM/YYYY hh24:mi:ss'))

filter("DAY_IN_YEAR"=TO_NUMBER(TO_CHAR(TO_DATE(:FROM_DT_DDMMYYYY_HH24MI,'DD/MM/YYYY hh24:mi:ss'),'DDD')))

And the problem starts.

The Real Time SQL monitoring is showing the same wrong estimation (11,517) while the Actual rows are (1M, 284K) respectively and the majority of time and resource consumption is done at those 5 and 4 operations.

I tried to figure out why the estimations in the new query are wrong but was not able to achieve this goal very quickly. I’ve added a full hint against SMHO_LOG_ENTRY table and sent this new query into production.

Since then I am trying to solve the problem at a statistics level.

The index definition is

INDEX SMHO_LGEN_MSG_TIME_NI on SMHO_LOG_ENTRY(msg_timestamp)local;

The switch to the bad index access plan seems to occur only when using bind variable (and the application is using bind variable in Production). With literals the full table scan seems to be always selected.

Today run of both queries with a from date and an end date of 26/11/2014 00:0:00 and 27/11/2014 00:0:00 gives the same number of records 284,469

I have used those two dates because they are the two dates that appears into the SQL real time monitoring report as Binds

Can someone give me a clue where those 11 and 517 index and table estimations are coming from? I’ve tried many checks using table/index/columns statistics but I have not been able to approach these two values

Best regards

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 05 2014 - 10:34:40 CET