OraFAQ Forum: Performance Tuning » TEMP usage history

Home » RDBMS Server » Performance Tuning » TEMP usage history (Oracle 10g, Sun OS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TEMP usage history [message #466345]

Mon, 19 July 2010 02:40

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

Hi ,

My stats jobs failed last night with "ORA-01652 :Unable to extend TEMP" error.

Is there any way to check this history data, what other session was using TEMP tablespace extensively ?

thanks

Report message to a moderator

Re: TEMP usage history [message #466347 is a reply to message #466345]

Mon, 19 July 2010 02:47

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

gkrishn wrote on Mon, 19 July 2010 02:40

Hi ,

My stats jobs failed last night with "ORA-01652 :Unable to extend TEMP" error.

Is there any way to check this history data, what other session was using TEMP tablespace extensively ?

thanks

No.

ORA-01652: unable to extend temp segment by string in tablespace string 
Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
 
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Report message to a moderator

Re: TEMP usage history [message #466348 is a reply to message #466347]

Mon, 19 July 2010 02:51

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

Hi Rahul, this is first time it failed with TEMP issue.
There must be some user query thta might have used lot of TEMP segments) i would like to point out this to the user so that this
wouldnt happen again.

I dont know where to look for this history information.

Report message to a moderator

Re: TEMP usage history [message #466350 is a reply to message #466348]

Mon, 19 July 2010 02:57

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

you said History DATA. not Query so I said NO.

Yes you can check the Which Query ware running in perticular time window.

1/ Log int to OEM
2/ Select Target ( Database Instance)
3/ go to "Performance"
3.1/ In view data drop down ( on Right hand side ) Select "Historical"

or you can check same in top activity. you will get the TOP SQL running.

Report message to a moderator

Re: TEMP usage history [message #466357 is a reply to message #466348]

Mon, 19 July 2010 03:31

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

gkrishn wrote on Mon, 19 July 2010 02:51

Hi Rahul, this is first time it failed with TEMP issue.
There must be some user query thta might have used lot of TEMP segments) i would like to point out this to the user so that this
wouldnt happen again.

I dont know where to look for this history information.

1/ Check for query which are performing BIG group by Results.
2/ Check for inner views with less selectivity and Group by.

Report message to a moderator

Re: TEMP usage history [message #486095 is a reply to message #466345]

Mon, 13 December 2010 10:06

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

i found it realy difficult , as my stats job runs for 8 hours.Is there a way to get TEMP usage statistics over a period of time,by session/sqlid ?

Report message to a moderator

Re: TEMP usage history [message #486104 is a reply to message #486095]

Mon, 13 December 2010 10:41

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Is there a way to get TEMP usage statistics over a period of time,by session/sqlid ?

You can join DBA_HIST_ACTIVE_SESS_HISTORY and DBA_HIST_SQL_PLAN and get SESSION_ID and TEMP_SPACE columns... if you have purchased the option to use these views.

Regards
Michel

Report message to a moderator

Previous Topic:	SID column in V$SESSION
Next Topic:	Index leading columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 20 17:35:10 CST 2025