Re: How to identify index range scan ?

bchorng_at_yahoo.com (Bass Chorng) wrote in message news:<bd9a9a76.0304021305.4b5d10b_at_posting.google.com>...
> Does anybody know how to identify index range scan from
> any of the v$ views ?
>
> I found index fast full scans (rowid ranges) and
> table scans (rowid ranges)in v$sesstat are not what they appear
> to be. I think they have nothing to do with index range scans.
>
> A bad index range scan (vs. Full table scan) can hurt more than
> anything else, but I found it usually very hard to identify,
> unless you already know the SQL and tkprof it.
>
> Thank.

Bass, in version 8.1 and below I think you are out of luck except for running explain plan (which tkprof can include), but with version 9+ I believe that Oracle includes v$ versions of plan tables. Unfortunately I no longer have a version 9 db to check but the view names should be in the Oracle 9+ Reference Manaul.

But if you have inappropriate range scans being ran the SQL statements would probably show up if you query the shared pool for the physical and logical IO hogs on your system rather than seek out range scans since most of them are probably OK.

HTH -- Mark D Powell -- Received on Thu Apr 03 2003 - 08:35:07 CST