Question on cell offloading
Date: Thu, 22 Jul 2021 01:44:37 +0530
Message-ID: <CAEjw_fiw8fqNcT8w0J_-oBV6ihVRsFHXt4deo=sGa2Uqfiak0g_at_mail.gmail.com>
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-lReceived on Wed Jul 21 2021 - 22:14:37 CEST