Gathering statistics and slow performance during that
614789Dec 23 2007 — edited Dec 28 2007In my application we load millions of records in a few tables in a matter of hours. There are various reports that are scehduled, queries being performaned, etc. We have a job that runs statistics every hour, we collect DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MySchema',options => 'GATHER STALE',force => TRUE);
Since we are constantly inserting, generally the jobs runs and does some stats gathering since they do get stale. The tables are partitioned by day.
When this job runs CP usage is pretty high on an average and all queries to the DB are very slow. Is there any way that we can optimize the stats gathering and also make the queries run faster.