Hello everybody,


I'm analyzing sysptprof database cause it gives is huge information on our
table/index usage.

I'm trying to detect the sequential scans but I'm not sure if I interpret it
well.

Is it correct that I need to do the following : (isreads/seqscans)* 100 to
compare seqscans vs qty reads ?
Cause I have one table which had only 1 isreads and 2 seqscans....

Or do I need to do the bufreads/seqscans* 100 ?

I hope someone can clarify this, cause the manuals aren't always that clear
on these topics

Thanks in advance,


Bjorn

Sorry,


I reversed isreads and seqscans, just a type-o


"Bjorn De Waele" <bjornREMTHIS@pandora.Be> wrote in message
news:FVFWd.31036$Zr1.3140715@phobos.telenet-ops.be...
> Hello everybody,
>
>
> I'm analyzing sysptprof database cause it gives is huge information on our
> table/index usage.
>
> I'm trying to detect the sequential scans but I'm not sure if I interpret
> it well.
>
> Is it correct that I need to do the following : (isreads/seqscans)* 100 to
> compare seqscans vs qty reads ?
> Cause I have one table which had only 1 isreads and 2 seqscans....
>
> Or do I need to do the bufreads/seqscans* 100 ?
>
> I hope someone can clarify this, cause the manuals aren't always that
> clear on these topics
>
> Thanks in advance,
>
>
> Bjorn
>

Bjorn De Waele wrote:
> Hello everybody,
>
>
> I'm analyzing sysptprof database cause it gives is huge information on our
> table/index usage.
>
> I'm trying to detect the sequential scans but I'm not sure if I interpret it
> well.
>
> Is it correct that I need to do the following : (isreads/seqscans)* 100 to
> compare seqscans vs qty reads ?
> Cause I have one table which had only 1 isreads and 2 seqscans....
>
> Or do I need to do the bufreads/seqscans* 100 ?
>
> I hope someone can clarify this, cause the manuals aren't always that clear
> on these topics

That's the calc that I've seen posted before. Personally I prefer to
compare seqscans to isopens as that is once per query and the number of seq
scans versus number of queries I think is a more accurate predictor or
whether you have too many queries performing scans than comparing to reads.

Art S. Kagel

Hello Art,


I found the isopen key in sysprofiles.
In fact, I'm about to tune my indices/tables.

That's why I want to compare my seq scans vs my index usage on table level.

Do you think this is possible ?

Thanks a lot.


"Art S. Kagel" <kagel@bloomberg.net> wrote in message
news:422CDC7F.4070406@bloomberg.net...
> Bjorn De Waele wrote:
>> Hello everybody,
>>
>>
>> I'm analyzing sysptprof database cause it gives is huge information on
>> our table/index usage.
>>
>> I'm trying to detect the sequential scans but I'm not sure if I interpret
>> it well.
>>
>> Is it correct that I need to do the following : (isreads/seqscans)* 100
>> to compare seqscans vs qty reads ?
>> Cause I have one table which had only 1 isreads and 2 seqscans....
>>
>> Or do I need to do the bufreads/seqscans* 100 ?
>>
>> I hope someone can clarify this, cause the manuals aren't always that
>> clear on these topics
>
> That's the calc that I've seen posted before. Personally I prefer to
> compare seqscans to isopens as that is once per query and the number of
> seq scans versus number of queries I think is a more accurate predictor or
> whether you have too many queries performing scans than comparing to
> reads.
>
> Art S. Kagel

Bjorn De Waele wrote:
> Hello Art,
>
>
> I found the isopen key in sysprofiles.
> In fact, I'm about to tune my indices/tables.
>
> That's why I want to compare my seq scans vs my index usage on table level.
>
> Do you think this is possible ?

Possible. To identify which tables are seeing seqscans, yes. To determine
whether the quantity is excessive, I'm uncomfortable. First I'd want to
update stats and see if the rate of seqscans for each table remains at the
same level. Then I'd like to try to trap the specific queries that are
causing the scans to see if specific filter values are so pervasive in that
table as to make a scan the most efficient route so that I really should not
worry except to examine if I can optimize the query with a more efficient
filter or (OK you got me on this one) another index that includes another
column or reorders columns from another index to eliminate the scan.

Art S. Kagel

> Thanks a lot.
>
<SNIP>