Home » SQL & PL/SQL » SQL & PL/SQL » Inserting Data For a long time?? (Oracle 10g)
Inserting Data For a long time?? [message #342228] Thu, 21 August 2008 13:40 Go to next message
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 #342230 is a reply to message #342228] Thu, 21 August 2008 13:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

There is not enough information for a real answer, but my bet is that there are either sessions holding on locks on the table, or the insert is taking as long as it takes because there IS so much data.
Re: Inserting Data For a long time?? [message #342231 is a reply to message #342228] Thu, 21 August 2008 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Insert into temp_x select ... from x"

And please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Inserting Data For a long time?? [message #342234 is a reply to message #342228] Thu, 21 August 2008 14:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Bill B
Messages: 1482
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 #346976 is a reply to message #342228] Wed, 10 September 2008 05:14 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear all,
I am trying to solve the issue slightly with more technical angle.I am having a table with more than one million records which i need to pump to a temp table.But the data is not inserting,which is taking more time.

[Updated on: Wed, 10 September 2008 05:16]

Report message to a moderator

Re: Inserting Data For a long time?? [message #346977 is a reply to message #346976] Wed, 10 September 2008 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why the answers that have been provided don't fit your requirements?

Regards
Michel
Re: Inserting Data For a long time?? [message #346987 is a reply to message #346977] Wed, 10 September 2008 05:50 Go to previous messageGo to next message
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 #347012 is a reply to message #346987] Wed, 10 September 2008 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still didn't answer the question:
Why INSERT SELECT does not fit your requirements?

Regards
Michel
Re: Inserting Data For a long time?? [message #347015 is a reply to message #342246] Wed, 10 September 2008 07:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #347158 is a reply to message #346976] Wed, 10 September 2008 22:11 Go to previous messageGo to next message
beginoracle
Messages: 4
Registered: September 2008
Junior Member

Hi ,It's taking so long may be its because the table you are trying to insert has not been analyzed recently. Ask your DBA to Analyze because there may be many changes on that table. Hope it may help. And make sure have access to that table.


Hope it helps.

Try it.

Oraclebegin
Re: Inserting Data For a long time?? [message #347190 is a reply to message #347158] Thu, 11 September 2008 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And where in the process would an analyze help speed up things?

[Updated on: Thu, 11 September 2008 00:49]

Report message to a moderator

Re: Inserting Data For a long time?? [message #347192 is a reply to message #347158] Thu, 11 September 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It's taking so long may be its because the table you are trying to insert has not been analyzed recently

This is not correct, there is no relation between insert (values) performances and statistics.

Regards
Michel
Re: Inserting Data For a long time?? [message #347207 is a reply to message #347158] Thu, 11 September 2008 01:15 Go to previous message
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



Previous Topic: ORA-01008 error for cursor
Next Topic: unique constraint error..
Goto Forum:
  


Current Time: Fri Dec 02 12:32:07 CST 2016

Total time taken to generate the page: 0.10936 seconds