Thanks for the question, krishna.
Asked: February 25, 2020 - 8:33 am UTC
Last updated: February 25, 2020 - 4:16 pm UTC
Version: 12.1.2.0
Viewed 1000+ times
You Asked
Hi Team ,
I have table named "CALL_LOGG" with ~1M record's , table is partitioned with range-hash
we have index in one of column named as logdate which is having date datatype .
most of time i see query using major filter as logdate is unable to make use of it's index .
Further, i found that this is due to out of bound issue from 10053 trace .
however, i want to know in case if i collect stats of table which is in our case "CALL_LOGG" , on let us say today , performance wise it will be executed fine for today and from tomorrow onwards plan for a table gets changed, again it will start hitting out of bound with column , gathering stats help us to retain things for normal time, but i had become daily stuff to deal with .
any other way can be used to tackle this ?
Regards,
Krishna
and Chris said...
The default stats gathering job should pick this up. So you shouldn't need to do this manually.
But this only picks up tables when the database more than STALE_PERCENT of rows have changed. Which defaults to 10%.
You can get this to pickup the table more frequently by lowering this value for the table:
exec dbms_stats.set_table_prefs ( user, 'TABLE_NAME', 'STALE_PERCENT', 1 );