Oracle学习笔记从案例中看索引的开销（十四）

/*
  为了提高建索引的效率，采用了并行的方式，并且设到了索引的属性中去了，引发了性能故障。
  一般来说，如果我们要做并行的操作，建议用HINT的方式给查询语句加索引，比如/*+parallel n*/
*/

drop table t purge;
create table t as select * from dba_objects where object_id is not null;
alter table T modify object_id not null;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
commit;

set timing on
create index idx_object_id on t(object_id) parallel 8;

索引已创建。

已用时间:  00: 00: 09.85


select index_name,degree from user_indexes where table_name=‘T‘;
INDEX_NAME                     DEGREE
------------------------------ -------
IDX_OBJECT_ID                  8



set linesize 1000
set autotrace traceonly

select count(*) from t;
执行计划
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |     1 |  5797   (2)| 00:01:10 |        |      |         |
|   1 |  SORT AGGREGATE           |               |     1 |            |          |        |      |         |
|   2 |   PX COORDINATOR          |               |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000      |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |               |     1 |            |          |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR    |               |  8100K|  5797   (2)| 00:01:10 |  Q1,00 | PCWC |         |
|   6 |       INDEX FAST FULL SCAN| IDX_OBJECT_ID |  8100K|  5797   (2)| 00:01:10 |  Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
         24  recursive calls
          0  db block gets
      25365  consistent gets
      20769  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          

set autotrace off
alter index   IDX_OBJECT_ID noparallel;
select index_name,degree from user_indexes where table_name=‘T‘;
INDEX_NAME                     DEGREE
------------------------------ -------
IDX_OBJECT_ID                  1        

SQL> select count(*) from t;
执行计划
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |  5797   (2)| 00:01:10 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID |  8100K|  5797   (2)| 00:01:10 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      20828  consistent gets
          0  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

/*  
  结论：如果表没有索引，插入的速度一般都不会慢，只有在有索引的情况下，才要考虑插入速度的优化。
        如果表有大量索引，一般来说，分区表的局部索引由于只需要更新局部分区的索引，所以索引的开销会比较小，所以插入性能比
        有着相同的记录数，列及索引的普通表更快。
  
*/

--构造分区表，插入数据。
drop table range_part_tab purge;
create table range_part_tab (id number,deal_date date,area_code number,nbr1 number,nbr2 number,nbr3 number,contents varchar2(4000))
           partition by range (deal_date)
           (
           partition p_201301 values less than (TO_DATE(‘2013-02-01‘, ‘YYYY-MM-DD‘)),
           partition p_201302 values less than (TO_DATE(‘2013-03-01‘, ‘YYYY-MM-DD‘)),
           partition p_201303 values less than (TO_DATE(‘2013-04-01‘, ‘YYYY-MM-DD‘)),
           partition p_201304 values less than (TO_DATE(‘2013-05-01‘, ‘YYYY-MM-DD‘)),
           partition p_201305 values less than (TO_DATE(‘2013-06-01‘, ‘YYYY-MM-DD‘)),
           partition p_201306 values less than (TO_DATE(‘2013-07-01‘, ‘YYYY-MM-DD‘)),
           partition p_201307 values less than (TO_DATE(‘2013-08-01‘, ‘YYYY-MM-DD‘)),
           partition p_201308 values less than (TO_DATE(‘2013-09-01‘, ‘YYYY-MM-DD‘)),
           partition p_201309 values less than (TO_DATE(‘2013-10-01‘, ‘YYYY-MM-DD‘)),
           partition p_201310 values less than (TO_DATE(‘2013-11-01‘, ‘YYYY-MM-DD‘)),
           partition p_201311 values less than (TO_DATE(‘2013-12-01‘, ‘YYYY-MM-DD‘)),
           partition p_201312 values less than (TO_DATE(‘2014-01-01‘, ‘YYYY-MM-DD‘)),
           partition p_201401 values less than (TO_DATE(‘2014-02-01‘, ‘YYYY-MM-DD‘)),
           partition p_201402 values less than (TO_DATE(‘2014-03-01‘, ‘YYYY-MM-DD‘)),
           partition p_201403 values less than (TO_DATE(‘2014-04-01‘, ‘YYYY-MM-DD‘)),
           partition p_201404 values less than (TO_DATE(‘2014-05-01‘, ‘YYYY-MM-DD‘)),
           partition p_max values less than (maxvalue)
           )
           ;

--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有100万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr1,nbr2,nbr3,contents)
      select rownum,
             to_date( to_char(sysdate-365,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,365)),‘J‘),
             ceil(dbms_random.value(591,599)),
             ceil(dbms_random.value(18900000001,18999999999)),
             ceil(dbms_random.value(18900000001,18999999999)),
             ceil(dbms_random.value(18900000001,18999999999)),
             rpad(‘*‘,400,‘*‘)
        from dual
      connect by rownum <= 2000000;
commit;


--以下是插入2014年部分日期随机数和表示福建地区号含义（591到599）的随机数记录，共有20万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr1,nbr2,nbr3,contents)
      select rownum,
             to_date( to_char(sysdate,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,60)),‘J‘),
             ceil(dbms_random.value(591,599)),
             ceil(dbms_random.value(18900000001,18999999999)),
             ceil(dbms_random.value(18900000001,18999999999)),
             ceil(dbms_random.value(18900000001,18999999999)),
             rpad(‘*‘,400,‘*‘)
        from dual
      connect by rownum <= 400000;
commit;

create index idx_parttab_id on range_part_tab(id) local;
create index idx_parttab_nbr1 on range_part_tab(nbr1) local;
create index idx_parttab_nbr2 on range_part_tab(nbr2) local;
create index idx_parttab_nbr3 on range_part_tab(nbr3) local;
create index idx_parttab_area on range_part_tab(area_code) local;


drop table normal_tab purge;
create table normal_tab (id number,deal_date date,area_code number,nbr1 number,nbr2 number,nbr3 number,contents varchar2(4000));
insert into normal_tab select * from range_part_tab;
commit;
create index idx_tab_id on normal_tab(id) ;
create index idx_tab_nbr1 on normal_tab(nbr1) ;
create index idx_tab_nbr2 on normal_tab(nbr2) ;
create index idx_tab_nbr3 on normal_tab(nbr3) ;
create index idx_tab_area on normal_tab(area_code) ; 

select count(*) from normal_tab where deal_date>=TO_DATE(‘2014-02-01‘, ‘YYYY-MM-DD‘) and deal_date<TO_DATE(‘2014-03-01‘, ‘YYYY-MM-DD‘);
select count(*) from range_part_tab where deal_date>=TO_DATE(‘2014-02-01‘, ‘YYYY-MM-DD‘) and deal_date<TO_DATE(‘2014-03-01‘, ‘YYYY-MM-DD‘);


set timing on 
insert into range_part_tab 
     select rownum,
             to_date( to_char(sysdate+60,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,60)),‘J‘),
             ceil(dbms_random.value(591,599)),
             ceil(dbms_random.value(18900000001,18999999999)),
             ceil(dbms_random.value(18900000001,18999999999)),
             ceil(dbms_random.value(18900000001,18999999999)),
             rpad(‘*‘,400,‘*‘)
        from dual
      connect by rownum <= 400000;
commit;

insert into normal_tab 
     select rownum,
             to_date( to_char(sysdate+60,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,60)),‘J‘),
             ceil(dbms_random.value(591,599)),
             ceil(dbms_random.value(18900000001,18999999999)),
             ceil(dbms_random.value(18900000001,18999999999)),
             ceil(dbms_random.value(18900000001,18999999999)),
             rpad(‘*‘,400,‘*‘)
        from dual
      connect by rownum <= 400000;
commit;

SQL> set timing on
SQL> insert into range_part_tab
  2       select rownum,
  3               to_date( to_char(sysdate+60,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,60)),‘J‘),
  4               ceil(dbms_random.value(591,599)),
  5               ceil(dbms_random.value(18900000001,18999999999)),
  6               ceil(dbms_random.value(18900000001,18999999999)),
  7               ceil(dbms_random.value(18900000001,18999999999)),
  8               rpad(‘*‘,400,‘*‘)
  9          from dual
 10        connect by rownum <= 400000;

已创建400000行。
已用时间:  00: 00: 51.20

SQL> insert into normal_tab
  2       select rownum,
  3               to_date( to_char(sysdate+60,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,60)),‘J‘),
  4               ceil(dbms_random.value(591,599)),
  5               ceil(dbms_random.value(18900000001,18999999999)),
  6               ceil(dbms_random.value(18900000001,18999999999)),
  7               ceil(dbms_random.value(18900000001,18999999999)),
  8               rpad(‘*‘,400,‘*‘)
  9          from dual
 10        connect by rownum <= 400000;

已创建400000行。
已用时间:  00: 01: 20.04

/*  
  结论：普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞。
        而ONLINE建索引的方式却是不会阻止针对该表的更新操作，与建普通索引相反的是，ONLINE建索引的动作是反过来被更新操作阻塞。
*/

drop table t purge;
create table t as select * from dba_objects;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
commit;
select sid from v$mystat where rownum=1; 
--12
set timing on
create index idx_object_id on t(object_id) online;
索引已创建。


session 2
sqlplus ljb/ljb
set linesize 1000
select sid from v$mystat where rownum=1; 
--134
--以下执行居然不会被阻塞
update t set object_id=99999 where object_id=8;


session 3
set linesize 1000
select * from v$lock where sid in (12,134);

SQL> select * from v$lock where sid in (134,12);

SQL> select * from v$lock where sid in (134,12);

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EB79320 2EB7934C         12 AE        100          0          4          0        278          0
2EB79394 2EB793C0        134 AE        100          0          4          0        303          0
2EB79408 2EB79434         12 DL     106831          0          3          0         25          0
2EB79574 2EB795A0         12 DL     106831          0          3          0         25          0
2EB795E8 2EB79614         12 OD     106831          0          4          0         25          0
2EB7965C 2EB79688         12 TX     131079      31688          0          4         11          0
0EDD7A9C 0EDD7ACC        134 TM     106831          0          3          0         23          0
0EDD7A9C 0EDD7ACC         12 TM     106831          0          2          0         25          0
0EDD7A9C 0EDD7ACC         12 TM     106834          0          4          0         25          0
2C17C3B8 2C17C3F8        134 TX     131079      31688          6          0         23          1
2C1A2448 2C1A2488         12 TX     589853      31754          6          0         25          0

已选择11行。

select  /*+no_merge(a) no_merge(b) */
(select username from v$session where sid=a.sid) blocker,
a.sid, ‘is blocking‘,
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a,v$lock b
where a.block=1 and b.request>0
and a.id1=b.id1
and a.id2=b.id2;

BLOCKER                               SID ‘ISBLOCKING BLOCKEE                               SID
------------------------------ ---------- ----------- ------------------------------ ----------
LJB                                   134 is blocking LJB                                    12