Home » RDBMS Server » Performance Tuning » delete statement cause application hang
delete statement cause application hang [message #130627] Mon, 01 August 2005 11:21 Go to next message
liux99
Messages: 29
Registered: June 2005
Junior Member
i have an application which checks if the data already stored in the database, if it does, then delete. When this delete involves thousands records, the application hangs there. The autocommit is turned false and the change is committed after the whole job is done.

I kind of suspecting the setting of the redo log buffer. But i am not experienced in tuning this.

Any suggestion on what would be the cause? Thanks.
Re: delete statement cause application hang [message #130630 is a reply to message #130627] Mon, 01 August 2005 11:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is becuase you might have done this deletes many times, and HWM is never been reset!.
TO reset the HWM, you need to truncate or use

-- Be warned that indexes need to be moved seperately.
sql> alter table table_name move tablespace same_tablespace_asitis.

If you are using a where condition, make sure there are latest stats available for indexes/tables.
or
If you are about to delete a big chunk of data from table (say 70%), then it is easy to do with CTAS.

create table orignal_backup table nologging as select * from original table where ( keep the records you want).
Now only wanted records are in backup table,
truncate the original table.
insert from backuptable into original table
or rename backuptable to original table.
Re: delete statement cause application hang [message #130640 is a reply to message #130630] Mon, 01 August 2005 14:36 Go to previous messageGo to next message
liux99
Messages: 29
Registered: June 2005
Junior Member
Mahesh,

Thanks for the reply.
Can you elaborate a little more on:
1. Why HWM can cause a delete statement hang?
2. Why i need reset HWM?

My situation is: I need clean the existing data in
order to reload the data, e.g. i will have the same
amount of inserts or so following the delete.
Re: delete statement cause application hang [message #130643 is a reply to message #130640] Mon, 01 August 2005 15:28 Go to previous message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>1. Why HWM can cause a delete statement hang?
>>2. Why i need reset HWM?

It may not be hanging.
It is just slow.


As the data is added to table,table size grows and the extent are allocated more.
The highest occupied level is HWM ( Higwatermark).
When you delete data, this space / extents are not released and HWM is NOT reset to the deleted level.
say at one stage, you may have 100 extents.
you delete 90% the data.
But still oracle maintains 100 extent.
when you query this table, oracle will search all the 100 extents(upto the HWM), though most of them contains no data
( which is reason for high i/0)

When you truncate the table,
the HWM is reset.extents are released. ( 100 extents allocated are releaseed and the MINinum extents are maintained)
after loading right data again, you may have say 10 extents allocated.
now if you query , oracle will search only 10 extents.!
--
-- Load some records
--

scott@9i > @emp_loopinsert

PL/SQL procedure successfully completed.

scott@9i > get emp_loopinsert
  1   begin
  2   for mag in 1..15 loop
  3       insert into emp (select * from emp);
  4   end loop;
  5   commit;
  6*  end;
scott@9i >
scott@9i > select count(*) from emp;

  COUNT(*)
----------
    458752


--
-- index and gather stats
--
scott@9i > create index myindex on emp(empno);

Index created.

scott@9i > analyze table emp compute statistics;

Table analyzed.

scott@9i > analyze index myindex compute statistics;

Index analyzed.

--
-- lets check how many extents are allocated and the size_in_MB it took
--

scott@9i > get max_ext_reach
  1  select  segment_name "name",
  2          segment_type,
  3          max_extents "maximum allowed",
  4          extents "current"
  5  from    user_segments
  6* where   segment_name='EMP';
  7  .
scott@9i > get size
  1* Select segment_name,bytes /(1024*1024) from user_segments where segment_name='EMP';
scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         22

scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       22

--
--Enable trace
--
scott@9i > set autotrace traceonly exp stat
scott@9i > set timing on

--
-- Lets do the delete
--

scott@9i > delete from emp where deptno=10;

98304 rows deleted.

Elapsed: 00:00:26.91

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=135 Card=152917 Byte
          s=764585)

   1    0   DELETE OF 'EMP'
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=135 Card=152917 Bytes
          =764585)





Statistics
----------------------------------------------------------
        296  recursive calls
     299679  db block gets
       1536  consistent gets
       1456  physical reads
   47245832  redo size
       1019  bytes sent via SQL*Net to client
       1044  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      98304  rows processed


scott@9i > set autotrace off
scott@9i > set time off


--
-- check the size/extents again.
-- thoufh 98304 rows are deleted, no space/extents is released
--
scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       22

scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         22

--
-- So the table has less data now.
-- But if you query the table, you will search alll the 22 extents, even though 
-- some have no data in it.
--
-- NOw use CTAS
-- Truncate the table
-- check the size/extents
-- all space is released.

scott@9i > create table another_emp nologging as select * from emp where deptno !=20;

Table created.

scott@9i > set timing on
scott@9i > truncate table emp;

Table truncated.

Elapsed: 00:00:02.18
scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                        1

Elapsed: 00:00:00.07
scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645          1

Elapsed: 00:00:00.07


--
-- insert your data back.
--

  1* insert /*+ append */ into emp (select * from another_emp)
scott@9i > /

196608 rows created.

Elapsed: 00:00:10.62

scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       10

Elapsed: 00:00:00.07
scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         10

Elapsed: 00:00:00.07

---------------------------------------------------------------------------------------------------
--
-- Since Truncate deletes all the data, it may not be an option (if you cannot use CTAS).
-- so the least we can do is ( if there is a huge delete)
-- 1. if you are deleting all data , truncate /drop the table
-- 2. if you are deleting only selective data, use CTAS as shown above.
-- 3. If you delete regularly and you want reset hwm alter table move will also help
      you can see here again, after delete no space is released.
      so move the table to the same tablespace it is.
      this will release the space
      You also need to move the indexes seperatly
      after all done, make sure you gather the table/index stats again!.
----------------------------------------------------------------------------------------------------
scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       10

scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         10

scott@9i > delete from emp;

196608 rows deleted.

scott@9i > commit;

Commit complete.

scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                       10

scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645         10

scott@9i > alter table emp move tablespace users;

Table altered.

scott@9i > @size

SEGMENT_NA BYTES/(1024*1024)
---------- -----------------
EMP                        1

scott@9i > @max_ext_reach

name       SEGMENT_TYPE       maximum allowed    current
---------- ------------------ --------------- ----------
EMP        TABLE                   2147483645          1


Previous Topic: data load-index rebuild or index drop
Next Topic: undo management
Goto Forum:
  


Current Time: Tue Dec 06 22:31:16 CST 2022