Is an Apply Server Performing Poorly for Certain Transactions?

SELECT p.OPERATION, p.OPTIONS, p.OBJECT_OWNER, p.OBJECT_NAME, p.COST
  FROM V$SQL_PLAN p, V$SESSION s, V$STREAMS_APPLY_SERVER a
  SEE CODE DEPOT FOR FULL SCRIPT
    AND s.SID = a.SID
    AND p.HASH_VALUE = s.SQL_HASH_VALUE;

This query shows the SQL operation that is currently being performed by the specified Apply server. The query also returns the owner and name of the table on which the operation is being performed and the cost of the operation. If the results show FULL for the COST column, the operation is causing full table scans, and indexing the table's key columns may solve the problem.

In addition, the following SQL query can be run to determine the specific DML or DDL SQL statement that is causing an Apply server to perform poorly.  Assume that the name of the Apply process is LN4_APPLY and that Apply server number two is performing poorly:

SELECT t.SQL_TEXT
  FROM V$SESSION s, V$SQLTEXT t, V$STREAMS_APPLY_SERVER a
  SEE CODE DEPOT FOR FULL SCRIPT
    ORDER BY PIECE;