Friday, 6 October 2017

Problem on a SELECT through a db link

Not my adventure entirely, yet interesting.
There exists a query in the shape INSERT ... SEELECT. SELECT is done on the view. The view returns huge set of data (if called without conditions). But there is a condition, thus the whole query in normal circumstances should be executed in a fraction of a second.
But yesterday out of nothing the plan changed. It changed several times in the past, but then it was enough to inject through a sql profile the hint optimizer_features_enable('10.2.0.4') to smooth things out. So developers injected it in a hard way - to the code itself. But yesterday the plan changed again despite the hint, and the change was based on the full view generation and remote site trying to push whole that mass of data back to the local site to filter through the condition - the local database waited on TCP Socket wait event, waiting for any data, while the remote database burnt CPU trying to produce full view set (which was so huge effort that it completely choked). And any action on the 'local' side did not fix the problem.
After some time spend on struggling with the issue the final solution was as follows:
  • on the local site - optimizer_features_enable('10.2.0.4') and rule injected by sql profile
  • on remote site - optimizer_features_enable('10.2.0.4') and rule injected by sql profile to the view
It is clear that rule and optimizer_features_enable are exclusive - on the Metalink though somewhere was a note that OPAQUE_TRANSFORM may be disabled by an event or optimizer_features_enable or rule hints. My guess is the rule hint now disabled OPAQUE_TRANSFORM (which is passed by default), which allowed for the plan control on the remote site and there optimizer_features_enable('10.2.0.4') was used.

No comments: