Oracle Application Performance bottleneck idenfiication Queries

The Performance of application can caused due to many reasons either in Application itself or Database side as well.

Especially the performance surely decrease when there is bottleneck left behind while writing the program without considering and keeping in mind that this program may face the Bulk data plugged in future to the application/ report.

That's why the following queries may help to see each area to make sure every things is in order before considering change to the program behavior to handle the bulk data.

--To see is there any invalid objects
select * from user_objects where status ='INVALID';

/
--To Check the DBA Tablespace
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
/

--To get the detail for long operations which is not completed and running.
select * from v$session_longops where totalwork <>sofar
/

--- To see is there any LOCK in place due to report.

SELECT s.sid,s.serial#,username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id
/

--To see is there any DBA_WAITERS, DBA_BLOCKERS

select * from dba_waiters
/
select * from dba_blockers
/
--========================================================--
--To see the Undo Retention.
select * from v$parameter where name like '%undo%'
/

--to get the process detail for each session running the queries
select a.process, a.program, a.module, a.machine, b.USED_UREC, c.sql_text
from v$sql c, v$session a, v$transaction b
where b.addr = a.taddr
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value
order by b.USED_UREC
/

--to check what is the undo table space

SELECT distinct rpad(s.sid,3) "SID",S.USERNAME,
E.SEGMENT_NAME,
T.START_TIME "Start",
rpad(T.STATUS,9) "Status",
round((t.used_ublk*8)/1024) "Size(MB)"
--T.USED_UBLK||' Blocks and '||T.USED_UREC||' Records' "Rollback Usage"
FROM DBA_DATA_FILES DF,
DBA_EXTENTS E,
V$SESSION S,
V$TRANSACTION T
WHERE DF.TABLESPACE_NAME = E.TABLESPACE_NAME AND
DF.FILE_ID = UBAFIL AND
S.SADDR = T.SES_ADDR AND
T.UBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID+E.BLOCKS AND
E.SEGMENT_TYPE in( 'ROLLBACK','TYPE2 UNDO')
/