|
|
|
|
| Re: Oracle Fragmentation script [message #466247 is a reply to message #466240] |
Sun, 18 July 2010 10:03   |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi,
We have an OLTP SYSTEM runing on oracle 10g database, We are facing some performance issue, may be because of some fragmentation, so I need a sql script which will help me to identify those objects which are fragmented.
Thanks
Prashant
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle Fragmentation script [message #466258 is a reply to message #466254] |
Sun, 18 July 2010 11:01 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
See we have an OLTP system and there are nos of delete and update happens daily on the tables and as a result our table became fragmented , so now i have to identify those table and re-organize them.So if u have any scripts then pl provide me.
|
|
|
|
|
|
| Re: Oracle Fragmentation script [message #466263 is a reply to message #466262] |
Sun, 18 July 2010 11:40 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
In 1997 (?) I think, there was a famous white paper called "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation". I'll attach a copy, just so anyone who thinks "fragmentation" is an issue can see how out-dated it is. All copies have been removed from Oracle Corp websites long ago, because the whole concept is meaningless since 8.1 introduced locally managed tablespaces.
@OP, in this paper you will find the queries you want, and I hope you also see that none of it is relevant today.
[edit: file uploaded]
-
Attachment: fragment.pdf
(Size: 62.44KB, Downloaded 2833 times)
[Updated on: Sun, 18 July 2010 11:41] Report message to a moderator |
|
|
|
| Re: Oracle Fragmentation script [message #466265 is a reply to message #466263] |
Sun, 18 July 2010 11:55 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Thanks John.
I found this query which can tell me the segment name which requires some action:
select segment_owner,segment_name,ALLOCATED_SPACE/1024/1024 alloc_mb,
USED_SPACE/1024/1024 used_mb,RECLAIMABLE_SPACE/1024/1024 rclm_mb
,round(100*RECLAIMABLE_SPACE/ALLOCATED_SPACE,4)recl_pct ,CHAIN_ROWEXCESS,c1,c2,c3
from table(dbms_space.asa_recommendations())
--rclm_pct will be a command line parameter, Using 25% as the default
where 100*RECLAIMABLE_SPACE/ALLOCATED_SPACE>10
--segment_name like 'ROW%'
order by recl_pct desc;
|
|
|
|
|
|
| Re: Oracle Fragmentation script [message #466267 is a reply to message #466266] |
Sun, 18 July 2010 12:14 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
I do not think I posted wrong question, even I posted the same with oracle as they are technology partner with our orgnisation and they are going to provide end to end solution for fragmentation and they also conveyed the message that yes our database have some fragmentation issues.
|
|
|
|
|
|
|
|
|
|
| Re: Oracle Fragmentation script [message #466278 is a reply to message #466265] |
Sun, 18 July 2010 13:21 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Oh no! I did not want this reply:
prashant_ora wrote on Sun, 18 July 2010 11:55Thanks John.
I found this query which can tell me the segment name which requires some action:
I posted that article, hoping it would be a polite way of demontrating that the whole concept of fragmentation is totally meaningless now. Any DBA who tells you differently, is showing that his knowledge of Oracle is 4 major releases out of date.
I'll stop trying to help! Over-and-out.
|
|
|
|
|
|
|
|
| Re: Oracle Fragmentation script [message #466300 is a reply to message #466289] |
Sun, 18 July 2010 22:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Some people with an imperfect understanding of databases will sometimes lump a number of issues (incorrectly) under the definition of "fragmentation". Since one of the impacts of fragmentation under Dictionary Managed Tablespaces was to under-utilise disk space, other unrelated issues that also under-utilise disk - such a HWM and the effect of deletions on b-tree indexes with sequentially allocated keys - might incorrectly be described as fragmented.
Here's something I wrote a while ago to identify indexes where updates and deletes have left recoverable space. I have used the term "Fragmented" a little loosely, certainly not to be confused with the fragmentation issue inherent in Dictionary Managed Tablespaces.
Ross Leishman
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
