Home » RDBMS Server » Performance Tuning » finding changed sql plans
finding changed sql plans [message #539664] Tue, 17 January 2012 12:30 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I recently ran into an issue where I found a SQL plan was
changed and it caused performance issues.

I used the following query (see below) to find the
plans associated with the SQL. I droppe the bad plan
and everything worked fine.

This was a manula process for me... I am wondering if there
is some sort of tool out there that can detect if a plan was
changed or if anybody has a script they would be will to
share.


select plan_name, sql_text, sql_handle, origin, 
optimizer_cost, enabled, fixed, accepted 
from dba_sql_plan_baselines
where sql_text like '%MERGE%ORDER_CORPORATE_INFO_TEMP%'

SYS_SQL_PLAN_f24b6a7a75820bb5
MERGE INTO ORDER_CORPORATE_INFO_HIST T USING ORDER_CORPORATE_INFO_TEMP S ON (T.C
SYS_SQL_cedef0a9f24b6a7a       AUTO-CAPTURE                3 YES NO  NO

SYS_SQL_PLAN_f24b6a7a6901a4b3
MERGE INTO ORDER_CORPORATE_INFO_HIST T USING ORDER_CORPORATE_INFO_TEMP S ON (T.C
SYS_SQL_cedef0a9f24b6a7a       AUTO-CAPTURE             9066 YES NO  NO


Re: finding changed sql plans [message #539665 is a reply to message #539664] Tue, 17 January 2012 13:08 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
The only thing, I understood, you use OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE (for what purpose?). The both of SQL Plan Baselines from your output have accepted=NO, so they cannot be applied at all.

Only the first of SQL Plan Baselines for each sql gets accepted=YES, if you use automatic capturing. So if you dropped one baseline and solved your issue, this baseline should have had accepted=YES. After that the others baselines have accepted=NO and cannot be aplied anymore (unless you set accepted=YES manually).

I suggest you to turn off automatic capturing completely.

[Updated on: Tue, 17 January 2012 13:12]

Report message to a moderator

Re: finding changed sql plans [message #539667 is a reply to message #539665] Tue, 17 January 2012 14:07 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
LNossov,

In my production system I have the following:


SQL> select value from v$parameter where name = 'optimizer_capture_sql_plan_baselines';

VALUE
-------
FALSE


SQL> select distinct(origin) from dba_sql_plan_baselines;

ORIGIN
--------------
AUTO-CAPTURE

SQL> select distinct(accepted) from dba_sql_plan_baselines;

ACC
---
YES
NO


select count(*) from dba_sql_plan_baselines where accepted = 'YES'
  COUNT(*)
----------
      1635


select count(*) from dba_sql_plan_baselines where accepted = 'NO'
  COUNT(*)
----------
      3886



I just have a few questions if you dont mind?

1) Since I have some columns where ACCEPTED='YES' does
this mean that this is the first SQL baseline for each SQL?
If not, is there a way I can tell what is the first SQL?

2) Since optimizer_capture_sql_plan_baselines=FALSE no new
plans will be automatically generated so my count should never exceed 1635+3886

3) By turning off automatic captutring, what will I gain?

4) Is there a way to remove all sql_plans where
accpeted = 'NO' or is this something I don't want to do?

Thanks in advance for your expertise.
Re: finding changed sql plans [message #539670 is a reply to message #539667] Tue, 17 January 2012 14:43 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Automatic capturing was active in the past, because of origin=AUTO-CAPTURE for all of the baselines.

Regarding your questions.

1. yes, if all baselines are automatically generated. You can find out the "first" baselines also by

select SIGNATURE, SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines where (SIGNATURE,CREATED) in (select SIGNATURE,min(CREATED) from dba_sql_plan_baselines group by SIGNATURE);


2. yes,
3. automatic capturing is already turned off,
4. baselines with accepted=no cannot be applied. So you don't need to remove them.

You wrote Quote:
I found a SQL plan was
changed and it caused performance issues


How did you find that?

[Updated on: Tue, 17 January 2012 14:44]

Report message to a moderator

Re: finding changed sql plans [message #539675 is a reply to message #539670] Tue, 17 January 2012 15:09 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
2. yes and no. The number of baselines with accepted=NO can grow further.

[Updated on: Tue, 17 January 2012 15:10]

Report message to a moderator

Previous Topic: AWR report
Next Topic: SQl ID from peoplesoft process
Goto Forum:
  


Current Time: Thu Sep 16 20:17:46 CDT 2021