this might help. It lists the SQL statements from the ASH history tables (remember, ASH needs licencing!) and shows the times that the snapshots were running:
dhss.snap_id = sn.snap_id
sq.sql_text like ‘SELECT COUNT%’
sq.sql_id = dhss.sql_id;
I’m only interested in SELECT COUNT statements here, for example, your checks might vary.
The results look a little like:
SQL_ID EXECUTIONS_DELTA BEGIN_INTERVAL_TIME END_INTERVAL_TIME SQL_TEXT
96fqh1b19dh67 1 18-OCT-17 13.12.55.000 18-OCT-17 220.127.116.113 SELECT COUNT(DISTINCT(batch#)) FROM sqllog$
760kzp1j1b8ds 1 18-OCT-17 13.12.55.000 18-OCT-17 18.104.22.1683 SELECT COUNT(*) FROM (SELECT FILETYPE_ID, FILETYPE_NAME FROM V$IOSTAT_FILE MINUS
96fqh1b19dh67 1 18-OCT-17 15.32.36.000 18-OCT-17 22.214.171.1246 SELECT COUNT(DISTINCT(batch#)) FROM sqllog$
Yes, I know, it’s been a long while since I was last on this database!
You can see part of the SQL text and the ASH snapshot times when the statement was known to have been running. If you know the SQL_ID, you can search for that, rather then trying to find the SQL text. You should note that the full text of the SQL is not necessarily going to be present in the DBA_HIST_SQLTEXT table.