TX:ITL LOCK(INITRANS,MAXINTRANS)

今天和老周老肖吃饭之于谈论了一个问题,就是INITRANS,MAXINTRANS对高并发量的数据块的影响。
大家都知道在进行大量DML对同一个块的时候(不同行),不会出现TX:ROW LOCK,但是由于ITL的限制
这样的操作可能出现TX:ITL LOCK(MODE=4),以前我遇到过各种TX TM,但是ITL确实没有遇到过,我记得
10G的不管ASSM还是MSSM都是默认的最打MAXTRANS为255,所以没怎么关注,因为如果要达到这个值需要
255个TRANSACTION对同一个块进行修改,基本不可能。
但是我却忽略了一点:
DIS402 3-19
Before the block reaches  PCTFREE, the free space is
used both for insertion of new rows and by the growth of the data block header.
可以发现PCTFREE除了存储可能的UPDATE数据还存储扩张的BLOCK header。

测试:
SQL>  create tablespace testo1
  2   datafile '/oradata/xuexi/XUEXI/datafile/testo1.dbf' size 50m segment space management AUTO;
SQL>  create tablespace testo2
  2   datafile '/oradata/xuexi/XUEXI/datafile/testo2.dbf' size 50m segment space management manual;
SQL>   create table test2
  2    initrans 100 maxtrans 200
  3    tablespace testo1
  4    as
  5    select * from dba_users;
 
Table created
 
SQL>
SQL>  create table test3
  2    initrans 100 maxtrans 200
  3    tablespace testo2
  4    as
  5    select * from dba_users;
  SQL> select TABLE_NAME,FREELISTS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS from user_tables where table_name in('TEST2','TEST3');
 
TABLE_NAME                      FREELISTS   PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS
------------------------------ ---------- ---------- ---------- ---------- ----------
TEST2                                             10                   100        255
TEST3                                   1         10         40        100        255
可以看到我们只能指定INI_TRANS,MAX_TRANS默认就是255,同时可以看到MSSM的PCT_FREE和PCT_USED都生效了因为他是FREELIST管理的(freelists=1),
而ASSM却只有PCT_FREE,而PCT_USED,freelists均为空,所以他是位图进行管理空块的,而PCT_FREE是必须因为剩余多少空间给UPDATE和扩张的ITL还是
它来指定,而为了避免FREELIST的争用,大家要尽量使用ASSM,默认的10G就是。
默认的INDEX的INI_TRANS=2,表的INI_TRANS=1,MAX_TRANS都是255,这样就出现一个问题,如果没有足够的PCTFREE来进行扩张ITL,即便MAX_TRANS为65555也没用,
所以如果出现ITL LOCK(MODE=4),大家应该考虑是:
1、增加PCTFREE
2、增加初始的块的ITL及提高INI_TRANS。
其实这样的情况很少,但是遇到还是要注意。

SQL> alter table test2 pctfree 40 initrans 150;
 
Table altered
 
SQL> select TABLE_NAME,FREELISTS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS from user_tables where table_name in('TEST2');
 
TABLE_NAME                      FREELISTS   PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS
------------------------------ ---------- ---------- ---------- ---------- ----------
TEST2                                             40                   150        255

但是这个操作只对后来分配的BLOCK生效,如果对现有的表,只有MOVE了,根据需求看看是否NOLOGGING
SQL> alter table test2 move pctfree 30 initrans 170;
 
Table altered
当然索引也可以一样处理

SQL> select TABLE_NAME,index_name,FREELISTS,PCT_FREE,INI_TRANS,MAX_TRANS from user_indexes ;
 
TABLE_NAME                     INDEX_NAME                      FREELISTS   PCT_FREE  INI_TRANS  MAX_TRANS
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
TEST3                          TEST_IN                                           10          2        255

注意索引没有PCT_USED,是否进行插入数据不是PCT_USED控制的,是根据ROWID确定的。同时对于索引来说,其pctfree仅仅是在create或rebuild时生效,对与后续的插入、修改之类的操作来说是无效的,pctfree的设置也仅仅是为了延缓由于insert等操作而导致的的索引块分裂。

SQL> alter index test_in  pctfree 30;
 
alter index test_in  pctfree 30
 
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
所以索引只能修改INITRANS
 
SQL>
SQL>
SQL> alter index test_in  initrans 170;
 
Index altered

 

时间: 2024-10-22 14:17:51

TX:ITL LOCK(INITRANS,MAXINTRANS)的相关文章

ORACLE等待事件:enq: TX - row lock contention

enq: TX - row lock contention等待事件,这个是数据库里面一个比较常见的等待事件.enq是enqueue的缩写,它是一种保护共享资源的锁定机制,一个排队机制,先进先出(FIFO).enq: TX - row lock contention等待事件,OACLE将其归类为application级别的等待事件.有些场景是因为应用逻辑设计不合理造成的.下面我们看看enq: TX - row lock contention的英文介绍: This wait indicates ti

【故障处理】队列等待之enq: TX - row lock contention

[故障处理]队列等待之enq: TX - row lock contention 1  BLOG文档结构图   2  前言部分 2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① enq: TX - row lock contention等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 ④ ADDM的使用 ⑤ 如何获取历史执行计划 ⑥ 查询绑定变量的具体值 ⑦ 很多有用的查询性能的SQL语句

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

  公司用户反馈一系统在14:00~15:00(2016-08-16)这个时间段反应比较慢,于是生成了这个时间段的AWR报告,   如上所示,通过Elapsed Time和DB Time对比分析,可以看出在这段时间内服务器并不繁忙.分析Top 5 Timed Events,我们可以看到前五的等待事件     可以看到等待事件enq: TX - row lock contention占了所有等待事件17.3%的比例,猜测有可能是锁等待(enqueue等待)引起的阻塞导致,但是这个还不能下定论,因为

20161208理解enq TX - row lock contention

[20161208]理解enq TX - row lock contention.txt >SELECT * FROM v$event_name WHERE name = 'enq: TX - row lock contention'; EVENT#   EVENT_ID NAME                          PARAMETER1  PARAMETER2      PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ------

ORACLE 归档空间满导致的enq: TX - row lock contention

  2016年10月10日,客户一预警系统发生会话数飙高,系统响应极慢,后来确诊根源是归档空间满,引起所有redo耗尽,导致会话堆积,下面是处理过程.  操作系统:HP-UX B.11.31 U ia64  数据库版本:ORACLE 10.2.0.5 RAC  按照常规处理思路,首先查看RAC数据库的告警日志:  实例1的告警日志 Mon Oct 10 19:24:48 EAT 2016 ORACLE Instance orcl1 - Can not allocate log, archival

【MOS】 Troubleshooting waits for enq: TX - allocate ITL entry(1472175.1)

Troubleshooting waits for 'enq: TX - allocate ITL entry' (文档 ID 1472175.1) In this Document Symptoms Cause Solution   Increase INITRANS   Increase PCTFREE   A Combination of increasing both INITRANS and PCTFREE References APPLIES TO: Oracle Database

oracle数据库enq: TX - allocate ITL entry性能诊断

朋友公司的某铁路集团医保系统出现性能问题业务不能正常办理,下面是出现性能问题时的awr报告 从等待事件来看主要是出现了多处锁竞争.其中enq: TX - allocate ITL entry等待事件是由于缺省情况下创建的表的INITRANS参数为1,索引的INITRANS参数值为2.当有太多的并发DML操作同时操作相同的数据块或索引块就会出现这个等待事件,可以通过查看Segments by ITL Waits部分的信息来了解出现大量并发DML操作的对象 从下面的信息可以看出消耗时间最长的语句都是

oracle的TX lock信息在哪里?

而 我们都知道ORACLE不会发生锁升级,锁对于ORACLE来说并不是稀缺资源,为什么DML lock对于ORACLE来说不是稀缺资源,下面来寻找答案. SQL> select * from emp where sal>2999; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- ------

Oracle中的锁(LOCK)机制

 本文结合示例简要的介绍了一下Oracle中锁的机制. 为了解决多用户环境下并发操作相同的资源而造成的错误修改数据的问题.单用户环境下不需要考虑锁,因为所有操作都是串行的.下面的文章简要的介绍了一下 锁的分类异常复杂,enqueue.latch.mutex等,都是为了解决并发存在的,自己也有些混乱,所以也不过多解释了.下面列举一些对于lock的要点内容. l 排他锁: 不允许相关的资源被共享.一个资源在一个时间点内只有一个事务能够获取该资源的排他锁,只有持有该锁的事务能够修改相关的资源, 其他想