Re: Very Strange Query Access Plan

Tom,

You might also want to check the num_distinct column of user_tab_columns to see if that's a realistic number. I've had situations where it was really low (like 400 when there were millions of distinct values) which resulted from too small an estimate using analyze or dbms_stats.

--Terry

• Original Message ----- From: Mercadante, Thomas F (LABOR) To: oracle-l_at_freelists.org Sent: Tuesday, October 02, 2007 11:29 AM Subject: RE: Very Strange Query Access Plan

  All,    

  Thanks to Alvaro Jose Fernandez & Ric Van Dyke, this is solved. The DENSITY and CLUSTER FACTOR values in the user_tab_columns for my database table had bad values. These values are calculated by the DBMS_STATS package. I manually set these to a much lower figure and my problem went away.    

  I'm still trying to determine what my next steps are. One definite step is to stop gathering stats for awhile!    

  Thanks    

  Tom    

  From: Koppelaars, Toon [mailto:T.Koppelaars_at_centraal.boekhuis.nl]   Sent: Tuesday, October 02, 2007 1:59 PM   To: Mercadante, Thomas F (LABOR); oracle-l_at_freelists.org   Subject: RE: Very Strange Query Access Plan    

  Can you provide us with a (couple of) example(s) of these queries?

    -----Oorspronkelijk bericht-----
    Van: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]Namens Mercadante, Thomas F (LABOR)     Verzonden: dinsdag 2 oktober 2007 17:12     Aan: oracle-l_at_freelists.org
    Onderwerp: Very Strange Query Access Plan

    All,      

    Running on 9.2.0.7 on Aix.      

    I have a database table with 18 million rows in it. There is an SSN column in the table. Only 625,000 rows have an SSN populated. I also have an index on this column. Statistics on both the table and index are gathered nightly (using dbms_stats with the estimated and default sample options).      

    Yesterday, queries against this table used the index. Today, they do not. There was no large data load overnight. We add about 10,000 records per day. This behavior has happened before where the index gets ignored for a day and then used the next day.

    I really need to use an index here. Queries via ssn are executed all day long and take 10 minutes to complete. CPU is pegged at 99%.

    What am I missing? Maybe histograms? Haven't used them before, so any suggestions would be helpful.

    Thanks

    Tom

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 03 2007 - 19:59:16 CDT