Re: Question for PeopleSoft DBAs out there
Date: Mon, 21 Jan 2008 15:20:06 -0600 (CST)
The OEM (specifically, Grid Control v10.2.0.3.0) interface to the Oracle Tuning Advisor seems to have some shortcomings.
First, you have to find the statement. Unless you know the exact time and the statement is a heavy hitter, that may not be possible via OEM, especially if bind variables aren't used, the variables change, and old ones age out relatively quickly. Or, if bind variables are used in a DB that is multibyte, the bind value may not be able to be evaluated via trace, due to an Oracle BUG.
Second, OEM's use of the Oracle Tuning Advisor seems to be limited to those statements already executed. I haven't seen a way to manually enter a statement to tune. (I suppose that's somewhat related to the first item)
Third, at least in a v10.1.0.5 DB (and maybe more), there's an Oracle BUG that causes the Oracle Tuning Advisor to ORA-600 when referencing in-memory statements via SQL ID.
My solution is to join the Toad for Oracle Beta group at Yahoo Groups. Of course, the prerequisite requires a recent and licensed copy of Toad to be installed, but it's well worth it, in my humble-but-not-completely-unbiased opinion. :)
> You could try using OEM to identify a good plan and store that and activate
> it. That is one of the real nice tools that 10g has for dealing with canned
> code you cant modify.