Re: CBO estimations

I may be wrong, but that looks like the 5% default value for ranges with bind variables:

SQL> select 206000 * .05 * .05 from dual;

206000*.05*.05

            515

The details are (of course) mentioned in Cost Based Oracle.

Regards

Martin

Am 05.12.2014 um 10:34 schrieb Mohamed Houri:
> 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