Re: Strange cell offload behavior

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Mon, 24 May 2021 02:08:53 +0300
Message-ID: <CAOVevU7qgHu9szpS70Ka-MX4D-DjTFcKv7Vv8ajEetbxUCdLHg_at_mail.gmail.com>

Hi Lok,

https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-1
https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-2
https://blogs.oracle.com/smartscan-deep-dive/correct-syntax-for-the-table_stats-hint

Have you read this series of articles by Roger MacNicol? You just need to analyze or provide the following trace events for your queries:
 https://github.com/xtender/xt_scripts/blob/master/trace_nsmtio.sql -- Buffer cache decision making:
alter session set events '10358 trace name context forever, level 2'; -- Direct I/O decision making:
alter session set events 'trace [NSMTIO] disk highest';

On Sun, May 23, 2021 at 7:43 PM Lok P <loknath.73_at_gmail.com> wrote:

> 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'))
>
> Regards
> Lok
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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

Received on Mon May 24 2021 - 01:08:53 CEST