要約

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. 

環境

• 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.

ソリューション

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.

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: