After disabling directive generation and usage, you can take a weedwhacker to
any previously created directives, extensions, and statistics
Something like this. Use at your own risk. Make sure you understand the impact.
-- delete directives
column directive_id format a30
EXEC DBMS_SPD.flush_sql_plan_directive;
SELECT to_char(directive_id) directive_id, type, enabled, stateFROM
dba_sql_plan_directives;
declareBEGIN FOR rec in (SELECT directive_id did FROM
DBA_SQL_PLAN_DIRECTIVES) LOOP DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE
(directive_id => rec.did); END LOOP;END;/
SELECT to_char(directive_id) directive_id, type, enabled, stateFROM
dba_sql_plan_directives;
-- delete all droppable extensions
column owner format a30column table_name format a30column extension format a60
SELECT owner, table_name, DBMS_LOB.substr(extension, 3000) extension FROM
dba_stat_extensions WHERE droppable='YES';
declarebegin for c in ( select owner, table_name, extension_name from
dba_stat_extensions where droppable='YES' ) loop
dbms_stats.delete_column_stats (c.owner, c.table_name, c.extension_name); end
loop;end;/
SELECT owner, table_name, DBMS_LOB.substr(extension, 3000) extension FROM
dba_stat_extensions WHERE droppable='YES';
BEGIN FOR rec IN ( SELECT owner, table_name, DBMS_LOB.substr(extension,
3000) extension FROM dba_stat_extensions WHERE droppable='YES' )
LOOP DBMS_OUTPUT.put_line(rec.extension); DBMS_STATS.drop_extended_stats(
rec.owner, rec.table_name, rec.extension ); END LOOP; END;/
SELECT owner, table_name, DBMS_LOB.substr(extension, 3000) extension FROM
dba_stat_extensions WHERE droppable='YES';
From: yasser8@xxxxxxxxx
Date: Wed, 2 Dec 2015 22:30:31 +0530
Subject: Re: Is it possible to disable SQL Directive collection for few tables?
To: ludovico.caldara@xxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
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@xxxxxxxxxxxxxx>:
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.
