DBMS_STATS options
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: DBMS_STATS options

  1. #1
    Join Date
    Jan 2001
    Posts
    3,131

    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 remember when this place was cool.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    Thanks dave,
    I can generate it through SQL plus that way, but it is a bit tedious.
    I remember when this place was cool.

  4. #4
    Join Date
    Jan 2002
    Location
    NC, USA
    Posts
    24
    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
    OCP DBA 8, 8i, 9i, 10g

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  6. #6
    Join Date
    Jan 2002
    Location
    NC, USA
    Posts
    24
    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
    Last edited by danromeo; 06-29-2005 at 04:24 PM.
    OCP DBA 8, 8i, 9i, 10g

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    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?
    I remember when this place was cool.

  8. #8
    Join Date
    Jan 2002
    Location
    NC, USA
    Posts
    24
    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

    ....
    Attached Files Attached Files
    OCP DBA 8, 8i, 9i, 10g

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    I'll work on it, thanks!
    I remember when this place was cool.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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