Re: AWR Sample Report

On Nov 18, 10:14 pm, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>

> Joel Garry,
> Can you explain again on what you said ?
>
> Thanks in Advance.
>
> With Regards,
> Raja.

A magic bullet (synonym for silver bullet) is a magical simple answer to a complex problem. In Oracle performance tuning, it refers to setting a parameter or performing some process to fix a problem, without properly defining the problem or solution. There has been some controversy over this (that's an understatement!) in the Oracle world. In general, a proper methodology for tuning will have ways to define problems and solutions. Unfortunately, using magic works often enough that some people consider it proper business practice. It becomes difficult to distinguish it from best practices and reasonable cookbooking (the latter would be making stuff work just by following directions, without thinking about it - like Oracle installs, ignoring configuration issues - XE is a good thing for a certain solution set, for example). As Arthur Clarke (IIRC) put it, sufficiently advanced technology is indistinguishable from magic. The problem with magic bullets is they overstate how advanced the technology is, so Oracle tuning is distinguishable from magic, as you hopefully will discover as you learn AWR and statspack.

Bind peeking was kind of an overreach in the technology on Oracle's part, in my opinion. Fortunately, when Oracle adds a feature like this they usually leave in some way to turn it off, often using a mystical incantation called an underscore init parameter. The problem they were addressing was apparently increasing performance by putting in some code to decide whether or not to make the optimizer have to come up with a new plan. This created a new problem, which is that it decides wrong, under some fairly common conditions - this is where I thought they overreached, it should have been obvious - but I don't know all the ins and outs of it, and don't know what they were intending, this is just my opinion. Since optimization is potentially done with any sql, and servers tend to not just do one thing, I think the goof was missing the same sql could do quite different things - getting a list of all product numbers for a drop down list of values, versus just a few for a report, for example. So is there no deterministic way to figure out whether bind peeking is a good idea? As Steve said, setting the underscore variable had helped a problem - only to bite them later. This is a general problem of oversimplification, solving problems with magic bullets.

So I ran into a performance problem with some app code, where it is not even asking Oracle to do anything. Some customers of the vendor had discovered setting the underscore variable helped their vague performance problem (which for all I know was another problem I had run into, solved by adding an index). So when I tell the vendor about the performance problem, they tell me to set the underscore variable. I then laugh about it on usenet.

jg

--
@home.com is bogus.
"This site is intended solely for use by Oracle's authorized users.
Use of this site is subject to the Legal Notices, Terms for Use and
Privacy Statement located on this site. Use of the site by customers
and partners, if authorized, is also subject to the terms of your
contract(s) with Oracle. Use of this site by Oracle employees is also
subject to company policies, including the Code of Conduct.
Unauthorized access or breach of these terms may result in termination
of your authorization to use this site and/or civil and criminal
penalties. "  Ahh, so that's how I terminate civial and criminal
penalties.