Home » SQL & PL/SQL » SQL & PL/SQL » Procedure is taking more time to execute (oracle 10g windows 7)
Procedure is taking more time to execute [message #637606] Thu, 21 May 2015 02:03 Go to next message
nayana08
Messages: 6
Registered: May 2015
Location: Ahemdabad
Junior Member
I have more than 10 procedures,In that it will perform insert ,update and delete operation on tables.
This procedure is taking more time to execute.I have check AWR report and top 5 wait events,it say DB file sequential read.
One table is taking more physical read.that table has more than 33 lakhs rows, this table is user for insert and update operation.i have also try to tune using forall..but there are so many insert and update statement in this procedure.

Could you please suggest any solution for this.


Re: Procedure is taking more time to execute [message #637607 is a reply to message #637606] Thu, 21 May 2015 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Optimize your code (remove loops as possible).
Optimize your queries.

Re: Procedure is taking more time to execute [message #637609 is a reply to message #637607] Thu, 21 May 2015 02:22 Go to previous messageGo to next message
nayana08
Messages: 6
Registered: May 2015
Location: Ahemdabad
Junior Member
There are so many insert and update.. how can i remove loops. and insert and update is taking more time it is showing in AWR report.
Re: Procedure is taking more time to execute [message #637610 is a reply to message #637609] Thu, 21 May 2015 02:33 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
My bet is it's not, I bet each action takes the same time as always...you're just hitting the reason row by row is called slow by slow - growth beyond a toy data set.

If you really want to make gains, stop processing row by row.

[Updated on: Thu, 21 May 2015 02:34]

Report message to a moderator

Re: Procedure is taking more time to execute [message #637611 is a reply to message #637609] Thu, 21 May 2015 02:34 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
An Explain Plan would be useful. Also, what indexes do you have on these tables?
Re: Procedure is taking more time to execute [message #637612 is a reply to message #637609] Thu, 21 May 2015 02:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
nayana08 wrote on Thu, 21 May 2015 00:22
There are so many insert and update.. how can i remove loops. and insert and update is taking more time it is showing in AWR report.


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: Procedure is taking more time to execute [message #637615 is a reply to message #637609] Thu, 21 May 2015 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use DBMS_HPROF or DBMS_PROFILER to know where the time is spent.

Edit: Also your AWR report will tell you how many times a procedure is called and how many times the SQL statements inside are called. If there is a big difference between these then you have loops and row by row process and, most often, loops can be removed.

[Updated on: Thu, 21 May 2015 02:48]

Report message to a moderator

Re: Procedure is taking more time to execute [message #637616 is a reply to message #637611] Thu, 21 May 2015 02:47 Go to previous messageGo to next message
nayana08
Messages: 6
Registered: May 2015
Location: Ahemdabad
Junior Member
CREATE TABLE AO_CHRGINF_T
(
PARTY_C NUMBER(10),
PARTY_CD VARCHAR2(10 BYTE),
PART_X VARCHAR2(30 BYTE),
ASONDT_D DATE,
SC_A NUMBER(15,2),
LR_A NUMBER(15,2),
NAA_A NUMBER(15,2),
TOTAL_A NUMBER(15,2),
INTREV_A NUMBER(15,2),
INTDYS_N NUMBER(10),
PREVDUES_A NUMBER(15,2),
CURRSC_A NUMBER(15,2),
CURRNAA_A NUMBER(15,2),
CURRLR_A NUMBER(15,2),
REMAIN_AMT NUMBER,
PAID_D DATE,
REVID NUMBER,
SC_ST NUMBER,
SC_EC NUMBER,
NAA_ST NUMBER,
NAA_EC NUMBER,
LR_ST NUMBER,
LR_EC NUMBER,
CURRINFRA_A NUMBER,
INF_ST NUMBER,
INF_EC NUMBER,
SC_INT NUMBER,
NAA_INT NUMBER,
LR_INT NUMBER,
NAA_ST_REV NUMBER,
NAA_EC_REV NUMBER,
SC_ST_REV NUMBER,
SC_EC_REV NUMBER,
LR_ST_REV NUMBER,
LR_EC_REV NUMBER,
IUF_REV NUMBER,
IUF_ST_REV NUMBER,
IUF_EC_REV NUMBER,
AMALG_AREA NUMBER
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE INDEX AO_CHRGINF_T_PARTY_C_REMAIN__4 ON AO_CHRGINF_T
(PARTY_C, REMAIN_AMT)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE BITMAP INDEX QUEST_SX_IDX50953A49F195114E25 ON AO_CHRGINF_T
(NAA_EC)
NOLOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE BITMAP INDEX QUEST_SX_IDX50953A49F197940A87 ON AO_CHRGINF_T
(AMALG_AREA, CURRINFRA_A, INTREV_A)
NOLOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX AO_CHRGINF_T ON AO_CHRGINF_T
(PARTY_C, ASONDT_D)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

Here also attaching AWR report.

Thanks.
Re: Procedure is taking more time to execute [message #637617 is a reply to message #637615] Thu, 21 May 2015 02:49 Go to previous messageGo to next message
nayana08
Messages: 6
Registered: May 2015
Location: Ahemdabad
Junior Member
I got which statement is taking more time.. which is insert and update on this table.
Re: Procedure is taking more time to execute [message #637618 is a reply to message #637616] Thu, 21 May 2015 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.
Never post schema names and storage clause in your CREATE object statements: we have the same users and tablespaces.

Re: Procedure is taking more time to execute [message #637619 is a reply to message #637617] Thu, 21 May 2015 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

nayana08 wrote on Thu, 21 May 2015 09:49
I got which statement is taking more time.. which is insert and update on this table.


This is not what I said, read my answer again.
The path is:
1/ optimize the code
2/ optimize the SQL statements

[Updated on: Thu, 21 May 2015 02:52]

Report message to a moderator

Re: Procedure is taking more time to execute [message #637620 is a reply to message #637619] Thu, 21 May 2015 02:52 Go to previous messageGo to next message
nayana08
Messages: 6
Registered: May 2015
Location: Ahemdabad
Junior Member
k sorry...
Re: Procedure is taking more time to execute [message #637622 is a reply to message #637620] Thu, 21 May 2015 02:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
nayana08 wrote on Thu, 21 May 2015 00:52
k sorry...


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Re: Procedure is taking more time to execute [message #637625 is a reply to message #637622] Thu, 21 May 2015 02:57 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
NOLOGGING .....

I hope it's not production
Re: Procedure is taking more time to execute [message #637627 is a reply to message #637617] Thu, 21 May 2015 03:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
nayana08 wrote on Thu, 21 May 2015 08:49
I got which statement is taking more time.. which is insert and update on this table.

So what are these statements? Do you know how to get the execution plan and execution statistics for these statements?

That AWR report is useless: all it says is that the database is doing virtually nothing, and that AWR is just reporting on itself.

And by the way, what's a "lakhs"?
Re: Procedure is taking more time to execute [message #637628 is a reply to message #637627] Thu, 21 May 2015 03:10 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Lakh.
Re: Procedure is taking more time to execute [message #637629 is a reply to message #637628] Thu, 21 May 2015 03:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gazzag wrote on Thu, 21 May 2015 01:10
Lakh.

DUH?
Re: Procedure is taking more time to execute [message #637630 is a reply to message #637629] Thu, 21 May 2015 03:15 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Quote:
A lakh or lac ... is a unit in the Indian numbering system equal to one hundred thousand (100,000; scientific notation: 105). In the Indian convention of digit grouping, it is written as 100,000.
Re: Procedure is taking more time to execute [message #637635 is a reply to message #637630] Thu, 21 May 2015 04:10 Go to previous messageGo to next message
nayana08
Messages: 6
Registered: May 2015
Location: Ahemdabad
Junior Member
Thanks for explanation.it was mistake.
Re: Procedure is taking more time to execute [message #637636 is a reply to message #637630] Thu, 21 May 2015 04:39 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
gazzag wrote on Thu, 21 May 2015 09:15
Quote:
A lakh or lac ... is a unit in the Indian numbering system equal to one hundred thousand (100,000; scientific notation: 105). In the Indian convention of digit grouping, it is written as 100,000.

Ah. I usually use SI units (that's my two groat's worth).
Re: Procedure is taking more time to execute [message #637637 is a reply to message #637636] Thu, 21 May 2015 04:41 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
John Watson wrote on Thu, 21 May 2015 10:39
(that's my two groat's worth).

Ah... gotcha Wink
Re: Procedure is taking more time to execute [message #637825 is a reply to message #637637] Tue, 26 May 2015 12:46 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I would get rid of the bitmap indexes and replace then with standard indexes. A bit map index is very expensive to maintain on inserts and updates.
Re: Procedure is taking more time to execute [message #637826 is a reply to message #637825] Tue, 26 May 2015 14:01 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Bill, I thought your statement regarding bitmap indexes was correct - but I tested it anyway:
orclz>
orclz> create table t1 (c1 date);

Table created.

Elapsed: 00:00:00.01
orclz> create index i1 on t1(c1);

Index created.

Elapsed: 00:00:00.03
orclz> insert into t1 select sysdate from dual connect by level < 1000000;

999999 rows created.

Elapsed: 00:00:07.67
orclz> drop table t1;

Table dropped.

Elapsed: 00:00:00.23
orclz> create table t1 (c1 date);

Table created.

Elapsed: 00:00:00.01
orclz> create bitmap index i1 on t1(c1);

Index created.

Elapsed: 00:00:00.03
orclz> insert into t1 select sysdate from dual connect by level < 1000000;

999999 rows created.

Elapsed: 00:00:02.53
orclz>
orclz> select * from v$version where rownum=1;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0

Elapsed: 00:00:00.03
orclz>
The results are repeatable (for me, anyway)
Previous Topic: Problem with calculating hours
Next Topic: Interview questions
Goto Forum:
  


Current Time: Thu Mar 28 09:41:46 CDT 2024