| statistics - to Update or Not [message #156789] |
Fri, 27 January 2006 15:43  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
As an Oracle certified Pro I have been thought that every time we have a change in the data we need to update object statistics.
Most of the times on the table with Cascade option = true for all index columns.
I have heavy OLTP - 1000 trxs/sec avarage, huge insert rate.
I use to update my stats after every big load of data - nightly on avarage 6 mln rows inserted. When I have to set up a new test environment, I run the application for a while to get enough data, and them run dbms_stats to gather the stats.
The same type of application runs on DB2. The DB2 guys are using different approach. They collect statistics from a test environment and import them into the new database. Since they are sure that they have the right execution plans, they completely disable all auto update stats options and never run statistics again. The presumption is that if once we have the right execution plans, nothing should ever change in the statistics.
They want me to apply the same approach for Oracle.
OK, but I have been thought the opposite thing! I would think that when I insert with crazy speed I would need to update the statistics to give the optimizer more info for the new structures.
Any thoughts on how the Db2 approach will reflect Oracle performance? I'm on the point to test it next week but it's kind of against of what I have been doing all my life...
Thanks a lot, mj
|
|
|
|
| Re: statistics - to Update or Not [message #156804 is a reply to message #156789] |
Fri, 27 January 2006 22:15  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Question. When you recalculate your statistics, do any of your explain plans change? If not, there is no need to analyze.
Unless your plans change, then the analyze is worthless.
The DB2 guy's approach is valid. Provided you are happy with the plans exactly as they are, and you know your data well enough that you cannot forsee future insert/update/delete patterns that would run poorly under these plans, then its OK.
The only consideration I can see is with time-sensitive histograms. Consider the SQL.
SELECT *
FROM big_table
WHERE insert_date > SYSDATE - 10
With up-to-date statistics, Oracle could use its histograms to determine that there are not many rows matching this query, and use an index to retrieve them. Without this knowlege, the "default" plan may be a full-table-scan.
If this sort of thiing is important in your system, then stick with your current approach.
_____________
Ross Leishman
|
|
|
|
| Re: statistics - to Update or Not [message #156856 is a reply to message #156789] |
Sat, 28 January 2006 14:52 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I would totally agree with the notion of using statistics from a test system to populate a new production system. And as Ross pointed out, if your data doesn't change enough for the plans to change, then you can do less or no re-analyzing.
But as he shows in his example, data does typically change over time, and that is why the CBO will change plans with it, provided you have new stats. Sure you may have the best plan possible now, but what happens in a month; is it still the best?
Perhaps if you have a fairly constant workload of your 1000 transactions, doing roughly the same thing, then full statistics every night is overkill. Maybe try once a week instead for a little while to see how it goes?
Some of the auto monitoring features that Oracle uses may help in determining when to re-gather, but then again I think most are based purely on volume or quantity of data change, which in your case means they might not help at all.
So try gathering them less frequently, once a week or once a month. And of course it could be that certain tables should be weekly and others can get away with monthly, or even less.
But the importing stats into production that were gathered in a properly representative test system -- that is a definite. But if it isn't representative test data, then stats will be bad.
|
|
|
|
| Re: statistics - to Update or Not [message #156861 is a reply to message #156856] |
Sat, 28 January 2006 19:32 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
quoting martin
>>if your data doesn't change enough for the plans to change, then you can do less or no re-analyzing.
quoting Ross
>> Unless your plans change, then the analyze is worthless.
As dexterously stated above by Ross and Scott, it is not a bad idea at all, provided it satisfies the criteria. Infact Oracle talks all about it in PLAN STABILITY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
