Suggestions for updating Jonathon Lewis' upper bound stat adjustment script for 12c?

From: <"">
Date: Tue, 24 Sep 2019 20:38:54 +0000
Message-ID: <0D8F4CAC0F9D3C4AACC63F50FD9957F762EA7DF8_at_PRDTXWPEMLMB32.prod-am.ameritrade.com>

Back in 2014 Jonathon had a great blog post about adjusting the max value of a column histogram. This was a life saver for me a few years later as we have a db that was getting very poor performance due to the most common queries running against huge tables and only querying the current days' data. So when stats were gathered the maxvalue would be less than the value the of the SQL in the WHERE clause. Bind variables are not used in the queries and we can't change that since it's a third party app. I have no hair left to tear out over that one.

Here is the link to the blog post:
https://jonathanlewis.wordpress.com/2014/07/04/adjusting-histograms/

What concerns me is the ominous quote "it's not appropriate for 12c which has introduced hybrid histograms that will require me to modify my "histogram faking" code a little". The db will be upgraded to 12c early next year and I don't want to suddenly drop back to the previous poor performance but I have no idea of how to go about adjusting the code to take hybrid histograms into account.

Any suggestions (Jonathon?)? Or alternatively is there a way to force the autostats gathering to not create hybrid histograms for this table or database?

Here is the slightly tweaked code I'm using which sets the maxval of the ITEMDATE column to a date 2 days in the future and is run daily: CREATE OR REPLACE PROCEDURE hsi.maxdate_for_hsi_itemdata IS

    m_distcnt       number;
    m_density       number;
    m_nullcnt       number;
    srec            dbms_stats.statrec;
    m_avgclen       number;
 
    d_array         dbms_stats.datearray := dbms_stats.datearray();
    ct          number;
 

begin  

    dbms_stats.get_column_stats(

        ownname     => 'HSI',
        tabname     => 'ITEMDATA',
        colname     => 'ITEMDATE',
        distcnt     => m_distcnt,
        density     => m_density,
        nullcnt     => m_nullcnt,
        srec        => srec,
        avgclen     => m_avgclen

    );  

    ct := 0;
    for r in (

        select  to_date(to_char(trunc(endpoint_value)),'J') + mod(endpoint_value,1) d_val
        from    DBA_tab_histograms
        where   table_name = 'ITEMDATA'
        and column_name = 'ITEMDATE'
        order by endpoint_number

    ) loop  

        ct := ct + 1;
        d_array.extend;
        d_array(ct) := r.d_val;
        if ct = 1 then
            srec.bkvals(ct) := 0;
        else
            srec.bkvals(ct) := 1;
        end if;
 

    end loop;  

    d_array(ct) := sysdate+2;  

    dbms_stats.prepare_column_values(srec, d_array);  

    dbms_stats.set_column_stats(

        ownname     => 'HSI',
        tabname     => 'ITEMDATA',
        colname     => 'ITEMDATE',
        distcnt     => m_distcnt,
        density     => m_density,
        nullcnt     => m_nullcnt,
        srec        => srec,
        avgclen     => m_avgclen

    );
end;
/  

TIA,
Jay Miller

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 24 2019 - 22:38:54 CEST