OraFAQ Forum: Performance Tuning » Fine Tune Insert Statement

Home » RDBMS Server » Performance Tuning » Fine Tune Insert Statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Fine Tune Insert Statement [message #246435]

Wed, 20 June 2007 23:38

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.

Report message to a moderator

Re: Fine Tune Insert Statement [message #246436 is a reply to message #246435]

Wed, 20 June 2007 23:42

BlackSwan
Messages: 26766
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

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.

Report message to a moderator

Re: Fine Tune Insert Statement [message #246532 is a reply to message #246518]

Thu, 21 June 2007 04:11

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi,

It runs for 2 seconds and the result is 45850 records.

Report message to a moderator

Re: Fine Tune Insert Statement [message #246775 is a reply to message #246532]

Thu, 21 June 2007 20:49

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?

Report message to a moderator

Re: Fine Tune Insert Statement [message #246871 is a reply to message #246435]

Fri, 22 June 2007 05:02

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

Report message to a moderator

Re: Fine Tune Insert Statement [message #247516 is a reply to message #246435]

Tue, 26 June 2007 03:30

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.

Attachment: EXPLAIN_PLAN.SQL
(Size: 5.14KB, Downloaded 1158 times)

Report message to a moderator

Re: Fine Tune Insert Statement [message #247523 is a reply to message #246435]

Tue, 26 June 2007 04:24

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

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;

Report message to a moderator

Re: Fine Tune Insert Statement [message #247532 is a reply to message #246435]

Tue, 26 June 2007 04:45

michael_bialik
Messages: 621
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

Report message to a moderator

Previous Topic:	spreport - tuning paramters!
Next Topic:	Long Running SQL - NO entry in Session_LongOps view?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 02:44:41 CDT 2024