We have sga_target set as 6GB and no specific value for dba_cache_size has
been set.
Below two lines suggest that the object was > medium table threshold and <
very large table threshold. But the immediate next line states it's going
with buffer cache reads. But nothing mentioned about why it decided to do
so?
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from
caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:NoDirectRead:[CACHE_READ]: tsn: 53, objd: 1063361, objn:
1063361
Should i also trace the one with less number of columns which is going for
smartscan and compare? But still if this trace won't give details on how it
decided that. No meaning in tracing that. And also the odd part is
increasing the number of columns in the SELECT list should actually make it
more favorable for smartscan because of more block number but somehow it's
working the other way here.
On Tue, May 25, 2021 at 9:50 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Thank you so much Sayan.
I tried tracing ,and below is the key section of the trace (10358+NSMTIO)
for the original query(i.e. will all the columns). I was trying to
understand it , but found it a bit confusing. I do see it has taken the
final decision on doing cache scan but not cell offload but not able to
understand the cause. Can you guide me here on what exactly it's pointing
to? Why are we failing to get the benefit of SMART SCAN?
NSMTIO: fple: FALSE, sage: TRUE, isTableSpaceOnSage: 1, invalid table
sapce number: FALSE, prmsdec: TRUE, is kernel txn table space encrypted:
-1,is enc_ktid encrypted: -1, is sage enabled based on data -layer checks:
1, isQesSageEnabled: TRUE
*** 2021-05-25T07:15:15.503247-04:00
kcbism: islarge 1 next 0 nblks 914171 type 3, bpid 65535, kcbisdbfc 0
kcbnhl 8192 kcbstt 9836 keep_nb 0 kcbnbh 255727 kcbnwp 4
NSMTIO: fple: FALSE, sage: TRUE, isTableSpaceOnSage: 1, invalid table
sapce number: FALSE, prmsdec: TRUE, is kernel txn table space encrypted:
-1,is enc_ktid encrypted: -1, is sage enabled based on data -layer checks:
1, isQesSageEnabled: TRUE
NSMTIO: fple: FALSE, sage: TRUE, isTableSpaceOnSage: 1, invalid table
sapce number: FALSE, prmsdec: TRUE, is kernel txn table space encrypted:
-1,is enc_ktid encrypted: -1, is sage enabled based on data -layer checks:
1, isQesSageEnabled: TRUE
NSMTIO: fple: FALSE, sage: TRUE, isTableSpaceOnSage: 1, invalid table
sapce number: FALSE, prmsdec: TRUE, is kernel txn table space encrypted:
-1,is enc_ktid encrypted: -1, is sage enabled based on data -layer checks:
1, isQesSageEnabled: TRUE
NSMTIO: fple: FALSE, sage: TRUE, isTableSpaceOnSage: 1, invalid table
sapce number: FALSE, prmsdec: TRUE, is kernel txn table space encrypted:
-1,is enc_ktid encrypted: -1, is sage enabled based on data -layer checks:
1, isQesSageEnabled: TRUE
kcbism: islarge 0 next 0 nblks 36 type 3, bpid 65535, kcbisdbfc 0 kcbnhl
8192 kcbstt 9836 keep_nb 0 kcbnbh 255727 kcbnwp 4
kcbism: islarge 1 next 0 nblks 541438 type 2, bpid 3, kcbisdbfc 0 kcbnhl
8192 kcbstt 9836 keep_nb 0 kcbnbh 255727 kcbnwp 4
kcbimd: nblks 541438 kcbstt 9836 kcbnbh 25572 bpid 3 kcbisdbfc 0 is_medium
0
kcbivlo: nblks 541438 vlot 500 pnb 255727 kcbisdbfc 0 is_large 0
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from
caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:NoDirectRead:[CACHE_READ]: tsn: 53, objd: 1063361, objn:
1063361
ckpt: 0, nblks: 541438, ntcache: 0, ntdist:0
Direct Path for pdb 0 tsn 53 objd 1063361 objn 1063361
Direct Path 0 ckpt 0, nblks 541438 ntcache 0 ntdist 0
Direct Path mndb 0 tdiob 859 txiob 0 tciob 133
Direct path diomrc 8 dios 2 kcbisdbfc 0
kcbdpc: kx 8 kc 8 lhs 4 rhs NSMTIO: Additional Info: VLOT=1278635
Object# = 1063361, Object_Size = 541438 blocks
SqlId = XXXXXXX, plan_hash_value = 529217307, Partition# = 0
NSMTIO: fple: FALSE, sage: TRUE, isTableSpaceOnSage: 1, invalid table
sapce number: FALSE, prmsdec: TRUE, is kernel txn table space encrypted:
-1,is enc_ktid encrypted: -1, is sage enabled based on data -layer checks:
1, isQesSageEnabled: TRUE
NSMTIO: kcbzib: Cache Scan triggered for tsn = 53, rdba=0x27c04404, fn =
159, kobjd = 1063361, block cnt = 124, noncontig = FALSE pre-warm = FALSE,
prefetch = FALSE
NSMTIO: kcbzib: Cache Scan triggered for tsn = 53, rdba=0x27c0440c, fn =
159, kobjd = 1063361, block cnt = 116, noncontig = FALSE pre-warm = FALSE,
prefetch = FALSE
NSMTIO: kcbzib: Cache Scan triggered for tsn = 53, rdba=0x27c04414, fn =
159, kobjd = 1063361, block cnt = 108, noncontig = FALSE pre-warm = FALSE,
prefetch = FALSE
NSMTIO: kcbzib: Cache Scan triggered for tsn = 53, rdba=0x27c0441c, fn =
159, kobjd = 1063361, block cnt = 100, noncontig = FALSE pre-warm = FALSE,
prefetch = FALSE
On Mon, May 24, 2021 at 4:39 AM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
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@xxxxxxxxx> 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
