-
How much data actually in the table?
Hi,
I am sure this question has been visited several times in this forum but couldn't find old thread.
We have a 3rd party application that is growing the database quite rapidly. They ran their purge routine and I am not convinced if it actually did anything.
I don't know what tables it purges so I can't query those tables directly.
My fellow dba thinks that dba_free_space is telling us the current free space in the tablespace while I was telling him that it only tells us the free extents and not how densely populated they are (so even if it deleted 50% of the records, it will still show the same free space if the deletes were spanned across all the extents). I don't think we can find the actual data sapce without an export/import.
However I was thinking if using average_row_size and number of rows will tell me what I want.
Your suggestions please?
Thanks,
-Rajeev
Rajeev Suri
-
Not sure how using average_row_size and number of rows is going to help if you dont know the tables involved or dont have the stats from the tables before the purge?
-
You can run this script before and after your purge and you should be able to find what tables are getting hit. You could even customize it to hit a particular schema to narrow your search down.
Code:
select substr(segment_name,1,15) NAME,substr(segment_type,1,7) TYPE,
substr(tablespace_name,1,10) AS TABLESPACE ,
round(sum(bytes)/1024/1024,2) Mb ,
sum(decode(extent_id, 0, bytes, 0))/1024 initial_ex,
sum(decode(extent_id, 1, bytes, 0))/1024 next_ex ,
max(extent_id)+1 extents , sum(bytes)/1024 ttlsize
from dba_extents
Where segment_type='TABLE'
and extent_id>'30'
group by segment_name, segment_type, tablespace_name
order by 7 ASC;
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
Fraze,
Thanks for pointing that...actually I know just a couple of big tables out of several hundred out there that I may go do a query now (I don't know how many records were there though before the purge)
OracleDoc,
I am not sure how this query will tell me anything useful?
If the records were evenly spread then their is a fair chance that each of those extents will still be present after the purge.
I am looking for something that takes into account the density of the extents and not the number.
Thanks,
-Rajeev
Rajeev Suri
-
What good will that statement do? Just by deleting rows from a table you dont get any extents back. You can only free extents by truncating the table or using deallocate unused to free extents over the high water mark
-
my mistake guys sorry about that. For some reason I thought that if you ran that script before your purge you could look at the megs column and see how big the table was then run it again after to see the difference. But, such is not the case. Because as you said, it won't free up the extents.
I'll go back under my rock now
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
one thing I did found out is that there are lobs used in one of the table and looking at Quest's Schema Manager I see that that Blob (that resides inside the tablespace) is taking about 2GB
So, I am guessing that the purge routine (written by webMethods application) is not cleaning the Lob on deletes.
Another possibility is that the purge is deleting the records but Lob is not shrinking; any way I can compress the LOB?
Thanks,
-Rajeev
Rajeev Suri
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|