Home » RDBMS Server » Performance Tuning » DBMS_STATS.gather_table_stats
DBMS_STATS.gather_table_stats [message #165796] Sat, 01 April 2006 21:54 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have a table which has 43M of records. It has 33 paritions by range on a date column, and has 8 bitmap indexes:

index1 has cardinality of 60000
index1 has cardinality of 5
index1 has cardinality of 60
index1 has cardinality of 580
index1 has cardinality of 800000
index1 has cardinality of 21
index1 has cardinality of 5
index1 has cardinality of 41000
index1 has cardinality of 150000

I used dbms_stats.SET_TABLE_STATS with num_rows => 43000000 and numblks => 230000.

I analyze it, using:

dbms_stats.gather_table_stats(ownname => user, tabname => v_table , estimate_percent => 100, 
method_opt => 'for all indexed columns size auto',
degree => 4 ,cascade => v_cascade );

the v_table = the tablename and v_cascade = true.

Problem is it's taking more than 2 hours to finish gathering stats. I don't know why, and how to resolve it.. Please advise..
thanks..

[Updated on: Mon, 21 January 2008 07:50] by Moderator

Report message to a moderator

Re: DBMS_STATS.gather_table_stats [message #165807 is a reply to message #165796] Sun, 02 April 2006 00:10 Go to previous messageGo to next message
Frank
Messages: 7878
Registered: March 2000
Senior Member
Quote:

estimate_percent => 100


What did you expect? It would analyze it within 10 minutes?
Re: DBMS_STATS.gather_table_stats [message #165811 is a reply to message #165807] Sun, 02 April 2006 01:31 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
No, I dont expect 10 minutes or even 30 - but more than 2 hours??! When I used 'analyze table <table> compute statistics', it just took 44 minutes.. But since dbms_stats is better, I've opted to use it..
Re: DBMS_STATS.gather_table_stats [message #165812 is a reply to message #165811] Sun, 02 April 2006 03:19 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I guess I'm comparing 'apples and oranges'. DBMS_STAT.gather_table_stat is different from ANALYZE table. The dbms_stat I used is actually like 'analyze table <table> estimate statistics FOR TABLE FOR ALL INDEXED COLUMNS
sample 100 percent'.. which I am yet to test...It should take longer to complete because it's not parallelized...
Re: DBMS_STATS.gather_table_stats [message #165817 is a reply to message #165812] Sun, 02 April 2006 05:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
parallelization may or may not help here.
Infact , in certain versions, even when you specify PARALLEL, Oracle will issue a hint NO_PARALLEL at runtime.
What is your version?
What is value for optimizer_features_enable?
Did you try with granularity?
granularity
Quoting Doc
Quote:


Granularity of statistics to collect (only pertinent if the table is partitioned).

DEFAULT: Gather global- and partition-level statistics.

SUBPARTITION: Gather subpartition-level statistics.

PARTITION: Gather partition-level statistics.

GLOBAL: Gather global statistics.

ALL: Gather all (subpartition, partition, and global) statistics.

And a bitmap index on column with 150000 cardinality?
Re: DBMS_STATS.gather_table_stats [message #165841 is a reply to message #165817] Mon, 03 April 2006 01:10 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
The version is 10g (10.2.0.1), the optimizer_features_enabled = 10.2.0.1.

I have not tried using granularity since I am ont really aware of its benefits...(yet Smile )

Regarding the bitmap index, the table has 4M records. So we made the index to bitmap since the column's cardinality is just 3.75% of the entire table.. is it that 'harmful'?
Re: DBMS_STATS.gather_table_stats [message #165870 is a reply to message #165841] Mon, 03 April 2006 02:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
In conventional wisdom, a bitmap index is good if the column has low distinct cardinality ( like a status column with YES or NO values or a Gender column with Male/Female). But there are more concerns here.
Please go through this ( all the three parts).
http://www.dbazine.com/oracle/or-articles/jlewis3
Re: DBMS_STATS.gather_table_stats [message #166023 is a reply to message #165870] Tue, 04 April 2006 02:04 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks Mahesh.. I would like to try out some few things to further understand Mr. Jlewis' explanation.

Going back to my original problem..., I have a table which I truncate, then repopulate and then collected with statistics (daily during load time). I use DBMS_STATS below to compute its stats:
dbms_stats.gather_table_stats(ownname => user, tabname => 'MYTAB' , estimate_percent => 100, method_opt => 'for all indexed columns size auto');

It has 42M rows, thus, it does take a very long time to complete. So, I am planning to use 'analyze' (below) instead:
analyze table MYTAB estimate statistics sample 100 percent for table for all indexed columns

it takes 15x faster.

Are they the same?? If not, how do I convert the DBMS_STATS above to 'ANALYZE table'? Is it at all possible? Why does DBMS_STATS take more time???...thanks in advance

[Updated on: Tue, 04 April 2006 02:05]

Report message to a moderator

Re: DBMS_STATS.gather_table_stats [message #166133 is a reply to message #166023] Tue, 04 April 2006 13:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Are they the same??
Your commands are NOT same.

I have two identical tables EMP and ANOTHER_EMP having identical indexes.
I will use your ANALYZE method on EMP
and your DBMS_STATS method on another_emp

You can see your ANALYZE method collect statistics only for the columns that are indexed But NOT for the indexes.
You are supposed to use

sql> analyze table emp estimate statistics sample 100 percent for table for all indexed columns for all indexes

DBMS_STATS will collect for all indexed columns and the indexes becuase of cascade=>true
Apart from these(and many more), you can see the allocated buckets will differ and DBMS_STATS is the Preferred statistics collection method for CBO.

If you are using 10g, there is lot of changes to be considered.

scott@9i >  analyze table emp estimate statistics sample 100 percent for table for all indexed columns;

Table analyzed.

scott@9i > exec dbms_stats.gather_table_stats('SCOTT','ANOTHER_EMP',ESTIMATE_PERCENT=>100,METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);

PL/SQL procedure successfully completed

scott@9i > select table_name,column_name,num_distinct,last_analyzed,num_buckets from user_tab_cols where table_name like ('%EMP%');

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LAST_ANAL NUM_BUCKETS
------------------------------ ------------------------------ ------------ --------- -----------
ANOTHER_EMP                    EMPNO                                    14 04-APR-06           1
ANOTHER_EMP                    ENAME
ANOTHER_EMP                    JOB                                       5 04-APR-06           1
ANOTHER_EMP                    MGR
ANOTHER_EMP                    HIREDATE
ANOTHER_EMP                    SAL
ANOTHER_EMP                    COMM
ANOTHER_EMP                    DEPTNO
EMP                            EMPNO                                    14 04-APR-06          13
EMP                            ENAME
EMP                            JOB                                       5 04-APR-06           4
EMP                            MGR
EMP                            HIREDATE
EMP                            SAL
EMP                            COMM
EMP                            DEPTNO

16 rows selected.

scott@9i > select table_name,index_name,num_rows,last_analyzed from user_indexes;

TABLE_NAME                     INDEX_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ------------------------------ ---------- ---------
ANOTHER_EMP                    ANOTHER_EMP_EMPNO                  458752 04-APR-06
ANOTHER_EMP                    ANOTHER_EMP_JOB                    458296 04-APR-06
EMP                            EMP_EMPNO
EMP                            EMP_JOB

I will try to test more on partitioned tables.
Regards.
Re: DBMS_STATS.gather_table_stats [message #166317 is a reply to message #166133] Wed, 05 April 2006 13:04 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks, Mahesh..
I used to look into the USER_TABLES, USER_TAB_STATISTICS and USER_IND_STATISTICS only - I never really thought that I should also check on the USER_TAB_COLS. I will read on NUM_BUCKETS - I think this will help me understand HISTOGRAMS. thanks again!
Re: DBMS_STATS.gather_table_stats [message #166319 is a reply to message #166317] Wed, 05 April 2006 13:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
and user_tab_histograms
Re: DBMS_STATS.gather_table_stats [message #295137 is a reply to message #166133] Mon, 21 January 2008 07:47 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member

Mahesh,
<<If you are using 10g, there is lot of changes to be considered.>>

Can you please detail out as to what changes need to be considered on 10g Environment or can you please point me any documents or links.

Thanks in advance.
Regards
Ravi
Re: DBMS_STATS.gather_table_stats [message #295247 is a reply to message #295137] Mon, 21 January 2008 20:53 Go to previous message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The quoted statement of mine was said for the issue posted by the OP.
What is your case?
Previous Topic: Newbie: SQL runs slow on 10g, not on 9i
Next Topic: From Where should i want to start for tuning sqls
Goto Forum:
  


Current Time: Thu Oct 30 07:38:55 CDT 2014

Total time taken to generate the page: 0.11841 seconds