Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: OMLETv4 The Ultimate Visual Real Time Oracle Monitoring Tool

Re: OMLETv4 The Ultimate Visual Real Time Oracle Monitoring Tool

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Sun, 30 May 2004 19:34:36 GMT
Message-ID: <Xns94F99E03A3AC3pobox002bebubcom@204.127.199.17>


amjadd_at_uop.edu.jo (omlet v4) wrote in
news:604b7892.0405290545.1b3fb9df_at_posting.google.com:

> Ed Stevens <nospam_at_noway.nohow> wrote in message
> news:<4269b012v3vns4nputqbo4g5sm8pskrhnl_at_4ax.com>...
>> On 26 May 2004 03:19:27 -0700, amjadd_at_uop.edu.jo (omlet v4) wrote:
>>

<snip>
>>
>> 4) "Set the following three init.ora parameters to: cursor_sharing =
>> force . . . " HONK! Wrong answer! If I do that, my acess plans
>> change, quite possibly for the worse.
>>
>> I don't consider myself an expert in this field, so if even I could
>> find problems without even opening the product . . .
>
> Ed,
>
> I doubt you know anything about cursor_sharing or access plans.
>

And you would? I know exactly what cursor sharing force does. It turns all literals in your select statement into bind valiables before they are parsed, optimized and executed. And that is all

So a report to find everyone in your department that made above 100,000 in sales last month which might have been

select ename from emp, sales
where deptno = 30
and qty > 100000
and emp.empid = sales.empid

becomes something like

select ename from emp, sales
where deptno = :bind_1
and qty > :bind_2
and emp.empid = sales.empid

If you can't see what this might do to access plans hopefully others considering setting this parameter do, or intend to find out before setting it.

>
> Anyway, most DBA's who use scripts, usually hurt their database;
> cursor sharing reduces overhead when accessing v$sysstat and
> v$filestat which most tuning script queries select from. I doubt you
> had any idea about this! do you? I thought so.
>

The single only reason for this setting is it will stop an application that doesn't use bind variables from bringing the database to its knees. If you have an application that has this bug and requires this parameter set, I would consider two options

1.
Give the app its own instance, preferably with a small sga. Then either set this parameter and let it limp along, or refuse to set it and watch it crash itself repeatedly, forcing the developer or vendor to fix their code. I like the sound of the latter myself.

2.
Use a log on trigger for the app that needs fixing.

While it is not beyond the bounds of belief that some odd data dictionary queries may actually benefit from this state of affairs, it should never be set at instance level, unless there is only one app in that instance and that app is so poorly designed that it needs it.

If you don't believe me because I am also an idiot like Ed, then check out what Tom Kyte has to say on the matter

</quote>

in short, cursor sharing is a crutch that MIGHT be useful in some cases to help a poorly written program survive for the period of time the developers are hard at work CORRECTING their bug and putting bind variable support into their application

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:518060 9822543

>
> Compare OMLET overhead to any other monitoing software including OEM
> and you would be shocked! Did I forget to mention I am a ex-Oracle
> kernel/Server Technology employee!?
>

I certainly am shocked. Imagine a monitoring tool that may render your database immobile, unless you agree to destabilize all your existing applications by changing their SQL with a single parameter.

-- 
Martin Burbridge
add one to pobox002 for email
Received on Sun May 30 2004 - 14:34:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US