For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Hello, Is there a way how to Unlock ODI Locked objects with Groovy SDK? Or is there another way how to unlock instead of going through ODI --> Locked Objects? ODI Version: 12.2.1.3 Thanks
post the output /u01/app/oracle/diag/rdbms/dbvs103/DBVS103/trace/DBVS103_ora_31822.trc
Is that the same as what you have posted above?
I can't help noticing that the number of large scattered reads (the 60 and 66 sizes) is 14 - which may be a coincidence, so I'd run a couple more tests with different number of rows to see if Oracle appears to be missing the smaller reads in this count.
Alternatively, for a hand-waving conjecture: maybe the description in the manual is wrong; maybe Oracle is aware of the maximum size of I/O allowed by the file system and "knows" that a 60 block read will break into two pieces and the statistic is actually counting the number of multiblock reads that will be fragmented at the next level down.
Regards
Jonathan Lewis
P.S. Which version of Oracle ?
GReat...I was just looking at 30 count it had 30 scattered reads, I even counted the no of block read via scattered its 1000.
Just reproduced this in 11.2.0.4 and 12.1.0.2 -- allowing for different effective settings of the db_file_mulitblock_read_count (yours appears to be set, or to have auto-set, to 66, mine was 128).
It looks as if the statistic isn't counting the reads of 7 and 8 blocks used for the smallest extents.
Might be worth messing around with uniform sizing at "N" blocks per extent to see if it's an anomaly of [edited] auto-allocate or whether there is a size below which the reads are not counted.
Hi Jonathan,
Thanks for your eagle eye on the trace
Yes, it seems that multiblock reads that are downsized because of extent boundary are not counted in this statistic.
I'll do further testing.
My db_file_multiblock_read_count was not set but 66 is the limit from (buffer_cache size / sessions) as it comes from a test database with very small SGA.
Regards,
Franck.
Spotted the same in the trace regarding the larger counts.
Just repeated test on 12.1.0.2 VM and see similar behaviour:
physical read total multi block requests 7 ... db file scattered read 30
physical read total multi block requests 7
...
db file scattered read 30
And in trace these smaller reads:
1x db file scattered read blocks=5 7x db file scattered read blocks=6 7x db file scattered read blocks =7 8x db file scattered read blocks =8
1x db file scattered read blocks=5
7x db file scattered read blocks=6
7x db file scattered read blocks =7
8x db file scattered read blocks =8
And these larger reads:
7 x db file scattered read block=120
And db_file_multiblock_read_count is 120.
Couldn't leave it alone.
Ran a few more tests with different extent sizes, with and without ASSM.
It looks like my platform doesn't include db file scattered reads of less than 128KB in the physical count. (or 16 blocks, given that I was using an 8KB block size).
I tested the second case I know where multiblock count is lower than specified.
I put 10 blocks in buffer cache with:
column rowid_list new_value rowid_list
select listagg(''''||rowid||'''',',') within group(order by rowid) rowid_list from DEMO where n like '9_0%';
alter system flush buffer_cache;
select count(*) from DEMO where rowid in ( &rowid_list );
and that brings the stats to 39 'db file scattered read' and 13 'multi block requests' which I think are those I've put in bold here:
1 WAIT #140706544598320: nam='db file sequential read' ela= 188 file#=6 block#=6514 blocks=1 obj#=95398 tim=57829212387
2 WAIT #140706544598320: nam='db file scattered read' ela= 458 file#=6 block#=6515 blocks=5 obj#=95398 tim=57829212993
3 WAIT #140706544598320: nam='db file scattered read' ela= 436 file#=6 block#=8280 blocks=8 obj#=95398 tim=57829213623
4 WAIT #140706544598320: nam='db file scattered read' ela= 398 file#=6 block#=8289 blocks=7 obj#=95398 tim=57829214130
5 WAIT #140706544598320: nam='db file scattered read' ela= 405 file#=6 block#=8296 blocks=8 obj#=95398 tim=57829214667
6 WAIT #140706544598320: nam='db file scattered read' ela= 398 file#=6 block#=8305 blocks=7 obj#=95398 tim=57829215209
7 WAIT #140706544598320: nam='db file scattered read' ela= 408 file#=6 block#=8312 blocks=8 obj#=95398 tim=57829215728
8 WAIT #140706544598320: nam='db file scattered read' ela= 399 file#=6 block#=11393 blocks=7 obj#=95398 tim=57829216240
9 WAIT #140706544598320: nam='db file scattered read' ela= 406 file#=6 block#=11400 blocks=8 obj#=95398 tim=57829216765
10 WAIT #140706544598320: nam='db file scattered read' ela= 398 file#=6 block#=11409 blocks=7 obj#=95398 tim=57829217280
11 WAIT #140706544598320: nam='db file scattered read' ela= 408 file#=6 block#=11416 blocks=8 obj#=95398 tim=57829217801
12 WAIT #140706544598320: nam='db file scattered read' ela= 397 file#=6 block#=11425 blocks=7 obj#=95398 tim=57829218319
13 WAIT #140706544598320: nam='db file scattered read' ela= 416 file#=6 block#=11432 blocks=8 obj#=95398 tim=57829218868
14 WAIT #140706544598320: nam='db file scattered read' ela= 388 file#=6 block#=11441 blocks=7 obj#=95398 tim=57829219389
15 WAIT #140706544598320: nam='db file scattered read' ela= 419 file#=6 block#=11448 blocks=8 obj#=95398 tim=57829219917
16 WAIT #140706544598320: nam='db file scattered read' ela= 301 file#=6 block#=11457 blocks=7 obj#=95398 tim=57829220346
17 WAIT #140706544598320: nam='db file scattered read' ela= 416 file#=6 block#=11464 blocks=8 obj#=95398 tim=57829220879
18 WAIT #140706544598320: nam='db file scattered read' ela= 1925 file#=6 block#=7554 blocks=66 obj#=95398 tim=57829223157
19 WAIT #140706544598320: nam='db file scattered read' ela= 1175 file#=6 block#=7620 blocks=60 obj#=95398 tim=57829224901
20 WAIT #140706544598320: nam='db file scattered read' ela= 918 file#=6 block#=8450 blocks=66 obj#=95398 tim=57829226328
21 WAIT #140706544598320: nam='db file scattered read' ela= 1127 file#=6 block#=8516 blocks=60 obj#=95398 tim=57829227995
22 WAIT #140706544598320: nam='db file scattered read' ela= 1466 file#=6 block#=8578 blocks=66 obj#=95398 tim=57829229919
23 WAIT #140706544598320: nam='db file scattered read' ela= 1272 file#=6 block#=8644 blocks=60 obj#=95398 tim=57829232069
24 WAIT #140706544598320: nam='db file scattered read' ela= 1932 file#=6 block#=9730 blocks=66 obj#=95398 tim=57829234780
25 WAIT #140706544598320: nam='db file scattered read' ela= 1287 file#=6 block#=9796 blocks=60 obj#=95398 tim=57829236860
26 WAIT #140706544598320: nam='db file scattered read' ela= 1564 file#=6 block#=9858 blocks=66 obj#=95398 tim=57829239045
27 WAIT #140706544598320: nam='db file scattered read' ela= 322 file#=6 block#=9924 blocks=60 obj#=95398 tim=57829239892
28 WAIT #140706544598320: nam='db file scattered read' ela= 359 file#=6 block#=9986 blocks=66 obj#=95398 tim=57829240705
29 WAIT #140706544598320: nam='db file scattered read' ela= 739 file#=6 block#=10052 blocks=60 obj#=95398 tim=57829241954
30 WAIT #140706544598320: nam='db file scattered read' ela= 180 file#=6 block#=11522 blocks=25 obj#=95398 tim=57829242308
31 WAIT #140706544598320: nam='db file scattered read' ela= 120 file#=6 block#=11548 blocks=9 obj#=95398 tim=57829242603
32 WAIT #140706544598320: nam='db file scattered read' ela= 119 file#=6 block#=11558 blocks=9 obj#=95398 tim=57829242831
33 WAIT #140706544598320: nam='db file scattered read' ela= 146 file#=6 block#=11568 blocks=9 obj#=95398 tim=57829243088
34 WAIT #140706544598320: nam='db file scattered read' ela= 132 file#=6 block#=11578 blocks=9 obj#=95398 tim=57829243319
35 WAIT #140706544598320: nam='db file scattered read' ela= 119 file#=6 block#=11588 blocks=9 obj#=95398 tim=57829243551
36 WAIT #140706544598320: nam='db file scattered read' ela= 118 file#=6 block#=11598 blocks=9 obj#=95398 tim=57829243773
37 WAIT #140706544598320: nam='db file scattered read' ela= 126 file#=6 block#=11608 blocks=9 obj#=95398 tim=57829244007
38 WAIT #140706544598320: nam='db file scattered read' ela= 128 file#=6 block#=11618 blocks=9 obj#=95398 tim=57829244274
39 WAIT #140706544598320: nam='db file scattered read' ela= 139 file#=6 block#=11628 blocks=9 obj#=95398 tim=57829244513
40 WAIT #140706544598320: nam='db file scattered read' ela= 139 file#=6 block#=11638 blocks=10 obj#=95398 tim=57829244753
