 DBMS_STATS, VLDB and limited window [message #199725] |
Thu, 26 October 2006 00:54  |
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  |
 |
Mahesh Rajendran
Messages: 10707
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 |
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 #199764 is a reply to message #199758] |
Thu, 26 October 2006 02:46 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
