I seem to remember a workaround is to use INSERT ALL on 11.2.0.4.
https://jonathanlewis.wordpress.com/2016/07/08/dml-and-bloom/
Sent from my iPhone
On 22 Jul 2021, at 13:03, Pap <oracle.developer35@xxxxxxxxx> wrote:
I don't have a 11.2 test instance right now with me to test, but what i did is
, on a 19C version database , i ran a INSERT AS SELECT query and saw the bloom
filter pruning visible but when i ran it with optimizer_feature_enable hint as
11.2.0.4 , and i saw the bloom filter pruning disappeared. But then i added
APPEND+ parallel hint along with 11.2.0.4 optimizer_feature_enable , but that
still not showing bloom pruning back in the plan. So hopefully the
parallel+append hint won't work.
On Thu, Jul 22, 2021 at 2:23 PM Lok P
<loknath.73@xxxxxxxxx<mailto:loknath.73@xxxxxxxxx>> wrote:
Have you tried forcing it , Parallel + APPEND hint, which would probably
force a direct read and thus will help it to go for cell smart scan+ bloom
filter pruning?
On Thu, Jul 22, 2021 at 1:45 AM Pap
<oracle.developer35@xxxxxxxxx<mailto:oracle.developer35@xxxxxxxxx>> wrote:
Hello Listers, There exists a bug in the lower version( say in current 11.2.0.4
)which restricts DML queries to get benefit of cell smartscan and bloom filter
pruning in case of full segment scans. And for that in this version, many times
we end up converting those INSERT INTO.. SELECT queries into cursor bulk
collect INSERT , so that those restrictions can be avoided as the query moves
to a SELECT part of the cursor. And in a few cases we end up setting the
_serial_direct_read = always at session level , so as to force the smartscan
for the DML query.
We have got a situation in which the query(INSERT INTO.. SELECT.... ) was
working fine in the 19C database and it was doing both cell offloading + bloom
filter pruning. Now as part of certain functionality migration as per business
requirement , the same query copied over to a 11.2.0.4 database , but here the
query is running long as it's neither doing cell offloading nor the bloom
filter pruning. So a code change will be needed for both the workarounds like
converting it to a cursor+ bulk collect or setting session level parameter
_serial_direct_read to always, both of these work arounds will need code
change. But we don't have the flexibility here to do that.
So my question is , Is there any other option in which we can achieve these two
features to work in this 11.2.0.4 version through some hints which we can push
through sql profile and make this DML work as it was?
Below is a sample bug for cell offloading and similar one also exist for bloom
filter pruning , i am yet to find that
Bug 13250070 - Enh: Serial direct reads not working in DML (Doc ID 13250070.8)
Regards
Pap
