SELECT Query - InVariable Time Taken to Display Data

[surajitmitra78]

Hi,

I have a query which takes 2 parameters to display data from a table which consists of around a 1 million records.

The below query gets around 150 recs in around 47 ms:
select * from statusdata where lookupid ='666666' and lid =' A';

But, when i fire the following query it gets around 78 recs in around 13.50 MIN:
select * from statusdata where lookupid ='758983' and lid =' A';

Any heads up as to what i should be looking at.

[pss]

1) Execution plan
2) Object statistics (specifically two columns used in predicates).

Is execution time consistent for the same set of predicates? Almost 14min for 1M records at most sounds too long. If it is in fact consistent then enable event 10046 to see what is really going on.

[Thomasps]

SQL> set autotrace on exp stat

and see how many blocks are accessing for each query. I think, the records for the first SELECT is densely packed (blocks) while the records for second query may not.

Statistics
----------------------------------------------------------
        206  recursive calls
          0  db block gets
         26  consistent gets
          2  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

[Kamkan]

Hi,
Are the columns used in where clause are indexed? If so, when was the statistics collected? Are you using ANALYZE command? If yes, delete the stats generated by ANALYZE command and gather new statistics using DBMS_STATS (more accurate). Try execute now and let us know.
Thanks

[mike9]

Please post the Execution Plan.

How many rows does your second query return?

[surajitmitra78]

Execution plan for both the queries:

16:38:55 SQL> select count(1) from status where ordernum = '666666' and loc = ' A';

COUNT(1)
----------
4122

Elapsed: 00:00:01.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'PK_STATUS' (UNIQUE) (Cost=4 Card=
3 Bytes=24)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
37 consistent gets
33 physical reads
0 redo size
407 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

1* select count(1) from status where ordernum = '835402' and loc = ' A'
16:40:02 SQL> /

COUNT(1)
----------
18

Elapsed: 00:02:45.05

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'PK_STATUS' (UNIQUE) (Cost=4 Card=
3 Bytes=24)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
437606 consistent gets
437594 physical reads
0 redo size
406 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

16:42:51 SQL>


i see a very high "consistent gets" and "physical reads" on the 2nd query (problem).

Can anyone give me some heads up for resolution of this kind of issue.
Just to have a background of this table, the table more than a 10 million of records and has data files upto 40-60 gb

[Bore]

That's really strange.
1) r u sure the plans are the correct ones in both cases
2) Tell us how the primary key of the STATUS table is defined. Which columns indexes the PK_STATUS index?
3) Try both queries without loc='A' and tell us what they returns as numbers and how they performs
4) Try the query only with loc='A' but without the ordernum and again, what it returns and how it performs.

I suspect this is a concatenated index on more than one column and just 6666 is much more selective than 835402, so that Oracle reads much more data in the second case, filtering it then by loc='A' so the end number is low, but the number of the processed blocks is high

Regards

[malay_biswal]

your exexcution plan says the physical reads is much higher when your resp time is high. That means the data is skewed. In plain word when number of records are higher for ordernum = '835402' than ordernum = '666666'

[surajitmitra78]

the problem i presented is only for the order# 835402 and this is not at all a problem with any of the order in this table and i have atleast a million of records which has no problem apart from this specific order.

PK_STATUS is a primary key index which has ORDERNUM+LOC+2 MORE COLUMNS and if i have to look at both the query results plan it seems to be identical. But, the moment i look at the statistics of the problem query with respect to high consistent gets and physical reads i beleive it has do some thing with the way the data is stored in the data files.

Can any one put more light on this please?

[Bore]

Yes, it is with this order because there are many records, much more than for any other for this order and you can see it by counting them. That's why the index range scann is doing that.
BTW have you tried rebuilding the index?

Regards