Re: Select is fast, insert is very very slow

Ranganath,

A couple of questions:

• is this SQL statement truly a "bulk load", executed by a single thread or process at a time, to load a truly large number of rows each time?
• or, is it being executed potentially by many different processes all executing at the same time?

The APPEND hint is making this into a "bulk load", which will force the statement to acquire enqueues blocking all other processes performing DML and DDL on that table. Then, the APPEND will cause a TEMPORARY segment to be created into which the rows will be inserted, which upon successful completion the TEMPORARY segment will be merged into the TABLE segment as a new set of extents. When all of this is completed, then and only then will all of the enqueues be released so that other activity can occur.

As a quick and easy experiment: what happens to INSERT performance if you remove the APPEND hint?

Hope this helps...

-Tim

on 5/15/03 1:01 AM, Krishnaswamy, Ranganath at Ranganath.Krishnaswamy_at_blr.hpsglobal.com wrote:

> Hi List,
>
> The below insert statement is taking about 5 minutes to insert
> around 74,000 records. I have altered the temp_segment_sr_filter table to
> nologging, increased the number of freelists to 2 and there are no indexes
> on the temp_segment_sr_filter table. The select statement takes only 250 ms
> to execute where as while inserting into the temp_segment_sr_filter it takes
> 5 minutes:
>
> INSERT /*+ append */ INTO
> TEMP_SEGMENT_SR_FILTER (FLIGHT_SCH_KEY, FLIGHT_SCH_TYPE, FLIGHT_NO,
> FLIGHT_SGT_KEY, ORG_STN_KEY, DEST_STN_KEY, SERVICE_CODE, AC_TYPE_KEY,
> POSITION_CODE)
> SELECT
> TDFS.FLIGHT_SCH_KEY,
> TDFS.FLIGHT_SCH_TYPE,
> FSH.FLIGHT_NO,
> FSG.FLIGHT_SGT_KEY,
> FSG.ORG_STN_KEY,
> FSG.DEST_STN_KEY,
> TOS.SERVICE_CODE,
> FSG.AC_TYPE_KEY AS AC_TYPE_KEY,
> POS.POSITION_CODE
> FROM
> TEMP_DAY_FLIGHT_SCHEDULE TDFS,
> FLIGHT_SCHEDULE FSH,
> FLIGHT_SGT FSG,
> TEMP_OFP_SERVICE TOS,
> POSITION POS
> WHERE
> TDFS.FLIGHT_SCH_KEY = FSH.FLIGHT_SCH_KEY
> AND FSH.FLIGHT_SCH_KEY = FSG.FLIGHT_SCH_KEY
> AND TOS.SERVICE_CODE <> '*'
> AND POS.POSITION_CODE <> '*'
> AND TDFS.FLIGHT_SCH_TYPE = 'F'
> AND FSG.AC_TYPE_KEY <> 132
> AND NOT EXISTS (
> SELECT
> /*+ index(SR i2_station_restriction) no_expand */
> 1
> FROM
> STATION_RESTRICTION SR,
> STN_RSTCN_TO_FRM SRTF,
> STN_RESTRICTION_SPL_CODE SRSC
> WHERE
> SR.STN_RSTCN_KEY = SRTF.STN_RSTCN_KEY
> AND SR.STN_RSTCN_KEY = SRSC.STN_RSTCN_KEY (+)
> AND SRSC.STN_RSTCN_KEY IS NULL
> AND (SR.AC_TYPE_KEY = FSG.AC_TYPE_KEY OR
> SR.AC_TYPE_KEY = 517 )
> AND (
> (
> POS.POSITION_CODE IN ('E', 'TE')
> AND (
> (
> SR.STN_KEY =
> FSG.ORG_STN_KEY
> AND ( (SRTF.STN_KEY =
> FSG.DEST_STN_KEY) OR (SRTF.STN_KEY = 0) OR (SRTF.STN_KEY IS NULL AND
> SRTF.COUNTRY_KEY = (SELECT COUNTRY_KEY FROM STATION WHERE STN_KEY =
> FSG.DEST_STN_KEY)) )
> AND SR.AIRLINE_KEY = (SELECT
> AIRLINE_KEY FROM AIRLINE WHERE AIRLINE_CODE = SUBSTR(FSH.FLIGHT_NO, 1, 2) )
> )
> OR
> (
> SR.STN_KEY = 0
> AND ( (SRTF.STN_KEY =
> FSG.DEST_STN_KEY) OR (SRTF.STN_KEY = 0) OR (SRTF.STN_KEY IS NULL AND
> SRTF.COUNTRY_KEY = (SELECT COUNTRY_KEY FROM STATION WHERE STN_KEY =
> FSG.DEST_STN_KEY)) )
> AND SR.AIRLINE_KEY IN (
> (SELECT AIRLINE_KEY FROM AIRLINE WHERE AIRLINE_CODE = SUBSTR(FSH.FLIGHT_NO,
> 1, 2)) , 106 )
> )
> )
> AND (SR.VALID_FROM <=
> TRUNC(to_date('14-May-03'))+FSG.DEP_DAY_CHG AND SR.VALID_TO >=
> TRUNC(to_date('14-May-03'))+FSG.DEP_DAY_CHG)
> AND (SR.WEEKDAYS LIKE ('%' ||
> DECODE(TO_CHAR(TRUNC(to_date('14-May-03'))+FSG.DEP_DAY_CHG, 'D'), '1', '7',
> TO_NUMBER(TO_CHAR(TRUNC(to_date('14-May-03'))+FSG.DEP_DAY_CHG , 'D')) - 1 )
> || '%'))
> )
> OR
> (
> POS.POSITION_CODE IN ('I', 'TI')
> AND (
> (
> SR.STN_KEY =
> FSG.DEST_STN_KEY
> AND ( (SRTF.STN_KEY =
> FSG.ORG_STN_KEY) OR (SRTF.STN_KEY = 0) OR (SRTF.STN_KEY IS NULL AND
> SRTF.COUNTRY_KEY = (SELECT COUNTRY_KEY FROM STATION WHERE STN_KEY =
> FSG.ORG_STN_KEY)) )
> AND SR.AIRLINE_KEY = (SELECT
> AIRLINE_KEY FROM AIRLINE WHERE AIRLINE_CODE = SUBSTR(FSH.FLIGHT_NO, 1, 2) )
> )
> OR
> (
> SR.STN_KEY = 0
> AND ( (SRTF.STN_KEY =
> FSG.ORG_STN_KEY) OR (SRTF.STN_KEY = 0) OR (SRTF.STN_KEY IS NULL AND
> SRTF.COUNTRY_KEY = (SELECT COUNTRY_KEY FROM STATION WHERE STN_KEY =
> FSG.ORG_STN_KEY)) )
> AND SR.AIRLINE_KEY IN
> ((SELECT AIRLINE_KEY FROM AIRLINE WHERE AIRLINE_CODE = SUBSTR(FSH.FLIGHT_NO,
> 1, 2)) , 106 )
> )
> )
> AND (SR.VALID_FROM <=
> TRUNC(to_date('14-May-03')) + FSG.ARR_DAY_CHG AND SR.VALID_TO >=
> TRUNC(to_date('14-May-03')) + FSG.ARR_DAY_CHG )
> AND (SR.WEEKDAYS LIKE ('%' ||
> DECODE(TO_CHAR(TRUNC(to_date('14-May-03'))+FSG.ARR_DAY_CHG, 'D'), '1', '7',
> TO_NUMBER(TO_CHAR(TRUNC(to_date('14-May-03'))+FSG.ARR_DAY_CHG, 'D')) - 1 )
> || '%'))
> )
> )
> --AND (SR.WEEKDAYS LIKE '%3%')
> AND (
> (SR.RESTRICTED_POSITION = '*' AND
> SR.SERVICE_KEY = 5 )
> OR (SR.RESTRICTED_POSITION =
> POS.POSITION_CODE AND SR.SERVICE_KEY = 5 )
> OR (SR.RESTRICTED_POSITION = '*' AND
> SR.SERVICE_KEY = TOS.SERVICE_KEY )
> OR (SR.RESTRICTED_POSITION =
> POS.POSITION_CODE AND SR.SERVICE_KEY = TOS.SERVICE_KEY )
> )
> AND IS_ALLOWED = 'N'
> )
>
> Can anybody help me in imporving the insert performance? Any help in this
> regard is very much appreciated.
>
> Thanks and Regards,
>
> Ranganath
>
>
>
>
>
>
>
>
> WARNING: The information in this message is confidential and may be legally
> privileged. It is intended solely for the addressee. Access to this message
> by anyone else is unauthorised. If you are not the intended recipient, any
> disclosure, copying, or distribution of the message, or any action or
> omission taken by you in reliance on it, is prohibited and may be unlawful.
> Please immediately contact the sender if you have received this message in
> error. Thank you.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).