OraFAQ Forum: Server Administration » DBMS_STATS for a Partitioned table

Home » RDBMS Server » Server Administration » DBMS_STATS for a Partitioned table (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_STATS for a Partitioned table [message #462229]

Wed, 23 June 2010 04:34

Ankurg1981
Messages: 3
Registered: February 2009
Location: Mumbai

Junior Member

Hi All,

This issue is a bit urgent for me. Any immediate help will be very useful.

I have a partitioned table with 29 partitions each having 3 subpartitions

This table is truncated daily and fresh data is loaded. Approx 20 mn every day

I have a process that updates 2-3 columns of this table and does some kind of reporting using this table and this process takes 5 - 7 mins to run.
I have to gather stats daily on this table to ensure that my reporting process runs effectively

I am using DBMS_STATS to do that. But it takes 40 mins to 1 hr to gather stats Sad

exec DBMS_STATS.gather_table_stats (user,'Table',estimate_percent=>100,degree=>8,cascade=>TRUE);

Is there is any way I can speed up this process???
I want to complete stats gathering in less than 5-10 mins
This table is populated throughout the day. Is there is any way I can do incremental stats gathering ?

Thanks in advance

- Ankur

Report message to a moderator

Re: DBMS_STATS for a Partitioned table [message #462323 is a reply to message #462229]

Wed, 23 June 2010 09:20

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Always post the exact Oracle Version with 4 digits and OS.
As far I know, incremental statistics is available from 11g.
Agree with you, gathering global stats could be very painful.
>>I have a process that updates 2-3 columns of this table
Is that restricted to a single partition?

Report message to a moderator

Re: DBMS_STATS for a Partitioned table [message #462325 is a reply to message #462229]

Wed, 23 June 2010 09:26

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Search metalink for "APPROX_GLOBAL AND PARTITION".
Seems it is an alternative for incremental statistics in 10g ( with some constraints) and the database needs to be on 10.2.0.5

Edit:
More information and write up here.
http://optimizermagic.blogspot.com/2009/02/maintaining-statistics-on-large.html

As usual, first test in a safe non-production environment.

[Updated on: Wed, 23 June 2010 09:27]

Report message to a moderator

Re: DBMS_STATS for a Partitioned table [message #462386 is a reply to message #462229]

Wed, 23 June 2010 21:21

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

UPDATE : See the EDIT below.

If you are TRUNCATing the table and reloading data every day and (I presume) not actually creating new partitions ........
any incremental statistics method wouldn't work.

However, you can *restore* statistics using DBMS_STATS.RESTORE_TABLE_STATS. Thus, you could restore the previous days statistics back. Of course, if you keep doing this every day, your statistics would become "stale", so you could do a Gather_Stats once a week or so.
Restore Table Statistics would work if
a. the data volumes are similar day-to-day
b. data skew is similar day-to-day
However, any columns that store date and sequence values that increment every day may need refreshing of statistics as actual values may soon be beyond the range of min/max stored in the statistics.

EDIT : If you are TRUNCATing the table do you check the statistics on the table after the truncate ? The HighWaterMark would be reset but statistics would still be usable so you wouldn't even have to re-Gather or restore statistics if data volume and data skew don't change significantly from one day to the next.

Hemant K Chitale

[Updated on: Wed, 23 June 2010 23:14] by Moderator

Report message to a moderator

Previous Topic:	need help to clear buffer pool (merged)
Next Topic:	script to automate startup/shutdown of mutliple instance in one db server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 10:42:10 CST 2024