10g AWR Dictionary Cache Stats: dc_histogram_data 過高 與 hard parse count過高的解法

AWR Dictionary Cache Stats:  dc_histogram_data 過高 與 hard parse count過高的解法

Find hard parse from session:

SELECT pa.SID, pa.VALUE "Hard Parses", ex.VALUE "Execute Count"

  FROM V$SESSTAT pa, V$SESSTAT ex

 WHERE pa.SID = ex.SID

   AND pa.STATISTIC#=(SELECT STATISTIC#

       FROM V$STATNAME WHERE NAME = 'parse count (hard)')

   AND ex.STATISTIC#=(SELECT STATISTIC#

       FROM V$STATNAME WHERE NAME = 'execute count')

   AND pa.VALUE > 0;

The output is similar to the following:

   SID  Hard Parses  Execute Count

------  -----------  -------------

     7            1             20

     8            3          12690

     6           26            325

    11           84           1619

如果看到hard parse count > 20, 或AWR Dictionary Cache Stats:  dc_histogram_data 過高,

1.  可設定cursor_sharing : force(暫解) , or

                                             Fine-tune SQL by : Cursor release/re-write SQL statement

2.使用以下設定, 資料欄位分布應該是歪斜的(distinct keys), 走full table scan甚至比fast index scan還快,此時要調整一下statistics對特定的column做skewonly統計資訊

10G solution

=========

1. You should begin by dropping the existing statistics for the sales table. You can do this by using the dbms_stats.delete_table_stats procedure.

BEGIN

dbms_stats.delete_table_stats(ownname=>'SH', tabname=>'SALES');

END;

/

2. Next lock the statistics on the effected table using dbms_stats.lock_table_stats.

BEGIN

dbms_stats.lock_table_stats(ownname=>'SH', tabname=>'SALES');

END;

/

3. The auto-stats gathering job, which will now maintain statistics for all other tables but not the effected table.

4. Create a job of your own that will gather statistics on the effected table using the following command. This command will allow Oracle to automatically gather histograms on all of the other columns in the table but not on the ID column. Note you need to set the force parameter to true to override the locked statistics.

BEGIN

dbms_stats.gather_table_stats('SH', 'SALES',

method_opt=>'for all columns size skewonly',

force => true);

END;

/

Ref:

http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm

https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating