-
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
-
Do you think 3 seconds elapsed time for 10000 rows is high?
-
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.
-
performance issue
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
-
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
-
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.
-
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
-
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...
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|