Re: OMLETv4 The Ultimate Visual Real Time Oracle Monitoring Tool

Martin Burbridge <pobox002_at_bebub.com> wrote in message news:<Xns94FBD78B584D3pobox002bebubcom_at_204.127.204.17>...
> amjadd_at_uop.edu.jo (omlet v4) wrote in
> news:604b7892.0406010424.7fff3994_at_posting.google.com:
>
> > Martin Burbridge <pobox002_at_bebub.com> wrote in message
> > news:<Xns94F99E03A3AC3pobox002bebubcom_at_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
> >>
> >
> > Here is a guy that cannot write SQL arguing about access plans?! Our
> > expert here skipped the "last month" predicate.
> >
>
> I also skipped a lot of things like all the columns you might want
> to see in an actual report. Its a code snippet to illustrate a point.
> The last month predicate is immaterial to the effects of cursor
> sharing so I left it out. If I wanted to be cute I would say sales
> is a view that only returns last months sales as that is what this
> application focuses on.
>
> I can write SQL perfectly well enough to know this is valid and will
> parse and run, given the presence of the dependent objects, not bad
> for pseudo code.
>
> >
> > Have you heard anything about "bind variable peeking"?! Have you heard
> > about oracle 9i?! Do you think Oracle engineers are as stupid as you
> > not to consider values of bind_1 and bind_2?!
> >
>
> Yes I have. I can also imagine that peeking at bind variables that
> needn't be variables can be less efficient than not having to bother,
> because the constant value is already a literal. Maybe you can present
> a test case showing that bind variable peeking for a fixed value is
> more efficient than just parsing a literal, but I would doubt it.

It is not only "parsing a literal". If cursor sharing is exact, then it is literally a HARD parse for each cache miss. HARD as opposed to soft and this would need a RATIO to tune and fix, dear!

I doubt you know the difference?! Do you?! You don't. Because if you only knew, you would not even use the perfectly "SNAIL executing" PL/SQL from the first place. You would immediatly start using JAVA and prepared statemnets and save your self 90% of the overhead. OR may be OCI/C++. The speed you gain: 10 folds!

Actually sharing a cursor is one of the best features Oracle 8i introduced. It took a while for the optimizer to catch up BUT Oracle 9i, 10g fixed most of the issues with selectivity predictions.

If you know the inner workings of cursors, YOU WOULD agree with me. select * from v$sysstat overhead is huge when it is issued over and over each time requesting different stat name.

Many monitoring vendors do this. You can actually check the overhead of OEM or Sotlight using OMLET. and you can do the reverse and check the overhead of OMLET using OEM or Spotlight.

Anyhow, OMLET uses jdbc prepared statements - straight forward and optimized stuff. It is OPEN source, so you optimize it further!? Received on Thu Jun 03 2004 - 07:48:07 CDT