RE: Performance issue post database analyze, need to understand whats wrong in analyze

Good morning,

 

As a good friend of mine told me: train time is think time so I thought I'd
add my comments to this.

 

a)      Why did the plan change - see Niall's and Greg's answers for an
explanation. Additionally I like to check the plans for a statement (proper
licensing assumed) using dbms_xplan.display_awr for that sql id as well. It
helped greatly in understanding the problem after developers told me "I have
no idea what the good plan is. All I know is that it's slow now"

b)      Preventing unpredictable execution plans with partitioned tables has
been discussed a number of times by much more knowledgeable people on this
list. In my experience (that was on 10.2.0.4 Linux/3 node RAC) copying
partition statistics forward seemed to be a great way of getting predictable
performance, especially if you partition by range on a time column

 

Hope this helps,

 

Martin

 

Martin Bach

Martin Bach Consulting Ltd

http://www.linkedin.com/in/martincarstenbach

http://martincarstenbach.wordpress.com

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Shastry(DBA)
Sent: 07 June 2011 06:30
To: joshuasingham@xxxxxxxxx
Cc: oracle-l
Subject: Re: Performance issue post database analyze, need to understand
whats wrong in analyze

 

Hi Joshua,

Thanks for the response. I am looking for the approach to find the issue
where post analyze why the plan got changed? How to dig out on this and any
measures to prevent the same issue in coming analyze job?

Regards,
Anantha

On Tue, Jun 7, 2011 at 10:01 AM, joshuasingham <joshuasingham@xxxxxxxxx>
wrote:

Hi Shastry,

 

Instead of using a hint you can use a solution for plan stability like
stored oulines, sql profile (see  kerry osborne blog for more details) and
if you using 11g you can use SQL plan management .

 

Regards

 

joshua 

On Tue, Jun 7, 2011 at 11:44 AM, Shastry(DBA) <shastry17@xxxxxxxxx> wrote:

Hi Team,

 

I need your help in knowing whats the issue with my database analyze? Last
Sunday analyze job has been run and post that the explain plan got changed
which caused severe performance issue with high GC BUFFER BUSY waits. Please
help me in understanding the issue.

 

Here is the problematic query :

 

SELECT DECODE (COUNT (1), 0, 'N', 'Y')
FROM CPR_LDAP_SUMMARY
WHERE LOWER (USERID) = :B1 AND UPPER (IPCACCESS) IN ('12', 'YES')

 

And plan details from stage & Prod 

 

 

Plan
SELECT STATEMENT  ALL_ROWSCost: 4  Bytes: 15  Cardinality: 1  
3 SORT AGGREGATE  Bytes: 15  Cardinality: 1  
2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE DMADM.CPR_LDAP_SUMMARY Cost: 4
Bytes: 15  Cardinality: 1  Partition #: 2  Partitions accessed #1
1 INDEX RANGE SCAN INDEX DM.CPR_LS_USERID_IDX Cost: 3  Cardinality: 1  

Prod plan is

-------- 

---------

Plan
SELECT STATEMENT  ALL_ROWSCost: 2  Bytes: 566  Cardinality: 1  
3 SORT AGGREGATE  Bytes: 566  Cardinality: 1  
2 PARTITION RANGE SINGLE  Cost: 2  Bytes: 566  Cardinality: 1  Partition #:
2  Partitions accessed #1
1 TABLE ACCESS FULL TABLE DM.CPR_LDAP_SUMMARY Cost: 2  Bytes: 566
Cardinality: 1  Partition #: 3  Partitions accessed #1

 

The issue was with plan change since last gather stats and performance
restored after re-gathering stats.

 

SQL_ID=dx87pq17pthq1 
Bad Plan Hash=1572312302 (FTS) 
Good Plan Hash= 2166829021 (function based index usage) 

The plan change from index usage to FTS since 05-jun-11 18hrs  and gather
stats on this table was on 05-jun-11 10:00 hrs. 

After re-gathering stats on DM.CPR_LDAP_SUMMARY the static plan used index
again and performance improved. 

If the issue re-appears with next gather stats, perf team suggested to
consider using following hint: 
SELECT /*+ index(CPR_LDAP_SUMMARY CPR_LS_USERID_IDX) */ COUNT (1) ... 

 

Thanks,

Shastry

Other related posts: