what's the relation between v$view and dba_hist*?
484696Aug 19 2010 — edited Aug 20 2010Today, I have ａ problem:
I ran a sql, and can be check in the v$sql, but I can't search it in the dba_hist_sqltext. why?
sample:
1. run a sql:
anmh@MYORACLE> select count(*) from a2;
2.check in v$sql:
sys@MYORACLE> select sql_id,sql_text
from v$sql
where sql_text like '%a2%'
order by LAST_LOAD_TIME
sql_id sql_text
880yywbu8kg7z select count(*) from a2
3.create snapshot:
esys@MYORACLE> select to_char(sysdate,'yyyymmdd hh24miss') from dual;
TO_CHAR(SYSDATE
---------------
20100819 143726
sys@MYORACLE> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
sys@MYORACLE> l
1* select to_char(sysdate,'yyyymmdd hh24miss') from dual
sys@MYORACLE> select snap_id, BEGIN_INTERVAL_TIME from dba_hist_snapshot order by 1 desc ;
SNAP_ID BEGIN_INTERVAL_TIME
12868 19-AUG-10 02.35.06.528 PM
4. check in dba_hist_sqltext:
sys@MYORACLE> select sql_id,sql_text from dba_hist_sqltext where sql_id='880yywbu8kg7z';
no rows selected
what 's the problem? thanks