Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Ireland
    Posts
    9

    Unanswered: gather_table_stats for set of values

    Platform: Oracle 8.1.7.4 on Solaris 8
    I am attempting to write a procedure which looks through the user_tab_modifications tables and performs a dbms_stats.gather_table_stats on any table with inserts greater than 1000. Is this possible? The reason that I am doing this is to prevent the system being hogged by unnecessary stats gathering. Is this the best way of doing it?

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Apart from where you're gathering your numbers... no - it isn't the best way.

    Table A had 1000 rows and had another 999 added. It might be worth doing a gather (let's forget Oracle's 'small table' ideas for a moment).

    Table B had 1,000,000 rows and had a 1000 added. It's probably a waste of time gathering statistics.

    Even if you change the 1000 rows to a percentage, it still isn't going to necessarily be the answer. You should analyse based on the 'demographics' of your table.

    It's a complex situation, but to look at it simply, if you have a table with a column for male/female, the ratio is likely to always stay the same (unless you open a girls school). You can analyse that index until you are blue in the face but it won't help. (that just contradicted my statement about table A above). This shows once again, it's about your specific data, in specific tables and about specific columns.

    This might explain a bit better.

    http://www.billmagee.co.uk/oracle/sqltune/index.html

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Doesn't he/she just want to gather stats on 'stale' objects?

    Someone else help me out here.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Nov 2003
    Location
    Ireland
    Posts
    9
    Thank you!
    I am initially doing a gather_schema_stats and then periodically going to do a gather_table_stats on any tables which have more than X% of rows inserted/deleted/updated.
    Would some one be able to tell me whether I would be better of doing this or gathering stats on a daily partition basis or is the 'GATHER STALE' option to one to go with.
    Oh so many options and not enough time!

Posting Permissions

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