Hi Jonathan,
Thank you for correcting me, you're absolutely right. I should have said
"haven't noticed".
Regarding what you've mentioned on hitting the double decompression, I
recently noticed that some queries were consuming much more CPU than usual
and thanks to the snapper tool I understood it was because 40% of the CUs
were sent to the DB node as compressed. To deduct this, I compared the
Statistics "Cells CUs processed for uncompressed" and "Cells CUs sent
uncompressed". I can even see the "Cells CUs sent head piece" statistics
which is incremented. I didn't find the reason for this phenomenon. I
thought it was because the cells were too busy, but now I have to check
whether the block size of 32kb might not be the root cause
Le mer. 17 juin 2020 à 11:30, Jonathan Lewis <jlewisoracle@xxxxxxxxx> a
écrit :
I prefer to use the expression: "haven't noticed any issues", rather than
"haven't had any issues". I've seen far too many systems that are allowed
to perform at sub-optimal speed because their owners didn't realise they
could do significantly better ;)
On a data warehouse with direct path loads and little in the way of
updates you're unlikely to run into the oddity of UNDO using 32KB blocks
that I wrote about a few years ago. (There is (was?) a hard limit of 255
undo records per undo block.) There's also a threat of undo space wastage
due to the implementation of the undo free pool in a highly concurrent
system that won't be relevant to you.
One potential threat that would be easy to miss is mentioned in Roger's
article - with a 32KB block you could get compression that was "too good" -
he mentioned a hard limit that I hadn't known about. There's also the
possibility that your queries could end up hitting the "double
decompression" problem more frequently because the disk server decompresses
and discovers the result set is to large to return so sends back the
compression unit to be decompressed by the database server.
Regards
Jonathan Lewis
.
On Sat, Jun 13, 2020 at 7:25 AM Ahmed Aangour <ahmed.aangour@xxxxxxxxx>
wrote:
Hi,
For information, my current client has been using a 32k block size in an
Exadata X6 data warehouse database and has encountered none issue for 10
years regarding that setting. Of course, we never update rows, and delete
statements are very rare. We mainly use direct path inserts and truncate
partitions.
Regards,
Ahmed
