Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> QUESTION - Behavior of returning records on a query
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
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 Received on Fri Feb 24 2006 - 17:12:46 CST