Home » RDBMS Server » Performance Tuning » DML suddenly taking a long time
DML suddenly taking a long time [message #65896] Tue, 25 January 2005 03:09 Go to next message
Shikha
Messages: 36
Registered: January 2002
Member
Hi,
This is in conjunction with earlier query I had put as why the DMLs are taking such a long time.
Here is an illustration of my problem:

What I am trying to do is, trying to delete records from the table MATERIALS. (and putting into a dump table)
There are, in all, about 3.9 million records. No partitions.

I want to delete about 1.8 million records which are ‘non-active’(i.e., ACTIVE=’N’)

Commit is being executed at each stage, I am doing this manually, Finding a good MATNR Range. There are two indexes on the two columns MATNR and ACTIVE, both normal indexes.

here are the sqls I proceeded with and check the timings it has taken.

INSERT /*+ APPEND */ INTO M_NON_ACTIVE_DUMP
SELECT * FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '103' AND '105';
-- 22145 Records inserted in 1.1 seconds

DELETE FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '103' AND '105';
-- 22145 rows deleted in 4.1 seconds
------------------------------------------------------------

INSERT /*+ APPEND */ INTO M_NON_ACTIVE_DUMP
SELECT * FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '105' AND '140';
-- 17099 Records inserted in 2.6 seconds

DELETE FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '105' AND '140';
-- 17099 rows deleted in 5.2 seconds
------------------------------------------------------------

INSERT /*+ APPEND */ INTO M_NON_ACTIVE_DUMP
SELECT * FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '140' AND '153';
-- 17996 Records inserted in 1.5 seconds

DELETE FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '140' AND '153';
-- 17996 rows deleted in 4.2 seconds
------------------------------------------------------------

INSERT /*+ APPEND */ INTO M_NON_ACTIVE_DUMP
SELECT * FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '153' AND '158';
-- 26183 Records inserted in 1.1 seconds

DELETE FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '153' AND '158';
-- ?????
(at this juncture, the delete statement simply hung and did not delete
for about 2 hours. After which, I killed it. And the killing is still
going on.
And I am 100% sure, there is nothing wrong with the tuning of
the sql. (Anyone deviates?)

Was I too greedy to have trying to delete more than 25K records at
one time?? I don’t think it was too bad a situation.
Or was it that there was some ‘saturation’ in the DBWR? (if I am putting it
correctly?) And that further deletes were not advisable then on?

Also, what about so much time in cancelling? Why would it take so
much time to cancel, after pressing Ctrl C? I also have killed the
job using Alter system. Why it takes more than 4 hours and still
cancelling?

(There is no one else accessing the database)

I am badly in need of a DBA suggestion here. What are the parameter I should be checking? Where should I start?

TIA,
Shikha
Re: DML suddenly taking a long time [message #65897 is a reply to message #65896] Tue, 25 January 2005 05:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
after every delete if there is a massive change in the records, there is good possiblity that the statistics is out of date. Analyze the table/indexes just before the last delete and try again. If it is good, you can collected the statistics intermittently~.
and USE BIND VARIABLES!
Re: DML suddenly taking a long time [message #65900 is a reply to message #65897] Tue, 25 January 2005 22:20 Go to previous messageGo to next message
Shikha
Messages: 36
Registered: January 2002
Member
Thanks a lot for the reply.

How do I use bind variables in my operations? Take for instance:

DELETE FROM MATERIALS
WHERE ACTIVE = 'N'
AND MATNR BETWEEN '153' AND '158';

1. What would be the way I could use bind variables here?

2. I did not think about analyzing though. As the select statement is still doing great. As you see the same SELECT in the INSERT INTO statement completed in a matter of a second. But the DELETE with same WHERE clause did not.

Thanks a lot in advance,

Shikha
Re: DML suddenly taking a long time [message #65902 is a reply to message #65900] Wed, 26 January 2005 05:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
to use BIND variables please look here.
http://www.orafaq.com/articles/archives/000036.htm
other things...
1. You are not using a dictionary managed tablespaces.
RIGHT?
Else look into migrating to locally managed tablespaces.
2. The presence of indexes may slowdown your DML , but will help your select.
Select statment and DML statement are thus very different.
You try some other workarounds..like
creating a temp/staging table with CTAS with nologging option in another tablespace. DO YOUR bulk delete in staging table.
then truncate your MATERIALS table, and insert from STAGING TABLE.
By this, avoid the constraints...( and do all above with nologging options)
3. Look into undo / RBS usage. Are these sized properly?
4. above all, first look into the execution plan and trace files~.
Re: DML suddenly taking a long time [message #65903 is a reply to message #65902] Wed, 26 January 2005 08:54 Go to previous messageGo to next message
Shikha
Messages: 36
Registered: January 2002
Member
Hi,
I just heard from the support team saying that there was "archiving is re-enabled and WE_ORA database was restarted"... and that "This problem should not occur again". I do not know what this means. I had rather a feeling that this problem might just have occured because of this enabling.

That apart, there are a cool piece of information I never tried before.

Surely bind variable usage is one thing I will induce in my coding now onwards in similar cases.

Also look forward to using CTAS.

I am not sure about the RBS thing. How to find whether it is proper or not.

Thanks a ton,

Shikha
Re: DML suddenly taking a long time [message #65905 is a reply to message #65903] Wed, 26 January 2005 22:11 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just a note in addition to Mahesh's replies :
If you only code PL/SQL and don't use dynamic SQL, you don't have to worry about bind-variables. PL/SQL will take care of that for you.

hth
Previous Topic: Latch waits are 39% of non-idle wait time
Next Topic: Hidden Parameters .
Goto Forum:
  


Current Time: Sat Sep 19 02:00:12 CDT 2020