Re: RE: Is it possible to disable SQL Directive collection for few tables?
From: <l.flatz_at_bluewin.ch>
Date: Wed, 2 Dec 2015 16:50:24 +0000 (GMT)
Message-ID: <28239893.37924.1449075024363.JavaMail.webmail_at_bluewin.ch>
IHAC who got an application with a lot of skew data distribution on search criteria. Thus we got ping-pong directives. Switch the plan one way. Find a query where that is wrong. Switch it the other way. Very enteraining if you are not affected from it. ----Ursprüngliche Nachricht----
Von : iggy_fernandez_at_hotmail.com
Datum : 02/12/2015 - 17:20 (GMT)
An : ludovico.caldara_at_gmail.com, oracle-l_at_freelists.org Betreff : RE: Is it possible to disable SQL Directive collection for few tables? re: as last resort, disable the whole adaptive features. I generally do not recommend it, but sometimes I do it :-) Alternatively, you could disable the creation and use of directives instead of disabling all the adaptive features. Date: Wed, 2 Dec 2015 16:39:42 +0100
Subject: Re: Is it possible to disable SQL Directive collection for few tables? From: ludovico.caldara_at_gmail.com
To: oracle-l_at_freelists.org
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
Date: Wed, 2 Dec 2015 16:50:24 +0000 (GMT)
Message-ID: <28239893.37924.1449075024363.JavaMail.webmail_at_bluewin.ch>
IHAC who got an application with a lot of skew data distribution on search criteria. Thus we got ping-pong directives. Switch the plan one way. Find a query where that is wrong. Switch it the other way. Very enteraining if you are not affected from it. ----Ursprüngliche Nachricht----
Von : iggy_fernandez_at_hotmail.com
Datum : 02/12/2015 - 17:20 (GMT)
An : ludovico.caldara_at_gmail.com, oracle-l_at_freelists.org Betreff : RE: Is it possible to disable SQL Directive collection for few tables? re: as last resort, disable the whole adaptive features. I generally do not recommend it, but sometimes I do it :-) Alternatively, you could disable the creation and use of directives instead of disabling all the adaptive features. Date: Wed, 2 Dec 2015 16:39:42 +0100
Subject: Re: Is it possible to disable SQL Directive collection for few tables? From: ludovico.caldara_at_gmail.com
To: oracle-l_at_freelists.org
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>: 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 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. -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 02 2015 - 17:50:24 CET