查询数据库版本信息
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
查询sga中的相关信息
select name, bytes/1024/1024"Size(M)"from v$sgainfo;
NAME Size(M)
-------------------------------- ----------
Fixed SGA Size 2.12744903
Redo Buffers 2.19921875
Buffer Cache Size 300
Shared Pool Size 216
Large Pool Size 4
Java Pool Size 4
Streams Pool Size 4
Shared IO Pool Size 0
Granule Size 4
Maximum SGA Size 816.328125
Startup overhead in Shared Pool 68.5404663
Free SGA Memory Available 284
查询初始化参数相关信息
select name,valuefrom v$parameterwhere name like'%pool%';
NAME VALUE
-------------------------------------------------------------------------------- ----------------------------------------------------
shared_pool_size 0
large_pool_size 0
java_pool_size 0
streams_pool_size 0
shared_pool_reserved_size 10905190
buffer_pool_keep
buffer_pool_recycle
global_context_pool_size
olap_page_pool_size 0
9 rows selected
v$sysstat是统计信息，v$statname用于显示从v$sesstat和v$sysstat解析过的统计名。比如，确定系统总的事务量，可以：
select a.value as "TransactionCount" from v$sysstat a, v$statname b
where a.statistic# = b.statistic# and b.name = 'user commits';
TransactionCount
----------------
6954
这个v$视图显示当前实例的状态。比如，要确定系统总的运行时间：
select (sysdate - startup_time)*24*60*60 as seconds from v$instance;
SECONDS
----------
34183
查询会话信息。比如：
select username, sid, serial# from v$session where username is not null;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYS 1 7
SYSMAN 32 165
SYSMAN 33 25
SYSMAN 35 55
SYS 37 1065
SYS 38 3205
SYSMAN 39 3
SYS 42 5039
SYSMAN 43 49
SYSMAN 44 97
SYSMAN 45 29
DBSNMP 46 95
SYSMAN 47 359
DBSNMP 48 893
SYSMAN 51 13
SYS 57 2345
查询sql相关信息。如下：
select sql_text, parse_calls, loads, executions from v$sql
where upper(sql_text)like'。。。';