Re: CBO estimations

Of course you are right. I am aware about that 0.25% for

• selectivity of column between unknown1 and unknown2
• selectvity of column betwen :bind1 and :bind2

I have now to get the arithmetic done by the CBO to get its 206K estimation in this particular case

Best regards

Mohamed Houri

2014-12-05 11:50 GMT+01:00 Martin Preiss <mtnpreiss_at_gmx.de>:

> Mohamed,
>
> 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>
>
>
>

-- 

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