DBA

I rely on dba_hist_sql_plan for me to track query performance for each sql_id, however, this time the query plan hash value shows in v$sql, but not recorded in dba_hist_sql_plan, though AWR snapshots are take every 1 hour. When I open myOracle support ticket, the support engineer told me that " The PHV 1779814119 was first loaded on 2023-08-20/08:36:37, and it's still the current execution plan, while PHV 521264029 was historically used and appears in the view dba_hist_sql_plan Once there is a new current PHV different than 1779814119 , you will find this in the historical sql plans when it's flushed by the mmon slaves as part of the AWR activities " I think that's a wrong statement. ChatGPT tells me that topNsql of AWR might be the answer, googling on topNsql results in a few findings such as this one below. I did not test it yet because I do not have DBA permission on the database, so in theory this could be the issue.  https://ironmandba.wordpress.com/modifying...

I use these query to get APEX meta information about APEX version, workspace, application, pages, and table/queries the page are using.  col comp_name format a30 col procedure format a20 col username format a30 col proxy format a30 col client format a30 col APPLICATION_ID  heading "APP_ID" format 9999 select comp_name,version,schema,procedure from dba_registry where comp_name like '%APEX%'; select username,created from dba_users where username like '%APEX%'; select owner,count(*) from dba_objects where owner like 'APEX%' group by owner; select owner,object_type,count(*) from dba_objects where owner like 'APEX_LISTENER'  group by owner,object_type; --select synonym_name,TABLE_OWNER,TABLE_NAME from dba_synonyms where OWNER='APEX_LISTENER'; select * from proxy_users where proxy like '%ORDS%' or proxy like '%APEX%'; -- list workspace and applications SELECT    --  w.workspace_id,     w.workspace,     a.application_id,     a....