| How to overcome rownum clause from select [message #487428] |
Mon, 27 December 2010 01:02  |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
I m facing some problems with high number of excutions of specific types of queries which is using only rownum clause. For exam.
select ani, rowid from tbl_smschat_upuor where rownum<=:"SYS_B_0";
DB is having high number of executions of these type of queries and these when I m checking the execution plan for the same type of queries it is accessing the full table scan.
So please kindly help me or tell me how can i resolve this performance releated issues due to this.
======================execution plan for above query
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 91289622
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1000 | 11000 | 8 (0)| 00
:00:01 |
|* 1 | COUNT STOPKEY | | | | |
|
| 2 | TABLE ACCESS FULL| TBL_SMSCHAT_UPUOR | 1000 | 11000 | 8 (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
173 consistent gets
0 physical reads
0 redo size
41959 bytes sent via SQL*Net to client
1246 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
=======================================================
Regards
Pradeep Sharma
|
|
|
|
| Re: How to overcome rownum clause from select [message #487430 is a reply to message #487428] |
Mon, 27 December 2010 01:16  |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
There is no performances related problem at Oracle level.
The only thing you can do is to call it less times.
In other words, you have to answer "why is it called so often?" and "how to modify the code to less call it?".
It is an application issue not an Oracle one.
Regards
Michel
[Updated on: Mon, 27 December 2010 01:17] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
