Home » RDBMS Server » Performance Tuning » problem in insert
problem in insert [message #267279] Wed, 12 September 2007 23:20 Go to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
Hi all,

I have problem in insert the records from one table to another.
one table have 6 lacs records and another table i m using range partition and i want to insert the records to partition table.

i m using this query

insert into t_receiptd2 ( select * from t_receiptd);

more than half hour but this query is not yet completed

what to do? please help me

thanks and regards
Anamika.

[Updated on: Wed, 12 September 2007 23:22]

Report message to a moderator

Re: problem in insert [message #267282 is a reply to message #267279] Wed, 12 September 2007 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>what to do?
Wait or abort.

>please help me
How?


SQL> SET TIME ON
SQL>select count(*) from t_receiptd
returns what value & how quickly [use CUT & PASTE plus <code tags>]
Re: problem in insert [message #267286 is a reply to message #267279] Wed, 12 September 2007 23:54 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
more than half hour but this query is not yet completed

Also check v$session_longops view for more details.



Regards
Mohammed Taj
Re: problem in insert [message #267663 is a reply to message #267279] Fri, 14 September 2007 02:46 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member

hi,

13:10:34 SQL> select count(*) from t_receiptd;

COUNT(*)
----------
593134

Elapsed: 00:00:03.21



12:56:27 SQL> insert into t_receiptd2 (select * from t_receiptd);
insert into t_receiptd2 (select * from t_receiptd)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Elapsed: 00:09:27.51



not inserting
Re: problem in insert [message #267664 is a reply to message #267279] Fri, 14 September 2007 02:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What is the contents of the trace file?
Re: problem in insert [message #267665 is a reply to message #267279] Fri, 14 September 2007 02:51 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
in trace contents what i see??

[Updated on: Fri, 14 September 2007 03:01]

Report message to a moderator

Re: problem in insert [message #267671 is a reply to message #267279] Fri, 14 September 2007 03:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>in trace contents what i see??
I don't know. You have problem. Without clues to me & others, your problem remains yours & yours alone.
Re: problem in insert [message #267672 is a reply to message #267279] Fri, 14 September 2007 03:11 Go to previous messageGo to next message
abhilash8@gmail.com
Messages: 5
Registered: September 2006
Junior Member
Set
UNDO_RETENTION to 0
and then check....

regards
abhilash
Re: problem in insert [message #267675 is a reply to message #267672] Fri, 14 September 2007 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why?

Regards
Michel
Re: problem in insert [message #267684 is a reply to message #267675] Fri, 14 September 2007 04:31 Go to previous messageGo to next message
abhilash8@gmail.com
Messages: 5
Registered: September 2006
Junior Member
UNDO_RETENTION specifies for how many seconds undo information is kept. The default is 900 seconds and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. By example this parameter specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries in addition to supporting Oracle flashback query.This could be the reason for your slow performance with multiple inserts.
Revert it back to normal after your insert operation.

regards
abhilash

Re: problem in insert [message #267693 is a reply to message #267684] Fri, 14 September 2007 05:01 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This could be the reason for your slow performance with multiple inserts.

Why?

Regards
Michel
Previous Topic: AWR report
Next Topic: RMAN causing CPU boottleneck
Goto Forum:
  


Current Time: Sat Dec 10 20:41:17 CST 2016

Total time taken to generate the page: 0.06885 seconds