I usually check SQL statement by AWR report:
SQL Statistics - SQL ordered by Elapsed Time or SQL ordered by CPU Time
I need to identify the hard parse sql , maybe having inappropriate plan or other reason that make it needs high cpu time or IO time. But hard parse SQL seems can't accumulate the statistics and show on AWR report, how can I check it?
I'd found SQL below to check hard parse from ASH, but always can't find the full sql text in v$sql(not exists).
select * from dba_hist_active_sess_history where IN_HARD_PARSE='Y'
select * from v$sql where sql_id=? --sql_id from the above result, almost no result in v$sql
Is there any other method or system view can check it?
