Home » RDBMS Server » Performance Tuning » BULK INSERT PERFORMANCE ON PROD (Oracle 12c, RHEL 6)
BULK INSERT PERFORMANCE ON PROD [message #651290] Sat, 14 May 2016 14:48 Go to next message
jesuisantony
Messages: 155
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.
Re: BULK INSERT PERFORMANCE ON PROD [message #651292 is a reply to message #651290] Sat, 14 May 2016 15:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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.

Re: BULK INSERT PERFORMANCE ON PROD [message #651319 is a reply to message #651292] Sun, 15 May 2016 16:09 Go to previous messageGo to next message
jesuisantony
Messages: 155
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)


Re: BULK INSERT PERFORMANCE ON PROD [message #651320 is a reply to message #651319] Sun, 15 May 2016 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 25794
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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.


Re: BULK INSERT PERFORMANCE ON PROD [message #651498 is a reply to message #651337] Thu, 19 May 2016 03:06 Go to previous messageGo to next message
Roachcoach
Messages: 1508
Registered: May 2010
Location: UK
Senior Member
That doesn't look all that bulk. But as mentioned - trace them both.
Re: BULK INSERT PERFORMANCE ON PROD [message #659703 is a reply to message #651498] Fri, 27 January 2017 12:10 Go to previous messageGo to next message
jesuisantony
Messages: 155
Registered: July 2006
Location: Chennai
Senior Member
Ok thanks. Will do.
Re: BULK INSERT PERFORMANCE ON PROD [message #659789 is a reply to message #659703] Mon, 30 January 2017 13:00 Go to previous message
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:
  


Current Time: Thu Jan 18 07:11:44 CST 2018

Total time taken to generate the page: 0.09165 seconds