So Jonathan, OP has supplied plans which shows both cases, I. E, with large
estimation the bigger bloom filter is consuming additional CPU cycle and
with smaller estimation the hash join is consuming higher CPU cycle. But
both the cases the total query execution time is closely equal, also op
mentioned both the plans running for ~30minutes+ many times of the day
so...
when you said below I. E favoring large bloom filter option, so I am
wondering if it's because it might help in less temp spill? Or say, do you
mean its better option of hinting the inline view or tran_tab estimation
very high so that a bigger bloom filter will be applied and the lesser
amounts of rows will be passed to the hash join which may also benefit in
case of large data volume as temp spill will be minimal?
*Note that the Offload Returned Bytes was 300GB for the 49K estimate with
the small Bloom filter, and 500GB for the 2M estimate with the large Bloom
filter.*
*It looks like we need to "fake" the system so that the Bloom filter
(estimate) is large enough to eliminate a lot of data while being small
enough to be sent to the cell server so that the 14 concurrently active
cells can do the row elimination. Beyond that I don't think there's a way
to make the query go faster than the (roughly) 650 seconds you've seen so
far*.
On Tue, 31 Jan, 2023, 3:51 am Jonathan Lewis, <jlewisoracle@xxxxxxxxx>
wrote:
Comparing the 5 hash join plans you've posted:
(a) The sample where you compared including the Bloom filter against the
one with the 2M hint.
They are nearly identical, and critically the CPU operations 6 and 7 is
very high with the row count dropping from 3G to 99M. The CPU at operation
2 is then small. I suspect you may have left the cardinality 2M hint in
this example.
(b) The other three plans, where the TRAN_TAB estimate is 49,000
The CPU time in the scan of the encrypt_tab is around 150 seconds and the
CPU for the hash join goes high - 350 for the original and the hinted
49,000; 450 for the one with the Bloom filter off.
Roughly speaking, Oracle's implementation of Bloom filtering is the first
check in a hash join (i.e. are there possible matches for the latest probe
table row in the relevant build table hash bucket), so we can can use the
CPU for that step down at the scan of encrypt_tab or up at the hash join.
We cannot avoid it. What we hope to do with the Bloom filter is reduce the
volume of data that passes up a couple of lines in the row (i.e.through a
couple of call stacks). When we disable the Bloom filter that part of the
test now happens at the hash join, and all the data passed in from the
cells has to pass up the plan.
The size of the hash table (i.e. the number of hash buckets / bits in the
Bloom filter) is affected by the optimizer's estimate of the number of rows
in the build (first) table. This is why the estimate of 2M eliminated more
data with the Bloom filter then the estimate of 49,000; it increased the
size of the Bloom filter so reduced the "collisions" or "false positives".
But there's a down-side.
When the estimate was 2M I think (it's a guess) that the Bloom filter
became too large to be passed down to the cell server, and that's why the
actual rows for the encrypt_tab scan was 3G; but when the estimate was 49K
the actual rows for the scan was 2G because the Bloom filter (when it was
enabled) was passed to the cell server which managed to eliminate SOME
data, though not very much. And, of course, since the cells had eliminated
data the Bloom filter operation at the database server didn't eliminate any
more data, so passed 2G rows up to the hash join.
Note that the Offload Returned Bytes was 300GB for the 49K estimate with
the small Bloom filter, and 500GB for the 2M estimate with the large Bloom
filter.
It looks like we need to "fake" the system so that the Bloom filter
(estimate) is large enough to eliminate a lot of data while being small
enough to be sent to the cell server so that the 14 concurrently active
cells can do the row elimination. Beyond that I don't think there's a way
to make the query go faster than the (roughly) 650 seconds you've seen so
far.
=======================================
THe two suggestions I've made so far (use RAW not HEX, and hash
partition/subpartition for partition-wise joins) can make a big difference
though.
a) using RAW means each value is half the size, so the number of hash
buckets can increase significantly for the same size memory, and that MAY
help Oracle with elimination (though you still have to avoid the bitmap /
Bloom filter getting too big)
b) if you hash partition the encrypt_tab table N ways (N = a power of 2)
and hash subpartition the trans_tab table to match then a bitmap / Bloom
filter then Oracle can walk through each of the N pairs in turn and the
bitmap / Bloom filter on each step can be just as big as the largest viable
Bloom filter that you could create for the full data set but only have to
cope with 1/Nth of the data, which means fewer collisions, more
elimination, and much less data passing up the plan.
Side note: Unless things have changed in recent versions a 10104 trace
will tell you about what's going on with the hash join, including the
number of buckets in the hash table and the distribution of rows per bucket
- that might be quite interesting and corroborate some of my comments about
collisions/false positives etc.
Regards
Jonathan Lewis
On Mon, 30 Jan 2023 at 18:37, Pap <oracle.developer35@xxxxxxxxx> wrote:
Yes Mark, that is kind of what I got to know from the requirements so
far.
And as I understood from this discussion so far, the encrypted raw value
is 50% smaller in size and is the same irrespective of the client and is
not going to change with the new oracle version etc. So we are in
agreement with the dev team to change the column to RAW data type. However,
considering that will be a design change and will take some time, we were
trying to see if we can make the existing query run faster by any quick fix
or code change etc. And as I posted the two sql monitors above, it appears
to be the same query sometimes running for 30 minutes and
sometimes ~10minutes, so trying to understand the cause and if we can make
it consistent?
