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
