| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: What Would Cause....
"Howard J. Rogers" wrote:
>
> 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
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
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
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
17 UNION-ALL
16 HASH JOIN OUTER
16 HASH JOIN OUTER
16 TABLE ACCESS FULL TS$
16 VIEW
16 SORT GROUP BY
22 VIEW DBA_DATA_FILES
22 UNION-ALL
4 NESTED LOOPS
5 NESTED LOOPS
23 FIXED TABLE FULL X$KCCFN
26 TABLE ACCESS BY INDEX ROWID FILE$
44 INDEX UNIQUE SCAN (object id 38)
4 TABLE ACCESS CLUSTER TS$
8 INDEX UNIQUE SCAN (object id 7)
18 NESTED LOOPS
19 NESTED LOOPS
19 NESTED LOOPS
23 FIXED TABLE FULL X$KCCFN
40 FIXED TABLE FIXED INDEX #1 X$KTFBHC
36 TABLE ACCESS BY INDEX ROWID FILE$
36 INDEX UNIQUE SCAN (object id 38)
18 TABLE ACCESS CLUSTER TS$
36 INDEX UNIQUE SCAN (object id 7)
16 VIEW
16 SORT GROUP BY
204987 VIEW DBA_FREE_SPACE
204987 UNION-ALL
204777 NESTED LOOPS
204778 NESTED LOOPS
23 INDEX FAST FULL SCAN (object id 39)
204799 TABLE ACCESS CLUSTER FET$
44 INDEX UNIQUE SCAN (object id 7)
204777 TABLE ACCESS CLUSTER TS$
210 NESTED LOOPS
211 NESTED LOOPS
13 TABLE ACCESS FULL TS$
222 FIXED TABLE FIXED INDEX #1 X$KTFBFE
210 INDEX UNIQUE SCAN (object id 39)
1 HASH JOIN OUTER
1 HASH JOIN OUTER
1 TABLE ACCESS FULL TS$
0 VIEW
0 SORT GROUP BY
0 NESTED LOOPS
2 TABLE ACCESS FULL TS$
0 FIXED TABLE FIXED INDEX #1 X$KTSTFC
1 VIEW
1 SORT GROUP BY
1 NESTED LOOPS
2 NESTED LOOPS
2 FIXED TABLE FULL X$KCCFN
2 FIXED TABLE FIXED INDEX #1 X$KTFTHC
1 TABLE ACCESS CLUSTER TS$
2 INDEX UNIQUE SCAN (object id 7)
********************************************************************************
Long-term I'll eventually be installing 9i and rebuilding the instance which will clean it up, but it's a hinderance at the moment.
Harvey
Harvey Received on Sun Dec 05 2004 - 07:19:15 CST
 |
 |