blocking,waiting锁的监控处理:

问题描述:
经常听到一些用户反应，说数据库的某些资源（表）的访问出现长时间等待或是死锁，然后就怀疑到机器性能或是部件损坏，这是一种误解。实际上这种情况往往不会是硬件的问题，通常是应用设计的不合理造成资源的的非正常锁定与等待。

1.以 dba 身份（不一定为system）登录入数据库中，创建三个基本表:temp_session，temp_lock, temp_sqltext，
并在将会进行查询的列上建立相应的索引:

drop table temp_session;
create table temp_session as
  select a.username, a.sid, a.serial#,a.lockwait, a.machine,a.status,a.last_call_et,a.sql_hash_value,a.program
  from   v$session a  where  1=2 ;

create unique index temp_session_u1 on temp_session(sid);
create index temp_session_n2 on temp_session(lockwait);
create index temp_session_n3 on temp_session(sql_hash_value);

2.从 v$lock 视图中取出字段，创建 temp_lock 表:
drop table temp_lock;
create table temp_lock as
select id1, kaddr, sid, request,type  
from v$lock  
where 1=2;

create index temp_lock_n1 on temp_lock(sid);
create index temp_lock_n2 on temp_lock(kaddr);

3. 从 v$sqltext 视图中取出字段，创建 temp_sqltext 表: 
drop table temp_sqltext;
create table temp_sqltext as  select hash_value , sql_text  from v$sqltext  where 1=2;
create index temp_sqltext_n1 on temp_sqltext ( hash_value);


4.获取数据
insert into temp_session
select a.username, a.sid, a.serial#,
       a.lockwait, a.machine,a.status,
       a.last_call_et,a.sql_hash_value,a.program
  from v$session a  where nvl(a.username,'NULL')< >'NULL;

insert into temp_lock select id1, kaddr, sid, request,type   from v$lock;

insert into temp_sqltext
select hash_value , sql_text    from v$sqltext s, temp_session m  where s.hash_value=m.sql_hash_value;

5.获取结果--正在等待别人的用户
select a.sid, a.serial#,a.machine,a.last_call_et, a.username, b.id1  
from temp_session a, temp_lock b
 where a.lockwait = b.kaddr;
 
6.获取结果--被等待的用户
select a.sid, a.serial#,a. machine,a.last_call_et,a.username,b.type,a.status,b.id1
  from temp_session a, temp_lock b
 where b.id1 in
      (select distinct e.id1
         from temp_session d, temp_lock e
        where d.lockwait = e.kaddr)
   and a.sid = b.sid 
   and b.request=0;

7.查出其sql
select a.username, a.sid, a.serial#,
 b.id1, b.type, c.sql_text
  from temp_session a, temp_lock b, temp_sqltext c
 where b.id1 in
       (select distinct e.id1
          from temp_session d, temp_lock e
         where d.lockwait = e.kaddr)
   and a.sid = b.sid
   and b.request=0
   and c.hash_value =a.sql_hash_value;
   
8.如果用户允许，可将该会话kill掉，事务失败会滚，资源释放（sid,serial为第5步得到的结果）：
  alter system kill session 'sid, serial#';  
  

1.查看某个表空间内所占空间大于某个值的段（表或索引）: 
    SELECT segment_name,bytes FROM dba_segments WHERE bytes>10000000 AND tablespace_name='tablespace_name';

 2.查看某个表空间内最大连续的自由空间大小:
   SELECT tablespace_name,max(bytes) FROM dba_free_space GROUP BY tablespace_name ORDER BY max(bytes);

 3.查看所有表空间的碎片程度（值在30以下表示碎片很多）
select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes)  from   (select  
     b.file_id  file_ID, 
     b.tablespace_name  tablespace_name, 
     b.bytes  Bytes, 
     (b.bytes-sum(nvl(a.bytes,0)))  used, 
     sum(nvl(a.bytes,0))  free, 
     sum(nvl(a.bytes,0))/(b.bytes)*100         Percent 
     from dba_free_space a,dba_data_files b  
     where a.file_id=b.file_id  
     group by b.tablespace_name,b.file_id,b.bytes  
     order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes); 

 4.迅速收缩临时段（适用于临时段表空间收缩很慢的情况）
   alter tablespace temp default storage(pctincrease 1)； 
   alter tablespace temp default storage(pctincrease 0)；

 5.查看自上次数据库启动以来所有数据文件的读写次数
   select 
      substr(DF.NAME,1,5) Drive,
      DF.NAME file_name,
      (fs.phyblkrd+fs.phyblkwrt)  
   from  v$filestat fs,v$datafile df
        where  df.file#=fs.file#;
 
 6.查看某用户下段存储的大小
   select SEGMENT_NAME,BYTES from dba_segments where segment_type='TABLE' and owner='owner_name' ;
   select SEGMENT_NAME,BYTES from dba_segments where segment_type='INDEX' and owner='owner_name' ;
由于oracle提供的oem工具的局限性，所以很多时候dba必需借助于一些脚本来管理、调优数据库。

This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:

EXEC ExportToExcel @server = ’.',
                   @uname = ’sa’,
                   @QueryText = ’SELECT au_fname FROM pubs..authors’,
                   @filename = ’c:\ImportToExcel.xls’

/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
- all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:

EXEC ExportToExcel @server = ’.',
                   @uname = ’sa’,
                   @QueryText = ’SELECT au_fname FROM pubs..authors’,
                   @filename = ’c:\ImportToExcel.xls’
*/

IF OBJECT_ID(‘ExportToExcel’) IS NOT NULL DROP PROC ExportToExcel
GO

CREATE PROCEDURE ExportToExcel (
  @server sysname = null,
  @uname sysname = null,
  @pwd sysname = null,
  @QueryText varchar(200) = null,
  @filename varchar(200) = ’c:\ImportToExcel.xls’
)
AS
DECLARE @SQLServer int,
        @QueryResults int,
        @CurrentResultSet int,
        @object int,
        @WorkBooks int,
        @WorkBook int,
        @Range int,
        @hr int,
        @Columns int,
        @Rows int,
        @indColumn int,
        @indRow int,
        @off_Column int,
        @off_Row int,
        @code_str varchar(100),
        @result_str varchar(255)

IF @QueryText IS NULL 
  BEGIN
    PRINT ’Set the query string’
    RETURN
  END

– Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername

– Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USER

SET NOCOUNT ON

EXEC @hr = sp_OACreate ’SQLDMO.SQLServer’, @SQLServer OUT
IF @hr <> 0
BEGIN
    PRINT ’error create SQLDMO.SQLServer’
    RETURN
END

–  Connect to the SQL Server
IF @pwd IS NULL
  BEGIN
    EXEC @hr = sp_OAMethod @SQLServer, ’Connect’, null, @server, @uname
    IF @hr <> 0
       BEGIN
         PRINT ’error Connect’
         RETURN
       END
  END
ELSE
  BEGIN
    EXEC @hr = sp_OAMethod @SQLServer, ’Connect’, null, @server, @uname, @pwd
    IF @hr <> 0
      BEGIN
        PRINT ’error Connect’
        RETURN
      END
  END

SELECT @result_str = ’ExecuteWithResults("’ + @QueryText + ’")’
EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT
IF @hr <> 0
BEGIN
    PRINT ’error with method ExecuteWithResults’
    RETURN
END

EXEC @hr = sp_OAMethod @QueryResults, ’CurrentResultSet’, @CurrentResultSet OUT
IF @hr <> 0
BEGIN
    PRINT ’error get CurrentResultSet’
    RETURN
END

EXEC @hr = sp_OAMethod @QueryResults, ’Columns’, @Columns OUT
IF @hr <> 0
BEGIN
    PRINT ’error get Columns’
    RETURN
END

EXEC @hr = sp_OAMethod @QueryResults, ’Rows’, @Rows OUT
IF @hr <> 0
BEGIN
    PRINT ’error get Rows’
    RETURN
END

EXEC @hr = sp_OACreate ’Excel.Application’, @object OUT
IF @hr <> 0
BEGIN
    PRINT ’error create Excel.Application’
    RETURN
END

EXEC @hr = sp_OAGetProperty @object, ’WorkBooks’, @WorkBooks OUT
IF @hr <> 0
BEGIN
    PRINT ’error create WorkBooks’
    RETURN
END

EXEC @hr = sp_OAGetProperty @WorkBooks, ’Add’, @WorkBook OUT
IF @hr <> 0
BEGIN
    PRINT ’error with method Add’
    RETURN
END

EXEC @hr = sp_OAGetProperty @object, ’Range("A1")’, @Range OUT
IF @hr <> 0
BEGIN
    PRINT ’error create Range’
    RETURN
END

SELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1

WHILE (@indRow <= @Rows)
BEGIN
SELECT @indColumn = 1

WHILE (@indColumn <= @Columns)
BEGIN

EXEC @hr = sp_OAMethod @QueryResults, ’GetColumnString’, @result_str OUT, @indRow, @indColumn
IF @hr <> 0
BEGIN
    PRINT ’error get GetColumnString’
    RETURN
END

EXEC @hr = sp_OASetProperty @Range, ’value’, @result_str
IF @hr <> 0
BEGIN
    PRINT ’error set value’
    RETURN
END

EXEC @hr = sp_OAGetProperty @Range, ’Offset’, @Range OUT, @off_Row, @off_Column
IF @hr <> 0
BEGIN
    PRINT ’error get Offset’
    RETURN
END

SELECT @indColumn = @indColumn + 1

END

SELECT @indRow = @indRow + 1
SELECT @code_str = ’Range("A’ + LTRIM(str(@indRow)) + ’")’
EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT
IF @hr <> 0
BEGIN
    PRINT ’error create Range’
    RETURN
END

END

SELECT @result_str = ’exec master..xp_cmdshell ”del ’ + @filename + ”’, no_output’
EXEC(@result_str)
SELECT @result_str = ’SaveAs("’ + @filename + ’")’
EXEC @hr = sp_OAMethod @WorkBook, @result_str
IF @hr <> 0
BEGIN
    PRINT ’error with method SaveAs’
    RETURN
END

EXEC @hr = sp_OAMethod @WorkBook, ’Close’
IF @hr <> 0
BEGIN
    PRINT ’error with method Close’
    RETURN
END

EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
    PRINT ’error destroy Excel.Application’
    RETURN
END

EXEC @hr = sp_OADestroy @SQLServer
IF @hr <> 0
BEGIN
    PRINT ’error destroy SQLDMO.SQLServer’
    RETURN
END
GO