Couldn’t find the long running SQL under v$session_longops

Why not use a join condition instead of an subquery

SELECT * from t_keyword 
INNER JOIN t_event event ON (t_keyword.id = rel.element_id) 
INNER JOIN t_event_element_rel rel ON event.id = rel.event_id 
WHERE event.domain_id=182 and event.event_type_cd=26 and event.event_create_date='2012-12-18'

Try creating a couple of temporary tables, indexing them, analyzing them, and then join to them..

CREATE TEMPORARY TABLE luid_lookup AS
  SELECT id, find_user(id) AS fuid FROM legacy_table;

CREATE TEMPORARY TABLE eoid_lookup AS
  SELECT external_object_id, find_external_object(external_object_id) AS feid FROM legacy_table;

CREATE INDEX luid_lookup_tmp_ix1 ON luid_lookup (id, fuid);
CREATE INDEX eoid_lookup_tmp_ix1 ON eoid_lookup (external_object_id, feid);

ANALYZE luid_lookup;
ANALYZE eoid_lookup;

And then

INSERT INTO interactions(
    user_id, external_object_id, origin, interaction_type, 
    interaction_sub_type, published_at, origin_id, created_at, updated_at)

SELECT b.fuid, c.feid, 'facebook' as origin,
    a.interaction_type, a.interaction_sub_type, a.published_at, a.origin_id, 
    a.current_date, a.current_date
from legacy_table a
join luid_lookup b on a.id = b.id
join eoid_lookup c on a.external_object_id = c.external_object_id

If the creation of the temporary tables takes a long time, then your find_user() and your find_external_object() methods need to be tuned (as opposed to the actual insert query).

One additional note .. if the id or external_object_id are NOT pkeys/unique of their tables, you will want to make sure that you're only getting unique values.. (If you don't, then you'll get duplicate rows when you join) In that case, make sure you do a subselect and get the distinct result (example below).

CREATE TEMPORARY TABLE luid_lookup AS
  SELECT id, find_user(id) AS fuid FROM (SELECT DISTINCT id FROM legacy_table) l_tab;