Oracle DB SQLs – DBA Queries – Performance, Top 10, Locks, Long Pending Jobs, etc.

 

Usage:

A few Miscellaneous SQLs for multiple DB Health Check Tasks:

1.    SQLs taking maximum resources

2.    Identify long running programs

3.    Identify database locks that is waiting for resolution

4.    Query executed from a single schema/session

5.    Track a single user or a session

 

SQLs:

Top 20 SQLs in Buffer:

 

-- Top Disk Reads, Reads per Execution

 

SELECT * FROM

    (SELECT SQA.SQL_TEXT SQL_TEXT

        ,TRUNC (SQA.DISK_READS / DECODE (SQA.EXECUTIONS, 0, 1, SQA.EXECUTIONS)

    ) READS_PER_EXECUTION

,SQA.BUFFER_GETS, SQA.DISK_READS, SQA.EXECUTIONS, SQA.SORTS, SQA.ADDRESS

FROM V$SQLAREA SQA

ORDER BY 2 DESC)

WHERE ROWNUM <= 21;

 

Top SQLs by Disk Reads (Secondary Memory Usage):

 

-- Top Disk Reads per Execution

 

SELECT SUBSTR (SQA.SQL_TEXT, 1, 200) SQL_TEXT

        ,TRUNC (SQA.DISK_READS / DECODE (SQA.EXECUTIONS, 0, 1, SQA.EXECUTIONS)

    ) READS_PER_EXECUTION

    ,SQA.BUFFER_GETS, SQA.DISK_READS, SQA.EXECUTIONS, SQA.SORTS, SQA.ADDRESS

FROM V$SQLAREA SQA

WHERE ROWNUM <= 21

ORDER BY 2 DESC;

 

 

Currently Active Sessions, SQLs:

-- Currently Active/Running Sessions, SQLs

 

SELECT SS.USERNAME, SS.SID, SS.OSUSER, SQT.SQL_ID, SQL_TEXT

FROM V$SQLTEXT_WITH_NEWLINES SQT,V$SESSION SS

WHERE SQT.ADDRESS = SS.SQL_ADDRESS

AND SQT.HASH_VALUE = SS.SQL_HASH_VALUE

AND SS.STATUS = 'ACTIVE'

AND SS.USERNAME <> 'SYSTEM'

ORDER BY SS.SID, SQT.PIECE;

 

Currently Active Database Locks:

 

-- Currently Active Database Locks - Waiting for at least 7777 seconds

 

SELECT OBJ.OBJECT_NAME, OBJ.OBJECT_TYPE, LKO.SESSION_ID,

  LK.TYPE,         -- TYPE OR SYSTEM/USER LOCK

  LK.LMODE,        -- LOCK MODE IN WHICH SESSION HOLDS LOCK

  LK.REQUEST, LK.BLOCK, LK.CTIME WAIT_DURATION         -- TIME SINCE CURRENT MODE WAS GRANTED

FROM V$LOCKED_OBJECT LKO, ALL_OBJECTS OBJ, V$LOCK LK

WHERE LKO.OBJECT_ID = OBJ.OBJECT_ID

  AND LK.ID1 = OBJ.OBJECT_ID

  AND LK.SID = LKO.SESSION_ID

  AND LK.CTIME > 7777

ORDER BY LK.CTIME DESC, LKO.SESSION_ID, OBJ.OBJECT_NAME;

 

 

Long Running DB Operations, Costly SQLs

--  Long Running DB Operations, Costly SQLs

 

SELECT SID, TO_CHAR(START_TIME,'HH24:MI:SS') STIME,

    MESSAGE,( SOFAR/TOTALWORK)* 100 PERCENT

FROM V$SESSION_LONGOPS

WHERE SOFAR/TOTALWORK < 1;

 

 

Long Running SQLs, currently in progress:

-- Queries that are Currently Running for more than 60 Seconds

 

SELECT SS.USERNAME, SS.SID, SS.SERIAL#, SS.LAST_CALL_ET/60 MINS_RUNNING, SQT.SQL_TEXT

FROM V$SESSION SS, V$SQLTEXT_WITH_NEWLINES SQT

WHERE  SS.SQL_ADDRESS = SQT.ADDRESS

AND SS.STATUS = 'ACTIVE'

AND SS.TYPE <> 'BACKGROUND'

AND SS.LAST_CALL_ET > 60

ORDER BY SID, SERIAL#, SQT.PIECE;

 

 

Identify a Single Problem SQL, Troublshoot:

Step 1:

-- Steps - Identify a slow SQL, get details for troubleshooting

-- 1. GET SQLS

 

SELECT

    SS.SID, SS.SERIAL#, SS.USERNAME,

    SS.OSUSER, TRX.START_TIME,

    PRC.SPID, SS.STATUS, SS.MACHINE,

    SQA.ACTION, SQA.MODULE, SS.PROGRAM

FROM

    V$SESSION SS, V$PROCESS PRC, V$TRANSACTION TRX,

    V$SQLAREA SQA

WHERE SS.PADDR = PRC.ADDR

AND SS.SADDR = TRX.SES_ADDR

AND SS.SQL_ADDRESS = SQA.ADDRESS (+)

AND TO_DATE(TRX.START_TIME,'MM/DD/YY HH24:MI:SS') <= SYSDATE - (15/1440) -- RUNNING FOR 15 MINUTES

ORDER BY TRX.START_TIME;

 

Step 2:

-- 2. GET SQL ADDRESS - ENTER THE SID VALUE

-- 3429    5977    00    INACTIVE    0000001B08BA4A78

 

SELECT SID, SERIAL#,SQL_ADDRESS, STATUS,PREV_SQL_ADDR FROM V$SESSION WHERE SID='3065';

 

 

Step 3:

-- 3. GET Full SQL

 

SELECT PIECE, SQL_TEXT FROM V$SQLTEXT X WHERE ADDRESS='10000009AB4EF340'

ORDER BY PIECE ASC;

 

 

 

 

Keywords:

Oracle DB, EBS, DBA, Performance Tuning, Optimization, PL/SQL, Top 10 SQLs, Query, SQL, SQLPLUS, Memory, Cards, Cost, V$SESSION, V$SQLAREA, V$SQLTEXT

Common Queries: AP, PO, Requisitions, Purchasing

 

Introduction:

Some common SQLs for Oracle Applications EBS Purchasing Module.

I am using Release 12.2.4. But these SQLs should be applicable for most of the versions.

 

SQLs:

Get Notifications associated with a PO Number:

SELECT NOTIFICATION_ID, RECIPIENT_ROLE, FROM_ROLE, USER_KEY, ITEM_KEY, MESSAGE_TYPE, MESSAGE_NAME, STATUS, BEGIN_DATE, RESPONDER, FROM_USER, TO_USER, SUBJECT
FROM WF_NOTIFICATIONS WN
WHERE MESSAGE_TYPE = 'POAPPRV'
AND USER_KEY = '1047424';
 

Notification Items associated with a Single User:

SELECT WN.*, WA.*
FROM WF_NOTIFICATIONS WN, WF_ACTIVITIES WA
WHERE WN.MESSAGE_NAME = WA.MESSAGE
--AND WN.SENT_DATE > SYSDATE-99
AND WN.STATUS NOT IN ('CANCELED', 'CLOSED')
AND LENGTH(WA.RESULT_TYPE) > 1
AND WA.END_DATE IS NULL
AND WN.RECIPIENT_ROLE = '<USERNAME>';

 

Add dates, if the requirement is to get only specific duration notifications. Also add these two statements, if required:

LENGTH(WA.RESULT_TYPE) > 1     ==>> OPEN, TO DO (NEEDS ACTION)

WA.RESULT_TYPE = '*'        ==>> OPEN, FYI

 

Notification Status à WN.STATUS

OPEN - NOT VIEWED EVEN ONCE - CAN BE FYI OR RESPONSED

CLOSED - VIEWED AT LEAST ONCE

CANCELLED - NO MORE ACTIVE (MOST LIKELY TIMED OUT/ EXPIRED)

 

Some common Work Flow Item Types:

PORCPT    PO_RCV_BUYER_NON_RECEIPT_JRAD

PORPOCHA    NEW_PO_CHANGE_JRAD

PORCPT        PO CONFIRM RECEIPT

PORPOCHA        PO CHANGE APPROVAL FOR REQUESTER

APCCARD    OIE_DUNNING_NOTIFICATIONS_1

POAPPRV    PO_PO_HAS_BEEN_APPROVE

APCCARD        CREDIT CARDS

POAPPRV        PO APPROVAL

 

All Notifications to All Users:

SELECT WN.RECIPIENT_ROLE, COUNT(*)
FROM WF_NOTIFICATIONS WN, WF_ACTIVITIES WA
WHERE WN.MESSAGE_NAME = WA.MESSAGE
AND WN.SENT_DATE > SYSDATE-30
AND WN.STATUS NOT IN ('CANCELED', 'CLOSED')
AND WN.RECIPIENT_ROLE NOT IN ('SYSADMIN')
AND WN.RECIPIENT_ROLE NOT LIKE '%|%'
AND WN.RECIPIENT_ROLE NOT LIKE '%:%'
AND LENGTH(WA.RESULT_TYPE) > 1
--AND ROWNUM < 100
AND WA.END_DATE IS NULL
GROUP BY WN.RECIPIENT_ROLE
HAVING COUNT(*) >= 2
ORDER BY 2 DESC;
 

All Requisition Header and Line Details:

SELECT *
FROM PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRH.CREATION_DATE BETWEEN SYSDATE-11 AND SYSDATE
AND ROWNUM < 21;

 

All Purchase Order Header and Line Details:

SELECT POH.*, POL.*
FROM PO_HEADERS_ALL POH, PO_LINES_ALL POL
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.CREATION_DATE BETWEEN SYSDATE-11 AND SYSDATE-4
AND ROWNUM < 21;

 

Keywords:

Oracle EBS, PO, AP, Purchasing, Account Payables, SQL, Query, sqlplus