DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: performance issue

  1. #1
    Join Date
    Jan 2008
    Posts
    5

    performance issue

    Hi Tuning gurus
    this querry works fine for lesser number of rows
    eg :--
    where ROWNUM <= 10 )
    where rnum >=1;
    but takes lot of time as we increase rownum ..
    eg :--
    where ROWNUM <= 10000 )
    where rnum >=9990;
    results are posted below
    pls suggest me
    oracle version -Oracle Database 10g Enterprise Edition
    Release 10.2.0.1.0 - Prod
    os version red hat enterprise linux ES release 4
    also statistics differ when we use table
    and its views
    results of view v$mail
    ---------------------------------------
    [select * from
    ( select a.*, ROWNUM rnum from
    ( SELECT M.MAIL_ID, MAIL_FROM, M.SUBJECT
    AS S1,CEIL(M.MAIL_SIZE) AS MAIL_SIZE,
    TO_CHAR(MAIL_DATE,'dd Mon yyyy hh:mi:ss
    am') AS MAIL_DATE1, M.ATTACHMENT_FLAG,
    M.MAIL_TYPE_ID, M.PRIORITY_NO, M.TEXT,
    COALESCE(M.MAIL_STATUS_VALUE,0),
    0 as email_address,LOWER(M.MAIL_to) as
    Mail_to, M.Cc, M.MAIL_DATE AS MAIL_DATE,
    lower(subject) as subject,read_ipaddress,
    read_datetime,Folder_Id,compose_type,
    interc_count,history_id,pined_flag,
    rank() over (order by mail_date desc)
    as rnk from v$mail M WHERE M.USER_ID=6 AND M.FOLDER_ID =1) a
    where ROWNUM <= 10000 )
    where rnum >=9990;]

    result :
    --------------------------------
    11 rows selected.
    Elapsed: 00:00:03.84
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14735 Card=10000 B
    ytes=142430000)
    1 0 VIEW (Cost=14735 Card=10000 Bytes=142430000)
    2 1 COUNT (STOPKEY)
    3 2 VIEW (Cost=14735 Card=14844 Bytes=211230120)
    4 3 WINDOW (SORT) (Cost=14735 Card=14844 Bytes=9114216)
    5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MAIL' (TABLE) (C
    ost=12805 Card=14844 Bytes=9114216)
    6 5 INDEX (RANGE SCAN) OF 'FOLDER_USERID' (INDEX) (C
    ost=43 Card=14844)

    Statistics
    ----------------------------------------------------------
    294 recursive calls
    0 db block gets
    8715 consistent gets
    8669 physical reads
    0 redo size
    7060 bytes sent via SQL*Net to client
    504 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    6 sorts (memory)
    0 sorts (disk)
    11 rows processed

    SQL> select count(*) from v$mail;
    Elapsed: 00:00:00.17
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=494 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FAST FULL SCAN) OF 'FOLDER_USERID' (INDEX) (Cost=
    494 Card=804661)

    Statistics
    ----------------------------------------------------------
    8 recursive calls
    0 db block gets
    2171 consistent gets
    2057 physical reads
    260 redo size
    352 bytes sent via SQL*Net to client
    504 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    results of original table mail
    ---------------------------------------
    [select * from
    ( select a.*, ROWNUM rnum from
    ( SELECT M.MAIL_ID, MAIL_FROM, M.SUBJECT
    AS S1,CEIL(M.MAIL_SIZE) AS MAIL_SIZE,
    TO_CHAR(MAIL_DATE,'dd Mon yyyy hh:mi:ss
    am') AS MAIL_DATE1, M.ATTACHMENT_FLAG,
    M.MAIL_TYPE_ID, M.PRIORITY_NO, M.TEXT,
    COALESCE(M.MAIL_STATUS_VALUE,0),
    0 as email_address,LOWER(M.MAIL_to) as
    Mail_to, M.Cc, M.MAIL_DATE AS MAIL_DATE,
    lower(subject) as subject,read_ipaddress,
    read_datetime,Folder_Id,compose_type,
    interc_count,history_id,pined_flag,
    rank() over (order by mail_date desc)
    as rnk from mail M WHERE M.USER_ID=6 AND M.FOLDER_ID =1) a
    where ROWNUM <= 10000 )
    where rnum >=9990;]
    result :
    -----------------------
    11 rows selected.
    Elapsed: 00:00:03.21
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14735 Card=10000 B
    ytes=142430000)
    1 0 VIEW (Cost=14735 Card=10000 Bytes=142430000)
    2 1 COUNT (STOPKEY)
    3 2 VIEW (Cost=14735 Card=14844 Bytes=211230120)
    4 3 WINDOW (SORT) (Cost=14735 Card=14844 Bytes=9114216)
    5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MAIL' (TABLE) (C
    ost=12805 Card=14844 Bytes=9114216)
    6 5 INDEX (RANGE SCAN) OF 'FOLDER_USERID' (INDEX) (C
    ost=43 Card=14844)

    Statistics
    ----------------------------------------------------------
    1 recursive calls
    119544 db block gets
    8686 consistent gets
    8648 physical reads
    0 redo size
    13510 bytes sent via SQL*Net to client
    4084 bytes received via SQL*Net from client
    41 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    11 rows processed
    SQL> select count(*) from mail;
    Elapsed: 00:00:00.34
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=494 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FAST FULL SCAN) OF 'FOLDER_USERID' (INDEX) (Cost=
    494 Card=804661)

    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    2183 consistent gets
    2062 physical reads
    72 redo size
    352 bytes sent via SQL*Net to client
    504 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed
    Thanks n regards

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you think 3 seconds elapsed time for 10000 rows is high?

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Your select statement shows 10K cardinality but actually 11 records being retrieved. May be tables need to analyze. As Tamil said, Why do yo9u think it's a perf issue as execution time is hardly 4 sec.
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Jan 2008
    Posts
    5

    performance issue

    Quote Originally Posted by tamilselvan
    Do you think 3 seconds elapsed time for 10000 rows is high?

    hi thanx for reply

    you are right but its actually retrieving 10 rows at a time
    anyways thanx a lot
    tc

  5. #5
    Join Date
    Jan 2008
    Posts
    5
    Quote Originally Posted by malay_biswal
    Your select statement shows 10K cardinality but actually 11 records being retrieved. May be tables need to analyze. As Tamil said, Why do yo9u think it's a perf issue as execution time is hardly 4 sec.
    hi thanx for response
    you are right but its actually retrieving 10 rows at a time
    i also analyzed the table
    anyways thanx a lot
    tc

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The point is not how many rows are actually returned to the user.

    The point is how much work the database must do to return those rows.

    In the first case, the database server can stop working once it has determined the first 10 rows.

    In the second case, the database server must keep working through 10,000 rows before it can return 10 rows.

    The methodology of returning data that you are using ('paging' logic, or moving window) is made to return the front part of the dataset as quickly as possible.

    For example, if the user sees 10 records per page (think google), the first page should be returned as quickly as possible. Your logic handles that. Then, each subsequent page takes a tiny bit longer. This is fine because most users do not page more than a few pages into any search before refining their search. So, one does not usually have to worry about the performance of returning the 1000th page, as you seem to be.

    If your users are going to regularly page to the 1000th page, then a different paradigm is called for - perhaps returning the entire resultset in one call and the breaking it into pages at the application level.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Code:
    ---------------------------------------
    [select * from
    ( select a.*, ROWNUM rnum from
    ( SELECT M.MAIL_ID, MAIL_FROM, M.SUBJECT
    AS S1,CEIL(M.MAIL_SIZE) AS MAIL_SIZE,
    TO_CHAR(MAIL_DATE,'dd Mon yyyy hh:mi:ss
    am') AS MAIL_DATE1, M.ATTACHMENT_FLAG,
    M.MAIL_TYPE_ID, M.PRIORITY_NO, M.TEXT,
    COALESCE(M.MAIL_STATUS_VALUE,0),
    0 as email_address,LOWER(M.MAIL_to) as
    Mail_to, M.Cc, M.MAIL_DATE AS MAIL_DATE,
    lower(subject) as subject,read_ipaddress,
    read_datetime,Folder_Id,compose_type,
    interc_count,history_id,pined_flag,
    rank() over (order by mail_date desc)
    as rnk from v$mail M WHERE M.USER_ID=6 AND M.FOLDER_ID =1) a
    where ROWNUM <= 10000 )
    where rnum >=9990;]
    Post the tkprof output for this query.

    You should not use "order by or rank" in the pagination query.

    Tamil

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Quote Originally Posted by tamilselvan
    You should not use "order by or rank" in the pagination query.
    Not sure I follow what you're after here - a pagination query, by definition, requires an order by or rank...
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    My demo explains What I said:

    Code:
    select * from (
            select a.object_id, a.status, rownum rnum
              from ( select object_id, status from page_table) a
            where rownum <5000
     ) where rnum >= 4990
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.02       0.01          0          2          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0         65          0          10
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.02       0.01          0         67          0          10
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 36
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
         10  VIEW  (cr=65 pr=0 pw=0 time=3988 us)
       4999   COUNT STOPKEY (cr=65 pr=0 pw=0 time=5064 us)
       4999    TABLE ACCESS FULL PAGE_TABLE (cr=65 pr=0 pw=0 time=58 us)
    
    ********************************************************************************
    
    select * from (
            select a.object_id, a.status, rownum rnum
              from ( select object_id, status from page_table
                      order by 2,1 ) a
            where rownum <5000
     ) where rnum >= 4990
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          2          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.03       0.02          0        563          0          10
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.03       0.03          0        565          0          10
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 36
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
         10  VIEW  (cr=563 pr=0 pw=0 time=29205 us)
       4999   COUNT STOPKEY (cr=563 pr=0 pw=0 time=29406 us)
       4999    VIEW  (cr=563 pr=0 pw=0 time=29397 us)
       4999     SORT ORDER BY STOPKEY (cr=563 pr=0 pw=0 time=24394 us)
      40501      TABLE ACCESS FULL PAGE_TABLE (cr=563 pr=0 pw=0 time=55 us)
    
    ********************************************************************************
    
    alter session set sql_trace = false
    Look at the row source.
    The first query stopped after reading 4999 rows.
    The 2nd query did full tablescan.

    That's why I said, in the pagination, inner most query should not contain order by clause. But your business logic needs, then go for it. And do not expect the query would perform in sub seconds.

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Sorry, but I have no clue where you're coming from on this.

    Of what possible use is a pagination query *without* an order by?

    It would be practically meaningless.

    When, exactly, have you asked for a page of data and cared absolutely nothing about the sort? I've never seen it happen. Plus, what guarantee do you then have that you won't end up showing the same data on page 2?

    The only way it makes sense to page through data is if the ordering of said data *does not change* between pages. And you should know as well as anyone that *nothing* guarantees the order of a dataset *except* an ORDER BY. Without one, the optimizer could, for any number of reasons, decide to use a different execution plan on page 20 and suddenly you are seeing duplicate data on 20. If you then hit previous page, if the new plan is used again, then page 19 will look nothing like it did before. The only way that pagination of data makes sense, or can possibly be useful, is with an ORDER BY.

    And the difference in explain plans is not a rigorous example. If the order by is on the primary index of the query, then a tablescan will *not* be performed. There are also any number of subsequent steps of the plan that can be shaved off because of the row limit - not just the fetching of the base table.

    That's actually part of the task of writing an optimal pagination query is figuring out how to get the most out of the pagination logic. And removing the ORDER BY to get the performance is simply not an option.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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