Home » RDBMS Server » Performance Tuning » insert time is drastically reduced (Oracle 12.1.0.2 ,Windows server 2012 64bit)
insert time is drastically reduced [message #664010] Mon, 03 July 2017 06:28 Go to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Hi,

there is a procedure for inserting data to a table, table ddl and procedure are as follows:

CREATE TABLE PT_PRO 
(
  ID NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER NOT NULL 
, DATE_ID NUMBER NOT NULL 
, GROUP_ID NUMBER 
, TYPE_ID NUMBER 
, CODE NUMBER 
}

(There is also an index on each column.)


declare 
type t_date_id is table of pt_pro.date_id%type index by pls_integer; 
type t_group_id is table of pt_pro.group_id%type index by pls_integer; 
type t_type_id is table of pt_pro.type_id%type index by pls_integer; 
type t_code is table of pt_pro.code%type index by pls_integer; 
p_ date_id t_date_id; 
p_ group_id t_group_id; 
p_ type_id t_type_id; 
p_code t_code; "
begin 
p_ date_id := :date_id; 
p_ group_id := : group_id ; 
p_ type_id := : type_id; 
p_code := :code; 
forall i in p_date.first..p_date_id.last 
insert into pt_pro (date_id,group_id,type _id,code) 
values (p_date_id(i), p_group_id(i) , p_type_id(i), p_code(i)); 

Previously, our inserting process was around 2 million record in 40 second,But now 7,000 records are inserting in 30 second(In some cases even more).

2 Images are attached, attached images show execution plan and consumed times.

Cloud anyone help me for resolving this issue?
  • Attachment: 2.png
    (Size: 27.98KB, Downloaded 1512 times)

[Updated on: Mon, 03 July 2017 06:31]

Report message to a moderator

Re: insert time is drastically reduced [message #664019 is a reply to message #664010] Mon, 03 July 2017 08:30 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
trace the session and see where the time is being spent.
Re: insert time is drastically reduced [message #664020 is a reply to message #664019] Mon, 03 July 2017 08:57 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Thank you for your reply.
What are the instructions of tracing sessions? How can do that?
Re: insert time is drastically reduced [message #664021 is a reply to message #664020] Mon, 03 July 2017 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
http://www.orafaq.com/wiki/SQL_Trace
Re: insert time is drastically reduced [message #664063 is a reply to message #664010] Tue, 04 July 2017 07:20 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Formatted SQL Trace output using TKprof is as follow:

SQL ID: 8vfg94vf8m7m9 Plan Hash: 523367902

INSERT INTO PT_PRO(DATE_ID,GROUP_ID,TYPE_ID,
  CODE) 
VALUES
 (:B1 , :B2 , :B3 , :B4 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.65      12.92       7426        857     165865        8022
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.65      12.92       7426        857     165865        8022

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 103  (D_USER)   (recursive depth: 1)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'PT_PRO'
      0    SEQUENCE OF 'ISEQ$$_108127' (SEQUENCE)

[Updated on: Tue, 04 July 2017 07:21]

Report message to a moderator

Re: insert time is drastically reduced [message #664065 is a reply to message #664063] Tue, 04 July 2017 07:24 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Include recursive operations, it might be struggling around the sequence.

Is the sequence ordered?
Re: insert time is drastically reduced [message #664066 is a reply to message #664065] Tue, 04 July 2017 07:26 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
sequence definition is :


  ID NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER NOT NULL 

Re: insert time is drastically reduced [message #664067 is a reply to message #664066] Tue, 04 July 2017 07:31 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Take order off it and cache higher

And check no-one has added any funky triggers whilst you weren't looking.

[Updated on: Tue, 04 July 2017 07:34]

Report message to a moderator

Re: insert time is drastically reduced [message #664069 is a reply to message #664010] Tue, 04 July 2017 07:42 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
This procedure had no problems with the default settings on sequence ( order and ...)
Re: insert time is drastically reduced [message #664071 is a reply to message #664069] Tue, 04 July 2017 07:49 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If you don't want to change sub-optimal values I don't know what to tell you

¯\_(ツ)_/¯
Re: insert time is drastically reduced [message #664073 is a reply to message #664069] Tue, 04 July 2017 08:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Tue, 04 July 2017 05:42
This procedure had no problems with the default settings on sequence ( order and ...)
Then please tell us where the problem does exist, since you claim to know where it does not exist.
Re: insert time is drastically reduced [message #664077 is a reply to message #664073] Tue, 04 July 2017 08:06 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
sorry, I don't mean that.I am not actually perfect as you. I was wondering just the cause of problem.
Re: insert time is drastically reduced [message #664082 is a reply to message #664077] Tue, 04 July 2017 08:13 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
It's not about being perfect, those values leave you vulnerable to contention and contention is often a transient, "sometimes there" event. Thus we strive to remove points of contention in order to scale effectively/maintain a stable performance profile.

If there is still a problem after that we can look again.

[Updated on: Tue, 04 July 2017 08:14]

Report message to a moderator

Re: insert time is drastically reduced [message #664084 is a reply to message #664082] Tue, 04 July 2017 08:18 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Thank you so much for your useful advise,I apologize again for the whisper.


I will post the results after changes here.

[Updated on: Tue, 04 July 2017 08:19]

Report message to a moderator

Re: insert time is drastically reduced [message #664085 is a reply to message #664077] Tue, 04 July 2017 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I realize that you are obfuscating the actual code that has the issue.
Posted code contains ZERO data to actually be INSERTED.

Row by row is slow by slow.

Do not do in PL/SQL that which can be done in plain SQL.
I strongly suspect that the slowness occurs during the loading of the PL/SQL data elements; not during the INSERT itself.

> our inserting process was around 2 million record in 40 second,But now 7,000 records are inserting in 30 second(In some cases even more).
What changed?

Re: insert time is drastically reduced [message #664088 is a reply to message #664085] Tue, 04 July 2017 08:24 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
BlackSwan wrote on Tue, 04 July 2017 14:18
I realize that you are obfuscating the actual code that has the issue.
Posted code contains ZERO data to actually be INSERTED.

Row by row is slow by slow.

Do not do in PL/SQL that which can be done in plain SQL.
I strongly suspect that the slowness occurs during the loading of the PL/SQL data elements; not during the INSERT itself.

> our inserting process was around 2 million record in 40 second,But now 7,000 records are inserting in 30 second(In some cases even more).
What changed?


That's what I thought but the image has no query presence so hoped it was accurate Smile

I actually suspect someone enterprising has added a trigger
Re: insert time is drastically reduced [message #664090 is a reply to message #664085] Tue, 04 July 2017 08:27 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
the complete code is written in c#(ADO.NEt)(I have just posted the query)
Bind variables will get values form array which are initialized.
Re: insert time is drastically reduced [message #664091 is a reply to message #664088] Tue, 04 July 2017 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I strongly suspect a trigger as well - 13 seconds for 8000 rows is ridiculously slow
Re: insert time is drastically reduced [message #664092 is a reply to message #664091] Tue, 04 July 2017 08:32 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Actually We have not defined any trigger.
Re: insert time is drastically reduced [message #664093 is a reply to message #664090] Tue, 04 July 2017 08:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Tue, 04 July 2017 06:27
the complete code is written in c#(ADO.NEt)(I have just posted the query)
Bind variables will get values form array which are initialized.

> our inserting process was around 2 million record in 40 second,But now 7,000 records are inserting in 30 second(In some cases even more).
What changed?

Re: insert time is drastically reduced [message #664170 is a reply to message #664010] Thu, 06 July 2017 01:24 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member

I think the problem was caused by PGA, Becuase:

Two days ago ( when we had problem), The PGA Advisor had advised increase the memory size from 50G to 60G, We didn't do it,but we restarted the database. After open database, the query run slow on first executions, but after that the query is running at high speed.


Can this be true?

Re: insert time is drastically reduced [message #664190 is a reply to message #664170] Thu, 06 July 2017 07:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Wed, 05 July 2017 23:24

I think the problem was caused by PGA, Becuase:

Two days ago ( when we had problem), The PGA Advisor had advised increase the memory size from 50G to 60G, We didn't do it,but we restarted the database. After open database, the query run slow on first executions, but after that the query is running at high speed.


Can this be true?

After first execution, both SQL & data can be resident in SGA so no additional parsing or physical I/O is required.
So it is reasonable & expected that after the first time query has been completed it will run faster.
Re: insert time is drastically reduced [message #664232 is a reply to message #664190] Fri, 07 July 2017 22:37 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
thanks, what about PGA?
Re: insert time is drastically reduced [message #664233 is a reply to message #664232] Fri, 07 July 2017 22:53 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Fri, 07 July 2017 20:37
thanks, what about PGA?
What about PGA?

post SQL & results that show PGA is an issue.
Previous Topic: Why my function based did not use?
Next Topic: insert query
Goto Forum:
  


Current Time: Thu Mar 28 05:14:58 CDT 2024