Re: Performance issue post database analyze, need to understand whats wrong in analyze
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: shastry17@xxxxxxxxx
- Date: Tue, 7 Jun 2011 06:59:20 +0100
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:
