Home » RDBMS Server » Server Administration » DBMS_STATS, VLDB and limited window
icon8.gif  DBMS_STATS, VLDB and limited window [message #199725] Thu, 26 October 2006 00:54 Go to next message
gauss
Messages: 7
Registered: March 2005
Junior Member
I've got a problem and I was hoping someone might have some ideas how to solve it.

I look after a very large system > 10TB, 10g. My problem is with running dbms_stats.

I need to run dbms_stats on one very large partitioned table (>10 billion rows) before I can start running 'gather stale'. Now my problem is I'm restricted by the system design. The database goes down every night for a cold backup. This isn't something I can change in the near future. This gives me a window of about 19hrs.

I can get through some subpartitions and partitons but not the table in that window.

Anyone got any ideas on different angles of attack??
Re: DBMS_STATS, VLDB and limited window [message #199741 is a reply to message #199725] Thu, 26 October 2006 01:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>The database goes down every night for a cold backup.
>>This gives me a window of about 19hrs.
May be i am misreading your post or your post is incomplete.
If your database goes 'down' , how can you collect stats?

>> I can get through some subpartitions and partitons but not the table in that window.

YOu mean , every day?
In most cases, no need for that.
Unless the table is undergoing a massive change (in all partitions), you no need to collect stats.
And you no need to collect stats for the whole table.
YOu can individually collect stats on selective partitions.

[Updated on: Thu, 26 October 2006 02:15]

Report message to a moderator

Re: DBMS_STATS, VLDB and limited window [message #199745 is a reply to message #199741] Thu, 26 October 2006 01:55 Go to previous messageGo to next message
gauss
Messages: 7
Registered: March 2005
Junior Member
Mahesh Rajendran wrote on Thu, 26 October 2006

>> I can get through some subpartitions and partitons but not the table in that window.

YOu mean , every day?



No once a week I would think. Partitions are daily with ~25 mil rows a day.

Mahesh Rajendran wrote on Thu, 26 October 2006

Unless the table is undergoing a massive change (in all partitions), you no need to collect stats.



No need to collect stats?

Mahesh Rajendran wrote on Thu, 26 October 2006

And you no need to collect stats for the whole table.
The idea of partitioning is 'divide and conquer'. YOu can individually collect stats on selective partitions.



Yes you can collect partition and subpartition stats but if a query requires global (table) stats then partition stats aren't going to help.
Re: DBMS_STATS, VLDB and limited window [message #199758 is a reply to message #199745] Thu, 26 October 2006 02:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> No need to collect stats?
What i meant is
Unless the table is undergoing a massive change (in all partitions), you no need to re-collect stats.

>>but if a query requires global (table) stats then partition stats aren't going to help
Yes. That is a fact which i overlooked.

Please post how are collecting the stats ( the complete dbms_stats command line).
You must already be using GRANULARITY.
A combination of right ESTIMATE_PERCENT and DEGREE might help.
Re: DBMS_STATS, VLDB and limited window [message #199764 is a reply to message #199758] Thu, 26 October 2006 02:46 Go to previous message
gauss
Messages: 7
Registered: March 2005
Junior Member
At the moment I've broken it up into a system of procedures and tables that gather global stats for objects and then partition and sub partition stats. The parameters for objects, such as degree and sample size are set at the object level and held in a table.

These are then scheduled and run through dbms_scheduler with failures being rescheduled to run the next day if the window allows etc.

I was really asking to see if anyone had any other ideas? Fresh set of eyes and all as the system I've put in place is a LOT more complex than a simple gather_schema_stats and I wanted to make sure I hadn't overlooked anything!

[Updated on: Thu, 26 October 2006 02:47]

Report message to a moderator

Previous Topic: renaming the shchema name while exporting
Next Topic: Web server name
Goto Forum:
  


Current Time: Wed Dec 07 03:03:22 CST 2016

Total time taken to generate the page: 0.05288 seconds