Re: Re[2]: Physics of the FILTER operation within SQL

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 2 Jul 2004 09:10:40 +0100
Message-ID: <00ba01c4600c$c6b21490$7102a8c0@Primary>

John,

You're observation about v$sqlarea is correct. However, that wasn't the view I mentioned:

>You can also play around with v$sql_workarea to get some idea

The view v$sql_workarea is a simple list of the cost and size of sort, hash, etc. operations that have been carried out recently.

(In fact, I've just decided that there probably won't be much difference in latch costs between hitting v$sql_workarea and v$sql - although it does depend on where you come from and where you are going to if you want to include the two of them in joins).

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "John Kanagaraj" <john.kanagaraj_at_hds.com> To: <oracle-l_at_freelists.org> Sent: Thursday, July 01, 2004 4:55 AM Subject: RE: Re[2]: Physics of the FILTER operation within SQL_PLANE.

Jonathan,

>You can also play around with v$sql_workarea to get some idea
>of what big operations have used a lot of CPU (rather than
>hammering v$sql - which is a bit labour intensive and latch-
>unfriendly in a highly concurrent system).

I thought that queries on v$sqlarea were to be avoided in favour of v$sql since the former requires a group by on x$kglcursor while the latter is a simple select on x$kglcursorr? Would that not prolong the query and increase the chances of latch contention when v$sqlarea is queried instead of v$sql? [I checked this on 8.1.7, 9.2.0.4 and 10.1.0.20.]

Thanks for all your (very valuable) inputs to this list! John Kanagaraj

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 02 2004 - 03:12:25 CDT

Re: Re[2]: Physics of the FILTER operation within SQL_PLANE.