Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Minneapolis, Minnesota
    Posts
    2

    Unanswered: Dbms_stats.gather_table_stats

    Hi,
    I was reading a previous thread concerning DBMS_STATS.GATHER_TABLE_STATS slowiness. In there The_Duck had posted an Index rebuild script.
    I pulled this piece out to see what information I had stored in the INDEX_STATS table.
    SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
    FROM INDEX_STATS;

    I alter it to run as SELECT * FROM INDEX_STATS; When I ran it I didn't get anything back. We have just recently upgraded to Oracle 9i and I am not a DBA. I am curious if anyone knows why I didn't get any results back. Could it be that the table is empty becuase DBMS_STATS.GATHER_TABLE_STATS has not been executed or that I might have insuffient privileges to the table?
    Thanks,
    Mary Wager

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You first have to populate INDEX_STATS ... This table has only 1 row at a time ... To populate, you must validate the index in question

    ie;

    validate index BC_SCAN_INV_PROC_NDX1;

    select * from INDEX_STATS;

    HTH
    Gregg

  3. #3
    Join Date
    Jan 2004
    Location
    Minneapolis, Minnesota
    Posts
    2

    Dbms_stats.gather_table_states

    Originally posted by gbrabham
    You first have to populate INDEX_STATS ... This table has only 1 row at a time ... To populate, you must validate the index in question

    ie;

    validate index BC_SCAN_INV_PROC_NDX1;

    select * from INDEX_STATS;

    HTH
    Gregg
    So if not indexes have been validated in the database then nothing gets entered into INDEX_STATS. Is it a best practice for a DBA to validate all the indexes after an upgrade?
    Thanks,
    Mary Wager

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I would ... Especially after an upgrade (of the database) ... I also
    periodically check indexes in order to see the node levels and
    the deleted leaf values ...

    HTH
    Gregg

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    hey, that's me up there.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    You hooligan, seeding confusion and chaos :-)

    All the best,
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

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

    I concur with Bill. You are definitely a life at this party. Also, I think "Grand Poohbah" is a more fitting moniker for you than Guru .... I have seen some of your questions :-)
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Duck,

    I agree with the prior 2 ... AND CONGRATULATIONS on "Grand Poohbah"

    Gregg

Posting Permissions

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