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

From: joshuasingham <joshuasingham_at_gmail.com>
Date: Tue, 7 Jun 2011 12:31:14 +0800
Message-ID: <BANLkTi=RgHr58fWBE2qH1gof5iWexa7sUQ_at_mail.gmail.com>



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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 06 2011 - 23:31:14 CDT

Original text of this message