Thank you Dominic.
It seems the blog talks of bloom pruning , does that also mean the cell
offloading restriction on DML will also be removed along with bloom pruning
with INSERT ALL ? I am not seeing a clear indication in Jonathan's blog
though if INSERT ALL will definitely work for 11.2.0.4 , but will try to
test it on an 11.2 instance. And again even this(INSERT ALL) will be a code
change too and also in that case we can safely convert it to a cursor+bulk
collect load , modification of the existing insert query which would
guaranteedly obey both cell offloading and bloom pruning feature. However,
we were looking if we can push some hints from outside and make the
existing INSERT follow cell offloading + bloom filter pruning?
As Lok P pointed earlier, I see Jonathan also mentioned the APPEND hint as
one of the workarounds in that blog, but it seems that works for higher
versions but not for 11.2.0.4 version.
On Thu, Jul 22, 2021 at 5:46 PM Dominic Brooks <dombrooks@xxxxxxxxxxx>
wrote:
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> 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> 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
