Re: OMLETv4 The Ultimate Visual Real Time Oracle Monitoring Tool

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Fri, 04 Jun 2004 01:18:58 GMT
Message-ID: <Xns94FDD861647B2pobox002bebubcom@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. 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.

Database Performance Tuning Guide
10g Release 1 (10.1)

http://download-west.oracle.com/docs/cd/B13789_ 01/server.101/b10752/memory.htm#32615

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

-- 
Martin Burbridge
add one to pobox002 for email

Received on Thu Jun 03 2004 - 20:18:58 CDT