Home » RDBMS Server » Performance Tuning » The difference between sql_text in v$sqlarea and v$sqltext (Oracle 9i, 9.2.0.2, Linux)
The difference between sql_text in v$sqlarea and v$sqltext [message #359902] Tue, 18 November 2008 12:09 Go to next message
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 #359904 is a reply to message #359902] Tue, 18 November 2008 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With v$sqlarea you have not the full query text unless you use sql_fulltext.
With v$sqltext you can display each row on a single line.

Regards
Michel
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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

Re: The difference between sql_text in v$sqlarea and v$sqltext [message #360613 is a reply to message #359902] Fri, 21 November 2008 10:59 Go to previous message
efachim
Messages: 42
Registered: July 2008
Member
Thanks Michel and Kevin for your feedback.
Previous Topic: Is this a 10g bug?
Next Topic: performance problems when using ...like string||'%';
Goto Forum:
  


Current Time: Mon Dec 05 04:44:25 CST 2016

Total time taken to generate the page: 0.08252 seconds