Avatar of dbadm
dbadmFlag for Italy asked on

GATHER_TABLE_STATS different elapsed

Hi,
I'd like to calculate the statistics on the tables TOM.TAB_DETTPP (Size = 450Gb including indexes and Lobs) and TOM.TAB_DETTCC (Size = 380Gb including indexes and Lobs)

I run the following GATHER_TABLE_STATS commands:


execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'TOM', TABNAME =>'TAB_DETTPP', ESTIMATE_PERCENT => 10, degree => 6, CASCADE => TRUE);

When I run this command on TOM.TAB_DETTPP (size 450G) it takes 1 hour

Open in new window


execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'TOM', TABNAME =>'TAB_DETTCC', ESTIMATE_PERCENT => 10, degree => 6, CASCADE => TRUE);

When I run this command on TOM.TAB_DETTCC (size 380G) it takes 6 hours

Open in new window


Please give me some tips on how to figure out why a 450G table takes so much less elapsed than a 380G table?


Regards


Oracle DatabaseSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
slightwv (䄆 Netminder)

Does the smaller table have more indexes?

Guess here but take a look at this in the docs:
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-CA6A56B9-0540-45E9-B1D7-D78769B7714C

cascade
      

Determines whether to collect index statistics as part of gathering table statistics.
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dbadm

Below details for the tables and indexes


desc TOM.TAB_DETTPP 


Name               Null?   TYPE        
------------------ -------- ------------
COD_A             NOT NULL VARCHAR2(20)
COD_B             NOT NULL VARCHAR2(18)
COD_C             NOT NULL VARCHAR2(5)  
NUM_A             NOT NULL NUMBER(38)  
COD_D             NOT NULL VARCHAR2(18)
COD_G             NOT NULL VARCHAR2(50)
COD_P             NOT NULL VARCHAR2(50)
NUM_S             NOT NULL NUMBER(38)  
COD_E             NOT NULL VARCHAR2(5)  
COD_F             NOT NULL VARCHAR2(18)
COD_G             NOT NULL VARCHAR2(18)
COD_T             NOT NULL VARCHAR2(20)
COD_Y             NOT NULL VARCHAR2(20)
COD_Z             NOT NULL VARCHAR2(18)
TYPE_Z            NOT NULL VARCHAR2(5)  
VAL                        VARCHAR2(20)

Open in new window


desc TOM.TAB_DETTCC


Name               Null?   TYPE          
------------------ -------- -------------
COD_A            NOT NULL VARCHAR2(20)  
COD_B            NOT NULL VARCHAR2(18)  
COD_C            NOT NULL VARCHAR2(5)  
NUM_A            NOT NULL NUMBER(38)    
COD_D            NOT NULL VARCHAR2(5)  
NUM_B            NOT NULL NUMBER(38)    
COD_E            NOT NULL VARCHAR2(50)  
COD_F NOT        NULL     VARCHAR2(18)  
TYPE_A           NOT NULL VARCHAR2(5)  
VAL                       VARCHAR2(255)

Open in new window



TAB_DETTCC        TABLE   size = 185G
PKX_DETTCC_01     INDEX   size = 69G
PKX_DETTCC         INDEX   size = 128G


TAB_DETTPP   TABLE   size = 283G
PKX_DETTPP   INDEX   size = 168G


I modified GATHER_TABLE_STATS command in:

execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'TOM', TABNAME =>'TAB_DETTPP', ESTIMATE_PERCENT => 10, No_Invalidate => FALSE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', degree => 6, CASCADE => TRUE);

Open in new window

execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'TOM', TABNAME =>'TAB_DETTCC', ESTIMATE_PERCENT => 10, No_Invalidate => FALSE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', degree => 6, CASCADE => TRUE);

Open in new window


I've added the following parameters:
No_Invalidate => FALSE
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'

In this case 450G table takes 60 minutes and 380G table takes 54 minutes.

Have you any idea? 
slightwv (䄆 Netminder)

Idea's about what?  In the grand scheme of things, 450 and 380 are 'similar' in size so I would expect the times to be about the same as well.

Are you expecting the times to be linear in that if one table is 20% smaller, gathering stats will be 20% faster?

Could be memory, could be disk, could be competing with other user processes could be ???

If you want a detailed explanation, I would probably open an SR with Oracle Support.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy