https://community.oracle.com/thread/3647112?start=30&tstart=0
I Just tried it on my test Windows 2008/ 11.2.0.3
By increasing the DB_FILE_MULTIBLOCK_READ_COUNT- It significantly improves the response time for DIRECT PATH READ
SQL> show parameter multiblock
NAME TYPE VALUE
SQL> ALTER SESSION SET sql_trace=TRUE;
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
SQL> select count(*) from big_table;
COUNT(*)
----------
37118976
SQL> set timing on
SQL> /
COUNT(*)
----------
37118976
Elapsed: 00:00:24.91
and the trace file is the below
PARSING IN CURSOR #335902688 len=31 dep=0 uid=0 oct=3 lid=0 tim=2775080312320 hv=1330050261 ad='7ff151fb990' sqlid='bd5mjp97ndx6p'
select count(*) from big_table
END OF STMT
PARSE #335902688:c=15600,e=57945,p=6,cr=94,cu=0,mis=1,r=0,dep=0,og=1,plh=599409829,tim=2775080312318
EXEC #335902688:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2775080312473
WAIT #335902688: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=9 tim=2775080312514
WAIT #335902688: nam='direct path read' ela= 16918 file number=1 first dba=90329 block cnt=39 obj#=81856 tim=2775080330042
WAIT #335902688: nam='direct path read' ela= 1302 file number=1 first dba=96512 block cnt=88 obj#=81856 tim=2775080331939
WAIT #335902688: nam='direct path read' ela= 48985 file number=1 first dba=96640 block cnt=128 obj#=81856 tim=2775080381642
WAIT #335902688: nam='direct path read' ela= 2208 file number=1 first dba=96768 block cnt=128 obj#=81856 tim=2775080384758
WAIT #335902688: nam='direct path read' ela= 641 file number=1 first dba=97024 block cnt=128 obj#=81856 tim=2775080387051
Now increasing the multiblock read count
SQL> alter session set db_file_multiblock_read_count=1024;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from big_table;
COUNT(*)
----------
37118976
Elapsed: 00:00:09.31
and the trace is below for this
ARSING IN CURSOR #335902688 len=52 dep=0 uid=0 oct=42 lid=0 tim=2775211375349 hv=302278082 ad='0' sqlid='7d0uktn908tf2'
alter session set db_file_multiblock_read_count=1024
END OF STMT
PARSE #335902688:c=0,e=205,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=2775211375348
EXEC #335902688:c=0,e=114,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=2775211375513
WAIT #335902688: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775211375544
*** 2015-01-13 10:56:04.533
WAIT #335902688: nam='SQL*Net message from client' ela= 12462509 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775223838075
CLOSE #335902688:c=0,e=16,dep=0,type=1,tim=2775223838194
=====================
PARSING IN CURSOR #348591800 len=399 dep=1 uid=0 oct=3 lid=0 tim=2775223840266 hv=2987523179 ad='7ff1528ad50' sqlid='8mu3z0kt13z3b'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("BIG_TABLE") FULL("BIG_TABLE") NO_PARALLEL_INDEX("BIG_TABLE") */ 1 AS C1, 1 AS C2 FROM "SYS"."BIG_TABLE" SAMPLE BLOCK (0.011589 , 1) SEED (1) "BIG_TABLE") SAMPLESUB
END OF STMT
PARSE #348591800:c=0,e=1202,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=4255388684,tim=2775223840264
EXEC #348591800:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=4255388684,tim=2775223840402
FETCH #348591800:c=15600,e=9533,p=0,cr=93,cu=0,mis=0,r=1,dep=1,og=1,plh=4255388684,tim=2775223849959
STAT #348591800 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=93 pr=0 pw=0 time=9536 us)'
STAT #348591800 id=2 cnt=4454 pid=1 pos=1 obj=81856 op='TABLE ACCESS SAMPLE BIG_TABLE (cr=93 pr=0 pw=0 time=5521 us cost=2 size=12 card=1)'
CLOSE #348591800:c=0,e=6,dep=1,type=0,tim=2775223850075
=====================
PARSING IN CURSOR #348592672 len=31 dep=0 uid=0 oct=3 lid=0 tim=2775223850333 hv=2920928257 ad='7ff1f316670' sqlid='a9c9vrkr1mp01'
select count(*) from big_table
END OF STMT
PARSE #348592672:c=15600,e=12081,p=0,cr=94,cu=0,mis=1,r=0,dep=0,og=1,plh=599409829,tim=2775223850331
EXEC #348592672:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2775223850406
WAIT #348592672: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775223850437
WAIT #348592672: nam='direct path read' ela= 21781 file number=1 first dba=90329 block cnt=39 obj#=81856 tim=2775223872881
WAIT #348592672: nam='direct path read' ela= 161673 file number=1 first dba=96640 block cnt=640 obj#=81856 tim=2775224039504
WAIT #348592672: nam='direct path read' ela= 18055 file number=1 first dba=98304 block cnt=1024 obj#=81856 tim=2775224075163
WAIT #348592672: nam='direct path read' ela= 35412 file number=1 first dba=99328 block cnt=1024 obj#=81856 tim=2775224117398
WAIT #348592672: nam='direct path read' ela= 63280 file number=1 first dba=100352 block cnt=640 obj#=81856 tim=2775224187153
*** 2015-01-13 10:56:04.907
WAIT #348592672: nam='direct path read' ela= 18056 file number=1 first dba=100992 block cnt=384 obj#=81856 tim=2775224209450
WAIT #348592672: nam='direct path read' ela= 160670 file number=1 first dba=101376 block cnt=1024 obj#=81856 tim=2775224373623
WAIT #348592672: nam='direct path read' ela= 16050 file number=1 first dba=103424 block cnt=1024 obj#=81856 tim=2775224407786
WAIT #348592672: nam='direct path read' ela= 29265 file number=1 first dba=104448 block cnt=1024 obj#=81856 tim=2775224443701
WAIT #348592672: nam='direct path read' ela= 13106 file number=1 first dba=105472 block cnt=1024 obj#=81856 tim=2775224463860
WAIT #348592672: nam='direct path read' ela= 12313 file number=1 first dba=106496 block cnt=1024 obj#=81856 tim=2775224483484
WAIT #348592672: nam='direct path read' ela= 5320 file number=1 first dba=107520 block cnt=1024 obj#=81856 tim=2775224496871
WAIT #348592672: nam='direct path read' ela= 12907 file number=1 first dba=108544 block cnt=1024 obj#=81856 tim=2775224517080
WAIT #348592672: nam='direct path read' ela= 20352 file number=1 first dba=109568 block cnt=1024 obj#=81856 tim=2775224544060
WAIT #348592672: nam='direct path read' ela= 23962 file number=1 first dba=111616 block cnt=1024 obj#=81856 tim=2775224582782
WAIT #348592672: nam='direct path read' ela= 11246 file number=1 first dba=113664 block cnt=1024 obj#=81856 tim=2775224612256
WAIT #348592672: nam='direct path read' ela= 11687 file number=1 first dba=114688 block cnt=1024 obj#=81856 tim=2775224630478
WAIT #348592672: nam='direct path read' ela= 19856 file number=1 first dba=115712 block cnt=1024 obj#=81856 tim=2775224656787
WAIT #348592672: nam='direct path read' ela= 19851 file number=1 first dba=116736 block cnt=1024 obj#=81856 tim=2775224685003
WAIT #348592672: nam='direct path read' ela= 18767 file number=1 first dba=118784 block cnt=1024 obj#=81856 tim=2775224718882
WAIT #348592672: nam='direct path read' ela= 15290 file number=1 first dba=120832 block cnt=1024 obj#=81856 tim=2775224752371
WAIT #348592672: nam='direct path read' ela= 26578 file number=1 first dba=122880 block cnt=1024 obj#=81856 tim=2775224794862
WAIT #348592672: nam='direct path read' ela= 17840 file number=1 first dba=124928 block cnt=1024 obj#=81856 tim=2775224828274
WAIT #348592672: nam='direct path read' ela= 10446 file number=1 first dba=126208 block cnt=768 obj#=81856 tim=2775224847268
WAIT #348592672: nam='direct path read' ela= 14746 file number=1 first dba=126976 block cnt=1024 obj#=81856 tim=2775224867798
WAIT #348592672: nam='direct path read' ela= 13894 file number=1 first dba=129024 block cnt=1024 obj#=81856 tim=2775224896643
WAIT #348592672: nam='direct path read' ela= 4059 file number=1 first dba=130048 block cnt=1024 obj#=81856 tim=2775224908034
WAIT #348592672: nam='direct path read' ela= 8972 file number=1 first dba=131072 block cnt=1024 obj#=81856 tim=2775224924395
WAIT #348592672: nam='direct path read' ela= 5014 file number=1 first dba=132096 block cnt=1024 obj#=81856 tim=2775224936765
WAIT #348592672: nam='direct path read' ela= 9246 file number=1 first dba=133120 block cnt=1024 obj#=81856 tim=2775224953730
So repeats the same behaviour mentioned in https://fritshoogland.wordpress.com/tag/oracle-io-multiblock-read-mbrc-multiblock-read-count/
I just split the trace file for clarity- attached is the untouced onespool.GIFTrace.GIFTrace128block.GIF
But changing the value may drive towards lot of fullscans .
I just tried by creating a 16KB block tablespace and it doesnt sound good...
copied the same table to the 16KB block and it performs bad actually
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,
2 dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tabl
3 esPace_name=b.tablespace_name;
esPace_name=b.tablespace_name
*
ERROR at line 3:
ORA-00920: invalid relational operator
SQL> CREATE TABLESPACE big_block DATAFILE 'D:\oracledb\oradata\LMSDB\sb01.dbf' S
IZE 2048M
2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
3 BLOCKSIZE 16K;
Tablespace created.
Elapsed: 00:00:12.09
SQL> create table big_table_bigblock tablespace big_block as select * from big_t
able;
Table created.
SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tablesPace_name=b.tablespace_name;
BLOCK_SIZE TABLESPACE_NAME TABLE_NAME
---------- ------------------------------ ------------------------------
8192 SYSTEM BIG_TABLE
16384 BIG_BLOCK BIG_TABLE_BIGBLOCK
SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tablesPace_name=b.tablespace_name;
BLOCK_SIZE TABLESPACE_NAME TABLE_NAME
---------- ------------------------------ ------------------------------
8192 SYSTEM BIG_TABLE
16384 BIG_BLOCK BIG_TABLE_BIGBLOCK
SQL> show parameter multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
parallel_adaptive_multi_user boolean TRUE
SQL> select bytes/1024/1024/1025 as GB, segment_name from dba_segments where segment_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK');
GB
----------
SEGMENT_NAME
--------------------------------------------------------------------------------
1.22195122
BIG_TABLE_BIGBLOCK
1.24878049
BIG_TABLE
SQL> select count(*) from big_table;
COUNT(*)
----------
11000000
SQL> select count(*) from big_table_big_block;
select count(*) from big_table_big_block
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from big_table_bigblock;
COUNT(*)
----------
11000000
SQL> spoll off
SP2-0042: unknown command "spoll off" - rest of line ignored.
SQL> spool off
PARSING IN CURSOR #101737960 len=30 dep=0 uid=0 oct=3 lid=0 tim=2787353576538 hv=1326693165 ad='7ff25361b18' sqlid='6fxg0qj7j7ftd'
select count(*) from big_table
END OF STMT
PARSE #101737960:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2787353576537
EXEC #101737960:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2787353576657
WAIT #101737960: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=589 tim=2787353576691
WAIT #101737960: nam='direct path read' ela= 24563 file number=1 first dba=90585 block cnt=39 obj#=83398 tim=2787353602067
WAIT #101737960: nam='direct path read' ela= 11121 file number=1 first dba=98176 block cnt=128 obj#=83398 tim=2787353614787
WAIT #101737960: nam='direct path read' ela= 7432 file number=1 first dba=98304 block cnt=128 obj#=83398 tim=2787353623248
WAIT #101737960: nam='direct path read' ela= 229 file number=1 first dba=98432 block cnt=128 obj#=83398 tim=2787353624329
WAIT #101737960: nam='direct path read' ela= 10556 file number=1 first dba=98560 block cnt=128 obj#=83398 tim=2787353635721
WAIT #101737960: nam='direct path read' ela= 17376 file number=1 first dba=98688 block cnt=128 obj#=83398 tim=2787353653941
WAIT #101737960: nam='direct path read' ela= 615 file number=1 first dba=98944 block cnt=128 obj#=83398 tim=2787353656291
WAIT #101737960: nam='direct path read' ela= 43335 file number=1 first dba=99072 block cnt=128 obj#=83398 tim=2787353700478
WAIT #101737960: nam='direct path read' ela= 1379 file number=1 first dba=99200 block cnt=128 obj#=83398 tim=2787353702886
WAIT #101737960: nam='direct path read' ela= 795 file number=1 first dba=99328 block cnt=128 obj#=83398 tim=2787353704582
now read from 16KB Block - The block count decreases resulting in slower response time
PARSING IN CURSOR #101737960 len=39 dep=0 uid=0 oct=3 lid=0 tim=2787379405714 hv=3142550934 ad='7ff203f7978' sqlid='0xufk4uxnz1cq'
select count(*) from big_table_bigblock
END OF STMT
PARSE #101737960:c=0,e=5371,p=0,cr=1081,cu=0,mis=1,r=0,dep=0,og=1,plh=3466094791,tim=2787379405713
EXEC #101737960:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3466094791,tim=2787379405788
WAIT #101737960: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=83398 tim=2787379405819
WAIT #101737960: nam='direct path read' ela= 169123 file number=6 first dba=67 block cnt=13 obj#=83404 tim=2787379575386
WAIT #101737960: nam='direct path read' ela= 11124 file number=6 first dba=81 block cnt=15 obj#=83404 tim=2787379586898
WAIT #101737960: nam='direct path read' ela= 2629 file number=6 first dba=97 block cnt=15 obj#=83404 tim=2787379590024
WAIT #101737960: nam='direct path read' ela= 4623 file number=6 first dba=113 block cnt=63 obj#=83404 tim=2787379595208
*** 2015-01-13 14:18:40.366
WAIT #101737960: nam='direct path read' ela= 6177 file number=6 first dba=177 block cnt=63 obj#=83404 tim=2787379602175
WAIT #101737960: nam='direct path read' ela= 4827 file number=6 first dba=241 block cnt=63 obj#=83404 tim=2787379607863
WAIT #101737960: nam='direct path read' ela= 23539 file number=6 first dba=305 block cnt=63 obj#=83404 tim=2787379632185
WAIT #101737960: nam='direct path read' ela= 2795 file number=6 first dba=369 block cnt=63 obj#=83404 tim=2787379636121
WAIT #101737960: nam='direct path read' ela= 5415 file number=6 first dba=433 block cnt=31 obj#=83404 tim=2787379642265
WAIT #101737960: nam='direct path read' ela= 2689 file number=6 first dba=464 block cnt=32 obj#=83404 tim=2787379645402
WAIT #101737960: nam='direct path read' ela= 11709 file number=6 first dba=497 block cnt=63 obj#=83404 tim=2787379657645
WAIT #101737960: nam='direct path read' ela= 112017 file number=6 first dba=625 block cnt=63 obj#=83404 tim=2787379771231
WAIT #101737960: nam='direct path read' ela= 9686 file number=6 first dba=753 block cnt=63 obj#=83404 tim=2787379783335
WAIT #101737960: nam='direct path read' ela= 1070 file number=6 first dba=864 block cnt=16 obj#=83404 tim=2787379785805
I Just tried it on my test Windows 2008/ 11.2.0.3
By increasing the DB_FILE_MULTIBLOCK_READ_COUNT- It significantly improves the response time for DIRECT PATH READ
SQL> show parameter multiblock
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_file_multiblock_read_count integer 128
SQL> ALTER SESSION SET sql_trace=TRUE;
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
SQL> select count(*) from big_table;
COUNT(*)
----------
37118976
SQL> set timing on
SQL> /
COUNT(*)
----------
37118976
Elapsed: 00:00:24.91
and the trace file is the below
PARSING IN CURSOR #335902688 len=31 dep=0 uid=0 oct=3 lid=0 tim=2775080312320 hv=1330050261 ad='7ff151fb990' sqlid='bd5mjp97ndx6p'
select count(*) from big_table
END OF STMT
PARSE #335902688:c=15600,e=57945,p=6,cr=94,cu=0,mis=1,r=0,dep=0,og=1,plh=599409829,tim=2775080312318
EXEC #335902688:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2775080312473
WAIT #335902688: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=9 tim=2775080312514
WAIT #335902688: nam='direct path read' ela= 16918 file number=1 first dba=90329 block cnt=39 obj#=81856 tim=2775080330042
WAIT #335902688: nam='direct path read' ela= 1302 file number=1 first dba=96512 block cnt=88 obj#=81856 tim=2775080331939
WAIT #335902688: nam='direct path read' ela= 48985 file number=1 first dba=96640 block cnt=128 obj#=81856 tim=2775080381642
WAIT #335902688: nam='direct path read' ela= 2208 file number=1 first dba=96768 block cnt=128 obj#=81856 tim=2775080384758
WAIT #335902688: nam='direct path read' ela= 641 file number=1 first dba=97024 block cnt=128 obj#=81856 tim=2775080387051
Now increasing the multiblock read count
SQL> alter session set db_file_multiblock_read_count=1024;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from big_table;
COUNT(*)
----------
37118976
Elapsed: 00:00:09.31
and the trace is below for this
ARSING IN CURSOR #335902688 len=52 dep=0 uid=0 oct=42 lid=0 tim=2775211375349 hv=302278082 ad='0' sqlid='7d0uktn908tf2'
alter session set db_file_multiblock_read_count=1024
END OF STMT
PARSE #335902688:c=0,e=205,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=2775211375348
EXEC #335902688:c=0,e=114,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=2775211375513
WAIT #335902688: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775211375544
*** 2015-01-13 10:56:04.533
WAIT #335902688: nam='SQL*Net message from client' ela= 12462509 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775223838075
CLOSE #335902688:c=0,e=16,dep=0,type=1,tim=2775223838194
=====================
PARSING IN CURSOR #348591800 len=399 dep=1 uid=0 oct=3 lid=0 tim=2775223840266 hv=2987523179 ad='7ff1528ad50' sqlid='8mu3z0kt13z3b'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("BIG_TABLE") FULL("BIG_TABLE") NO_PARALLEL_INDEX("BIG_TABLE") */ 1 AS C1, 1 AS C2 FROM "SYS"."BIG_TABLE" SAMPLE BLOCK (0.011589 , 1) SEED (1) "BIG_TABLE") SAMPLESUB
END OF STMT
PARSE #348591800:c=0,e=1202,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=4255388684,tim=2775223840264
EXEC #348591800:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=4255388684,tim=2775223840402
FETCH #348591800:c=15600,e=9533,p=0,cr=93,cu=0,mis=0,r=1,dep=1,og=1,plh=4255388684,tim=2775223849959
STAT #348591800 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=93 pr=0 pw=0 time=9536 us)'
STAT #348591800 id=2 cnt=4454 pid=1 pos=1 obj=81856 op='TABLE ACCESS SAMPLE BIG_TABLE (cr=93 pr=0 pw=0 time=5521 us cost=2 size=12 card=1)'
CLOSE #348591800:c=0,e=6,dep=1,type=0,tim=2775223850075
=====================
PARSING IN CURSOR #348592672 len=31 dep=0 uid=0 oct=3 lid=0 tim=2775223850333 hv=2920928257 ad='7ff1f316670' sqlid='a9c9vrkr1mp01'
select count(*) from big_table
END OF STMT
PARSE #348592672:c=15600,e=12081,p=0,cr=94,cu=0,mis=1,r=0,dep=0,og=1,plh=599409829,tim=2775223850331
EXEC #348592672:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2775223850406
WAIT #348592672: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775223850437
WAIT #348592672: nam='direct path read' ela= 21781 file number=1 first dba=90329 block cnt=39 obj#=81856 tim=2775223872881
WAIT #348592672: nam='direct path read' ela= 161673 file number=1 first dba=96640 block cnt=640 obj#=81856 tim=2775224039504
WAIT #348592672: nam='direct path read' ela= 18055 file number=1 first dba=98304 block cnt=1024 obj#=81856 tim=2775224075163
WAIT #348592672: nam='direct path read' ela= 35412 file number=1 first dba=99328 block cnt=1024 obj#=81856 tim=2775224117398
WAIT #348592672: nam='direct path read' ela= 63280 file number=1 first dba=100352 block cnt=640 obj#=81856 tim=2775224187153
*** 2015-01-13 10:56:04.907
WAIT #348592672: nam='direct path read' ela= 18056 file number=1 first dba=100992 block cnt=384 obj#=81856 tim=2775224209450
WAIT #348592672: nam='direct path read' ela= 160670 file number=1 first dba=101376 block cnt=1024 obj#=81856 tim=2775224373623
WAIT #348592672: nam='direct path read' ela= 16050 file number=1 first dba=103424 block cnt=1024 obj#=81856 tim=2775224407786
WAIT #348592672: nam='direct path read' ela= 29265 file number=1 first dba=104448 block cnt=1024 obj#=81856 tim=2775224443701
WAIT #348592672: nam='direct path read' ela= 13106 file number=1 first dba=105472 block cnt=1024 obj#=81856 tim=2775224463860
WAIT #348592672: nam='direct path read' ela= 12313 file number=1 first dba=106496 block cnt=1024 obj#=81856 tim=2775224483484
WAIT #348592672: nam='direct path read' ela= 5320 file number=1 first dba=107520 block cnt=1024 obj#=81856 tim=2775224496871
WAIT #348592672: nam='direct path read' ela= 12907 file number=1 first dba=108544 block cnt=1024 obj#=81856 tim=2775224517080
WAIT #348592672: nam='direct path read' ela= 20352 file number=1 first dba=109568 block cnt=1024 obj#=81856 tim=2775224544060
WAIT #348592672: nam='direct path read' ela= 23962 file number=1 first dba=111616 block cnt=1024 obj#=81856 tim=2775224582782
WAIT #348592672: nam='direct path read' ela= 11246 file number=1 first dba=113664 block cnt=1024 obj#=81856 tim=2775224612256
WAIT #348592672: nam='direct path read' ela= 11687 file number=1 first dba=114688 block cnt=1024 obj#=81856 tim=2775224630478
WAIT #348592672: nam='direct path read' ela= 19856 file number=1 first dba=115712 block cnt=1024 obj#=81856 tim=2775224656787
WAIT #348592672: nam='direct path read' ela= 19851 file number=1 first dba=116736 block cnt=1024 obj#=81856 tim=2775224685003
WAIT #348592672: nam='direct path read' ela= 18767 file number=1 first dba=118784 block cnt=1024 obj#=81856 tim=2775224718882
WAIT #348592672: nam='direct path read' ela= 15290 file number=1 first dba=120832 block cnt=1024 obj#=81856 tim=2775224752371
WAIT #348592672: nam='direct path read' ela= 26578 file number=1 first dba=122880 block cnt=1024 obj#=81856 tim=2775224794862
WAIT #348592672: nam='direct path read' ela= 17840 file number=1 first dba=124928 block cnt=1024 obj#=81856 tim=2775224828274
WAIT #348592672: nam='direct path read' ela= 10446 file number=1 first dba=126208 block cnt=768 obj#=81856 tim=2775224847268
WAIT #348592672: nam='direct path read' ela= 14746 file number=1 first dba=126976 block cnt=1024 obj#=81856 tim=2775224867798
WAIT #348592672: nam='direct path read' ela= 13894 file number=1 first dba=129024 block cnt=1024 obj#=81856 tim=2775224896643
WAIT #348592672: nam='direct path read' ela= 4059 file number=1 first dba=130048 block cnt=1024 obj#=81856 tim=2775224908034
WAIT #348592672: nam='direct path read' ela= 8972 file number=1 first dba=131072 block cnt=1024 obj#=81856 tim=2775224924395
WAIT #348592672: nam='direct path read' ela= 5014 file number=1 first dba=132096 block cnt=1024 obj#=81856 tim=2775224936765
WAIT #348592672: nam='direct path read' ela= 9246 file number=1 first dba=133120 block cnt=1024 obj#=81856 tim=2775224953730
So repeats the same behaviour mentioned in https://fritshoogland.wordpress.com/tag/oracle-io-multiblock-read-mbrc-multiblock-read-count/
I just split the trace file for clarity- attached is the untouced onespool.GIFTrace.GIFTrace128block.GIF
But changing the value may drive towards lot of fullscans .
I just tried by creating a 16KB block tablespace and it doesnt sound good...
copied the same table to the 16KB block and it performs bad actually
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,
2 dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tabl
3 esPace_name=b.tablespace_name;
esPace_name=b.tablespace_name
*
ERROR at line 3:
ORA-00920: invalid relational operator
SQL> CREATE TABLESPACE big_block DATAFILE 'D:\oracledb\oradata\LMSDB\sb01.dbf' S
IZE 2048M
2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
3 BLOCKSIZE 16K;
Tablespace created.
Elapsed: 00:00:12.09
SQL> create table big_table_bigblock tablespace big_block as select * from big_t
able;
Table created.
SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tablesPace_name=b.tablespace_name;
BLOCK_SIZE TABLESPACE_NAME TABLE_NAME
---------- ------------------------------ ------------------------------
8192 SYSTEM BIG_TABLE
16384 BIG_BLOCK BIG_TABLE_BIGBLOCK
SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tablesPace_name=b.tablespace_name;
BLOCK_SIZE TABLESPACE_NAME TABLE_NAME
---------- ------------------------------ ------------------------------
8192 SYSTEM BIG_TABLE
16384 BIG_BLOCK BIG_TABLE_BIGBLOCK
SQL> show parameter multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
parallel_adaptive_multi_user boolean TRUE
SQL> select bytes/1024/1024/1025 as GB, segment_name from dba_segments where segment_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK');
GB
----------
SEGMENT_NAME
--------------------------------------------------------------------------------
1.22195122
BIG_TABLE_BIGBLOCK
1.24878049
BIG_TABLE
SQL> select count(*) from big_table;
COUNT(*)
----------
11000000
SQL> select count(*) from big_table_big_block;
select count(*) from big_table_big_block
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from big_table_bigblock;
COUNT(*)
----------
11000000
SQL> spoll off
SP2-0042: unknown command "spoll off" - rest of line ignored.
SQL> spool off
PARSING IN CURSOR #101737960 len=30 dep=0 uid=0 oct=3 lid=0 tim=2787353576538 hv=1326693165 ad='7ff25361b18' sqlid='6fxg0qj7j7ftd'
select count(*) from big_table
END OF STMT
PARSE #101737960:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2787353576537
EXEC #101737960:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2787353576657
WAIT #101737960: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=589 tim=2787353576691
WAIT #101737960: nam='direct path read' ela= 24563 file number=1 first dba=90585 block cnt=39 obj#=83398 tim=2787353602067
WAIT #101737960: nam='direct path read' ela= 11121 file number=1 first dba=98176 block cnt=128 obj#=83398 tim=2787353614787
WAIT #101737960: nam='direct path read' ela= 7432 file number=1 first dba=98304 block cnt=128 obj#=83398 tim=2787353623248
WAIT #101737960: nam='direct path read' ela= 229 file number=1 first dba=98432 block cnt=128 obj#=83398 tim=2787353624329
WAIT #101737960: nam='direct path read' ela= 10556 file number=1 first dba=98560 block cnt=128 obj#=83398 tim=2787353635721
WAIT #101737960: nam='direct path read' ela= 17376 file number=1 first dba=98688 block cnt=128 obj#=83398 tim=2787353653941
WAIT #101737960: nam='direct path read' ela= 615 file number=1 first dba=98944 block cnt=128 obj#=83398 tim=2787353656291
WAIT #101737960: nam='direct path read' ela= 43335 file number=1 first dba=99072 block cnt=128 obj#=83398 tim=2787353700478
WAIT #101737960: nam='direct path read' ela= 1379 file number=1 first dba=99200 block cnt=128 obj#=83398 tim=2787353702886
WAIT #101737960: nam='direct path read' ela= 795 file number=1 first dba=99328 block cnt=128 obj#=83398 tim=2787353704582
now read from 16KB Block - The block count decreases resulting in slower response time
PARSING IN CURSOR #101737960 len=39 dep=0 uid=0 oct=3 lid=0 tim=2787379405714 hv=3142550934 ad='7ff203f7978' sqlid='0xufk4uxnz1cq'
select count(*) from big_table_bigblock
END OF STMT
PARSE #101737960:c=0,e=5371,p=0,cr=1081,cu=0,mis=1,r=0,dep=0,og=1,plh=3466094791,tim=2787379405713
EXEC #101737960:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3466094791,tim=2787379405788
WAIT #101737960: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=83398 tim=2787379405819
WAIT #101737960: nam='direct path read' ela= 169123 file number=6 first dba=67 block cnt=13 obj#=83404 tim=2787379575386
WAIT #101737960: nam='direct path read' ela= 11124 file number=6 first dba=81 block cnt=15 obj#=83404 tim=2787379586898
WAIT #101737960: nam='direct path read' ela= 2629 file number=6 first dba=97 block cnt=15 obj#=83404 tim=2787379590024
WAIT #101737960: nam='direct path read' ela= 4623 file number=6 first dba=113 block cnt=63 obj#=83404 tim=2787379595208
*** 2015-01-13 14:18:40.366
WAIT #101737960: nam='direct path read' ela= 6177 file number=6 first dba=177 block cnt=63 obj#=83404 tim=2787379602175
WAIT #101737960: nam='direct path read' ela= 4827 file number=6 first dba=241 block cnt=63 obj#=83404 tim=2787379607863
WAIT #101737960: nam='direct path read' ela= 23539 file number=6 first dba=305 block cnt=63 obj#=83404 tim=2787379632185
WAIT #101737960: nam='direct path read' ela= 2795 file number=6 first dba=369 block cnt=63 obj#=83404 tim=2787379636121
WAIT #101737960: nam='direct path read' ela= 5415 file number=6 first dba=433 block cnt=31 obj#=83404 tim=2787379642265
WAIT #101737960: nam='direct path read' ela= 2689 file number=6 first dba=464 block cnt=32 obj#=83404 tim=2787379645402
WAIT #101737960: nam='direct path read' ela= 11709 file number=6 first dba=497 block cnt=63 obj#=83404 tim=2787379657645
WAIT #101737960: nam='direct path read' ela= 112017 file number=6 first dba=625 block cnt=63 obj#=83404 tim=2787379771231
WAIT #101737960: nam='direct path read' ela= 9686 file number=6 first dba=753 block cnt=63 obj#=83404 tim=2787379783335
WAIT #101737960: nam='direct path read' ela= 1070 file number=6 first dba=864 block cnt=16 obj#=83404 tim=2787379785805
