How to set histogram value manually

Sometimes you may want to set histogram value manually to make oracle choose a better plan for specified SQL. Here is how to do this.

Step 1. Set histogram

declare
  srec       dbms_stats.statrec;
  n_array dbms_stats.numarray;
  begin
    n_array     := dbms_stats.numarray( -1,
            -1,
            -1,
            335352456,
            ...
            340722947);
    srec.bkvals := null;
    srec.epc    := 255;
    dbms_stats.prepare_column_values(srec, n_array);
    dbms_stats.set_column_stats(ownname => user,
                                tabname => 'CASES',
                                colname => 'CASE_ID',
                                srec    => srec);
  end;
  /

Step 2. Check the result and lock the statistics

SQL> select ENDPOINT_NUMBER,ENDPOINT_VALUE from dba_tab_histograms where TABLE_NAME=’CASES’ and COLUMN_NAME=’CASE_ID’ order by 1;
Make sure SQL is plan and lock the statistics
SQL>  exec dbms_stats.lock_table_stats(user,’CS_PRIORITIZATION_CALC’);

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: