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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 24 Sep 2019 22:00:31 +0000
Message-ID: <CWXP265MB17508EC855A8303347F791FDA5840_at_CWXP265MB1750.GBRP265.PROD.OUTLOOK.COM>

I'm not sure if I've written a note on fixing up the high value for hybrids, I'll have to check. However I do have a note on how to generate a hybrid from scratch (character type in my example) here: https://jonathanlewis.wordpress.com/2018/10/10/hybrid-fake/

If you want to control what autostats does you can use a one-off call to dbms_stats.set_table_prefs() to set a method_opt for the table. Something like:

begin

        dbms_stats.set_table_prefs(
                ownname => user,
                tabname => 'T1',
                pname   => 'METHOD_OPT',
                pvalue  => 'for all columns size auto for columns size 1 dont_want_histogram1, dont_want_histogram2'
        );

end;
/

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of dmarc-noreply_at_freelists.org <dmarc-noreply_at_freelists.org> Sent: 24 September 2019 21:38
To: oracle-l_at_freelists.org
Subject: Suggestions for updating Jonathon Lewis' upper bound stat adjustment script for 12c?

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


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

Received on Wed Sep 25 2019 - 00:00:31 CEST