RE: INDEX STATS??
- From: "Justin Cave" <justin@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 6 Aug 2004 21:35:14 -0600
I assume the ACXSTSATS table is something you are populating. When and how
are you doing that?
I can all but guarantee that weekly rebuilds of all indexes is causing more
harm than good. I would wager that the daily rebuilds of "hot" indexes is
also causing more harm than good-- unless you are doing massive deletes that
delete all but one or two blocks from a particular index block, Oracle is
very good at keeping indexes balanced.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Wolfson Larry - lwolfs
Sent: Friday, August 06, 2004 9:31 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: INDEX STATS??
Below is a list of daily stats. The DB was 8.1.6.2 until July 24th upgraded
to 9.2.0.4
The schema is analyzed every Wednesday.
We noticed we lost some stats on Thursday and a lot more on Sunday.
everyday we rebuild "HOT" indexes. Those with massive deletes.
every Sunday we rebuild all the indexes in the schema.
I checked and that was the difference(thanks for script Wolfgang)
Now in 9.2.0.4 the same thing is happening.
Are we shooting ourselves in the foot?
Is the optimizer really using these stats?
Funny thing. CPU utilization on 9.2.0.4 is less than half of 816 (more
dramatic after implementing CPU costing).
Other funny thing. Client claims his batch jobs running faster and faster
but we don't have any other stat jobs then what happens Wednesday.
Larry
OOPS! I'll see what XTRA stats were.
SELECT STATID,COUNT(*) FROM &&USER..ACXSTATS GROUP BY STATID;
STATID COUNT(*)
------------------------------ ----------
JUL01THU2000 7415
JUL02FRI2000 7415
JUL03SAT2000 7407
JUL04SUN2000 6777
JUL05MON2000 6777
JUL06TUE2000 6777
JUL07WED2000 7461 Analzye
JUL08THU2000 7423
JUL09FRI2000 7423
JUL10SAT2000 7423
JUL11SUN2000 6793
JUL12MON2000 6793
JUL13TUE2000 6793
JUL14WED2000 7461 Analzye
JUL15THU2000 7423
JUL16FRI2000 7423
JUL17SAT2000 7423
JUL18SUN2000 6793
JUL19MON2000 6793
JUL20TUE2000 6793
JUL21WED2000 7461 Analzye
JUL22THU2000 7423
JUL23FRI2000 7423
JUL24SAT2355 7400 9204 lost 24 from plan_table and
gained one
JUL25SUN2000 7646 XTRA?
JUL26MON2000 7624 XTRA?
JUL27TUE2000 7624 XTRA?
JUL28WED2000 7467 Analzye
JUL29THU2000 7429
JUL30FRI2000 7429
JUL31SAT2000 7429
AUG01SUN2000 6792
AUG02MON2000 6792
AUG03TUE2000 6792
AUG04WED2000 7467 Analzye
AUG05THU2000 7429
AUG06FRI2000 7429
**********************************************************************
The information contained in this communication is confidential, is intended
only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are hereby
notified that any dissemination, distribution, or copying of this
communication is strictly prohibited.
If you have received this communication in error, please re-send this
communication to the sender and delete the original message or any copy of
it from your computer system. Thank You.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
