Home » RDBMS Server » Performance Tuning » Fine Tune Insert Statement
icon4.gif  Fine Tune Insert Statement [message #246435] Wed, 20 June 2007 23:38 Go to next message
kumarvk
Messages: 211
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 #246436 is a reply to message #246435] Wed, 20 June 2007 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW the STICKY posts at the top of this forum.

Use CUT & PASTE to post EXPLAIN PLAN for the SELECT statement.

CDO or RBO?

[Updated on: Wed, 20 June 2007 23:42] by Moderator

Report message to a moderator

Re: Fine Tune Insert Statement [message #246518 is a reply to message #246435] Thu, 21 June 2007 03:31 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 #246532 is a reply to message #246518] Thu, 21 June 2007 04:11 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

It runs for 2 seconds and the result is 45850 records.
Re: Fine Tune Insert Statement [message #246775 is a reply to message #246532] Thu, 21 June 2007 20:49 Go to previous messageGo to next message
kumarvk
Messages: 211
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 messageGo to next message
michael_bialik
Messages: 611
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 messageGo to next message
kumarvk
Messages: 211
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 messageGo to next message
michael_bialik
Messages: 611
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 messageGo to next message
kumarvk
Messages: 211
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;


Re: Fine Tune Insert Statement [message #247532 is a reply to message #246435] Tue, 26 June 2007 04:45 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try:

CREATE INDEX ... ON SSCDTL
(CAR_NO , DOC_NO, ID, JOB_CODE) ...


And re-run your statement.

Look at DBMS_XPLAN package for EXPLAIN
Previous Topic: spreport - tuning paramters!
Next Topic: Long Running SQL - NO entry in Session_LongOps view?
Goto Forum:
  


Current Time: Sat Dec 03 20:29:08 CST 2016

Total time taken to generate the page: 0.13946 seconds