Re: QUESTION - Behavior of returning records on a query

From: noone <noone_at_nowhere.com>
Date: Sat, 25 Feb 2006 03:49:36 GMT
Message-ID: <ktQLf.35100$Jd.23846@newssvr25.news.prodigy.net>

BD wrote:
> Hi, all.
>
> I have a query I have been asked to tune. I'll describe my issue, and
> then outline the specifics below.
>
> Basically, several variants of this query are being used. In each
> varation, one variable (field) is being changed - it's likely in a
> drop-down or something. The complaint is that the speed of returning
> the result set does not appear to be consistent as this field is
> changed.
>
> This field can be one of 9 different values. The developer who has
> asked me to look at it indicates that when the application runs, it
> returns values _immediately_ if this value is set to one thing, but
> takes several seconds if it is set to something else.
>
> I ran the query in a SQLPlus window, and noted how long it took, and
> how many rows were returned, for each value.
>
> In _MOST_ cases, the results are the same, in that: I see no results
> for about 15 seconds, and then the result set is returned.
>
> When the field is set to one specific value, I see records returned
> INSTANTLY, although there are many more records returned, and it takes
> about twice as long to return the whole result set as it does in any
> other case.
>
> What I do not understand is:
>
> -Why am I seeing records returned instantly in one case, and in every
> other case, the query is processed for almost 15 seconds before the
> result set is returned?
> -How can I tune this? I know that sounds like a 'dopey' question, but
> my problem is that I cannot understand how the behavior is different,
> and so I am unclear what to address!
>
> I don't expect the group wants to see create statements for all the
> tables and indexes here... so I'll start with the query, the explain
> plan, the values returned, and an outline of the behavior which I can't
> make sense of.
>
> Thanks for reading this far! ;)
>
> QUERY:
>
> Select
> tblCollect.*,
> tblCollectDetail.*,
> tblAccountInfo.fstrListFormatName as fstrPerson,
> tblAccountInfo.fstrID as fstrAccountID,
> tblAccountInfo.fstrAccountType as fstrAccountType,
> tblAccountInfo.fstrOffice as fstrLocalOffice,
> tblAccountInfo.fdtmEnd

>>From tblCollect, tblCollectDetail, tblAccountInfo

> WHERE
> tblCollect.fstrCategory='VALUE1' AND /* this is the value being
> changed */
> tblCollectDetail.fdtmClosedDate='31-Dec-9999' AND
> tblCollectDetail.flngVer=0 AND
> tblAccountInfo.flngAccountKey=tblCollect.flngAccountKey AND
> tblCollectDetail.flngCollectKey=tblCollect.flngCollectKey;
>
> Value being changed: "WHERE tblCollect.fstrCategory='<value>'
>
> Explain Plan:
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1;2372
> NESTED LOOPS 1; 202; 2372
> NESTED LOOPS 1; 155; 2370
> TABLE ACCESS FULL GTAPP.TBLCOLLECTDETAIL 1; 67; 2369
> TABLE ACCESS BY INDEX ROWID GTAPP.TBLCOLLECT 34 K; 2 M; 1
> INDEX UNIQUE SCAN GTAPP.PK_COLLECT 34 K
> TABLE ACCESS BY INDEX ROWID GTAPP.TBLACCOUNTINFO 378 K;16 M;2
> INDEX UNIQUE SCAN GTAPP.PK_ACCOUNTINFO 378 K; 1
>
> Results:
>
> 'VALUE1'
> Exists in 3966 records
> Query returns 11 rows after 10 sec
>
> 'VALUE2'
> Exists in 4 records
> Query returns 0 rows after 12 sec
>
> 'VALUE3'
> Exists in 10725 records
> Query returns 3 rows after 12 sec
>
> 'VALUE4'
> Exists in 143 records
> Query returns 5 rows after 12 sec
>
> 'VALUE5'
> Exists in 1471675 records
> Query returns 7585 rows after 23.28
> ****QUERY BEGINS RETURNING VALUES IMMEDIATELY
>
> Sorry for the length of the post, and thanks for any thoughts!
>
> BD
>

it is a matter of selectivity... apparently there are approximately 1.5M records in this db.. retrieving 4 out of 1.5 takes some time especially when it is doing a full table scan on collectdetail. etc..

But to start returning 1.47M records - does not take any time at all to start finding matches...

Can you tell us what indexes exist on these tables? do they include the accountid, collectid, category? have you analyzed the indexes/tables recently? Received on Fri Feb 24 2006 - 21:49:36 CST