Re: Question on cell offloading

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> 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> 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