Re: Baseline usage?
- From: Seth Miller <sethmiller.sm@xxxxxxxxx>
- To: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- Date: Fri, 26 Sep 2014 16:20:42 -0500
If you know the sql id, you can use the DBMS_XPLAN.DISPLAY_CURSOR('sql id',
format=>'TYPICAL +NOTE') to confirm whether a baseline was used or not.
There may be child cursors so make sure you are looking at the right cursor
and specify which child if necessary.
Seth Miller
On Fri, Sep 26, 2014 at 3:43 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:
> Well, it's just a single baseline. But, without the baseline a particular
> query ran in about 1.3 sec. With the baseline it ran in .03 sec. The AWR
> shows the latter timing for the query (it gets run thousands of times per
> hour), but I can't find anywhere else to prove it. There is another query
> running a few hundred times per hour that was running in about 3 minutes
> that the baseline should take that about .3 seconds. However, if it uses
> the baseline it probably doesn't make enough impact to show on the AWR
> report. I would just like a second method of verifying the baseline is
> being used.
>
> Sent from my iPad
>
> On Sep 26, 2014, at 1:21 PM, Seth Miller <sethmiller.sm@xxxxxxxxx> wrote:
>
> You can have multiple plans in the plan history per sql statement. You can
> also have multiple baselines per sql statement. You can even have multiple
> baseline plans enabled and accepted but none of these things means that
> those plans will be used.
>
> Can you post examples of your findings?
>
> Seth Miller
>
> On Fri, Sep 26, 2014 at 12:57 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
> wrote:
>
>> That's what I would have thought, but the AWR shows the queries, the
>> timing on the AWR report indicates that the baseline was used, but the
>> executions number never increases.
>>
>> Sent from my iPad
>>
>> On Sep 26, 2014, at 11:38 AM, Seth Miller <sethmiller.sm@xxxxxxxxx>
>> wrote:
>>
>> If you mean number of executions, you can query
>> DBA_SQL_PLAN_BASELINES.EXECUTIONS.
>>
>> Seth Miller
>>
>> On Fri, Sep 26, 2014 at 11:27 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx>
>> wrote:
>>
>>> Does anyone know where we can check and see how often a sql plan
>>> baseline is used?
>>>
>>> Sent from my iPad--
>>> //www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>
>
Other related posts:
