Re: Question on cell offloading

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