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

Thread: index not working...

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    index not working...

    Hi Guys,
    Today we had a major breakdown on our production database,
    oracle version=8.1.7
    os =sun 2.7

    We deleted our stats usind dbms_Stats.delete_table_stats package and regenerated the stats using dbs_stats.gather_table_stats.And once the clients started using our production database, full table scans were going on some of the huge tables..which resulted in high CPU utilization and bring down the database..after this we did the following process.
    We dropped earlier statistics.
    2. recalculated the statistics 10% ( last setting)
    3. once we started the service the there were few bad sql's coming up and cpu usage was 100%.
    4. we tried increasing the sampling rate one big table( one of the bad query )
    it did not help.
    5. We tried dbms_stats compute statistics on reg_user , it did not help.
    6. We tried old analyze satistics but it didnot help.
    7. we tried to revert back the statistics, but still same problem. ( first time we might have made mistake )
    8. I opend the tar with oracle and spend about hour and half trying to explain the situation.
    CSR's focus was tuning sql rather than stats. At that point I thought fixing stats should solve our problem.
    9. We wanted to get old stats which were exported earlier, so we deleted the stats and imported the old
    stats. Did not work. it was incomplete.
    10.Before step 9 we have ok stats and wanted to go back, but could not use it since we didn't store it in.
    table.
    11. I decided to just go forward with calculation and fix the issues as they come.
    12. we tried calling the more stats on cart to see if post-sale query will use it. no luck.


    can u please give a solution and why the index were not used..

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    did you chck that the indexes were actually valid?

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Was the db shutdown overnight, where any init.ora parameters changed, check the altert.log for alter system commands.
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    anandkl

    We rebounced the database no error in logs...index are fine.,
    anandkl

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... and you "cascaded" the gather_table_stats, so the indexes have stats?

    What's your optimizer_index_cost_adj setting, should be around 20 as a start?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Check you DB_FILE_MULTIBLOCK_READ_COUNT hasn't change. It can be changed via ALTER SYSTEM, so the alert.log would tell you.

    When was the db last bounced, prior the most recent restart?
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    anandkl

    The db_file_multiblock_read_count is fine,we had bounced the database couple of times ..

    The optimizer_index_cost_adj =10 right now...


    regards
    anandkl
    anandkl

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    since you have not mentioned what options you used in dbms_stats

    this is what we use over here

    Code:
    dbms_stats.gather_schema_stats(OWNNAME => 'USERNAME',
                                           ESTIMATE_PERCENT => 20,
                                           METHOD_OPT => 'FOR ALL COLUMNS SIZE 75',
    				       GRANULARITY => 'ALL',
                                           DEGREE => 8,
                                           CASCADE => TRUE);
    And have you looked explain plan and determine why they are not using indexes?

  9. #9
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,

    like Pando said, we need
    * statistics of the huge table -> dba_tables-row,
    * the still bad SQL
    * the explain-pan
    * the indexes defined on the table

    Orca

  10. #10
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    3. once we started the service the there were few bad sql's coming up and cpu usage was 100%.

    are these sqls issued for the first time or
    is it a routine sql statements coming from application which used to come earlier ?

    check if there are any locks .
    check if u have gathered statistics on indexes as well .

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