A detailed view of Oracle's common dynamic performance

First look at when to use dynamic performance view:

(1) Nomount

When you start the routine, Oracle opens the parameter file, assigns the SGA, and starts the background process. Therefore, when the routine is in the Nomount state, only access to the dynamic performance view of the information obtained from the SGA is accessible.

(2) MOUNT

When installed in a database, Oracle opens all control files based on initialization parameters control_files. When the routine is in the Mount state, not only can the dynamic performance view of the information from the SGA be accessed, but also the dynamic performance view of obtaining information from the control file can be accessed.

(3) OPEN

You can access the data dictionary view only when you are in the open state.

The following are common dynamic performance views:

1, v$fixed_table

Used to list all available dynamic performance views and dynamic performance tables

05:06:34 sql> select name from v$fixed_table where name like ' v$% ';

NAME

------------------------------

V$waitstat

V$bh

V$gc_element

V$cr_block_server

V$current_block_server

V$gc_elements_with_collisions

V$file_cache_transfer

V$temp_cache_transfer

V$class_cache_transfer

V$instance_cache_transfer

V$lock_element

v$bsp

V$locks_with_collisions

V$file_ping

V$temp_ping

V$class_ping

2, V$instance

To get the details of the current routine

05:14:56 sql> Col host_name for A20

05:15:08 sql> R

1* Select instance_name, host_name,status from v$instance

instance_name host_name STATUS

---------------- -------------------- ------------

ORCL Oraserv OPEN

3, V$SGA

Used to display the main components of the SGA (shared pool, database cache, and redo log buffer dimensions).

5:17:36 sql> SELECT * from V$SGA;

NAME VALUE

-------------------- ----------

Fixed Size 1218992

Variable Size 79693392

Database buffers 201326592

Redo buffers 2973696

4, V$sgainfo

Used to obtain more detailed information about the SGA

5:19:55 sql> SELECT * from V$sgainfo;

NAME BYTES RES

-------------------------------- ---------- ---

Fixed SGA Size 1218992 No

Redo buffers 2973696 No

Buffer Cache Size 201326592 Yes

Shared Pool Size 71303168 Yes

Large Pool Size 4194304 Yes

Java Pool Size 4194304 Yes

Streams Pool Size 0 Yes

Granule Size 4194304 No

Maximum SGA Size 285212672 No

Startup overhead in Shared Pool 37748736 No

Free SGA Memory Available 0