How do we find the longest running queries in an Oracle database
The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.
Certifications:
Certifications:
Latest posts by Stratos Matzouranis (see all)
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
In the Oracle databases we have the possibility to find statistical data for the queries by making use of the reports ASH and AWR. But to activate this feature we must have Enterprise version. In Standard we can install the package Statspack which provides us with some information but in this article we will see how we can obtain information about the sql queries that are cached through the dynamic view v$SQL.
In particular, we will see a query with which we can obtain information about cached queries based on the criteria of when they were last run, the time they lasted and the user who executed them.
What information can we find through the query in v$SQL?
With this query we can see the following:
- The SQL Text
- The last time he ran
- How many times has it run until it was loaded into the cache
- Which user ran it
- How long was it in total?
The code
select
sql_text,
sql_fulltext,
sql_id,
last_active_time,
executions,
PARSING_SCHEMA_NAME,
round((elapsed_time/1000000),0) Duration_in_Seconds,
round((elapsed_time/1000000/60),0) Duration_in_Minutes
from v$sql
where 1=1
and last_active_time > TO_DATE('2021-10-01 01:00:00', 'YYYY-MM-DD HH-MI-SS')
and elapsed_time/1000000 > 0 --duration in seconds
and upper(service) = (select upper(value) from v$parameter where name like '%service_name%')
and parsing_schema_name not in ('SYS','SYSMAN','AUDSYS')
and executions > 0
--and parsing_schema_name ='STRATOS'
order by elapsed_time desc;
The results it will return will be as we see indicatively in the image below:
