Dynamic Performance View Related to SGA, PGA, SESSION, Process

Contents: SGA, PGA, SESSION, PROCESS, Archive Log Related Dynamic Performance Views.

Trace Files

Finding locations of trace files.

SQL > select value from v$diag_info where name='Default Trace File';

To find all trace files for the current instance

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

The path to the ADR trace directory for the current instance is returned.

To determine the trace file for each Oracle Database process:

SQL>SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

SGA

Desc V$sga_dynamic_components  

(is used to see the size of sga components like large pool, java pool, stream pool etc..)

Column component format a15

PGA

SQL> Select name,value from v$pgastat where name in ('max PGA allocated','total PGA allocated');

SESSION

SQL> Select program from v$session order by program;              

-- gives my particular session information.

PROCESS

SQL> Select program from v$process order by program;              

-- gives my particular process information.

Archive Log Dynamic Performance View

Sql> SELECT * FROM v$ARCHIVED_LOG;

Sql> SELECT * FROM v$ARCHIVE_DEST;

To Find Online (Archive Redo Log File)

SQL > select sequence#, member  from v$log l, v$logfile f  where f.group# = l.group#  order by 1;

V$ASM_CLIENT :-  To view ASM Clients ((any database instance,OCR file is called an ASM client).

Identify More CPU Time Consuming Statement (Time Consuming SQL Statement)

SELECT SQL_TEXT, EXECUTIONS

  FROM V$SQL

 WHERE CPU_TIME > 2000;

Identify Session login from the System. (Identify session login from the machine within last day)

SELECT *

  FROM V$SESSION

 WHERE MACHINE = 'LOCALHOST' AND TRUNC (LOGON_TIME) = TRUNC (SYSDATE) - 5;

Identify Locking Session.

SELECT SID,CTIME FROM V$LOCK WHERE BLOCK>0;

SGA Free Memory (To know SGA Free Memory)

SELECT POOL,ROUND(BYTES/1024/1024,0) FREE_MB FROM V$SGASTAT WHERE NAME LIKE '%free memory%';