For a query that returns if there are any active transactions within current session ,
SELECT COUNT(*) FROM v$transaction t INNER JOIN v$session s ON (t.ses_addr = s.saddr ) INNER JOIN v$mystat m ON (s.sid = m.sid ) WHERE ROWNUM = 1;
EXPLAIN shows 0 cost. However, that doesn't seem to be true. Periodically running this query in high load environment causes server to waste almost all its resources on it.
What is the right way to estimate impact of such queries (I believe the same problem occurs not for just this particular query, but anything that involves system views)?