Home » RDBMS Server » Performance Tuning » Fine Tune Insert Statement
Fine Tune Insert Statement [message #246435] |
Wed, 20 June 2007 23:38 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
Hi All,
I have the following insert statement. What happens is that it takes more than 20 minutes to insert. Even though the no. of records is about 500+.
INSERT INTO SSCDTL (CAR_NO,DOC_NO,DOC_DATE,JOB_CODE,DESCRIPTION,DOC_AMT,ALOCATE_CODE,ID)
SELECT A.CAR_NO,B.INV_NO,B.INV_DATE,C.JOB_CODE,C.JOB_DESC,C.JOB_PRICE,C.ALOCATE_CODE,C.SEQNO
FROM SSCHDR A,SSAMAST B,SSADETL C
WHERE A.CAR_NO=B.CAR_NO AND B.INV_NO=C.INV_NO AND
(C.ALOCATE_CODE='HDV' OR C.ALOCATE_CODE='LDV' OR C.ALOCATE_CODE='MDV' OR C.ALOCATE_CODE='BUS' OR
C.ALOCATE_CODE='OPEL') AND (A.CAR_NO,B.INV_NO,C.JOB_CODE) NOT IN
(SELECT D.CAR_NO,D.DOC_NO,D.JOB_CODE FROM SSCDTL D);
Can anyone help to fine tune this and make to faster to run?
My Oracle 8i server is running under Windows 2000 server with dual process.
|
|
|
|
Re: Fine Tune Insert Statement [message #246518 is a reply to message #246435] |
Thu, 21 June 2007 03:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
How many rows are in each referenced table?
How many distict values has a ALOCATE_CODE column of SSADETL table?
What is the result of following query and how long it runs:
SELECT COUNT(*)
FROM SSCHDR A,SSAMAST B,SSADETL C
WHERE A.CAR_NO=B.CAR_NO AND B.INV_NO=C.INV_NO AND
(C.ALOCATE_CODE='HDV' OR C.ALOCATE_CODE='LDV' OR
C.ALOCATE_CODE='MDV' OR C.ALOCATE_CODE='BUS' OR
C.ALOCATE_CODE='OPEL')
Michael.
|
|
|
|
Re: Fine Tune Insert Statement [message #246775 is a reply to message #246532] |
Thu, 21 June 2007 20:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
Hi,
There are 11388 records in SSCHDR, 36954 records in SSAMAST,
216869 records in SSADETL and the script which you gave runs for couple of seconds and records shown is 45850.
The following are the distinct value for column ALOCATE_CODE
A.EXP,BUS,D.EXP,HDV,LDV,LUBPK,MDV,MISC,OPEL,OTHRS,P.WRN,PDI,PR.PM,PR.TM,U.WRN,W.SVC,NULL
Anything you require?
|
|
|
Re: Fine Tune Insert Statement [message #246871 is a reply to message #246435] |
Fri, 22 June 2007 05:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Hi.
You didn't:
1. Post EXPLAIN PLAN.
2. Specified how many rows are in SSCDTL table as well.
What indexes you have for all tables?
Do you have multicolumn index on (CAR_NO, DOC_NO, JOB_CODE) of SSCDTL table?
Michael
|
|
|
Re: Fine Tune Insert Statement [message #247516 is a reply to message #246435] |
Tue, 26 June 2007 03:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
There are
11388 records in SSCHDR,
36954 records in SSAMAST,
216869 records in SSADETL,
77112 records SSCDTL and the script which you gave runs for couple of seconds and records shown is 45850.
The following are the distinct value for column ALOCATE_CODE
A.EXP,BUS,D.EXP,HDV,LDV,LUBPK,MDV,MISC,OPEL,OTHRS,P.WRN,PDI,PR.PM,PR.TM,U.WRN,W.SVC,NULL
I have uploaded the explain_plan table for our reference.
|
|
|
Re: Fine Tune Insert Statement [message #247523 is a reply to message #246435] |
Tue, 26 June 2007 04:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
I asked for EXPLAIN, not it's INSERTs.
Do you have triggers on SSCDTL table?
Do you have multicolumn index on (CAR_NO, DOC_NO, JOB_CODE) of SSCDTL table?
Assuming you do have such an index (if you don't - create it ) - try:
SELECT A.CAR_NO,B.INV_NO,B.INV_DATE,C.JOB_CODE,C.JOB_DESC,C.JOB_PRICE,C.ALOCATE_CODE,C.SEQNO
FROM SSCHDR A,SSAMAST B,SSADETL C
WHERE A.CAR_NO=B.CAR_NO AND B.INV_NO=C.INV_NO AND
(C.ALOCATE_CODE='HDV' OR C.ALOCATE_CODE='LDV' OR C.ALOCATE_CODE='MDV' OR C.ALOCATE_CODE='BUS' OR
C.ALOCATE_CODE='OPEL') AND
NOT EXISTS ( SELECT 1 FROM SSCDTL D
WHERE D.CAR_NO = A.CAR_NO AND D.DOC_NO = B.INV_NO AND D.JOB_CODE = C.JOB_CODE);
How long it runs? Post it's EXPLAIN as well.
[Updated on: Tue, 26 June 2007 04:31] Report message to a moderator
|
|
|
Re: Fine Tune Insert Statement [message #247529 is a reply to message #247523] |
Tue, 26 June 2007 04:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
My index key for SSCDTL. I don't have trigger. I have given you the records from my plan_table. What do you mean by EXPLAIN? Please let me know how to do it?
CREATE UNIQUE INDEX CSS.SSCDTL_PK ON SSCDTL
(CAR_NO, DOC_DATE, DOC_NO, ID, JOB_CODE)
NOLOGGING
TABLESPACE SSU
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
|
|
|
|
Goto Forum:
Current Time: Sat Aug 17 01:58:50 CDT 2024
|