测试设置多快读参数性能

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

 

 

SQL> conn scott/tiger
Connected.

SQL> create table big_table
  2  as
  3  select rownum id, a.*
  4  from all_objects a
  5  where 1=0;

Table created.

SQL> alter table big_table nologging;

Table altered.

SQL> SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='BIG_TABLE';

TABLESPACE_NAME
------------------------------
USERS

---SYS DBA
SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='TABLE' AND TABLESPACE_NAME='USERS'
--11g 这里查不到big_table 也许是nologging的原因。
--这里是要估计big_table 大于 data buffer cache 尺寸 为后面实验做准备；

select bytes from dba_segments where wner='SCOTT' and segment_name='BIG_TABLE'
7340032

SQL> select * from v$sgainfo
  2  ;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1337128 No
Redo Buffers                        6037504 No
Buffer Cache Size                  96468992 Yes
Shared Pool Size                  176160768 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                   4194304 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  447778816 No
Startup overhead in Shared Pool    58720256 No

NAME                                  BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available         155189248

--这里databuffer 大概有100m ，所以我们插入big_table 表 使得大于100m；

 

*************************************************************

declare
l_cnt number;
l_rows number := &1;
begin
    insert /*+ append */
    into big_table
    select rownum, a.*
    from all_objects a;
    l_cnt := sql%rowcount;
    commit;
    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into big_table select
        ROWNUM,OWNER, OBJECT_NAME, SUBOBJECT_NAME,
        OBJECT_ID, DATA_OBJECT_ID,
        OBJECT_TYPE, CREATED, LAST_DDL_TIME,
        TIMESTAMP, STATUS, TEMPORARY,
        GENERATED, SECONDARY,NAMESPACE,EDITION_NAME
        from big_table
        where rownum <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/

--输入99999

-----------------------------------------

SET SERVEROUTPUT ON

declare
 l_count PLS_INTEGER;
 l_time NUMBER(10,1);
 l_starting_time PLS_INTEGER;
 l_ending_time PLS_INTEGER;
 l_blocks PLS_INTEGER;
 l_starting_blocks PLS_INTEGER;
 l_ending_blocks PLS_INTEGER;
 l_dbfmbrc PLS_INTEGER;
BEGIN
 dbms_output.put_line('dbfmbrc blocks seconds');
 FOR i IN 1..16
 LOOP
  l_dbfmbrc :=i*2;
 
  execute immediate 'alter session set db_file_multiblock_read_count='||l_dbfmbrc;
 
  execute immediate 'alter system set events ''immediate trace name flush_cache''';
 
  select value into l_starting_blocks
  from v$mystat ms JOIN v$statname USING(statistic#)
  where name='physical reads';
 
  l_starting_time:=dbms_utility.get_time();
  select count(*) into l_count from scott.big_table;
  l_ending_time :=dbms_utility.get_time();
 
  select value into l_ending_blocks
  from v$mystat ms join v$statname using(statistic#)
  where name ='physical reads';
 
  l_time :=round((l_ending_time-l_starting_time)/100,1);
  l_blocks :=l_ending_blocks-l_starting_blocks;
  dbms_output.put_line(l_dbfmbrc||' '||l_blocks||' '||to_char(l_time));
  END LOOP;
  END;
/

 

dbfmbrc blocks seconds
2 18314 .4
4 18314 .2
6 18314 .1
8 18314 .1
10 18314 .1
12 18314 .1
14 18314 .1
16 18314 .1
18 18314 .1
20 18314 .1
22 18314 .1
24 18314 .1
26 18314 .1
28 18314 .1
30 18314 .1
32 18314 .1

PL/SQL procedure successfully completed.

 

 

SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     32

 

可以看到，在此参数6-32之间 多块读性能最好。

 

---

11g 这里 此参数貌似自动调整。因为我之前的是默认 42.。。。。。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/713360/viewspace-710922/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/713360/viewspace-710922/