OraFAQ Forum: Performance Tuning » TOP_LEVEL_SQL

Home » RDBMS Server » Performance Tuning » TOP_LEVEL_SQL_ID (Oracle 11.2.0.4, Windows 2008 Server)

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

TOP_LEVEL_SQL_ID [message #652376]

Tue, 07 June 2016 16:27

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

We have been getting

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: TOP_LEVEL_SQL_ID [message #652383 is a reply to message #652376]

Wed, 08 June 2016 00:35

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

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.

Report message to a moderator

Re: TOP_LEVEL_SQL_ID [message #652471 is a reply to message #652376]

Thu, 09 June 2016 10:51

jesuisantony
Messages: 166
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.

Report message to a moderator

Re: TOP_LEVEL_SQL_ID [message #652474 is a reply to message #652471]

Thu, 09 June 2016 13:41

John Watson
Messages: 8978
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?

Report message to a moderator

Re: TOP_LEVEL_SQL_ID [message #652491 is a reply to message #652376]

Fri, 10 June 2016 03:52

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

As a matter of interest, what does the following yield?

Code: [Select all] [Show/ hide]

SQL> SELECT SUM(bytes)/1048576
     FROM dba_temp_files
     WHERE tablespace_name='TEMP';

Report message to a moderator

Re: TOP_LEVEL_SQL_ID [message #652501 is a reply to message #652471]

Fri, 10 June 2016 10:10

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

s. program_id, program_line# in v$sql, stats$sql_summary.

Report message to a moderator

Re: TOP_LEVEL_SQL_ID [message #652507 is a reply to message #652491]

Fri, 10 June 2016 15:29

jesuisantony
Messages: 166
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.

Report message to a moderator