DA Morgan wrote:
> Howard J. Rogers wrote:
>
>> Niall Litchfield wrote:
>>
>>> "Harvey" <harveyb_at_NoSpambeliveau.ccHere> wrote in message
>>> news:41B06DE3.9C679990_at_NoSpambeliveau.ccHere...
>>>
>>>> Hi All:
>>>>
>>>> I've encountered this situation twice, and I'm not sure of what the
>>>> cause is. If anyone has a hint of where to look I'd appreciate it.
>>>>
>>>> The problem - when looking at a database via DBA Studio I go to Storage
>>>> and then Tablespaces to display the list/utilization. Generally
>>>> speaking this takes a few seconds to load/display, however, what I'm
>>>> encountering are significant delays, in the order of up to ten minutes.
>>>> I've had two databases that were configured identically, with one
>>>> displaying this behavior and the other working fine. There's nothing
>>>> remarkable in the alert logs. Any ideas/suggestions?
>>>>
>>>> The environment - Oracle 8.1.7.4 on Solaris 8. (The environment for
>>>> the
>>>> first encounter of this type was Oracle 8.1.7.4 on Windows 2000).
>>>
>>>
>>>
>>>
>>> I wonder if the *slow* tablespaces are in fact dictionary managed and
>>> have rather a lot of extents in the objects in them, whereas the
>>> *fast* ones are locally managed.
>>> You can however find out rather than guessing. You can trace the dba
>>> studio session and then see (using tkprof, traceanalyser or some
>>> other tool) what is consuming the time. ten minutes does sound
>>> excessive.
>>>
>>> Cheers
>>>
>>>
>>
>>
>> I realise we don't have to guess, because the OP could test as you
>> describe. But I'd like to predict that the slowly-displaying
>> tablespaces will actually be the locally-managed ones. And the
>> fast-displaying ones will be dictionary-managed. Not out of
>> perversity, but because extent calculations are the one thing LMTs do
>> worse than DMTs.
>>
>> It will be interesting to see whether the style of extent management
>> has anything to do with this at all!
>>
>> Regards
>> HJR
>
>
> Just to be contrary I'll posit that it is neither. I can't see any
> difference in asking the equivalent question in SQL*Plus so I'll put
> my money on the fact that it is the tool and not the database.
What does "[there is] no difference in asking the equivalent question in
SQL*Plus" mean?
The question the OP is asking, I believe, is to find out about used
extents and free space within a tablespace.
Presumably, therefore, the "equivalent" display mechanism in SQL*Plus is
a query from dba_extents and/or dba_free_space. If you're thinking of
some other equivalent, you should let us in on the specifics.
If it is indeed the case that you are thinking of querying dba_extents
and/or dba_free_space in SQL*Plus, then I'm afraid that it is definitely
the case that it is generally slower to query those views for LMTs than
DMTs. That you haven't been able to "see" it, doesn't mean the slow-down
isn't there.
Connor McDonald discussed the point in a contribution to the Cooperative
FAQ on Jonathan's website (where he said that LMTs are not the only
reason querying dba_extents is slow, but didn't dismiss that as one
factor either). And if I could remember where Jonathan himself discussed
the issue, I'd point you to the relevant quotation. But I don't, so I
can't, and maybe Jonathan could refresh my memory.
HJR
Received on Sat Dec 04 2004 - 20:58:07 CST
