In older versions of Oracle, there was a rule saying that V$SQLAREA is
evil because queries against it will hit library cache latch once for
every row. I was unable to confirm this in 11.2.0.3. The only latch it
seems to be hitting is SQL memory manager workarea list latch.
Did anyone else notice that? If my findings are right, that would make
V$SQLAREA view much more useful for monitoring and querying purposes.


--
http://mgogala.byethost5.com

On Wed, 14 Dec 2011 20:47:02 +0000, Mladen Gogala wrote:

BTW, here is an old discussion from the Oracle-l mentioning V$SQLAREA:

http://www.freelists.org/post/oracle...out-VSQLAREA,1

Another text is here:
https://netfiles.uiuc.edu/jstrode/www/oraview/V$SQLAREA.html


--
http://mgogala.byethost5.com

On Dec 14, 3:57*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
You have to remember that the whole latch/locking mechanisms changed
with 11.1 and even more in 11.2
Here is a quote from Tanel Poder:
"In Oracle 11g even more library cache operations have been changed to
use KGX mutexes instead of latches."

Expand your search like:
http://lmgtfy.com/?q=oracle+11.2+latch+locking

On Wed, 14 Dec 2011 17:46:18 -0800, onedbguru wrote:

I am aware of that. Jonathan Lewis also writes about that in his latest
book. I only wanted to stress the fact that the old recommendation for
V$SQLAREA no longer holds true and that the steep price tag connected to
that table has been removed.



--
http://mgogala.byethost5.com

On Thu, 15 Dec 2011 02:26:06 +0000, Mladen Gogala wrote:

BTW, I tested this and here is the test:

SQL> set autotrace on
SQL> select count(*) from v$sqlarea;

COUNT(*)
----------
844

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 3512498725

--------------------------------------------------------------------------------
-------------

)| Time |

--------------------------------------------------------------------------------
-------------

)| 00:00:01 |

)| 00:00:01 |

--------------------------------------------------------------------------------
-------------


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE'))


Statistics
----------------------------------------------------------
317 recursive calls
0 db block gets
79 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from v$sql;

COUNT(*)
----------
1367

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 1001747286

--------------------------------------------------------------------------------
-------

e |

--------------------------------------------------------------------------------
-------

00:01 |

00:01 |

--------------------------------------------------------------------------------
-------


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("INST_ID"=USERENV('INSTANCE'))


Statistics
----------------------------------------------------------
327 recursive calls
0 db block gets
72 consistent gets
1 physical reads
0 redo size
423 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL> select name from v$latch where name like '%library%';

NAME
----------------------------------------------------------------
library cache load lock

Elapsed: 00:00:00.03
SQL> select gets,misses,immediate_gets,immediate_misses
2 from v$latch where name='library cache load lock';

GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- -------------- ----------------
0 0 0 0

Elapsed: 00:00:00.01
SQL>

This was freshly started instance. Select from V$SQLAREA used to last
much longer than the select from V$SQL and there were library cache latch
waits. In this case, after a query from V$SQLAREA, there were no library
cache latch hits. There is a complete new latch zoo in 11.2.0.3:

SQL> select name from v$latch where name like '%curso%';

no rows selected

Elapsed: 00:00:00.01
SQL> select name from v$latch where name like '%SQL%';

NAME
----------------------------------------------------------------
SQL memory manager latch
SQL memory manager workarea list latch
XDB PL/SQL Support
PL/SQL warning settings

Elapsed: 00:00:00.01

I was monitoring each of these latches and the only latch that was
increased after the each execution was SQL memory manager workarea list
latch. I was unable to establish the precise correlation, though. My
conclusion is that querying V$SQLAREA is no longer detrimental for the
instance performance, as it used to be. The version I used for testing is
11.2.0.3


--
http://mgogala.byethost5.com

However, just as they make something viable, Oracle often makes it
redundant.

v$sqlstats came out as a "latch-free" equivalent of v$sqlarea in 10g, and
then 11g gave us v$sqlstats_plan_hash.

The latter sums child cursors by plan hash value - so you can get a better
idea from the summary of the different amount of work that different plans
may give for the same query.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen (AT) gmail (DOT) com> wrote