Re: How to get the maximum space used in TEMP during process execution

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Tue, 4 Nov 2014 12:26:39 -0600
Message-ID: <CAMHX9JLt-uzqEUtZTwAbCp_CLTNQZNhJQ9L8Qm7o2pVxYsJFdA_at_mail.gmail.com>

The problem with these memory/temp space ASH metrics is that if the offending session ends up being idle (while still having the cursor open, but just not fetching from it at the moment) then these records won't show up in ASH at all (and you'll miss some of the mem/temp usage). But in practice it's mostly ok, when you have a runaway query using lots of temp, it tends to be active too doing more and more of its stuff. As far as PGA/memory goes, you could correlate the SUM of ASH pga_usage (from a single ASH sample) to DBA_HIST_PROCESS_MEM_SUMMARY reported mem usage to see how much is "lost". With TEMP you'd need to sample v$tempseg_usage/v$temp_space_header if you want a more accurate picture. But ASH is fine in 99% of cases.

Note that you shouldn't just use MAX(temp_space_allocated) across every ASH sample nor SUM(temp_space_allocated) across multiple ASH samples as these metrics are not additive across ASH samples. You'll need to group by ASH sample_id or sample_time:

http://blog.tanelpoder.com/files/scripts/ash/ashmem.sql

SQL> _at_ash/ashmem

SAMPLE_TIME                    TOTAL_PGA_MB TOTAL_TEMP_MP ACT_SESSIONS
------------------------------ ------------ ------------- ------------
2014-11-03 20:34:04.638                2894             0           22
2014-11-03 20:34:03.638                2862             0           21
2014-11-03 20:33:29.358                2827             0           12
2014-11-03 20:33:33.378                2807             0           14
2014-11-03 20:34:06.658                2757             0           21
2014-11-03 20:59:14.611                2753             0          918
2014-11-03 20:34:00.608                2687             0           20
2014-11-03 20:34:02.628                2684             0           21
2014-11-03 20:33:31.368                2649             0           12
2014-11-03 20:59:13.601                2639             0          909

Then you could drill down into just one ASH sample (more group by queries) to find the top SQL ID or operation...

Tanel

On Tue, Nov 4, 2014 at 10:38 AM, kyle Hailey <kylelf_at_gmail.com> wrote:

>
> If you are on 11.2+ you can check out TEMP_SPACE_ALLOCATED in
> v$active_session_history.
>
> TEMP_SPACE_ALLOCATED - Amount of TEMP memory (in bytes) consumed by this
> session at the time this sample was taken
>
>
> I haven't had a chance to play with it much but it sounds pretty good for
> tracking temp space usage as a session does work.
>
>
>
> - Kyle
>
>
> On Sun, Nov 2, 2014 at 1:19 AM, Eriovaldo Andrietta <ecandrietta_at_gmail.com
> > wrote:
>
>> Hello,
>>
>> I would like to know if this way is correct. If no, what is the correct
>> way to get the maximum space used during a process execution.
>>
>> I am doing it:
>>
>> begin
>> dbms_application_info.set_module(module_name => 'MY_PROCESS_001'
>> ,action_name => '');
>> end;
>> /
>>
>> then I execute the process:
>> select * from ...
>> update table set ...
>> delete table ...
>> exec procedure ....
>>
>> at final I do:
>>
>> select max(temp_space_allocated/1024/1024) TEMP_MB
>> from v$active_session_history
>> where module = 'MY_PROCESS_001';
>>
>> The result of this query is the maximum space quantity used in TEMP
>> during all the time. Is it correct ?
>>
>> Is there another way to do it ?
>>
>> Regards
>> Eriovaldo
>>
>>
>>
>

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

Received on Tue Nov 04 2014 - 19:26:39 CET