10g: Current plan

SELECT sql_id, operation,options,object_owner,object_name,object_alias "Query Block", optimizer, to_CHAR(timestamp, 'DD/MM/YYYY HH24:MI:SS')
   FROM v$sql_plan
   WHERE sql_id in (select sql_id from v$sqlarea WHERE sql_text like '%SELECT COUNT (*) FROM USRNAME where ID=%';)
   order by timestamp,id
/

10g: Plan from history

SELECT sql_id, operation,options,object_owner,object_name,object_alias "Query Block", optimizer, to_CHAR(timestamp, 'DD/MM/YYYY HH24:MI:SS')
   FROM DBA_HIST_SQL_PLAN
   WHERE sql_id in (select sql_id from v$sqlarea WHERE DBA_HIST_SQLTEXT like '%SELECT COUNT (*) FROM USRNAME where ID=%';)
   order by timestamp,id
/

9i: Current plan

SELECT operation,options,object_owner,object_name
FROM v$sql_plan
WHERE hash_value in (SELECT hash_value FROM v$sqlarea WHERE sql_text like '%SELECT eFolderID,eActionName,eWaitTime,ePriority FROM eWait WHERE eWaitTime <= to_date%')
order by hash_value, id
/

9i: Plan from history (Statspack)

SELECT operation,options,object_owner,object_name
FROM stats$sql_plan
WHERE hash_value in (SELECT hash_value FROM v$sqlarea WHERE sql_text like '%SELECT eFolderID,eActionName,eWaitTime,ePriority FROM eWait WHERE eWaitTime <= to_date%')
order by hash_value, id
/