Home » RDBMS Server » Server Administration » Clarification Required On Query (Oracle 11.2.0.3, HP-UX 11.31)
Clarification Required On Query [message #657944] Mon, 28 November 2016 13:52 Go to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Hi All,

I am trying to get the object information related to performance issues. File#, block# information received from v$session_wait. Some of the online documents suggest to use the query:
SELECT relative_fno, owner, segment_name, segment_type 
 FROM dba_extents 
 WHERE file_id = &file_id
 AND &block BETWEEN block_id AND block_id + blocks - 1;

However, I am comfortable to use the below given query:

SELECT relative_fno, owner, segment_name, segment_type 
 FROM dba_extents 
 WHERE file_id = &file_id
 and block_id=&block_id

Both of them provide the same result. Is there any difference between the queries? How will it benefit?

Regards,
A
Re: Clarification Required On Query [message #657945 is a reply to message #657944] Mon, 28 November 2016 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
jesuisantony wrote on Mon, 28 November 2016 11:52
Hi All,

I am trying to get the object information related to performance issues. File#, block# information received from v$session_wait. Some of the online documents suggest to use the query:
SELECT relative_fno, owner, segment_name, segment_type 
 FROM dba_extents 
 WHERE file_id = &file_id
 AND &block BETWEEN block_id AND block_id + blocks - 1;

However, I am comfortable to use the below given query:

SELECT relative_fno, owner, segment_name, segment_type 
 FROM dba_extents 
 WHERE file_id = &file_id
 and block_id=&block_id

Both of them provide the same result. Is there any difference between the queries? How will it benefit?

Regards,
A
the top query reports on a range of blocks
the bottom query reports on a single block

Why do you think either will help solve a performance issue?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof


Re: Clarification Required On Query [message #657946 is a reply to message #657944] Mon, 28 November 2016 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The first one is correct, the second one is not.
You just have been lucky to get a result with the second one.
Just add one to your &block value and you will see.

Re: Clarification Required On Query [message #657947 is a reply to message #657946] Mon, 28 November 2016 15:32 Go to previous messageGo to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Thanks Mike. I am just trying to understand the logic behind this query. I am looking for the rows which have the event "read by other session". The columns p1,p2 provide me the datafile,block# information.

I have the block# directly. Why shouldn't I use it in th where clause rather have the range of blocks covered?
Re: Clarification Required On Query [message #657952 is a reply to message #657947] Tue, 29 November 2016 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't try what I suggested you to try, did you?

Example:
1 is equal to 1 and 1 is between 1 and 5
2 is not equal to 1 but is between 1 and 5

Re: Clarification Required On Query [message #659704 is a reply to message #657952] Fri, 27 January 2017 12:23 Go to previous message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Michel - When I add 1 to the block_id, I am not getting any row? Whereas I am can get the actual table information when querying using the actual block ID.

I am unable to understand this logic yet. Is there any document link that will allow me to understand this?
Previous Topic: Idle instance
Next Topic: How to check what Procedure is calling in database jobs?
Goto Forum:
  


Current Time: Thu Mar 28 15:21:50 CDT 2024