>> 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. These literals are there for a purpose, and may
enable advanced features such as partition pruning. Cursor sharing
force will also remove these literals, meaning for example a month
to date sales report will share the same plan as one over the last
three years. The only parse you save here is the one you actually
wanted.

>
> I doubt you know the difference?! Do you?! You don't.
>

I think the above shows otherwise.

>
> 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!
>

Where did PL/SQL come from? But since you mentioned it, it does
have the advantage of being very bind friendly, so that you have to
go out of your way a bit more to create the kind of problems that
cursor sharing force fixes.

>
> 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.
>

Funnily enough, there are no mentions of magical overhead reducing
properties in the fine Tuning Manual, just this rather stark warning.

<quote>
Setting CURSOR_SHARING to FORCE forces similar statements to share
the executable SQL area, potentially deteriorating execution plans.
Hence, FORCE should be used as a last resort, when the risk of
suboptimal plans is outweighed by the improvements in cursor sharing.
</quote>

That text also appears unchanged in the 9i manual. More benefits
include:

<quote>
Note: Oracle does not recommend setting CURSOR_SHARING to
FORCE in a DSS environment or if you are using complex queries.
Also, star transformation is not supported with CURSOR_SHARING
set to either SIMILAR or FORCE. For more information, see the
"OPTIMIZER_FEATURES_ENABLE Parameter" on page 14-6.
</quote>

Oops, there go some of those desirable advanced optimizer features.
In fact there are so many drawbacks mentioned to setting this
parameter, that the preferred solution is to rewrite the application.

<quote>
Note: For existing applications where rewriting the code to use
bind variables is impractical, it is possible to use the CURSOR_
SHARING initialization parameter to avoid some of the hard parse
overhead.
</quote>

Are you seeing a pattern yet? I'm surprised you didn't seem to
know all this already. It would be pretty funny for someone who
didn't know how to program Oracle efficiently using bind variables,
had then written a performance monitoring tool without reading
the Performance Tuning Guide either.

But not so funny if someone mistakenly decided to use said
application in a real database.