Re: What Would Cause....

Harvey wrote:
> Hi All:
>
> To clarify, the problem exists when, in DBA Studio (or EM Console) in
> the left pane you expand the storage tab and then select Tablespaces,
> which displays a summary of all of them in the right pane. In both
> cases there are a couple of Dictionary managed tablespaces, but the
> majority are LMT. On the same server there is a second instance
which
> is configured the same way (the scripts that created it are exactly
the
> same as those which built the first instance), and the response time
> there is a second or two for the same operation.
>
> Niall suggested doing a trace, which I did last night. The following
is
> the relevant section from the tkprof output.
>
> SELECT d.status "Status", d.tablespace_name "Name", d.contents
"Type",
> d.extent_management "Extent Management", TO_CHAR(NVL(a.bytes / 1024
/
> 1024,
> 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(a.bytes - NVL(f.bytes,
> 0), 0)
> /1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024,
0),
> '99999999.999') "Used (M)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0))
/
> a.bytes * 100, 0), '990.00') "Used %"
> FROM
> sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes
from
> dba_data_files group by tablespace_name) a, (select
tablespace_name,
> sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE
> d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name =
> f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND
> d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status",
> d.tablespace_name "Name", d.contents "Type", d.extent_management
> "Extent
> Management", TO_CHAR(NVL(a.bytes / 1024 / 1024,
0),'99,999,990.900')
> "Size
> (M)", TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999999.999')
> ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') "Used
(M)",
> TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM
> sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes
from
> dba_temp_files group by tablespace_name) a, (select
tablespace_name,
> sum(bytes_cached) bytes from v$temp_extent_pool group by
> tablespace_name) t
> WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name =
> t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND
> d.contents
> like 'TEMPORARY'
>
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.03 0.03 0 0
> 0 0
> Execute 1 0.01 0.01 0 0
> 0 0
> Fetch 1 492.85 495.16 36 106676654
> 153 17
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 492.89 495.20 36 106676654
> 153 17

My first comment is that Howard was correct, and I was incorrect, it is the LMTs that provide slow results in various of the views.

My second comment is that the various bugs surrounding dba_free_space, dba_extents etc, were often susceptible to you rewriting the offending SQL to include hints. In fact I suspect that your query will benefit from a use_hash hint as shown in Hans Peter-sloot's contribution to the faq at http://www.jlcomp.demon.co.uk/faq/free_space.html, I'd prefer to rewrite the query rather than the view as suggested in the article, but that is a minor quibble.

Incidentally this problem is, arguably, one of the few caused by lack of statistics on the data dictionary. The use of the hint in the definition of DBA_FREE_SPACE from 8i and up will cause the optimiser to use the CBO for these queries, but there won't be any appropriate stats on the underlying objects.
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com Received on Mon Dec 06 2004 - 06:59:29 CST