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 >