监控 Oracle 性能是数据库管理的关键任务，确保系统的高效运行和优化资源利用。以下是一些常用的 SQL 语句，用于监控 Oracle 数据库的性能：
1. **监控事例的等待事件**：通过 `v$session_wait` 视图，我们可以查看当前会话正在等待的事件类型、等待次数和总等待时间，从而分析系统中的性能瓶颈。
```sql
select event, sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr", count(*) "Tot"
from v$session_wait
group by event order by 4;
```
2. **回滚段的争用情况**：利用 `v$rollstat` 和 `v$rollname` 视图，可以检查回滚段的等待和获取次数，计算争用比例，以判断是否需要增加或调整回滚段大小。
```sql
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
```
3. **监控表空间的 I/O 比例**：查询 `v$filestat` 和 `dba_data_files` 来获取每个表空间的物理读写次数，有助于理解数据文件的 I/O 行为。
```sql
select df.tablespace_name name, df.file_name "file",
f.phyrds pyr, f.phyblkrd pbr, f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
```
4. **监控文件系统的 I/O 比例**：通过 `v$datafile` 和 `v$filestat` 视图，可以监控单个数据文件的物理读写活动。
```sql
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
```
5. **查找用户下的所有索引**：在特定用户模式下，我们可以列出所有索引及其属性，包括索引名称、唯一性以及列名。
```sql
select user_indexes.table_name, user_indexes.index_name, uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
```
6. **监控 SGA 的逻辑和物理读取命中率**：通过 `v$sysstat` 视图，我们可以计算缓冲区的命中率，这是评估 SGA 效率的重要指标。
```sql
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
```
7. **监控 SGA 字典缓冲区的命中率**：通过 `v$rowcache` 视图，可以查看字典缓存的命中率，评估其效率。
```sql
select parameter, gets, Getmisses, getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <> 0
group by parameter, gets, getmisses;
```
8. **监控 SGA 共享缓存区的命中率**：`v$librarycache` 视图提供了共享池的命中率信息，理想情况下应小于 1%。
```sql
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio", sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
```
9. **显示所有数据库对象的类别和大小**：通过查询 `DBA_OBJECTS` 视图，可以获取数据库中所有对象的类型和大小信息，帮助识别存储空间的使用情况。
```sql
select object_type, SUM(bytes) / 1024 / 1024 "Size (MB)"
from DBA_OBJECTS
group by object_type
order by 2 DESC;
```
这些 SQL 查询是数据库管理员日常监控和性能优化的重要工具，它们可以帮助识别性能问题、调整数据库配置和资源分配，以确保 Oracle 数据库的最佳性能。
