| DML suddenly taking a long time [message #65896] |
Tue, 25 January 2005 03:09  |
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 #65900 is a reply to message #65897] |
Tue, 25 January 2005 22:20  |
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 #65903 is a reply to message #65902] |
Wed, 26 January 2005 08:54 |
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 |
Frank
Messages: 7901
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
