DBMS_STATS options

Howdy all you politically correct boys and girls;

Is there any way to run dbms_stats at the schema level and omit a few HUGE partitioned tables.

What we have now is a script that analyzes all of the tables for this schema, this works fine BUT.. we are adding another 40 or so tables to this schema and now have to keep updating this script.

Essentially we would like to avoid this and just have the flexability to analyze the monster tables at other times.

I do not see any options online, a simple WHERE clause would work if it existed.


HP/UX 11
Oracle 9.2.0.6

i dont think there is an exceptions kinda thing, but you can do a little loop to select the table name from user_tables with a where clause then pass the table name to dbms_stats

Thanks dave,
I can generate it through SQL plus that way, but it is a bit tedious.

ho rm analyze.tmp.sql
spool analyze.tmp.sql
set heading off
select 'prompt >processing: '
||table_name
||chr(13)
||'select to_char(sysdate,''MM/DD/YY HH24:MI:SS'') from dual;'
||chr(13)
||'execute dbms_stats.gather_table_stats(ownname => user, tabname => '''||table_name||''', degree => 4, cascade => true);'
||chr(13)
||'select to_char(sysdate,''MM/DD/YY HH24:MI:SS'') from dual;' from user_tables where table_name<>'BIG_TABLE';
spool off
@analyze.tmp.sql
ho rm analyze.tmp.sql

Quote:

---

Originally Posted by danromeo

ho rm analyze.tmp.sql
spool analyze.tmp.sql
set heading off
select 'prompt >processing: '
||table_name
||chr(13)
||'select to_char(sysdate,''MM/DD/YY HH24:MI:SS'') from dual;'
||chr(13)
||'execute dbms_stats.gather_table_stats(ownname => user, tabname => '''||table_name||''', degree => 4, cascade => true);'
||chr(13)
||'select to_char(sysdate,''MM/DD/YY HH24:MI:SS'') from dual;' from user_tables where table_name<>'BIG_TABLE';
spool off
@analyze.tmp.sql
ho rm analyze.tmp.sql

---

HUH?

SQL no worky

are you running on windows?

if yes you need to change rm with del

you need to create a sql file with that content (an.sql)

then execute that script:

sqlplus user/pass@host @an.sql

Quote:

---

Originally Posted by danromeo

are you running on windows?

---

I tried it in both environments.
I also tried just the SQL in SQL plus.
Did you get it to run?

C:\Home\Romeo\oracle\sql>sqlplus romeo/romeo @analyze.sql

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jun 29 16:28:47 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

rm: cannot remove `analyze.tmp.sql': No such file or directory


select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'A', degree =>
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'A1', degree =>
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'A2110', degree
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'CUST_INFO', de
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'EXCEPTIONS', d
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'MLOG$_MY_OBJEC
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'MY_OBJECTS', d
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'MY_OBJECTS_MV'
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'ST', degree =>
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'STATES', degre
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'T', degree =>
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'T1', degree =>

12 rows selected.

>processing: A

06/29/05 16:28:48


PL/SQL procedure successfully completed.


06/29/05 16:28:48

>processing: A1

06/29/05 16:28:48


PL/SQL procedure successfully completed.


06/29/05 16:28:48

>processing: A2110

06/29/05 16:28:48


PL/SQL procedure successfully completed.


06/29/05 16:28:50

....

I'll work on it, thanks!

I just rewrote my entire statistics gathering process. Basically what I do in this case is I have a table of owner/tablenames that want to exclude from analyzing. I use dbms_stats.gather_database_stats(options=>'LIST STALE') to get a list of objects to analyze. I then bounce those against my table that stores the objects I want to exclude to decide if I need to analyze the table.

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

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;
/

---