Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> QUESTION - Behavior of returning records on a query

QUESTION - Behavior of returning records on a query

From: BD <bobby_dread_at_hotmail.com>
Date: 24 Feb 2006 15:12:46 -0800
Message-ID: <1140822766.071996.240890@t39g2000cwt.googlegroups.com>


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 Received on Fri Feb 24 2006 - 17:12:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US