Thank you so much Jonathan.
I think you are exactly correct on this. I verified the P1 column in
dba_hist_active_sess_history and if i compare the value of P1 column after
subtracting the value of db_files parameter(which is set as 12000 in our
case) its pointing to the file_id of the tempfile only. So it means the
logging of current_obj# as the physical object(rather the global temporary
table) must be a sampling/logging timing error.
Also i checked , the overall database Avg response time of the 'direct path
read temp' is coming ~5 to 6milli sec and the Avg response time of the
'direct path write temp' is coming close to ~3milli second. And so , as you
pointed out already , these must not be coming from the flash cache.
And as Lok already pointed to the doc stating, for temp read and write to
use flash we have to have DB version 11.2.0.4.1 which we are not right now
but even we do that , we will be missing on the other criteria that says
the "data disk group" has to be on the HIGH redundancy mode. We are
currently on 'NORMAL' redundancy to reduce space usage. So in this
situation the best option seems to be the last one as mentioned in the doc
i.e '*if the temp is used intensively, a diskgroup from flash disks can be
created and the temp files can be placed in that Diskgroup*." So , as we
have ~200TB+ flash on our X8-M machine and two temp tablespaces each having
~1TB in size, it should be okay to park at least one temp tablespace
(~1TB) to fully flash.
Regards
Pap
On Sun, Jan 9, 2022 at 4:17 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Well done.
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),
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:
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.
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:
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
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
Jonathan
On Sat, 8 Jan 2022 at 19:57, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank You Lok.
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.
