Hi, We are using version 11.2.0.4 of oracle. We are many times seeing performance issue due to change in execution plan due to missing/stale statistics. And we do have the oracle provided default stats gather job(auto optimizer stats collection) enabled. But still at some point in time of the day we found few of the partitions/tables needs additional stats collection as because they were loaded freshly and the optimizer went for bad execution path estimating zero/stale stats on them. So we want to streamline/standardize the stats gathering process across the databases.
We have many ETL jobs which loads data into the transaction tables and also reading from the transaction tables to move it out to other system, and as per the standard, we should be gathering stats after the data load so that the statistics would be up to date. And that will need to be implemented for specific objects for specific load which will need deep analysis and time considering thousands of ETL jobs and also other application loading data 24/7 through UI also. To avoid this work, team is suggesting to have a job created which will do something as below logic. And will schedule it to run hourly once or once in 2hrs etc.
I had few questions around this
1)if this above approach is okay or anything missing? As because i was thinking the deafult stats gathering job might be doing the same thing i.e first finding out the stale/empty objects and gathering stats on those object. So then should we just increase the frequency of the default stats gather job, rather creating our own stats gather job?
2)If the stats gather of two table overlap. i.e say the obejct which is gathered using auto stats job is also getting gathered by this manual job at same time. Will there be any locking/queueing issue observed or any other side effects like failuer etc?
3) I know that stats gather can make the query hard parse , so i was wondering to what extent it will impact the performance, considering we have mostly the environment are combination of data warehousing+OLTP type? Or is it true that the method is okay, we just have to decrease the frequency of stats collection.. may be once in 5-6hrs?
Begin
Open cursor with required functional SCHEMA list(user1,user2,user3);
loop
dbms_stats.gather_schema_stats(owner=>cursor.user1,objlist=>V_ALLSTALEOBJECTS,options=>'list_stale');
Gather stats in loop for all the objects in V_ALLSTALEOBJECTS;
similarly...
dbms_stats.gather_schema_stats(owner=>cursor.user1,objlist=>V_ALLEMPTYOBJECTS,options=>'list_empty');
Gather stats in loop for all the objects in V_ALLEMPTYOBJECTS;
end loop;
/
Edited- Want to correct, for some environment we have the oracle provided default stats gather job(auto optimizer stats collection) enabled but for some not, and in those we have job to gather stats on specific objects at a particular time interval.