TOP
首先查看系统资源占用信息，TOP看一下
ps -mp 4318 -o THREAD,tid,time
再通过ps命令查看这个程序的线程信息,tid代码线程ID，time代表这个线程的已运行时间
由上面TOP可知进程ID为15669
printf “%x\n” 4329
10e9n
有了线程ID的16进制后，再在jstack中查看进程堆栈信息(之所有拿到TID信息，主要是为了查找方便
jstack 4318 |grep 10e9n
1.top 查到pid 28555
2.ps aux|grep 28555 确定到是tomcat的进程
3.显示线程列表 ps -mp 28555 -o THREAD,tid,time 查到tid 28802
4. printf "%x\n" 28802 将线程id，tid进行16进制转换
5.jstack pid |grep tid -A 30 显示堆栈信息 jstack 28555 |grep 28802 -A 30
iostat -dxm 3 查看io
iotop -oP 只显示有I/O行为的进程
pidstat -d 1 展示I/O统计，每秒更新一次
查看什么sql占用高
select sql_text
from v$process pr,v$session ss,v$sqlarea sl
where pr.addr=ss.PADDR
and ss.SQL_HASH_VALUE=sl.HASH_VALUE
and pr.spid=1606;
查看这些占用CPU资源很高的Oracle进程究竟是在做什么操作
select sql_text,spid,v$session.program,process from
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
1.查询表空间使用率
select a.tablespace_name, total, free,(total-free) as usage from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
2.查看总消耗时间最多的前10条SQL语句
select * from (select v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,rank() over(order by v.elapsed_time desc) elapsed_rank from v$sql v) a where elapsed_rank <= 10;
3.查看CPU消耗时间最多的前10条SQL语句
select * from (select v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,rank() over(order by v.cpu_time desc) elapsed_rank from v$sql v) a where elapsed_rank <= 10;
4.查看消耗磁盘读取最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
5. IOps和IO吞吐量 （oracle 11g）
select sum(decode(name,'physical read IO requests',value,'physical write IO requests',value,0)) as iops,
sum(decode(name,'physical read bytes',value,'physical write bytes',value,0)) / 1024 / 1024 as mbps from v$sysstat
where name in ('physical read IO requests','physical write IO requests','physical read bytes','physical read total bytes',
'physical write bytes','physical write total bytes','physical read total IO requests','physical write total IO requests');
6.内存使用情况
SGA / PGA 使用情况
-- pctused: 使用率
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);
根据占用cpu高的进程号来查询者个进程执行的sql语句：
SELECT sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address)
IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
ORDER BY piece ASC;
例：查询31968进程对应的sql语言：
SELECT sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address)
IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '31968'))
ORDER BY piece ASC;
通过pid查看程序执行对应的sql:
select sql_text
from v$process pr,v$session ss,v$sqlarea sl
where pr.addr=ss.PADDR
and ss.SQL_HASH_VALUE=sl.HASH_VALUE
and pr.spid=10840;
查看当前正在执行的SQL：
select a.program, b.spid, c.sql_text,c.SQL_ID
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null;
1，查看CPU占用高的进程号
2，根据进程号查看该进程在做什么
select sql_text,spid,v$session.program,process
from v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in(PID);
3，看看数据库的等待事件都有些什么
select sid,event,p1,p1text from v$session_wait;
看看等待事件由什么进程造成的
select spid from v$process where addr in(select paddr from v$session where sid in(84,102,101));
利用一下脚本可以由已知session的SID来获得SQL语句
select sql_text
from v$sql_text a
where a.hash_value=(
select sql_hash_value
from v$session b
where b.SID=’&sid’)
order by piect ASC;
查看当前会话sql_id
select sql_id ,username,status,event from v$session;
根据sql_id查看sql语句
select sql_text from v$sql where sql_id='cx7sxk891r782';
ORACLE查询当前执行效率低的sql
--CPU高的SQL
select sql_text from v$sql order by cpu_time desc where rownum <10
--逻辑读多的SQL：
select * from (select buffer_gets, sql_text from v$sqlarea where buffer_gets > 500000 order by buffer_gets desc) where rownum<=30;
--执行次数多的SQL ：
select sql_text,executions from
(select sql_text,executions from v$sqlarea order by executions desc)
where rownum<10;
--读硬盘多的SQL ：
select sql_text,disk_reads from (select sql_text,disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;
select *
from (
select sql_text,sql_id,cpu_time
from v$sql
order by cpu_time desc)
where ownum<=10
order by rownum asc;
select *
from (
select sql_text,sql_id,cpu_time
from v$sqlarea
order by cpu_time desc)
where rownum<=10
order by rownum asc;
--这2个语句效果基本一样，一个从v$sql视图查询一个从v$sqlarea视图查询。
--列出使用频率最高的5个查询：
select sql_text,executions
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from v$sql)
where exec_rank <=5;
--消耗磁盘读取最多的sql top5：
select disk_reads,sql_text
from (select sql_text,disk_reads,
dense_rank() over
(order by disk_reads desc) disk_reads_rank
from v$sql)
where disk_reads_rank <=5;
--找出需要大量缓冲读取（逻辑读）操作的查询：
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
dense_rank() over
(order by buffer_gets desc) buffer_gets_rank
from v$sql)
where buffer_gets_rank<=5;
select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes'; --数据库允许的最大连接数
*修改最大连接数:
alter system set processes = 300 scope = spfile
shutdown immediate
startup
查看连接消耗情况
默认情况下，oracle记录了机器名（hostname），而没有记录ip。所以，只能知道每个机器消耗了多少连接（弊端：如果hostname重名时，视作一台机器）。
查询：客户端设备标识、客户端程序、oracle用户名、消耗的连接数量
select b.MACHINE, b.PROGRAM, b.USERNAME, count(*) from v$process a, v$session b
where a.ADDR = b.PADDR and b.USERNAME is not null
group by b.MACHINE, b.PROGRAM, b.USERNAME
order by count(*) desc