脚本:监控临时表空间使用率

针对字典管理临时表空间:

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from 
(select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, 
(select sum(blocks) total_blocks from dba_data_files where tablespace_name='TEMP') f;

针对本地管理表空间:

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from 
(select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, 
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

示例输出:

 select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
  2  from (select sum(used_blocks) tot_used_blocks from 
v$sort_segment where tablespace_name='TEMP') s, 
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

percent used
------------
           0

Comments

  1. REM Listing of temp segments
    
    SELECT A.tablespace_name tablespace, D.mb_total,
    SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
    D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
    FROM v$sort_segment A,
    (
    SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
    FROM v$tablespace B, v$tempfile C
    WHERE B.ts#= C.ts#
    GROUP BY B.name, C.block_size
    ) D
    WHERE A.tablespace_name = D.name
    GROUP by A.tablespace_name, D.mb_total;
    
    REM Temp segment usage per session
    
    SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
    P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
    COUNT(*) statements
    FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
    WHERE T.session_addr = S.saddr
    AND S.paddr = P.addr
    AND T.tablespace = TBS.tablespace_name
    GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
    P.program, TBS.block_size, T.tablespace
    ORDER BY sid_serial;
    
    
    
    
    
    
    
    
    How Do You Find Who And What SQL Is Using Temp Segments?
    
    For 8.1.7 to 9.2:
    
    SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
    FROM v$session a, v$sort_usage b, v$sqlarea c
    WHERE a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    ORDER BY b.tablespace, b.blocks;
    
    For 10.1 and above:
    
    SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
    FROM v$session a, v$tempseg_usage b, v$sqlarea c
    WHERE a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    ORDER BY b.tablespace, b.blocks;
    
    
    
    
    REM  #*****************************************************
    REM  #File Name: sort.sql
    REM  #
    REM  #Purpose:   Report Sort Statistics
    REM#
    REM  #*****************************************************
    
    SELECT  substr(vs.username,1,20) "db user",
            substr(vs.osuser,1,20)   "os user",
            substr(vsn.name,1,20)   "Type of Sort",
            vss.value
    FROM    v$session vs,
            v$sesstat vss,
            v$statname vsn
    WHERE   (vss.statistic#=vsn.statistic#) AND
            (vs.sid = vss.sid) AND
            (vsn.name like '%sort%')
    ORDER BY 2,3;
    
    Creator of Sort Segment in Oracle 8 and above
    
     SELECT   b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, 
              a.username, a.osuser, a.status
     FROM     v$session a,v$sort_usage b
     WHERE    a.saddr = b.session_addr
     ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
    

Trackbacks

  1. […] 脚本:监控并行进程状态 脚本:监控数据库中的活跃用户及其运行的SQL 脚本:监控临时表空间使用率 Script to show Active Distributed Transactions Gather DBMS_STATS Default parameter Script:Datafile […]

沪ICP备14014813号-2

沪公网安备 31010802001379号