Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_STATS package (Oracle 11.1.0.6.0)
DBMS_STATS package [message #365203] Thu, 11 December 2008 01:42 Go to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
HI,

I am trying to use dbms_stats.gather_table_stats to collect the statistics for a partitioned table. It takes more than 30 minutes to complete. However same amount of data in Oracle 9.2.0.6.0 is taking only 2-3 minute.

dbms_stats.GATHER_TABLE_STATS (OWNNAME=> 'MYDBA'
,tabname=>'TAB1', PARTNAME=>'PART1', estimate_percent=>10);

Is there any parameter which is causes this time delay?

Thanks in advance,
GS
Re: DBMS_STATS package [message #365211 is a reply to message #365203] Thu, 11 December 2008 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the other parameters of the procedure and what are the default values, they change with the version.

Regards
Michel
Re: DBMS_STATS package [message #365319 is a reply to message #365211] Thu, 11 December 2008 07:46 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
HI,

INCREMENTAL value for the table is set to TRUE. However still even if I analyze a particular partition, it always analyzes all the available partitions.

Is there any other parameter to be set to analyze only the requested partition?

Thanks in advance,
GS
Re: DBMS_STATS package [message #365322 is a reply to message #365319] Thu, 11 December 2008 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Argument               Type                
---------------------- --------------------
OWNNAME                VARCHAR2            
TABNAME                VARCHAR2            
PARTNAME               VARCHAR2            
ESTIMATE_PERCENT       NUMBER              
BLOCK_SAMPLE           PL/SQL BOOLEAN      
METHOD_OPT             VARCHAR2            
DEGREE                 NUMBER              
GRANULARITY            VARCHAR2            
CASCADE                PL/SQL BOOLEAN      
STATTAB                VARCHAR2            
STATID                 VARCHAR2            
STATOWN                VARCHAR2            
NO_INVALIDATE          PL/SQL BOOLEAN      
STATTYPE               VARCHAR2            
FORCE                  PL/SQL BOOLEAN      

All these ones.

Regards
Michel
Re: DBMS_STATS package [message #365325 is a reply to message #365203] Thu, 11 December 2008 08:14 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
try this hope it helps

dbms_stats.gather_table_stats( ownname=>'',tabname=>'',estimate_percent=>100,degree=>8);
Re: DBMS_STATS package [message #365326 is a reply to message #365325] Thu, 11 December 2008 08:16 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ravi214u wrote on Thu, 11 December 2008 15:14
try this hope it helps

dbms_stats.gather_table_stats( ownname=>'',tabname=>'',estimate_percent=>100,degree=>8);


Why?

Regards
Michel

Previous Topic: Query (merged 3)
Next Topic: SQL Problem
Goto Forum:
  


Current Time: Sun Dec 04 04:18:35 CST 2016

Total time taken to generate the page: 0.14800 seconds