OraFAQ Forum: Performance Tuning » Finding Status Progress of Query with Single Block I/O

Home » RDBMS Server » Performance Tuning » Finding Status Progress of Query with Single Block I/O

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

Finding Status Progress of Query with Single Block I/O [message #267838]

Sat, 15 September 2007 11:39

kumarsz
Messages: 180
Registered: March 2005
Location: canada

Senior Member

Hi,

I am using Oracle 9i R2 on Linux

I have a query for which Execution Plan shows index access (single block access)

If I have a executed the query and it is not completed for long time, is there a way to find out whether it is really executing and what is the Progress of the query?

Thanks and Regards,
Pratap

Report message to a moderator

Re: Finding Status Progress of Query with Single Block I/O [message #267840 is a reply to message #267838]

Sat, 15 September 2007 11:42

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Did you read the sticky on top of the forum?
Did you try what is indicated inside?

Regards
Michel

Report message to a moderator

Re: Finding Status Progress of Query with Single Block I/O [message #267841 is a reply to message #267840]

Sat, 15 September 2007 11:46

kumarsz
Messages: 180
Registered: March 2005
Location: canada

Senior Member

Hi Michel,

I have ready the sticky and i am participating this forum for a long time, so will adhere to the rules.

However, this question is not related to particular query and many times i am helpless with some queries which show index access but stall for a long time.

Thanks and Regards,
Pratap

Report message to a moderator

Re: Finding Status Progress of Query with Single Block I/O [message #267844 is a reply to message #267841]

Sat, 15 September 2007 11:53

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I was talking about this sticky: 10g - How to Identify Performance Bottleneck which is more general than its title and talk about query tuning.

You can always activate trace for the sesson executing your query.
Have a look at How to set trace by Pete Finnigan.

Regards
Michel

Report message to a moderator

Re: Finding Status Progress of Query with Single Block I/O [message #267846 is a reply to message #267838]

Sat, 15 September 2007 11:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

First determine the SID (& SERIAL#) of the session with the "long running" query.

SELECT * FROM V$SESS_IO WHERE SID = <long_sid>;
multiple time with short delay between invocation.
If numbers are increasing, then the query is retrieving results.


09:58:12 SQL> DESC V_$SESSION_LONGOPS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 OPNAME                                             VARCHAR2(64)
 TARGET                                             VARCHAR2(64)
 TARGET_DESC                                        VARCHAR2(32)
 SOFAR                                              NUMBER
 TOTALWORK                                          NUMBER
 UNITS                                              VARCHAR2(32)
 START_TIME                                         DATE
 LAST_UPDATE_TIME                                   DATE
 TIMESTAMP                                          DATE
 TIME_REMAINING                                     NUMBER
 ELAPSED_SECONDS                                    NUMBER
 CONTEXT                                            NUMBER
 MESSAGE                                            VARCHAR2(512)
 USERNAME                                           VARCHAR2(30)
 SQL_ADDRESS                                        RAW(4)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 QCSID                                              NUMBER

Report message to a moderator

Re: Finding Status Progress of Query with Single Block I/O [message #267847 is a reply to message #267844]

Sat, 15 September 2007 12:02

kumarsz
Messages: 180
Registered: March 2005
Location: canada

Senior Member

Many Thanks Michel !!

I will go through the link

However,with 9i R2 i am using
If i start the trace while the statement has already started executing and eventually if i stop the trace after some time say 30 min., the resulted tkprof file shows SQL Statements executed in session as 0. which will not help me if my query is really executing and where it has reached.

Here, do you mean rather than tkprof shall i start looking the raw trace file itself for the findings?

Thanks and Regards,
Pratap

Report message to a moderator

Re: Finding Status Progress of Query with Single Block I/O [message #267848 is a reply to message #267846]

Sat, 15 September 2007 12:07

kumarsz
Messages: 180
Registered: March 2005
Location: canada

Senior Member

Hello Anacedent,

i missed your post before earlier reply.

I will try the v$sess_io and will let you know the results ...many thanks for this.

To know further..Is it like that
The muliblock I/O queries appear in v$session_lonops and Single Block I/O queries appear in v$sess_IO?

Or Muliblock I/O ones appear both the places?

Thanks and Regards,
Pratap

Report message to a moderator

Re: Finding Status Progress of Query with Single Block I/O [message #267849 is a reply to message #267847]

Sat, 15 September 2007 12:12

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, look at the raw trace, it is not difficult to read.
You can also query v$session_wait to see on what is waiting your query but I prefer 10046/SQL trace.

Regards
Michel

Report message to a moderator