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

  srec       dbms_stats.statrec;
  n_array dbms_stats.numarray;
    n_array     := dbms_stats.numarray( -1,
    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);

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’);


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: