| Delete Query taking long time [message #64980] |
Mon, 22 March 2004 20:46  |
Srihari
Messages: 22
Registered: October 2000
|
Junior Member |
|
|
Hello Friends!!!
Delete query is taking too long about 2 minutes.
delete from PTest
where TRGT_HOST > ' ' and PORT_NO > ' ' and SITE_NAME > ' '
and INFO > ' ' and MSMT_HOST > ' ' and MSMT_TIME < to_date('06/02/2004 12:23:53','dd/mm/yyyy hh24:mi');
Table has a composite primary key and iam forcing the delete query to use the index that too columns used in the order as is defined in the primary key.
Still the query takes 2 minutes.Table Size is 90 lacs and no of rows deleted is 1.3 lacs.
Any ways to fasten the process?
Statistics
----------------------------------------------------------
39789 recursive calls
162532 db block gets
17745 consistent gets
10476 physical reads
55729464 redo size
850 bytes sent via SQL*Net to client
715 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
1 sorts (disk)
138147 rows processed
SQL> show sga
Total System Global Area 25998604 bytes
Fixed Size 70924 bytes
Variable Size 8998912 bytes
Database Buffers 16850944 bytes
Redo Buffers 77824 bytes
Please help!!!
Thanks and regards
Srihari
|
|
|
|
| Re: Delete Query taking long time [message #64989 is a reply to message #64980] |
Wed, 24 March 2004 11:35  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Could you post the execution plan also ? Have you analyzed the table ?
Post the execution plan/statistics without the index hint. Also select * from user_ind_columns where index_name='your index name'. Also whats the distinct_keys,clustering_factor,num_rows from user_indexes
why "where TRGT_HOST > ' ' and PORT_NO > ' ' and SITE_NAME > ' ' " ? did you mean is not NULL
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
