Dynamic performance views

SGA:

1. v$sgastat -- shows values of memory parameters n SGA.
SQL> select * from v$sgastat where pool = 'large pool';
POOL NAME BYTES
----------- -------------------------- ----------
large pool free memory 33554432

2. v$sga: Queries the size of instance for shared pool, large pool, Db buffer cache, fixed memory areas etc.

SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 741952
Variable Size 419430400
Database Buffers 805306368
Redo Buffers 5517312

3. v$instance: Queries the status of the instance
4. v$process and v$bgprocess: Queries server and background processes created for the instance.

SQL> desc v$bgprocess;
Name Null? Type
----------------------------------------- -------- ---------------------------- PADDR RAW(8)
NAME VARCHAR2(5)
DESCRIPTION VARCHAR2(64)
ERROR NUMBER
SQL> desc v$process;
Name Null? Type
----------------------------------------- -------- ---------------------------- ADDR RAW(8)
PID NUMBER
SPID VARCHAR2(12)
USERNAME VARCHAR2(15)
SERIAL# NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TRACEID VARCHAR2(255)
BACKGROUND VARCHAR2(1)
LATCHWAIT VARCHAR2(16)
LATCHSPIN VARCHAR2(16)
PGA_USED_MEM NUMBER
PGA_ALLOC_MEM NUMBER PGA_FREEABLE_MEM NUMBER PGA_MAX_MEM NUMBER

Archive log:

1. v$archive_dest: For current instance displays all archive log destinations including statuses etc-
SYS@DB01> SELECT destination, binding, target, statusFROM v$archive_dest; 2
DESTINATION
--------------------------------------------------------------------------------
BINDING TARGET STATUS
--------- ------- ---------
/u01/app/oracle/product/9.2.0.8/dbs/archMANDATORY PRIMARY VALID
OPTIONAL PRIMARY INACTIVE
OPTIONAL PRIMARY INACTIVE
DESTINATION
--------------------------------------------------------------------------------
BINDING TARGET STATUS
--------- ------- ---------
OPTIONAL PRIMARY INACTIVE
OPTIONAL PRIMARY INACTIVE
OPTIONAL PRIMARY INACTIVE

v$database: Current status of Archiving:
SYS@DB01> select NAME, LOG_MODE from v$database;
NAME LOG_MODE
--------- ------------
DB01 ARCHIVELOG
SYS@DB01>

V$archived_log: Displays archived log information from control file.

SYS@DB01> select * from v$archived_log;
RECID STAMP
---------- ----------
NAME
--------------------------------------------------------------------------------
DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS FIRST_CHANGE#
---------- ---------- ---------- ----------------- --------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTR STA ARC
--------- ------------ --------- ---------- ---------- ------- ------- --- ---
APP DEL S COMPLETIO DIC DIC END BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION#
--- --- - --------- --- --- --- ------------ ---------------- -----------
1
700221743/u01/app/oracle/product/9.2.0.8/dbs/arch1_18.dbf

v$archive_processes- Displays information of all the ARCH processes
SYS@DB01> select * from v$archive_processes;
PROCESS STATUS LOG_SEQUENCE STAT
---------- ---------- ------------ ----
0 ACTIVE 0 IDLE
1 ACTIVE 0 IDLE
2 STOPPED 0 IDLE
3 STOPPED 0 IDLE
4 STOPPED 0 IDLE
5 STOPPED 0 IDLE
6 STOPPED 0 IDLE
7 STOPPED 0 IDLE
8 STOPPED 0 IDLE
9 STOPPED 0 IDLE