Home » RDBMS Server » Performance Tuning » Delete Select
Delete Select [message #231233] Mon, 16 April 2007 01:32 Go to next message
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
Re: Delete Select [message #231238 is a reply to message #231233] Mon, 16 April 2007 01:40 Go to previous messageGo to next message
martijn
Messages: 283
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?
Re: Delete Select [message #231240 is a reply to message #231233] Mon, 16 April 2007 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Delete Select [message #231251 is a reply to message #231238] Mon, 16 April 2007 02:24 Go to previous messageGo to next message
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

Re: Delete Select [message #231254 is a reply to message #231251] Mon, 16 April 2007 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Delete Select [message #231255 is a reply to message #231233] Mon, 16 April 2007 02:35 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Could be HWM

Ross Leishman
Re: Delete Select [message #231274 is a reply to message #231255] Mon, 16 April 2007 03:16 Go to previous messageGo to next message
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 

********************************************************************************
Re: Delete Select [message #231276 is a reply to message #231274] Mon, 16 April 2007 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Delete Select [message #231278 is a reply to message #231276] Mon, 16 April 2007 03:54 Go to previous messageGo to next message
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
Re: Delete Select [message #231284 is a reply to message #231278] Mon, 16 April 2007 04:42 Go to previous messageGo to next message
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
Re: Delete Select [message #231289 is a reply to message #231284] Mon, 16 April 2007 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Delete Select [message #231294 is a reply to message #231289] Mon, 16 April 2007 05:23 Go to previous messageGo to next message
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
********************************************************************************
Re: Delete Select [message #231300 is a reply to message #231294] Mon, 16 April 2007 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Delete Select [message #231304 is a reply to message #231233] Mon, 16 April 2007 06:22 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Do you have any foreign keys pointing to that table?
Any triggers?

Michael
Re: Delete Select [message #231327 is a reply to message #231304] Mon, 16 April 2007 09:54 Go to previous messageGo to next message
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
Re: Delete Select [message #231333 is a reply to message #231327] Mon, 16 April 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What relation this create table has with the question? Question

Regards
Michel
Re: Delete Select [message #231337 is a reply to message #231333] Mon, 16 April 2007 10:31 Go to previous messageGo to next message
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.
icon12.gif  Re: Delete Select [message #231512 is a reply to message #231337] Tue, 17 April 2007 06:32 Go to previous messageGo to next message
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.
Re: Delete Select [message #231542 is a reply to message #231512] Tue, 17 April 2007 07:52 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Delete Select [message #231894 is a reply to message #231233] Wed, 18 April 2007 14:51 Go to previous messageGo to next message
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
Re: Delete Select [message #233433 is a reply to message #231233] Thu, 26 April 2007 02:31 Go to previous messageGo to next message
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.
Re: Delete Select [message #233468 is a reply to message #231894] Thu, 26 April 2007 04:21 Go to previous message
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:
  


Current Time: Sat Dec 03 14:21:48 CST 2016

Total time taken to generate the page: 0.05462 seconds