| Query using rownum in where cluase with millions of records [message #485580] |
Wed, 08 December 2010 23:10  |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
There is a table in Database with millios of records and a query --- Select rowid, ANI, DNIS, message from tbl_sms_talkies where rownum<=:"SYS_B_0" ---- using the high CPU and also this query having high number of executions.
Help me to resolve this query performance issue with the database.
Regards
Pradeep
|
|
|
|
|
|
|
|
| Re: Query using rownum in where cluase with millions of records [message #485587 is a reply to message #485585] |
Wed, 08 December 2010 23:47  |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
CREATE TABLE TBL_SMS_TALKIES
(
ANI VARCHAR2(12 BYTE),
DNIS VARCHAR2(12 BYTE),
MESSAGE VARCHAR2(500 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 14M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
|
|
|
|
|
|
|
|
|
|
| Re: Query using rownum in where cluase with millions of records [message #485666 is a reply to message #485608] |
Thu, 09 December 2010 06:20 |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
As you said I have tried to take that table in keep pool. but I didn't get any performance related success with this. I am sending the statistics of query before and after placing the table in keep pool.
Before keeping this table Statistics is as follows ==
Execution Plan
----------------------------------------------------------
Plan hash value: 2362346581
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 3549 | 136 (0)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TBL_SMS_TALKIES | 21 | 3549 | 136 (0)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1000)
Statistics
----------------------------------------------------------
123 recursive calls
0 db block gets
1776 consistent gets
4 physical reads
0 redo size
1844 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
6 rows processed
=================================================================
After taking table in keep pool using the below command
alter table tbl_sms_talkies storage (buffer_pool keep);
statistics is as follows
Execution Plan
----------------------------------------------------------
Plan hash value: 2362346581
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 3549 | 136 (0)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TBL_SMS_TALKIES | 21 | 3549 | 136 (0)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1761 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
So Please suggest what else we can do in this issue..
Regards
Pradeep Sharma
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
