| Inserting Data For a long time?? [message #342228] |
Thu, 21 August 2008 13:40  |
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
|
Senior Member |
|
|
Hi All,
I have written a bulk insert procedure to fetch the data to a table say Temp_X,I have created the procedure and it says no error but the procedure is executing for a long time still it didn't displayed procedure executed successfully...Below is my procedure..
--------------------
CREATE OR REPLACE PROCEDURE TEST_PROC1(P_ARRAY_SIZE IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF TEMP_X%ROWTYPE;
L_DATA ARRAY;
CURSOR C IS
SELECT column1,column2
from x ;
BEGIN
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_X';
END;
BEGIN
OPEN C;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT 10000;
FORALL i IN 1..l_data.COUNT
INSERT INTO TEMP_X VALUES l_data(i);
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
COMMIT;
END;
END;
Thanks & Regards,
Hammer
|
|
|
|
|
|
|
|
| Re: Inserting Data For a long time?? [message #342234 is a reply to message #342228] |
Thu, 21 August 2008 14:29  |
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
|
Senior Member |
|
|
Hi Thomas,
The Records which I am trying to insert is not huge amount it is about less than 3 million.Whether it will take a day? to execute this procedure.Can you kindly let me know where i am missing.
Thanks & Regards,
Hammer.
|
|
|
|
| Re: Inserting Data For a long time?? [message #342246 is a reply to message #342234] |
Thu, 21 August 2008 15:14 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
You will have to investigate what the database is doing.
- Check for locks.
- Check what the session is doing.
- Check current wait events.
Since you continue to not follow the guidelines and don't post the necessary information for a better answer you seem to be on your own with that.
|
|
|
|
| Re: Inserting Data For a long time?? [message #342255 is a reply to message #342228] |
Thu, 21 August 2008 16:03 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Why use a loop, it is the slowest methoid.
CREATE OR REPLACE PROCEDURE TEST_PROC1(P_ARRAY_SIZE IN PLS_INTEGER DEFAULT 100)
IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_X';
INSERT INTO TEMP_X
SELECT column1,column2
from x ;
END;
|
|
|
|
|
|
|
|
| Re: Inserting Data For a long time?? [message #346987 is a reply to message #346977] |
Wed, 10 September 2008 05:50 |
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
|
Senior Member |
|
|
Hi Michel,
Thanks for your immediate response.But the issue which i am facing is almost a week which is not able to fix and the only problem is the data are not inserting.Is that something we need to check memory.I have fine tuned the query with bulk insert also.The version which i am using is as below.Need your kind advice.But in development it is working fine we are using enterprise edition.
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Thanks & Regards
Hammer.
[Updated on: Wed, 10 September 2008 05:52] Report message to a moderator |
|
|
|
|
|
| Re: Inserting Data For a long time?? [message #347015 is a reply to message #342246] |
Wed, 10 September 2008 07:41 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
| ThomasG wrote on Thu, 21 August 2008 22:14 | You will have to investigate what the database is doing.
- Check for locks.
- Check what the session is doing.
- Check current wait events.
Since you continue to not follow the guidelines and don't post the necessary information for a better answer you seem to be on your own with that.
|
That statement hasn't change since last week.
|
|
|
|
|
|
|
|
|
|
| Re: Inserting Data For a long time?? [message #347207 is a reply to message #347158] |
Thu, 11 September 2008 01:15 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
Hammer, I will throw out some suggestions from my experience with that scenario. but for the level of detail provided
this becomes a try-it-out test as there is not enough information to provide a definitive reason for these suggestions
except to determine if there is merit in them from the outcome:
Try the insert as a CREATE TABLE .. as SELECT statement in the execute_immediate.
Otherwise:
Use the /*+ append */ hint with the Insert statement.
and make sure there are no indexes on the insert table.
Have # of commits = 1/10th the count of records to insert ( if 3 million, then every 300,000).
Test:
Find out where you are in the insert to know how many records a second you are processing:
create a function fn_x that accepts and returns a varchar field. define it as an autonomous_transaction.
Have the function return whatever value is passed in,
wrap it around a key field in the VALUE section of the insert. In the function you can write the key to a temp log table on the side with the key and current time (and commit each time).
You can create a couple sequences to keep track of record count in fn_x and the number of inserts to the log (to shut it off after you have collected enough records to check the insert rate).
If staying with the Bulk Collect and Insert Loop, put
in displays after the insert and after the FETCH to see how much time is spent on each part of the operation.
If it's the select, make sure with an explain plan peruse, you aren't using any nested loop index lookups on joined tables.
use the USE_HASH optimizer hint for all the joins.
Regards,
Harry
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
