SELECT Query - InVariable Time Taken to Display Data
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: SELECT Query - InVariable Time Taken to Display Data

  1. #1
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67

    SELECT Query - InVariable Time Taken to Display Data

    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.
    Regards,
    Surajit K Mitra

  2. #2
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    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.

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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.

    Code:
    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
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72

    Wink

    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

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    Please post the Execution Plan.

    How many rows does your second query return?

  6. #6
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67
    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
    Regards,
    Surajit K Mitra

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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'
    http://www.perf-engg.com
    A performance engineering forum

  9. #9
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67
    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?
    Regards,
    Surajit K Mitra

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width