查找运行系统里低劣的SQL方法
查找运行系统里bad sql是一个古老的话题, 我们要根据自己的实际情况来分析。绝不能教条的运用下面介绍的这些方法。使用这些SQL语句时，会对系统表产生分组操作，当然也增大了系统的负载。建议大家在系统启动了一段时间后，在半夜负载较轻的时间定时(例如:一个月)来查一查。一定要具体问题具体分析。 <p> 下面是我收藏的一些查找bad sql的方法:</p><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px" twffan="done"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div twffan="done"><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--><img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #808080" twffan="done">*</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> (</span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> buffer_gets, sql_text
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$sqlarea
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> buffer_gets </span><span style="COLOR: #808080" twffan="done">></span><span style="COLOR: #000000" twffan="done"></span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">500000</span><span style="COLOR: #000000" twffan="done">
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">order</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"> buffer_gets </span><span style="COLOR: #0000ff" twffan="done">desc</span><span style="COLOR: #000000" twffan="done">) </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> rownum</span><span style="COLOR: #808080" twffan="done"><=</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">30</span><span style="COLOR: #000000" twffan="done">;</span></div>
</pre></div><p> -- 执行次数多的SQL </p><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 600px" twffan="done"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div twffan="done">
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--><img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> sql_text,executions </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done">
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> (</span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> sql_text,executions </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$sqlarea </span><span style="COLOR: #0000ff" twffan="done">order</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"> executions </span><span style="COLOR: #0000ff" twffan="done">desc</span><span style="COLOR: #000000" twffan="done">)
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> rownum</span><span style="COLOR: #808080" twffan="done"><</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">81</span><span style="COLOR: #000000" twffan="done">;</span></div>
</pre></div><p> -- 读硬盘多的SQL </p><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 600px" twffan="done"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div twffan="done">
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--><img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> sql_text,disk_reads </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done">
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> (</span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> sql_text,disk_reads </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$sqlarea </span><span style="COLOR: #0000ff" twffan="done">order</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"> disk_reads </span><span style="COLOR: #0000ff" twffan="done">desc</span><span style="COLOR: #000000" twffan="done">)
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> rownum</span><span style="COLOR: #808080" twffan="done"><</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">21</span><span style="COLOR: #000000" twffan="done">;</span></div>
</pre></div><p> -- 排序多的SQL </p><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px" twffan="done"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div twffan="done">
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--><img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> sql_text,sorts </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done">
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> (</span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> sql_text,sorts </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$sqlarea </span><span style="COLOR: #0000ff" twffan="done">order</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"> sorts </span><span style="COLOR: #0000ff" twffan="done">desc</span><span style="COLOR: #000000" twffan="done">)
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> rownum</span><span style="COLOR: #808080" twffan="done"><</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">21</span><span style="COLOR: #000000" twffan="done">;</span></div>
</pre></div><p> --分析的次数太多，执行的次数太少，要用绑变量的方法来写sql</p><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 600px" twffan="done"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div twffan="done">
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--><img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">set</span><span style="COLOR: #000000" twffan="done"> pagesize </span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">600</span><span style="COLOR: #000000" twffan="done">;
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">set</span><span style="COLOR: #000000" twffan="done"> linesize </span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">120</span><span style="COLOR: #000000" twffan="done">;
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> substr(sql_text,</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">1</span><span style="COLOR: #000000" twffan="done">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">80</span><span style="COLOR: #000000" twffan="done">) "sql", </span><span style="COLOR: #ff00ff" twffan="done">count</span><span style="COLOR: #000000" twffan="done">(</span><span style="COLOR: #808080" twffan="done">*</span><span style="COLOR: #000000" twffan="done">), </span><span style="COLOR: #ff00ff" twffan="done">sum</span><span style="COLOR: #000000" twffan="done">(executions) "totexecs"
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$sqlarea
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> executions </span><span style="COLOR: #808080" twffan="done"><</span><span style="COLOR: #000000" twffan="done"></span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">5</span><span style="COLOR: #000000" twffan="done">
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">group</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"> substr(sql_text,</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">1</span><span style="COLOR: #000000" twffan="done">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">80</span><span style="COLOR: #000000" twffan="done">)
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">having</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #ff00ff" twffan="done">count</span><span style="COLOR: #000000" twffan="done">(</span><span style="COLOR: #808080" twffan="done">*</span><span style="COLOR: #000000" twffan="done">) </span><span style="COLOR: #808080" twffan="done">></span><span style="COLOR: #000000" twffan="done"></span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">30</span><span style="COLOR: #000000" twffan="done">
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">order</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"></span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">2</span><span style="COLOR: #000000" twffan="done">;</span></div>
</pre></div><p> -- 游标的观察</p><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px" twffan="done"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div twffan="done">
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--><img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">set</span><span style="COLOR: #000000" twffan="done"> pages </span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">300</span><span style="COLOR: #000000" twffan="done">;
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #ff00ff" twffan="done">sum</span><span style="COLOR: #000000" twffan="done">(a.value), b.name
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$sesstat a, v$statname b
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> a.statistic# </span><span style="COLOR: #808080" twffan="done">=</span><span style="COLOR: #000000" twffan="done"> b.statistic#
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #808080" twffan="done">and</span><span style="COLOR: #000000" twffan="done"> b.name </span><span style="COLOR: #808080" twffan="done">=</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #ff0000" twffan="done">'</span><span style="COLOR: #ff0000" twffan="done">opened cursors current</span><span style="COLOR: #ff0000" twffan="done">'</span><span style="COLOR: #000000" twffan="done">
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">group</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"> b.name;
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #ff00ff" twffan="done">count</span><span style="COLOR: #000000" twffan="done">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">0</span><span style="COLOR: #000000" twffan="done">) </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$open_cursor;
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #ff00ff" twffan="done">user_name</span><span style="COLOR: #000000" twffan="done">,sql_text,</span><span style="COLOR: #ff00ff" twffan="done">count</span><span style="COLOR: #000000" twffan="done">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">0</span><span style="COLOR: #000000" twffan="done">) </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$open_cursor
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">group</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #ff00ff" twffan="done">user_name</span><span style="COLOR: #000000" twffan="done">,sql_text </span><span style="COLOR: #0000ff" twffan="done">having</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #ff00ff" twffan="done">count</span><span style="COLOR: #000000" twffan="done">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">0</span><span style="COLOR: #000000" twffan="done">)</span><span style="COLOR: #808080" twffan="done">></span><span style="FONT-WEIGHT: bold; COLOR: #800000" twffan="done">30</span><span style="COLOR: #000000" twffan="done">;</span></div>
</pre></div><p> --查看当前用户&username执行的SQL</p><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 600px" twffan="done"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div twffan="done">
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--><img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> sql_text </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$sqltext_with_newlines </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> (hash_value,address) </span><span style="COLOR: #808080" twffan="done">in</span><span style="COLOR: #000000" twffan="done">
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> (</span><span style="COLOR: #0000ff" twffan="done">select</span><span style="COLOR: #000000" twffan="done"> sql_hash_value,sql_address </span><span style="COLOR: #0000ff" twffan="done">from</span><span style="COLOR: #000000" twffan="done"> v$session </span><span style="COLOR: #0000ff" twffan="done">where</span><span style="COLOR: #000000" twffan="done"> username</span><span style="COLOR: #808080" twffan="done">=</span><span style="COLOR: #ff0000" twffan="done">'</span><span style="COLOR: #ff0000" twffan="done">&username</span><span style="COLOR: #ff0000" twffan="done">'</span><span style="COLOR: #000000" twffan="done">)
<img src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061129110428687.gif" align="top" twffan="done" /> </span><span style="COLOR: #0000ff" twffan="done">order</span><span style="COLOR: #000000" twffan="done"></span><span style="COLOR: #0000ff" twffan="done">by</span><span style="COLOR: #000000" twffan="done"> address,piece;</span></div>
</pre></div>
页:
[1]