DBMS_STATS and Locking
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: DBMS_STATS and Locking

Hybrid View

  1. #1
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    DBMS_STATS and Locking

    When does gathering statistics lock tables and prevent DML?
    I'm new at this, so forgive if this is a dumb question?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Never. The Oracle mantra is "Readers don't block writers, writers don't block readers."

    DBMS_STATS is a reader.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Now that I've had more time reading other notes/thread/books, I did find something that says when indexes are analyzed, a shared lock is put on the related table and this would prevent DMLs on that table.

    Do you concur?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    No, sounds wrong to me. Do you have a reference for that?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Yeah...
    http://www.dbasupport.com/oracle/ora9i/CBO4_1.shtml

    Towards the bottom of the page where titled "Statistics Locks?"

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think that refers only (if obscurely) to the ANALYZE INDEX ... VALIDATE STRUCTURE command, and since 9.2 (I think?) that also has an ONLINE clause which allows DML. On the face of it I believe that article is wrong in respect of analyzing indexes, and in particular "Preferably avoid all activities during the statistics generation phase" is very misleading.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Thanks for clarifying that. I'll do somemore investigating and not take that article at face value.

  8. #8
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    So let me ask you this...
    I thought that one reason to do estimate instead of compute on really large tables is to avoid long running analysis where, for example, things can be locked up.

    So if no locking occurs, what other negative affects of analyzing/getting stats have that would make me do estimates rather than compute?

  9. #9
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Slimdave,
    AskTom says with great conviction that analyze (w/o validate) does NOT lock anything up (see http://asktom.oracle.com/pls/ask/f?p...:2045859310335.

    But I still want to know, why not always do compute?
    Is the main reason simply the extra load that is put on the database during the analysis (as implied in the above AskTom article)?

    If so, then it would be acceptable to gather stats in the middle of the night when least amount of users are impacted. Yes?
    Last edited by dbbyleo; 07-27-2006 at 04:51 PM.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's a matter of trading off the resource usage of the two methods against their relative accuracy. In many cases estimating can give very good results on a very low percentage -- 1-3% for example. The best practice is to run a compute, then store the values you've gathered and compare them against reducing values of the estimate percent until you start getting an unacceptable deviation between them.

    http://oraclesponge.wordpress.com/20...lock-sampling/

    You also need to consider whether you want to keep gathering statistics very regularly. You may find that an execution plan changes for the worse at some point due to a change in statistics, so preserving old statistics may also be advisable.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width