INDEX STATS??

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Fri, 6 Aug 2004 21:47:06 -0500
Message-ID: <433A07749711884D8032B6A0AB115262C2BD07@conmsx07.corp.acxiom.net>

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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Aug 06 2004 - 22:08:19 CDT