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
