ORACLE服务器监控

--按运行时间来查找top10的sql
SELECT sql_id,child_number,sql_text,elapsed_time
FROM (SELECT sql_id,child_number,sql_text,elapsed_time, cpu_time,disk_reads,
                    RANK () OVER(ORDER BY elapsed_time DESC ) as elapsed_rank
                    FROM v$sql)
WHERE elapsed_rank <=10;
--利用DBMS_XPLAN来执行查询执行计划，具体用法，可以参考oracle官方文档
----------------------------------------------------
--针对某些sql进行跟踪，产生跟踪信息-------------
----------------------------------------------------
一、跟踪当前会话
1、识别跟踪文件，通过设置，找到有特殊标记的trc文件
ALTER SESSION SET TRACEFILE_IDENTIFIER=TRSEN;
2、只产生执行统计信息及执行计划，但是不包括绑定变量及各种等待事件花费的时间,若获得更高级信息可利用dbms_session包的函数进行跟踪
ALTER SESSION SET SQL_TRACE=TRUE;
二、跟踪其他会话
1、利用dbms_monitor包来完成工作
begin
for row in(select sid,serial# from v$session where lower(program) like '%sqlplus%')
loop
dbms_monitor.session_trace_enable(session_id=>row.sid,
serial_num=>row.serial#,
waits=>true,
binds=>false);
end loop;
end;
/
2、找出跟踪的trc文件对应的spid
select pid,spid,sid from v$session s,v$process p where p.addr=s.paddr;

--查看等待事件
SELECT wait_class,event,total_waits AS waits,
ROUND(time_waited_micro/1000) AS total_ms,
ROUND(time_waited_micro*100/SUM(time_waited_micro) OVER(),2) AS pct_time,
ROUND((time_waited_micro/total_waits)/1000,2) AS avg_ms
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited_micro DESC;
--------------------------------------------------
--列出一些常见的等待事件，也可查询官方文档--
--------------------------------------------------
application类型
1、enq:enqueue name:enqueue是锁上的一个等待。每种锁类型都有它自己的等待事件
2、SQL*Net message to client:服务器进程正在给客户端进程发送信息
3、SQL*Net more data to client:服务器进程正在发送数据到客户端进程
commit类型
1、log file sync:服务器在等待重做日志记录被写入文件，可能是由于发起了提交操作
concurrency类型
1、library cache pin:当加载或修改库缓存中的对象(共享的SQL或类似对象)时将发生钉住(pin)操作
2、latch:latch name:闩锁与锁类似，但是，它使用在共享内存上而不是表数据上。很多闩锁都有自己的等待事件
3、cursor:mutex:为了在共享池中添加或修改共享的sql语句，服务器进程正在等待一个互斥(类似于闩锁)
4、row cache lock:等待数据字典表的缓存副本上的锁
5、buffer busy waits:缓冲区告诉缓存中的块正被另一个会话访问
configuraion类型
1、 log file switch(reason):等待日志文件切换，可能是因为日志没有归档或没有设置检查点
2、free buffer waits:缓冲区高速缓存中块都是脏块，可能表明DBWR进程遭遇瓶颈
3、write complete waits:等待DBWR将数据块写到磁盘
System I/O类型
1、log file singleparallel write:等待日志文件写操作完成。这个等待时间通常由LGWR进程记录
2、control file sequential read|write: 等待控制文件的读操作完成。它通常发生在读取特定的V$表或者执行系统操作时
User I/O类型
1、db file single write:对数据文件的单块写，可能是写数据文件头
2、direct path write temp:直接写到临时的数据文件，跳过缓冲区告诉缓存，也不涉及DBWR
3、direct path read:跳过缓冲区高速缓存直接读取数据文件
4、direct path read temp:跳过缓冲区高速缓存，从临时表空间读取数据
5、read by other session:等待其他会话将数据读入缓冲区告诉缓存。以前被记录为buffer busy waits
6、db file parallel read:在单次操作中读取多个数据块到缓冲区高速缓存中的操作
7、db file scattered read: 作为全表扫描或其他多块读取的一部分，从磁盘进行读取
8、db file sequential read:从磁盘读取单块数据，通常由索引读取导致