Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Jun 6 '13 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. –  David Aldridge Jun 6 '13 at 7:55
    
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 –  kevinsky Jun 6 '13 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 Jun 6 '13 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 Jun 6 '13 at 20:19

1 Answer 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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