Strange cell offload behavior
Date: Sun, 23 May 2021 22:12:35 +0530
Message-ID: <CAKna9Vbv9qedEtEdHmsBOXX42YkQPk1V-a_hquBRXNFWyL4cWg_at_mail.gmail.com>
Hello Listers,
Its version 19C of oracle and optimizer_features_enable 19.1.0.
While working on a separate issue, we came across a situation which is a bit odd. A simple SELECT query fetching ~13 columns from a table - TAB1 showing odd behaviour. Sometimes within seconds and sometimes more than a minute. When , It was running for more than a minute , it happened to be clear that it's not doing cell offloading from its sql monitor. And then by just removing one column from its SELECT list , the smartscan started happening and the query finished in a few seconds.
Then I started running the same query for all the ~13 columns in the SELECT list but this time by setting session level "serial_direct_read"= always. And here we see the smart scan again started happening and the query finished in quick time. Is this behaviour because of any bug ? Because I have not seen any such criteria in which the smartscan is restricted by the number of columns in the SELECT list.
Attached is the sample table script with the test case which produces the above scenario. Table TAB1 is a non partition table with size ~7GB and there are ~64million rows in it.
Note- Its a third party database and we see few of the optimizer parameters set to non default as its clear from the outline section. But at least none of these should be impacting the choice of smartscan.
SELECT A_ID, A_ACTN, RNM, FNM, OVL, NWVL, COL1, COL2, COL3, COL4, COL5, COL6, COL7 FROM USER2.TAB1 WHERE OVL <> NWVL AND ( (RNM = 'XXXX' AND COL1 = '1') OR (RNM = 'YYYY' AND COL1 = '1'))
-- http://www.freelists.org/webpage/oracle-lReceived on Sun May 23 2021 - 18:42:35 CEST
- text/plain attachment: Offload_test.txt