Show Processlist for Oracle
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When your DBA is not around and you have access to SQL Plus or some other DB tool, but don't know how to extract a processlist from Oracle, specially when it involves getting statistics, like long running queries, execution time etc.
Why would you need it ?
The main benefit from using this query is that you, as an Atlassian application admin, having access to the application's database, would be able to monitor, from a database perspective, the application's performance.
It'll give you:
which queries are long running (in a troubleshooting scenario for poor performance, this is highly valuable information)
what is being run in the application's database (tables that are being queried/updated for debugging purposes)
how long does each query take (maybe an index is needed somewhere? Or a huge table that needs maintenance?)
Where does it apply ?
Performance troubleshooting cases.
Monitoring for daily activities, related administrative tasks executed by the application.
Improvement on database resources, if that's the case - (slow queries could be related to a undersized DB server instead of problems with the data themselves)
Seeing new ways for retrieving useful information from the database, for decision making purposes.
Environment
Oracle Database (Tested in 12c, should work fine for any other Oracle version, from 10g or higher)
SQL*Plus
You'll need your DBA to grant you access to query those Oracle's special objects. Good news is you only need SELECT privileges, as you'll only see data, not manipulate it on those V$ tables.
Solution
Run the following query on SQL Plus. You can run it on other DB tools, but then you'll need to execute it from the SELECT portion onward.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SET WRAP OFF
SET LINESIZE 400
SET PAGESIZE 1000
COLUMN PU FORMAT A8 HEADING 'O/S|LOGIN|ID'
COLUMN SU FORMAT A8 HEADING 'ORACLE|USER ID'
COLUMN STAT FORMAT A8 HEADING 'SESSION|STATUS'
COLUMN STATE FORMAT A7 HEADING 'SESSION|STATE'
COLUMN SPID FORMAT 999999 HEADING 'UNIX|PROCESS|ID'
COLUMN ETIME FORMAT 999999 HEADING 'EXEC TIME|IN SECONDS'
COLUMN TXT FORMAT A220 HEADING 'SQL QUERY'
SELECT P.USERNAME PU
, S.USERNAME SU
, S.STATUS STAT
, S.STATE STATE
, LPAD(P.SPID,7) SPID
, S.LAST_CALL_ET ETIME
, SUBSTR(SA.SQL_TEXT,1,540) TXT
FROM V$PROCESS P
, V$SESSION S
, V$SQLAREA SA
WHERE P.ADDR = S.PADDR
AND S.STATUS = 'ACTIVE'
AND S.USERNAME IS NOT NULL
AND S.SQL_ADDRESS = SA.ADDRESS(+)
AND S.SQL_HASH_VALUE = SA.HASH_VALUE(+)
AND S.USERNAME = 'CONFLUENCE'
ORDER BY 6 DESC ;It will produce an output such as below:
Was this helpful?