Re: OMLETv4 The Ultimate Visual Real Time Oracle Monitoring Tool

Martin Burbridge <pobox002_at_bebub.com> wrote in message news:<Xns94FDD861647B2pobox002bebubcom_at_216.148.227.77>...
> amjadd_at_uop.edu.jo (omlet v4) wrote in
> news:604b7892.0406030448.326ecb77_at_posting.google.com:
>
> > 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!
> >
>
> If cursor sharing is force it is still a hard parse for each cache
> miss, you just might have fewer misses because all your literals have
> been turned to bind variables so that statements that are the same,
> except for literals, can share the same execution plan. I'm beginning
> to think that you are never going to grasp this.
>
> Sadly the most common reason to have a lot of statements that differ
> only in the literal values they contain is that you have a badly
> written application that does not use bind variables. Cursor sharing
> force is designed for these applications.
>
> The less common, though actually valid reason to put literals in
> your SQL, is that you want them to be parsed separately and have
> different plans. As Howard mentioned a datawarehouse or dealing
> with skewed data.

For skewed data or "Zipfian" distributions generally, sideline the optimizer and use "index only" tables; see the paper "Integrating IR and RDBMS using cooperative indexing" Oracle Corporation, appeared in SIGIR96. Sadly, according to your "SAD" view of the Oracle world, most v$ views are badly written apps that use no bind variables when querying x$ tables. You know what?! You guessed right!

I agree with your "SAD" view; that is why I say if you can afford CURSOR SHARING set to FORCE, do it; else use SIMILAR -- actually builds histograms and is designed for issues you raised ealier; else use EXACT and use only one statement

select * from v$sysstat

without a where clause, without joins. Received on Sat Jun 05 2004 - 04:51:44 CDT