Streams SQL Apply Degrades with Large Transactions and Transformations

(Doc ID 453466.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.2]
Information in this document applies to any platform.


Streams' Apply performance is very slow;
It is taking a number of hours to process a transaction with 200K LCRs;
High CPU is also observed from the parallel query server involved in processing the transaction;
Any kind of large transaction on any table which has a transformation appears to be associated with the issue.

Periodically generating stack information from the p00* server which is generating high CPU shows:

ktaifm ktagetg ktagdw ktaadm kksfbc opiexe kpoal8 opiodr kpoodrc rpiswu2
kpoodr upirtrc kpurcsc kpuexecv8 kpuexec OCIStmtExecute knalspGetLcr
knalsglcr knaspx knalsptxn knasm2 knalsma kxfprdp opirip opidrv sou2o opimai_real main

The stack should be periodically obtained by using PSTACK , a debugger such as GDB or from
short_stack within sqlplus , ie

The functions on the stack which begin kta are involved in lock access.


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms