developers

    Forum Stats

  • 3,873,762 Users
  • 2,266,638 Discussions
  • 7,911,626 Comments

Discussions

OEM temp tablespace usage

2642171
2642171 Member Posts: 11
edited Nov 25, 2019 5:58AM in General Database Discussions

Hi

Can anyone tell me where I can find historical temp data usage in OEM?

We have OEM installed for the database.

It ran out of temp tablespace a few days ago and I want to check which user process were using the most temp tablespace.

Thanks in advance.

Tagged:
AndrewSayerAdityanath DewoolkarJonathan LewisUser_595E7

Answers

  • GregV
    GregV Member Posts: 3,106 Gold Crown
    edited Nov 21, 2019 10:43AM

    Hi,

    I don't think this is available. You can follow this note that suggests a method to monitor temp usage over time:

    How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

    User_595E7
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Nov 21, 2019 12:14PM
    2642171 wrote:Can anyone tell me where I can find historical temp data usage in OEM? It ran out of temp tablespace a few days ago and I want to check which user process were using the most temp tablespace. 

    I'm not sure you can find it from OEM, but if you're licensed to access all the performance and diagnostic data then you could query dba_hist_active_sess_history directly and this might give you some clues about users of large amounts of temporary space.

    select

            sample_id, session_id, session_serial#, sql_id, temp_space_allocated

    from

            dba_hist_active_sess_history

    where

            dbid = {your db id}

    and     instance_number = {your instance: non-RAC => 1}

    and     sample_time between {start date/time} and {end date/time}

    and     nvl(temp_space_allocated,0) != 0

    order by

            session_id, sample_id

    /

    This isn't perfect. You're looking at snapshots taken every 10 seconds so a session might acquire and release lots of temp space in a 10 second gap. UPDATE:  that last acquire/release might be wrong; it's possible that the session will simply remember its past allocations forever, so the key measure might be an increase in the allocation reported over time rather than an absolute size.

    Also, the sql_id reported is the sql_id of the query actually running at that instant, which might not be the query responsible for the temp space allocation - added to which if there are several open cursors for a session (e.g. some complicate PL/SQL block in control) they might all have contributed to the total.  However, if you see a single SQL_ID over 5 minutes with no random sized gaps in the sample id (maybe sample time, one every 10 seconds is a better guideline) and the temp_space_allocated slowly growing then maybe that SQL_id is the one that took up the space.

    Regards

    Jonathan Lewis

    User_595E7
  • Adityanath Dewoolkar
    Adityanath Dewoolkar Member Posts: 346 Bronze Badge
    edited Nov 22, 2019 4:07AM

    You can use below query to identify problematic sql_id's in case you diagnostic tuning pack license:

    select sql_id,sum(time_Waited) from dba_hist_active_sess_history where snap_id>=&begin_snap and snap_id<=&end_snap

    and event='direct path write temp'

    group by sql_id

    order by 2 desc

    where begin_snap & end_snap covers time when issue occured,

    Regards,

    Adi

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Nov 22, 2019 3:39PM
    select sql_id,sum(time_Waited) from dba_hist_active_sess_history where snap_id>=&begin_snap and snap_id<=&end_snap

    No, do not sum time_waited - see http://www.aioug.org/sangam12/Presentations/20130.pdf  written by the people who created ASH.

    If you want a statistically valid estimate of the time spent in a particular wait event then count the number of samples for that event and multiply by the sample interval (which is 1 second for v$active_session_history and 10 seconds for dba_hist_active_sess_history).

    Regards

    Jonathan Lewis

    AndrewSayerAdityanath Dewoolkar
  • Adityanath Dewoolkar
    Adityanath Dewoolkar Member Posts: 346 Bronze Badge
    edited Nov 25, 2019 3:42AM

    Thanks @Jonathan Lewis

    Modified it accordingly:

    select sql_id,count(time_Waited)*10 from dba_hist_active_sess_history where snap_id>=&begin_snap and snap_id<=&end_snap

    and event='direct path write temp'

    group by sql_id

    order by 2 desc

    Regards,

    Adi

    Jonathan LewisUser_595E7
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Nov 25, 2019 5:23AM

    @Adi,

    It's probably better to use count(*) - otherwise someone else might read you code and think there's something very important about the time_waited column, and try to use that for some other unsuitable purpose.

    Regards

    Jonathan Lewis

    Adityanath Dewoolkar
  • Adityanath Dewoolkar
    Adityanath Dewoolkar Member Posts: 346 Bronze Badge
    edited Nov 25, 2019 5:58AM

    Modified it accordingly:

    select sql_id,count(*)*10 from dba_hist_active_sess_history where snap_id>=&begin_snap and snap_id<=&end_snap

    and event='direct path write temp'

    group by sql_id

    order by 2 desc

    Regards,
    Adi

    User_595E7
  • User_595E7
    User_595E7 Member Posts: 1 Blue Ribbon

    I found this discussion when searching the internet to find out what was causing our TEMP space issues.

    The SQL provided by Mr. Jonathan Lewis was really helpful to figure out that one of our user's query was the cause.

    Thank you Mr. Lewis, appreciate that you posted the solution here.


    Thanks,

    Sam

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond

    Sam,

    You're welcome.

    Thanks for the acknowledgement.


    Regards

    Jonathan Lewis

    User_595E7
developers