Re: Interesting problem
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: John Kanagaraj <john.kanagaraj@xxxxxxx>
- Date: Fri, 27 May 2005 22:10:00 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
