星期一, 8月 19, 2013

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對特定的columnskewonly統計資訊

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

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...