Re: V$SQLAREA
Date: Thu, 15 Dec 2011 03:23:59 +0000 (UTC)
Message-ID: <pan.2011.12.15.03.23.59_at_gmail.com>
On Thu, 15 Dec 2011 02:26:06 +0000, Mladen Gogala wrote:
> On Wed, 14 Dec 2011 17:46:18 -0800, onedbguru 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."
>
> 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.
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
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU
)| Time |
| 0 | SELECT STATEMENT | | 1 | 26 |
0 (0
)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | FIXED TABLE FULL| X$KGLCURSOR_CHILD_SQLID | 1 | 26 |0 (0
)| 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
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Tim
e |
| 0 | SELECT STATEMENT | | 1 | 13 | 0
(0)| 00:
00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | FIXED TABLE FULL| X$KGLCURSOR_CHILD | 1 | 13 | 0
(0)| 00:
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.comReceived on Wed Dec 14 2011 - 21:23:59 CST