Home » RDBMS Server » Performance Tuning » TOP_LEVEL_SQL_ID (Oracle 11.2.0.4, Windows 2008 Server)
TOP_LEVEL_SQL_ID [message #652376] Tue, 07 June 2016 16:27 Go to next message
jesuisantony
Messages: 155
Registered: July 2006
Location: Chennai
Senior Member
We have been getting
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP 
error.

We enabled the trace and found the sql_id (select query) which was hitting the tablespace issue. The application user, machine and program details were identified. However, we were not sure which procedure was triggering this SQL statement. We understand that there is a column named "TOP_LEVEL_SQL_ID" in v$active_session_history. But I understand that we are not expected to use the tables provided through the diagnostic tuning pack.

Is there a way that I can find the top level sql during the runtime or through any other dynamic or fixed views.

We generated the trace through "1652 EVENTS ERRORSTACK LEVEL 3". This has bunch sql_id and statements mentioning parent and child cursor. I am unable to figure out the top level sql query.

Please help on this.
Re: TOP_LEVEL_SQL_ID [message #652383 is a reply to message #652376] Wed, 08 June 2016 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way that I can find the top level sql during the runtime or through any other dynamic or fixed views.


Use Statspack.

Re: TOP_LEVEL_SQL_ID [message #652471 is a reply to message #652376] Thu, 09 June 2016 10:51 Go to previous messageGo to next message
jesuisantony
Messages: 155
Registered: July 2006
Location: Chennai
Senior Member
Can you please help me here? I am not sure which section to look at.

I will provide the scenario. There is a SELECT statement hitting the tablespace issue. This is being triggered by a DBMS_STATS procedure. I figured out that the SELECT statement is encountering issue? How can I figure out the PL/SQL procedure which is triggering this SQL statement.
Re: TOP_LEVEL_SQL_ID [message #652474 is a reply to message #652471] Thu, 09 June 2016 13:41 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
If you upload the statspack report that covers the time of the problem, perhaps someone can assist. If it really is dbms_stats that is hitting the issue, you don't have may options. Why not just make the temporary tablespace bigger?
Re: TOP_LEVEL_SQL_ID [message #652491 is a reply to message #652376] Fri, 10 June 2016 03:52 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
As a matter of interest, what does the following yield?
SQL> SELECT SUM(bytes)/1048576
     FROM dba_temp_files
     WHERE tablespace_name='TEMP';
Re: TOP_LEVEL_SQL_ID [message #652501 is a reply to message #652471] Fri, 10 June 2016 10:10 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
s. program_id, program_line# in v$sql, stats$sql_summary.
Re: TOP_LEVEL_SQL_ID [message #652507 is a reply to message #652491] Fri, 10 June 2016 15:29 Go to previous messageGo to next message
jesuisantony
Messages: 155
Registered: July 2006
Location: Chennai
Senior Member
gazzag - The output is 10000.

LNossov - Couldn't get you. I can see the columns program_id, program_line# in both v$sql, stats$sql_summary. Will this help me to get the top_level_sql_id. Or is there something else that I can get out of this?

John - I can make the temp tablespace big. But I am not sure how much big. The explain plan shows that the estimated size is 36G. But I am not sure if that can fix the issue. The reason I am trying to find the top level sql is, it is not evident how the sql is getting triggered. If I can get the top_level_sql then I can work drill this issue down further.
Re: TOP_LEVEL_SQL_ID [message #652524 is a reply to message #652507] Sat, 11 June 2016 13:08 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
It is your top level procedure. program_id = object_id in dba_objects.
Re: TOP_LEVEL_SQL_ID [message #659702 is a reply to message #652524] Fri, 27 January 2017 12:07 Go to previous message
jesuisantony
Messages: 155
Registered: July 2006
Location: Chennai
Senior Member
Thank you. You have answered my query.
Previous Topic: How long lock should be in order to take snapshot of locks
Next Topic: BULK INSERT PERFORMANCE ON PROD
Goto Forum:
  


Current Time: Sat Jan 20 06:07:13 CST 2018

Total time taken to generate the page: 0.01326 seconds