Role of High_value and Low_value column of dba_tab_columns while generating execution plan.

Our support team called us, the Oracle DBA team, for a problem. The problem was a program, was running OK before but it runs very slowly now, but they said that nothing has changed about the program.Previously query used to take 2 millisec and now taking one hour to execute.
Basic questions araised. what caused performace of query to degrade.
I gathered query plan which was executing at that moment.

Current SQL statement :
-------------------------------------------
SELECT m.corr_acc_no, m.sub_acc_no "Accoun t No.", m.short_code "Short Code", m.stmt_date "STmt. Date", m.status "Status", trunc(m.sys_entry_date) "Load Date", count(*) "Entries"
FROM SCRTLMAPP.message_header m
WHERE m.sys_entry_date >= trunc(sysdate)-4 AND m.status >= 100
GROUP BY m.corr_acc_no, m.sub_acc_no, m.short_code, m.stmt_date, m.status, trunc(m.sys_entry_date)
ORDER BY 1,2,3,5
--------------------------------------------+------------------------------------------+
: Id : Operation : Name : Rows: Bytes : Cost : Time :
--------------------------------------------+-----------------------------------------+
: 0 : SELECT STATEMENT : : : : 164K : :
: 1 : SORT GROUP BY : : 2 : 92 : 164K : 00:34:34:
: 2 : TABLE ACCESS FULL : MESSAGE_HEADER: 2 : 92 : 164K : 00:34:34:
--------------------------------------------+------------------------------------------+
Predicate Information:
----------------------
2 - filter(("M"."STATUS">=100 AND "M"."SYS_ENTRY_DATE">=TRUNC(SYSDATE@!)-4))
--
With Sql_id we found previous query plan.
----------------------------------------------------------------------------------------------
Id :Operation : Name :Rows :Bytes :Cost :Time
--------------------------------------------------------------------------------------------+
0 :SELECT STATEMENT : : : : 7 : :
1 :SORT GROUP BY : : 1 : 46 : 7 : 00:00:01 :
2 :TABLE ACCESS BY INDEX ROWID :MESSAGE_HEADER :1: 46: 6: 00:00:01
3 :INDEX SKIP SCAN :MESSAGE_HEADERIXB :1 : : 5 :00:00:01
---------------------------------------------------------+-----------------------------------+
Predicate Information:
---------------------
-2 - filter("M"."SYS_ENTRY_DATE">=TRUNC(SYSDATE@!)-4)
-3 - access("M"."STATUS">=100)3 - filter("M"."STATUS">=100)

Query was previously using Index Skip scan and Currently it was using Full table Scan.
Question arises what caused Change?
We found that Tables stats was gather pervious day.So I have revert back the stats. I found Query started using indexes. So i generated 10053 trace of the query with different stats that generated different Plans.

So i generated 10053 trace of the query with different stats that generated different Plans.
Query using Index (Part of Trace File)
------------------------------------------------------------------
*****************************SYSTEM STATISTICS INFORMATION
***************************** Using NOWORKLOAD Stats
CPUSPEED: 1043 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
--------------------------------------------------------------------
Table Stats::
Table: MESSAGE_HEADER Alias: M
#Rows: 9538250 #Blks: 762025 AvgRowLen: 162.00
Index Stats::
Index: MESSAGE_HEADERIXA Col#: 4
LVLS: 2 #LB: 50790 #DK: 9099770 LB/K: 1.00 DB/K: 1.00 CLUF: 351680.00
Index: MESSAGE_HEADERIXB Col#: 14 26 4
LVLS: 2 #LB: 41930 #DK: 9494790 LB/K: 1.00 DB/K: 1.00 CLUF: 371550.00
Index: MESSAGE_HEADERIXC Col#: 14 16 9
LVLS: 2 #LB: 26160 #DK: 4865 LB/K: 5.00 DB/K: 632.00 CLUF: 3078500.00
Index: MESSAGE_HEADERIXD Col#: 3 4
LVLS: 2 #LB: 41540 #DK: 9966350 LB/K: 1.00 DB/K: 1.00 CLUF: 344500.00
Index: MESSAGE_HEADERIXZ Col#: 4 1 26 14 16
LVLS: 3 #LB: 144050 #DK: 9477320 LB/K: 1.00 DB/K: 1.00 CLUF: 457530.00
Index: MESSAGE_HEADER_IND_KEY Col#: 1 2 3 4
LVLS: 3 #LB: 75470 #DK: 9490520 LB/K: 1.00 DB/K: 1.00 CLUF: 3446280.00
Column (#9): CORR_ACC_NO(NUMBER)
AvgLen: 7.00 NDV: 3853 Nulls: 0 Density: 8.2645e-04 Min: 0 Max: 100077909
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 136
Column (#1): SUB_ACC_NO(VARCHAR2)
AvgLen: 16.00 NDV: 3441 Nulls: 0 Density: 0.0011136
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 158
Column (#24): SHORT_CODE(VARCHAR2)
AvgLen: 4.00 NDV: 25 Nulls: 0 Density: 5.2504e-08
Histogram: Freq #Bkts: 25 UncompBkts: 5359 EndPtVals: 25
Column (#26): STATUS(NUMBER)
AvgLen: 3.00 NDV: 14 Nulls: 0 Density: 0.071429 Min: -1 Max: 42
Histogram: HtBal #Bkts: 10 UncompBkts: 10 EndPtVals: 3
Column (#2): STMT_DATE(DATE)
AvgLen: 8.00 NDV: 106 Nulls: 0 Density: 5.2504e-08 Min: 2453776 Max: 2454936
Histogram: Freq #Bkts: 106 UncompBkts: 5359 EndPtVals: 106
Column (#30): SYS_ENTRY_DATE(DATE)
AvgLen: 8.00 NDV: 635828 Nulls: 0 Density: 3.1307e-06 Min: 2453305 Max: 2454933
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Access Path: TableScan
Cost: 167722.77 Resp: 167722.77 Degree: 0
Cost_io: 166695.00 Cost_cpu: 12866550691
Resp_io: 166695.00 Resp_cpu: 12866550691
kkofmx: index filter:"M"."STATUS">=100
kkofmx: index filter:"M"."STATUS">=100
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (skip-scan)
SS sel: 5.2421e-08 ANDV (#skips): 3
SS io: 3.00 vs. table scan io: 166695.00
Skip Scan chosen Access Path: index (SkipScan)
Index: MESSAGE_HEADERIXB resc_io: 6.00 resc_cpu: 44032
ix_sel: 5.2421e-08 ix_sel_with_filters: 5.2421e-08
Cost: 6.00 Resp: 6.00 Degree: 1
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (skip-scan)
SS sel: 5.2421e-08 ANDV (#skips): 9535627
SS io: 9535627.00 vs. table scan io: 166695.00
Skip Scan rejected
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXZ
resc_io: 144054.00 resc_cpu: 2921337020
ix_sel: 1 ix_sel_with_filters: 5.2421e-08 Cost: 144325.45
Resp: 144325.45 Degree: 1


We can see that Oracle using skip scan.
It used because "M"."STATUS">=100 is out of range of Min=-1 and Max=42 value of Status columns.

Query using Full table scan(Part of Trace File)
------------------------------------------------------------------------------------------
Column (#26): STATUS(NUMBER)
AvgLen: 3.00 NDV: 18 Nulls: 0 Density: 0.071429 Min: -1 Max: 1103
Histogram: HtBal #Bkts: 10 UncompBkts: 10 EndPtVals: 3
Column (#30): SYS_ENTRY_DATE(DATE)
AvgLen: 8.00 NDV: 635828 Nulls: 0 Density: 3.1307e-06 Min: 2453305 Max: 2454933
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Access Path: TableScan
Cost: 167800.75 Resp: 167800.75 Degree: 0
Cost_io: 166695.00 Cost_cpu: 13842794223
Resp_io: 166695.00 Resp_cpu: 13842794223
kkofmx: index filter:"M"."STATUS">=100
kkofmx: index filter:"M"."STATUS">=100
Access Path: index (skip-scan)
SS sel: 0.071429 ANDV (#skips): 723680
SS io: 723680.00 vs. table scan io: 166695.00
Skip Scan rejected
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXB
resc_io: 73258.00 resc_cpu: 3619052852
ix_sel: 1 ix_sel_with_filters: 0.071429
Cost: 73587.41 Resp: 73587.41 Degree: 1
Access Path: index (skip-scan)
SS sel: 0.071429 ANDV (#skips): 9535627
SS io: 9535627.00 vs. table scan io: 166695.00
Skip Scan rejected
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXZ
resc_io: 196172.00 resc_cpu: 4557617793
ix_sel: 1 ix_sel_with_filters: 0.071429
Cost: 196576.38 Resp: 196576.38 Degree: 1

We can see that Oracle rejected skip scan and selected full table scan.
It used Full Table scan because "M"."STATUS">=100 is in range of Min=-1 and Max=1103 value of Status columns.


We know why plan had changed.
So i tried to query the status column to find max value of status .It turned out to be 42.
Question araised how come max value 1103 was gathered during stats.End user purged data after gathering stats.

Min and Max value in 10053 is nothing but high_value and low_value of dba_tab_columns/dba_tab_col_statistics.
we can also find that density and cardinality is same in both the case.
So I could conculde that Min and Max value play a important role in generaing a excution plan.