Abnormal Form

I've been enjoying the clean and organized look of Subquery Factoring for awhile now, despite some of the current limitations of the feature. But I've recently run into what appears to be very strange behavior when using a global view (gv$...) in a subquery. Upon reflection, this limitation is understandable, given the nature of global instance gv$ views, but it's still a rude surprise. Try the following test in your RAC test instance to see if the behavior is the same. We're using 9.2.0.8.0.

This query displays a list of all the sessions in all instances of a database, grouped by client machine. It was run while connected to instance 1.

select inst<em>id, machine, count(*) as sessions
from gv$session
group by inst</em>id, machine;

INST_ID MACHINE   SESSIONS

      1 appsrv1         20
      1 appsrv2         40
      2 batchsrv1        5
      2 batchsrv2        5

Let's test it in a subquery: with sessions as (
select inst<em>id, machine, count(*) as sessions
from gv$session
group by inst</em>id, machine
)
select inst_id, machine, sessions
from sessions;

INST_ID MACHINE   SESSIONS

      1 appsrv1         20
      1 appsrv2         40
      2 batchsrv1        5
      2 batchsrv2        5

So far, so good. Now, let's try adding the per instance subtotals: with sessions as (
select inst<em>id, machine, count(*) as sessions
from gv$session
group by inst</em>id, machine
)
select inst<em>id, machine, sessions
from sessions
union all
select inst</em>id, 'Instance Subtotal', sum(sessions)
from sessions
group by inst_id;

INST_ID MACHINE           SESSIONS

      1 appsrv1                 20
      1 appsrv2                 40
      1 Instance Subtotal       60

I was expecting to see an instance subtotal of 60 for instance 1 and 10 for instance 2. But as you can see, the session counts from the second node disappeared entirely. So, I've learned my lesson: Don't use subquery factoring with global views.