Do you have Toad? Run it through a tuning lab.

Why select from the mam_assets table twice for a.id?

Code:
FROM MAM_ASSETS A
 11  WHERE ((A.ACL_ID=65536) AND
 12  (A.ASSET_GROUP_XID=120) AND
 13  (A.IS_CURRENT_VERSION=1) AND
 14  A.ID IN ( SELECT A.ID ASSET_ID FROM MAM_ASSETS A
 15             WHERE (A.ID IN (SELECT SV65539.ID FROM MAM_ATTR_STRING_VALUES SV65539