2

I have a schema with some database links to other schemas that we use to pull data from. We pull the data into a staging table, massage it, and then do some merges into our schema/database.

The data in the linked schema is a little much to pull into test systems, so I want to only get the first 10000 records, which is easy enough.

select * from 
(
    SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
    FROM sradmin.RACEWAY@sr25 r
    LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

This returns very quickly as in under 2 seconds.

However when I try to insert this into my staging table, it causes SQL Developer to hang. Not a windows hang, the Script Output window just shows "ScriptRunner Task" with the moving bar back and forth showing that it's doing something.

INSERT INTO STG_TRANSIT(TRANSIT_ID, SHIP, TRANSIT_TYPE, COMPARTMENT_ID, DESCRIPTION)
select * from 
(
  SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
  FROM sradmin.RACEWAY@sr25 r
  LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

When it does this I can see that there are DW locks, but nothing else is using the linked databases that I'm trying to query.

Any help would be greatly appreciated as I am very much a novice with Oracle.

8
  • Could you check the alert.log for any issue? Also could you query v$lock and v$session_wait in join with v$process and v$session to check where query is waiting?
    – Ste
    Commented Jun 6, 2013 at 7:17
  • Have a look at the execution plans for differences, in particular for signs that the complete data sets for the two tables are being read over the db link and the join executed locally. Commented Jun 6, 2013 at 7:55
  • 1
    I would create a view encapsulating your query on the origin database and then query the view. That way you force the join and trimming to be done on the origin database
    – kevinskio
    Commented Jun 6, 2013 at 13:34
  • @Ste - There wasn't anything in the alert.log for my ip address. There are lots of "Fatal NI connect error 12170." however. I'm digging into the other thing you suggested.
    – sdanna
    Commented Jun 6, 2013 at 20:04
  • @Ste - This is the query I'm using. I'm not sure what to make of the data that comes back though in regards to checking where the query is waiting. Select * From V$lock L Join V$session_Wait Sw On L.Sid = Sw.Sid Join V$session S On L.Sid = S.Sid join v$process p on s.paddr = p.addr where block = 1;
    – sdanna
    Commented Jun 6, 2013 at 20:19

1 Answer 1

1

Based on your question and other discussion comments, I didn't see any clue for network and blocking issues.

Here, I'd like to give following approaches to try which can help in your situation:

Approach#1:

Suspecting performance issue because of indexes in STG_TRANSIT table. In the sutuation of big STG_TRANSIT table and there are indexes available, what if you might drop them and rebuild them after populating tables during off hours.

Approach#2:

Create temporary table -

create table temp_STG_TRANSIT 
as
select * from 
(
  SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
  FROM sradmin.RACEWAY@sr25 r
  LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

Perform bulk insert -

insert /* APPEND */ into STG_TRANSIT select a.* from temp_STG_TRANSIT as a;

Approach#3:

Trying with DRIVING_SITE hint which forces query execution to be done at a different site than the initiating instance, following query pattern can help in your context:

INSERT INTO STG_TRANSIT(TRANSIT_ID, SHIP, TRANSIT_TYPE, COMPARTMENT_ID, DESCRIPTION)
SELECT /*+ DRIVING_SITE(X) */ *
FROM
(
    SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
    FROM sradmin.RACEWAY@sr25 r
    LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
) X

Refer link for the details of DRIVING_SITE hint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.