Well, it depends on how your application is built.
I've seen poorly written applications that did not parse once / execute many times, but instead, kept closing and re-submitting the same cursor which led to a lot of parses. In that case, the dbms_stats often caused library cache locks on this application, because of the constant pressure on the library cache. The application was soft parsing like crazy, while dbms_stats kept invalidating dependent cursors, which actually led to a lot of hard parsing and hence latching.
So, depending on you much parse you're gonna do, it won't cause any bottlenecks. However if you are gonna have lots of parses on cursors dependent on the partition you will be analyzing, it's possible you see some latch serialization issue.
There is also the option of gathering stats as pending and making it "live" afterwards if you're on 11g. Also gather stats with NO_INVALIDATE which could alleviate concurrency problem.
I think he meant doing processing/queries on the partition while statistics are gathered on it ?
in Oracle readers do not block writers & writers do not block readers.
"bad" statistics do not result in SQL to throw error.
"bad" statistics may result in SQL running slower when compared to running using "good" statistics.
Read how to gather incremental stats in 11g for partition table
Stats gathering process doesn't lock any operation for any session.But if you use Auto sampling , Oracle reads lot of data from the table to gather stats related information and develop histogram.If you able to follow incremental stats , it will minimize time and resource requirement to collect stats faster.
i have got 5 Process writing into date partitioned table every day at night at various times..My question was if i collect stats of the same table through two different process will it lock the Dictionary tables or fail the process if the dictionary tables are locked?
Since the process are executing at different times this may not happen, but since these processes are trigered when incoming files for each process are received.So there could be a chance that two process start collecting stats together.
My question was if i collect stats of the same table through two different process will it lock the Dictionary tables or fail the process if the dictionary tables are locked?
And my questions are the same as I posted earlier:
What? Why would you do that?
What possible reason/benefit could there be in 'collecting same table partition statistics' at the same time?
If you want to collect stats then just collect stats - you don't need to collect stats of the same table through two different process'.