RE: INDEX STATS??

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Tue, 10 Aug 2004 01:43:26 -0500
Message-ID: <433A07749711884D8032B6A0AB115262C2BD0C@conmsx07.corp.acxiom.net>

ACXSTATS is just the name of the stats table - exec dbms_stats.create_stat_table('&&SCHEMA','ACXSTATS');

exec
dbms_stats.export_table_stats('&USER','&TABLE',NULL,'ACXSTATS','&STID',&CASC ADE); The STATSID is month,day, day-of-week, timestamp (2000 = 8PM)

I was really wondering:
1) Why am I losing stats? I thought rebuilds would retain them in 9. 2) What's happening to plans? I asked client to send me code and explains and they said there were too many. But batch application jobs have been running faster every day (almost, they sent me their figures)the last two weeks, since upgrade to 9.2.0.4

I tried to generate all the plans in the shared pool and had some success, but also got a number of ORA-600s.

Larry

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Justin Cave Sent: Friday, August 06, 2004 10:35 PM
To: oracle-l_at_freelists.org
Subject: RE: INDEX STATS??

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfson Larry - lwolfs
Sent: Friday, August 06, 2004 9:31 PM
To: oracle-l_at_freelists.org
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_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 Tue Aug 10 2004 - 01:40:03 CDT