Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, krishna.

Asked: February 25, 2020 - 8:33 am UTC

Answered by: Chris Saxon - Last updated: February 25, 2020 - 4:16 pm UTC

Category: Database Administration - Version: 12.1.2.0

Viewed 100+ 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 we 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 );

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.