OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Delete Select

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete Select [message #231233]

Mon, 16 April 2007 01:32

fcatak
Messages: 16
Registered: March 2007
Location: turkey

Junior Member

Hi I have a table which has ~400.000 rows. When I try to delete this table with

delete from table_name

. It takes 10 minutes to delete. And also select without any where statement runs so slowly. There is no transaction on this table.

thanks your advice.

http://fcatak.spaces.msn.com

Report message to a moderator

Re: Delete Select [message #231238 is a reply to message #231233]

Mon, 16 April 2007 01:40

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

(maybe you can give us some more information about version etc.)

Are your statistics up-to-date?

What does the explainplan show?

Report message to a moderator

Re: Delete Select [message #231240 is a reply to message #231233]

Mon, 16 April 2007 01:43

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How many blocks do you have?
Is there any error message like "unable to allocate new log" in alert.log?
Trace your session to see on which event it waits.

Regards
Michel

Report message to a moderator

Re: Delete Select [message #231251 is a reply to message #231238]

Mon, 16 April 2007 02:24

fcatak
Messages: 16
Registered: March 2007
Location: turkey

Junior Member

ORACLE version : V9.2.0.1.0
Statistics up to date

my alert log writes something like that when ı run the delete statement

Mon Apr 16 09:13:16 2007
Thread 1 advanced to log sequence 6352
  Current log# 6 seq# 6352 mem# 0: C:\IFSSTART\REDO23.LOG
Mon Apr 16 09:13:33 2007
Thread 1 advanced to log sequence 6353
  Current log# 7 seq# 6353 mem# 0: C:\IFSSTART\REDO31.LOG
Mon Apr 16 09:13:58 2007
Thread 1 advanced to log sequence 6354
  Current log# 8 seq# 6354 mem# 0: C:\IFSSTART\REDO32.LOG
Mon Apr 16 09:14:24 2007
Thread 1 advanced to log sequence 6355
  Current log# 9 seq# 6355 mem# 0: C:\IFSSTART\REDO33.LOG
Mon Apr 16 09:14:50 2007
Thread 1 advanced to log sequence 6356
  Current log# 1 seq# 6356 mem# 0: C:\IFSSTART\REDO11.LOG
Mon Apr 16 09:15:13 2007
Thread 1 advanced to log sequence 6357
  Current log# 2 seq# 6357 mem# 0: C:\IFSSTART\REDO12.LOG
Mon Apr 16 09:15:33 2007
Thread 1 advanced to log sequence 6358
  Current log# 3 seq# 6358 mem# 0: C:\IFSSTART\REDO13.LOG
Mon Apr 16 09:15:51 2007

Report message to a moderator

Re: Delete Select [message #231254 is a reply to message #231251]

Mon, 16 April 2007 02:33

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Blocks?
Trace your query, easy, efficient:

alter session set sql_trace=true;
delete ...
alter session set sql_trace=false;
exit;
goto user_dump_dest directory and tkprof the trace file.

Regards
Michel

Report message to a moderator

Re: Delete Select [message #231255 is a reply to message #231233]

Mon, 16 April 2007 02:35

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Could be HWM

Ross Leishman

Report message to a moderator

Re: Delete Select [message #231274 is a reply to message #231255]

Mon, 16 April 2007 03:16

fcatak
Messages: 16
Registered: March 2007
Location: turkey

Junior Member

********************************************************************************

delete from favori_trype_all_voucher_qry


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1     18.78    1321.33      82503       8143    2486852      342717
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     18.78    1321.34      82503       8144    2486852      342717

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  
 342717   TABLE ACCESS FULL FAVORI_TRYPE_ALL_VOUCHER_QRY 

********************************************************************************

Report message to a moderator

Re: Delete Select [message #231276 is a reply to message #231274]

Mon, 16 April 2007 03:29

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

tkprof with waits=yes.
If you don't have wait events in the trace then use

alter session set events '10046 trace name context forever, level 8';

instead of "set sql_trace=true" and

alter session set events '10046 trace name context off';

to stop the trace.
Start a new session to make a new test.

And for the third time: HOW MANY BLOCKS DO YOU HAVE IN YOUR TABLE? (post user_tables.blocks, user_tables.empty_blocks and user_segments.blocks)

Regards
Michel

Report message to a moderator

Re: Delete Select [message #231278 is a reply to message #231276]

Mon, 16 April 2007 03:54

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
And for the third time: HOW MANY BLOCKS DO YOU HAVE IN YOUR TABLE? (post user_tables.blocks, user_tables.empty_blocks and user_segments.blocks)

with up-to-date statistics.

exec dbms_stats.gather_table_stats('OWNER','TABLE_NAME');

regards
Taj

Report message to a moderator

Re: Delete Select [message #231284 is a reply to message #231278]

Mon, 16 April 2007 04:42

fcatak
Messages: 16
Registered: March 2007
Location: turkey

Junior Member

BLOCKS
   	TABLE_NAME			BLOCKS	EMPTY_BLOCKS
	FAVORI_TRYPE_ALL_VOUCHER_QRY	8191	1
SEGMENTS

   	SEGMENT_NAME			SEGMENT_TYPE	BYTES		BLOCKS
	FAVORI_TRYPE_ALL_VOUCHER_QRY	TABLE		67108864	8192
	FAVORI_TRYPE_ALL_VOUCHER_IX2	INDEX		13631488	1664
	FAVORI_TRYPE_ALL_VOUCHER_IX1	INDEX		31457280	3840

Report message to a moderator

Re: Delete Select [message #231289 is a reply to message #231284]

Mon, 16 April 2007 05:09

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

OK, that's consistent.
Now trace your process with wait events.

Just a remark: if you want to delete all rows, why not truncate the table?

Regards
Michel

Report message to a moderator

Re: Delete Select [message #231294 is a reply to message #231289]

Mon, 16 April 2007 05:23

fcatak
Messages: 16
Registered: March 2007
Location: turkey

Junior Member

********************************************************************************

DELETE FROM FAVORI_TRYPE_ALL_VOUCHER_QRY


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2     24.64     905.48      93616      24247    2484858      342867
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     24.64     905.48      93616      24247    2484858      342867

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  
      0   TABLE ACCESS FULL FAVORI_TRYPE_ALL_VOUCHER_QRY 


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     93578        1.77       1328.39
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       10.56         10.57
  log file switch completion                     65        1.02         16.22
  latch free                                      2        0.00          0.00
********************************************************************************

Report message to a moderator

Re: Delete Select [message #231300 is a reply to message #231294]

Mon, 16 April 2007 05:42

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Now you know where you are waiting: you wait reading the files.
So have a look at this point.

Regards
Michel

Report message to a moderator

Re: Delete Select [message #231304 is a reply to message #231233]

Mon, 16 April 2007 06:22

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Do you have any foreign keys pointing to that table?
Any triggers?

Michael

Report message to a moderator

Re: Delete Select [message #231327 is a reply to message #231304]

Mon, 16 April 2007 09:54

fcatak
Messages: 16
Registered: March 2007
Location: turkey

Junior Member

that's a simple table created from a view

Create Table FAVORI_TRYPE_ALL_VOUCHER_QRY as
select * from TRYPE_ALL_VOUCHER_QRY;

there is no foreign key and triggers

Report message to a moderator

Re: Delete Select [message #231333 is a reply to message #231327]

Mon, 16 April 2007 10:28

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What relation this create table has with the question?

Regards
Michel

Report message to a moderator

Re: Delete Select [message #231337 is a reply to message #231333]

Mon, 16 April 2007 10:31

fcatak
Messages: 16
Registered: March 2007
Location: turkey

Junior Member

It is the table I try to speed up. It is created from a view and it has no trigger etc.

Report message to a moderator

Re: Delete Select [message #231512 is a reply to message #231337]

Tue, 17 April 2007 06:32

kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA

Junior Member

Hi,
Its bad idea to delete all the rows(~400,000) instead of truncating. Truncate resets the HWM. Delete doesnt do that. Every query scans upto the HWM. Truncate and Generate statistics using DBMS_STATS.

Report message to a moderator

Re: Delete Select [message #231542 is a reply to message #231512]

Tue, 17 April 2007 07:52

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Does the table have indexes? When you delete the table rows, it also deletes the index rows. This could be causing the high values in CURRENT on the TK*Prof.

Ross Leishman

Report message to a moderator

Re: Delete Select [message #231894 is a reply to message #231233]

Wed, 18 April 2007 14:51

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

To me, a large number of db file sequential reads against a full table scan looks like a candidate for either chained / migrated rows or a rather small extent size (although, in that case, you'd tend to see "interleaved" db file scattered read / db file sequential read's).

It'd be interesting to see chain_cnt from dba_tables after it's been analysed to prove / disprove this. Also, what block size are you using?

Regards

Report message to a moderator

Re: Delete Select [message #233433 is a reply to message #231233]

Thu, 26 April 2007 02:31

colly_liu
Messages: 6
Registered: April 2007

Junior Member

you should use truncate table increase the speed,delete a large table is an inefficient way.

Report message to a moderator

Re: Delete Select [message #233468 is a reply to message #231894]

Thu, 26 April 2007 04:21

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 93578 1.77 1328.39
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 10.56 10.57
log file switch completion 65 1.02 16.22
latch free 2 0.00 0.00

Isn't it a situation where data are stored in chained or migrated blocks, as mchadder said.
After analyzing from user_tables.chain_cnt, pct_free, pct_used, and the block size, we can deside that.
If it's the problem, we can fix it by properly setting pct_free and block size...

[Updated on: Thu, 26 April 2007 04:23]

Report message to a moderator

Previous Topic:	pls!!!!!! need help
Next Topic:	Error while trying to compute Schema Statistics

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 00:15:33 CDT 2024