Foiled again by stale stats

[djbjr]

Our data warehouse was victimized last night by a stage table with stale stats. The load hang and users logged in to find missing data. (yuck)

So I am now try to create standards for gathering stats on our tables. Anyone know a good resource for becoming an expert on the DBMS STATS PACKAGE functionality?

Any advice or experiences you guys could share with me to bring back to my team...

As always, anything will be appreciated.

dj

 

[SantaMufasa]

DJ,

We re-analyze our stats nightly by schema. (We encountered problems when we executed an ANALYZE_DATABASE.) Here is the script we use to script ANALYZE_SCHEMA commands for an entire database:

set echo off
set trimspool on
set feedback off
set pagesize 0
spool temp.sql
select translate('exec dbms_utility.ANALYZE_SCHEMA(^'||
	username||'^,^ESTIMATE^,null,10,null)','^','''')
from dba_users
where username not in ('SYS')
/
spool off
set feedback on
set pagesize 35

Here is sample output of the above script:

SQL> @statsanalyzerbyschemascripter
exec dbms_utility.ANALYZE_SCHEMA('SYSTEM','ESTIMATE',null,10,null)
exec dbms_utility.ANALYZE_SCHEMA('OUTLN','ESTIMATE',null,10,null)
exec dbms_utility.ANALYZE_SCHEMA('DBSNMP','ESTIMATE',null,10,null)
exec dbms_utility.ANALYZE_SCHEMA('DHUNT','ESTIMATE',null,10,null)
exec dbms_utility.ANALYZE_SCHEMA('SUMMIT','ESTIMATE',null,10,null)

We have this script (and its subsequent "@temp" command) batched up in our nightly "cron" job.

Let us know if this helps.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:41 (26Jan05) UTC (aka "GMT" and "Zulu"),
@ 12:41 (26Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[dbtoo2001]

Export the old stats before re-analyzing, just in case.
What about dbms_stats.gather_schema_stats with the gather stale options?

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_st2.htm#1003995

 

[djbjr]

Thanks for the feedback.

One issue is that we have some tables that react differently depending on how the stats are gathered.

That may be an issue when trying to analyze the schema.

 

[SantaMufasa]

One issue is that we have some tables that react differently depending on how the stats are gathered.

I've not heard of such an anomaly. Can you please explain?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:31 (26Jan05) UTC (aka "GMT" and "Zulu"),
@ 14:31 (26Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[dbtoo2001]

We tested that recently too.
We do an analyze table estimate 5%.
Switched to dbms_stats and the application went to a crawl. I really couldn't determine an accurate reason as to why this occurs. The procedure is probably doing much more work than the 'simple' analyze command which is causing the optimizer to view things very differently.

Tom Kyte has a long discussion on the topic at

http://asktom.oracle.com/pls/ask/f?...P8_DISPLAYID,F4950_P8_CRITERIA:5792247321358,

Really, it boils down to what is working for your application.

 

[rasprer]

Sorry to interrupt this thread...but, is the anaylze_schema only intended for CBO (cost based). Is it necessary for Rule based as well.

SantaMufasa...I've rebuilt all my indexes and tables with the command you suggested..

alter table table1 /* size: 45088768 */ move parallel nologging tablespace tablspace1;

alter index index1 /* size: 589824 */ rebuild parallel tablespace indextablspace;

...shoud I still issue an analyze_schema for better performance?

 

[SantaMufasa]

J,

Statistics gathering has no effect upon Rule-based Optimizer. And, yes, once you have defragged tables and indexes, it potentially changes statistics such as:
BLOCKS
EMPTY_BLOCKS
CHAIN_CNT
AVG_SPACE_FREELIST_BLOCKS
NUM_FREELIST_BLOCKS

So, I would regather stats.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:45 (27Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:45 (27Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rasprer]

SantaMufasa..THanks. My optimizer is set to 'Rule', so I guess I don't need to re-gather stats. For Optimizer set to Rule..is there anything I can do after I have defragged by tables and indexes?

 

[SantaMufasa]

Yes, I believe Prayer is always good. (It certainly cannot interfere with either RBO or CBO.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:54 (27Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:54 (27Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rasprer]

I've been trying to convince our Principal System's engineer to switch to CBO, but he keeps saying, 'if it ain't broke fix, don't fix it'. I would rather set it to COST based. Would you happen to have any links or documentation that I can use for reference and the advantages of moving from RBO to CBO. Maybe I can convince him after I forward the link and/or documentation.

 

[SantaMufasa]

J,

To deal with his "If it ain't broke..." concept, remind him that eventually it will be broken since Oracle has announced its intention to retire RBO.

If you Google for "Oracle CBO versus RBO", there are nearly 900 document hits, many of which are good treatments of the topic.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:55 (27Jan05) UTC (aka "GMT" and "Zulu"),
@ 13:55 (27Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.