Re: Difference in temp table insert performance

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_at_gmail.com> 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_at_gmail.com> 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.
>>
>>

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