OraFAQ Forum: Performance Tuning » The difference between sql

Home » RDBMS Server » Performance Tuning » The difference between sql_text in v$sqlarea and v$sqltext (Oracle 9i, 9.2.0.2, Linux)

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

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

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

efachim
Messages: 42
Registered: July 2008

Member

Thanks Michel and Kevin for your feedback.

Report message to a moderator

Previous Topic:	Is this a 10g bug?
Next Topic:	performance problems when using ...like string\|\|'%';

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 08:21:46 CDT 2024