Re: SQL Tuning Regarding System CPU Stats

On May 17, 7:00 pm, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> On Thu, 17 May 2007 08:55:33 -0700, andrew.markiewicz wrote:
> > Hello all.
> > We are in the process of upgrading from 9i to 10g and starting to use
> > system statistics. Since the system statistics are used per database
> > instance and not per user/schema, what approach is recommended for
> > tuning a production system within a development environment?
>
> The best approach is: if it ain't broken, don't fix it. Besides that,
> what does it mean to "tune production system"? I was under the impression
> that applications are what needs tuning, not the system. There is a little
> known book, published by O'Reilly few years ago, named "Optimizing Oracle
> for Performance", written by two guys, Cary Millsap and Jeff Holt. The
> book is as timeless as Mona Lisa, but not as inscrutable. It certainly
> doesn't depend on Oracle version. It deals with performance problems from
> the philosophical point of view and answers the question of life universe
> and everything. There is also a prolific author named Tom Kyte who writes
> various things, Oracle books among other things. I can wholeheartedly
> recommend his collected works, too. Further reading would include Cost-
> Based fundamentals by Jonathan Lewis, RAC Handbook by K. Gopalakrishnan,
> Oracle 10 Wait events by Kirti Deshpande (phenomenal book!!!) and Dan
> Tow's SQL Tuning.
> For further specialized topics, I would recommend numerous available
> articles by Tom Kyte, Jonathan Lewis, Wolfgang Breitling, Tim Gorman,
> The Hotsos gang, Julian Dyke, Howard Rogers, Connor McDonald as well
> as specialized DW books by Ralph Kimball and Gavin Powell.
>
> --http://www.mladen-gogala.com

I frequent Tom Kyte's site and have both his books, studied and restudied Wolfgang Brietling's paper on the 10053 trace such that it is tattered, highlighted and stained with coffee and tears, own and read Jonathon Lewis's "Cost Based Oracle Fundamentals", and peruse OTN, Metalink, groups such as this and other forums. I am familiar with HOW to tune, but was interested in how others approach setting up the environment for tuning with using system statistics.

According to Jonathon's book "Cost Based Oracle Fundamentals", p. 82, "The default value for this parameter (_optimizer_cost_model) is 'choose', and 9i will choose to use CPU costing only if the system statistics exist, but 10g will always choose to use CPU costing and then synthesize some statistics if there aren't any in place....". So db version does matter to how the CBO will generate execution plans. I don't particularly care for the idea of Oracle generating CPU stats that will then be used to generate SQL statements, therefore we must gather system stats. Also, we currently set the optimizer_index_cost_adj and optimizer_index_caching values which will continue to have an influence on the CBO in conjunction with the CPU stats. So the use of those values will need to be reviewed as well.

I suppose I can't speak for others shops, but here we typically have our best machines for production and the next best for test. There are system differences between the machines and databases (CPU speed, allocated memory for SGA, size of buffer cache, speed of the hard drive the datafiles are on, existence of an OS file or block cache, etc). I am not a SysAdmin but I think these differences will have an impact on the system statistics that would be optimal for that machine.
So I feel there are two (possibly three) options for how we approach setting up our IS environment for development (which has nothing to do with tuning individual SQL.)

1. Import stats gathered from the production machines so the CBO on the test db should choose a similar/same plan that will be seen in production. Potential issues are that developers may get a plan that does not actually perform well on the test machine but would do well on production. I can envision developers noticing a difference or some poor performance and tune it (perhaps with hints) on the test system, which will then not be optimal in production. To some extent I suppose this happens today since the test machine may not be as good and may be slower in response, but the execution plans will be tuned to minimize consistent gets.
2. Use the system stats for the machine the db is on. This may allow you to get the best performance on that machine, but then I see a potential for plan instability when transferred to production. I don't like surprise plans in the production db.

In summary, my main issue is that I see performance tuning with system stats as being more complicated in a holistic sense than previous releases without system statistics. I haven't had a great deal of opportunity to actually get my hands dirty and test out using the system stats, but as I am upgrading the db and settng up a test environment for the developers, these questions arose and I am wondering how others have approached the issue. If it even is one. Perhpas I am just overthinking it.

Andrew Received on Fri May 18 2007 - 09:36:13 CDT