Re: System Stats opinions Please - WORKLOAD vs. NOWORKLOAD?
From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Fri, 8 Jul 2011 08:58:34 -0700 (PDT)
Message-ID: <1310140714.9229.YahooMailNeo_at_web65907.mail.ac4.yahoo.com>
There are 2 schools of thought, those who keep stats untouched if the performance is acceptable (regather stats only if there are any significant changes being introduced i.e. converting table to a partitioned table, creating new indexes, db upgrades, etc) and those who keep gathering stats on a scheduled basis. I've seen systems where bind peeking is disabled (no major data skewness) and stats gathering is mostly frozen, simply because performance stability and consistency is far more important than unpredictability of few sql execution plans turning poor. This may change with sql plan baselines. Even if you verify change in execution plans after gathering system stats, catching things like a query using a different index which has very poor caching (and hence more disk I/O's and slower response time) is going to be difficult even though the original index may incur couple more logical I/O's for each execution but with better caching (by virtue of other queries using this index more often) response time is going to be faster. Unless you are not satisfied with current system performance, I don't recommend gathering system stats again as amount of test cycle to go through can rather be combined with your 11gR2 upgrade. I've worked on IBM power 7 system with Oracle 11gR2 on it, no major stability issues observed. Thanks, Sai http://sai-oracle.blogspot.com
Date: Fri, 8 Jul 2011 08:58:34 -0700 (PDT)
Message-ID: <1310140714.9229.YahooMailNeo_at_web65907.mail.ac4.yahoo.com>
There are 2 schools of thought, those who keep stats untouched if the performance is acceptable (regather stats only if there are any significant changes being introduced i.e. converting table to a partitioned table, creating new indexes, db upgrades, etc) and those who keep gathering stats on a scheduled basis. I've seen systems where bind peeking is disabled (no major data skewness) and stats gathering is mostly frozen, simply because performance stability and consistency is far more important than unpredictability of few sql execution plans turning poor. This may change with sql plan baselines. Even if you verify change in execution plans after gathering system stats, catching things like a query using a different index which has very poor caching (and hence more disk I/O's and slower response time) is going to be difficult even though the original index may incur couple more logical I/O's for each execution but with better caching (by virtue of other queries using this index more often) response time is going to be faster. Unless you are not satisfied with current system performance, I don't recommend gathering system stats again as amount of test cycle to go through can rather be combined with your 11gR2 upgrade. I've worked on IBM power 7 system with Oracle 11gR2 on it, no major stability issues observed. Thanks, Sai http://sai-oracle.blogspot.com
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 08 2011 - 10:58:34 CDT