RE: Performance problem: Loading data via insert

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Fri, 14 Mar 2014 16:27:29 +0800
Message-ID: <0BDF2A25A09ADD40908745EEFC0A0FB6018909E6_at_HKMGAXMB103A.zone1.scb.net>



So, apparently, you are not doing BULK INSERTs but Row-By-Row Inserts. All suggestions about using PARALLEL and NOLOGGING are, then, irrelevant.  

Does this database receive a significantly different (more columns) XML file ? Could the delay be in the parsing of the XML file ? Is it parsed on the database server or parsed and inserted from an application server ? If using an application server, have you looked at load on the application server and network roundtrip time ?    

Hemant K Chitale    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Abhishek Gurung Sent: Friday, March 14, 2014 3:16 PM
To: Oracle Freelist
Subject: RE: Performance problem: Loading data via insert  

Hi Veerabasai‚Äčah,  

Thanks for replying.

What we do is data comes in XML files we parse them convert them into insert statement and load those data into

tables. Each of these file contains data for multiple tables. before inserting data we also Create and alter tables in database,

if we find any new tables or columns in XML file. Then we execute this sql file containing insert statements to load

data into different tables.

The loading process for all the databases instances start almost at same time(as soon as they receive the xml file).

We have even try to load the files while no other database loading was going on but still the loading is taking

same time. So we conclude that there is something wrong with this database instance but unable to identify it.  

Regards

Abhishek  


Date: Fri, 14 Mar 2014 10:01:25 +1300
Subject: Re: Performance problem: Loading data via insert From: veeracb_at_gmail.com
To: sameer.choudhari_at_gmail.com
CC: carlos.sierra.usa_at_gmail.com; abhishek.gurung_at_hotmail.com; oracle-l_at_freelists.org

Do you always load the data or cleanup some old data before inserting ? What else is running at the same time the load is running, is something blocking this load ?  

As Carlos suggested, it would be better if you get more data around the problem and try identify where your time is going.  

On Fri, Mar 14, 2014 at 9:42 AM, Sameer Choudhari <sameer.choudhari_at_gmail.com> wrote:

Try tuning your insert statements with hints like PARALLEL NOLOGGING .

On Friday, 14 March 2014, Carlos Sierra <carlos.sierra.usa_at_gmail.com> wrote:

Abhishek,  

As always, start with the evidence:

What facts have you collected? What is AWR telling you? What are the SQL Traces telling? Which other diagnostics tools have you used for your case?

Cheers,

Carlos Sierra

blog: carlos-sierra.net
twitter: _at_csierra_usa

Life's Good!  

On Mar 13, 2014, at 6:52 AM, Abhishek Gurung <abhishek.gurung_at_hotmail.com> wrote:          

        Hi         

        We are facing a performance problem but unable to identify where is the problem.         

	We have a production database server where about 8 database instances of oracle is running.
	Daily we insert in each of these database instances about 1-5 GB of data in different tables.
	All database instances are working fine where we are able to load around 2-3 GB of data within 1hour except 
	1 where it is taking about 8 hrs to load only 4 GB of data.
	
	When I try to load the same 4GB of data in another database where only one instance is running and with
	very less configuration as compared to the Production server we were able to load it in 1hr 45 minutes.
	
	Can anyone suggest how can we proceed to identify the problem?
	
	Regards
	Abhishek

 

 

--

Sent from Google Nexus

--

Veerabasaiah C B

"Only put off until tomorrow what you are willing to die having left undone. - Picasso"

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 14 2014 - 09:27:29 CET

Original text of this message