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

From: Tiwari, Yogesh <Yogesh.Tiwari_at_fidelity.co.in>
Date: Sat, 5 Dec 2015 09:10:30 +0000
Message-ID: <16B7E76F772C9141A2BCA83CD8D92501202935B9_at_INDEL7006WIN.intl.intlroot.fid-intl.com>

Thanks all for your response.
That's why I like this list, you throw one question at them, and you get so many options.

Anyway, we gave feedback to application team. They are now working on breaking that query into multiple parts, and use temporary tables for intermediate results. Unfortunately, they are not comfortable with usage of optimizer* hints within query, citing future release/changes in oracle version.

Additionally, we are internally considering, if we should be disable new 12c optimizer* params, that are contributing to instability in plans. There are huge queries in this datawarehouse database. We have been worked on over 15 different queries in past 2 weeks. It has been no less than a nightmare. :(

Thanks again for your response.

Thanks,
Yogi

Disclaimer: The information transmitted is intended for the person or entity to which it is addressed and may contain confidential, privileged or copyrighted material or attorney work product. If you receive this in error, please contact the sender and delete the material from any computer. Any comments or statements made are not necessarily those of FIL India Business Services Private Limited or any other Fidelity entity. All e-mails may be monitored or recorded.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Koehler Sent: 03 December 2015 14:23
To: ludovico.caldara_at_gmail.com; yasser8_at_gmail.com Cc: oracle-l_at_freelists.org
Subject: Re: Is it possible to disable SQL Directive collection for few tables?

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

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Sat Dec 05 2015 - 10:10:30 CET