Delete process- Background Reorganize - optimization techniques

skarangi
SQL-Fighter

Hi,

 In the nightly batch runs with multiple jobs running and perform different operations on database, when a delete process runs on a massively huge table and that reaches a 25% limit causing reorganize, I see performance impacts to current operation and other parallel jobs. 

1) Have you experienced this situation?

 

2) How did you overcome it? 

 3) Also from EXA_DBA_AUDIT_SQL or any other table, how do we identify that this current delete statement has triggered Reorganize?

 

Thanks in advance.

 

regards,
Sreekanth

 

1 REPLY 1

mwellbro
Xpert

Hi @skarangi ,

regarding 3) : I don´t think there is an apparent way to check if a given delete was an actual delete or just a "below threshold marking run". As a workaround you could check the exa_dba_tables for the DELETE_PERCENTAGE column, if deletes are frequent and the value is low that could be an indicator ( or if you export / copy that table regularly you would have an historically exact reference for the DELETE_PERCENTAGE´s ).
When going for exa_dba_audit_sql you could probably take the current table_row_count from exa_dba_tables and calculate the row_count there to add/substract for INSERT/DELETE to give you the respective numbers - not ideal, but might work.

Personally haven´t had to content with issues on that front but I can imagine that a DELETE on a large enough table ( and with a few indexes for good measure ) might be noticeable as you described.

Cheers,
Malte