OraFAQ Forum: Performance Tuning » DBA_SEGMENTS: Criteria for a DB/Table export

Home » RDBMS Server » Performance Tuning » DBA_SEGMENTS: Criteria for a DB/Table export

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBA_SEGMENTS: Criteria for a DB/Table export [message #218746]

Fri, 09 February 2007 15:13

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.

Report message to a moderator

Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218748 is a reply to message #218746]

Fri, 09 February 2007 15:38

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218749 is a reply to message #218746]

Fri, 09 February 2007 15:39

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218751 is a reply to message #218748]

Fri, 09 February 2007 15:51

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?

Report message to a moderator

Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218759 is a reply to message #218751]

Fri, 09 February 2007 18:23

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #218760 is a reply to message #218759]

Fri, 09 February 2007 18:24

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Meanwhile, when was the last time you updated statistics? And how?

Report message to a moderator

Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #219728 is a reply to message #218760]

Thu, 15 February 2007 13:26

kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA

Junior Member

Stats are being gathered weekly (stale stats)

Report message to a moderator

Re: DBA_SEGMENTS: Criteria for a DB/Table export [message #219851 is a reply to message #219728]

Fri, 16 February 2007 06:26

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 11:30:06 CST 2024