select * from v$fixed_view_definition where view_name='V$SESSION';all of the information of the underlying X$tables that make up a v$veiw. 创建视图的语句,可以查到基础表.

select * from dba_views;DBA开头的view创建的信息

select * from v$database;

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
v$database,v$version提供数据库的基础信息

select * from v$sysaux_occupants where occupant_name like '%AWR%';

查看awr使用的空间状况

select * from v$license;

select * from v$option; 我安装的ORACLE支持那些特性

select * from v$sga; 基本的系统内存信息

Fixed Size 1218316
Variable Size 79694068
Database Buffers 83886080
Redo Buffers 2973696

select * from v$sgastat;提供动态的SGA和memory资源信息.

breakdown for the oracle sga以及shared pool的所有buckets详细信息.

select * from v$parameter;提供参数的信息

ISDEFAULT=FALSE表示这个参数被修改过.ISSYS_MODIFIABLE有4个值

TRUE

IMMEDIATE 立刻生效

DEFERRED

FALSE 需要重启

select * from v$sysstat;--Determining Hit Ratio for Data

to see how often your data is being read from memory.

select * from v$sysmetric where metric_name='Buffer Cache Hit Ratio';

select * from v$ROWCACHE; how often the data dictionary calls are effectively hitting the memory cache.shared pool的数据字典命中率

select * from V$LIBRARYCACHE sql和pl/sql的命中率

语句共享，绑定变量

pin hists 95+ perccent

reload hit ratio 99+ percent

reloads 发生在一个语句之前被解析，但是shared pool没有足够的空间来存储它当另一个语句解析时。之前那个sql的body被pushed out of memory（the head is still there)。 当这个语句再次被需要时，一次reload会被记录.另外当执行计划改变时也会导郅reload.

select sum(pins) "Executions",sum(pinhits) "Hits",((sum(pinhits)/sum(pins))*100) "PinHitRatio",sum(reloads) "Misses",((sum(pins)/(sum(pins)+sum(reloads)))*100) "relhitratio" from v$librarycache;

select sql_id,count(*) from v$sql_bind_capture group by sql_id;绑定变量
select * from v$sqlarea where sql_id='19rkm1wsf9axx'

select name,kept,sharable_mem from v$db_object_cache;

object在内存中的一些信息,大小,是否keep在内存中等

v$sqlarea  全表扫描会占用很大的(DB_CACHE_SIZE)(数据大硬盘上读入DB_CACHE中)

poorly written SQL和PL/SQL statements.

select a.sid,b.piece,a.username,b.sql_text from v$session a,v$sqltext b where a.sql_address=b.address
and a.sql_hash_value=b.hash_value order by a.username,a.sid,b.piece;

联合v$session和v$sqltext显示被当前session执行的sql

select a.sid,a.username,b.block_GETS,b.consistent_gets,b.physical_reads,b.block_changes,b.consistent_changes from v$session a,v$sess_io b where a.sid=b.sid
order by a.username;

每个session的physical disk和memory hits.通过v$session,v$sqltext,v$sess_io 找到用户当前执行的状况.

select a.sid,a.username,b.owner,b.object,b.type  from v$session a,v$access b where a.sid=b.sid;

alter index HRDT_INDEX4 nomonitoring usage;
select index_name, table_name, monitoring, used,
start_monitoring, end_monitoring
from v$object_usage;查看一个物件有没有被使用如index

v$datafile,v$filestat,dba_data_files provide file I/O activity .datafile匀衡的分配在每个磁盘上

通过v$filestat 查看数据文件的读写状况.可以根据这些信息,把数据文件放到不同的磁盘下. temporary tablespaces 通过v$tempfile 和v$tempstats.

SQL> select file#,phyrds,phywrts,readtim,writetim from v$filestat;

     FILE#     PHYRDS    PHYWRTS    READTIM   WRITETIM
---------- ---------- ---------- ---------- ----------
         1       5845        241       7925         78
         2         49        220         36         57
         3       1300        692       3645        139
         4          8          4          4          2
         5         10          4         17          2
         6          8          4          6          1

6 rows selected.

moving data files

1.alter tablespace XXXX offline;

2.cp datafile1 'datafile2'

3.alter database XXXX rename datafile 'datafile1' to 'datafile2'

4.alter tablespace XXXXX online;

rm datafile1

查看segments的使用状况 ensure objects are not building up too many extents.

一个segment有太多的extents会影响性能,考虑加大extent的大小

select segment_name,extents,bytes from dba_segments where extents>