Home » RDBMS Server » Performance Tuning » Finding Status Progress of Query with Single Block I/O
Finding Status Progress of Query with Single Block I/O [message #267838] Sat, 15 September 2007 11:39 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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



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 Go to previous messageGo to next message
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


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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: Finding Status Progress of Query with Single Block I/O [message #267850 is a reply to message #267838] Sat, 15 September 2007 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30141363147028
Re: Finding Status Progress of Query with Single Block I/O [message #267851 is a reply to message #267850] Sat, 15 September 2007 12:22 Go to previous message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Michel and Anacedent !!

Thank you very much..I will follow the tips.

Regards,
Pratap
Previous Topic: RMAN causing CPU boottleneck
Next Topic: statspack
Goto Forum:
  


Current Time: Thu Dec 08 14:03:19 CST 2016

Total time taken to generate the page: 0.08583 seconds