-- 一个关于效率问题的测试2 --

Question

-- 周末闲的没事，做了一个关于效率问题的测试，有兴趣的同学可以动动手，大家共同探讨，共同进步！-- 有手机用户好友信息表 ( MobileFrends )
-- 先不考虑表分区问题，也不考虑客房端打过来的数据是否合法(假设程序已经控制了其数据合法问题，去重问题存储过程会处理)
-- 约束：同一个用户的好友只能添加一次，即：primary key(mobile,frendmobile)
-- 以下操作假设你当前用户有足够的表空间，有足够的undo空间！数据库牌归档模式下！CREATE TABLE MobileFrends(
mobile varchar2(20) not null,      -- 手机号
frendmobile varchar2(20) not null, -- 好友手机号
frendname varchar2(100) null,      -- 好友名称
cdate date default sysdate,        -- 创建时间
udate date default sysdate,        -- 修改时间
constraints pk_MobileFrends primary key(mobile,frendmobile)
);-- 构建测试数据：( 假设此表现约有10到20万用户，每个用户约有5到120个好友 )
-- 下面假设每条记录的随机不会违反主键约束，我想这样的随机插入还会违反主键约束，那我应该去买彩票啦----- 经测试，中途还真的出现这样的情况)
declare
  v_rand1 number(18,0);
  v_rand2 number(18,0);
  v_mobile varchar2(20);
begin
  select trunc(dbms_random.value(100000,200001)) into v_rand1 from dual;
  for i in 1..v_rand1 loop
    select trunc(dbms_random.value(5,121)) into v_rand2 from dual;
    select '13'||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))  as mobile into v_mobile
       from dual;
    for j in 1..v_rand2 loop
      insert into MobileFrends(mobile,frendmobile,cdate,udate)
      select v_mobile, '13'||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10))||
           trunc(dbms_random.value(0,10)) as frendmobile,sysdate,sysdate
       from dual;
    end loop;
    commit;
  end loop;
end;
/-- 当然：由于我用的笔记本测试，在测试的过程中出现很多次“Checkpoint not complete”等待事件！先不管它(假设事先我们的mobilefrends所在的表空间足够大)-- 查看生成的总记录数及用户数!
eygle@SZTYORA>  set autotrace on
eygle@SZTYORA>  select count(*), count(distinct mobile) from mobilefrends
  2  /  COUNT(*) COUNT(DISTINCTMOBILE)
---------- ---------------------
   8154248                130406已用时间:  00: 03: 05.25执行计划
----------------------------------------------------------
Plan hash value: 2780501972-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    12 |  9610   (3)| 00:01:56 |
|   1 |  SORT GROUP BY     |              |     1 |    12 |            |          |
|   2 |   TABLE ACCESS FULL| MOBILEFRENDS |  9159K|   104M|  9610   (3)| 00:01:56 |
-----------------------------------------------------------------------------------Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          6  recursive calls
          1  db block gets
      43748  consistent gets
      25556  physical reads
        132  redo size
        492  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed-- 由上可以看出：生成的总记录数为8154248，总用户数为130406，
-- 执行全表扫描，逻辑计次数为43748，物理读次数为25556，执行时间5.25秒-- 创建索引：
create index mobilefrend_indx1 on mobilefrends(mobile);
eygle@SZTYORA> select count(*), count(distinct mobile) from mobilefrends;  COUNT(*) COUNT(DISTINCTMOBILE)
---------- ---------------------
   9318427                148910已用时间:  00: 00: 01.87执行计划
----------------------------------------------------------
Plan hash value: 1521255173-------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |     1 |    12 |  8405   (3)| 00:01:41 |
|   1 |  SORT GROUP BY        |                   |     1 |    12 |            |          |
|   2 |   INDEX FAST FULL SCAN| MOBILEFREND_INDX1 |  9377K|   107M|  8405   (3)| 00:01:41 |
-------------------------------------------------------------------------------------------Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      29985  consistent gets
          0  physical reads
          0  redo size
        492  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed-- 再次查询，可以看到此时采用的是索引扫描，逻辑读次数为 29985，物理读次数为0，执行时间为1.87秒

Answer 1

------------------------------------ 搭建测试环境：-------------------------------------
---------- 测量 redo 的脚本 -------------------------------------------------------------- 要查看生成的redo量相当简单，我们可以使用SQL*Plus的内置特性AUTOTRACE。不过AUTOTRACE只能用于简单的DML，对其他的操作就力所不能及了，
-- 例如：它无法查看一个存储过程调用做了什么。为此，我们需要访问两个动态性能视图：
-- *(1) V$MYSTAT，其中有会话的统计信息。
-- *(2) V$STATNAME，这个视图能告诉我们V$MYSTAT中的每一行表示什么(所查看的统计名)。-- 因为为了测试其性能的需要，我经常要做这种测量，所以使用了两个脚本，分别为mystat和mystat2(我将这两个脚本都保存在C盘根目录下)。
-- mystat.sql脚本把我们感兴趣的统计初始值(如redo大小)保存在一个SQL*Plus变量中：
set verify off
column value new_val V
define S="&1"set autotrace off;
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/-- mystat2.sql脚本只是打印出该统计的初始值和结束值之差：
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/---------------------------------------------------------------------------------------eygle@SZTYORA> select * from (select distinct mobile from mobilefrends order by sys_guid() ) where rownum<10;MOBILE
----------------------------------------
13914226313
13499997213
13898105155
13626495385
13329559458
13132056780
13897188299
13611241935
13547119893已选择9行。------------------------------------------------------------------------------------------------------------eygle@SZTYORA> set timing on
eygle@SZTYORA> select mobile, frendmobile from mobilefrends where mobile=13611241935;MOBILE                                   FRENDMOBILE
---------------------------------------- ----------------------------------------
13611241935                              13511456790
13611241935                              13772394933
13611241935                              13616260471
13611241935                              13233556509
13611241935                              13601951317
13611241935                              13564595719
13611241935                              13608378935
13611241935                              13637199598
13611241935                              13503482506
13611241935                              13705216597
13611241935                              13853669951
13611241935                              13716309920
13611241935                              13111407424
13611241935                              13122505326
13611241935                              13846410891
13611241935                              13562543709
13611241935                              13528888145
13611241935                              13603215533已选择18行。已用时间:  00: 00: 02.09执行计划
----------------------------------------------------------
Plan hash value: 395359246----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   413 |  9912 | 10689   (5)| 00:02:09 |
|*  1 |  TABLE ACCESS FULL| MOBILEFRENDS |   413 |  9912 | 10689   (5)| 00:02:09 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter(TO_NUMBER("MOBILE")=13611241935)Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      37479  consistent gets
          0  physical reads
          0  redo size
       1139  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processedeygle@SZTYORA> select mobile, frendmobile from mobilefrends where mobile='13611241935';MOBILE                                   FRENDMOBILE
---------------------------------------- ----------------------------------------
13611241935                              13111407424
13611241935                              13122505326
13611241935                              13233556509
13611241935                              13503482506
13611241935                              13511456790
13611241935                              13528888145
13611241935                              13562543709
13611241935                              13564595719
13611241935                              13601951317
13611241935                              13603215533
13611241935                              13608378935
13611241935                              13616260471
13611241935                              13637199598
13611241935                              13705216597
13611241935                              13716309920
13611241935                              13772394933
13611241935                              13846410891
13611241935                              13853669951已选择18行。已用时间:  00: 00: 00.01执行计划
----------------------------------------------------------
Plan hash value: 3011189231------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |   413 |  9912 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PK_MOBILEFRENDS |   413 |  9912 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("MOBILE"='13611241935')Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1139  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed-- 可以看到 第一个查询，其条件为 where mobile=13611241935　，执行的是全表扫描，其执行时间为2.09秒；
--          第二个查询，其条件为 where mobile='13611241935'　，执行主键索引扫描，其执行时间为0.01秒；
-- 第二个查询条件仅比第一个查询条件多了两个单引号(')，其效率却比第一个查询的提高了208倍。
-- 这是因为 mobile字段是字符串字段，当执行 where mobile=13611241935 会发生mobile隐式类型转换(将varchar2转变为整型，然后再与13611241935比较)，
-- 导致无法采用索引；而当执行 where mobile='13611241935'，不会有隐式类型转换的情况！
--------------------------------------------------------------------------------------------------------- 现在，有开发人员写了一个批量插入 mobilefrends 表的存储过程： ---
create or replace procedure addfriendsfast1(
  v_mobile in varchar2, -- 是宿主手机号
  v_list in clob)       -- 是传过来的一批、将要将其添加为好友的手机号手机号，以逗号隔开
is
  v_loop number(18,0);
  v_frendmobile mobilefrends.frendmobile%type;
  v_list2 clob;
  v_cnt number(18,0);
begin
  select ','||v_list||',', length(v_list)-length(replace(v_list,',',''))+2 into v_list2, v_loop from dual;
  for i in 2..v_loop loop
    select substr(v_list2,instr(v_list2,',',1,i-1)+1, instr(v_list2,',',1,i)-instr(v_list2,',',1,i-1)-1) into v_frendmobile from dual;
    if v_frendmobile is not null and v_frendmobile<>v_mobile then -- 自己不能将自己添加为好友
      select count(1) into v_cnt from mobilefrends where mobile=v_mobile and frendmobile=v_frendmobile;
      if v_cnt=0 then
        insert into mobilefrends(mobile,frendmobile,cdate,udate) values(v_mobile,v_frendmobile,sysdate,sysdate);
      end if;
    end if;
  end loop;
  commit;
end;
/

Answer 2

-- 测试1：eygle@SZTYORA> select count(*) from mobilefrends where mobile='13897188299';  COUNT(*)
----------
       101eygle@SZTYORA> @c:\mystat.sql "redo size"NAME                           VALUE
------------------------- ----------
redo size                     197660eygle@SZTYORA> exec addfriendsfast1('13897188299','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,13691147539');PL/SQL 过程已成功完成。eygle@SZTYORA> @c:\mystat2.sql "redo size"NAME                               V DIFF
------------------------- ---------- --------------------------------
redo size                     206880            9,220eygle@SZTYORA> select count(*) from mobilefrends where mobile='13897188299';  COUNT(*)
----------
       113-- 可以看到插入12条记录，生成约9220 Byte 的redo日志-- 试想一下：mobilefrends现在有近一千万条记录，若每插入一条记录，要先判断此记录在mobilefrends表中是否已经存在，
-- 那么当我传过来300条记录时，就是执行“ select count(1) into v_cnt from mobilefrends where mobile=v_mobile and frendmobile=v_frendmobile; ”这个语句300次
-- 此方法显然是不可取的-- 再看方法二(利用临时表)：
create global temporary table mobilefrends_mem(frendmobile varchar2(20))
on commit delete rows;create or replace procedure addfriendsfast2(
  v_mobile in varchar2, -- 是宿主手机号
  v_list in clob)       -- 是传过来的一批、将要将其添加为好友的手机号手机号，以逗号隔开
is
  v_loop number(18,0);
  v_frendmobile mobilefrends.frendmobile%type;
  v_list2 clob;
  v_cnt number(18,0);
begin
  select ','||v_list||',', length(v_list)-length(replace(v_list,',',''))+2 into v_list2, v_loop from dual;
  for i in 2..v_loop loop
    insert into mobilefrends_mem(frendmobile)
    select substr(v_list2,instr(v_list2,',',1,i-1)+1, instr(v_list2,',',1,i)-instr(v_list2,',',1,i-1)-1) as frendmobile
     from dual;
  end loop;
  insert into mobilefrends(mobile,frendmobile,cdate,udate)
  select distinct v_mobile, t1.frendmobile, sysdate, sysdate -- 去重
  from mobilefrends_mem t1
  where t1.frendmobile not in (select t2.frendmobile from mobilefrends t2 where t2.mobile=v_mobile )
    and t1.frendmobile is not null -- 排除空值
    and t1.frendmobile<>v_mobile;  -- 自己不能将自己添加为好友
  commit;
end;
/-- 测试2：eygle@SZTYORA> select count(*) from mobilefrends where mobile='13898105155';  COUNT(*)
----------
         9eygle@SZTYORA> @c:\mystat.sql "redo size"NAME                           VALUE
------------------------- ----------
redo size                     206880eygle@SZTYORA> exec addfriendsfast1('13898105155','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,13691147539');PL/SQL 过程已成功完成。eygle@SZTYORA> @c:\mystat2.sql "redo size"NAME                               V DIFF
------------------------- ---------- --------------------------------
redo size                     216072            9,192eygle@SZTYORA> select count(*) from mobilefrends where mobile='13898105155';  COUNT(*)
----------
        21-- 可以看到插入12条记录，生成约9192 Byte 的redo日志-- 再看插入24条记录时两种方法的执行时间：
eygle@SZTYORA> set timing on;eygle@SZTYORA> exec addfriendsfast1('13898117070','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,13691147539,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');PL/SQL 过程已成功完成。已用时间:  00: 00: 00.09eygle@SZTYORA> exec addfriendsfast2('13698117899','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,13691147539,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');PL/SQL 过程已成功完成。已用时间:  00: 00: 00.06-- 可以看到，插入24条记录，方法二比方法一要快三分之一倍，随着插入记录的增多，这个比率将会有更大的提高(您可以多测试一下)-- 再看方法三(利用connect by 行转列)：
create or replace procedure addfriendsfast3(
  v_mobile in varchar2, -- 是宿主手机号
  v_list in clob)       -- 是传过来的一批、将要将其添加为好友的手机号手机号，以逗号隔开
is
begin
    insert into MobileFrends(mobile, frendmobile, cdate, udate)
    with a as( select distinct to_char(substr(','||v_list||',',instr(','||v_list||',',',',1,level)+1,
                                       instr(','||v_list||',',',',1,level+1)-instr(','||v_list||',',',',1,level)-1)) frendmobile
                 from dual
              connect by
                level <= length(','||v_list||',') - length(replace(','||v_list||',', ',', ''))-1 )
    select v_mobile, a.frendmobile, sysdate, sysdate
      from a
     where not exists (select 1 from MobileFrends b where b.mobile=v_mobile and b.frendmobile=a.frendmobile)
       and a.frendmobile is not null
       and a.frendmobile<>v_mobile; -- 自己不能将自己添加为好友
  commit;
end;
/eygle@SZTYORA> exec addfriendsfast2('13914226313','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,13691147539,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');PL/SQL 过程已成功完成。已用时间:  00: 00: 00.23eygle@SZTYORA> exec addfriendsfast3('13698117899','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,13691147539,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');PL/SQL 过程已成功完成。已用时间:  00: 00: 00.09eygle@SZTYORA> exec addfriendsfast2('13914226314','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,13691147539,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');PL/SQL 过程已成功完成。已用时间:  00: 00: 00.23eygle@SZTYORA> exec addfriendsfast3('13698117889','13511456790,13877776666,13900124444,13566778899,15077094125,15900897764,13690145561,15941307721,15879421340,13244791488,15169177743,13691147539,13299472833,13390907765,15594309191,15899997819,13444456672,13699954646,13766889910,15001438871,15019181663,13941566788,13177455468,13301522233');PL/SQL 过程已成功完成。已用时间:  00: 00: 00.09
-- 还有：由于方法一采用的是循环判断插入方法，所以同一批记录的cdate、udate并不相同，但方法二和方法三不会出现这种情况！------------------------------------------------------------------------------------------------------------------------------------- 附：生产库运行版的真实存储过程如下(还考虑了frendname字段)：-- 用序列，用于去除重复手机号的数据
-- (如果手机号重复，以最后一个输入的手机号为准，例如：13691147539:luoyoumou1,13691147539:luoyoumou2，则真正插入原表的记录应该是13691147539:luoyoumou2)CREATE GLOBAL TEMPORARY TABLE MobileFrends_Mem(
frendmobile varchar2(20) not null,
frendname varchar2(100) null
) on commit delete rows;create or replace procedure add_friends(
  v_mobile in varchar2,
  v_list in clob)
as
  v_mobile2 mobilefrends.mobile%type;
begin
  -- v_list 传入的格式: 手机号:名称\n手机号:名称\n手机号:名称\n....
  v_mobile2 := trim(v_mobile);
  if v_mobile2 is not null and lengthb(v_mobile2)<=20 then
    insert into MobileFrends_Mem(frendmobile, frendname)
    with a as (
      select to_char(substr(','||v_list||',',instr(','||v_list||',',',',1,level)+1,
                    instr(','||v_list||',',',',1,level+1)-instr(','||v_list||',',',',1,level)-1)) frendmobile
        from dual
     connect by
       level <= length(','||v_list||',')-length(replace(','||v_list||',',',',''))-1),
         b as (select trim(substr(a.frendmobile,1,instr(a.frendmobile,':')-1)) as frendmobile, 
                      trim(substr(a.frendmobile,instr(a.frendmobile,':')+1)) as frendname
                 from a )
    select b.frendmobile, b.frendname frendname -- 考虑手机号前后带有空格的情况
     from b
    where lengthb(b.frendmobile)<=20 and lengthb(b.frendname)<=100
      and b.frendmobile is not null
      and b.frendmobile<>v_mobile2; -- 不能将自己添加为自己的好友    -- 去除重复数据：
    delete from MobileFrends_mem a1
     where rowid not in (select max(rowid) from MobileFrends_mem a2 group by a2.frendmobile);    merge into MobileFrends a
    using MobileFrends_mem b on (a.mobile=v_mobile2 and a.frendmobile=b.frendmobile)
    when matched then
    update set a.frendname=b.frendname,a.udate=sysdate
    where a.frendname<>b.frendname 
    when not matched then
    insert(mobile,frendmobile,frendname,cdate,udate)
    values(v_mobile2,b.frendmobile,b.frendname,sysdate,sysdate);
    commit;
  end if;
exception when others then
  rollback;
end;
/exec add_friends('13691147539','15077094125:程彩翼,13699154724:罗有勇,15077498871:小明');select * from mobilefrends
where mobile='13691147539';exec add_friends('13691147539','15077094125:程彩翼,13699154724:罗有勇,15077498871:小明,13344778891:李小花,15077094125:老婆');

Answer 3

对于oracle 编程还在学习中，希望会的朋友多回复，顶