I have sql hash value of a query.
I don't have the sqltext, plan hash or sql_id - only the hash value of the sql.
When I'm querying the v$sql view there are no result (I guess the sql is not in the memory already):
select * from v$sql where hash_value = 'hv_Example';
Is there a way to achieve the sql_id from the hash_value?
I didn't found a view that holds the hash value and the sql_id togather...
V$SQL contains both HASH_VALUE and SQL_ID, but not forever.
According to Tanel Poder's blog, the HASH_VALUE can be derived from the SQL_ID, but the inverse operation is not possible. But converting HASH_VALUE to SQL_ID wouldn't solve your problem anyway.
First of all, I'm not sure why you would have access to the HASH_VALUE but not the SQL_ID. The SQL_ID is much more commonly used for performance tuning. Whatever program or source is providing the HASH_VALUE should be modified to return the SQL_ID instead.
But the real problem here, whichever value you have, is that data is not permanently stored in V$SQL. As statements get old they are aged out of the shared pool, and disappear from V$SQL. Also, if you're using a clustered system, make sure you're using GV$SQL.
If you are using Enterprise Edition, and have licensed the Oracle Diagnostic Pack, you may be able to find historical SQL_ID values with one of these two statements:
select sql_id
from gv$active_session_history
where sql_full_plan_hash_value = 'XYZ';
select sql_id
from dba_hist_active_sess_history
where sql_full_plan_hash_value = 'XYZ';
Data in GV$ACTIVE_SESSION_HISTORY typically lasts for about a day. Data in DBA_HIST_* stays for 8 days by default, but is configurable. You can check the AWR retention with this query: select retention from dba_hist_wr_control;
If the HASH_VALUE is less old than the retention period, but still not in AWR, then you can probably ignore it. ASH and AWR use sampling, which means they do not capture everything that happens in a database. ASH takes a sample every second and AWR takes a sample every 10 seconds.
A lot of people struggle with this concept but it's helpful to come to terms with the idea of sampling. Systems that capture all of the information, like tracing everything, are overwhelming and use up too much space and processing power. If we're looking at performance problems then we do not care about something that doesn't happen for more than 10 seconds out of 8 days.