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

Hi

How are you gathering stats. It appears that the optimizer believes the
relevant partition is either empty or has exactly 1 row in it (probably the
former). That is unlikely to be correct if a scan of the partition is
causing the trouble.

On Tue, Jun 7, 2011 at 4: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
>



-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: