oracle 监控sql

最新推荐文章于 2023-05-09 16:17:10 发布

NM_IT

最新推荐文章于 2023-05-09 16:17:10 发布

阅读量524

点赞数

分类专栏：数据库文章标签：性能优化数据库

数据库专栏收录该内容

36 篇文章 0 订阅

订阅专栏

1、监控事例的等待

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> </span><span class="kwd">event</span><span class="pun">,</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">decode</span><span class="pun">(</span><span class="pln">wait_Time</span><span class="pun">,</span><span class="lit">0</span><span class="pun">,</span><span class="lit">0</span><span class="pun">,</span><span class="lit">1</span><span class="pun">))</span><span class="pln"> </span><span class="str">"Prev"</span><span class="pun">,</span><span class="pln">      
sum</span><span class="pun">(</span><span class="pln">decode</span><span class="pun">(</span><span class="pln">wait_Time</span><span class="pun">,</span><span class="lit">0</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">0</span><span class="pun">))</span><span class="pln"> </span><span class="str">"Curr"</span><span class="pun">,</span><span class="pln">count</span><span class="pun">(*)</span><span class="pln"> </span><span class="str">"Tot"</span><span class="pln">      
</span><span class="kwd">from</span><span class="pln"> v$session_Wait      
</span><span class="kwd">group</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> </span><span class="kwd">event</span><span class="pln"> order </span><span class="kwd">by</span><span class="pln"> </span><span class="lit">4</span><span class="pun">;</span><span class="pln">     
</span></code>

2、回滚段的争用情况

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> name</span><span class="pun">,</span><span class="pln"> waits</span><span class="pun">,</span><span class="pln"> gets</span><span class="pun">,</span><span class="pln"> waits</span><span class="pun">/</span><span class="pln">gets </span><span class="str">"Ratio"</span><span class="pln">      
</span><span class="kwd">from</span><span class="pln"> v$rollstat a</span><span class="pun">,</span><span class="pln"> v$rollname b      
</span><span class="kwd">where</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">usn </span><span class="pun">=</span><span class="pln"> b</span><span class="pun">.</span><span class="pln">usn</span><span class="pun">;</span><span class="pln">     
</span></code>

3、监控表空间的 I/O 比例

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> df</span><span class="pun">.</span><span class="pln">tablespace_name name</span><span class="pun">,</span><span class="pln">df</span><span class="pun">.</span><span class="pln">file_name </span><span class="str">"file"</span><span class="pun">,</span><span class="pln">f</span><span class="pun">.</span><span class="pln">phyrds pyr</span><span class="pun">,</span><span class="pln">     
f</span><span class="pun">.</span><span class="pln">phyblkrd pbr</span><span class="pun">,</span><span class="pln">f</span><span class="pun">.</span><span class="pln">phywrts pyw</span><span class="pun">,</span><span class="pln"> f</span><span class="pun">.</span><span class="pln">phyblkwrt pbw     
</span><span class="kwd">from</span><span class="pln"> v$filestat f</span><span class="pun">,</span><span class="pln"> dba_data_files df     
</span><span class="kwd">where</span><span class="pln"> f</span><span class="pun">.</span><span class="pln">file</span><span class="com"># = df.file_id    </span><span class="pln"> 
order </span><span class="kwd">by</span><span class="pln"> df</span><span class="pun">.</span><span class="pln">tablespace_name</span><span class="pun">;</span><span class="pln">     
</span></code>

4、监控文件系统的 I/O 比例

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> substr</span><span class="pun">(</span><span class="pln">a</span><span class="pun">.</span><span class="pln">file</span><span class="com">#,1,2) "#", substr(a.name,1,30) "Name",     </span><span class="pln"> 
a</span><span class="pun">.</span><span class="pln">status</span><span class="pun">,</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">bytes</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">.</span><span class="pln">phyrds</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">.</span><span class="pln">phywrts      
</span><span class="kwd">from</span><span class="pln"> v$datafile a</span><span class="pun">,</span><span class="pln"> v$filestat b      
</span><span class="kwd">where</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">file</span><span class="com"># = b.file#;     </span><span class="pln"> 
</span></code>

5、在某个用户下找所有的索引

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> user_indexes</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">,</span><span class="pln"> user_indexes</span><span class="pun">.</span><span class="pln">index_name</span><span class="pun">,</span><span class="pln">uniqueness</span><span class="pun">,</span><span class="pln"> column_name     
</span><span class="kwd">from</span><span class="pln"> user_ind_columns</span><span class="pun">,</span><span class="pln"> user_indexes     
</span><span class="kwd">where</span><span class="pln"> user_ind_columns</span><span class="pun">.</span><span class="pln">index_name </span><span class="pun">=</span><span class="pln"> user_indexes</span><span class="pun">.</span><span class="pln">index_name     
</span><span class="kwd">and</span><span class="pln"> user_ind_columns</span><span class="pun">.</span><span class="pln">table_name </span><span class="pun">=</span><span class="pln"> user_indexes</span><span class="pun">.</span><span class="pln">table_name      
order </span><span class="kwd">by</span><span class="pln"> user_indexes</span><span class="pun">.</span><span class="pln">table_type</span><span class="pun">,</span><span class="pln"> user_indexes</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">,</span><span class="pln">     
user_indexes</span><span class="pun">.</span><span class="pln">index_name</span><span class="pun">,</span><span class="pln"> column_position</span><span class="pun">;</span><span class="pln">     
</span></code>

6、监控 SGA 的命中率

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">value </span><span class="pun">+</span><span class="pln"> b</span><span class="pun">.</span><span class="pln">value </span><span class="str">"logical_reads"</span><span class="pun">,</span><span class="pln"> c</span><span class="pun">.</span><span class="pln">value </span><span class="str">"phys_reads"</span><span class="pun">,</span><span class="pln">     
round</span><span class="pun">(</span><span class="lit">100</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="pun">((</span><span class="pln">a</span><span class="pun">.</span><span class="pln">value</span><span class="pun">+</span><span class="pln">b</span><span class="pun">.</span><span class="pln">value</span><span class="pun">)-</span><span class="pln">c</span><span class="pun">.</span><span class="pln">value</span><span class="pun">)</span><span class="pln"> </span><span class="pun">/</span><span class="pln"> </span><span class="pun">(</span><span class="pln">a</span><span class="pun">.</span><span class="pln">value</span><span class="pun">+</span><span class="pln">b</span><span class="pun">.</span><span class="pln">value</span><span class="pun">))</span><span class="pln"> </span><span class="str">"BUFFER HIT RATIO"</span><span class="pln">      
</span><span class="kwd">from</span><span class="pln"> v$sysstat a</span><span class="pun">,</span><span class="pln"> v$sysstat b</span><span class="pun">,</span><span class="pln"> v$sysstat c     
</span><span class="kwd">where</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">statistic</span><span class="com"># = 38 and b.statistic# = 39     </span><span class="pln"> 
</span><span class="kwd">and</span><span class="pln"> c</span><span class="pun">.</span><span class="pln">statistic</span><span class="com"># = 40;     </span><span class="pln"> 
</span></code>

7、监控 SGA 中字典缓冲区的命中率

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> parameter</span><span class="pun">,</span><span class="pln"> gets</span><span class="pun">,</span><span class="typ">Getmisses</span><span class="pln"> </span><span class="pun">,</span><span class="pln"> getmisses</span><span class="pun">/(</span><span class="pln">gets</span><span class="pun">+</span><span class="pln">getmisses</span><span class="pun">)*</span><span class="lit">100</span><span class="pln"> </span><span class="str">"miss ratio"</span><span class="pun">,</span><span class="pln">     
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">-(</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">getmisses</span><span class="pun">)/</span><span class="pln"> </span><span class="pun">(</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">gets</span><span class="pun">)+</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">getmisses</span><span class="pun">))))*</span><span class="lit">100</span><span class="pln"> </span><span class="str">"Hit ratio"</span><span class="pln">     
</span><span class="kwd">from</span><span class="pln"> v$rowcache      
</span><span class="kwd">where</span><span class="pln"> gets</span><span class="pun">+</span><span class="pln">getmisses </span><span class="pun"><></span><span class="lit">0</span><span class="pln">     
</span><span class="kwd">group</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> parameter</span><span class="pun">,</span><span class="pln"> gets</span><span class="pun">,</span><span class="pln"> getmisses</span><span class="pun">;</span><span class="pln">      
</span></code>

8、监控 SGA 中共享缓存区的命中率，应该小于1%

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> sum</span><span class="pun">(</span><span class="pln">pins</span><span class="pun">)</span><span class="pln"> </span><span class="str">"Total Pins"</span><span class="pun">,</span><span class="pln"> sum</span><span class="pun">(</span><span class="pln">reloads</span><span class="pun">)</span><span class="pln"> </span><span class="str">"Total Reloads"</span><span class="pun">,</span><span class="pln">     
sum</span><span class="pun">(</span><span class="pln">reloads</span><span class="pun">)/</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">pins</span><span class="pun">)</span><span class="pln"> </span><span class="pun">*</span><span class="lit">100</span><span class="pln"> libcache     
</span><span class="kwd">from</span><span class="pln"> v$librarycache</span><span class="pun">;</span><span class="pln">     
 
</span><span class="kwd">select</span><span class="pln"> sum</span><span class="pun">(</span><span class="pln">pinhits</span><span class="pun">-</span><span class="pln">reloads</span><span class="pun">)/</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">pins</span><span class="pun">)</span><span class="pln"> </span><span class="str">"hit radio"</span><span class="pun">,</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">reloads</span><span class="pun">)/</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">pins</span><span class="pun">)</span><span class="pln"> </span><span class="str">"reload percent"</span><span class="pln">      
</span><span class="kwd">from</span><span class="pln"> v$librarycache</span><span class="pun">;</span><span class="pln">     
</span></code>

9、显示所有数据库对象的类别和大小

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> count</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span><span class="pln"> num_instances </span><span class="pun">,</span><span class="pln">type </span><span class="pun">,</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">source_size</span><span class="pun">)</span><span class="pln"> source_size </span><span class="pun">,</span><span class="pln">     
sum</span><span class="pun">(</span><span class="pln">parsed_size</span><span class="pun">)</span><span class="pln"> parsed_size </span><span class="pun">,</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">code_size</span><span class="pun">)</span><span class="pln"> code_size </span><span class="pun">,</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">error_size</span><span class="pun">)</span><span class="pln"> error_size</span><span class="pun">,</span><span class="pln">     
sum</span><span class="pun">(</span><span class="pln">source_size</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">parsed_size</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">code_size</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">error_size</span><span class="pun">)</span><span class="pln"> size_required      
</span><span class="kwd">from</span><span class="pln"> dba_object_size      
</span><span class="kwd">group</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> type order </span><span class="kwd">by</span><span class="pln"> </span><span class="lit">2</span><span class="pun">;</span><span class="pln">     
</span></code>

10、监控 SGA 中重做日志缓存区的命中率，应该小于1%

<code class="prettyprint"><span class="pln">SELECT name</span><span class="pun">,</span><span class="pln"> gets</span><span class="pun">,</span><span class="pln"> misses</span><span class="pun">,</span><span class="pln"> immediate_gets</span><span class="pun">,</span><span class="pln"> immediate_misses</span><span class="pun">,</span><span class="pln">     
</span><span class="typ">Decode</span><span class="pun">(</span><span class="pln">gets</span><span class="pun">,</span><span class="lit">0</span><span class="pun">,</span><span class="lit">0</span><span class="pun">,</span><span class="pln">misses</span><span class="pun">/</span><span class="pln">gets</span><span class="pun">*</span><span class="lit">100</span><span class="pun">)</span><span class="pln"> ratio1</span><span class="pun">,</span><span class="pln">     
</span><span class="typ">Decode</span><span class="pun">(</span><span class="pln">immediate_gets</span><span class="pun">+</span><span class="pln">immediate_misses</span><span class="pun">,</span><span class="lit">0</span><span class="pun">,</span><span class="lit">0</span><span class="pun">,</span><span class="pln">     
immediate_misses</span><span class="pun">/(</span><span class="pln">immediate_gets</span><span class="pun">+</span><span class="pln">immediate_misses</span><span class="pun">)*</span><span class="lit">100</span><span class="pun">)</span><span class="pln"> ratio2     
FROM v$latch WHERE name IN </span><span class="pun">(</span><span class="str">'redo allocation'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'redo copy'</span><span class="pun">);</span><span class="pln">      
</span></code>

11、监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size

<code class="prettyprint"><span class="pln">SELECT name</span><span class="pun">,</span><span class="pln"> value FROM v$sysstat WHERE name IN </span><span class="pun">(</span><span class="str">'sorts (memory)'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'sorts (disk)'</span><span class="pun">);</span><span class="pln">      
</span></code>

12、监控当前数据库谁在运行什么SQL语句

<code class="prettyprint"><span class="pln">SELECT osuser</span><span class="pun">,</span><span class="pln"> username</span><span class="pun">,</span><span class="pln"> sql_text </span><span class="kwd">from</span><span class="pln"> v$session a</span><span class="pun">,</span><span class="pln"> v$sqltext b     
</span><span class="kwd">where</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">sql_address </span><span class="pun">=</span><span class="pln">b</span><span class="pun">.</span><span class="pln">address order </span><span class="kwd">by</span><span class="pln"> address</span><span class="pun">,</span><span class="pln"> piece</span><span class="pun">;</span><span class="pln">     
</span></code>

13、监控字典缓冲区

<code class="prettyprint"><span class="pln">SELECT </span><span class="pun">(</span><span class="pln">SUM</span><span class="pun">(</span><span class="pln">PINS </span><span class="pun">-</span><span class="pln"> RELOADS</span><span class="pun">))</span><span class="pln"> </span><span class="pun">/</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">PINS</span><span class="pun">)</span><span class="pln"> </span><span class="str">"LIB CACHE"</span><span class="pln"> FROM V$LIBRARYCACHE</span><span class="pun">;</span><span class="pln">     
SELECT </span><span class="pun">(</span><span class="pln">SUM</span><span class="pun">(</span><span class="pln">GETS </span><span class="pun">-</span><span class="pln"> GETMISSES </span><span class="pun">-</span><span class="pln"> USAGE </span><span class="pun">-</span><span class="pln"> FIXED</span><span class="pun">))</span><span class="pln"> </span><span class="pun">/</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">GETS</span><span class="pun">)</span><span class="pln"> </span><span class="str">"ROW CACHE"</span><span class="pln"> FROM V$ROWCACHE</span><span class="pun">;</span><span class="pln">     
SELECT SUM</span><span class="pun">(</span><span class="pln">PINS</span><span class="pun">)</span><span class="pln"> </span><span class="str">"EXECUTIONS"</span><span class="pun">,</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">RELOADS</span><span class="pun">)</span><span class="pln"> </span><span class="str">"CACHE MISSES WHILE EXECUTING"</span><span class="pln"> FROM V$LIBRARYCACHE</span><span class="pun">;</span><span class="pln">      
 
</span><span class="pun">后者除以前者，此比率小于</span><span class="lit">1</span><span class="pun">%，接近</span><span class="lit">0</span><span class="pun">%为好。</span><span class="pln">     
 
SELECT SUM</span><span class="pun">(</span><span class="pln">GETS</span><span class="pun">)</span><span class="pln"> </span><span class="str">"DICTIONARY GETS"</span><span class="pun">,</span><span class="pln">SUM</span><span class="pun">(</span><span class="pln">GETMISSES</span><span class="pun">)</span><span class="pln"> </span><span class="str">"DICTIONARY CACHE GET MISSES"</span><span class="pln">     
FROM V$ROWCACHE     
</span></code>

14、找ORACLE字符集

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> sys</span><span class="pun">.</span><span class="pln">props$ </span><span class="kwd">where</span><span class="pln"> name</span><span class="pun">=</span><span class="str">'NLS_CHARACTERSET'</span><span class="pun">;</span><span class="pln">      
</span></code>

15、监控 MTS

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> busy</span><span class="pun">/(</span><span class="pln">busy</span><span class="pun">+</span><span class="pln">idle</span><span class="pun">)</span><span class="pln"> </span><span class="str">"shared servers busy"</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> v$dispatcher</span><span class="pun">;</span><span class="pln">     
 
</span><span class="pun">此值大于</span><span class="lit">0.5</span><span class="pun">时，参数需加大</span><span class="pln">     
 
</span><span class="kwd">select</span><span class="pln"> sum</span><span class="pun">(</span><span class="pln">wait</span><span class="pun">)/</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">totalq</span><span class="pun">)</span><span class="pln"> </span><span class="str">"dispatcher waits"</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> v$queue </span><span class="kwd">where</span><span class="pln"> type</span><span class="pun">=</span><span class="str">'dispatcher'</span><span class="pun">;</span><span class="pln">     
</span><span class="kwd">select</span><span class="pln"> count</span><span class="pun">(*)</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> v$dispatcher</span><span class="pun">;</span><span class="pln">     
</span><span class="kwd">select</span><span class="pln"> servers_highwater </span><span class="kwd">from</span><span class="pln"> v$mts</span><span class="pun">;</span><span class="pln">     
 
servers_highwater</span><span class="pun">接近</span><span class="pln">mts_max_servers</span><span class="pun">时，参数需加大</span><span class="pln">     
</span></code>

16、碎片程度

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> tablespace_name</span><span class="pun">,</span><span class="pln">count</span><span class="pun">(</span><span class="pln">tablespace_name</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> dba_free_space </span><span class="kwd">group</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> tablespace_name      
having count</span><span class="pun">(</span><span class="pln">tablespace_name</span><span class="pun">)></span><span class="lit">10</span><span class="pun">;</span><span class="pln">     
 
alter tablespace name coalesce</span><span class="pun">;</span><span class="pln">     
alter table name deallocate unused</span><span class="pun">;</span><span class="pln">     
 
create </span><span class="kwd">or</span><span class="pln"> replace view ts_blocks_v </span><span class="kwd">as</span><span class="pln">     
</span><span class="kwd">select</span><span class="pln"> tablespace_name</span><span class="pun">,</span><span class="pln">block_id</span><span class="pun">,</span><span class="pln">bytes</span><span class="pun">,</span><span class="pln">blocks</span><span class="pun">,</span><span class="str">'free space'</span><span class="pln"> segment_name </span><span class="kwd">from</span><span class="pln"> dba_free_space     
</span><span class="kwd">union</span><span class="pln"> all     
</span><span class="kwd">select</span><span class="pln"> tablespace_name</span><span class="pun">,</span><span class="pln">block_id</span><span class="pun">,</span><span class="pln">bytes</span><span class="pun">,</span><span class="pln">blocks</span><span class="pun">,</span><span class="pln">segment_name </span><span class="kwd">from</span><span class="pln"> dba_extents</span><span class="pun">;</span><span class="pln">     
 
</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> ts_blocks_v</span><span class="pun">;</span><span class="pln">     
 
</span><span class="kwd">select</span><span class="pln"> tablespace_name</span><span class="pun">,</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">),</span><span class="pln">max</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">),</span><span class="pln">count</span><span class="pun">(</span><span class="pln">block_id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> dba_free_space      
</span><span class="kwd">group</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> tablespace_name</span><span class="pun">;</span><span class="pln">     
 
</span><span class="pun">查看碎片程度高的表</span><span class="pln">     
 
SELECT segment_name table_name </span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(*)</span><span class="pln"> extents     
FROM dba_segments WHERE owner NOT IN </span><span class="pun">(</span><span class="str">'SYS'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'SYSTEM'</span><span class="pun">)</span><span class="pln"> GROUP BY segment_name     
HAVING COUNT</span><span class="pun">(*)</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln">SELECT MAX</span><span class="pun">(</span><span class="pln"> COUNT</span><span class="pun">(*)</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> FROM dba_segments GROUP BY segment_name</span><span class="pun">);</span><span class="pln">     
</span></code>

17、表、索引的存储情况检查

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> segment_name</span><span class="pun">,</span><span class="pln">sum</span><span class="pun">(</span><span class="pln">bytes</span><span class="pun">),</span><span class="pln">count</span><span class="pun">(*)</span><span class="pln"> ext_quan </span><span class="kwd">from</span><span class="pln"> dba_extents </span><span class="kwd">where</span><span class="pln">      
tablespace_name</span><span class="pun">=</span><span class="str">'&tablespace_name'</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> segment_type</span><span class="pun">=</span><span class="str">'TABLE'</span><span class="pln"> </span><span class="kwd">group</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> tablespace_name</span><span class="pun">,</span><span class="pln">segment_name</span><span class="pun">;</span><span class="pln">     
 
</span><span class="kwd">select</span><span class="pln"> segment_name</span><span class="pun">,</span><span class="pln">count</span><span class="pun">(*)</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> dba_extents </span><span class="kwd">where</span><span class="pln"> segment_type</span><span class="pun">=</span><span class="str">'INDEX'</span><span class="pln"> </span><span class="kwd">and</span><span class="pln"> owner</span><span class="pun">=</span><span class="str">'&owner'</span><span class="pln">     
</span><span class="kwd">group</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> segment_name</span><span class="pun">;</span><span class="pln">     
</span></code>

18、找使用CPU多的用户session

<code class="prettyprint"><span class="kwd">select</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">sid</span><span class="pun">,</span><span class="pln">spid</span><span class="pun">,</span><span class="pln">status</span><span class="pun">,</span><span class="pln">substr</span><span class="pun">(</span><span class="pln">a</span><span class="pun">.</span><span class="pln">program</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> prog</span><span class="pun">,</span><span class="pln">a</span><span class="pun">.</span><span class="pln">terminal</span><span class="pun">,</span><span class="pln">osuser</span><span class="pun">,</span><span class="pln">value</span><span class="pun">/</span><span class="lit">60</span><span class="pun">/</span><span class="lit">100</span><span class="pln"> value     
</span><span class="kwd">from</span><span class="pln"> v$session a</span><span class="pun">,</span><span class="pln">v$process b</span><span class="pun">,</span><span class="pln">v$sesstat c     
</span><span class="kwd">where</span><span class="pln"> c</span><span class="pun">.</span><span class="pln">statistic</span><span class="com">#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;</span></code>