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 10.2.0.2 to 10.2.0.3 [Release 10.2]
Information in this document applies to any platform.

Symptoms

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.

Cause

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