Schema browswer uses v$temp_extent_pool to determin how much TEMP space is in use.
However, if your database is RAC, then that only shows part of the picture, misleading you into thinking less TEMP is in use by excluding the other instances.
Is this a feature, i.e. intentional to only show a single instance’s usage? I suppose it’s possible somebody might want that but it seems inappropriate to me.
Or is this a bug - I think this is the case.
If it is a feature, is there a way to toggle RAC (gv$ queries) instead of current instance (v$) in the usage queries?
If not, can this be considered a feature request?
Are you sure it matters in this case? I just ran a little experiment:
On an otherwise idle rac cluster, I made two connections, one to each instance.
In instance one, I ran this query to start using up temp space:
select o1.*
from dba_objects o1, dba_objects o2
order by 2;
Then, in Toad connected to instance 2, I went to Administer -> Tablespaces and started hitting refresh. I could see my temp tablespace space usage increasing rather quickly.
ok, so different screens show different results for what is essentially the same information. That screams bug to me.
The Administrer->Tablespaces screen uses gv$temp_extent_pool (which seems correct to me)
Schema browser RHS uses v$temp_extent_pool (which seems wrong to me)
I think it does matter that the tool doesn’t indicate that it’s behaving differently and the schema browser’s behavior seems to give incorrect (or at least unexpected) results.
If found this because I’m monitoring a set of large processes that I know are chewing up temp. However the schema browser showed me that temp was only about 50% of what I had expected.
Fortunately, I was skeptical of my good fortune and double checked the results and saw that TEMP usage was double what was reported.
Had I been looking at the Administer page, then I’d have gotten the correct answer. But I already had the schema browser open so I looked there and was misled.
Again, I suppose somebody somewhere might want a single-instance view, but I think the schema browser should default to all instances, or at least highlight the fact that it’s not showing the full utilization, but only that of the current instance.
Yeah and I just realized that I have the threaded query option enabled in Toad so I’m not sure which instance my temp-space-hogging threaded query was connected to. I’ll change the SB to use GV for rac.
OK, it’s done for next beta (after today)