logminer精简使用手册

创建相关包
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmslm.sql
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmslmd.sql

 


分析
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_74_9dl7y16k_.arc',Options=>dbms_logmnr.NEW);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_75_9dl8f7ox_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_76_9dl8f9n0_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_77_9dl9cf4j_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_78_9dl9ch2r_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_79_9dlc7ljx_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_80_9dlc7n34_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_81_9dlgr3hs_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_82_9dlgr59d_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_83_9dll8nft_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_84_9dll8p64_.arc',Options=>dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(LogFileName => '/oracle10g/app/oracle/flash_recovery_area/ORCL10G/archivelog/2014_01_05/o1_mf_1_85_9dlos5c5_.arc',Options=>dbms_logmnr.ADDFILE);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

或者加上过滤条件:
startscn => ,
endscn =>,
starttime =>,
endtime => 

保存内容
create table test1 tablespace users as select * from V$LOGMNR_CONTENTS ;

查看内容
select sql_redo from test1 
    where upper(sql_redo) like '%*%';

浅析:library cache lock和library cache pin

这两天研究了一下library cache lock和library cache pin,对其的认识更加清晰了一些。

 

在解读之前得说一些常识性的东西,方面之后理解:

首先library cache lock和library cache pin不是latch,我们可以把他看成一个内存里面的锁。

这两个“锁”也有自己的模式,就像TX锁有Row-S、exclusive(X)、Share(S)这些模式一样。

library cache lock和library cache pin是游标上面的争用,游标分为瞬时和存储性的,他们的“锁”能够拥有的模式是不同的。但是这个不是今天的重点。

lock可以持有的锁模式:0 、n、s、x(瞬时游标无S、X)

pin可以持有的锁模式:0、s、x

0模式:证明无锁

n模式:允许游标申请s、x时,我们称之为破碎解析锁,他与0的区别是,当sx模式的锁申请后,他会通告,自己被更“高级”的锁模式占用了

s模式:共享模式。允许其他游标申请S,但是不允许申请X

X模式:独占模式。拒绝其他对游标的申请。

 

其实从模式中我们就能看到。造成library cache lock和library cache pin锁的原因无非两种情况:

1.会话1持有S模式的锁(无论是lock还是pin),会话2申请X模式的锁。那么就要产生等待,等会话一释放S模式锁之后才行。

2.会话1持有X模式的锁(无论是lock还是pin),会话2申请X,S模式的锁。那么就要产生等待,等会话一释放X模式锁之后才行。

这两种情况如果是发生在申请LOCK的时候就是等待library cache lock当然,申请PIN的时候就是等待library cache pin。

OK,现在开始一些稍微深入的东西:

11g之后lock和pin一般都出现在存储过程上面,对象和表上面都较少了。

在SQL、存储过程等的编译阶段,需要的是获取父游标的句柄handel(个人觉得这个地方也可以理解为获取heap0,heap0是游标中的0号对,里面是此游标的元数据之类的信息,也可以认为就是handel),获取句柄这个动作其实就是申请lock,这个时候产生争用,那么,library cache lock来了。

之后在执行阶段,我们需要获取执行计划才能执行SQL吧,执行计划存储在子游标的heap6里面,这个时候我们就要去pin住heap6.如果产生争用,那么library cache pin来了。

这儿有一点要注意,编译阶段lock,执行阶段pin。lock是肯定在pin之前的。

这个仅仅是针对SQL和存储过程的,对象和表相关的与之类似。

现在,懂了吧?晕不晕?

最后我们想一下如何模拟这个现象。

会话1正在调用存储过程A。这个时候他将以n模式获取LOCK以及S模式获取PIN。

此时,会话2更改A,比如delete A,更改A内部代码,甚至grant这个存储过程的权限给其他用户,这个时候会以X模式获取LOCK,在获取之前,该存储过程游标lock的模式为会话1持有的n,那么会话2能够正常获取lock的x。但是该存储过程游标pin的模式为会话1持有的s。那么获取PIN的X就被堵塞了,只能等待。这个时候就产生了library cache pin。

继续,会话3在这个时候更改A,比如delete A,更改A内部代码,甚至grant这个存储过程的权限给其他用户,这个时候会以X模式获取LOCK,在获取之前,该存储过程游标lock的模式为会话2持有的x,那么会话2不能够正常获取lock的x,这个时候就产生了library cache lock。

可能有点绕,慢慢品味。哈哈。。。。

 

 

运维小技巧:加速oracle脚本的执行速度以及实时观察脚本执行的时间、位置

你是否遇到过一下场景:

1.需要批量创建或者重建索引或者其他oracle数据库相关操作,都写在一个.sql文件里面,然后执行,但是感觉很慢想优化,又或者是不知道执行到哪儿了。

2.明明开了并行重建索引,发现还是没有走并行。

3.重建索引老是报资源繁忙,导致失败。

其实你需要在你的脚本前面加一些参数:

alter session set sort_area_size=1610612736;
alter session set workarea_size_policy=MANUAL;
alter session set db_file_multiblock_read_count=512;
alter session set events ‘10351 trace name context forever, level 128’;
alter session set “_sort_multiblock_read_count”=128;
alter session set ddl_lock_timeout=1000;
alter session enable parallel ddl;
alter session enable parallel dml;
set timing on
set time on
set echo on

 

还是那句话,谁用谁知道。

PS:不知道这些参数啥意思?试试吧。。。。如果你有批量执行的语句写在一个sql里面,不妨在最开始加上这些试试。你会很惊喜的,至少我第一次用的时候是。

放心,我不坑人。。。。

运维小技巧:如何后台执行脚本

在日常操作中,难免会跑一些脚本,例如expdp/impdp导入导出、为表空间增加多个数据文件、批量重建索引等等。

除非能够很快结束的操作,否则极度推荐使用后台执行,原因如下:

1.例如在导入导出的时候如果不后台执行,那么中途如果网络断了或者其他原因,或导致导入导出中段。

2.在脚本结束前,不能做关机等操作,若是到了下班时间,还不敢说走就走。

3.一个终端只能执行一个脚本。

4.例如为一个表空间增加100个数据文件,你一个一个粘贴复制,不仅浪费时间精力,还容易出错。

等等,不管你是否觉得这些理由对不对,但是,我还是给予忠告:尽量后台执行。

那么问题来了:如何后台执行。我一般在日常中需要后台执行的要么是.sh文件,要么是.sql文件。

1.需要执行的是sh文件(文件名为 test.sh)。

nohup sh test.sh>sh.log 2>&1 &

2.需要执行的是sql文件(文件名为 test.sql)。

 nohup sqlplus "/ as sysdba" @test.sql>./test.log 2>&1  &

之后我们可以 tail -f  test.log 来查看脚本执行信息。

很方便的,谁用谁知道。

 

调查表空间突然增长的原因

客户说有个表空间突然增加,想看看原因。。整理了些sql,分享一下

1.可以通过增长大概时间查询快照id

select snap_id,to_char(BEGIN_INTERVAL_TIME,'YYYY-MM-DD hh24-mi-ss')
  ,to_char(END_INTERVAL_TIME,'YYYY-MM-DD hh24-mi-ss')
   from DBA_HIST_SNAPSHOT order by 1 desc;

2.查询看哪些对象导致的(前三句sql是自己用过的)
--确定快照和表空间后可执行以下sql调查。这条sql可能查出的结果都是0,原因应该是视图本身的原因

SELECT t1.obj#,
	       t1.dataobj#,
	       t2.SPACE_ALLOCATED_TOTAL - t1.SPACE_ALLOCATED_TOTAL,
	                                  a.owner,a.object_name
	FROM (
	SELECT *
	FROM DBA_HIST_SEG_STAT
	WHERE snap_id=&s1 ) t1,
	        (SELECT *
	         FROM DBA_HIST_SEG_STAT
	         WHERE snap_id=&s2 )t2,
	                            DBA_HIST_SEG_STAT_OBJ a WHERE t1.obj# = t2.obj#
	    AND t1.dataobj# = t2.dataobj#
	    AND t1.obj#=a.obj#
	    AND t1.dataobj#=a.dataobj#
	    and a.tablespace_name='&tabname'
	ORDER BY 3 DESC;

    --如果上述sql没法得到结果,可以用这条:

		SELECT a.*,
	       b.OBJECT_NAME
	FROM
	    (SELECT OBJ#,
	            sum(SPACE_ALLOCATED_DELTA)
	     FROM DBA_HIST_SEG_STAT
	     WHERE SNAP_ID>=&s1
	         AND SNAP_ID<=&s2
	     GROUP BY OBJ#) a,
	                    DBA_HIST_SEG_STAT_OBJ b
	WHERE a.OBJ# = b.OBJ#
	    AND b.tablespace_name='&tablespace'
	ORDER BY 2 DESC;
- -这句也行,酌情更改
 select s.begin_interval_time,s.end_interval_time,(select object_name from dba_objects where object_id=ss.obj#),
 ss.* from dba_hist_seg_stat ss,dba_hist_snapshot s
 where ss.dbid=s.dbid
 and ss.instance_number=s.instance_number
 and ss.snap_id=s.snap_id
 and ss.instance_number=1
 and s.end_interval_time between sysdate-1 and sysdate
 and ss.obj#=1583206

– -另外,也可以用块的改变。这个sql没用过,网上搜的

SELECT a.OBJECT_NAME,
	       to_char(c.END_INTERVAL_TIME,'yyyy-mm-dd') SNAP_TIME,
	       sum(b.DB_BLOCK_CHANGES_DELTA)*8/1024/1024 GB
	FROM dba_objects a,
	    (SELECT SNAP_ID,
	            obj#,
	            DB_BLOCK_CHANGES_DELTA
	     FROM DBA_HIST_SEG_STAT
	     WHERE DB_BLOCK_CHANGES_DELTA > 20000
	     ORDER BY snap_id DESC, DB_BLOCK_CHANGES_DELTA DESC) b,
	                                       DBA_HIST_SNAPSHOT c
			WHERE a.object_id=b.obj#
			    AND object_type='TABLE'
			    AND b.SNAP_ID=c.SNAP_ID
			GROUP BY a.OBJECT_NAME,
			         c.END_INTERVAL_TIME
			ORDER BY GB

 

	- -这个也是网上搜的,根据自己的需求酌情更改
	column owner format a16
	column object_name format a36
	column start_day format a11
	column block_increase format 9999999999

	select   obj.owner, obj.object_name,
	         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
	         sum(a.db_block_changes_delta) block_increase
	from     dba_hist_seg_stat a,
	         dba_hist_snapshot sn,
	         dba_objects obj
	where    sn.snap_id = a.snap_id
	and      obj.object_id = a.obj#
	and      obj.owner not in ('SYS','SYSTEM')
	and      end_interval_time between to_timestamp('01-JAN-2000','DD-MON-RRRR')
	         and to_timestamp('02-FEB-2013','DD-MON-RRRR')
	group by obj.owner, obj.object_name,
	         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
	order by obj.owner, obj.object_name
	/

3.如有必要,查看对应表空间表的情况

	select a.*,b.created from
	(select owner, segment_name, segment_type, bytes / 1024 / 1024 size_mb
	  from dba_segments
	 where tablespace_name = '&tablespace'
	 order by size_mb desc)a,dba_objects b
	 where a.owner=b.owner
	 and a.segment_name=b.object_name
	 and a.segment_type=b.object_type

4. 确认对象之后可以查询表的改变情况

	begin dbms_stats.flush_database_monitoring_info;end;   --此句生产库可执行
	select * from dba_tab_modifications where table_name in ('FINVOUCHERDET','FINACCTSUM','PRJPROCESSES')
	--再来一句查看表的情况
	select a.*,b.created,b.object_id from
	(select owner, segment_name, segment_type, bytes / 1024 / 1024 size_mb
	  from dba_segments
	 where tablespace_name = '&tablespace'
	 order by size_mb desc)a,dba_objects b
	 where a.owner=b.owner
	 and a.segment_name=b.object_name
	 and a.segment_type=b.object_type

打完收工!

归档日志满,导致数据库hang住

在一次做测试的过程中,创建测试表他居然hang住了。
作为一个dba遇到这种情况第一反应当然是看等待事件咯:

SQL> select username,SID,SERIAL#,EVENT,STATUS,MACHINE,PROGRAM,module,OSUSER,TERMINAL,blocking_session,SQL_ID from v$session where username is not null and event not like '%message%';

USERNAME       SID  SERIAL# EVENT                          STATUS   MACHINE                        PROGRAM                                  MODULE                         OSUSER
------------ ----- -------- ------------------------------ -------- 
TERMINAL        BLOCKING_SESSION SQL_ID
--------------- ---------------- -------------

OWI            148     4387 log file switch (archiving nee ACTIVE   monthone                       sqlplus@monthone (TNS V1-V3)             SQL*Plus                       oracle
                            ded)
pts/0                        130 ggssnxvbw19s0

很明显,归档满了,没说的看归档空间

 

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     128
Next log sequence to archive   128
Current log sequence           130
SQL> show parameter recover

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_recovery_file_dest                string
/orat/flash
db_recovery_file_dest_size           big integer
1395M
db_unrecoverable_scn_tracking        boolean
TRUE
recovery_parallelism                 integer
0
SQL> !df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              16G   16G  840K 100% /
tmpfs                 932M  375M  557M  41% /dev/shm
/dev/sdb1             9.9G  6.8G  2.7G  73% /orat

目录中其实是还有空间滴。那么问题就只能是db_recovery_file_dest_size这个参数了

 

SELECT substr(name, 1, 30) name, round(space_limit/1024/1024)||'M' AS quota,
       round(space_used/1024/1024)||'M'        AS used,round(100*space_used/space_limit) used1,
       round(space_reclaimable/1024/1024)||'M' AS reclaimable,
        number_of_files   AS files
  FROM  v$recovery_file_dest 
  /

 NAME                           QUOTA                USED                  USED% RECLAIMABLE               FILES
------------------------------ -------------------- -------------------- ------ -------------------- ----------
/orat/flash                    1395M                1393M                   100 0M                          107

果然用完了,那么,进rman清撒

我之前在操作系统层面进行了删除,所以现在需要crosscheck一下
1.rman target /
2.crosscheck archivelog all;
3.delete expired archivelog all;

操作完毕,创建表空间语句分分钟结束

SQL>
Table created.

owi等待事件模拟中可能会用到的脚本

 --my_sess_event.sql:求当前会话的v$session_event视图的结果

select event, total_waits, time_waited
  from v$session_event
 where sid = (select sid from v$mystat where rownum = 1)
 order by 3 desc;

--show_param.sql:显示包括隐含参数在内的参数值
select ksppinm, ksppstvl
  from x$ksppi x, x$ksppcv y
 where (x.indx = y.indx)
   and (translate(ksppinm, '_', '#') like '%&1%');

--system_event.sql:v$system_event视图上,求整个系统当前等待状况.
select *
  from (select event, total_waits, time_waited
          from v$system_event
         where wait_class <> 'Idle'
         order by 3 desc)
 where rownum <= 100;


--sesstat.sql: 求当前会话的v$sesstat视图结果
select n.name, sum(s.value)
  from v$sesstat s, v$statname n
 where n.name like '%&stat_name%'
   and s.statistic# = n.statistic#
   and s.sid = (select sid from v$mystat where rownum = 1)
 group by n.name;

--undosize.sql: 获得当前事务的undo数据信息
select used_ublk, used_urec
  from v$transaction t, v$session s
 where s.sid = (select sid from v$mystat where rownum = 1)
   and s.taddr = t.addr;

模拟latch:cache buffers chains

latch:cache buffers chains

代表性情况:
低效的sql
hot block

模拟争用

一:低效的sql

--创建cbc_test(id,name)表,对于id列创建cbc_test_idx索引,id列是唯一建,是选择性非常好的列
--多个会话同时通过cbc_test_idx索引查询扫描cbc_test表
--广泛的索引扫描引起不必要的缓冲区查询增加,因此发生latch:cache buffers chains争用。
create table cbc_test(id number,name char(100));
insert into cbc_test(id,name)
select rownum,object_name from dba_objects; id列选择性很好
多执行几次打到15w行以上:
select count(*) from cbc_test;

create index cbc_test_idx on cbc_test(id);

扫描:

create  or replace procedure cbc_do_select is
  begin
  		for x in (select /*+ index(cbc_test cbc_test_idx)*/  * from cbc_test where id>0 ) loop
  		null;
  	end loop;
  end;
  		/  



SQL> create table cbc_test(id number,name char(100));

Table created.


SQL> insert into cbc_test(id,name)
  2  select rownum,object_name from dba_objects; 

86905 rows created.

SQL> /

86905 rows created.

SQL> select count(*) from cbc_test;

  COUNT(*)
----------
    173810

SQL> create index cbc_test_idx on cbc_test(id);

Index created.

SQL> 
SQL> create  or replace procedure cbc_do_select is
  2    begin
  3             for x in (select /*+ index(cbc_test cbc_test_idx)*/  * from cbc_test where id>0 ) loop
  4             null;
  5     end loop;
  6    end;
  7             /  

Procedure created.

10000个会话同时查询,模拟低效的sql大量执行:
var job_no number;
begin 
	for idx in 1..10000 loop
	    dbms_job.submit(:job_no,'cbc_do_select;');
	    commit;
	     end loop;
	     end;
	     /


SQL>  select event, total_waits, time_waited
    from v$session_event
  where sid = 146
 order by 3 desc

可以看到 latch: cache buffers chains 等待的时间大量的增加。

EVENT                             TOTAL_WAITS TIME_WAITED
--------------------------------------------- -----------
SQL*Net message from client                49       96807
db file sequential read                 14401         213
buffer busy waits                          47         123
control file sequential read            23191         114
latch: cache buffers chains                35          84
library cache lock                         16          79
latch: shared pool                         39          60
enq: RO - fast object reuse                 1          57
events in waitclass Other                  26          43
latch: row cache objects                   20          38
cursor: pin S wait on X                     4          16

EVENT                             TOTAL_WAITS TIME_WAITED
--------------------------------------------- -----------
library cache: mutex X                      1           4
direct path write                          97           2
log file switch completion                  2           1
SQL*Net break/reset to client               9           1
db file scattered read                      1           0
log file sync                               3           0
utl_file I/O                              217           0
SQL*Net message to client                  49           0
Disk file operations I/O                    8           0
direct path read                           97           0

21 rows selected.

SQL> /

EVENT                             TOTAL_WAITS TIME_WAITED
--------------------------------------------- -----------
SQL*Net message from client                49       96807
db file sequential read                 14402         213
buffer busy waits                          49         128
control file sequential read            23394         116
latch: cache buffers chains                35          84
library cache lock                         16          79
latch: shared pool                         39          60
enq: RO - fast object reuse                 1          57
events in waitclass Other                  27          45
latch: row cache objects                   20          38
cursor: pin S wait on X                     4          16

EVENT                             TOTAL_WAITS TIME_WAITED
--------------------------------------------- -----------
library cache: mutex X                      1           4
direct path write                          97           2
log file switch completion                  2           1
SQL*Net break/reset to client               9           1
db file scattered read                      1           0
log file sync                               3           0
utl_file I/O                              217           0
SQL*Net message to client                  49           0
Disk file operations I/O                    8           0
direct path read                           97           0

21 rows selected.

SQL> /

EVENT                             TOTAL_WAITS TIME_WAITED
--------------------------------------------- -----------
SQL*Net message from client                49       96807
db file sequential read                 14436         219
buffer busy waits                          85         215
control file sequential read            26974         135
latch: cache buffers chains                47         122
library cache lock                         22         112
latch: shared pool                         40          65
enq: RO - fast object reuse                 1          57
events in waitclass Other                  31          53
latch: row cache objects                   23          41
cursor: pin S wait on X                     4          16

EVENT                             TOTAL_WAITS TIME_WAITED
--------------------------------------------- -----------
library cache: mutex X                      1           4
direct path write                          97           2
log file switch completion                  2           1
SQL*Net break/reset to client               9           1
db file scattered read                      1           0
log file sync                               3           0
utl_file I/O                              217           0
SQL*Net message to client                  49           0
Disk file operations I/O                    8           0
direct path read                           97           0

21 rows selected.

二:hot block
在第一个实验的基础上,反复扫描特定块

reate or replace procedure cbc_do_select(p_from in number,p_to in number) is begin
	for idx in 1..50000 loop
		for x in(select id from cbc_test
					where id between p_from and p_to) loop
					null;
					end loop;
				end loop;
			end;
			/


var job_no number;
begin 
	for idx in 1..10000 loop
	    dbms_job.submit(:job_no,'cbc_do_select(1000,1010);');
	    commit;
	     end loop;
	     end;
	     /



SQL> /

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             33       18573
control file sequential read         10745          78
db file sequential read                108          16
buffer busy waits                        3          11
latch: shared pool                       3           8
SQL*Net break/reset to client            1           7
latch: row cache objects                 3           4
latch: cache buffers chains              7           2
library cache lock                       1           1
log file sync                           16           1
utl_file I/O                            43           0
SQL*Net message to client               33           0
Disk file operations I/O                 6           0

13 rows selected.

SQL> /

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             33       18573
control file sequential read         11886          95
db file sequential read                116          16
buffer busy waits                        3          11
latch: shared pool                       3           8
SQL*Net break/reset to client            1           7
latch: row cache objects                 3           4
latch: cache buffers chains              7           2
library cache lock                       1           1
log file sync                           16           1
utl_file I/O                            43           0
SQL*Net message to client               33           0
Disk file operations I/O                 6           0

13 rows selected.

产生了latch: cache buffers chains。在11g中 latch: cache buffers chains 的算法优化了很多,所以,这种情况也会相对10g改善很多。
我们现在去看看是那些块是热点块,这个方法在实际生产过程中是很适用的:

SQL>  select * from 
  2  (
  3     select addr,child#,gets,sleeps from v$latch_children
  4             where name='cache buffers chains'
  5             order by sleeps desc
  6  ) where rownum <20;

ADDR                 CHILD#       GETS     SLEEPS
---------------- ---------- ---------- ----------
000000008CD23820        253    3520374        455
000000008CD09160        101   76764536        348
000000008CD77178        736    3460021        311
000000008CE0D090       1595    5402650        190
000000008CE53E88       2002    3411577         88
000000008CDFF468       1518    5085572         73
000000008CDEBB58       1404    3389122         70
000000008CD1F3D0        227    3376461         68
000000008CE0BBE0       1589    3403447         67
000000008CDB81C0       1105    3400248         62
000000008CE33EC8       1818    3381546         58
000000008CD437E0        437    3398994         57
000000008CD944B8        904    3375898         54
000000008CD6D1D0        675    3405044         53
000000008CE5B0E8       2046    3407607         52
000000008CDE49C0       1361    3379856         48
000000008CD44FB0        447    3382578         48
000000008CDBC6D8       1132    3407425         46
000000008CD0FE48        138    3360903         41

19 rows selected.

当前以下的子锁存器正在被使用
253
101
736
1595

现在利用x$bh视图可以知道那些是热点块

 

select hladdr,obj,(select object_name
	 				from dba_objects
	 				where (data_object_id is null and object_id=x.obj)
	 				or data_object_id=x.obj
	 				and rownum=1) as object_name,dbarfil,file#,dbablk,tch
	 				from x$bh x
	 				where hladdr in
	 				('000000008CD23820','000000008CD09160','000000008CD77178','000000008CE0D090')
	 				order by hladdr,obj;

SQL> /

HLADDR                  OBJ OBJECT_NAME                       DBARFIL      FILE#     DBABLK        TCH
---------------- ---------- ------------------------------ ---------- ---------- ---------- ----------
000000008CD09160          2 ICOL$                                   1          1      31578          2
000000008CD09160         48 I_COL1                                  1          1       9422          1
000000008CD09160        501 I_WRI$_OPTSTAT_H_ST                     2          2      65583          0
000000008CD09160        518 C_TOID_VERSION#                         1          1      66282          1
000000008CD09160      89791                                         1          1      97114          0
000000008CD09160      89792 CBC_TEST                                1          1      97114         59
000000008CD09160      89793 CBC_TEST_IDX                            1          1      96881        209
000000008CD09160 4294967295                                         3          3       3220          0
000000008CD09160 4294967295                                         3          3       3220         11
000000008CD09160 4294967295                                         3          3       2987          1
000000008CD09160 4294967295                                         3          3       3453         17
000000008CD23820         36 I_OBJ1                                  1          1      50443          1
000000008CD23820         37 I_OBJ2                                  1          1      41301          1
000000008CD23820         37 I_OBJ2                                  1          1      72133          1
000000008CD23820         69 VIEW$                                   1          1      19844          5
000000008CD23820        227 IDL_UB2$                                1          1      19611         14
000000008CD23820        289 I_JOB_JOB                               1          1       2026          1
000000008CD23820        289 I_JOB_JOB                               1          1       2026          1
000000008CD23820        289 I_JOB_JOB                               1          1       2026         48
000000008CD23820        289 I_JOB_JOB                               1          1       2026          1
000000008CD23820        289 I_JOB_JOB                               1          1       2026          1
000000008CD23820        498 I_WRI$_OPTSTAT_HH_ST                    2          2       9770          1
000000008CD23820        555 KOTMD$                                  1          1      10702          3
000000008CD23820      89791                                         1          1      98394          0
000000008CD23820      89791                                         1          1      98161          0
000000008CD23820      89792 CBC_TEST                                1          1      98161         59
000000008CD23820 4294967295                                         3          3       4267          2
000000008CD23820 4294967295                                         3          3       4733          2
000000008CD23820 4294967295                                         3          3        162          1
000000008CD77178         37 I_OBJ2                                  1          1      41303          1
000000008CD77178        227 IDL_UB2$                                1          1      19613         14
000000008CD77178        228 IDL_SB4$                                1          1      19380         14
000000008CD77178        289 I_JOB_JOB                               1          1       2028          1
000000008CD77178        289 I_JOB_JOB                               1          1       2028          1
000000008CD77178        289 I_JOB_JOB                               1          1       2028          1
000000008CD77178        289 I_JOB_JOB                               1          1       2028          1
000000008CD77178        289 I_JOB_JOB                               1          1       2028         46
000000008CD77178        289 I_JOB_JOB                               1          1       2028          1
000000008CD77178        498 I_WRI$_OPTSTAT_HH_ST                    2          2       9772          0
000000008CD77178        518 C_TOID_VERSION#                         1          1      10704          1
000000008CD77178        518 C_TOID_VERSION#                         1          1      62993          1
000000008CD77178       6477 WRH$_LIBRARYCACHE                       2          2       5201          4
000000008CD77178      89791                                         1          1      98163          0
000000008CD77178      89791                                         1          1      97930          0
000000008CD77178      89792 CBC_TEST                                1          1      98163         59
000000008CD77178 4294967295                                         3          3       4269          1
000000008CE0D090          2 ICOL$                                   1          1      22421          2
000000008CE0D090          2 ICOL$                                   1          1      31563          2
000000008CE0D090        121 I_SYSAUTH1                              1          1        964        218
000000008CE0D090        225 IDL_UB1$                                1          1      26992          1
000000008CE0D090        518 C_TOID_VERSION#                         1          1      22654          1
000000008CE0D090        518 C_TOID_VERSION#                         1          1      66267          1
000000008CE0D090       6584 WRH$_SYSMETRIC_HISTORY                  2          2      60764          1
000000008CE0D090      89791                                         1          1      97099          0
000000008CE0D090      89791                                         1          1     101437          0
000000008CE0D090      89791                                         1          1     101204          0
000000008CE0D090      89791                                         1          1     105542          0
000000008CE0D090      89792 CBC_TEST                                1          1      96866         59
000000008CE0D090 4294967295                                         3          3       2972          1
000000008CE0D090 4294967295                                         3          3       3205          0
000000008CE0D090 4294967295                                         3          3       3205          1
000000008CE0D090 4294967295                                         3          3       3205          0

62 rows selected.

我们可以看到CBC_TEST 的
97114
98161块和CBC_TEST_IDX的
96881块 引起了争用

在生产中遇到了类似的问题,可以这种方法定位热点块。然后进行进一步的分析。

ps:在11g中感觉出现 latch: cache buffers chains 的情况会比之前的版本更少,这是因为oracle对算法进行了改进,更多是以share的模式获取 latch: cache buffers chains ,能够感觉到oracle在不断的优化自己。

运维优化基础:对各个级别锁的理解

经常对各种锁都迷迷糊糊的,感觉是常识很多书,博客都是直接说得到了xx锁,还有经常听说几级几级锁,什么鬼?所以。。。。然后就。。。然后我就瓜了。
今天好好来归纳总结一下
0:none
1:null 空
2:Row-S 行共享(RS):共享表锁
3:Row-X 行专用(RX):用于行的修改
4:Share 共享锁(S):阻止其他DML操作
5:S/Row-X 共享行专用(SRX):阻止其他事务操作
6:exclusive 专用(X):独立访问使用

总的来说,分为两种
排他锁(Exclusive,简称X锁):一旦用户对某个资源添加了X锁,则其他用户都不能再对该资源添加任何类型的锁,直到该用户释放了资源上的X锁为止。
共享锁(Share,简称S锁):一旦用户对某个资源添加了S锁,则其他用户都不能在该资源上添加X锁,只能添加S锁,直到该用户释放了资源上的S锁为止。

看到这儿,我好像明白了什么。。继续。。。
行级共享锁(Row Shared,简称RS锁)

通常是通过select … from for update语句添加的,同时该方法也是我们用来手工锁定某些记录的主要方法。比如,当我们在查询某些记录的过程中,不希望其他用户对查询的记录进行更新操作,则可以发出这样的语句。当数据使用完毕以后,直接发出rollback命令将锁定解除。当表上添加了RS锁定以后,不允许其他事务对相同的表添加排他锁,但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行。

行级排他锁(Row Exclusive,简称RX锁)

当我们进行DML时会自动在被更新的表上添加RX锁,或者也可以通过执行lock命令显式的在表上添加RX锁。在该锁定模式下,允许其他的事务通过DML语句修改相同表里的其他数据行,或通过lock命令对相同表添加RX锁定,但是不允许其他事务对相同的表添加排他锁(X锁)。

对于RS和RX的解释,感觉差不多有木有。。。思考了一下我觉得有以下区别:
1.对于RS是共享锁,所以a事务可以通过 for update对某表的一行添加这个锁,b事务也能在这个表上添加锁(排他锁除外)。而RX的锁的话便不允许其他事物在加锁到他锁定的行了。
2.RS主要是for update产生的,而RX是dml产生的。

共享锁(Share,简称S锁)

通过lock table in share mode命令添加该S锁。在该锁定模式下,不允许任何用户更新表。但是允许其他用户发出select …from for update命令对表添加RS锁。
感觉这个类似于查了全表的select …from for update,不过这个是表级上的。。。。其他事物还能上共享锁,他只是保证数据不被更改。
共享行级排他锁(Share Row Exclusive,简称SRX锁)

通过lock table in share row exclusive mode命令添加SRX锁。该锁定模式比行级排他锁和共享锁的级别都要高,这时不能对相同的表进行DML操作,也不能添加共享锁

排他锁(Exclusive,简称X锁)

通过lock table in exclusive mode命令添加X锁。在该锁定模式下,其他用户不能对表进行任何的DML和DDL操作,该表上只能进行查询。

SRX和x感觉也差不多啊。。。。我发现的一个区别是,当一个表上有RS锁,那么x锁是加不上去滴,SRX可以。。。。

最后加两个表辅助理解:

这五种模式的TM锁的兼容关系如下表所示(√表示互相兼容的请求;×表示互相不兼容的请求;N/A表示没有锁定请求):

-	S	X	RS	RX	SRX	N/A
S	√	×	√	×	×	√
X	×	×	×	×	×	√
RS	√	×	√	√	√	√
RX	×	×	√	√	×	√
SRX	×	×	√	×	×	√
N/A	√	√	√	√	√	√

对Oracle数据库中的各SQL语句所产生的表级锁的情况进行汇总,如下表所示:

SQL语句	                                   表锁定模	允许的表锁定模式
Select * from ……           	               RS	        RS、RX、S、SRX、X
Insert into ……	                           RX	        RS、RX
Update ……	                               RX	        RS、RX
Delete from ……	                           RX	        RS、RX
Select * from for update	               RS	        RS、RX、S、SRX
lock table in row share mode	           RS	        RS、RX、S、SRX
lock table in row exclusive mode           RX	        RS、RX
lock table in share mode	               S	        RS、S
lock table in share row exclusive mode     SRX	        RS
lock table in exclusive mode	           X	        RS

大概理解到这儿吧,感觉够用了。。。如果有问题希望大家指出,我之后发现问题也会完善。

借鉴博客:http://blog.sina.com.cn/s/blog_5f724a710102vcym.html

 

sql性能不好,优化一下,先提取sql文本。。什么?文本少了点啥?乱码?

在优化一条sql的时候,无论通过什么方法,得到的sql文本都是有点问题,sql有点长,其中一段内容如下:

select 1 from tablename b where b.location2 = ‘and subs
tr(fault_telno,1,7) = b.phoneprefix

发现问题了吗。没错标红的地方。这应该是一个过滤条件,location2后面肯定是两个引号中间加一个值。但是现在不知道怎么回事成了这样。

有经验的朋友可能知道可能是字符集的问题。但是今天讨论的主题是,就算知道他是字符集的问题,我们怎么在不改变字符集的情况下对文本信息进行采集。

当时正好我们伟大的熊爷在旁边(你猜是哪个熊爷)。赶紧请教,结果熊爷利用了一个dump的方式获取了文本的情况,并且判断出了没有现实的部分是汉字。之后改变了我crt上的字符集,之后获取了正确的sql文本。我把方法记录了一下,说不定以后能用得着。

SQL> select dump(to_char(sql_text))
     from dba_hist_sqltext where sql_id='d9m4vdtv5fnkh';
115,101,108,101,99,116,32,49,53,49,53,32,116,97,115,107,105,
100,44,48,32,100,101,97,108,115,97,116,101,44,98,117,103,95,
105,110,102,111,95,111,116,104,101,114,97,114,101,97,95,118,
105,101,119,46,102,97,117,108,116,95,110,97,109,101,44,98,117,
103,95,105,110,102,111,95,111,116,104,101,114,97,114,101,97,
95,118,105,101,119,46,99,111,110,110,101,99,116,95,116,101,
108,110,111,44,98,117,103,95,105,110,102,111,95,111,116,104,
101,114,97,114,101,97,95,118,105,101,119,46,102,97,117,108,
116,95,100,97,116,101,44,98,117,103,95,105,110,102,111,95,
111,116,104,101,114,97,114,101,97,95,118,105,101,119,46,102,
97,117,108,116,95,114,101,97,115,111,110,95,100,101,115,99,
44,98,117,103,95,105,110,102,111,95,111,116,104,101,114,97,
114,101,97,95,118,105,101,119,46,102,97,117,108,116,95,116,
101,108,110,111,44,98,117,103,95,105,110,102,111,95,111,116,
104,101,114,97,114,101,97,95,118,105,101,119,46,99,111,110,
110,101,99,116,95,110,97,109,101,44,98,117,103,95,105,110,102,
111,95,111,116,104,101,114,97,114,101,97,95,118,105,101,119,46,
102,97,117,108,116,95,105,100,44,98,117,103,95,105,110,102,111,
95,111,116,104,101,114,97,114,101,97,95,118,105,101,119,46,102,
97,117,108,116,95,97,100,100,114,101,115,115,32,102,114,111,109,
32,98,117,103,95,105,110,102,111,95,111,116,104,101,114,97,114,
101,97,95,118,105,101,119,32,32,119,104,101,114,101,32,110,101,
116,95,102,108,97,103,32,61,32,49,32,97,110,100,32,102,108,97,
103,95,99,111,109,102,105,114,109,32,61,32,57,32,97,110,100,32,
32,101,120,105,115,116,115,32,40,32,115,101,108,101,99,116,32,
49,32,102,114,111,109,32,122,120,100,98,95,99,99,46,116,98,108,
95,99,100,109,97,95,104,108,114,32,98,32,119,104,101,114,101,32,
98,46,108,111,99,97,116,105,111,110,50,32,61,32,39,185,227,176,
178,39,32,97,110,100,32,115,117,98,115,116,114,40,102,97,117,108,
116,95,116,101,108,110,111,44,49,44,55,41,32,61,32,98,46,112,104,
111,110,101,112,114,101,102,105,120,32,41,32,32,97,110,100,32,98,
117,115,105,95,116,121,112,101,95,105,100,32,32,105,110,32,40,39,
49,53,51,50,32,39,44,39,53,52,56,49,39,41


-----这就是我们的sql文本(原来还能这样玩儿,感兴趣的同学可以翻译一下)之后。。

SQL> select dump('location2') from dual;

DUMP('LOCATION2')
----------------------------------------------
Typ=96 Len=9: 108,111,99,97,116,105,111,110,50

之后将sql和location2的ascii码拿到文本工具中去比对(后面的几个字符肯定就是类似于=’xxx’,也就是之前没有成功显示的部分)。找到了我们之前没看到的部分。如下:

32,61,32,39,185,227,176,178,39

32为空格,61为=,39为引号。

所以上面翻译过来是:

= ‘185,227,176,178’

引号中间可以判断出是中文(具体是啥有兴趣的去试试翻译,我可以告诉大家是一个城市的名字)。现在可以确定,丢失这一部分其实是存在的,但是因为字符集的问题,没办法显示好中文。具体是哪儿字符集的问题,这儿不是重点不深入去讨论。可能是linux系统当前环境变量的原因。。。。但是我刚提到了,我这个是我自己crt的原因。多的就不赘述了,以上方法希望对你有帮助。