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