Home » RDBMS Server » Performance Tuning » DBA_SEGMENTS: Criteria for a DB/Table export
DBA_SEGMENTS: Criteria for a DB/Table export [message #218746] Fri, 09 February 2007 15:13 Go to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

Can someone shed some light on the following:
Queriying DBA_SEGMENT I found that there are some tables/indexes, whose EXTENTS are well into hundreds. Is there a rule, or a certain value, at which, it is recommended exporting either a database or tables/indexes, to basically reset that number?
We are seeing some weird performance on our Oracle 9i, prod database, and are currently trying to certain ora.init params, but so far to no success.

Thank you.
Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218748 is a reply to message #218746] Fri, 09 February 2007 15:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>and are currently trying to certain ora.init params, but so far to no success.
Application tuning based upon the principal of READY, FIRE, AIM.
You have no idea what exactly is wrong, but are hoping to get lucky by guessing semi-random parameters to change.

So exactly why do you think that reducing the number of EXTENTS will improve performance?

I suggest you get a copy of Optimizing Oracle Performance (ISBN - 0-596-00527) & take a scientific approach to optimization.
Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218749 is a reply to message #218746] Fri, 09 February 2007 15:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What is your version?
If you are using LMT, number of extents does not matter (unless we are talking about a really large number, say hundreds and thousands of it. Still it its has less to do with performance).

>> it is recommended exporting either a database or tables/indexes, to basically reset that number
Widely propogated myth.
What really matters is sizing the extents properly.
Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218751 is a reply to message #218748] Fri, 09 February 2007 15:51 Go to previous messageGo to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

Thank you for your reply.
Fortunatelly we are not in a :READY, FIRE,AIM
We have done our analysis, and research. We have also bench-marked our most intensive processes.
Over-all we have seen significant improvement in the performance, however there are a hand-full of processes, where performance has deteriorated.
We have Oracle 9I, and I was googling for dba_segments. I found an article by someone in GB, who suggested exporting tables/indexes when extents are over 10. I could not find anything else related to this.
Since we switched from Oracle 8 to Oracle 9i, this particular process, degraded from about 30 mins to over 3 hours.
I have reviewed each and every SELECT, INSERT, UPDATE. The highest cost I have seen is 40. The longest amount of time, any select would run was about 40 seconds (and it only gets executed ones). I could attribute the total run-time to the amount of data, however, I don't see a 5 fold increase fo data.

When does Oracle recommend exporting either entire database, or individual objects, to eliminate defragmentation?
Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218759 is a reply to message #218751] Fri, 09 February 2007 18:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> When does Oracle recommend exporting either entire database, or individual objects, to eliminate defragmentation?
As we said before, How are you sure that your database is fragmented and it is causing the this performance drop?
If you do a re-org to elimiate fragmentation, you have to do that too frequently, all your life.
In 9i and above, you are supposed to use LMT with uniform extent size. That will ***almost*** fix all fragementation ( still you will have to deal with inter-block fragmentation. Using PCTUSED/PCTFREE is the only method to fix).
>>who suggested exporting tables/indexes when extents are over 10
Wrong. Myth. Waste of time and effort.
Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218760 is a reply to message #218759] Fri, 09 February 2007 18:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Meanwhile, when was the last time you updated statistics? And how?
Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #219728 is a reply to message #218760] Thu, 15 February 2007 13:26 Go to previous messageGo to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

Stats are being gathered weekly (stale stats)
Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #219851 is a reply to message #219728] Fri, 16 February 2007 06:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
On the subject of Stats, it's worth checking that you have stats on all your tables - Gather Stale won't gather stats on tables that don't have any stats to start with.

{correct typo}

[Updated on: Fri, 16 February 2007 06:27]

Report message to a moderator

Previous Topic: Power Builder Data window linkage performance
Next Topic: transaction
Goto Forum:
  


Current Time: Thu Dec 08 22:26:45 CST 2016

Total time taken to generate the page: 0.13469 seconds