Oracle下日常维护常用SQL

查询Oracle正在执行的sql语句及执行该语句的用户
SELECT b.sid oracleID,

b.username 登录Oracle用户名,

b.serial#,

spid 操作系统ID,

paddr,

sql_text 正在执行的SQL,

b.machine 计算机名
F
FROM v$process a, v$session b, v$sqlarea c
W
WHERE a.addr = b.paddr

AND b.sql_hash_value = c.hash_value

或者
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT

from v$session a, v$sqlarea b
w
where a.sql_address = b.address

查询Oracle执行过的sql语句及执行该语句的用户
---执行过的

select a.USERNAME 登录Oracle用户名,

a.MACHINE 计算机名,

SQL_TEXT,

b.FIRST_LOAD_TIME,

b.SQL_FULLTEXT

from v$sqlarea b, v$session a

where a.sql_hash_value = b.hash_value

and b.FIRST_LOAD_TIME between '2016-11-01/09:24:47' and

'2016-11-31/09:24:47'

order by b.FIRST_LOAD_TIME desc;


查看正在执行sql的发起者的发放程序
SELECT OSUSER 电脑登录身份,

PROGRAM 发起请求的程序,

USERNAME 登录系统的用户名,

SCHEMANAME,

B.Cpu_Time 花费cpu的时间,

STATUS,

B.SQL_TEXT 执行的sql

FROM V$SESSION A

LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS

AND A.SQL_HASH_VALUE = B.HASH_VALUE

ORDER BY b.cpu_time DESC

v$sql、v$sqlarea 、v$sqltext
1
这三哥视图都可以用于查询共享池中已经解析过的SQL语句及其相关信息。
V$SQL中列出了共享SQL区中所有语句的信息，它不包含GROUP BY字句，并且为每一条SQL语句中单独存放一条记录；
V$SQLAREA中一条记录显示了一条共享SQL区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL语句的统计信息；
V$SQLTEXT包含了库缓存中所有共享游标对应的SQL语句。它将SQL语句分片显示。

查出oracle当前的被锁对象
SELECT l.session_id sid,

s.serial#,

l.locked_mode 锁模式,

l.oracle_username 登录用户,

l.os_user_name 登录机器用户名,

s.machine 机器名,

s.terminal 终端用户名,

o.object_name 被锁对象名,

s.logon_time 登录数据库时间
F
FROM v$locked_object l, all_objects o, v$session s

WHERE l.object_id = o.object_id

AND l.session_id = s.sid
O
ORDER BY sid, s.serial#;

kill掉当前的锁对象可以为
alter system kill session 'sid， s.serial#‘;
1
查看占io较大的正在运行的session
SELECT se.sid,

se.serial#,

pr.SPID,

se.username,

se.status,

se.terminal,

se.program,

se.MODULE,

se.sql_address,

st.event,

st. p1text,

si.physical_reads,

si.block_changes

FROM v$session se, v$session_wait st, v$sess_io si, v$process pr

WHERE st.sid = se.sid

AND st. sid = si.sid

AND se.PADDR = pr.ADDR

AND se.sid > 6

AND st. wait_time = 0

AND st.event NOT LIKE '%SQL%'

ORDER BY physical_reads DESC


查询碎片程度高
（实际使用率小于30%）的表，也就是可以收缩的表
条件为什么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",



r
round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"



F
FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3



o
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高的表
集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描
select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,



r
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"



f
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

或
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



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#, 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


根据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;


根据spid查询具体的sql语句
select ss.SID,

ss.SERIAL#,

ss.LOGON_TIME,

pr.SPID,

ss.action,

sa.SQL_FULLTEXT,

ss.machine,

ss.TERMINAL,

ss.PROGRAM,

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



查看历史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



t
tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB



t
tcp 0 0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB

出现两个，说明来自220，连接了228数据库服务器，但是又通过228服务器的dblink去连接了16服务器
查询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;


查询DDL锁的sql
查询x开头的动态性能视图，只能用sys用户
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



查询锁住的DDL对象
select d.session_id, s.SERIAL#, d.name

from dba_ddl_locks d, v$session s

where d.owner = 'CC'

and d.SESSION_ID = s.sid


查询当前正在执行的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



查询正在执行的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


查询正在执行的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
1

查询一个会话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;

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 <=5;


TOP 10 物理读排序（消耗IO排序，即最差性能SQL、低效SQL排序）
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


查询当前正在消耗temp空间的sql语句
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


查询需要使用绑定变量的sql,10G以后推荐第二种
（任何一条执行过的语句不管执行了几次在V$SQL中都只有一条记录，V$SQL中会记录执行了几次。两条一模一样的语句但是在不同的schema下执行的两种结果，如select * from t1.test在sye、system下执行则V$SQL只有一条记录(谁先执行则PARSING_SCHEMA_NAME显示谁)。如在sys和system都执行select * from test则V$SQL中有两条记录，两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样。同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录，说明V$SQL对应的sql语句必须一模一样，如果alter system flush shared_pool（主站慎用）后再执行一样的语句，发现语句在V$SQL中的SQL_ID和HASH_VALUE与之前的一样，说明SQL_ID和HASH_VALUE应该是oracle自己的一套算法来的，只是根据sql语句内容来进行转换，sql语句不变则SQL_ID和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 */



F
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)


查看数据文件可用百分比
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,



R
ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件总容量",



R
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",



R
ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "文件可用容量",



R
ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "文件可用百分比"



f
from dba_free_space a,dba_data_files b



w
where a.file_id=b.file_id



g
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE



o
order by b.tablespace_name;


查看数据文件可用百分比（文件自增长的情况下）
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,



R
ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量",



R
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",



R
ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)*100||'%' "文件可用百分比"



f
from dba_free_space a,dba_data_files b



w
where a.file_id=b.file_id and b.file_id>4



g
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES



o
order by b.tablespace_name;


查看表空间可用百分比
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";


查看临时表空间使用率（临时文件是AUTOEXTENSIBLE的情况下可能空闲率是0）
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


查询undo表空间使用情况
select tablespace_name, status, sum(bytes) / 1024 / 1024 M

from dba_undo_extents

group by tablespace_name, status


查看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
1
2
或
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 SPID
1
查看行迁移或行链接的表
select * From dba_tables where nvl(chain_cnt, 0) <> 0
1
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.
数据缓冲区命中率（百分比小于90就要加大db_cache_size）
查询V$SYSSTAT视图可以查看从内存中读取数据的频率。它提供了数据库中设置的数据块缓存区的命中率。这个信息可以帮助您判断系统何时需要更多的数据缓存(DB_CACHE_SIZE)，或者系统的状态何时调整得不佳(二者均将导致较低的命中率)。
通常情况下，您应当确保读数据的命中率保持在95%以上。将系统的命中率从98%提高到99%，可能意味着性能提高了100%(取决于引起磁盘读操作的语句)。
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';

或
SELECT 1 - (SUM(DECODE(NAME, 'physical reads', VALUE, 0)) /

(SUM(DECODE(NAME, 'db block gets', VALUE, 0)) +

(SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) "Read Hit Ratio"

FROM v$sysstat;

或者
在Oracle 10g中，也可以直接获得V$SYSMETRIC中的 AWR 信息:
select metric_name, value

from v$sysmetric

where metric_name = 'Buffer Cache Hit Ratio';


测定数据字典的命中率(V$ROWCACHE)
可以使用V$ROWCACHE视图来发现对数据字典的调用是否有效地利用了通过init.ora参数SHARED_POOL_SIZE分配的内存缓存.
如果字典的命中率不高，系统的综合性能将大受影响。推荐的命中率是95%或者更高。如果命中率低于这个百分比，说明可能需要增加init.ora参数SHARED_POOL_SIZE。
但要记住，在V$SGASTAT视图中看到的共享池包括多个部分，而这里仅仅就是其中之一。注意：在大幅度使用公共同名的环境中，字典命中率可能难以超过75%，即使共享池的尺寸很大。这是因为Oracle必须经常检查不存在的对象是否依旧存在。
SQL> select sum(gets),sum(getmisses),(1 - (sum(getmisses) / (sum(gets)+ sum(getmisses)))) * 100 HitRate from v$rowcache;



SUM(GETS) SUM(GETMISSES) HITRATE

---------- -------------- ----------

35555492 186408 99.4784608

在Oracle 10g中，也可以直接获得V$SYSMETRIC中的AWR信息:
SQL> select metric_name, value from v$sysmetric where metric_name ='Library Cache Hit Ratio';



M
METRIC_NAME VALUE

---------------------------------------------------------------- ----------
L
Library Cache Hit Ratio 98.7987987
L
Library Cache Hit Ratio 100


测定共享SQL和PL/SQL的命中率(V$LIBRARYCACHE)
访问V$LIBRARYCACHE视图可以显示实际使用的语句(SQL和PL/SQL)访问内存的情况。如果init.ora的参数SHARED_POOL_SIZE设置得太小，内存中就没有足够的空间来存储所有的语句。固定命中率通常应该是95%或更高，而重载的次数不应该超过1%。查询V$SQL_BIND_CAPTURE视图，看看每个SQL绑定是否太高，是否需要CURSOR_SHARING。
SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;



SUM(PINHITS)/SUM(PINS)*100
-
--------------------------

98.2787413813059

SQL> select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;



SUM(PINHITS-RELOADS)/SUM(PINS)
-
------------------------------

98.0597157838185

最好的方式：
select sum(pins) "Executions",

sum(pinhits) "Hits",

((sum(pinhits) / sum(pins)) * 100) "PinHitRatio",

sum(reloads) "Misses",

((sum(pins) / (sum(pins) + sum(reloads))) * 100) "RelHitRatio"

from v$librarycache;


查询 v$sql_bind_capture，看看 average binds 是否大于15 (issue):
select sql_id, count(*) bind_count

from v$sql_bind_capture

where child_number = 0

group by sql_id
h
having count(*) > 20 order by count(*);



确定需要固定的PL/SQL对象
的可用空间均成为许多零散的片段，而没有足够大的连续空间，这是共享池中的普遍现象。消除共享池错误的关键是理解哪些对象会引起问题。一旦知道了会引起潜在问题的PL/SQL对象，就可以在数据库启动时固定这个代码(这时共享池是完全连续的)。
select name, sharable_mem

from v$db_object_cache

where sharable_mem > 100000

and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')

and kept = 'NO';


通过V$SQLAREA查找有问题的查询
V$SQLAREA视图提供了一种识别有潜在问题或者需要优化的SQL语句的方法，从而可通过减少磁盘的访问来优化数据库的综合性能。
select b.username username,

a.disk_reads reads,

a.executions exec,

a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio,

a.command_type,

a.sql_text Statement

from v$sqlarea a, dba_users b

where a.parsing_user_id = b.user_id

and a.disk_reads > 100000

order by a.disk_reads desc;


检查用户的当前操作及其使用的资源
将V$SESSION和V$SQLTEXT连接就可以显示目前每一个会话正在执行的SQL语句。这在有些时候是极为有用的，例如DBA希望查看某一个给定的时间点上系统究竟执行了哪些操作。
select a.sid, a.username, s.sql_text

from v$session a, v$sqltext s

where a.sql_address = s.address

and a.sql_hash_value = s.hash_value

order by a.username, a.sid, s.piece;

select a.username,

b.block_gets,

b.consistent_gets,

b.physical_reads,

b.block_changes,

b.consistent_changes

from v$session a, v$sess_io b

where a.sid = b.sid

order by a.username;


查找磁盘I/O问题
视图V$DATAFILE、V$FILESTAT和V$DBA_DATA_FILES提供了数据库中所有数据文件和磁盘的文件I/O活动信息。理想情况下，物理的读和写应当平均分布。如果没有合理的配置系统，其综合性能就会受到影响。
select a.file#, a.name, a.status, a.bytes, b.phyrds, b.phywrts

from v$datafile a, v$filestat b

where a.file# = b.file#;


查询归档日志切换频率
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss')



f
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from



v
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
v$loghist where first_time>sysdate-3 order by 1;

或
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



F
FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time>sysdate-18



a
and FIRST_TIME>ADD_MONTHS(SYSDATE,-1) ORDER BY FIRST_TIME)



G
GROUP BY TO_CHAR(first_time, 'MM/DD')



ORDER BY MIN(RN);

查询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'


查询没有索引的表
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)

查询7天的db time（db time=db cpu+io time+wait time不包含空闲等待）
WITH 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

查询产生热块较多的对象
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;


导出AWR报告的SQL语句
select * from dba_hist_snapshot
1
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))
1
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));
1

查询某个SQL的执行计划
select a.hash_value,a.* from v$sql a where sql_id='0n4qfzbqfsjm3'
1
select * from table(dbms_xplan.display_cursor(v$sql.hash_value,0,'advanced'));
1
含顺序的
select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));
1
不过要先创建xplan包，再执行
SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;


S
SQL> grant execute on sys.xplan to public;