Optimizer v/s Data

In this blog, we would discuss the issue that is normally faced, but gets unnoticed, due to the data entered into the system, may be because of the application bug. Hence, the title of this blog as "Optimizer v/s Data".

Let us visit the query and we would paste only a part of that query that is worth discussing here.

and nsup_date_time between to_date(to_char(sysdate,'DD-MON-YYYY')' ''00:00:00','DD-MON-YYYY HH24:MI:SS')and to_date(to_char(sysdate,'DD-MON-YYYY')' ''23:59:59','DD-MON-YYYY HH24:MI:SS')

This is an indexed column on mt.m_nagn table and the original query as well as the modified query with an ORDERED hint uses this Index. For my curiosity and simplicity of the query, I modified this condition as :

nsup_date_time between trunc(sysdate) and trunc(sysdate+1)-1/86400;

Surprisingly, after this change, the query planned changed and optimizer ceased to use this index and opted for Full table scan. This impacted the performance with significant increase in the elapsed time (from less than a seconds to 10 minutes).

Why did the optimizer behave abnormally after this small change in the query ? Both the changes will not alter the amount of data to be fetched as both these are requesting data for a day, then, can it be an optimizer bug ?

So, for testing purpose, I modified the query to make it a simple single table query with a single predicate.

explain plan for
select * from mt.m_nagnwhere nsup_date_time between to_date(to_char(sysdate,'DD-MON-YYYY')' ''00:00:00','DD-MON-YYYY HH24:MI:SS')and to_date(to_char(sysdate,'DD-MON-YYYY')' ''23:59:59','DD-MON-YYYY HH24:MI:SS');

Explained.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
-------------------------------------------------------------------------------

0 SELECT STATEMENT 28401 2523K 10507
* 1 FILTER
2 TABLE ACCESS BY INDEX ROWID M_NAGN 28401 2523K 10507
* 3 INDEX RANGE SCAN M_NAGN_MK2 51123 149
-------------------------------------------------------------------------------

explain plan for
select * from mt.m_nagnwhere nsup_date_time between trunc(sysdate) and trunc(sysdate+1)-1/86400;

Explained.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 435K 37M 14616
* 1 FILTER
* 2 TABLE ACCESS FULL M_NAGN 435K 37M 14616
--------------------------------------------------------------------

At the time of writing this blog, since the table was analyzed, the statistics of this table changed. Hence, the cost and cardinality of the Index / Full table scan steps will not match as compared to my previous blog.

Seeing both the plans, the query with original predicate does an index-scan whereas modified goes for a Full Table Scan.

As mentioned in my previous blog, the cardinality calculation of the original predicate is :
Nrows * 0.0025 = 11360572*0.0025 = 28401.43 = 28401. This matches the cardinality of the Table Access via rowid step. Optimizer assumes that 28401 rows will be fetched and hence opts for an Index Scan.

With the modified query, based on the predicate, optimizer assumes that 435k rows will be fetched and hence feels FTS being a better and viable option than an Index Scan. The calculation made by the optimizer is as under :

nsup_date_time between trunc(sysdate) and trunc(sysdate+1)-1/86400.

This can be written as :
nsup_date_time >= trunc(sysdate) and nsup_date_time <= trunc(sysdate+1)-1/86400.

Now, since trunc(sysdate+1) is converted into bind variable, the selectivity for this predicate will be 5%. For trunc(sysdate), the calculation takes into account low_value and high_value. This is interesting. The query from dba_tab_column reveals some interesting fact about the data.

select owner, num_rows, blocks from dba_tables
where table_name='M_NAGN';

OWNER NUM_ROWS BLOCKS
--------- --------------------- ----------
MT 11360572 151955

select num_distinct, num_nulls, density, get_date(low_value) "LOW",
get_date(high_value) "HIGH" from dba_tab_columns
where table_name='M_NAGN'
and column_name='NSUP_DATE_TIME';

NUM_DISTINCT NUM_NULLS DENSITY LOW HIGH
------------ ---------- ---------- ----------- -----------
76637 0 .000013049 16-JAN-2003 03-APR-2024

The high_value in the table entered is '03-APR-2024' and there were around 7 rows with this date, rest all the values were of current year (infact, current month). Hence, the calculation for this is :

SQL> select (to_date('03-Apr-2024 14:00:00','DD-Mon-YYYY HH24:MI:SS')-sysdate) "Req.Range" from dual;

Req.Range
----------
5937.938

SQL> select (to_date('03-Apr-2024 14:00:00','DD-Mon-YYYY HH24:MI:SS') - to_date('16-Jan-2003 22:00:00','DD-Mon-YYYY HH24:MI:SS')) "Available Range" from dual;

Available Range
---------------
7747.66667

Data to be fetched is 5938 days out of 7748 available days, and this is
= 5938/7748*1/ndv*nrows = 5938/7748*1/76637*11360572= 8706792.08
Total Cardinality is 8706792.08 * 5/100 = 435339.604 = 435K

It can be seen that any wrong value into a column can change the optimizer calculation of cardinality and thus can impact the performance. This entry of the future date caused this behaviour and could have been rectified by either correcting this entry or leaving it as NULL.

There are two fixes for this issue :

1. Index Hint. This would have improved the performance of the query with drastically low logical i/o's but would have come out as a BUG in the optimizer.

2. Update the Data to reflect correct date or leave these entries as NULL (provided the column is not defined as not null).

For me, it is an Application BUG in the code.

Again, I will reiterate, that optimizer is a piece of code and it has to work on the inputs provided to it. If the underlying input has an issue, the output returned by the optimizer will also be an issue. Hence, it is always important to know the underlying data, by way of which, a query performance can be improved by either using a HINT or fixing the underlying data.