Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Unanswered: DBMS_STATS.GATHER_TABLE_STATS Slowliness ...

    Hi !!
    I have a Database in Oracle 8.1.7.4.1., wich has become very large ... It currently ocupies 6 Gigas, with 3 years of continuous work...
    Two weeks ago, I've made for the first time the Analyze instruction, with the next statement:

    DBMS_STATS.GATHER_TABLE_STATS (Owner_DB, table_X);

    I've made it for all tables in my Database.
    But, since the day immediately after, I've been watching that the access time to the Data, has become very slower ...

    Is this a problem ?? Is this normal to happen ?? Do I have to make any other instructions ??

    Thanx,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    instead use
    DBMS_STATS.GATHER_SCHEMA_STATS (
    ownname VARCHAR2,
    estimate_percent NUMBER DEFAULT NULL,
    block_sample BOOLEAN DEFAULT FALSE,
    method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
    degree NUMBER DEFAULT NULL,
    granularity VARCHAR2 DEFAULT 'DEFAULT',
    cascade BOOLEAN DEFAULT FALSE);
    and be sure to set CASCADE to TRUE so statistics for indexes are also collected.

  3. #3
    Join Date
    Dec 2003
    Posts
    76
    Thank you very much ...

    I am running that Package right now ... Hope it works, because my access times to Tables, and specially, to Views were too much slower than before ...

    For what I've understand, these Package, will gather Statistics from all objects in the Database, instead of the one I ran before (DBMS_STATS.GATHER_TABLE_STATS) wich gather only Stats from Tables, right ??
    Having Stats for all objects of the Database (including Views) will make all instructions given by me to the Database run smoother and quicker, right ??

    Originally posted by anacedent
    instead use
    DBMS_STATS.GATHER_SCHEMA_STATS (
    ownname VARCHAR2,
    estimate_percent NUMBER DEFAULT NULL,
    block_sample BOOLEAN DEFAULT FALSE,
    method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
    degree NUMBER DEFAULT NULL,
    granularity VARCHAR2 DEFAULT 'DEFAULT',
    cascade BOOLEAN DEFAULT FALSE);
    and be sure to set CASCADE to TRUE so statistics for indexes are also collected.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It should make DB access faster;
    but there is no guarentee.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Don't put statistics on SYS or SYSTEM objects ...

    You might also look at what other things may be causing the response time to slow down ... ie, are you indexes being maintained or are they "Browning" and need to be rebuilt ???
    Look at v$waitstat to see what "waits" are occurring
    sql>select * from v$waitstat;
    and
    v$system_event

    sql> select substr(event,1,25),total_waits,total_timeouts,time _waited,average_wait
    from v$system_event;

    Are the tables extending too much .... How about your redo's and archival processing ???

    Have you run an explain plan on some of the "slower" queries and looked at their execution plan ???

    HTH
    Gregg

  6. #6
    Join Date
    Dec 2003
    Posts
    76
    When you say:

    * ...You might also look at what other things may be causing the response time to slow down ... ie, are you indexes being maintained or are they "Browning" and need to be rebuilt ??? ...*
    --> What do you really mean with maintaining Indexes ?? I've built my indexes once, and never rebuilted them .... Is that needed ???


    * ... Have you run an explain plan on some of the "slower" queries and looked at their execution plan ??? ... *
    --> No, I've never run an explain plan on any query at all ... Could you explain me better what is an explain plan, please ??


    Thanx,

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by aucrun
    When you say:

    * ...You might also look at what other things may be causing the response time to slow down ... ie, are you indexes being maintained or are they "Browning" and need to be rebuilt ??? ...*
    --> What do you really mean with maintaining Indexes ?? I've built my indexes once, and never rebuilted them .... Is that needed ???

    Thanx,
    You need to rebuild indexes from time to time, especially for highly active tables. Improper sizing or increased growth can fragment an index.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    As THE_DUCK says ... Indexes take a beating on tables with heavy
    dml (updates,deletes,inserts) causing node splits and fragmentation.
    Index "browning" is: The rows in an index are not deleted, but are
    "flagged" as deleted.... The "space" is not available for other rows.
    By rebuilding the indexes you relinquish the space to the freelist. You
    can also resize indexes if they have extended are are causing multiple reads to get the data ... You can:
    sql> validate index index_name;
    sql> select * From index_stats;

    Look at the del_lf_rows and del_lf_len (deleted leaf rows and lenght)

    As far as explain plan ... You can set autotrace on in sqlplus
    and run a sql statement ... The results will show you the cost of the query and the execution path Oracle takes to get the data ... You may
    find that Oracle is no longer using an index for 1 reason or another...

    You could use tkprof or other tools the will give you the execution plan.

    HTH
    Gregg

  9. #9
    Join Date
    Dec 2003
    Posts
    76
    Ok then ...

    I've rebuilded all Indexes, and Gathered Schem Stats (DBMS_GARHER_SCHEMA_STATS) ... and ...

    '... it appears to be quicker, in access to Database, just like as it was before DBMS_GATHER_TABLE_SCHEMA ...'

    So, I think that it worked out ...
    Thanx a lot,

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    Along the lines fo what Gregg said and described in perfect detail,
    I downlaoded this handy-dandy
    index rebuild script which does what Gregg described in one fell swoop.
    Just change the SCHEMA owner to the schema you want to analyze indexes on.

    Cut/paste the output into any sql window, or you can spool the output and run later.

    (please note someone else wrote this for public use, not me, but it works great.
    All I did was add the 'compress' part for the index rebuild)
    PHP Code:
    DECLARE
      
    vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
      
    vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
      
    vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
      
    vCursor  NUMBER;                            /* DBMS_SQL cursor        */
      
    vNumRows INTEGER;                           /* DBMS_SQL return rows   */
      
    vHeight  index_stats.height%TYPE;           /* Height of index tree   */
      
    vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
      
    vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
      
    vDLfPerc   NUMBER;                          /* Del lf Percentage      */
      
    vMaxHeight NUMBER;                          /* Max tree height        */
      
    vMaxDel    NUMBER;                          /* Max del lf percentage  */
      
    CURSOR cGetIdx IS SELECT owner,index_name
         FROM dba_indexes WHERE OWNER 
    'SCHEMA_OWNER';
    BEGIN
      
    /* Define maximums. This section can be customized. */
      
    vMaxHeight := 3;
      
    vMaxDel    := 20;

      
    /* For every index, validate structure */
      
    OPEN cGetIdx;
      
    LOOP
         FETCH cGetIdx INTO vOwner
    ,vIdxName;
         EXIT 
    WHEN cGetIdx%NOTFOUND;
         
    /* Open DBMS_SQL cursor */
         
    vCursor := DBMS_SQL.OPEN_CURSOR;
         
    /* Set up dynamic string to validate structure */
         
    vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
         
    DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
         
    vNumRows := DBMS_SQL.EXECUTE(vCursor);
         
    /* Close DBMS_SQL cursor */
         
    DBMS_SQL.CLOSE_CURSOR(vCursor);
         
    /* Does index need rebuilding?  */
         /* If so, then generate command */
         
    SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
            FROM INDEX_STATS
    ;
         IF 
    vDLfRows 0 THEN         /* handle case where div by zero */
            
    vDLfPerc := 0;
         ELSE
            
    vDLfPerc := (vDLfRows vLfRows) * 100;
         
    END IF;
         IF (
    vHeight vMaxHeight) OR (vDLfPerc vMaxDelTHEN
            DBMS_OUTPUT
    .PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD compress;');
         
    END IF;

      
    END LOOP;
      
    CLOSE cGetIdx;
    END;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Dec 2003
    Posts
    76
    Thankx to all, who helped me in this Forum ....

    I've made the Resizing of Rollback Segments, I've Rebuilded Indexes, and I've gathered Stats for Schema ....
    The result seems to be, an better/quicker access to Data, which means that my problem is resolved ...

    So, once more, thanx to all of you ...

    About that Package that you've sent to me, THE_DUCK, I've not tested it yet, but I will ....

  12. #12
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Duck,

    Where did you find that package?

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by Todd Barkus
    Duck,

    Where did you find that package?
    I downloaded it from a website with a bunch of other helpful sql procedures. I then went through them and customized them for my own use. Some have authors noted, but this specific one did not.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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