Procedure is taking more time to execute [message #637606] |
Thu, 21 May 2015 02:03 |
|
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 #637615 is a reply to message #637609] |
Thu, 21 May 2015 02:46 |
|
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 |
|
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 #637627 is a reply to message #637617] |
Thu, 21 May 2015 03:04 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
nayana08 wrote on Thu, 21 May 2015 08:49I 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 #637636 is a reply to message #637630] |
Thu, 21 May 2015 04:39 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
gazzag wrote on Thu, 21 May 2015 09:15Quote: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 #637826 is a reply to message #637825] |
Tue, 26 May 2015 14:01 |
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)
|
|
|