Home » SQL & PL/SQL » SQL & PL/SQL » Running dbms_stats is taking very long time
Running dbms_stats is taking very long time [message #191672] Thu, 07 September 2006 12:47 Go to next message
baskarb_2006
Messages: 5
Registered: September 2006
Location: chennai
Junior Member

I am analying the table using the following dbms_stat package. It took 16 minutes

exec dbms_stats.gather_table_stats(ownname=>upper('bas'),tabname=>upper('t'),cascade=>true,no_invalidate=>true,estimate_percent=>1,method_opt= >'FOR ALL INDEXED COLUMNS',granularity=>'all');

where as if analyze the table using following analyze command

analyze table t estimate statistics sample 10 percent for table for all indexes for all indexed columns

It took only 7 minutes.

Pl.note that table 't' has record count of 7704997. we are using oracle 9.2.0.6.

Pl. anyone tell me how to improve the speed of dbms_stat package for the above method.

Regards,
B.Baskar

Re: Running dbms_stats is taking very long time [message #191676 is a reply to message #191672] Thu, 07 September 2006 13:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
obviously, I misread. Apologies.

Is a partitioned table?
Did you try dbms_stats with a proper bucket size?

[Updated on: Thu, 07 September 2006 13:10]

Report message to a moderator

Re: Running dbms_stats is taking very long time [message #192249 is a reply to message #191676] Mon, 11 September 2006 09:56 Go to previous messageGo to next message
baskarb_2006
Messages: 5
Registered: September 2006
Location: chennai
Junior Member

Hi,

Sorry for my delayed reply. Most of our table are partitioned.

This is the first time i am using this dbms_stat package. pl. suggest me the proper bucket size.

Regards,
B.Baskar
Re: Running dbms_stats is taking very long time [message #192258 is a reply to message #192249] Mon, 11 September 2006 10:24 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Expected behaviour(in 9i. Last time i checked this, in 10gR2 this must have been fixed).
As said before, dbms_stats does a lot more work than analyze.
The major difference is dbms_stats will collect statistcs for individual partitions and the global statistics for the whole table.
Analayze will consider only the individual partition and derives the global stats from it.
Look into Oracle documentation.
Make use of the PARTNAME feature AND collect the stats in parallel.
http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats.htm#999107.

Regarding bucket size, you have to come up with one based on your data distribution.
Previous Topic: Finding Tables with similar columns
Next Topic: ORA-00911 when trying to insert user name and sysdate
Goto Forum:
  


Current Time: Mon Dec 05 06:39:40 CST 2016

Total time taken to generate the page: 0.11685 seconds