RE: Performance issue post database analyze, need to understand whats wrong in analyze
- From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
- To: "'shastry17@xxxxxxxxx'" <shastry17@xxxxxxxxx>, 'oracle-l' <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 7 Jun 2011 09:45:03 -0400
Shastry,
You forgot to put which version you are on and it would have been nice to know
what key(s) the table is partitioned on as well. You also didn't specify how
you gathered stats. Partition level only or global stats?
As Niall said the optimizer thinks the partition it is accessing is empty.
Without understanding the data and not knowing the partitioning scheme I have
no way of advising why that is, but my guess would be that the high/low values
of either the partition(s) or the global stats are off.
Forward copying stats in 10g is a risky business because NDV's at least at the
global level will be off (Doug Burns has some good papers on this). This is
somewhat fixed in 11g with incremental stats but it takes up space in SYSAUX to
store the information needed for this.
Thanks,
Finn
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Shastry(DBA)
Sent: Monday, June 06, 2011 11:44 PM
To: oracle-l
Subject: Performance issue post database analyze, need to understand whats
wrong in analyze
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)<tricon:40,-1%7C%281%29;chat85601771817718>, 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
--------<tricon:40,-1%7C--------;kkekre@xxxxxxxxx>
---------<tricon:40,-1%7C---------;kkekre@xxxxxxxxx>
Plan
SELECT STATEMENT ALL_ROWSCost: 2 Bytes:
566<tricon:40,-1%7C566;kkekre@xxxxxxxxx> Cardinality: 1
3 SORT AGGREGATE Bytes: 566<tricon:40,-1%7C566;kkekre@xxxxxxxxx> Cardinality:
1
2 PARTITION RANGE SINGLE Cost: 2 Bytes:
566<tricon:40,-1%7C566;kkekre@xxxxxxxxx> Cardinality: 1 Partition #: 2
Partitions accessed #1
1 TABLE ACCESS FULL TABLE DM.CPR_LDAP_SUMMARY Cost: 2 Bytes:
566<tricon:40,-1%7C566;kkekre@xxxxxxxxx> 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
>>> This e-mail and any attachments are confidential, may contain legal,
>>> professional or other privileged information, and are intended solely for
>>> the addressee. If you are not the intended recipient, do not use the
>>> information in this e-mail in any way, delete this e-mail and notify the
>>> sender. CEG-IP1
Other related posts:
