ORACLE DBA Usefull Basic performance related Queries

How to Finding oracle blocking objects:

SET PAGES 200
SET LINES 200

SELECT 
    (SELECT username FROM v$session WHERE sid = a.sid) AS blocker,
    a.sid,
    ' is blocking ' AS "IS BLOCKING",
    (SELECT username FROM v$session WHERE sid = b.sid) AS blockee,
    b.sid
FROM 
    v$lock a,
    v$lock b
WHERE 
    a.block = 1
    AND b.request > 0
    AND a.id1 = b.id1
    AND a.id2 = b.id2
/

How to find Table lock

SELECT 
    a.sid,
    a.serial#
FROM 
    v$session a,
    v$locked_object b,
    dba_objects c
WHERE 
    b.object_id = c.object_id
    AND a.sid = b.session_id
    AND c.object_name = 'Table_Name'
/

How to identify Client IDENTIFIED in particular session:

SET LINES 200
SET PAGES 200

COL USERNAME FOR a10
COL MODULE FOR a25
COL ACTION FOR a25
COL PROGRAM FOR a18
COL CLIENT_IDENTIFIER FOR a15

SELECT 
    sid,
    serial#,
    USERNAME,
    CLIENT_IDENTIFIER,
    status,
    LAST_CALL_ET / 60 AS last_call_minutes,
    SQL_HASH_VALUE,
    module,
    action,
    program
FROM 
    v$session
WHERE 
    sid = '&n'
/

How to identify SQL_HASH_VALUE in SID:

SET LINES 200
SET PAGES 200

COL MODULE FOR a30
COL ACTION FOR a35
COL PROGRAM FOR a18

SELECT 
    sid,
    serial#,
    status,
    LAST_CALL_ET / 60 AS last_call_minutes,
    SQL_HASH_VALUE,
    module,
    action,
    program
FROM 
    v$session
WHERE 
    sid = '&n'
/

How to find SQL TEXT:

select sql_text from v$sql where hash_value='HASH_VALUE'; 

How to get pid from sid?

SELECT 
    b.spid,
    a.sid,
    a.serial#,
    a.username,
    a.osuser
FROM 
    v$session a,
    v$process b
WHERE 
    a.paddr = b.addr
    AND b.spid = '6514'

How to find sqlid?

select * from v$session where sid='749'

  SELECT 
    a.sid,
    a.program,
    b.sql_text
FROM 
    v$session a,
    v$sqltext b
WHERE 
    a.sql_hash_value = b.hash_value
    AND a.sid = '749'
ORDER BY 
    a.sid,
    hash_value,
    piece;

How to kill the particular session:

  ALTER SYSTEM KILL SESSION 'sid,serial#';

Finding Long Running queries

 SELECT
    sesion.sid,
    sesion.username,
    optimizer_mode,
    hash_value,
    address,
    cpu_time,
    elapsed_time,
    sql_text
FROM
    v$sqlarea sqlarea,
    v$session sesion
WHERE
    sesion.sql_hash_value = sqlarea.hash_value
    AND sesion.sql_address = sqlarea.address
    AND sesion.username IS NOT NULL;

How to find query progressing/not

User below query and give SID and execute 2 time if values are changing query is progressing

 SELECT 
    b.name,
    a.value AS vlu
FROM 
    v$sesstat a,
    v$statname b
WHERE 
    a.statistic# = b.statistic#
    AND sid = &sid
    AND a.value != 0
    AND b.name LIKE '%row%';

 Active User Session and Executing SQL Details

  SELECT s.sid, s.serial#, q.sql_id, q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
  AND s.username = 'APPS'; -- or your specific schema