Re: Is it possible to disable SQL Directive collection for few tables?

From: Stefan Koehler <contact_at_soocs.de>
Date: Thu, 3 Dec 2015 09:52:46 +0100 (CET)
Message-ID: <1544423075.990680.1449132766924.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

Hi guys,
(hidden) parameter "_optimizer_enable_extended_stats" = FALSE would also be another option, if the issue is only related to a few queries.

Parameter can be applied by hint OPT_PARAM() in SQL itself or if not modifiable through SQL patch.

P.S.: You can also be much more specific with (hidden) parameter "_optimizer_extended_stats_usage_control", but then we would need to know what the exact root cause is.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Yasser Khan <yasser8_at_gmail.com> hat am 2. Dezember 2015 um 18:00 geschrieben:
>
> Hi Ludovico,
>
> Seems like even after disabling sql plan directives they are still used by dbms_stats to create extended statistics, so I think its not going to
> help Yogesh as his problem was due creation of extended statistics as directed by SPD.
>
> Please correct me if I am wrong.
>
> Thanks,
> Yasser
>
>
> On Wed, Dec 2, 2015 at 9:09 PM, Ludovico Caldara <ludovico.caldara_at_gmail.com <mailto:ludovico.caldara_at_gmail.com> > wrote:
> > > Hi Yogesh,
> >
> > in 12cR1 there's no easy way to disable directives in advance for specific tables.
> >
> > What you can do:
> > 1. wait for the directives to be created (after a few misestimates) and then disable them manually with pl/sql (notice that deleting them doesn
> > not guarantee that you will not have them recreated later)
> >
> > BEGIN
> > FOR rec in (select d.directive_id as did from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on
> > (d.directive_id=o.directive_id) where o.owner='APPUSER' and o.object_name in ('TAB','TAB_PROP'))
> > LOOP
> > DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'ENABLED','NO');
> > END LOOP;
> > END;
> > /
> >
> > 2. you can stage/export/import directives from one DB to another one, this will keep the directive status.
> >
> > I've explained this step in my blog:
> > http://www.ludovicocaldara.net/dba/sql-plan-directives-problem/
> >
> >
> > 3. as last resort, disable the whole adaptive features. I generally do not recommend it, but sometimes I do it :-)
> >
> > HTH
> > --
> > Ludovico
> >
> > 2015-12-01 5:51 GMT+01:00 Tiwari, Yogesh <Yogesh.Tiwari_at_fidelity.co.in <mailto:Yogesh.Tiwari_at_fidelity.co.in> >:
> > > > >
> > > Oracle-l ,
> > >
> > > Is it possible to disable SQL directive collection for few tables?
> > >
> > > We are on 12.1.0.2 PSU4, linux x86-64. We saw perf degradation after upgrade to 12c. One of the query, huge 500lines, involving complex
> > > views, saw change in plan, and even baselines are getting ignored, that we got from 11g db. Later, we compared stats from 11g db, and deleted
> > > extended stats, and it worked like charm. Hence, the question.
> > >
> > > Thanks,
> > > Yogi

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

Received on Thu Dec 03 2015 - 09:52:46 CET