Extent allocation time -- process check
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 4 Feb 2010 13:23:16 -0600 (CST)
Hey all,
We had an unexpected filling of a production tablespace in 10.1.0.5.0. Now,
after the triage, I'm trying to determine how that could have happened.
Here's the facts:
- Single instance (non-RAC)
- TS is locally managed and AUTOALLOCATE (sigh)
- 7 datafiles totalling ~214GB
- Lovely Grid Control (LGC) reported shortly before the incident that the
TS was 95.66% full, which leaves ~9GB unallocated.
- LGC has the TS full metric collecting every 30 minutes.
- Consistent growth over the past three years has been 11GB per *month*.
The 9GB growth spike in a matter of minutes is highly suspect. My theory is
that LGC is full of S. Either LCG's wrong or there is significant waste due
to extent fragmentation, which in my case is much less likely than the
former as segments in this TS are rarely dropped (the DBA_FREE_SPACE view
would seem to agree with this).
So I've restored the pertinent archive logs plus and minus two hours to view
the extent allocations as specified in this thread:
//www.freelists.org/post/oracle-l/Extent-allocation-time,3
In order to pull the object name from the opcode 14.4 trace, I've taken the
DBA hex value from the "ADD:" line and used it in this SQL:
SELECT
'a3bc189',
file_id,
tablespace_name,
owner,
segment_name,
block_id
FROM
dba_extents
WHERE
file_id = DBMS_UTILITY.data_block_address_file (TO_NUMBER ('a3bc189',
'xxxxxxxx'))
AND block_id = DBMS_UTILITY.data_block_address_block (TO_NUMBER
('a3bc189',
'xxxxxxxx'));
...with 'a3bc189' being the DBA hex value from the trace file. Repeat for
each value found in the trace.
Is this the (or 'a') correct method for determining what extents were
allocated in a given time period? Everything appears to tie out for me, but
I can't find any verification of the method I'm using.
Thoughts?
TIA!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
