SQL Statements Performed Across Database Links run Slowly. Explain Plan Shows Function SYS_OP_C2C has been Applied to Predicates, and Query uses a Full Table Scan. (Doc ID 2010872.1)

Last updated on FEBRUARY 21, 2024

Applies to:

Symptoms

• Insert using dblink is slow.  The insert inserts to remote table, selecting from local tables with subselect referencing remote table:
  INSERT INTO Test@DBLINK
  (SELECT ...
  FROM
  (LOCAL TABLES)
  WHERE ...
  AND NOT EXISTS (select 'X'
  from Test@DBLINK ...
  where TIME.EMPLID = JOB.EMPLID
  and TIME.DUR = '27-APR-15')) 
    
   
• The recursive sql for the insert statement (which sends back recursive select statement from remote table which pulls data from local) is taking most time using function SYS_OP_C2C which uses a full table scan:
  SELECT "EMPLID","STATUS"..."LASTUPDDTTM"
  FROM
  "Test" "A1" WHERE "EMPLID"=:1...

  call count cpu elapsed disk query current rows
  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  Parse 1 0.00 0.00 0 0 0 0
  Execute 39 0.01 0.01 0 1 0 0
  Fetch 78 185.01 1000.40 7158599 7629332 0 39
  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  total 118 185.02 1000.41 7158599 7629333 0 39

  Plan hash value: 1862217533

  -----------------------------------------------------------------------------------
  | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
  -----------------------------------------------------------------------------------
  |* 1 | TABLE ACCESS FULL| Test | 1 | 41 | 14635 (1)| 00:01:34 |
  -----------------------------------------------------------------------------------
  ...
  1 - filter((SYS_OP_C2C("EMPLID")=:1<======filter operation occurring
  AND SYS_OP_C2C("STATUS")=:5)

    
   
• The local and remote databases have different NLS setting

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.