OraFAQ Forum: Performance Tuning » BULK INSERT PERFORMANCE ON PROD

Home » RDBMS Server » Performance Tuning » BULK INSERT PERFORMANCE ON PROD (Oracle 12c, RHEL 6)

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

BULK INSERT PERFORMANCE ON PROD [message #651290]

Sat, 14 May 2016 14:48

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

We are performing a bulk insert operation (LOAD TABLE CONVENTIONAL) with 18k rows. A COMMIT is provided at the end. The operation takes just 1 minute in the non-prod environment. However, this take 6 minutes in a PRODUCTION environment.

The DB configuration is similar for both PROD and NON-PROD. The only difference is PROD has archive log mode enabled. The non-prod does not.

This has been raised as concern as there is a difference seen in the performance between the environment. We also tried using the NOLOGGING option in PROD. But the behavior is the same.

Could archive log mode cause this much delay? Is this the expected behavior? Is it possible to reduce the time for this operation? I understand that archive logging could incur performance overhead. But we would like to have reasonable time which could be lesser than 6 minutes.

Report message to a moderator

Re: BULK INSERT PERFORMANCE ON PROD [message #651292 is a reply to message #651290]

Sat, 14 May 2016 15:16

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Could archive log mode cause this much delay? Is this the expected behavior? Is it possible to reduce the time for this operation? I understand that archive logging could incur performance overhead. But we would like to have reasonable time which could be lesser than 6 minutes.

May be. May be. Maybe.
You have to determine where the time is spent.
So trace the session.

You also have to post the actual command you execute.

Report message to a moderator

Re: BULK INSERT PERFORMANCE ON PROD [message #651319 is a reply to message #651292]

Sun, 15 May 2016 16:09

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Please find the code.

insert into AJANTA

         (

         EXXTRAFFUR,MYLAXPANIT,SETLFUR,

         TRANSDOERXCOPANT,THROPAGHPPATFUREED,

         ITEMFUREED,AREXHXNMA,PAVNXCLAR,

         SEARCH,SEARCHXPAOM,

         FULXSTOWCE,STATPASFUR,REVERTXSTOWCE,

         REVERTXSTOWCEXCLAR,TRANSDOERXDTTM,SPACCXPROCESSFUR

         )

         values('1','1','1',:1 , :2 ,:3 ,:4 ,'F',:5

         ,'STATE',0,1,'MAR','2',sysdate,SEQXPAVXSPACCXDTLFUR.NEXTVAL)

Report message to a moderator

Re: BULK INSERT PERFORMANCE ON PROD [message #651320 is a reply to message #651319]

Sun, 15 May 2016 17:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I use URL below to format the SQL
http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

INSERT INTO ajanta 
            (exxtraffur, 
             mylaxpanit, 
             setlfur, 
             transdoerxcopant, 
             thropaghppatfureed, 
             itemfureed, 
             arexhxnma, 
             pavnxclar, 
             search, 
             searchxpaom, 
             fulxstowce, 
             statpasfur, 
             revertxstowce, 
             revertxstowcexclar, 
             transdoerxdttm, 
             spaccxprocessfur) 
VALUES     ('1', 
            '1', 
            '1', 
            :1, 
            :2, 
            :3, 
            :4, 
            'F', 
            :5, 
            'STATE', 
            0, 
            1, 
            'MAR', 
            '2', 
            SYSDATE, 
            seqxpavxspaccxdtlfur.NEXTVAL)

single row INSERT should "quick"

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

[Updated on: Sun, 15 May 2016 17:05]

Report message to a moderator

Re: BULK INSERT PERFORMANCE ON PROD [message #651337 is a reply to message #651319]

Mon, 16 May 2016 00:28

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

You have to determine where the time is spent.
So trace the session.

Report message to a moderator

Re: BULK INSERT PERFORMANCE ON PROD [message #651498 is a reply to message #651337]

Thu, 19 May 2016 03:06

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

That doesn't look all that bulk. But as mentioned - trace them both.

Report message to a moderator

Re: BULK INSERT PERFORMANCE ON PROD [message #659703 is a reply to message #651498]

Fri, 27 January 2017 12:10

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Ok thanks. Will do.

Report message to a moderator

Re: BULK INSERT PERFORMANCE ON PROD [message #659789 is a reply to message #659703]

Mon, 30 January 2017 13:00

forums.sqltop
Messages: 1
Registered: January 2017

Junior Member

Enable SQL trace, 10046, trcsess and tkprof in Oracle

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

You could see at statement level what events are waiting for, like this

PARSE #4:c=0,e=1024,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=11746417122304
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=11746417122304
WAIT #4: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #4: nam='SQL*Net message from client' ela= 6924288 p1=1413697536 p2=1 p3=0

[Edit MC: remove advert]

[Updated on: Mon, 30 January 2017 13:09] by Moderator

Report message to a moderator

Previous Topic:	TOP_LEVEL_SQL_ID
Next Topic:	Query is taking longer duration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:42:47 CDT 2024