hello
Oracle Version:-
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
I am creating an index on partition table like below on my 11g DB.
table size is 70G.
parallel_max_servers =64
CREATE INDEX CRCA.I_CRCA_IX2 ON CRCA.CR_CALCULATION_AGENTS (AGENTS, CACHED_STATE_NAME)
INITRANS 32 TABLESPACE DATA_IDX parallel 16 ONLINE ;
but when ever I check the event and p3 columns for all my parallel sessions its shows dbf seq read and 1,
my question is when I set db_file_multiblock_read_count=64 always it should pick the 64 block and event should scattered read.
very few times its goes for scattered read.
what can be the problem ?
this kind of very wired behavior from Oracle.
@osstat.sql
STAT_NAME VALUE OSSTAT_ID COMMENTS CUM
------------------------------ ---------------- ---------- ---------------------------------------------------------------- ---
NUM_CPUS 8 0 Number of active CPUs NO
LOAD 20 15 Number of processes running or waiting on the run queue NO
NUM_CPU_CORES 8 16 Number of CPU cores NO
NUM_CPU_SOCKETS 2 17 Number of physical CPU sockets NO
PHYSICAL_MEMORY_BYTES 16,807,608,320 1008 Physical memory size in bytes NO
look at all parallel sessions top one is 64 then 2 with scatterd read after that all seq. read with 1 block.
Sid Logi Oracle
Cpu% Reads Sid,Serial<Blk User OS User Svr - Pgm Wait Event Sta:SIW P1 P2 P3
---- ------ --------------- -------- -------- ---------- ------------------ ------- ----- ------- --- -
3.5 22534 822, 26 SYS oracle 17393-P021 dbf scat read Wtg: 0 53 547648 64
3.7 27472 1025, 112 SYS oracle 17413-P031 dbf scat read Wtg: 0 83 332252 2
3.5 17569 972, 278 SYS oracle 17395-P022 dbf seq read Wtg: 0 16 956848 1
4.4 28465 923, 57 SYS oracle 17403-P026 dbf seq read Wtg: 0 18 670272 1
2.1 9487 739, 157 SYS oracle 17407-P028 dbf seq read Wtg: 0 27 744150 1
2.2 9030 1206, 85 SYS oracle 17389-P019 dbf seq read Wtg: 0 44 345188 1
3.1 18637 1172, 96 SYS oracle 17409-P029 dbf seq read Wtg: 0 52 444235 1
3.3 18669 1035, 143 SYS oracle 17385-P017 dbf seq read Wtg: 0 52 818677 1
2.6 11422 1237, 95 SYS oracle 17391-P020 dbf seq read Wtg: 0 56 647459 1
2.3 13579 821, 18 SYS oracle 17397-P023 dbf seq read Wtg: 0 65 723467 1
3.4 20640 749, 270 SYS oracle 17401-P025 dbf seq read Wtg: 0 68 724106 1
2.9 16963 1076, 115 SYS oracle 17411-P030 dbf seq read Wtg: 0 81 454682 1
3.2 20068 764, 84 SYS oracle 17399-P024 dbf seq read Wtg: 0 84 851352 1
2.7 17100 1151, 209 SYS oracle 17383-P016 dbf seq read Wtg: 0 95 201814 1
2.2 9414 772, 51 SYS oracle 17405-P027 dbf seq read Wtg: 0 109 835226 1
3.4 23696 813, 135 SYS oracle 17387-P018 dbf seq read Wtg: 0 111 672930 1