Oracle Query Execution Time – How to Find Execution Time of Old Queries

oracleoracle-11g

I have a delete query which was run on a table in Oracle 11g Enterprise Edition about 2 months back. I need to find the exact time of when the query was executed.

I found the query in the DBA_HIST_SQLTEXT view. But I do not see any date/time field in the table to find the exact time of execution. Is there any way that this can be found out?

I also tried to extract the same from AWR report. But snapshots are available only till 1.5 months back.

Best Answer

The typical retention period for an AWR report is 45 days. So unless, this period has been modified to 60 days (2 months) or more in your DB, the DBA_HIST* views will not have the information you are looking for. You can verify the retention period by querying the DBA_HIST_WR_CONTROL view. In case the retention period has not been modified you can query the DBA_HIST_ACTIVE_SESS_HISTORY view and get SQL_EXEC_START time for the SQL_ID for a given period.

Related Solutions

Oracle: terrible execution times from virtual application server

This is almost certainly a networking issue (as the execution on a different machine is way faster) so I doubt you'll be able to go much further with any Oracle tools. I've never had any needs to tweak out-of-the-box network parameters to get good performance.

What I suggest is you use a standard network benchmarking tool, e.g. iPerf. It needs to be cross-platform since you have a mixed Linux/Windows environment. Keep in mind that the issue could be due to either low bandwidth or high latency. Run the same test from the other client from which you're getting good SQL Plus results, compare and if it's massively different send the evidence to your networking admin guys.

By the way, if you still need more advice I suggest you post to https://serverfault.com/ as you'll likely to reach more experienced network admins there.

How to know when a query was executed

select LAST_LOAD_TIME, LAST_ACTIVE_TIME 
from v$sql
where sql_id = Your SQL ID

Hope this will give you the expected result

Best Answer

Related Solutions

Oracle: terrible execution times from virtual application server

How to know when a query was executed

Related Question