[20190219]那个更快(11g).txt

--//前几天测试11g Query Result Cache RC Latches时,链接http://blog.itpub.net/267265/viewspace-2632907/
--//有网友指出测试有问题,建立索引唯一,并不会导致select count(*) from t,选择索引执行.实际上执行计划还是全表扫描.
--//也就有了如下测试,不过结果有点让我吃惊,设置not null反而更慢.通过测试说明:
--//另外我也做了10g下的测试,链接如下:http://blog.itpub.net/267265/viewspace-2636321/ => [20190215]那个更快(10g).txt

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t as select rownum id from dual ;
--//分析表略.
--//另外说明一下,先建立表主要避免编译过程时报错.

2.建立测试环境:
create table job_times (sid number, time_ela number,method varchar2(20));

CREATE OR REPLACE PROCEDURE do_work
(
   p_iterations   IN NUMBER
  ,p_method       IN VARCHAR2
)
IS
   l_rowid   ROWID;
   v_t       NUMBER;
BEGIN
   INSERT INTO job_times VALUES ( SYS_CONTEXT ('userenv', 'sid') ,DBMS_UTILITY.get_time ,p_method) RETURNING ROWID INTO l_rowid;

   FOR i IN 1 .. p_iterations
   LOOP
      SELECT COUNT (*) INTO v_t FROM t;
   END LOOP;

   UPDATE job_times SET time_ela = DBMS_UTILITY.get_time - time_ela WHERE ROWID = l_rowid;

   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE do_work1
(
   p_iterations   IN NUMBER
  ,p_method       IN VARCHAR2
)
IS
   l_rowid   ROWID;
   v_t       NUMBER;
BEGIN
   INSERT INTO job_times VALUES ( SYS_CONTEXT ('userenv', 'sid') ,DBMS_UTILITY.get_time ,p_method) RETURNING ROWID INTO l_rowid;

   FOR i IN 1 .. p_iterations
   LOOP
      SELECT COUNT (*) INTO v_t FROM t where id=1;
   END LOOP;

   UPDATE job_times SET time_ela = DBMS_UTILITY.get_time - time_ela WHERE ROWID = l_rowid;

   COMMIT;
END;
/

3.测试:
--//执行脚本如下:注一定要等N个会话执行完成在回车,进行下一项测试.
--//可以打开另外的会话执行select method,count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times group by method order by 3 ;
--//确定测试是否完成.

$ cat bb.txt
delete from job_times;
commit ;
drop table t purge;
create table t as select rownum id from dual ;
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
alter procedure do_work compile ;
alter procedure do_work1 compile ;
host sleep 5

host seq &&1 | xargs -I{} echo "sqlplus -s -l  scott/&&2 <<< \"execute do_work(&&3,'null')\" & "  | bash > /dev/null
host read -p 'wait finish...'

create unique index pk_t on t(id);
alter table t modify (id  not null);
host seq &&1 | xargs -I{} echo "sqlplus -s -l  s