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>
