--ORACLE
1、查询碎片程度高的表
条件为什么block>100，因为一些很小的表，只有几行数据实际大小很小，但是block一次性分配就是5个（11g开始默认一次性分配1M的block大小了，见create table storged的NEXT参数），5个block相对于几行小表数据来说就相差太大了。
算法中/0.9是因为块的pfree一般为10%，所以一个块最多只用了90%，而且一行数据大于8KB时容易产生行链接，把一行分片存储，一样的一个块连90%都用不满、
AVG_ROW_LEN还是比较准的，比如个人实验情况一表6个字段，一个number，其他5个都是char(100)但是实际数据都是’1111111’7位，AVG_ROW_LEN显示依然为513
SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小M",
(NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"实际大小M",
round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"
FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)30;
3、集群因子clustering_factor高的表
集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描
select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"
from user_tables tab, user_indexes ind where tab.table_name=ind.table_name
and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3
4、根据sid查spid或根据spid查sid
select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.sid=XX or p.spid=YY
5、根据sid查看具体的sql语句
select username,sql_text,machine,osuser from v$session a,v$sqltext_with_newlines b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid order by piece;
6、根据spid查询具体的sql语句
select ss.SID, pr.SPID, ss.action, sa.SQL_FULLTEXT, ss.TERMINAL, ss.PROGRAM, ss.SERIAL#, ss.USERNAME, ss.STATUS, ss.OSUSER, ss.last_call_et
from v$process pr, v$session ss, v$sqlarea sa
where ss.status='ACTIVE' and ss.username is not null and pr.ADDR = ss.PADDR
and ss.SQL_ADDRESS = sa.ADDRESS and ss.SQL_HASH_VALUE = sa.HASH_VALUE
and pr.spid = XX
7、查看历史session_id的SQL来自哪个IP
(当然这是个误解，都是历史的了，怎么可能还查到spid，其实查看trace文件名就可以知道spid,trace文件里面有sid和具体sql，如果trace存在incident，那trace就看不到具体sql，但是可以在incident文件中看到具体的sql，如DW_ora_17751.trc中17751就是spid，里面有这样的内容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具体的sql语句)
DB_ora_29349.trc中出现如下
*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726
通过表V$ACTIVE_SESSION_HISTORY来查，如下
select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807
查询上面的machine的IP是多少
select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.machine='localhost'
通过上面的spid在oracle服务器上执行netstat -anp |grep spid即可
[oracle@dwdb trace]$ netstat -anp |grep 17630
tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB
tcp 0 0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB
出现两个，说明来自220，连接了228数据库服务器，但是又通过228服务器的dblink去连接了16服务器
8、查询DML死锁会话sid，及引起死锁的堵塞者会话blocking_session
select sid, blocking_session, LOGON_TIME,sql_id,status,event,seconds_in_wait,state, BLOCKING_SESSION_STATUS from v$session where event like 'enq%' and state='WAITING' and BLOCKING_SESSION_STATUS='VALID'
BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.
可以在v$session.LOGON_TIME上看到引起死锁的堵塞者会话比等待者要早
如果遇到RAC环境，一定要用gv$来查，并且执行alter system kill session 'sid,serial#'要到RAC对应的实例上去执行
或如下也可以
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
a.id1,
a.id2,
' is blocking ' "IS BLOCKING",
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
9、查询DDL锁的sql
SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.v_$session_wait
WHERE event like 'library cache %'
p1raw结果为'0000000453992440'
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIME
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='0000000453992440';
结果为671 0 3 2011-11-1 12:00:00
525 2 0 2011-11-4 12:00:00
10、查询锁住的DDL对象
select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid
11、查询当前正在执行的sql
SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text
FROM v$process,v$session s,v$sql
WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value
12、查询正在执行的SCHEDULER_JOB
select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process where session_id=sid and paddr=addr
13、查询正在执行的dbms_job
select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process where a.sid=b.sid and paddr=addr
14、查询一个会话session、process平均消耗多少内存，查看下面avg_used_M值
select round(sum(pga_used_mem)/1024/1024,0) total_used_M, round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,
round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M, round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M from v$process;
15、TOP 10 执行次数排序
select *
from (select executions,username,PARSING_USER_ID,sql_id,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc)
where rownum 4
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES
order by b.tablespace_name;
24、查看表空间可用百分比
select b.tablespace_name,a.total,b.free,round((b.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by "% Free";
25、查看临时表空间使用率
SELECT temp_used.tablespace_name,total,used,
total - used as "Free",
round(nvl(total-used, 0) * 100/total,3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes)/1024/1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
26、查询undo表空间使用情况
select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status
27、查看ASM磁盘组使用率
select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例" from gv$asm_diskgroup
28、统计每个用户使用表空间率
SELECT c.owner "用户",
a.tablespace_name "表空间名",
total/1024/1024 "表空间大小M",
free/1024/1024 "表空间剩余大小M",
( total - free )/1024/1024 "表空间使用大小M",
Round(( total - free ) / total, 4) * 100 "表空间总计使用率%",
c.schemas_use/1024/1024 "用户使用表空间大小M",
round((schemas_use)/total,4)*100 "用户使用表空间率%"
FROM (SELECT tablespace_name,
Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
Sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b,
(Select owner ,Tablespace_Name,
Sum(bytes) schemas_use
From Dba_Segments
Group By owner,Tablespace_Name) c
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by c.owner,a.tablespace_name;
SELECT c.owner,
a.tablespace_name,
total/1024/1024,
free/1024/102,
( total - free )/1024/1024,
Round(( total - free )/total, 4)*100,
c.schemas_use/1024/1024,
round((schemas_use)/total,4)*100
FROM (SELECT tablespace_name,
Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
Sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b,
(Select owner ,Tablespace_Name,
Sum(bytes) schemas_use
From Dba_Segments
Group By owner,Tablespace_Name) c
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by c.owner,a.tablespace_name;
29、查看闪回区\快速恢复区空间使用率
select sum(percent_space_used)||'%' "已使用空间比例" from V$RECOVERY_AREA_USAGE
30、查看僵死进程，分两种
alter system kill session一执行则session即标记为KILLED，但是如果会话产生的数据量大则这个kill可能会比较久，在这个过程中session标记为KILLED但是这个会话还在V$session中，则V$session.paddr还在，所以可以匹配到V$process.addr，所以process进程还在；当kill过程执行完毕，则这个会话即不在V$session中
会话不在的
select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)
会话还在的，但是会话标记为killed
select * from v$process where addr in (select paddr from v$session where status='KILLED')
再根据上述结果中的SPID通过如下命令可以查看到process的启动时间
ps auxw|head -1;ps auxw|grep SPID
31、查看行迁移或行链接的表
select * From dba_tables where nvl(chain_cnt,0)0
chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.
32、数据缓冲区命中率
LECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads';
或
SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads,
round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';
33、共享池命中率
以下两者应该都可以，看个人怎么理解
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;
34、查询归档日志切换频率
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss')
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from
v$log_history where first_time > sysdate - 3 order by first_time,minutes;
或
select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,First_change#,switch_change# from
v$loghist where first_time>sysdate-3 order by 1;
35、查询lgwr进程写日志时每执行一次lgwr需要多少秒，在state是waiting的情况下，某个等待编号seq#下，seconds_in_wait达多少秒，就是lgwr进程写一次IO需要多少秒
select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'
36、查询没有索引的表
Select table_name from user_tables where table_name not in (select table_name from user_indexes)
Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)
37、查询7天的db time
TH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate - 7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'DB time')
select to_char(BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') ||
to_char(END_INTERVAL_TIME, ' hh24:mi') date_time,
stat_name,
round((e_value - nvl(b_value, 0)) /
(extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +
extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 +
extract(minute from(end_interval_time - begin_interval_time)) * 60 +
extract(second from(end_interval_time - begin_interval_time))),
0) per_sec
from sysstat
where (e_value - nvl(b_value, 0)) > 0
and nvl(b_value, 0) > 0
38、查询产生热块较多的对象
x$bh .tch(Touch)表示访问次数越高，热点快竞争问题就存在
SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id b.dbablk;
39、导出AWR报告的SQL语句
select * from dba_hist_snapshot
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));
40、查询某个SQL的执行计划
select a.hash_value,a.* from v$sql a where sql_id='0n4qfzbqfsjm3'
select * from table(dbms_xplan.display_cursor(v$sql.hash_value,0,'advanced'));
含顺序的
select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));
不过要先创建xplan包，再执行
SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;
SQL> grant execute on sys.xplan to public;
--DB2
--每天需要监控的内容
--监控CPU和内存
vmstat 1 5
--监控IO
iostat 1 5
sar -d 1 5
--查看表空间的存储空间状态
--查看数据库目录的可用空间 dbpath
--查看日志目录所在的文件系统的剩余空间
df -k /logdir
--查看归档的日志
db2adutl query db testdb
--检查诊断日志和管理通知日志
db2diag.log
db2instname.nfy
--检查数据库备份
db2 list history backup all for testdb
db2 list history archive log all for testdb
db2 list history all for testdb
--查看数据库表的状态
db2 "select tabname,colcount,status from syscat.tables
where tabschema not like 'SYS%' order by tabname
"
status = N 正常
status = C 需要检查完整性
db2 set integrity for tabname immediate checked
--查看缓存池命中率，数据逻辑读，数据物理读
db2 "select SNAPSHOT_TIMESTAMP,char(bp_name,15)
,TOTAL_LOGICAL_READS,TOTAL_PHYSICAL_READS,total_hit_ratio_percent
,data_logical_reads,data_physical_reads,data_hit_ratio_percent
from sysibmadm.bp_hitratio
"
--查看缓存池命中率，索引逻辑读，索引物理读
db2 "select SNAPSHOT_TIMESTAMP,char(bp_name,15)
,TOTAL_LOGICAL_READS,TOTAL_PHYSICAL_READS,total_hit_ratio_percent
,index_logical_reads,index_physical_reads,index_hit_ratio_percent
from sysibmadm.bp_hitratio
"
--监控执行成本最高的SQL
db2 "select agent_id,PERCENT_ROWS_SELECTED
from sysibmadm.appl_performance
order by percent_rows_selected
"
--监控运行最长的SQL
db2 "select agent_id,appl_status,elapsed_time_min
from sysibmadm.long_running_sql
order by elapsed_time_min desc fetch first 5 rows only
"
--监控执行次数最多的SQL
db2 "select SNAPSHOT_TIMESTAMP, NUM_EXECUTIONS, char(STMT_TEXT,150)
from sysibmadm.top_dynamic_sql
order by num_executions desc fetch first 10 rows only
"
--监控排序次数最多的SQL
db2 "select stmt_sorts,char(STMT_TEXT,150),SNAPSHOT_TIMESTAMP,sorts_per_execution
from sysibmadm.top_dynamic_sql
order by stmt_sorts desc fetch first 10 rows only
"
--监控引起锁等待的SQL
db2 "select agent_id, char(stmt_text,100) as statement, stmt_elapsed_time_ms
from table(snapshot_statement('TESTDB',-1)) as B
where agent_id in
(select agent_id_holding_lk from table(snapshot_lockwait('TESTDB',-1)) as A
order by lock_wait_start_time asc fetch first 20 rows only
)
order by stmt_elapsed_time_ms desc
"
--查看选择的行数与读取的行数的比例 应大于 20%
db2 "select float(rows_selected)/rows_read from sysibmadm.snapdb where rows_read!=0 "
--表扫描次数 scans
db2pd -d testdb -tcbstats
--查看扫描次数多的表相关的语句
db2 " select stmt_text from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-2)) AS T WHERE stmt_text like '%TESTTAB%' "
--检查锁相关的等待，超时，升级，死锁
db2 get snapshot for all on testdb > log.txt
grep -n "Deadlocks detected" log.txt | grep -v "= 0"
grep -n "Lock waits" log.txt | grep -v "= 0"
grep -n "Lock escalation" log.txt | grep -v "= 0"
grep -n "Lock Timeouts" log.txt | grep -v "= 0"
--查看当前运行最频繁、最消耗资源的SQL （Costly SQL）
db2 "select substr(stmt_text,1,100) as stmt_text
from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-2)) AS T
where rows_read!=0
order by rows_returned/rows_read asc fetch first 10 rows only
"
--执行最频繁的SQL
db2 "select substr(stmt_text,1,100) as stmt_text, num_executions
from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-2)) AS T
order by num_executions desc fetch first 10 rows only
"
--排序最多的语句
db2 "select substr(stmt_text,1,100) as stmt_text , total_sorts
from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-2)) AS T
order by total_sorts desc fetch first 10 rows only
"
----------------------------------------------------------------------------------
-- 每周需要监控的内容
----------------------------------------------------------------------------------
--检查备份完整性
db2ckbkp -h TESTDB.0.db2inst1.NODE0000.CATN0000.20131023221025.001
--检查是否需要runstats
没有搜集过统计信息的表
db2 "select tabname from syscat.tables where stats_time is null "
没有收集过统计信息的索引
db2 "select indname from syscat.indexes where stats_time is null "
15天没有更新过统计信息的表
db2 "select tabname from syscat.tables where stats_time < current timestamp - 15 days"
--监控表是否需要重组
db2 reorgchk update statistics on table all
--监控新对象
db2 "select tabschema, tabname, create_time
from syscat.tables
where create_time > '2014-01-01.00.00.00.000000'
"
--包缓存中的SQL
db2 "select substr(stmt_text,1,200) as sql_stmt, current date
from table(snapshot_dyn_sql('TESTDB',-1)) as snapshot_dyn_sql
"
监控系统资源占用情况
db2 "select application_handle, substr(application_name,1,30) as appname, total_cpu_time
from table(mon_get_connection(null,null)) as t
order by total_cpu_time desc
"
后台实用程序
db2 list utilities
--获得数据库总大小信息
db2 "call get_dbsize_info(?,?,?,0)"
--检查数据库用户db2inst1的权限
db2 " select substr(authority,1,30) as authority , d_user, d_group, d_public,role_user, role_group,role_public,d_role
from table(sysproc.auth_list_authorities_for_authid('DB2INST1','U')) as t
order by authority
"
--查找无效对象
db2 "select char(tabschema,20), char(tabname,40),type,status from syscat.tables order by 1"
db2 "select viewname from syscat.views where valid='N'"
db2 "select trigname from syscat.triggers where VALID='N' "
---------------------------------------------------------------
--内存监控
--数据库和实例每一个内存池的内存大小
db2mtrk -i -p -v -d
--实例总共占用的内存
db2pd -dbptnmem
Cached：含在MemUsed内，已经分配的内存，当前没有使用到，
但这部分内存不能给其他进程使用，DB2可以分配给其他内存池。
----
db2top 每隔一段时间收集一次快照，然后通过计算其与最近一次快照之间的数值差别与经过的时间
--交互模式
db2top -d testdb
db2top -d testdb -f db2top_collect.txt -C -m 2 -i 15
其中-m参数指定运行多少分钟，-i指定每隔多少秒收集一次快照
--播放监控文件
db2top -d testdb -f db2top_collect.txt -b l -A
--直接跳转到某个时间点重新播放监控
db2top -d testdb -f db2top_collect.txt /02:00:00
--IO监控 硬盘使用 5秒钟监控一次，监控10次
--linux
iostat -d 5 10
--aix
iostat -D 5 10
--CPU和虚拟内存监控（物理内存和交换空间），5秒钟监控一次，监控10次
vmstat 5 10
ipcs -a 进程间通信的信息
db2grep -dump 查看DB2安装版本
