整合营销服务商
电脑端+手机端+微信端=数据同步管理
免费咨询热线：
电脑端+手机端+微信端=数据同步管理
免费咨询热线：
看重复索引
WITH indexes AS (
SELECT
i.owner,
i.index_name,
i.table_name,
listagg(c.column_name, ', ') WITHIN GROUP (
ORDER BY
c.column_position) AS columns
FROM
dba_indexes i
JOIN dba_ind_columns c ON
i.index_name=c.index_name
GROUP BY
i.owner,
i.table_name,
i.index_name,
i.leaf_blocks )
SELECT
i.OWNER ,
i.table_name,
i.index_name AS "Deletion candidate index",
i.columns AS "Deletion candidate columns",
j.index_name AS "Existing index",
j.columns AS "Existing columns"
FROM
indexes i
JOIN indexes j ON
i.table_name=j.table_name
AND j.columns LIKE i.columns || ',%'用户命中率
SET LINESIZE 500
COLUMN "Hit Ratio %" FORMAT 999.99
SELECT a.username "Username",
b.consistent_gets "Consistent Gets",
b.block_gets "DB Block Gets",
b.physical_reads "Physical Reads",
Round(100* (b.consistent_gets + b.block_gets - b.physical_reads) /
(b.consistent_gets + b.block_gets),2) "Hit Ratio %"
FROM v$session a,
v$sess_io b
WHERE a.sid=b.sid
AND (b.consistent_gets + b.block_gets) > 0
AND a.username IS NOT NULL;查询碎片程度高（实际使用率小于30%）的表
可以收缩的表条件为什么block>100，因为一些很小的表，只有几行数据实际大小很小，但是block一次性分配就是5个（11g开始默认一次性分配1M的block大小了，见create table storged的NEXT参数），5个block相对于几行小表数据来说就相差太大了。
算法中/0.9是因为块的pfree一般为10%，所以一个块最多只用了90%，而且一行数据大于8KB时容易产生行链接，把一行分片存储，一样的一个块连90%都用不满 ，AVGROWLEN还是比较准的，比如个人实验情况一表6个字段，一个number，其他5个都是char(100)但是实际数据都是’1111111’7位，AVGROWLEN显示依然为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 DBA_TABLES
where blocks > 100
and owner='TXPROD'
and (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
(BLOCKS * 8192 / 1024 / 1024) < 0.3
order by (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
(BLOCKS * 8192 / 1024 / 1024) desc查询索引碎片的比例
索引删除行数除以索引总行数的百分比>30%即认为索引碎片大，也就是需要重建的索引
select name,
del_lf_rows,
lf_rows,
round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) || '%' frag_pct
from index_stats
where round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) > 30;集群因子clustering_factor高的表
集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描 ：
方法一
/* Formatted on 2019/11/6 10:02:07 (QP5 v5.326) */
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.owner='TXPROD'
AND tab.blocks > 100
AND NVL (ind.clustering_factor, 1)
/ DECODE (tab.num_rows, 0, 1, tab.num_rows) BETWEEN 0.35
AND 3方法二
/* Formatted on 2019/11/6 10:03:16 (QP5 v5.326) */
SELECT tab.owner,
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 dba_tables tab, dba_indexes ind
WHERE tab.table_name=ind.table_name
AND tab.OWNER=ind.OWNER
AND tab.owner NOT IN ('SYS',
'SYSTEM',
'WMSYS',
'DBSNMP',
'CTXSYS',
'XDB',
'ORDDATA',
'SYSMAN',
'CATALOG',
'APEX_030200',
'MDSYS',
'OLAPSYS',
'EXFSYS')
AND tab.blocks > 100
AND NVL (ind.clustering_factor, 1)
/ DECODE (tab.num_rows, 0, 1, tab.num_rows) BETWEEN 0.35
AND 3根据sid查spid或根据spid查sid
SELECT s.sid,
s.serial#,
p.spid,
s.terminal,
s.LOGON_TIME,
s.status,
s.PROGRAM,
s.CLIENT_IDENTIFIER,
s.machine,
s.action,
s.MODULE,
s.PROCESS "客户端机器进程号",
s.osuser
FROM v$session s, v$process p
WHERE s.paddr=p.addr AND s.sid=XX OR p.spid=YY根据sid查看具体的sql语句，不要加条件v$session.status=’ ACTIVE’，比如toad对同一数据库开两个连接会话，都执行了一些语句，其中一个窗口查询select * from v$session时会发现另一个窗口在v$session.status是INACTIVE，并不代表另一个窗口没有执行过sql语句，而当前窗口是active状态，对应的sql_id对应的语句就是select * from v$session而不是之前执行过的sql语句，ACTIVE表示当前正在执行sql。
一个sid可能执行过很多个sql，所以有时需要的sql通过如下查不到是正常的，比如查询到某死锁源sid，通过如下查询可能只是个select语句，而真正引起死锁的sql却查不到，是因为可能这个sid持续了很长时间，这个sid之前执行的一些sql在v$sql可能已经被清除了。
方法一
/* Formatted on 2019/11/6 10:04:20 (QP5 v5.326) */
SELECT username,
sid,
SERIAL#,
LOGON_TIME,
status,
PROGRAM,
CLIENT_IDENTIFIER,
machine,
action,
PROCESS "客户端机器进程号",
osuser,
sql_text
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;方法二
/* Formatted on 2019/11/6 10:04:35 (QP5 v5.326) */
SELECT username,
sid,
SERIAL#,
LOGON_TIME,
status,
sql_fulltext,
PROGRAM,
CLIENT_IDENTIFIER,
machine,
a.action,
PROCESS "客户端机器进程号",
osuser
FROM v$session a, v$sql b
WHERE DECODE (a.sql_hash_value, 0, prev_hash_value, sql_hash_value)= b.hash_value
AND a.sid=&sid如果上面语句执行太慢，则按如下两步
/* Formatted on 2019/11/6 10:05:18 (QP5 v5.326) */
SELECT sql_hash_value,
prev_hash_value,
username,
sid,
SERIAL#,
LOGON_TIME,
status,
PROGRAM,
CLIENT_IDENTIFIER,
machine,
action,
PROCESS "客户端机器进程号",
osuser
FROM v$session
WHERE sid=&sid;
SELECT sql_fulltext
FROM v$sql
WHERE hash_value=XX;--XX为上面 sqlhashvalue，如果 sqlhashvalue为0，则XX为上面 prevhashvalue
根据spid查询具体的sql语句(不要加条件v$session.status=’ ACTIVE’,比如toad对同一数据库开两个连接会话，都执行了一些语句，其中一个窗口查询select * from v$session时会发现另一个窗口在v$session.status是INACTIVE，并不代表另一个窗口没有执行过sql语句，而当前窗口是active状态，对应的sql_id对应的语句就是select * from v$session而不是之前执行过的sql语句，ACTIVE表示当前正在执行sql。)
/* Formatted on 2019/11/6 10:05:33 (QP5 v5.326) */
SELECT ss.SID,
ss.SERIAL#,
ss.LOGON_TIME,
pr.SPID,
sa.SQL_FULLTEXT,
ss.machine,
ss.TERMINAL,
ss.PROGRAM,
ss.USERNAME,
ss.CLIENT_IDENTIFIER,
ss.action,
ss.PROCESS "客户端机器进程号",
ss.STATUS,
ss.OSUSER,
ss.status,
ss.last_call_et,
sa.sql_text
FROM v$process pr, v$session ss, v$sql sa
WHERE pr.ADDR=ss.PADDR
AND DECODE (ss.sql_hash_value, 0, prev_hash_value, sql_hash_value)= sa.hash_value
AND pr.spid=&spid查看历史session_id的SQL来自哪个IP
查看trace文件名就可以知道spid,trace文件里面有sid和具体sql，如果trace存在incident，那trace就看不到具体sql，但是可以在incident文件中看到具体的sql，如DWora17751.trc中17751就是spid，里面有这样的内容Incident 115 created, dump file: /XX/incident/incdir115/DWora17751i115.trc，那么在DWora17751_i115.trc就可以看到具体的sql语句)
DBora29349.trc中出现
*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726 通过表V$ACTIVESESSIONHISTORY来查
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服务器
查询死锁堵塞的会话sid
最简单的一个SQL
select * from V$SESSION_BLOCKERS select * from dba_waiters;最常用的一个SQL
select sid,status,LOGON_TIME,sql_id,blocking_session "死锁直接源",FINAL_BLOCKING_SESSION "死锁最终源",event,seconds_in_wait "会话锁住时间_S",LAST_CALL_ET "会话持续时间_S" from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID';可以把两者SID放入v$session，发现LOGONTIME字段FINALBLOCKING_SESSION比SID要早
BLOCKINGSESSION:Session identifier of the blocking session. This column is valid only if BLOCKINGSESSIONSTATUS has the value VALID. FINALBLOCKINGSESSION:Session identifier of the blocking session. This column is valid only if FINALBLOCKINGSESSIONSTATUS has the value VALID.
如果遇到RAC环境，一定要用gv$来查，并且执行alter system kill session 'sid,serial#'要到RAC对应的实例上去执行
把上面被堵塞会话的sid代入如下语句，可以发现锁住的对象和对象的哪一行(如果sid是堵塞源的会话，则 rowwaitobj#=-1，表示锁持有者，就是死锁源了 )
select s.sid,s.username,d.owner,d.object_name,s.row_wait_obj#,s.row_wait_row#,s.row_wait_file#,s.row_wait_block# from v$session s,dba_objects d where s.row_wait_obj#=d.object_id and s.sid in(XX,XX);查询锁住的DDL对象
sql
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;查询超过两个小时的不活动会话
select s.sid,s.serial#,p.spid,s.LOGON_TIME,s.LAST_CALL_ET,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.terminal,s.action,s.PROCESS "客户端机器进程号",s.osuser from v$session s,v$process p where s.paddr=p.addr and s.sid in (select sid from v$session where machine<>&DB服务器名称 and status='INACTIVE' and sql_id is null and LAST_CALL_ET>7200);查询堵塞别的会话超过30分钟且自身是不活动的会话
select count(ss.SID),ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER from v$session ss group by ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER having count(ss.SID)>10;查询当前正在执行的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 and s.STATUS='ACTIVE';查询正在执行的SCHEDULER_JOB sql
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;查询正在执行的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;查询一个会话session、process平均消耗多少PGA内存，查看下面avgusedM值
/* Formatted on 2019/11/6 10:06:48 (QP5 v5.326) */
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;TOP 10 执行次数排序
/* Formatted on 2019/11/6 10:07:07 (QP5 v5.326) */
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 <=5;TOP 10 物理读排序（消耗IO排序，即最差性能SQL、低效SQL排序）
/* Formatted on 2019/11/6 10:06:06 (QP5 v5.326) */
SELECT *
FROM ( SELECT DISK_READS,
username,
PARSING_USER_ID,
sql_id,
ELAPSED_TIME / 1000000,
sql_text
FROM v$sql, dba_users
WHERE user_id=PARSING_USER_ID
ORDER BY DISK_READS DESC)
WHERE ROWNUM <=5;注意：不要使用DISK_READS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool。
TOP 10 逻辑读排序（消耗内存排序）
select * from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc) where rownum <=5;注意：不要使用BUFFER_GETS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）
TOP 10 CPU排序(单位秒=cpu_time/1000000)
select * from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc) where rownum <=5;注意：不要使用CPU_TIME/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool。
查询等待事件
select event,sum(decode(wait_time,0,0,1)) "之前等待次数", sum(decode(wait_time,0,1,0)) "正在等待次数",count(*) from v$session_wait group by event order by 4 desc;查询当前等待事件对应的对象
select distinct wait_class#,wait_class from v$session_wait_class order by 1;以上sql发现wait_class#=6的是空闲等待
select * from(select sid,event,p1text,p1,p2text,p2,p3text,p3,WAIT_TIME,SECONDS_IN_WAIT,wait_class# from v$session_wait where wait_class# <> 6 order by wait_time desc)where rownum <=10;能查出等待的对象是否来自数据文件（如果以上查到p1text是file#或file number）
select * from(select owner,segment_name,segment_type,block_id,bytes from dba_extents where file_id=p1 and block_id<p2 order="" by="" block_id="" desc)where rownum<2把上面第二个sql结果的p1、p2值代入上述sql的fileid、blockid
通过AWR的top sql或v$sql.sql_text查看是否有该对象的语句，检查该语句的执行计划就可以查出问题所在。
查询当前正在消耗临时空间的sql语句
方法一：
/* Formatted on 2019/11/6 10:08:27 (QP5 v5.326) */
SELECT DISTINCT
se.username,
se.sid,
su.blocks * TO_NUMBER (RTRIM (p.VALUE)) / 1024 / 1024 AS space_G,
su.tablespace,
sql_text
FROM V$TEMPSEG_USAGE su,
v$parameter p,
v$session se,
v$sql s
WHERE p.name='db_block_size'
AND su.session_addr=se.saddr
AND su.sqlhash=s.hash_value
AND su.sqladdr=s.address
AND se.STATUS='ACTIVE'方法二：
/* Formatted on 2019/11/6 10:09:13 (QP5 v5.326) */
SELECT v$sql.sql_id,
v$sql.sql_fulltext,
swa.TEMPSEG_SIZE / 1024 / 1024 TEMPSEG_M,
swa.*
FROM v$sql_workarea_active swa, v$sql
WHERE swa.sql_id=v$sql.sql_id AND swa.NUMBER_PASSES > 0查询因PGA不足而使用临时表空间的最频繁的10条SQL语句
/* Formatted on 2019/11/6 10:07:40 (QP5 v5.326) */
SELECT *
FROM ( SELECT OPERATION_TYPE,
ESTIMATED_OPTIMAL_SIZE,
ESTIMATED_ONEPASS_SIZE,
SUM (OPTIMAL_EXECUTIONS) optimal_cnt,
SUM (ONEPASS_EXECUTIONS) AS onepass_cnt,
SUM (MULTIPASSES_EXECUTIONS) AS mpass_cnt,
s.sql_text
FROM V$SQL_WORKAREA swa, v$sql s
WHERE swa.sql_id=s.sql_id
GROUP BY OPERATION_TYPE,
ESTIMATED_OPTIMAL_SIZE,
ESTIMATED_ONEPASS_SIZE,
sql_text
HAVING SUM (ONEPASS_EXECUTIONS + MULTIPASSES_EXECUTIONS) > 0
ORDER BY SUM (ONEPASS_EXECUTIONS) DESC)
WHERE ROWNUM < 10查询正在消耗PGA的SQL
select s.sql_text, sw.EXPECTED_SIZE, sw.ACTUAL_MEM_USED,sw.NUMBER_PASSES, sw.TEMPSEG_SIZE from v$sql_workarea_active sw, v$sql s where sw.sql_id=s.sql_id;查询需要使用绑定变量的sql，10G以后推荐第二种
注意：任何一条执行过的语句不管执行了几次在V$SQL中都只有一条记录，V$SQL中会记录执行了几次。两条一模一样的语句但是在不同的schema下执行的两种结果，如select * from t1.test在sye、system下执行则V$SQL只有一条记录(谁先执行则PARSINGSCHEMANAME显示谁)。如在sys和system都执行select * from test则V$SQL中有两条记录，两条记录的CHILDNUMBER和PARSINGSCHEMA_NAME不一样。
同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录，说明V$SQL对应的sql语句必须一模一样，如果alter system flush sharedpool（主站慎用）后再执行一样的语句，发现语句在V$SQL中的SQLID和HASHVALUE与之前的一样，说明SQLID和HASHVALUE应该是oracle自己的一套算法来的，只是根据sql语句内容来进行转换，sql语句不变则SQLID和HASH_VALUE也不变。
第一种
select * from ( select count(*),sql_id, substr(sql_text,1,40) from v$sql group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10;第二种
count(1)>10表示类语句运行了10次以上 select sql_id, FORCE_MATCHING_SIGNATURE, sql_text from v$SQL where FORCE_MATCHING_SIGNATURE in (select /*+ unnest */ FORCE_MATCHING_SIGNATURE from v$sql where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > 10)查看数据文件可用百分比
dbafreespace并不会包含所有fileid，如果该数据文件满了，则 dbafreespace.fileid没有该数据文件，所以以下sql中 a.fileid=b.fileid的条件过滤后是不会有所有file_id的
/* Formatted on 2019/11/6 10:09:31 (QP5 v5.326) */
SELECT b.file_id,
b.tablespace_name,
b.file_name,
b.AUTOEXTENSIBLE,
ROUND (b.MAXBYTES / 1024 / 1024 / 1024, 2) || 'G'
"文件最大可用总容量",
ROUND (b.bytes / 1024 / 1024 / 1024, 2) || 'G'
"文件总容量",
ROUND ((b.bytes - SUM (NVL (a.bytes, 0))) / 1024 / 1024 / 1024, 2)
|| 'G'
"文件已用容量",
ROUND (SUM (NVL (a.bytes, 0)) / 1024 / 1024 / 1024, 2) || 'G'
"文件可用容量",
ROUND (SUM (NVL (a.bytes, 0)) / (b.bytes), 2) * 100 || '%'
"文件可用百分比"
FROM dba_free_space a, dba_data_files b
WHERE a.file_id=b.file_id
GROUP BY b.tablespace_name,
b.file_name,
b.file_id,
b.bytes,
b.AUTOEXTENSIBLE,
b.MAXBYTES
ORDER BY b.tablespace_name;--如下为标准版
/* Formatted on 2019/11/6 10:10:23 (QP5 v5.326) */
SELECT b.file_id,
b.tablespace_name,
b.file_name,
b.AUTOEXTENSIBLE,
ROUND (b.MAXBYTES / 1024 / 1024 / 1024, 2) || 'G'
"文件最大可用总容量",
ROUND (b.bytes / 1024 / 1024 / 1024, 2) || 'G'
"文件当前总容量",
ROUND ((b.bytes - SUM (NVL (a.bytes, 0))) / 1024 / 1024 / 1024, 2)
|| 'G'
"文件当前已用容量",
ROUND (
( DECODE (AUTOEXTENSIBLE, 'NO', b.BYTES, b.MAXBYTES)
+ SUM (NVL (a.bytes, 0))
- b.bytes)
/ 1024
/ 1024
/ 1024,
2)
|| 'G'
"文件可用容量",
ROUND (
( DECODE (AUTOEXTENSIBLE, 'NO', b.BYTES, b.MAXBYTES)
+ SUM (NVL (a.bytes, 0))
- b.bytes)
/ (DECODE (AUTOEXTENSIBLE, 'NO', b.BYTES, b.MAXBYTES)),
2)
* 100
|| '%'
"文件可用百分比"
FROM dba_free_space a, dba_data_files b
WHERE a.file_id=b.file_id
GROUP BY b.tablespace_name,
b.file_name,
b.file_id,
b.bytes,
b.AUTOEXTENSIBLE,
b.MAXBYTES
ORDER BY DECODE (AUTOEXTENSIBLE, 'NO', b.BYTES, b.MAXBYTES)
+ SUM (NVL (a.bytes, 0))
- b.bytes;查看数据库文件的实际总量，单位G
SELECT /*+ USE_MERGE(C,B) */ a.datafile_size + b.tempfile_size - c.free_size
FROM (SELECT SUM(bytes / 1024 / 1024 / 1024) datafile_size
FROM dba_data_files) a,
(SELECT SUM(bytes / 1024 / 1024 / 1024) tempfile_size
FROM dba_temp_files) b,
(SELECT SUM(bytes / 1024 / 1024 / 1024) free_size
FROM dba_free_space) c查看表空间可用百分比（ dbafreespace不会包含所有tablespace，如果一个表空间的数据文件都满了，则这个表空间不会出现在dbafreespace中 ）
select b.tablespace_name,
a.maxsize max_M,
a.total total_M,
b.free free_M,
round((b.free / a.total) * 100) "% Free"
from (select tablespace_name,
sum(bytes / (1024 * 1024)) total,
sum(MAXBYTES / (1024 * 1024)) maxsize
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";--如下为标准版
select b.tablespace_name,
a.maxsize max_M,
a.total total_M,
b.free free_M,
round(((a.maxsize + b.free - a.total) / a.maxsize) * 100) "% Free"
from (select tablespace_name,
sum(bytes / (1024 * 1024)) total,
sum((decode(AUTOEXTENSIBLE, 'NO', BYTES, MAXBYTES)) /
(1024 * 1024)) maxsize
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";查看临时表空间使用率
方法一
/* Formatted on 2019/11/6 10:13:28 (QP5 v5.326) */
SELECT temp_used.tablespace_name,
ROUND (total),
used,
ROUND (total - used) AS "Free",
ROUND (NVL (total - used, 0) * 100 / total, 1) "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 (DECODE (autoextensible, 'YES', MAXBYTES, bytes))
/ 1024
/ 1024
total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name=temp_total.tablespace_name;方法二
/* Formatted on 2019/11/6 10:13:40 (QP5 v5.326) */
SELECT a.tablespace_name,
ROUND (a.BYTES / 1024 / 1024)
total_M,
ROUND (a.bytes / 1024 / 1024 - NVL (b.bytes / 1024 / 1024, 0))
free_M,
ROUND (b.bytes / 1024 / 1024)
used,
ROUND (b.using / 1024 / 1024)
using
FROM ( SELECT tablespace_name,
SUM (DECODE (autoextensible, 'YES', MAXBYTES, bytes))
bytes
FROM dba_temp_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name,
SUM (bytes_cached) bytes,
SUM (bytes_used) using
FROM v$temp_extent_pool
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name(+);真实使用率：
SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "FREE_SPACE(M)"
FROM (SELECT tablespace_name,
Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name=F.tablespace(+)
AND D.tablespace_name in ('TEMP', 'TEMP1')查询undo表空间使用情况
select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status;查询使用undo比较多的SQL
select *from ( select maxqueryid, round(sum(undoblks )*8/1024) consumed_size_MB from v$undostat group by maxqueryid order by consumed_size_MB desc ) where rownum<10;估计undo需要多大
/* Formatted on 2019/11/6 10:14:09 (QP5 v5.326) */
SELECT (UR * (UPS * DBS/1024/1024)) AS "Mb"
FROM (SELECT MAX (tuned_undoretention) AS UR FROM v$undostat),
(SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
FROM v$undostat
WHERE undoblks=(SELECT MAX (undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name=(SELECT UPPER (VALUE)
FROM v$parameter
WHERE name='undo_tablespace'));产生undo的当前活动会话是哪些
方法一
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name, a.value, d.used_urec, d.used_ublk FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d WHERE a.statistic#=b.statistic# AND a.inst_id=c.inst_id AND a.sid=c.sid AND c.inst_id=d.inst_id AND c.saddr=d.ses_addr AND b.name='undo change vector size' AND a.value>0 ORDER BY a.value DESC;方法二
select s.sid,s.serial#,s.sql_id,v.usn,r.status, v.rssize/1024/1024 mbfrom dba_rollback_segs r, v$rollstat v,v$transaction t,v$session sWhere r.segment_id=v.usn and v.usn=t.xidusn and t.addr=s.taddrorder by 6 desc;查看ASM磁盘组使用率
select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例" from gv$asm_diskgroup;统计每个用户使用表空间率
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 "用户", "表空间名";查看闪回区\快速恢复区空间使用率
select sum(percent_space_used)||'%' "已使用空间比例" from V$RECOVERY_AREA_USAGE 或 select round(100*(a.space_used/space_limit),2)||'%' "已使用空间比例",a.* from v$recovery_file_dest a;查看僵死进程，分两种（一种是会话不在的，另一种是会话标记为killed的但是会话还在的）
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 SPID32. 查看行迁移或行链接的表
select * From dba_tables where nvl(chain_cnt,0)<>0chain_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.
*数据缓冲区命中率（百分比小于90就要加大db_cache_size） *
SELECT 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';共享池命中率（百分比小于90就要加大sharedpoolsize）
以下两者可以根据个人理解运用
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;查询归档日志切换频率
select sequence#,
to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,
round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,
1) minutes
from v$log_history
where first_time > sysdate - 3
order by first_time, minutes;或
SELECT TO_CHAR(first_time, 'MM/DD') DAY,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23,
COUNT(*) TOTAL
FROM (SELECT ROWNUM RN, FIRST_TIME
FROM V$LOG_HISTORY
WHERE first_time > sysdate - 18
and FIRST_TIME > ADD_MONTHS(SYSDATE, -1)
ORDER BY FIRST_TIME)
GROUP BY TO_CHAR(first_time, 'MM/DD')
ORDER BY MIN(RN);查询lgwr进程写日志时每执行一次lgwr需要多少秒，在state是waiting的情况下，某个等待编号seq#下，secondsinwait达多少秒，就是lgwr进程写一次IO需要多少秒
select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'查询没有索引的表
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)查询一个AWR周期内的平均session数、OS平均负载、平均db time、平均每秒多少事务
select to_char(max(BEGIN_TIME), 'yyyy-mm-dd hh24:mi') ||
to_char(max(end_time), '--hh24:mi') time,
snap_id,
trunc(sum(case metric_name
when 'Session Count' then
average
end),
2) sessions,
trunc(sum(case metric_name
when 'Current OS Load' then
average
end),
2) OS_LOAD,
(trunc(sum(case metric_name
when 'Database Time Per Sec' then
average
end),
2) / 100) *
(ceil((max(end_time) - max(BEGIN_TIME)) * 24 * 60 * 60)) Database_Time_second,
trunc(sum(case metric_name
when 'User Transaction Per Sec' then
average
end),
2) User_Transaction_Per_Sec
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;--Database Time Per Sec对应值的单位是百分一秒/每秒 --(/100)(ceil((max(end_time)-max(BEGIN_TIME))246060))是代表每个snap周期内的总秒数，oracle 两个时间相减默认的是天数,2460*60 为相差的秒数 --这个SQL查到的DB TIME比较准确，和awr上面的db time比较一致
查询产生热块较多的对象
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
AND e.block_id + e.blocks > b.dbablk;手工创建快照的语句
exec dbms_workload_repository.create_snapshot; AWR设置每隔30分钟收集一次报告，保留14天的报告
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>14*24*60, interval=>30); select * from dba_hist_wr_control;AWR基线查看和创建
select * from dba_hist_baseline; exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>7550,end_snap_id=>7660,baseline_name=>'am_baseline');导出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));导出最新ADDM的报告（需要sys用户）
select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id=( select max(t.task_id) from dba_advisor_tasks t, dba_advisor_log l where t.task_id=l.task_id and t.advisor_name='ADDM' and l.status='COMPLETED' ); select task_id,task_name,description from dba_advisor_tasks order by 1 desc select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id=XX 查询某个SQL的执行计划
select * from table(dbms_xplan.display_cursor('sql_id',0,' advanced '));上面的0表示v$sql.childnumber为0，如果一个sqlid在v$sql中有多行说明有多个childnumber，要看哪儿childnumber的执行计划，就写哪个的值，比如要看child_number为2的执行计划，就把上面sql的0改为2 。
官方文档对display_cursor这个函数的说明里面没有advanced这个参数值，只有BASIC、TYPICAL、ALL这几个，不过实践中发现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;查询Rman的配置信息
SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;查询Rman备份集详细信息（未过期的，过期并已删除的查不到）
SELECT B.RECID BackupSet_ID,
A.SET_STAMP,
DECODE(B.INCREMENTAL_LEVEL,
'',
DECODE(BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1,
'Incr-1级',
0,
'Incr-0级',
B.INCREMENTAL_LEVEL) "Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE(A.STATUS,
'A',
'AVAILABLE',
'D',
'DELETED',
'X',
'EXPIRED',
'ERROR') "STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.BYTES / 1024 / 1024 / 1024 "Size(G)",
A.COMPRESSED,
A.TAG "Tag",
A.HANDLE "Path"
FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
WHERE A.SET_STAMP=B.SET_STAMP
AND A.DELETED='NO'
ORDER BY A.COMPLETION_TIME DESC;查询Rman备份进度
SELECT SID, SERIAL#, opname,ROUND(SOFAR/TOTALWORK*100)||'%' "%_COMPLETE", TRUNC(elapsed_seconds/60) || ':' || MOD(elapsed_seconds,60) elapsed, TRUNC(time_remaining/60) || ':' || MOD(time_remaining,60) remaining, CONTEXT,target,SOFAR, TOTALWORK FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK !=0 AND SOFAR <> TOTALWORK; 查询执行过全表扫描的sql语句的SQLID和sqlfulltext
select s.sid,s.serial#,s.inst_id,s.sql_id,s.username,s.target,s.ELAPSED_SECONDS,s.START_TIME,s.LAST_UPDATE_TIME,v.sql_fulltext from gv$session_longops s,gv$sql v where s.OPNAME='Table Scan' and s.SQL_PLAN_OPERATION='TABLE ACCESS' and s.SQL_PLAN_OPTIONS='FULL' and s.sql_id=v.sql_id order by s.LAST_UPDATE_TIME desc 查询死事务需要多长的回滚时间
X$KTUXE：[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)X$KTUXE表的一个重要功能是，可以获得无法通过v$transaction来观察的死事务信息，当一个数据库发生异常中断，或者进行延迟事务恢复时，数据库启动后，无法通过V$TRANSACTION来观察事务信息，但是X$KTUXE可以帮助我们获得这些信息。该表中的KTUXECFL代表了事务的Flag标记，通过这个标记可以找到那些Dead事务：
SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL; KTUXECFL COUNT(*) ------------------------ ---------- DEAD 1 NONE 2393 KTUXESIZ用来记录事务使用的回滚段块数，可以通过观察这个字段来评估恢复进度,例如如下事务回滚经过测算需要大约3小时：
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL='DEAD'; ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ ---------------- ---------- ---------- ---------- ---------- FFFFFFFF7D07B91C 10 39 2567412 1086075
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL='DEAD'; ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ ---------------- ---------- ---------- ---------- ---------- FFFFFFFF7D07B91C 10 39 2567412 1086067
SQL> declare l_start number; l_end number; begin select ktuxesiz into l_start from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39; dbms_lock.sleep(60); select ktuxesiz into l_end from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39; dbms_output.put_line('time_H:'|| round(l_end/(l_start -l_end)/60,2)); end; /
time_H:3 把XXX用户下面的某些YYY表赋权给user,XXX\YYY要大写
set serveroutput on --XXX要大写 declare tablename varchar2(200); begin for x IN (SELECT * FROM dba_tables where owner='XXX' and table_name like '%YYY%') loop tablename:=x.table_name; dbms_output.put_line('GRANT SELECT ON XXX.'||tablename||' to user'); EXECUTE IMMEDIATE 'GRANT SELECT ON XXX.'||tablename||' TO user'; end loop; end; Oracle查出一个用户具有的所有系统权限和对象权限
系统权限（和用户自己查询select * from session_privs的结果一致）
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='用户名' UNION ALL SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='用户名');对象权限（和用户自己查询select * FROM TABLE_PRIVILEGES where GRANTEE='当前用户'的结果一致）
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='用户名' UNION ALL SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='用户名');查询某个用户拥有的角色
select * from dba_role_privs where GRANTEE='用户名'; 查询拥有DBA角色权限的用户
select * from dba_role_privs where GRANTED_ROLE='DBA'; 查询某个角色拥有的系统权限
select * from ROLE_SYS_PRIVS where role='角色名' 清除某个SQL的执行计划
Exec DBMS_SHARED_POOL.PURGE('v$sqlarea.ADDRESS,v$sqlarea.HASH_VALUE','c') 查询密码是否有过期限制，默认是180天，一般修改为unlimited
select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD%'; ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED 查询和修改隐含参数（必须在sysdba权限下操作）
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx=b.indx and a.ksppinm like '%_small_table_threshold%' alter system set "_small_table_threshold"=value scope=both sid='*'; 不加sid则说明在默认在RAC的所有实例中修改 需要注意的是一定要加上双引号, 另外引号内不能有空格, 只能包含参数的名字
评估PGA该设置多少
select PGA_TARGET_FOR_ESTIMATE from (select * from V$PGA_TARGET_ADVICE where ESTD_OVERALLOC_COUNT=0 order by 1) where rownum=1; 评估SGA该设置多少
select SGA_SIZE from (select * from V$SGA_TARGET_ADVICE where ESTD_DB_TIME_FACTOR=1 order by 1) where rownum=1; 查看shared pool还剩多少
select * from v$sgastat where name='free memory' and pool='shared pool'; 统计所有表的容量大小(含分区字段、LOB字段) 一般先执行select distinct SEGMENTTYPE from dbasegments where owner<>'SYS' and tablespacename<>'SYSAUX'查看到所有的segmenttype
如下SQL就足够了
SELECT owner, table_name, TRUNC(sum(bytes) / 1024 / 1024) AS "SIZE(MB)"
FROM (SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type='TABLE'
UNION ALL
SELECT s.segment_name table_name, pt.owner, s.bytes
FROM dba_segments s, dba_part_tables pt
WHERE s.segment_name=pt.table_name
AND s.owner=pt.owner
AND s.segment_type='TABLE PARTITION'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name=i.index_name
AND s.owner=i.owner
AND s.segment_type='INDEX'
UNION ALL
SELECT pi.table_name, pi.owner, s.bytes
FROM dba_part_indexes pi, dba_segments s
WHERE s.segment_name=pi.index_name
AND s.owner=pi.owner
AND s.segment_type='INDEX PARTITION'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name=l.segment_name
AND s.owner=l.owner
AND s.segment_type='LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name=l.index_name
AND s.owner=l.owner
AND s.segment_type='LOBINDEX'
union all
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name=l.segment_name
AND s.owner=l.owner
AND s.segment_type='LOB PARTITION')
GROUP BY owner, table_name
HAVING SUM(bytes) / 1024 / 1024 > 10
ORDER BY SUM(bytes) desc查看当前会话的SID
select * from V$MYSTAT where rownum<2 查询某个SID的某个统计信息，比如consistent gets一致性读
select A.SID,A.STATISTIC#,A.VALUE SID_VALUE,B.NAME,B.VALUE ALL_SID_VALUE from V$SESSTAT A ,V$SYSSTAT B where A.STATISTIC#=B.STATISTIC# and A.SID=1187 and B.NAME='consistent gets' V$SYSSTAT统计整个DB的统计信息，V$SYSSTAT已经取代了V$STATNAME，并且多了VALUE这一列 V$SESSTAT统计每个用户的统计信息 查询某个SID的某个等待事件的信息，比如log file sync
select A.SID,A.EVENT,C.NAME,C.PARAMETER1,C.PARAMETER2,C.PARAMETER3, A.TIME_WAITED SID_TIMEWAITED,B.TIME_WAITED ALL_SID_TIMEWAITED,A.TOTAL_WAITS SID_TOTALWAITS,B.TOTAL_WAITS ALL_SID_TOTALWAITS from V$SESSION_EVENT A ,V$SYSTEM_EVENT B,V$EVENT_NAME C where A.EVENT=B.EVENT and A.EVENT=C.NAME and A.SID=1 and C.NAME='log file sync'V$SESSION_EVENT描述每个用户的等待事件信息 V$SYSTEM_EVENT描述整个DB等待事件信息 V$EVENT_NAME描述等待事件信本身的信息(比如 V$ACTIVE_SESSION_HISTORY的P1TEXT、P2TEXT、P2TEXT匹配 V$EVENT_NAME的PARAMETER1、PARAMETER2、PARAMETER3)
RAC跨节点杀会话
alter system kill session 'SID,serial#,@1' --杀掉1节点的进程 alter system kill session 'SID,serial#,@2' --杀掉2节点的进程 Truncate 分区的SQL
ALTER TABLE table_name TRUNCATE PARTITION p1 DROP STORAGE UPDATE GLOBAL INDEXES; Drop分区的SQL
ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES; DATAGUARD主备延迟多少时间的查询方法 备 库
select value from v$dataguard_stats where name='apply lag'或 备库
sqlplus>select ceil((sysdate-next_time)*24*60) "M" from v$archived_log where applied='YES' AND SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE applied='YES'); 查看某个包或存储过程是否正在被调用,如果如下有结果，则此时不能编译，否则会锁住
select * from V$DB_OBJECT_CACHE where pin>0 and name=upper('XX')查询数据库打补丁的记录
select * from dba_registry_history;查询某表的索引字段的distinct行数和CLUSTERING_FACTOR信息
select a.table_name,
a.index_name,
b.COLUMN_NAME,
a.blevel,
a.distinct_keys,
A.CLUSTERING_FACTOR,
A.NUM_ROWS,
trunc((a.distinct_keys / A.NUM_ROWS), 2) * 100 || '%' "distinct%",
trunc((a.CLUSTERING_FACTOR / A.NUM_ROWS), 2) * 100 || '%' "CLUSTERING_FACTOR%"
from DBA_IND_STATISTICS a, DBA_IND_COLUMNS b
where a.table_name='XX'
and a.INDEX_NAME=b.index_name
order by 5 desc查询某表的所有字段的distinct行数
select a.table_name,
b.num_rows,
a.column_name,
a.data_type,
a.data_length,
a.num_distinct,
trunc((a.num_distinct / b.num_rows), 2) * 100 || '%'
from dba_TAB_COLS a, dba_tables b
where a.table_name='XX'
and a.table_name=b.table_name
order by 6 desc查询5G以上空闲空间可以进行收缩的数据文件
者介绍
梁敬彬，福富研究院副理事长、公司唯一四星级内训师，国内一线知名数据库专家，在数据库优化和培训领域有着丰富的经验。多次应邀担任国内外数据库大会的演讲嘉宾，在业界有着广泛的影响力。著有多本畅销书籍，代表作有《收获，不止Oracle》。文末将有梁老师的新书《收获，不止SQL优化》大彩蛋哦~
SQL优化是一个复杂的工程，首先要讲究从整体到局部。今天我们首先学习关于数据库整体优化都有哪些性能工具，接着分析这些工具的特点，并结合案例进行探索，最后再进行总结和思考。
总体学习思路如下图所示：
都有哪些性能工具
这里首先要分成两部分：一种是不同调优场景的分析，可分为单纯场景的优化和复杂场景的优化；而另一种是基于这些场景的工具应用，就是针对单纯场景的优化手段和复杂场景的优化手段。
1、不同调优场景分析
我们继续探讨，单纯是有多单纯呢？哦，其实可以理解为无菌真空实验室里的实验。比如一条SQL很慢，原因是未走高效的索引查询而走全表扫描，加个索引就快了，执行速度从10s变成了0.1s；或者一条SQL执行速度被优化到1s左右，逻辑读控制在50个左右，应该就已经OK。这就是单纯的环境，我们差不多无须再考虑优化了。
那啥是复杂呢？那就是，刚才那个语句加了索引后，本应该从10s变成0.1s，结果还是10s，甚至变成30s了，这是咋回事呢？原来，现在系统是整体出问题了，数据库主机资源耗尽，啥语句都跑不快的。
还有那个逻辑读在50左右的SQL，如果一天执行几百几千万次，这要是能将逻辑读降低一点，得省多少的逻辑读啊。原来复杂环境真的很复杂，要考虑SQL本身没问题而是被环境影响，还要考虑SQL的执行频率，判断其调优价值与调优空间，这些在单纯的环境里，是不用考虑的。
2、不同场景对应工具
接下来，我们说说这两种场景对应的工具的使用。关于局部分析调优工具，这个其实就是在说SQL的执行计划了，这是SQL优化最重要的手段之一，通过分析执行计划，我们可以知道SQL语句的访问路径，知道它慢在哪里，从而进行SQL优化。由于在随后的章节中我们会详细介绍执行计划相关知识，这里就不再细述了。
关于整体的调优工具，这里我们先撇开主机、网络、存储等层面的因素，暂时从数据库的整体层面入手。主要工具有AWR、ASH、ADDM、AWRDD这四个工具。其中AWR是关注数据库的整体性能的报告；ASH是数据库中的等待事件与哪些SQL具体对应的报告；ADDM是Oracle给出的一些建议；而AWRDD是Oracle针对不同时段的性能的一个比对报告，比如今天早上9点系统很慢，而昨天这个时候很正常，很多人就想知道今天早上9点和昨天早上9点有什么不同，于是就有了这个报告。
整体分析调优是必需的，那么我们对此的学习也有规律可循。首先是获取系统整体信息的手段，一般通过报告和日志获取。好比破案一样，这就是收集证据的阶段。接下来要找到蛛丝马迹，那就是如何发现问题。在本书中就是需要关注提取到的这些报告的哪些要点、哪些关键字，具体流程图如下：
整体性能工具的要点
现代人对健康都比较重视，每年都会进行健康体检。其实数据库性能工具的应用（报告获取和关注要点）和体检是非常类似的。
1. 报告的获取
Oracle性能报告分成AWR、ASH、ADDM、AWRDD和AWRSQRPT这5个类型。
什么？这么多，好复杂啊，记也记不住，我不想听不想听！
别急，你只要去医院体检过，你就能听懂。
Really?
我们去医院体检，最终会得到一份体检报告，往往能看到很多总体性指标，这些指标会判断你是否健康。没毛病最好，万一有毛病，报告里要进一步判断是什么毛病，是高血压，还是骨质增生，还是胃有毛病……这就是现实中的体检报告。
而Oracle提供的一种性能收集和分析工具，它能提供一个时间段内整个系统资源使用情况的报告，这个报告里有很多总体性指标来判断系统是否健康。没毛病最好，万一有毛病，问题出在什么模块，是日志切换过于频繁，还是硬解析过大，还是某些SQL相关等待事件在耗资源……这就是AWR报告。这样看来，体检报告和AWR报告非常类似。
假设体检报告说你有胃病，很可能只告诉你胃有问题，却无法告诉你具体啥毛病，因为你手上的体检报告不会详细到拥有你胃部所有相关指标。你要得到这些指标需要做进一步信息收集，那就是胃镜。同样假设你的数据库是SQL相关等待事件问题，AWR报告很可能只告诉你有这个问题而无法告诉你是哪些SQL引发的。你要得到这些指标，想了解具体某些SQL和相关等待事件的对应需要做进一步的信息收集，那就是ASH报告。看来对比胃镜和ASH报告，二者也非常类似。
刚才说的胃病，或许是医生告诉你的，因为上面有很多指标你无法读懂，这时如果你能拿到一张医生的病历卡记录，这里没有指标，只有白底黑字用文字描述的病情，告诉你要如何治疗，那你一定会看得很明白。同样假设，如果将含各种晦涩的指标的数据库体检报告用一些白底黑字的文字代替，用文字直接说明数据库遇到了什么问题，告诉你该如何去优化，那新手一定会看得很明白，这就是ADDM报告。看来病历卡记录和ADDM报告，二者也非常类似。
假如你在一年前也做过体检，并将报告带到了医院，负责任的医生就一定会让你将旧的体检报告也提供给他。他会认真地比对两张报告，查看他关注的健康指标是否有异常波动，这些波动对医生很有参考意义，往往预示着病情的发展趋势。好了，别紧张，这只是比喻。
假设你有系统新旧两个时段的两份AWR报告，负责任的DBA一定会让你将旧的AWR报告也提供给他。他会认真地比对两份报告，查看他关注的数据库指标是否有异常波动，这些波动对DBA很有参考意义，往往预示着数据库性能瓶颈的发展趋势。Oracle提供了一个工具能够将两个时段的AWR报告合并，并能方便地显示出比对信息，这个工具就是AWRDD。看来医生分析前后两次体检报告的动作和AWRDD报告比起来，两者也非常类似。
大家知道做胃镜是一件很麻烦的事(类似ASH报告)，如果没毛病就没必要让我们遭这罪。可万一体检报告无情地告诉你胃有毛病，甚至是医生分析你前后两次体检报告（类似ADDM）后告诉你胃病在加速中，你被迫无奈只好去做胃镜了。做完后医生发现你胃部有大量息肉，却无法判断这些息肉是否为良性。于是还要做进一步的检查，这就是活检。不要紧张，平时注意健康生活就好。同样ASH报告判断出某些SQL有问题，却无法得到执行计划等更详细的信息，只能依靠AWRSQRPT去获取这些信息。看来活检和AWRSQRPT报告比起来，两者也非常类似。
最后恭喜你，活检报告显示未产生癌变，只要好好治疗，注意身体，胃就能恢复健康！看本书的读者们，你们都是IT人士，生活无规律加班熬夜者居多，一定要注意身体哦！
对了，还有一件最重要的事没交代。大家似乎搞懂了Oracle五大性能报告，可是这些好东西在哪里才能得到呢？别着急，后续章节马上就会告诉你如何获取这五大性能报告。
2. 报告的关注点
如果患者拿着有各种晦涩指标的体检报告来到门诊请教医生，他一定会关注各种指标来判断患者具体是什么毛病。同样你也会对Oracle的性能报告中的各种指标进行关注来判断数据库出了什么毛病。两者非常类似，关注不同的指标，都是为了施救，前者救人，后者救数据库。
听起来是不是很激动，恨不得马上就要开始当救库英雄了！别急，接下来还要告诉你关注什么，然后在案例中让你感受一下什么叫救库英雄。
特别提醒：
这里有一个特别值得注意的地方，那就是性能报告的采样时间。Oracle默认是每小时产生一个采样点，你可以收集每个小时的性能报告。我们对此要敏感，比如你的性能故障是发生在今天早上7点～8点。然后系统自动恢复了，你获取一张8点～9点的性能报告来查问题，就毫无意义了。
3、五大性能报告的获取1AWR的获取与说明
获取AWR报告的方式有两种：一种是直接获取方式，调后台脚本awrrpt.sql来获取，执行方式一般是在sqlplus下执行@?/rdbms/admin/awrrpt.sql；
另一种则是通过调用命令包，获取dbms_workload_repository这个包的awr_report_html程序，用SQL命令的形式输出内容。
Select output from table(dbms_workload_repository.awr_report_html
(v_dbid, v_instance_number,v_min_snap_id,v_max_snap_id))
（1）直接获取
试验1（未使用批量提交）：
接下来通过提示就可以生成AWR报告了，具体步骤略去，详情请扫本章最后的二维码。
试验2（单机下，正确使用批量提交）：
接下来通过提示就可以生成awr报告了，具体步骤略去。
（2）通过调用命令包获取
直接调用工具包的方式，特别适合用在程序自动获取报告的场景。
注：其中977587123是数据库的主机标识，可以在数据库的数据字典中查到，1是标识实例，如果是RAC，就有1和2两个，单机就只有1。1920和1921是两个断点时间，比如9点和10点之间。
2ASH的获取与说明
获取ASH报告的方式也有两种：一种是直接获取方式，调后台脚本ashrpt.sql来获取，执行方式一般是在sqlplus下执行@?/rdbms/admin/ashrpt.sql；另一种则是通过调用命令包，获取dbms_workload_repository这个包的ash_report_html程序。用SQL命令的形式输出内容。
select output from table(dbms_workload_
repository.ash_report_html( dbid,inst_num,l_btime,l_etime)
（1）直接获取
说明：
如果你是一路回车，就是获取最近5分钟的ASH报告。
如果你根据Oldest ASH sample available 时间，然后回车，选择的是目前可收集的最长ASH运行情况。
你可以选择Oldest ASH sample available和Latest ASH sample available之间时间，然后输入时长，比如30表示30分钟，取你要取的任何时段的ASH报告。
ASH报告的获取不同于AWR的地方在于，快照之间有无重启动作不影响报告的获取。
ASH报告可以直接手工获取，比如select output from table(dbms_workload_ repository.ash_report_html( dbid,inst_num,l_btime,l_etime)。
（2）通过调用命令包获取
直接调用工具包的方式，特别适合用在程序自动获取报告的场景。
注：其中977587123是数据库的主机标识，可以在数据库的数据字典中查到，1是标识实例，如果是RAC，就有1和2两个，单机就只有1。SYSDATE-30/1440,SYSDATE-1/1440 分别是开始时间和结束时间。
3ADDM的获取与说明
获取ADDM报告的方式也有两种，一种是直接获取方式：调后台脚本addmrpt.sql来获取，执行方式一般是在sqlplus下执行@?/rdbms/admin/addmrpt.sql。另一种则是通过调用命令包的方式获取：调用dbms_workload_repository这个包的addm_report_html程序。用SQL命令的形式输出内容。
- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name=> 'ADDM',
task_name=> 'MYADDM',
task_desc=> 'MYADDM');
（1）直接获取
@?/rdbms/admin/addmrpt.sql
具体执行过程略去。
（2）通过调用命令包获取
注：直接调用工具包的方式，适合用在自动获取报告的场景。
4AWRDD的获取与说明
获取AWRDD报告一般是用直接获取的方式，这个脚本的交互部分需要输入要进行对比的两个awr报告的begin snap_id与end snap_id，然后输入对比结果报告的名称，这里就不详细介绍了，请读者自行试验完成。
直接获取：
@?/rdbms/admin/awrddrpt.sql
具体略去。
5AWRSQ获取与说明
获取AWRSQRPT报告的关键之处在于，交互部分要输入所要分析的SQL的SQL_ID，这是关键之处。而这个SQL_ID可以从AWR报告中获取。
以上5个报告的获取本身并不难，操作一遍就会了，笔者也会再提供在线操作视频，让大家实际体会一遍。现在关键在于，要明白这5个报告的作用和相互之间的区别，搞懂这些，调优之路就算完成过半了。当然，接下来如何分析读懂这五大报告的关键指标就非常重要了，有一些指标你必须关注，否则你就当不了“医生”了。
4、五大报告关注的要点1AWR的关注点
AWR报告是五大报告中最全面最重要的一个报告，它的相关指标也显得格外重要。这里我们列出DB Time、load_profile、efficiency percentages、top 5 events、SQL Statistics、Segment_statistics这6个指标入手分析。
（1）AWR关注点1之DB Time
DB Time这个指标主要用来判断当前系统有没有遇到相关瓶颈，是否较为繁忙导致等待时长很长。一般来说，Elapsed时间乘以CPU个数的时间如果结果大于DB Time，我们认为系统压力不大，反之则压力较大。如下例子中，60.11×64=3847.04<5990.6，说明系统现在还是比较繁忙的。
（2）AWR关注点2之load_profile
load_profile这个指标主要用来展现当前系统的一些指示性能的总体参数，比如经典的Redo size就是用来显示平均每秒的日志尺寸和平均每个事务的日志尺寸，结合Transactions这个每秒事务数的指标，就可以分析出当前事务的繁忙程度。
下图中显示每秒有6777.1个事务数，这在现实中几乎不可能，现实中的运营商系统一般在200上下比较正常，超过1000就属于非常繁忙了。
把上图和下面的图进行比较，就非常明显了，下图显示每秒有0.6个事务，平均每个事务产生的日志尺寸是7位数。这说明系统是一个提交不频繁的处理大任务事件的系统。而上图的尺寸是3位数。这里非常容易看出，这是一个提交非常频繁且每个事务都非常小的密集提交系统。
（3）AWR关注点3之efficiency percentages
efficiency percentages是一些命中率指标，其中Buffer Hit、Library Hit等都表示SGA（System global area）的命中率。在下图中Soft Parse指标表示共享池的软解析率，在OLTP系统中如果该指标低于90%应当引起你的注意，这表示存在未使用绑定变量的情况。我们通过比对两个报告，可以看出明显差异，如下面系列图所示。
报告1（未有效地使用绑定变量，产生大量硬解析的场景）。
报告2（有效地使用绑定变量，进行绑定变量优化后的场景）。
（4）AWR关注点4之top 5 events
等待事件是衡量数据库整体优化情况的重要指标，通过观察Top 5 Timed Foreground Events模块的Event和%DB time两列，可以非常直观地看出当前数据库面临的主要等待事件是什么。下图两个例子分别告诉我们数据库面临锁等待和日志切换等待的情形。
（5）AWR关注点5 之SQL Statistics
SQL Statistics分别从几个维度来罗列出TOP的SQL，这是一种简单粗暴但有效的方法。看看执行时长，直接拿出来优化一般都是对的做法。
（6）AWR关注点6 之Segment Statistics
使用Segment Statistics指标进行寻找和判断，也是一个非常直接的优化手段。当我们知道繁忙落在数据库的那个表段是索引段时，优化就变得相对简单了，比如最简单粗暴的方法就是对表和索引进行数据清理和瘦身。
2ASH的关注点
ASH是啥？哦，有人想起来了，胃镜。
完成了ASH报告的获取后，打开获得的ASH报告，其实对于该报告可关注的东西非常直接，就是看看哪些SQL和哪些等待事件是相关联的。
如下图所示：
3ADDM的关注点
ADDM是啥？哦，是医生的门诊报告。
由于这是Oracle的一些分析建议，所以ADDM的阅读非常简单，基本上从FINDING 1、FINDING 2顺序往下看就可以了。一般是从数据库整体配置和局部SQL两方面给出建议。我们看看都能明白，如下图所示：
整体性的建议
局部SQL建议
4AWRDD的关注点
AWRDD是啥？哦，是医生在看你前后两次体检报告，在比较指标的变化。其实这个关注点很简单，基本上就是AWR关注什么，AWRDD就关注什么，没什么特别的，简单列举如下。
（1）AWRDD关注点1 之不同时期load profile的比较
（2）AWRDD关注点2 之不同时期等待事件的比较
（3）AWRDD关注点3 之不同时期TOP SQL的比较
5AWRSQRPT的关注点
AWRSQRPT是啥？哦，有人想起来了，活检。别打颤！
其实没啥，就是看看AWR和ASH里看不到的东西。都有啥呢？比如执行计划的相关细节，关于执行计划我们会在后面详细说明。这里要特别注意一点，Oracle的执行计划可能会随着环境的变化而变化，会随着数据的变化而变化，因此可能会产生多个执行计划，这个AWRSQRPT就会出现多个执行计划。具体详见下面系列图。
（1）Plan statistics
（2）Execution Plan
（3）是否有多个执行计划
案例的分享与交流
说了这么多，我们来看几个相关案例，体会使用工具进行整体优化的重要性。
1、和并行等待有关的案例
这是来自某政府系统的一个平台的案例，请看下图，这是AWR报告的Top 5 Timed Events的展现，可以看出当前数据库的等待事件主要是PX Deq相关的等待，这属于滥用并行等待导致系统资源紧张的一个案例。
该案例暴露出的问题比想象中更严重，因为该系统的不少表和索引的属性被设置了并行度，这导致所有对这些表和索引的访问都成了并行访问。后续解决思路就是将表和索引的并行属性去掉。将一些需要并行处理的大任务进行时间切割，确认部分大任务是可以放在凌晨业务低峰期执行的，就设置了并行的Hint任务，让部分SQL在夜间并行执行，大部分SQL在白天正常执行，从而系统恢复正常，业务也能顺利开展。
2、和热块竞争有关的案例
接下来我们再看一个案例，这是某运营商的系统，从AWR报告的Top 5 Timed Events等待事件主要是gc buffer busy来看，当前系统主要等待事件是热块竞争的等待。
等待事件对应的SQL主要有哪些，我们其实可以通过对应时间段的ASH报告分析出来，比如下图就是和AWR的对应。
将AWR报告和ASH报告结合起来看，往往可以找出具体需要优化的SQL。在本案例中，我们发现两个节点共同访问一些对象导致热块竞争。后续通过一系列改造，让不同的业务跑在不同的节点上，从而避免了两个节点访问同一个对象，问题得以缓解。
3、和日志等待有关的案例
这是一个典型的案例，从Transactions达到800多，可以看出事务非常繁忙，再从Per Transaction才1000左右，可以看出每个事务非常小。这说明了系统存在事务未批量提交的情况。这种情况一般出现在循环中，把提交写到循环里面的情况。后续通过排查，发现果真是如此原因。
接下来的log file switch(checkpoint incomplete) 和log file sync的相关等待正是由于日志切换过于频繁导致的等待，这正是如前所述，未批量提交导致。
4、新疆某系统的前台优化
如下是新疆某运营商的优化案例，我们通过Top 5 Timed Events等待事件发现了瓶颈主要在IO。接下来我们迅速到Tablespace IO Stats模块去查看，如下图所示：
果然是有点问题。这个AV RD(MS)项表示平均一次物理读花费的时间（单位为ms）。有一种说法是， AV RD(MS)大于7就说明系统有严重的IO问题，其中BOSSWG_PERF_DATA居然达到了47，这说明当前的存储IO存在瓶颈。后续通过改善存储解决了问题。
5、浙江某系统的调优案例
这个案例来自浙江某生产系统，我们通过Top 5 Timed Events等待事件发现了瓶颈主要在gc buffer busy等待事件，这和新疆某系统的前台优化案例类似。不过AWR报告非常强大，你通过各个细节都可以很有收获，从而找到解决问题的方法，比如你此时直接定位到Segments by Global Cache Buffer Busy模块，如下图所示：
通过观察segments by global cache buffer busy的对象，我们找到了相关需要优化的表。最后我们结合业务，通过对该表瘦身、增加分区、避免两个节点同时访问的方案，优化了对应SQL的性能。
总结
End.
来源：公众号“DBAplus社群”
运行人员：中国统计网小编（微信号：itongjilove）
微博ID：中国统计网
中国统计网，是国内最早的大数据学习网站，公众号：中国统计网
http://www.itongji.cn
AWR 是 Oracle 10g 版本 推出的新特性， 全称叫Automatic Workload Repository-自动负载信息库 AWR 是通过对比两次快照(snapshot)收集到的统计信息，来生成报表数据，生成的报表包括多个部分。
前面已经对awr报告的WORKLOAD REPOSITORY report部分做了介绍，下面根据生产环境对Report Summary部分的一些关键参数做分析，内容比较多，大家尽量耐心看完，还是有点用的。
这里主要显示SGA中每个区域的大小（在AMM改变它们之后），可用来与初始参数值比较。
Buffer Cache：最终目的就是尽可能的减少磁盘I/O以便快速的读或写。也不是越大越好，如果Buffer Cache过大，会造成大的LRU 列表和 dirty list，引发逻辑读的过程消耗CPU量高。同时大的Buffer Cache也会增加DBWn 进程的负担。
shared pool主要包括library cache和dictionary cache。
library cache用来存储最近解析（或编译）后SQL、PL/SQL和Java classes等。 dictionary cache用来存储最近引用的数据字典。
发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。
我们可以看到shared pool一直收缩，在shrink过程中一些row cache 对象被lock住可能导致前台row cache lock等解析等待，最好别让shared pool shrink。如果这里shared pool一直在grow，那说明shared pool原有大小不足以满足需求(可能是大量硬解析)，结合后面的解析信息和SGA breakdown来一起诊断问题。
这里可以看到生产的硬解析每秒为102，硬解析为7.5，问题不大，但是Logons每秒3.3，表明可能有争用问题，下面针对每个指标具体分析：
这里主要显示数据库负载概况，将之与基线数据比较才具有更多的意义，如果每秒或每事务的负载变化不大，说明应用运行比较稳定。
单个的报告数据只说明应用的负载情况，绝大多数据并没有一个所谓“正确”的值，Logons大于每秒1~2个、Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题。
上述所有指标 的目标均为100%，即越大越好，在少数bug情况下可能超过100%或者为负值。
80%以上 %Non-Parse CPU
90%以上 Buffer Hit%, In-memory Sort%, Soft Parse%
95%以上 Library Hit%, Redo Nowait%, Buffer Nowait%
98%以上 Latch Hit%
这里主要显示Oracle关键指标的内存命中率及其它数据库实例操作的效率。其中Buffer Hit Ratio 也称Cache Hit Ratio，
Library Hit ratio也称Library Cache Hit ratio。
在一个使用直接读执行大型并行查询的DSS环境，20%的Buffer Hit Ratio是可以接受的，而这个值对于一个OLTP系统是完全不能接受的。
根据多年的经验，对于OLTP系统，Buffer Hit Ratio理想应该在90%以上。
Buffer Nowait表示在内存获得数据的未等待比例。在缓冲区中获取Buffer的未等待比率
Buffer Nowait的这个值一般需要大于99%。否则可能存在争用
buffer hit 高速缓存命中率，反应物理读和缓存命中间的纠结，表示进程从内存中找到数据块的比率，监视这个值是否发生重大变化比这个值本身更重要
Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低（可参考90%阀值），考虑增加LOG BUFFER。
当redo buffer达到1M时，就需要写到redo log文件，所以一般当redo buffer设置超过1M，不太可能存在等待buffer空间分配的情况。
library hit表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，
Oracle检查Library Cache确定是否存在解析过的版本，如果存在，Oracle立即执行语句；如果不存在，Oracle解析此语句，并在Library Cache中为它分配共享SQL区。
Latch Hit：Latch是一种保护内存结构的锁，可以认为是SERVER进程获取访问内存数据结构的许可。
要确保Latch Hit>99%，否则意味着Shared Pool latch争用，可能由于未共享的SQL，或者Library Cache太小，可使用绑定变更或调大Shared Pool解决。
要确保>99%，否则存在严重的性能问题。
Parse CPU to Parse Elapsd：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，越高越好。即：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。如果该比率为100%，意味着CPU等待时间为0，没有任何等待。
Non-Parse CPU ：SQL实际运行时间/(SQL实际运行时间+SQL解析时间)，太低表示解析消耗时间过多。
计算公式为：% Non-Parse CPU=round(100*1-PARSE_CPU/TOT_CPU),2)。如果这个值比较小，表示解析消耗的CPU时间过多。
Soft Parse：软解析的百分比（softs/softs+hards），近似当作sql在共享区的命中率
In-memory Sort：在内存中排序的比率，如果过低说明有大量的排序在临时表空间中进行。
Execute to Parse：是语句执行与分析的比例，如果要SQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。
Memory Usage %：对于一个已经运行一段时间的数据库来说，共享池内存使用率，应该稳定在75%-90%间，
如果太小，说明Shared Pool有浪费，而如果高于90，说明共享池中有争用，内存不足。
这个数字应该长时间稳定在75%～90%。如果这个百分比太低，表明共享池设置过大，带来额外的管理上的负担，从而在某些条件下会导致性能的下降。如果这个百分率太高，会使共享池外部的组件老化，如果SQL语句被再次执行，这将使得SQL语句被硬解析。这里显示生产环境比率低于75%，没有充分利用shared pool。
SQL with executions>1：执行次数大于1的sql比率，如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。
在一个趋向于循环运行的系统中，必须认真考虑这个数字。在这个循环系统中，在一天中相对于另一部分时间的部分时间里执行了一组不同的SQL语句。
在共享池中，在观察期间将有一组未被执行过的SQL语句，这仅仅是因为要执行它们的语句在观察期间没有运行。只有系统连续运行相同的SQL语句组，这个数字才会接近100%。
Memory for SQL w/exec>1：执行次数大于1的SQL消耗内存的占比。
这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。
这个数字将在总体上与% SQL with executions>1非常接近，除非有某些查询任务消耗的内存没有规律。
在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果Statspack报表的时间窗口足够大到覆盖所有的周期，
执行次数大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。
总结：通过ORACLE的实例有效性统计数据，我们可以获得大概的一个整体印象，但是不能由此来确定数据运行的性能。当前性能问题的确定，我们主要还是依靠后面介绍的等待事件来确认。
可以这样理解两部分的内容，hit统计帮助我们发现和预测一些系统将要产生的性能问题，这样可以做到未雨绸缪。而wait事件，就是表明当前数据库已经出现了性能问题需要解决，所以是亡羊补牢的性质。
后面会分享更多DBA方面的内容，感兴趣的朋友可以关注下！！
*请认真填写需求信息，我们会在24小时内与您取得联系。
