Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Interesting problem

Re: Interesting problem

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 27 May 2005 22:10:00 +0000
Message-Id: <1117231800l.4784l.1l@medo.noip.com>


Comments in-line.
On 05/27/2005 05:22:13 PM, John Kanagaraj wrote:
> Mladen,

>=20
> I think you are confusing V$SQL and V$SQLAREA - only the latter requires =
a
> SORT since it is a GROUP BY on V$SQL. It is true though that the lib cach=
e
> latch needs to be taken for access to either. If Quest Spotlight goes
> against V$SQLAREA, then they really don't know what they are doing. I als=
o
> believe that read consistency if provided for _some_ V$ views and not for
> some others.=20

>=20
> The query below goes against V$SQLAREA, but may be worth trying:

John, let's see. Here is a very simple script which should increase the num= ber of
latch gets, if I am correct:
=20
create global temporary table mylatch=20 on commit delete rows as
select name,gets from v$latch where name like 'library cache%'; commit;
REM /tmp/nuno.sql
insert into mylatch
select name,gets from v$latch where name like 'library cache%'; select count(*) from (select sql_text from v$sql); select l.name,l.gets - m.gets as "Diff. gets" from v$latch l, mylatch m
where l.name =3D m.name;
commit;

The part after insert is stored as a separate file called "nuno.sql". The database is my personal 10.1.0.4 toy on my home PC which I use only to=20 check and verify things like this one. Except for my foreground session, it is completely inactive. If I am mistaken, the difference should be in=20 single digits.

SQL> @/tmp/nuno

7 rows created.

  COUNT(*)


       538

NAME                      Diff. gets
------------------------- ----------
library cache                   2299
library cache lock                16
library cache pin                 24
library cache pin allocat          0

ion

library cache lock alloca 0
tion

library cache load lock            0
library cache hash chains          0

7 rows selected.

Commit complete.

So, with this attempt we got 2299 Library Cache latch hits. Let's see what = happens
when V$SQL is replaced by V$SQLAREA:

SQL> @/tmp/nuno

7 rows created.

  COUNT(*)


       539

NAME                      Diff. gets
------------------------- ----------
library cache                   2599
library cache lock               130
library cache pin                175
library cache pin allocat          8

ion

library cache lock alloca 6
tion

library cache load lock           18
library cache hash chains          0

7 rows selected.

Commit complete.

SQL> / Commit complete.

SQL> We have one more SQL (of course, the subquery is going toward a different t= able)
and 300 more hits. Returning things the way they were, replacing V$SQLAREA = with V$SQL=20
will give us this:

SQL> @/tmp/nuno

7 rows created.

  COUNT(*)


       549

NAME                      Diff. gets
------------------------- ----------
library cache                   2316
library cache lock                16
library cache pin                 24
library cache pin allocat          0

ion

library cache lock alloca 0
tion

library cache load lock            0
library cache hash chains          0

7 rows selected.

Commit complete.

SQL> The number of the hits to library cache latch is of the same order of magni= tude, ie.
V$SQLAREA is not significantly more "evil" then V$SQL. My original assumpti= on was wrong:
there is more then one latch hit per row, in both cases. This is freshly st= arted, almost
inactive database. I will leave to your imagination to extrapolate what wou= ld happen if we=20
tried doing this with a live OLTP database that has been up for two weeks a= nd is used by few=20
hundreds concurrent users. By "doing this", I mean executing query hitting = V$SQL in a sequential
manner, once every minute. I'm quite sure that the resident DBA would resor= t not to the=20
Qwest tools, but the Smith & Wesson ones. Given that S&W 44 magnum is the m= ost powerful handgun
in the world and would shoot my head clean off and that I am not a punk fee= ling lucky,=20
I would not do things like that.=20

--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 27 2005 - 18:14:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US