OraFAQ Forum: Performance Tuning » Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization

Home » RDBMS Server » Performance Tuning » Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization [message #314135]

Tue, 15 April 2008 19:35

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?

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

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

Attachment: withouthint.JPG
(Size: 27.84KB, Downloaded 1251 times)

Report message to a moderator

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

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Check your Wait Events.

Ross Leishman

Report message to a moderator

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

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 1224 times)

Report message to a moderator

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

rleishman
Messages: 3728
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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

rleishman
Messages: 3728
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

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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>

Report message to a moderator

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

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.

Report message to a moderator

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

ajexpert
Messages: 16
Registered: April 2008
Location: U.S.

Junior Member

Hi Ana,

Waiting for your "Metaphorical" reply.

Report message to a moderator

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

rleishman
Messages: 3728
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

Report message to a moderator

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

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?

Attachment: waitevents.txt
(Size: 18.10KB, Downloaded 2170 times)

Report message to a moderator

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

singh.neerajin
Messages: 26
Registered: April 2008
Location: noida

Junior Member

I think you can try out BULK INSERTION along with /*+ append /hints,,,,,

Report message to a moderator

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

ajexpert
Messages: 16
Registered: April 2008
Location: U.S.

Junior Member

I did the same, but this couldn,t help

Report message to a moderator

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

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

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.

Report message to a moderator

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

rleishman
Messages: 3728
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

Report message to a moderator

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

victoryhendry
Messages: 96
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

Report message to a moderator

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

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?

Report message to a moderator

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

kapardi
Messages: 19
Registered: May 2011
Location: Pune

Junior Member

nlnkapardi@gmail.com

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,

Report message to a moderator

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

Michel Cadot
Messages: 68637
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

Report message to a moderator

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

LNossov
Messages: 318
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

Cool

*/ INTO tableA (col1, col2, col3)
SELECT /*+ parallel(tableB Cool

Cool

full(tableB) */ col1, col2, col3 from tableB;

Regards
Leonid

Report message to a moderator

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

LNossov
Messages: 318
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

Report message to a moderator

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

Michel Cadot
Messages: 68637
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 14:20:49 CDT 2024