日常维护SQL.docVIP

1.查询有enqueue等待的事件SELECT? ?b.SID, b.serial#, b.username, machine, event, wait_time,? ?? ?? ?? ?CHR (BITAND (p1, /? ?? ?? ?|| CHR (BITAND (p1, / 65535) Enqueue Type? ? FROM v$session_wait a, v$session b? ?WHERE a.event NOT LIKE SQL*N%? ???AND a.event NOT LIKE rdbms%? ???AND a.SID = b.SID? ???AND b.SID 8? ???AND a.event = enqueueORDER BY username; 2 如何确定哪个表空间读写频繁?select name,phyrds,phywrts,readtim,writetim? ?? ?? ?from v$filestat a,v$dbfile b? ?? ?? ?where a.file# = b.file#? ?? ?? ?order by readtim descLibrary Cache Pin/Lock Pile Up SELECT s.sid, kglpnmod “Mode”, kglpnreq “Req”, SPID “OS Process”FROM v$session_wait w, x$kglpn p, v$session s ,v$process oWHERE p.kglpnuse=s.saddrAND kglpnhdl=w.p1rawand w.event like ‘%library cache pin%’and s.paddr=o.addr 4全表扫描的表 SQLcol name for a30SQLselect name,value from v$sysstat? ?2 where name in (’table scans(short tables)’,table scans(long tables)’); 5 查询SQL语句执行时,硬语法分析的次数select name,valuefrom v$sysstatwhere name like ‘parse count%’;该项显示buffer cache大小是否合适。公式：1-((physical reads-physical reads direct-physical reads direct (lob)) / session logical reads)执行：select 1-((a.value-b.value-c.value)/d.value)??from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d??where a.name=’physical reads’ and? ?? ?? ?b.name=’physical reads direct’ and? ?? ?? ?c.name=’physical reads direct (lob)’ and? ?? ?? ?d.name=’session logical reads’; 7 该项显示buffer命中率。公式：1-(physical reads/ (db block gets+consistent gets))执行：select 1 - (sum(decode(name, ‘physical reads’, value, 0)) /? ?? ? (sum(decode(name, ‘db block gets’, value, 0)) +? ?? ? sum(decode(name, ‘consistent gets’, value, 0))))? ?? ? “Buffer Hit Ratio”from v$sysstat; 8 Soft parse ratio：这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为0.2则表示硬解析率太高。不过，如果总解析量(parse count total)偏低，这项值可以被忽略。公式：1 - ( parse count (hard) / parse count (total) )执行：select 1-(a.value/b.value)??from v$sysstat a,v$sysstat b??Where a.name=’parse