Home » RDBMS Server » Performance Tuning » insert statement runs very slow
| insert statement runs very slow [message #201623] |
Mon, 06 November 2006 02:21  |
beastjj
Messages: 3
Registered: October 2006
Location: CHINA
|
Junior Member |
|
|
insert statement runs slow
Hi,Buddy:
DB VERSION: 10.1.0.2.0
Now i meet such situation: table and index analyzed every midnight, and the biggest table REQDTF has 3893353 rows.when i running such statement, it executed 0.27 second.
2:27:53 SQL> select count(*) from reqdtf;
COUNT(*)
----------
3893353
Executed in 0.27 seconds
02:28:00 SQL>
other tables have only 100000 rows data.REQDTF_AVL_TEMP will be truncated everyday before insert.
my problem is when i insert REQDTF_AVL_TEMP table, it cost 20 minute, all index are used. so i want to speed up this statement.for 20 minutes can't bear.thanks for you aiding.
INSERT /*+noappend*/ INTO REQDTF_AVL_TEMP ( FPMODRFLG , FOYAODRNO ,FOYAODRNO2 ,FREQSEQNO, FSTRSEQNO, FKOITEMNO ,FLOTNO ,FLOTNO2, FKOWHCD ,FKOLCTCD ,FREQDTE ,FFIRMREQ , FSTRUUNIT , FISSWAY , FISSTTL , FNOREQQTY , FREQDTFLG,FPRJNO , FITEMNO , FMFGODRSTS , FMOISSFLG , FOYAITEM_FUNIT , FMFGODRQTY, FITEMTYP , FPASTFLG ,FITMCNVTYP , FUNEXCHG , FKOITEM_FUNIT ) SELECT REQDTF.FPMODRFLG,REQDTF.FOYAODRNO,REQDTF.FOYAODRNO2,REQDTF.FREQSEQNO,REQDTF.FSTRSEQNO,REQDTF.FKOITEMNO,REQDTF.FLOTNO,REQDTF.FLOTNO2,R EQDTF.FKOWHCD, REQDTF.FKOLCTCD, CASE WHEN REQDTF.FREQDTE <= :B7 THEN :B7 ELSE REQDTF.FREQDTE END , REQDTF.FFIRMREQ, REQDTF.FUUNIT, REQDTF.FISSWAY, DECODE(REQDTF.FFIRMREQ,0,DECODE(REQDTF.FSPAREITEM,REQDTF.FKOITEMNO,REQDTF.FISSTTL,LEAST(REQDTF.FISSTTL,REQDTF.FFIRMREQ)),REQDTF.FISST TL) REQDTF_FISSTTL, REQDTF.FNOREQQTY,REQDTF.FREQDTFLG,MORLSF.FPRJNO,MORLSF.FITEMNO,MORLSF.FMFGODRSTS,MORLSF.FMOISSFLG,ITEMM_A.FUNIT,MORLSF.FMFGODRQTY,ITE MM_A.FITEMTYP, CASE WHEN REQDTF.FREQDTE <= :B7 THEN 'Y' ELSE 'N' END, ITEMM_B.FITMCNVTYP, 1, ITEMM_B.FUNIT FROM MORLSF,REQDTF,ITEMM ITEMM_A,ITEMM ITEMM_B WHERE ( MORLSF.FMFGODRSTS <> 'C' ) AND ( MORLSF.FMFGODRSTS <> 'H' ) AND ( REQDTF.FREQDTE <= :B6 ) AND ( FFIRMREQ - FISSTTL - FNOREQQTY > 0 ) AND ( MORLSF.FMFGODRSTS <> :B5 ) AND ( MORLSF.FITEMNO = ITEMM_A.FITEMNO ) AND ( REQDTF.FOYAODRNO = MORLSF.FMFGODRNO ) AND ( REQDTF.FPMODRFLG = 'M' ) AND ( REQDTF.FPMODRFLG = MORLSF.FPMODRFLG ) AND ( RTRIM(REQDTF.FKOITEMNO) LIKE RTRIM(:B4 ) ) AND ( RTRIM(REQDTF.FKOWHCD) LIKE RTRIM(:B3 ) ) AND ( REQDTF.FREQDTFLG <> :B2 ) AND ( REQDTF.FKOITEMNO = ITEMM_B.FITEMNO) AND ( RTRIM(ITEMM_B.FITEMTYP) LIKE :B1 ) AND ( NVL(MORLSF.FMOISSFLG,'N') <> 'C')
31562 rows created.
Elapsed: 00:20:37.19
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
------------------
| 0 | INSERT STATEMENT | | 31 | 6293 | 7468
(1)| 00:01:30 |
| 1 | NESTED LOOPS | | 31 | 6293 | 7468
(1)| 00:01:30 |
| 2 | NESTED LOOPS | | 31 | 5580 | 7452
(1)| 00:01:30 |
| 3 | NESTED LOOPS | | 31 | 4929 | 7437
(1)| 00:01:30 |
|* 4 | TABLE ACCESS BY INDEX ROWID| REQDTF | 190 | 21090 | 7342
(1)| 00:01:29 |
|* 5 | INDEX RANGE SCAN | REQDTF5 | 4756 | | 13391
(1)| 00:02:41 |
|* 6 | TABLE ACCESS BY INDEX ROWID| MORLSF | 1 | 48 | 1
(0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_MORLSF_1__10 | 1 | | 0
(0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | ITEMM | 1 | 21 | 1
(0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_ITEMM_1__12 | 1 | | 0
(0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | ITEMM | 1 | 23 | 1
(0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_ITEMM_1__12 | 1 | | 0
(0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("FFIRMREQ"-"FISSTTL"-"FNOREQQTY">0 AND
"REQDTF"."FREQDTE"<=TO_DATE('2006-10-15 00:00:00', 'yyyy-mm-dd hh2
4:mi:ss'))
5 - access("REQDTF"."FPMODRFLG"='M')
filter(RTRIM("REQDTF"."FKOITEMNO") LIKE '%' AND RTRIM("REQDTF"."FKOWHCD")
LIKE '%'
AND "REQDTF"."FREQDTFLG"<>'02')
6 - filter(NVL("MORLSF"."FMOISSFLG",'N')<>'C' AND "MORLSF"."FMFGODRSTS"<>'C'
AND
"MORLSF"."FMFGODRSTS"<>'H' AND "MORLSF"."FPMODRFLG"='M' AND
"REQDTF"."FPMODRFLG"="MORLSF"."FPMODRFLG")
7 - access("REQDTF"."FOYAODRNO"="MORLSF"."FMFGODRNO")
9 - access("MORLSF"."FITEMNO"="ITEMM_A"."FITEMNO")
10 - filter(RTRIM("ITEMM_B"."FITEMTYP") LIKE 'P')
11 - access("REQDTF"."FKOITEMNO"="ITEMM_B"."FITEMNO")
Statistics
----------------------------------------------------------
2375 recursive calls
102571 db block gets
2886786 consistent gets
262418 physical reads
12226768 redo size
639 bytes sent via SQL*Net to client
2327 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
31562 rows processed
[Updated on: Mon, 06 November 2006 03:41] by Moderator Report message to a moderator |
|
|
|
 Re: insert statement runs very slow [message #201625 is a reply to message #201623] |
Mon, 06 November 2006 02:28  |
beastjj
Messages: 3
Registered: October 2006
Location: CHINA
|
Junior Member |
|
|
table structure are as follow:
-- Create table
create global temporary table REQDTF_AVL_TEMP
(
FPMODRFLG CHAR(1),
FOYAODRNO CHAR(10),
FOYAODRNO2 NUMBER,
FREQSEQNO NUMBER,
FSTRSEQNO CHAR(4),
FKOITEMNO CHAR(15),
FLOTNO CHAR(10),
FLOTNO2 NUMBER,
FKOWHCD CHAR(6),
FKOLCTCD CHAR(6),
FREQDTE DATE,
FFIRMREQ NUMBER(18,6),
FSTRUUNIT CHAR(2),
FISSWAY CHAR(1),
FISSTTL NUMBER(18,6),
FNOREQQTY NUMBER(18,6),
FREQDTFLG CHAR(2),
FPRJNO CHAR(10),
FITEMNO CHAR(15),
FMFGODRSTS CHAR(1),
FMOISSFLG CHAR(1),
FOYAITEM_FUNIT CHAR(2),
FMFGODRQTY NUMBER(18,6),
FITEMTYP CHAR(1),
FPASTFLG CHAR(1),
FITMCNVTYP CHAR(1),
FUNEXCHG NUMBER(18,6),
FKOITEM_FUNIT CHAR(2)
)
on commit delete rows;
-- Create/Recreate indexes
create index IDX_REQDTF_AVL_TEMP on REQDTF_AVL_TEMP (FKOITEMNO, FKOWHCD, FREQDTE);
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
-- Create table
create table REQDTF
(
FPMODRFLG CHAR(1) not null,
FOYAODRNO CHAR(10) not null,
FOYAODRNO2 NUMBER default 0 not null,
FREQSEQNO NUMBER default 0 not null,
FSTRSEQNO CHAR(4) not null,
FKOITEMNO CHAR(15) not null,
FLOTNO CHAR(10) not null,
FLOTNO2 NUMBER default 0 not null,
FKOWHCD CHAR(6) not null,
FKOLCTCD CHAR(6) not null,
FKOISSTO CHAR(6),
FREQORG NUMBER(18,6) default 0,
FREQDTE DATE,
FFIRMREQ NUMBER(18,6) default 0,
FUUNIT CHAR(2),
FASSNSTS CHAR(1),
FASSNREQ NUMBER(18,6) default 0,
FISSWAY CHAR(1),
FISSTTL NUMBER(18,6) default 0,
FEXISSQTY NUMBER(18,6) default 0,
FNOREQQTY NUMBER(18,6) default 0,
FCORRWAY CHAR(3),
FCORRFACT NUMBER(12,6) default 0,
FREQDTFLG CHAR(2),
FNETREQQTY NUMBER(18,6) default 0,
FNETISSQTY NUMBER(18,6) default 0,
FENTDT VARCHAR2(19),
FENTUSR VARCHAR2( ,
FUPDTEDT VARCHAR2(19),
FUPDTEUSR VARCHAR2(,
FUPDTEPRG VARCHAR2(110),
FSPCCORRCD VARCHAR2(3),
FNOTE VARCHAR2(60),
FROUTSEQ CHAR(3) not null,
FISEQNO CHAR(3),
FICLASS CHAR(3),
FJCLASS CHAR(2),
FSPAREITEM CHAR(15),
FALTISSTTL NUMBER(18,6) default 0,
FLEVEL NUMBER,
FREQSEQNO2 NUMBER default 0,
FISSTOWIP NUMBER(18,6) default 0,
FUNPROCISS NUMBER(18,6) default 0,
FDCCTRL CHAR(1) default 'I',
FCONO CHAR(7),
CACHE1 CHAR(100),
FCHGQTY NUMBER(18,6),
FWEBFLG CHAR(1) default 'N',
FBULKFLG CHAR(1)
)
tablespace OMSDATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 80K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table REQDTF
add constraint PK_REQDTF_3__17 primary key (FPMODRFLG, FOYAODRNO, FOYAODRNO2, FREQSEQNO, FSTRSEQNO, FKOITEMNO, FKOWHCD, FKOLCTCD, FLOTNO, FLOTNO2, FROUTSEQ)
using index
tablespace OMSIDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index REQDTF4 on REQDTF (FKOITEMNO)
tablespace OMSIDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 80K
minextents 1
maxextents unlimited
);
create index REQDTF5 on REQDTF (FPMODRFLG, FOYAODRNO, FKOITEMNO, FKOWHCD, FREQDTFLG)
tablespace OMSIDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 80K
minextents 1
maxextents unlimited
)
compress 1;
create index REQDTF6 on REQDTF (FKOISSTO)
tablespace OMSIDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 80K
minextents 1
maxextents unlimited
);
create index REQDTF7 on REQDTF (FKOWHCD)
tablespace OMSIDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 80K
minextents 1
maxextents unlimited
);
create index REQDTF8 on REQDTF (FOYAODRNO, FKOITEMNO, FLOTNO, FLOTNO2)
tablespace OMSIDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 80K
minextents 1
maxextents unlimited
);
create index REQDTF9 on REQDTF (CACHE1)
tablespace OMSIDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 200K
minextents 1
maxextents unlimited
);
|
|
|
|
Goto Forum:
Current Time: Wed Nov 27 05:50:33 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
