Periodically there would be cursor: pin S wait on X spikes on one of our reporting databases which resulted in 20+ Average Active Sessions on some SQLs that didn’t seem to originate from any of our code. Looking a little deeper at them:

SQL> @stext
Enter value for sql_id: 0ah5yq1sbn2b1

--------------
SELECT /*+ FULL(P) +*/ * FROM "OWNER"."TABLE_NAME" P

There were strange looking + FULL(P) + hint-like hints. While trying to track some information about them, nothing in MOS, but ran across a good post from Jonathan Lewis here.

“As part of the optimizatin strategy in recent versions of Oracle the initiating database sends queries about the remote tables to the remote database(s) – which don’t RUN the queries, but do optimise them. I suspect this is just the mechanism by which the local database gets the remote(s) to supply the object statistics of the remote objects.” — Jonathan Lewis

I opened an SR to confirm and these “hints” indeed originate from an optimization step triggered from calls through a DB Link. We’d also fell into the following bug:

Bug : 18693124 Abstract : High version_count for SQL with remote PL/SQL operand Affected Rel : 11.2.0.3 / 11.2.0.4 / 12.1.0.2

The fix is to either apply a patch or set event 22827 on the target and source databases which moves the code path back to the Kernel Generic LIbrary (KGL). Obviously if you stumble into something like this you’ll have to verify with Support before going crazy, but I learned something new.