Home » RDBMS Server » Performance Tuning » Delete Query taking long time
Delete Query taking long time [message #64980] Mon, 22 March 2004 20:46 Go to next message
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 Go to previous message
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
Previous Topic: log file sync
Next Topic: Different way of execute
Goto Forum:
  


Current Time: Thu Oct 01 03:34:37 CDT 2020