| The difference between sql_text in v$sqlarea and v$sqltext [message #359902] |
Tue, 18 November 2008 12:09  |
efachim
Messages: 42
Registered: July 2008
|
Member |
|
|
Hi Guys,
I have implemented part of the wait statistics gathering package discussed in this link http://www.perfvision.com/ash.php, from the sticky thread above. Within it, the author chose to display the sql_text field from v$sqltext view which is clearly incomplete(not showing the complete sql statement often) compared to the sql_text field in v$sqlarea view. I am wondering whether anyone knows the advantage of doing it this way, rather than displaying the complete sql_text from v$sqlarea view?
Option 1 - Author's approach
select
t.address,
a.hash_value,
t.command_type,
t.piece,
t.sql_text,
a.disk_reads,
a.buffer_gets,
a.parse_calls
from v$sqltext t, v$sqlarea a
where t.hash_value in (select sql_hash_value from v$session)
and t.hash_value = a.hash_value;
Option 2 - My approach
select
t.address,
a.hash_value,
t.command_type,
t.piece,
a.sql_text,
a.disk_reads,
a.buffer_gets,
a.parse_calls
from v$sqltext t, v$sqlarea a
where t.hash_value in (select sql_hash_value from v$session)
and t.hash_value = a.hash_value;
thanks, E.
|
|
|
|
|
|
| Re: The difference between sql_text in v$sqlarea and v$sqltext [message #360413 is a reply to message #359904] |
Thu, 20 November 2008 22:04  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
try using each for something real, then after you have spent some time trying to get a sql query formmatted to a correct and readable form you can tell us why you might use the easy one instead of the hard one.
Bottom line is most of the time one of two things is true:
1) 99% of all SQL statements are less than 1000 bytes wide
2) you don't actually need to see more than the first 1000 bytes of the text
It is only in those rare situations where the query is actually longer than 1000 bytes, and you need to see the entire query, that you need to go to the other view with pieces and reconstruct the query.
kevin
[Updated on: Thu, 20 November 2008 22:05] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
