Re: Discrepency between SQL/MP Explain plan, actual sequence of IOs

On 21 Nov., 09:42, wbreidbach <wolfgang.breidb...@bv-
zahlungssysteme.de> wrote:
On 20 Nov., 19:34, "Brian Ray" <be_...@xxxxxxxxxxxxx> wrote:





Working on a Pathway server that does the ole' "grab a screen's worth of
data for display in a list." Basic first page, next page, previous page kind
of stuff. It opens a cursor consisting of a two-table inner join, let's call
them TABLE1 and TABLE2. There's a lot of predicate filtering on several of
TABLE1's columns, for which we built an index, and a tiny bit of filtering
on one of TABLE2's columns (beyond the implicit filtering of the inner
join). So on inhouse SQL compilation we get an Explain much like the
following:

- Operation 1) Scan TABLE1 via the aforementioned purpose-built index.

- Operation 2) Scan TABLE2 via one of its indexes containing the one column
used in the predicate filter and then

- Operation 2.1) Hash join the result to the result of Operation 1.

And hooray, no sorts required. In this dev pathway our datasets are pretty
small and the join method usually changes when we get the software into a
bigger production database. But I'm happy because it's concentrating on
TABLE1's index right out of the gate; this is the most selective step.

So then it's just a few dozen fetches until we hit end of cursor or fill up
the reply message with result rows. Cursor is BROWSE ACCESS.

Now Measure was showing some unusually high RECORDS USED and RECORDS
ACCESSED counts for this cursor, but low LOGICAL READS on the corresponding
tables, so I popped a some SQLSADISPLAY statements after the cursor open and
the fetch. The behavior reported by SQLSADISPLAY was:

- Cursor open: A block's worth (?) of *TABLE2's* rows accessed, and a few
dozen less rows used--all the rows that would satisfy the SELECT. None of
TABLE1's rows accessed or used.

- Each fetch: 1 row accessed and used from *TABLE1*, none for TABLE2.

Which seems like the exact opposite of the strategy that the Explain plan
laid out. Between SQL compilation and runtime, can the query processor flip
strategies on the fly? And why, if the cursor is BROWSE ACCESS, does it
scoop up all the rows from one of the tables? This cursor does not trigger a
sort. Nor is it locking any rows. Since the entire result set of one of the
scans resided in a single block (I'm guessing), did the thing say "Aha, no
point in going back to the disk/disk cache for TABLE2's rows for these
fetches, I'll flip the whole access plan on its head."?

And as mentioned, I'm confused why all the SQLSTMT's records accessed, or at
least records used, are not reflected in the corresponding FILEs' logical
reads--the SQLSTMT counters are much higher than the FILE counters. Do the
records accessed/used on a cursor open somehow "not count"? Do only the rows
accessed/used during the fetch count?

Thanks,

Brian Ray

Hi Brian,

perhaps you should use Measure to check this. Just do an "ADD SQLSTMT
<your object>" and you will see, which statement produces how much I/
O.
Regards
Wolfgang- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi again,

sorry, hit the "send button" too early, you already did what I
recommended. But you should have a look at the object itself by doing
a FUP INFO..., DETAIL. If you do not have a "VALID SQL OBJECT", the
program would be SQL-compiled every time it is used and this might
explain the different explain plans. Another thing are the statistics
on the tables. Are the actual? And did you use UPDATE STATISTICS or
UPDATE ALL STATISTICS? If you use the probabilistic option, queries
might run better, but creating the statistics takes much longer.
Regards
Wolfgang
.

Relevant Pages

  • Re: Discrepency between SQL/MP Explain plan, actual sequence of IOs
    ... It opens a cursor consisting of a two-table inner join, ... There's a lot of predicate filtering on several of ... So then it's just a few dozen fetches until we hit end of cursor or fill up ...
    (comp.sys.tandem)
  • Re: URL links dont work in outlook express
    ... hovering your cursor over a link for a preset amount of time will open the ... You click on a hyperlink and an IE window opens but it doesn't load: ... > "PA Bear" wrote in message ... >> Are You Ready for WinXP SP2? ...
    (microsoft.public.windows.inetexplorer.ie6_outlookexpress)
  • Re: Word 2003 Slow Startup
    ... Is it just the initial opening that is slow? ... applications, I'd also assume they'd work properly with Word (and the problem ... > after the application opens to when I can do something with the> document ... > cursor goes away, and it takes 20 seconds before the menu dropdown> appears ...
    (microsoft.public.word.application.errors)
  • Discrepency between SQL/MP Explain plan, actual sequence of IOs
    ... Working on a Pathway server that does the ole' "grab a screen's worth of ... There's a lot of predicate filtering on several of ... So then it's just a few dozen fetches until we hit end of cursor or fill up ...
    (comp.sys.tandem)
  • Re: Dont know how to explain
    ... opens and then goes back to the previous page, when I go to favorites at ... top I place the cursor on favorites the menu drops down and before I can ... a friend said it could be my backspace button on the keyboard could ...
    (microsoft.public.windowsxp.general)