Re: Difference in temp table insert performance
Date: Sun, 9 Jan 2022 10:47:15 +0000
Message-ID: <CAGtsp8nak0AY14PY9pkQqUS4fRRM80h7EMZBCEjXmnuQ4MbPGw_at_mail.gmail.com>
Well done.
Generally I know what ought to be possible; and a lot of the time I know
what isn't yet possible (for the client's versions); but knowing exactly
what IS possible (or will shortly become possible) depends on a lot of
details that aren't always easy to find.
My guideline is:
You picked up on the firect path read requests / approximate read time
(336K/1277) ... which is good. But that was one detail that prompted me to
comment on the flash cache stuff. The average read time is around 3.5 -
4.00 milliseconds: which is slow for flash cache.
Another thought cross my mind about that following your reminder about the
object id being wrong. The wait event parameters can tell you something:
Even if the object id is about one of the permanent tables you can check
whether the file number is one of the temporary files. I think (based on my
memory of trace files) that for temporary files the p1 file number will be
the tempfile number PLUS the value of the db_files parameter.
Regards
On Sat, 8 Jan 2022 at 19:57, Pap <oracle.developer35_at_gmail.com> wrote:
> Thank You Lok.
So changing from 11.2.0.4.0 to 11.2.0.4.1 may make a difference to the
performance, and you've also discovered a detail in a MOS note that
(probably) isn't available in the Reference Manuals and might be version
dependent. I hope you now understand why I am often a little vague about
what should work in one release and what might change in some unspecified
release of a newer version when I don't have access to the data, the
application, the (identical) software or the platform (all of which are
available when I'm doing work for a paying client),
a) Know what might be possible
b) Check the relevant manuals
c) Check the MOS Knowledgebase
d) Check the MOS bug database
e) Internet search (Oracle-L, Oracle forums, generic google) -- > may lead
to repeat from (b) with better search terms
f) do the experiment - starting small then growing in scale and complexity
until it breaks or you're satisfied.
NAME : direct path read temp
PARAMETER1 : file number
PARAMETER2 : first dba
PARAMETER3 : block cnt
NAME : direct path write temp
PARAMETER1 : file number
PARAMETER2 : first dba
PARAMETER3 : block cnt
Jonathan
>
> I just had a quick look into the writeback flash cache doc . It says the
> DB home to be "11.2.0.4.*1 *or higher" (I see from v$version in our case
> its 11.2.0.4.*0*), and also i am surprised to see another criteria for
> writeback to work is Data disk group to have HIGH redundancy , but in this
> case as i see from v$asm_diskgroup , its set as 'NORMAL' redundancy. And
> Infra DBA saying it's done that way to save space.
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 09 2022 - 11:47:15 CET