OraFAQ Forum: Performance Tuning » What is the use of DBM STATS PACKAGE?

Home » RDBMS Server » Performance Tuning » What is the use of DBM STATS PACKAGE? (Oracle Database 10.2.0.1 on AIX 5.3)

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

What is the use of DBM STATS PACKAGE? [message #466852]

Wed, 21 July 2010 09:07

vcm643
Messages: 1
Registered: July 2010
Location: Philippines

Junior Member

Hi,

I'am a new DBA and I am new here in ORAFAQ, we are having a performance slowdown in one of our database. When there is a batch process, it is uses a lot of space in TEMP tablespace, but we can't find any errors. We would like to try gathering table statistics, my questions is, what is the purpose of gathering statistics? What benefits can it contribute to the performance of the database? Thanks.

Report message to a moderator

Re: What is the use of DBM STATS PACKAGE? [message #466853 is a reply to message #466852]

Wed, 21 July 2010 09:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

By default in V10+ statistics are gathered nightly so you do not need to duplicate this effort.

Report message to a moderator

Re: What is the use of DBM STATS PACKAGE? [message #466860 is a reply to message #466852]

Wed, 21 July 2010 09:23

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

Senior Member
Account Moderator

>>my questions is, what is the purpose of gathering statistics? What benefits can it contribute to the performance of the database?
Just like google/yahoo maps or GPS.
You need the best route from point A to point B.
If the current changes in roads are not updated to Maps, the software will
give the best route based on available data which may not be efficient at all.
In Oracle, CBO will use various factors to decide the best PLAN to fetch the records from database.
These factors are derived from the available statistics.
If the statistics are old/stale and underlying table/index data
has
changed a lot, the generated plan may not be the best one.

Report message to a moderator

Re: What is the use of DBM STATS PACKAGE? [message #466862 is a reply to message #466852]

Wed, 21 July 2010 09:25

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

http://www.lmgtfy.com/?q=oracle+dbms_stats

Regards
Michel

Report message to a moderator

Re: What is the use of DBM STATS PACKAGE? [message #467062 is a reply to message #466862]

Thu, 22 July 2010 03:16

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

I'm sorry when ask you: "How do you know or think about DBMS_STATS when Temp increasing?". I think, you should knew what caused the problem, and Google show you some cases, one of them was DBMS_STATS?

Report message to a moderator

Previous Topic:	Hint is not working properly
Next Topic:	optimised query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 04 01:48:33 CDT 2024