One of our customers is seeing CPU spikes on the database machine
We were able to isolate it to one specific query
SELECT /* +LEADING(s pstamp acct) */ DISTINCT acct.island_anchor_uid
FROM SUB_TABLE subscr,P_TIMESTAMP pts,ACCT_TABLE acct
WHERE subscr.app_id = ':FTS_SYNC' AND pts.pdbts > subscr.lpd
AND pts.pdeleted = 0
AND acct.exp_obj_uid = pts.puid
AND acct.app_id = subscr.app_id
AND acct.state = '2'
AND pts.pdbts > acct.led;
The tables involved are small (from a few rows in subscription table to about 30,000 rows in ACCT_TABLE and 91,000 rows in the P_TIMESTAMP table)
What is also interesting is that there seems to be an inflexion point beyond which the spikes are seen. The same query was being executed without issues for an hour and after that, it started showing the spikes. We were also able to reproduce the spike by stopping the sync and manually executing the query.