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 Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
Re: BULK INSERT PERFORMANCE ON PROD [message #651319 is a reply to message #651292] |
Sun, 15 May 2016 16:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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)
|
|
|
Re: BULK INSERT PERFORMANCE ON PROD [message #651320 is a reply to message #651319] |
Sun, 15 May 2016 17:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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 #659789 is a reply to message #659703] |
Mon, 30 January 2017 13:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/483b8c99d92c8dfde82ba552f6d7b54b?s=64&d=mm&r=g) |
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
|
|
|
Goto Forum:
Current Time: Sun May 05 20:02:44 CDT 2024
|