Streams SQL Apply Degrades with Large Transactions and Transformations
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.
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
Million Knowledge Articles and hundreds of Community platforms