DBMS_STATS options

Quote:

---

Originally Posted by marist89

You can read about my basic algorithm at http://marist89.blogspot.com/2005/06...sstats_14.html.

---

Mr. Genius, do you have any tables where you want to generate histograms, I didn't see it mentioned in your blog?
If so, what criteria are you using to determine the need for the histograms.
Thanks.
:D

Quote:

---

Originally Posted by dbtoo

Mr. Genius, do you have any tables where you want to generate histograms, I didn't see it mentioned in your blog?
If so, what criteria are you using to determine the need for the histograms.
Thanks.
:D

---

Histograms are a special case. Most times, you know what tables need histograms and how many buckets. Same deal, if the table/index reports as stale, you bounce it off a table of know tables that need histograms.

Thanks again guys, looks like I will be taking on this database in the future so there will be plenty of stupid questions on the horizon for me.
This is a lot bigger and more complex then anything I have handled before, well...short of Dave's political views.

If you want to keep it in PL/SQL then just adapt the following ...

Code:

---

Begin
  For x in (Select table_name from user_tables minus
            select table_name from excluded_tables)
  Loop
      DBMS_STATS.Gather_Table_Stats(user,x.table_name);
  End Loop;
End;
/

---

You can obviously put whatever SQL statement you like in the cursor, to exclude tables over a certain size, or those with a particular prefix (eg. "LKP%") or partitioned tables etc.

I sometimes use a quick lookup table in the schema, i.e.,

Code:

---

create table lookup_table (table_to_analyze varchar2(30));

insert into lookup_table (....the list of tables you want to analyze...)


declare

cursor cur_tabs is select table_to_analyze from lookup_table;

BEGIN

  for rec_tabs in cur_tabs loop

      DBMS_STATS.gather_table_stats (ownname          => 'WHATEVER',
                                    tabname          => rec_tabs.table_to_analyze,
                                    estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE,
                                    method_opt      => 'for all columns size auto',
                                    cascade          => true
                                    );
  end loop;
END;
/

---