DBA常用SQL语句系列

一、数据库构架体系
  
1、表空间的监控是一个重要的任务,我们必须时刻关心表空间的设置,是否满足现在应用的需求,以下的语句可以查询到表空间的详细信息

SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
          MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,
          CONTENTS,LOGGING,
          EXTENT_MANAGEMENT,  -- Columns not available in v8.0.x
          ALLOCATION_TYPE,    -- Remove these columns if running  
          PLUGGED_IN,          -- against a v8.0.x database
          SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later
FROM     DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;

2、对于某些数据文件没有设置为自动扩展的表空间来说,如果表空间满了,就将意味着数据库可能会因为没有空间而停止下来。监控表空间,最主要的就是监控剩余空间的大小或者是使用率。以下是监控表空间使用率与剩余空间大小的语句

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM  
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL  --if have tempfile  
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,  
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM  
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

3、除了监控表空间的剩余空间,有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力,虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能

SELECT T.TABLESPACE_NAME,D.FILE_NAME,
        D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,
      DBA_DATA_FILES  D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME

4、我相信使用字典管理的表空间的也不少吧,因为字典管理的表空间中,每个表的下一个区间的大小是不可以预料的,所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检查那些表的扩展将引起表空间的扩展。

SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME
FROM ALL_TABLES A,
(SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNK
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME
AND A.NEXT_EXTENT > F.BIG_CHUNK

5、段的占用空间与区间数也是很需要注意的一个问题,如果一个段的占用空间太大,或者跨越太多的区间(在字典管理的表空间中,将有严重的性能影响),如果段没有可以再分配的区间,将导致数据库错误。所以,段的大小与区间监控也是一个很重要的工作

SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,
ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",
EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS,
S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"
FROM DBA_SEGMENTS S
WHERE S.OWNER NOT IN ('SYS','SYSTEM')
ORDER BY Used_Extents DESC

6、对象的空间分配与空间利用情况,除了从各个方面的分析,如分析表,查询rowid等方法外,其实oracle提供了一个查询空间的包dbms_space,如果我们稍封装一下,将是非常好用的一个东西。


CREATE OR REPLACE PROCEDURE show_space
        (p_segname in varchar2,
         p_type in varchar2 default 'TABLE' ,
         p_owner in varchar2 default user)
AS
     v_segname varchar2(100);
     v_type varchar2(10);
     l_free_blks number;
     l_total_blocks number;
     l_total_bytes number;
     l_unused_blocks number;
     l_unused_bytes number;
     l_LastUsedExtFileId number;
     l_LastUsedExtBlockId number;
     l_LAST_USED_BLOCK number;
     PROCEDURE  p( p_label in varchar2, p_num in number )
     IS
     BEGIN
     dbms_output.put_line( rpad(p_label,40,'.')|| p_num );
     END;
BEGIN
     v_segname := upper(p_segname);
     v_type := p_type;
     if (p_type = 'i' or p_type = 'I') then
       v_type := 'INDEX';
     end if;
     if (p_type = 't' or p_type = 'T') then
       v_type := 'TABLE';
     end if;
     if (p_type = 'c' or p_type = 'C') then
       v_type := 'CLUSTER';
     end if;
     --以下部分不能用于ASSM
     dbms_space.free_blocks
     ( segment_owner => p_owner,
     segment_name => v_segname,
     segment_type => v_type,
     freelist_group_id => 0,
     free_blks => l_free_blks );
     --以上部分不能用于ASSM
     dbms_space.unused_space
     ( segment_owner => p_owner,
     segment_name => v_segname,
     segment_type => v_type,
     total_blocks => l_total_blocks,
     total_bytes => l_total_bytes,
     unused_blocks => l_unused_blocks,
     unused_bytes => l_unused_bytes,
     LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
     LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
     LAST_USED_BLOCK => l_LAST_USED_BLOCK );
     --显示结果
     p( 'Free Blocks', l_free_blks );
     p( 'Total Blocks', l_total_blocks );
     p( 'Total Bytes', l_total_bytes );
     p( 'Unused Blocks', l_unused_blocks );
     p( 'Unused Bytes', l_unused_bytes );
     p( 'Last Used Ext FileId', l_LastUsedExtFileId );
     p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
     p( 'Last Used Block', l_LAST_USED_BLOCK );
END;

执行结果将如下所示


SQL> set serveroutput on;
SQL> exec show_space('test');
Free Blocks.............................1
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................1
Last Used Ext BlockId...................48521
Last Used Block.........................2
PL/SQL procedure successfully completed

7、数据库的常规参数我就不说了,除了V$parameter中的常规参数外,ORACLE还有大量的隐含参数,下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述。


SELECT NAME  
,VALUE  
,decode(isdefault, 'TRUE','Y','N') as "Default"  
,decode(ISEM,'TRUE','Y','N') as SesMod  
,decode(ISYM,'IMMEDIATE', 'I',  
'DEFERRED', 'D',  
'FALSE', 'N') as SysMod  
,decode(IMOD,'MODIFIED','U',  
'SYS_MODIFIED','S','N') as Modified  
,decode(IADJ,'TRUE','Y','N') as Adjusted  
,description  
FROM ( --GV$SYSTEM_PARAMETER  
SELECT x.inst_id as instance  
,x.indx+1  
,ksppinm as NAME   
,ksppity  
,ksppstvl as VALUE   
,ksppstdf as isdefault  
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM  
,decode(bitand(ksppiflg/65536,3),  
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM  
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD  
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ  
,ksppdesc as DESCRIPTION   
FROM x$ksppi x  
,x$ksppsv y  
WHERE x.indx = y.indx  
AND substr(ksppinm,1,1) = '_'  
AND x.inst_id = USERENV('Instance')  
)  
ORDER BY NAME

8、数据库的索引如果有比较频繁的Delete操作,将可能导致索引产生很多碎片,所以,在有的时候,需要对所有的索引重新REBUILD,以便合并索引块,减少碎片,提高查询速度。


SQL> set heading off
SQL> set feedback off
SQL> spool d:\index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND owner = USER
SQL>spool off

这个时候,我们打开spool出来的文件,就可以直接运行了。
  
9、表的主键是必要的,没有主键的表可以说是不符合设计规范的,所以我们需要监控表是否有主键

SELECT table_name
FROM all_tables
WHERE owner = USER
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = USER
AND constraint_type = 'P'

二、性能监控
  
1、数据缓冲区的命中率已经不是性能调整中的主要问题了,但是,过低的命中率肯定是不可以的,在任何情况下,我们必须保证有一个大的data buffer和一个高的命中率。
这个语句可以获得整体的数据缓冲命中率,越高越好


SELECT a.VALUE + b.VALUE logical_reads,
c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads'

2、库缓冲说明了SQL语句的重载率,当然,一个SQL语句应当被执行的越多越好,如果重载率比较高,就考虑增加共享池大小或者是提高Bind变量的使用
以下语句查询了Sql语句的重载率,越低越好

SELECT SUM(pins) total_pins,SUM(reloads) total_reloads,
SUM(reloads)/SUM(pins)*100 libcache_reload_ratio
FROM  v$librarycache

3、用户锁,数据库的锁有的时候是比较耗费资源的,特别是发生锁等待的时候,我们必须找到发生等待的锁,有可能的话,杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话


SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
               'TX','ROW LOCK',
               NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL

4、锁与等待,如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表,而谁在等待。


SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
        o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC

以上查询结果是一个树状结构,如果有子节点,则表示有等待发生。如果想知道锁用了哪个回滚段,还可以关联到V$rollname,其中xidusn就是回滚段的USN
  
5、如果发生了事务或锁,想知道哪些回滚段正在被使用吗?其实通过事务表,我们可以详细的查询到事务与回滚段之间的关系。同时,如果关联会话表,我们则可以知道是哪个会话发动了这个事务。


SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",
t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used",
t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName
FROM v$session s,v$transaction t,v$rollname r
WHERE s.SADDR=t.SES_ADDR
AND t.XIDUSN=r.usn

6、想知道现在哪个用户正在利用临时段吗?这个语句将告诉你哪个用户正在利用临时段。


SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,  
a.username, a.osuser, a.status,c.sql_text
FROM v$session a,v$sort_usage b, v$sql c
WHERE a.saddr = b.session_addr  
AND a.sql_address = c.address(+)
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

7、如果利用会话跟踪或者是想查看某个会话的跟踪文件,那么查询到OS上的进程或线程号是非常重要的,因为文件的令名中,就包含这个信息,以下的语句可以查询到进程或线程号,由此就可以找到对应的文件。


SELECT p1.value||'\'||p2.value||'_ora_'||p.spid filename
     FROM
         v$process p,
         v$session s,
         v$parameter p1,
         v$parameter p2
     WHERE p1.name = 'user_dump_dest'
     AND p2.name = 'db_name'
     AND p.addr = s.paddr
     AND s.audsid = USERENV ('SESSIONID');

8、在ORACLE 9i中,可以监控索引的使用,如果没有使用到的索引,完全可以删除掉,减少DML操作时的操作。
以下就是开始索引监控与停止索引监控的脚本


set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
  
SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
FROM dba_indexes
WHERE owner = USER;  
  
spool off  
set heading on
set echo on
set feedback on
------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
  
SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
FROM dba_indexes
WHERE owner = USER;  
  
spool off  
set heading on
set echo on
set feedback on

如果需要监控更多的用户,可以将owner=User改写成别的
监控结果在视图v$object_usage中查询


隐含参数:
select a.ksppinm "parameter ", a.ksppdesc "descriptoin "  
from x$ksppi a,x$ksppcv b,x$ksppsv c  
where a.indx=b.indx and a.indx=c.indx and a.ksppinm like '/_%' escape '/';


Check OS process id from Oracle sid


  
select spid from v$process  
where addr in ( select paddr from v$session where sid=[$sid) ]  

Check Oracle sid from OS process id


select sid from v$session  
where paddr in ( select addr from v$process where spid=[$pid) ]  

Check current SQL in a session


select SQL_TEXT       from  V$SQLTEXT
where HASH_VALUE     =
( select SQL_HASH_VALUE  from v$session
where sid = &sid)
order by PIECE

Checking v$session_wait


select * from v$session_wait  
where event not like 'rdbms%'
and  event not like 'SQL*N%'
and  event not like '%timer';

Dictionary Cache Hits


   SELECT sum(getmisses)/sum(gets) FROM  v$rowcache;
/*It should be < 15%, otherwise Add share_pool_size*/

Check DB object name from file id and block#


select owner,segment_name,segment_type  
from dba_extents
where file_id = [$fno and &dno between block_id and block_id + blocks – 1 ]

#寻找hot block
select /*+ ordered */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
l.name = 'cache buffers chains' and
l.sleeps > &sleep_count and
x.hladdr = l.addr and
e.file_id = x.file# and
x.dbablk between e.block_id and e.block_id + e.blocks - 1;
  
#找出每个文件上的等待事件
select df.name,kf.count from v$datafile df,x$kcbfwait kf where (kf.indx+1)=df.file#;
  
#找出引起等待事件的SQL语句.
select sql_text from v$sqlarea a,v$session b,v$session_wait c where a.address=b.sql_address and b.sid=c.sid and c.event=[$ll]
  
#监控共享池中哪个对象引起了大的内存分配
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

判断你是从pfile启动还是spfile启动的简单方法!!!
  
select decode(count(*), 1, 'spfile', 'pfile' )  
from v$spparameter  
where rownum=1  
and isspecified='TRUE'  
  /  
  
DECODE  
------  
spfile


show_space脚本

CREATE OR REPLACE PROCEDURE show_space
                         ( p_segname IN VARCHAR2,
                           p_owner   IN VARCHAR2 DEFAULT USER,
                           p_type    IN VARCHAR2 DEFAULT 'TABLE',
                           p_partition IN VARCHAR2 DEFAULT NULL )
-- This procedure uses AUTHID CURRENT USER so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user who wanted to use it.
AUTHID CURRENT_USER
as
     l_free_blks                 number;
     l_total_blocks              number;
     l_total_bytes               number;
     l_unused_blocks             number;
     l_unused_bytes              number;
     l_LastUsedExtFileId         number;
     l_LastUsedExtBlockId        number;
     l_LAST_USED_BLOCK           number;
     l_segment_space_mgmt        varchar2(255);
     l_unformatted_blocks number;
     l_unformatted_bytes number;
     l_fs1_blocks number; l_fs1_bytes number;
     l_fs2_blocks number; l_fs2_bytes number;
     l_fs3_blocks number; l_fs3_bytes number;
     l_fs4_blocks number; l_fs4_bytes number;
     l_full_blocks number; l_full_bytes number;
  
     -- Inline procedure to print out numbers nicely formatted
     -- with a simple label.
     PROCEDURE p( p_label in varchar2, p_num in number )
     IS
     BEGIN
         dbms_output.put_line( rpad(p_label,40,'.') ||
                               to_char(p_num,'999,999,999,999') );
     END;
BEGIN
    -- This query is executed dynamically in order to allow this procedure
    -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
    -- via a role as is customary.
    -- NOTE: at runtime, the invoker MUST have access to these two
    -- views!
    -- This query determines if the object is an ASSM object or not.
    BEGIN
       EXECUTE IMMEDIATE
           'select ts.segment_space_management
              FROM dba_segments seg, dba_tablespaces ts
             WHERE seg.segment_name      = :p_segname
               AND (:p_partition is null or
                   seg.partition_name = :p_partition)
               AND seg.owner = :p_owner
               AND seg.tablespace_name = ts.tablespace_name'
              INTO l_segment_space_mgmt
             USING p_segname, p_partition, p_partition, p_owner;
    EXCEPTION
        WHEN too_many_rows THEN
           dbms_output.put_line
           ( 'This must be a partitioned table, use p_partition => ');
           RETURN;
    END;
  
    -- If the object is in an ASSM tablespace, we must use this API
    -- call to get space information; else we use the FREE_BLOCKS
    -- API for the user managed segments.
    IF l_segment_space_mgmt = 'AUTO'
    THEN
      dbms_space.space_usage
      ( p_owner, p_segname, p_type, l_unformatted_blocks,
        l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
        l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
        l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
  
      p( 'Unformatted Blocks ', l_unformatted_blocks );
      p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
      p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
      p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
      p( 'FS4 Blocks (75-100)', l_fs4_blocks );
      p( 'Full Blocks        ', l_full_blocks );
   ELSE
      dbms_space.free_blocks(
        segment_owner     => p_owner,
        segment_name      => p_segname,
        segment_type      => p_type,
        freelist_group_id => 0,
        free_blks         => l_free_blks);
  
      p( 'Free Blocks', l_free_blks );
   END IF;
  
   -- And then the unused space API call to get the rest of the
   -- information.
   dbms_space.unused_space
   ( segment_owner     => p_owner,
     segment_name      => p_segname,
     segment_type      => p_type,
     partition_name    => p_partition,
     total_blocks      => l_total_blocks,
     total_bytes       => l_total_bytes,
     unused_blocks     => l_unused_blocks,
     unused_bytes      => l_unused_bytes,
     LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
     LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
     LAST_USED_BLOCK => l_LAST_USED_BLOCK );
  
     p( 'Total Blocks', l_total_blocks );
     p( 'Total Bytes', l_total_bytes );
     p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
     p( 'Unused Blocks', l_unused_blocks );
     p( 'Unused Bytes', l_unused_bytes );
     p( 'Last Used Ext FileId', l_LastUsedExtFileId );
     p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
     p( 'Last Used Block', l_LAST_USED_BLOCK );
END;

posted on 2007-04-27 10:10  Leetle  阅读(564)  评论(0编辑  收藏  举报

导航