Home » RDBMS Server » Performance Tuning » Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization (Oracle 10g)
icon4.gif  Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314135] Tue, 15 April 2008 19:35 Go to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
We are performing Oracle Migration on 10g

Some of the tables contain a huge data e.g. 7 million records to be migrated.

The table structure of Source Database is different from the Target Database (Where we are doing the migration).
So we have made a use of temporary tables to collect the data from different tables using joins and populate the main table using INSERT INTO tableA SELECT * FROM tableZ

Now the problem statement.
Some of the queries take infinite time and data doesnt get inserted into the main tables. I have seen the explain plan and didnt find anything unusual. The same query works fine when we use date time stamp and restrict the data.

What parameters do we have to take care ? Is there any configuration needed on Oracle Server side ? If yes which parameters?
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314138 is a reply to message #314135] Tue, 15 April 2008 20:31 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>Some of the queries take infinite time and data doesnt get inserted into the main tables.

You decided that we don't need to see the SQL & yet you expect magic silver bullet solution(s).

SQL_TRACE will show you where the time is being spent.

>I have seen the explain plan and didnt find anything unusual.

You decided that you have all the answers & don't need a second opinion WRT the EXPLAIN_PLAN.

Please let us know when & where you find the missing golden fleece.
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314155 is a reply to message #314138] Tue, 15 April 2008 22:10 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
OK...I am uploding the files where I there was full table scan. I have used index hint and surprizingly the time it took was more then full table scan.

This file is with full table scan
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314157 is a reply to message #314138] Tue, 15 April 2008 22:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Check your Wait Events.

Ross Leishman
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314158 is a reply to message #314138] Tue, 15 April 2008 22:11 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
This one is using index hint but the time taken is more then full table scan.

I tell you that it contains huge number of records
  • Attachment: withhints.JPG
    (Size: 33.80KB, Downloaded 375 times)
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314159 is a reply to message #314155] Tue, 15 April 2008 22:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
ajexpert wrote on Wed, 16 April 2008 13:10
OK...I am uploding the files where I there was full table scan. I have used index hint and surprizingly the time it took was more then full table scan.

This file is with full table scan


Not surprising at all. Do you think it would be easier to read an entire Oracle Manual section-wise in order of the index entries, or just by starting at the start and reading to the end?

See http://www.orafaq.com/node/1403

Ross Leishman
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314160 is a reply to message #314157] Tue, 15 April 2008 22:14 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
How do I check Wait Events?

Also I found that there is something called Segment tuning.

Can you tell me if We need to increase server parameters?

I can give you the SGA info

NAME BYTES RESIZEABLE
-------------------------------- ---------- ----------
Fixed SGA Size 791660 No
Redo Buffers 1048576 No
Buffer Cache Size 528482304 Yes
Shared Pool Size 1048576000 Yes
Large Pool Size 83886080 Yes
Java Pool Size 209715200 Yes
Streams Pool Size 0 Yes
Granule Size 8388608 No
Maximum SGA Size 1879048192 No
Startup overhead in Shared Pool 33554432 No
Free SGA Memory Available 0
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314162 is a reply to message #314138] Tue, 15 April 2008 22:18 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
I have tried NOLOGGING and /*+ APPEND */ hints but couldn't find much performance improvement
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314164 is a reply to message #314160] Tue, 15 April 2008 22:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
ajexpert wrote on Wed, 16 April 2008 13:14
How do I check Wait Events?



SELECT  sid, event, total_waits, time_waited, average_wait, max_wait
FROM    v$session_event
WHERE   sid = &1
ORDER BY 1,4
/


Ross Leishman
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314165 is a reply to message #314135] Tue, 15 April 2008 22:27 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Ready, Fire, AIM!

You have on a blindfold while being completely in the dark and shooting wildly & hoping to get lucky.

Good Luck on your wild goose chase.
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314174 is a reply to message #314165] Tue, 15 April 2008 23:34 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
Hi anacedent,

I like your idioms.

BTW please let me know what extra information you need from my side.

I need to resolve this issue at the earliest.

Thanks,

Ajay M
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314176 is a reply to message #314135] Tue, 15 April 2008 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/

Which have you done & what were the specific results?

POST inputs & outputs with <code tags>

Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314180 is a reply to message #314176] Tue, 15 April 2008 23:52 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
Here it is
<code start>
INSERT /*+ APPEND */ INTO tableA (col1, col2, col3)
SELECT col1, col2, col3 from tableB
</code finish>

tableA - Main Table
tableB - Temporary Table

We are storing data into several temporary tables using joins and finally arriving to above statement.

Happy to provide further details.

icon6.gif  Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314185 is a reply to message #314176] Wed, 16 April 2008 00:12 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
Hi Ana,

Waiting for your "Metaphorical" reply.
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314254 is a reply to message #314185] Wed, 16 April 2008 03:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hey, those code tags look great. Was that a joke? It went over my head if it was.

Just to shake things up a bit, could you try them like it suggests in the Posting Guidelines - that way they'll preserve the formatting of your code.

Did you consider and reject the idea of capturing the wait events? If so, you might reconsider; asking the database is a great way to find out what it's doing; and finding out what it's doing is a great first step to make it do it faster.

Ross Leishman
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314410 is a reply to message #314254] Wed, 16 April 2008 11:03 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
OK...i did look at the wait events, but couldn't figure out much information...

Do you wanna look at the wait events?

Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314451 is a reply to message #314135] Wed, 16 April 2008 14:53 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

I think you can try out BULK INSERTION along with /*+ append /hints,,,,,
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314452 is a reply to message #314135] Wed, 16 April 2008 14:55 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
I did the same, but this couldn,t help
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314453 is a reply to message #314452] Wed, 16 April 2008 15:01 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

or you can use ........

create table TEMP1 as select * from main_temp..

after that you can rename column of temp1.. it will work faster..

instead of using insertion you directly create table on run time as query written above, after insertion you can rename column or add column or rename table////

[Updated on: Wed, 16 April 2008 15:03]

Report message to a moderator

Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314463 is a reply to message #314135] Wed, 16 April 2008 15:49 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ok,

 INSERT /*+ APPEND */ INTO tableA (col1, col2, col3)
SELECT col1, col2, col3 from tableB 


This SQL cannot be tuned any further.

The only other further SQL tuning you have is partitioned target and source table, and you would insert with parallel hints option and parallel dml enabled.

You are down to tuning the database only.

checking target is properly sized, initrans, pctfree etc on both source and target. Disabling indexes during load and rebuilding them afterwards. target table set to nologging.
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314489 is a reply to message #314463] Wed, 16 April 2008 21:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So you have posted the wait events for every active session.

Could you tell me WHICH session was the one that was currently running the "hanging" insert?

Ross Leishman
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #443819 is a reply to message #314135] Wed, 17 February 2010 07:33 Go to previous messageGo to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

Hi,

The better way is try to use Big tablespace. before performing the insertion, you need to enable the big tablespace, which is having more space and takes less time.

Thanks and Regards,

Vetrivel
Bangalore
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #443823 is a reply to message #443819] Wed, 17 February 2010 07:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) What is it about this problem that makes you think a BigTablespace will make a difference?

2) Why have you resurrected a 2 year old post?
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #510473 is a reply to message #443823] Mon, 06 June 2011 03:27 Go to previous messageGo to next message
kapardi
Messages: 19
Registered: May 2011
Location: Pune
Junior Member

Hello Everybody

Please can anyone please suggest me that how to manage the long running QUERIES like (Full Table Scan in UPDATE,INSERT,SELECT)..

There is an update statement where it is doing FULLTABLE SCAN of nearly 24Lakhs recods and it is running for 9Hrs how to tune this type of queries

Please update me

Thanks and Regards
Kapardi,
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #510489 is a reply to message #510473] Mon, 06 June 2011 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #514309 is a reply to message #510489] Sun, 03 July 2011 16:34 Go to previous messageGo to next message
LNossov
Messages: 310
Registered: July 2011
Location: Germany
Senior Member
Hello,

as I see, nobody suddested to use PDML for this INSERT. If you have enough CPU's, you can parallelize your sql. For ex.

INSERT /*+ APPEND parallel(tableA Cool */ INTO tableA (col1, col2, col3)
SELECT /*+ parallel(tableB Cool full(tableB) */ col1, col2, col3 from tableB;

Regards
Leonid
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #514310 is a reply to message #514309] Sun, 03 July 2011 16:36 Go to previous messageGo to next message
LNossov
Messages: 310
Registered: July 2011
Location: Germany
Senior Member
sorry, for bad format:

Hello,

as I see, nobody suddested to use PDML for this INSERT. If you have enough CPU's, you can parallelize your sql. For ex.

INSERT /*+ APPEND parallel(tableA 8) */ INTO tableA (col1, col2, col3)
SELECT /*+ parallel(tableB 8) full(tableB) */ col1, col2, col3 from tableB;

Regards
Leonid
Re: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #514317 is a reply to message #514310] Sun, 03 July 2011 23:27 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
sorry, for bad format:

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Regards
Michel
Previous Topic: How to measure the sql run time?
Next Topic: how to clear cache for testing tuned code
Goto Forum:
  


Current Time: Tue Dec 06 14:36:18 CST 2016

Total time taken to generate the page: 0.32855 seconds