Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2006
    Posts
    74

    Unanswered: DBMS_STATS.gather_database_stats

    Hi

    Can DBMS_STATS.gather_database_stats increase performance of a database, and if so how and what does it do?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Are you unwilling or incapable to Read The Fine Manual?
    DBMS_STATS
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2006
    Posts
    74
    How can I run stats on a single table? I have tried this but it doesnt seem to work.

    exec dbms_stats.set_table_stats(ownname ='JANE',tabname='AAFRICRFINRP'

  4. #4
    Join Date
    Jan 2006
    Posts
    74
    Not to worry figured it out

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    V10+ Oracle automagically keep statistics current
    Code:
    SQL> select job_name from DBA_SCHEDULER_JOBS;
    
    JOB_NAME
    ------------------------------
    AUTO_SPACE_ADVISOR_JOB
    GATHER_STATS_JOB
    FGR$AUTOPURGE_JOB
    PURGE_LOG
    RLM$SCHDNEGACTION
    RLM$EVTCLEANUP
    MGMT_STATS_CONFIG_JOB
    MGMT_CONFIG_JOB
    Guess which job does it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2006
    Posts
    74
    Cant wait to upgrade!!!

  7. #7
    Join Date
    Jan 2006
    Posts
    74
    I'm trying to gather stats on a schema, running a 9.0.1 DB we had a DBA consultant come in and told us to run the below but just getting errors can someone please help.


    execute dbms_stats.gather_schema_stats(ownname => ‘AGRESSO’, estimate_percent => 20, method_opt => ‘FOR ALL INDEXED cOLUMNS SIZE 10’, cascade => TRUE);


    Error is

    ERROR at line 1:
    ORA-06550: line 1, column 96:
    PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
    ( - + case mod not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall merge time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Both of these work for us

    Code:
    begin
      dbms_stats.gather_schema_stats(ownname=> 'AGRESSO' , 
                                     options=> 'GATHER AUTO');
    end;
    
    /** to make sure all objects are re-analyzed, whether or not Oracle thinks they should be  **/
    
    begin
      dbms_stats.gather_schema_stats( ownname=> 'AGRESSO' , 
                                      cascade=> DBMS_STATS.AUTO_CASCADE, 
                                      estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, 
                                      degree=> null, 
                                      no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, 
                                      granularity=> 'AUTO', 
                                      method_opt=> 'FOR ALL COLUMNS SIZE AUTO', 
                                      options=> 'GATHER');
    end;
    We're on 10.1.0.4
    --=cf

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >we had a DBA consultant come in and told us to run the below but just getting errors

    So ask the DBA consultant what is wrong with his code.
    I no longer have any V9 DBs.
    Have you tried to actually Read The Fine Manual to see what is valid for "method_opt " for V9?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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