通过v$sqlarea,v$sql查询最占用资源的查询
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><B><SPAN style="FONT-SIZE: 14pt; COLOR: #02368d; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">通过</SPAN></B><B><SPAN lang=EN-US style="FONT-SIZE: 14pt; COLOR: #02368d"><FONT face="Times New Roman">v$sqlarea,v$sql</FONT></SPAN></B><B><SPAN style="FONT-SIZE: 14pt; COLOR: #02368d; FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">查询最占用资源的查询</SPAN></B><B><SPAN lang=EN-US style="FONT-SIZE: 14pt; COLOR: #02368d"></SPAN></B></P><P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><B><SPAN lang=EN-US style="FONT-SIZE: 14pt; COLOR: #02368d"><FONT face="Times New Roman"> </FONT></SPAN></B></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">-----------------------<BR>v$sqlarea,v$sql<BR>-----------------------</SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">从</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">V$SQLAREA</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">中查询最占用资源的查询</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"></SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">select b.username username,a.disk_reads reads,<BR> a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,<BR> a.sql_text Statement<BR>from v$sqlarea a,dba_users b<BR>where a.parsing_user_id=b.user_id<BR> and a.disk_reads > 100000<BR>order by a.disk_reads desc;</SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">用</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">buffer_gets</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">列来替换</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">disk_reads</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">列可以得到占用最多内存的</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">sql</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">语句的相关信息。</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"></SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"> </SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">V$SQL</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">是内存共享</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">SQL</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">区域中已经解析的</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">SQL</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">语句。</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"></SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"><BR></SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">列出使用频率最高的</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">5</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">个查询：</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"></SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">select sql_text,executions<BR>from (select sql_text,executions,<BR> rank() over<BR> (order by executions desc) exec_rank<BR> from v$sql)<BR>where exec_rank <=5;</SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">消耗磁盘读取最多的</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">sql top5</SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">：</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"><BR>select disk_reads,sql_text<BR>from (select sql_text,disk_reads,<BR> dense_rank() over<BR> (order by disk_reads desc) disk_reads_rank<BR> from v$sql)<BR>where disk_reads_rank <=5;</SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"><BR></SPAN><SPAN style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: Verdana; mso-hansi-font-family: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">找出需要大量缓冲读取（逻辑读）操作的查询：</SPAN><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"></SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt; TEXT-ALIGN: left; mso-pagination: widow-orphan" align=left><SPAN lang=EN-US style="FONT-SIZE: 9pt; FONT-FAMILY: Verdana; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">select buffer_gets,sql_text<BR>from (select sql_text,buffer_gets,<BR> dense_rank() over<BR> (order by buffer_gets desc) buffer_gets_rank<BR> from v$sql)<BR>where buffer_gets_rank<=5;</SPAN></P>
<P class=MsoNormal style="MARGIN: 0cm 0cm 0pt"><SPAN lang=EN-US><FONT face="Times New Roman"> </FONT></SPAN></P>
页:
[1]