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

Table partition Statistics

user545707 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    >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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

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

  • 5. Re: Table partition Statistics
    sb92075 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    >.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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

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

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points