分类目录归档：数据库

ORACLE 12C TOP N SQL实现分页功能

发表于 2013 年 4 月 15 日由惜分飞

从oracle 12c开始,oracle 也提供了类似sql server的top,mysql的limit分页功能，在本文中分别通过TOP N和传统方法来实现分页，sql实现效果是(按照id 倒序排列，取第六条到底十条)
TOP N写法

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

SQL> set autot on exp stat
SQL> SELECT id
  2  FROM t_xifenfei
  3  ORDER BY id  desc offset 5 rows FETCH next 5 ROWS ONLY;

        ID
----------
       188
       187
       186
       185
       184


Execution Plan
----------------------------------------------------------
Plan hash value: 755690401

---------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |   192 |  7488 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |            |   192 |  7488 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|            |   192 |   768 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_XIFENFEI |   192 |   768 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE  WHEN (5>=0)
              THEN 5 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumber">5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ID") DESC
              )<=CASE  WHEN (5>=0) THEN 5 ELSE 0 END +5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        619  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

传统分页写法

SQL> select id from (select id,rownum rn from (
  2  select id  from t_xifenfei order by id desc
  3  )) where rn<11 and rn>=6;

        ID
----------
       188
       187
       186
       185
       184


Execution Plan
----------------------------------------------------------
Plan hash value: 327151993

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |   192 |  4992 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                 |            |   192 |  4992 |     3   (0)| 00:00:01 |
|   2 |   COUNT               |            |       |       |            |          |
|   3 |    VIEW               |            |   192 |  2496 |     3   (0)| 00:00:01 |
|   4 |     SORT ORDER BY     |            |   192 |   768 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T_XIFENFEI |   192 |   768 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=6 AND "RN"<11)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        619  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

从这里可以看出来两种sql分页写法，在小数据量上效率都差不多，但是明显TOP N的写法更加简单，更加灵活.如果数据量大可能还是需要自己写分页SQL。TOP N是通过ROW_NUMBER() OVER INTERNAL_FUNCTION(“ID”)和CASE WHEN内部转换实现分页功能.

发表在 ORACLE 12C | 2 条评论

12c 使用sequence作为列默认值

发表于 2013 年 4 月 14 日由惜分飞

官方文档创建表语句部分说明
在12c中,表支持默认列为sequence值，而且不用使用传统的触发器来实现该功能.

12c创建表使用默认sequence测试过程

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

SQL> create table t_xifenfei
  2  (
  3  id number GENERATED ALWAYS as identity (
  4  start with 1
  5  increment by 1
  6  ), 
  7  name varchar2(200)
  8  );

Table created.

SQL> insert into t_xifenfei(name) values('www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL> col name for a30
SQL> select * from t_xifenfei;

        ID NAME
---------- ------------------------------
         1 www.xifenfei.com

SQL> insert into t_xifenfei(name) values('www.orasos.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_xifenfei;

        ID NAME
---------- ------------------------------
         1 www.xifenfei.com
         2 www.orasos.com

SQL> insert into t_xifenfei values(5,'www.xifenfei.com');
insert into t_xifenfei values(5,'www.xifenfei.com')
            *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL> insert into t_xifenfei(name) values('www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_xifenfei;

        ID NAME
---------- ------------------------------
         1 www.xifenfei.com
         2 www.orasos.com
         3 www.xifenfei.com

补充说明
1.如果设置了列默认值为seq,则不能手工插入一个该列值否则报ORA-32795
2.通过10046跟踪该insert语句未发现trigger对应sql语句操作,比传统自己编写触发器效率原则上更加高

发表在 ORACLE 12C | 评论关闭

模拟enq: TX – allocate ITL entry等待

发表于 2013 年 4 月 10 日由惜分飞

今天在分析一份awr中发现了较为明显的enq: TX – allocate ITL entry等待,这里通过试验详细重现了enq: TX – allocate ITL entry等待
创建测试对象

SQL> create table t_xifenfei (name char(2000)) pctfree 0 initrans 1;

Table created.

SQL> insert into t_xifenfei select object_name from all_objects    where rownum < 5;

4 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL>  select distinct dbms_rowid.rowid_relative_fno(rowid) file#,
  2  dbms_rowid.rowid_block_number(rowid) block# from t_xifenfei;

     FILE#     BLOCK#
---------- ----------
         4         32

bbed查看block

BBED> set block 32
        BLOCK#          32

BBED> map
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 32                                    Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[4]                                @118     

 ub1 freespace[38]                          @126    --该block空闲空间为38byte 

 ub1 rowdata[8024]                          @164     

 ub4 tailchk                                @8188    


BBED> p ktbbh
struct ktbbh, 72 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x0000d318
      ub4 ktbbhod1                          @24       0x0000d318
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0xc0320e3b
      ub2 kscnwrp                           @32       0x0b2c
   b2 ktbbhict                              @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01000019
   struct ktbbhitl[0], 24 bytes             @44       --1个itl slot为24byte
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0015
         ub2 kxidslt                        @46       0x0019
         ub4 kxidsqn                        @48       0x00000005
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x0080009d
         ub2 kubaseq                        @56       0x0002
         ub1 kubarec                        @58       0x28
      ub2 ktbitflg                          @60       0x2004 (KTBFUPB)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0xc0320e4e
   struct ktbbhitl[1], 24 bytes             @68       --有两个itl slot
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x0000
         ub2 kxidslt                        @70       0x0000
         ub4 kxidsqn                        @72       0x00000000
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x00000000
         ub2 kubaseq                        @80       0x0000
         ub1 kubarec                        @82       0x00
      ub2 ktbitflg                          @84       0x0000 (NONE)
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000

通过bbed我们可以得出如下结论：
1.该block剩余38 byte 空闲空间可以用来存放数据
2.该block 初始化 itl 为2(和我们设置的1不相符)
3.一个itl slot为24byte

更新表记录

--session 1
SQL> select trim(name) from t_xifenfei;

TRIM(NAME)
--------------------------------------------------------------------------------
ICOL$
I_USER1
CON$
UNDO$

SQL> update t_xifenfei set name='WWW.XIFENFEI.COM' WHERE name='ICOL$';

1 row updated.

--session 2
SQL> update t_xifenfei set name='www.orasos.com' where name='UNDO$';

1 row updated.

--session 3
SQL> update t_xifenfei set name='www.xifenfei.com' where name='CON$';

1 row updated.

--session 4
SQL> update t_xifenfei set name='www.xifenfei.com' where name='I_USER1';
--hang住

--session 5
SQL> select event from v$session where  event like 'enq%';

EVENT
----------------------------------------------------------------
enq: TX - allocate ITL entry

通过这里可以看到我们模拟了4个update 该block操作(均未提交)，前面三个可以正常的update操作，第四个出现了enq: TX – allocate ITL entry等待,根据我们知识分析(未提交事务的itl不能覆盖,一个dml操作需要一个itl),这里使用了3个itl slot,而我们已经知道一个itl 需要24byte，该block初始化有2个itl,现在这里有3个dml操作成功,即占用了3个itl,所以该block的剩余空间只有38-24=14 byte<24byte,因此无法分配第四个itl slot从而出现了enq: TX - allocate ITL entry等待
bbed验证上述分析

BBED> map
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 32                                    Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[4]                                @142     

 ub1 freespace[14]                          @150     

 ub1 rowdata[8024]                          @164     

 ub4 tailchk                                @8188    


BBED> p ktbbh
struct ktbbh, 96 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x0000d318
      ub4 ktbbhod1                          @24       0x0000d318
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0xc0320eb0
      ub2 kscnwrp                           @32       0x0b2c
   b2 ktbbhict                              @36       3
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01000019
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0003
         ub2 kxidslt                        @46       0x001f
         ub4 kxidsqn                        @48       0x00000208
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00800027
         ub2 kubaseq                        @56       0x0414
         ub1 kubarec                        @58       0x01
      ub2 ktbitflg                          @60       0x0001 (NONE)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x00000000
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x000a
         ub2 kxidslt                        @70       0x000f
         ub4 kxidsqn                        @72       0x00000185
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x0080008a
         ub2 kubaseq                        @80       0x01a6
         ub1 kubarec                        @82       0x0c
      ub2 ktbitflg                          @84       0x0001 (NONE)
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000
   struct ktbbhitl[2], 24 bytes             @92      
      struct ktbitxid, 8 bytes              @92      
         ub2 kxidusn                        @92       0x0008
         ub2 kxidslt                        @94       0x002a
         ub4 kxidsqn                        @96       0x00000217
      struct ktbituba, 8 bytes              @100     
         ub4 kubadba                        @100      0x008000cc
         ub2 kubaseq                        @104      0x0291
         ub1 kubarec                        @106      0x12
      ub2 ktbitflg                          @108      0x0001 (NONE)
      union _ktbitun, 2 bytes               @110     
         b2 _ktbitfsc                       @110      0
         ub2 _ktbitwrp                      @110      0x0000
      ub4 ktbitbas                          @112      0x00000000

可以看到剩余空间为14byte，事务槽为3个，因此上述分析为正确。

提交会话测试

--session 1
SQL> commit;

Commit complete.

--session 4
SQL> update t_xifenfei set name='www.xifenfei.com' where name='I_USER1';


1 row updated.

证明commit掉事务后,itl slot可以重利用

总结说明
enq: TX – allocate ITL entry为分配ITL条目的等待，因为PCTFREE不足，BLOCK中没有足够空间分配ITL，ORACLE只能重用ITL，但是这个时候由于没有COMMIT，无法重用ITL，所以会出现allocate ITL等待事件。要解决此类问题，我们可以考虑增加PCTFREE和initrans大小,需要注意该修改只能对于新block生效,已经存放数据的block不会发生改变.另外可以考虑修改业务逻辑,减少频繁访问

发表在 Oracle性能优化 | 标签为 enq: TX - allocate ITL entry | 2 条评论

17813235971

QQ 咨询

分类目录归档：数据库

ORACLE 12C TOP N SQL实现分页功能

12c 使用sequence作为列默认值

模拟enq: TX – allocate ITL entry等待

文章分类

最近发表

分类目录归档：数据库

ORACLE 12C TOP N SQL实现分页功能

12c 使用sequence作为列默认值

模拟enq: TX – allocate ITL entry等待

标签云

文章分类

最近发表