Re: Overall executions at DB level

On Thu, 10 Aug 2006 05:32:39 -0700, Spendius wrote:

> Hi,
> Do you think doing
> SQL> SELECT SUM(executions) FROM V$SQL[AREA]
> at regular intervals relevantly reflects the number of total
> SQL executions whatsoever in your database ?
> (I'm not sure as I've noticed sometimes that substracting
> a value from the next one returns a negative number
> - guess it's because in the meantime Oracle rid V$SQL
> of a few statements...)
>
> Thanks.
> Spendius

You're aware that touching a row in V$SQL means first acquiring a latch? SQL> select gets,immediate_gets from v$latch   2 where name like 'SQL memory manager%';

      GETS IMMEDIATE_GETS
---------- --------------

      6124              0
         0             90

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

  COUNT(*)

       313

SQL> @/tmp/1

      GETS IMMEDIATE_GETS
---------- --------------

      6530              0
         0             96

SQL> Doing a sum on executions from V$SQL will touch each row, every time you do it. Of course, it all depends on the interval. As for the negative values, Oracle performance tables are not protected by any transaction consistency mechanisms. If you are unlucky enough to select while the table is being written into, oh well....

-- 
http://www.mgogala.com