OraFAQ Forum: Performance Tuning » Delete stmt taking so much time

Home » RDBMS Server » Performance Tuning » Delete stmt taking so much time (oracle)

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

Delete stmt taking so much time [message #514949]

Thu, 07 July 2011 08:03

shyamu544
Messages: 10
Registered: July 2011

Junior Member

Hi Team,

I'm deleteing a table which has 6118884 rows .

Following delete stmt I'm using it

Delete from dfn_dw_sales_fact dw WHERE dfn_source_system_id = 4;

It will deelete 108140 rows.

For excuting this delete stmt taking 30 mins.

Is there any possible way to tune this one ?
(Since this delete i'm using in one my stored procedure)

Please advise me.

FYI:

I had created index on dfn_dw_sales_fact.dfn_source_system_id (but no use) and there is no referncial constaraints also on this table.

Please advise me and appricate your early response.

Thanks,
Shyamu.A

Report message to a moderator

Re: Delete stmt taking so much time [message #514951 is a reply to message #514949]

Thu, 07 July 2011 08:06

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) Post an explain plan for the delete
2) Post DLL for table and all indexes
3) Trace the delete statement and see where the time is being spent.

Report message to a moderator

Re: Delete stmt taking so much time [message #514952 is a reply to message #514951]

Thu, 07 July 2011 08:09

shyamu544
Messages: 10
Registered: July 2011

Junior Member

How can i find the explain plan of this delete stmt ?
I'm new to this PL/SQL
and I'm using TOAD.

Please advise me

Report message to a moderator

Re: Delete stmt taking so much time [message #514953 is a reply to message #514952]

Thu, 07 July 2011 08:12

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I will wager 1 kibble on row by row processing.

Why are you deleting [so many] rows via a stored procedure?

Report message to a moderator

Re: Delete stmt taking so much time [message #514955 is a reply to message #514953]

Thu, 07 July 2011 08:14

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Explain plans should be got in sqlplus like this:

SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

Report message to a moderator

Re: Delete stmt taking so much time [message #514956 is a reply to message #514953]

Thu, 07 July 2011 08:15

shyamu544
Messages: 10
Registered: July 2011

Junior Member

There is an bussiness logic is there actually to delete like that( on each day)

Report message to a moderator

Re: Delete stmt taking so much time [message #514957 is a reply to message #514953]

Thu, 07 July 2011 08:15

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Roachcoach wrote on Thu, 07 July 2011 14:12

I will wager 1 kibble on row by row processing.

I read the question as single delete statement.

Report message to a moderator

Re: Delete stmt taking so much time [message #514958 is a reply to message #514956]

Thu, 07 July 2011 08:16

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

That's fine, but it doesn't explain (not obviously anyway) why it is a stored procedure.

@CM: I didn't think so but we hopefully find out Smile

Smile

[Updated on: Thu, 07 July 2011 08:17]

Report message to a moderator

Re: Delete stmt taking so much time [message #514959 is a reply to message #514958]

Thu, 07 July 2011 08:17

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If it's application code why wouldn't it be in a stored procedure?

Report message to a moderator

Re: Delete stmt taking so much time [message #514960 is a reply to message #514958]

Thu, 07 July 2011 08:17

shyamu544
Messages: 10
Registered: July 2011

Junior Member

Was there any stmt to delete bulkly ?

Report message to a moderator

Re: Delete stmt taking so much time [message #514961 is a reply to message #514960]

Thu, 07 July 2011 08:19

shyamu544
Messages: 10
Registered: July 2011

Junior Member

@cookiemonster: it's not in applciation code. it's pure data base project.

Report message to a moderator

Re: Delete stmt taking so much time [message #514962 is a reply to message #514960]

Thu, 07 July 2011 08:20

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Delete does bulk. Unless you call it in a loop so that it only deletes one row each time it's called.
Is that what you are doing?

Report message to a moderator

Re: Delete stmt taking so much time [message #514963 is a reply to message #514960]

Thu, 07 July 2011 08:20

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I'm cynical though Wink

Wink

It could just be a massive table and it's the undo generation killing it and/or no indexes, but as prior to edit: I'm cynical.

[Updated on: Thu, 07 July 2011 08:21]

Report message to a moderator

Re: Delete stmt taking so much time [message #514964 is a reply to message #514963]

Thu, 07 July 2011 08:22

shyamu544
Messages: 10
Registered: July 2011

Junior Member

I'm doing bulkly only.

Report message to a moderator

Re: Delete stmt taking so much time [message #514965 is a reply to message #514951]

Thu, 07 July 2011 08:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

cookiemonster wrote on Thu, 07 July 2011 14:06

1) Post an explain plan for the delete
2) Post DLL for table and all indexes
3) Trace the delete statement and see where the time is being spent.

Report message to a moderator

Re: Delete stmt taking so much time [message #514966 is a reply to message #514965]

Thu, 07 July 2011 08:24

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

In addition to that, if using a SP - post it too.

Report message to a moderator

Re: Delete stmt taking so much time [message #514967 is a reply to message #514963]

Thu, 07 July 2011 08:26

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Roachcoach wrote on Thu, 07 July 2011 14:20

It could just be a massive table and it's the undo generation killing it and/or no indexes

More likely to be too many indexes than no indexes every index has to be modified for each row deleted and the numbers involved suggest full table scan.

Report message to a moderator

Re: Delete stmt taking so much time [message #514968 is a reply to message #514967]

Thu, 07 July 2011 08:29

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Not quite a 2% delete, I've expected an index to be used if available but indeed it always varies.

Although most definitely fair comment about too many indexes and updates thereof.

[Updated on: Thu, 07 July 2011 08:30]

Report message to a moderator

Re: Delete stmt taking so much time [message #514969 is a reply to message #514968]

Thu, 07 July 2011 08:32

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I miss-estimated obviously. All the more reason to see the explain plan.

Report message to a moderator

Re: Delete stmt taking so much time [message #514972 is a reply to message #514967]

Thu, 07 July 2011 09:19

shyamu544
Messages: 10
Registered: July 2011

Junior Member

Thank you all for you I/p

Delete from dfn_dw_sales_fact dw WHERE dfn_source_system_id = 4; --- Table acess.

And

DELETE /*+RULE*/ FROM dfn_dw_sales_fact WHERE dfn_source_system_id = 4 -- Indexed scan.

Probelm solved thanks for inputs.

Report message to a moderator

Re: Delete stmt taking so much time [message #514973 is a reply to message #514972]

Thu, 07 July 2011 09:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do as below so we can know complete Oracle version & OS name.

Post via COPY & PASTE complete results of
SELECT * from v$version;

Report message to a moderator

Re: Delete stmt taking so much time [message #514974 is a reply to message #514972]

Thu, 07 July 2011 09:23

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You solved it by using a RULE hint?
What version of oracle are you using?

Report message to a moderator

Re: Delete stmt taking so much time [message #514975 is a reply to message #514974]

Thu, 07 July 2011 09:24

shyamu544
Messages: 10
Registered: July 2011

Junior Member

I'm using Oracle 8i

Report message to a moderator

Re: Delete stmt taking so much time [message #514976 is a reply to message #514975]

Thu, 07 July 2011 09:28

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I bet if you analyze the table it'll start using the index without the hint.
You should really upgrade to 10g or 11g since:
1) They are actually still supported by oracle, unlike 8i which has been out of support for years.
2) The CBO works better.
3) Oracle automatically gathers stats for you.

And next time you post a question tell us what version you are using at the start - it does make a difference.

Report message to a moderator

Re: Delete stmt taking so much time [message #514977 is a reply to message #514975]

Thu, 07 July 2011 09:29

shyamu544
Messages: 10
Registered: July 2011

Junior Member

CM: Can you one more favout to me...

I wann to test Some table data which is Prodcution has 6100000 rows.

I need to create one table in test env. with data in PROD ENv.

But If i'm using below querry it's taking so much time to insert.

Create table abcd as select * from abcd@dblink;

Is there any othere alternative ?

Report message to a moderator

Re: Delete stmt taking so much time [message #514979 is a reply to message #514977]

Thu, 07 July 2011 09:31

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Export/import.
Might well be faster than pulling the data over a DB link.

Report message to a moderator

Re: Delete stmt taking so much time [message #514980 is a reply to message #514977]

Thu, 07 July 2011 09:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Delete stmt taking so much time [message #514981 is a reply to message #514980]

Thu, 07 July 2011 09:33

shyamu544
Messages: 10
Registered: July 2011

Junior Member

CM: Can you tell me the process export and import into test env. ?

Report message to a moderator

Re: Delete stmt taking so much time [message #514982 is a reply to message #514981]

Thu, 07 July 2011 09:40

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Export and import are actual oracle utilities described in the utilities guide

Report message to a moderator

Previous Topic:	Become DB import too slow after server reboot
Next Topic:	query optimization

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 22:47:47 CDT 2024