Re: Question on cell offloading

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_at_gmail.com> 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_at_gmail.com<mailto:loknath.73_at_gmail.com>> 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_at_gmail.com<mailto:oracle.developer35_at_gmail.com>> 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

--
http://www.freelists.org/webpage/oracle-l