| Stats Gathering on Whole Schema [message #274704] |
Tue, 16 October 2007 22:21  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
I need some advice on the way stats should be gather after a Database is
build from a export file, I mean i have a approx 300 GB ( data) and 500 gb Index size database
that needs to gather STATS once import is done, including data and indexes,
I used this option and it never got completed in 18 hrs so then i have to stop.
BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'OWNER'
,Granularity => 'ALL'
,Options => 'GATHER'
,Gather_Temp => TRUE
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO '
,DEGREE => 8
,CASCADE => TRUE
,No_Invalidate => FALSE);
END;
Is there a better option i should use to complete stats on this sized schema in less time.(Schema includes some partition tables and partition indexes also.)
And 2 Question is, In Production What should be the approach for gathering STATS,
Total of 220 tables out of which 13 are range partitions on Date, having monthly partitions,It's an (OLTP+datawarehouse Combination) with constant data insertion in current month partition.
Data insertion / Update happens on current month partition , rarely on older partitions, some around 20 are big transactional tables with some 10-15 million rows which are not partitioned but data gets change daily, and remaining tables are small from 20 rows -200000 rows which very rarely gets updated, What should be my approach on gathering STATS on such schema.
Thanks
|
|
|
|
|
|
| Re: Stats Gathering on Whole Schema [message #274723 is a reply to message #274715] |
Tue, 16 October 2007 23:35  |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Thanks Anacedent for that Answer,
I think i know that,
My 1 Question was for Whole Schema Stats for the First time after import.
2 question your approach gather_table_stats , so you want me to run whole tablestats for 300 mil rows table ?.
And Oracle Version is 10g Rel2.
I am expecting some advice on my both questions.
Thanks Again.
[Updated on: Tue, 16 October 2007 23:38] Report message to a moderator |
|
|
|
|
|
| Re: Stats Gathering on Whole Schema [message #274917 is a reply to message #274704] |
Wed, 17 October 2007 12:31 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
As a follow up to my post from last night.
Below is from one of my Production DB servers.
1 select to_char(last_analyzed,'YYYY-MM'), count(last_analyzed)
2 from dba_tables where owner like 'DW%'
3 group by to_char(last_analyzed,'YYYY-MM')
4* order by 2
SQL> /
TO_CHAR COUNT(LAST_ANALYZED)
------- --------------------
2006-09 8
2007-02 709
2007-05 740
2007-04 810
2007-07 844
2007-06 1047
2006-12 1268
2007-09 1676
2007-08 1956
2007-01 2376
2007-03 2412
2007-10 4633
2006-11 26433
13 rows selected.
We moved onto these systems a year ago which is why Nov. 2006 has the highest count.
My point is that even when invoking DBMS_STATS.GATHER_SCHEMA_STATS daily, most tables do NOT have their statistics actually updated.
A majority of the tables had their statistics collected when they 1st arrived on this system & have not been updated since.
There are between 800 & 900 customer schemas on this system & each schema contains about 50 tables.
So what this says to me is that only about 5 tables per schema are active enough to have fresh statistics for this month,2007-10
[Updated on: Wed, 17 October 2007 12:32] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
