10 Replies Latest reply: Jan 25, 2014 4:35 PM by himmy RSS

    Table partition Statistics

    user545707

      Hi,

       

      We have a table partitioned by Date, in which data is inserted by many processes throughout the night.

      Statistics has to be collected on the table after each processing is completed as the table will be read by another process.

       

      Is there is chance of table locking or dictionary table locking if multiple processing are collecting same table partition statistics as the same time?

        • 1. Re: Table partition Statistics
          sb92075

          >Is there is chance of table locking or dictionary table locking if multiple processing are collecting same table partition statistics as the same time?

          Error would be thrown if any problem occurred.

          • 2. Re: Table partition Statistics
            rp0428
            Is there is chance of table locking or dictionary table locking if multiple processing are collecting same table partition statistics as the same time?

            What? Why would you do that?

             

            What possible reason/benefit could there be in 'collecting same table partition statistics' at the same time?

            • 3. Re: Table partition Statistics
              Alvaro

              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.

              • 4. Re: Table partition Statistics
                Alvaro

                I think he meant doing processing/queries on the partition while statistics are gathered on it ?

                • 5. Re: Table partition Statistics
                  sb92075

                  Alvaro wrote:

                   

                  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.

                  • 6. Re: Table partition Statistics
                    ABOracle

                    Read how to gather incremental stats in 11g for partition table

                     

                     

                     

                    <<http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf>>

                     

                     

                     

                     

                     

                     

                    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.

                     

                     

                     

                     

                     

                    Thanks

                    • 7. Re: Table partition Statistics
                      user545707

                      Thanks All...

                       

                      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.

                      • 8. Re: Table partition Statistics
                        sb92075

                        >.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?

                        no

                        • 9. Re: Table partition Statistics
                          rp0428
                          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'.

                          • 10. Re: Table partition Statistics
                            himmy

                            Well its an extra overhead of collecting table stats through two different process

                            it doesn't matter how many times yo are collecting stats