Skip to Main Content
  • Questions

  • DBMS_STATS.GATHER_TABLE_STATS Gives wrong row count (NUM_ROWS column in user_tables)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ramesh.

Asked: June 11, 2007 - 11:12 am UTC

Last updated: August 30, 2016 - 2:47 am UTC

Version: 10.2.0.3

Viewed 50K+ times! This question is

You Asked

Tom, I am a big fan of yours and you are awesome. Here is something I observed today. I always thought analyzing table will populate number of rows in user_tables.num_rows column.

I have a table with 204,913 records. When I do a select count(1) from table I get the same result. However, when I analyzed using dbms_stats, I get wrong row count.
BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'MK2'
     ,TabName        => 'MIG_CASENAME'
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE);
END;


Everytime, I run the above code, I get wrong rowcount in user_tables.num_rows column. My results were like 205,940 , 204,680 etc

When I used Analyze command ,I get the exact row count populated.
Analyze Table MK2.MIG_CASENAME


Can you explain why.
Thanks
Ramesh

and Tom said...

dbms_stats estimates in 10g by default.

I would never expect the num_rows to be exact.

the numbers are closer than close enough.

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ramesh, June 12, 2007 - 3:38 pm UTC

Thanks for the answer.

Is it really needed to anylyze the table

Banerjee, March 25, 2011 - 12:47 pm UTC

Is it really needed to analyze table. In our production more than 500 tables are present. In the test db (contains very small amount of data) if we analyze all the table (10% only) then it takes almost 2 hour to complete the task.

We can schedule the job in the weekly basis. but the question is it really going to increase the performance. And i heard it from my dba friend that while analyzing the table you should include index also. is it needed.
Tom Kyte
March 25, 2011 - 4:47 pm UTC

the only answer is:

it depends.


how frequently does the data change?

does the data change in such a way that the statistics don't really change? (eg: you delete 1,000,000 rows, but you insert 1,000,000 similar rows - the statistics will not change too much)



Think about what statistics are used for - to estimate cardinalities. If the stats you have "are good enough" to get a good estimate, they are fine.

Typically - the times you need to frequently regather stats would be:

o tables that grow and grow and grow and grow. In 11g with incremental statistics - partitioning would be the answer, there you only have to gather stats on the new partitions - never the old.

o columns that increase in value (like sequence populated columns or sysdate populated columms) - but the rest of the table doesn't really change size very much


In the latter case, you can use dbms_stats.set_**** routines to TELL US what the stats are - you don't need to gather.



As far as making stats gathering faster you have:

o get to 11g - totally rewritten and faster than before

o use parallel

o sample instead of compute.


I am assuming that when you said analyze you really truly meant "dbms_stats"

Update Stats along with DML

koshal patibandla, March 28, 2011 - 10:30 am UTC

I think it would be nice if oracle could provide an option to user to Update the stats after DML. Something like this

Insert into Target select * from source UPDATE Stats.

Here you know what the data you are going to insert and with minimum of work you would be able Update the stats.
As the data is already in the cache querying would be faster.

Similar for delete and merge.

good point

A reader, March 28, 2011 - 3:09 pm UTC

Kaushal - refreshing wish , but what about a databse with high number of concurrent users and too many users inserting/updating/deleteing data from the same table...and then using the "the feature you wish" the start updating the stats..kind of all dressed up and no where to go...but would like to have Tom's opinion on this.

thanks any way - as it is hard to hear something even this innovative nowdays :)
Tom Kyte
March 29, 2011 - 3:46 am UTC

it would be useful in a single limited case:

data warehouse load.


and that is about it. We do it for indexes in a fashion (if you create or rebuild an index in 10g and up - we COMPUTE statistics for it). It still works at the segment level and probably always will - at least for the foreseeable future. I could see the use of a create table as select that would compute statistics as it loaded, or a direct path insert into an empty segment. doesn't happen yet, but I could see that being useful.

Pending Statistics in 11GR2

Rajeshwaran, Jeyabal, April 09, 2011 - 5:46 pm UTC

Tom:

I was reading about Pending statistics from Oracle docs

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#BEIEGBGI 

rajesh@11GR2> create table t
  2  nologging
  3  as
  4  select a.*,
  5      case when rownum <10 then rownum else 99 end as id
  6  from all_objects a;

Table created.

Elapsed: 00:00:05.86
rajesh@11GR2>
rajesh@11GR2> create index t_ind on t(id) nologging;

Index created.

Elapsed: 00:00:00.31
rajesh@11GR2>
rajesh@11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname => user,
  4     tabname => 'T',
  5     estimate_percent => dbms_stats.auto_sample_size,
  6     method_opt => 'for all indexed columns size 1');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
rajesh@11GR2>
rajesh@11GR2> select column_name,num_distinct,low_value,high_value,density
  2  from user_tab_col_statistics
  3  where table_name ='T';

COLUMN_NAME                    NUM_DISTINCT LOW_VALUE                      HIGH_VALUE                  DENSITY
------------------------------ ------------ ------------------------------ ------------------------------ -------
ID                                       10 C102                           C164                         0

Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2> select table_name, column_name,endpoint_number,endpoint_value
  2  from user_tab_histograms
  3  where table_name ='T';

TABLE_NAME                     COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------------------------ --------------- --------------
T                              ID                                           0              1
T                              ID                                           1             99

Elapsed: 00:00:00.01
rajesh@11GR2>
rajesh@11GR2> begin
  2    dbms_stats.set_table_prefs(ownname=>user,
  3        tabname =>'T',
  4        pname =>'PUBLISH',
  5        pvalue =>'FALSE');
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> begin
  2   dbms_stats.gather_table_stats(
  3      ownname=>user,
  4      tabname =>'T',
  5      estimate_percent=>100,
  6      method_opt=>'for all indexed columns size 254');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
rajesh@11GR2>
rajesh@11GR2> select num_rows,blocks,avg_row_len,sample_size,last_analyzed
  2  from user_tab_pending_stats
  3  where table_name ='T';

  NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
---------- ---------- ----------- ----------- ---------
     71478       1083         100       71478 09-APR-11

Elapsed: 00:00:00.01
rajesh@11GR2>
rajesh@11GR2> SELECT column_name,endpoint_number,endpoint_value
  2  from user_tab_histgrm_pending_stats
  3  where table_name ='T';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ --------------- --------------
ID                                           1              1
ID                                           2              2
ID                                           3              3
ID                                           4              4
ID                                           5              5
ID                                           6              6
ID                                           7              7
ID                                           8              8
ID                                           9              9
ID                                       71478             99

10 rows selected.

Elapsed: 00:00:00.09
rajesh@11GR2>
rajesh@11GR2> select column_name,num_distinct,low_value,high_value,density
  2  from user_tab_col_statistics
  3  where table_name ='T';

COLUMN_NAME                    NUM_DISTINCT LOW_VALUE                      HIGH_VALUE                  DENSITY
------------------------------ ------------ ------------------------------ ------------------------------ -------
ID                                       10 C102                           C164                         0

Elapsed: 00:00:00.01
rajesh@11GR2>
rajesh@11GR2> select table_name, column_name,endpoint_number,endpoint_value
  2  from user_tab_histograms
  3  where table_name ='T';

TABLE_NAME                     COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------------------------ --------------- --------------
T                              ID                                           1              1
T                              ID                                           2              2
T                              ID                                           3              3
T                              ID                                           4              4
T                              ID                                           5              5
T                              ID                                           6              6
T                              ID                                           7              7
T                              ID                                           8              8
T                              ID                                           9              9
T                              ID                                       71478             99

10 rows selected.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> select dbms_stats.get_prefs('PUBLISH',USER,'T') as get_params
  2  from dual;

GET_PARAMS
------------------------------
FALSE

Elapsed: 00:00:00.03
rajesh@11GR2>


Questions:

1) I made the PUBLISH parameter as FALSE by calling dbms_stats.set_table_prefs. When i regather stats the Pending stats got updated in user_tab_pending_stats & user_tab_histgrm_pending_stats fine.

But why it got updated in user_tab_histograms ?


Tom Kyte
April 12, 2011 - 3:57 pm UTC

that looks like a problem, I have a small test case and will pursue it.

Pending Statistics in 11GR2

Rajeshwaran, Jeyabal, April 13, 2011 - 12:59 pm UTC

Tom:

Can you please post your script here?
Tom Kyte
April 13, 2011 - 1:54 pm UTC

why, you have a test case of your own right above, just use that.

Pending Statistics in 11GR2

Rajeshwaran, Jeyabal, April 13, 2011 - 2:33 pm UTC

Tom:

As you see above, my Test script doesn't work proper.

Even if i set PUBLISH=FALSE pending statistics got published to user_tab_histograms.

you said that you have a small test case and will pursue it. So i asked you to publish your test case.

Tom Kyte
April 13, 2011 - 2:52 pm UTC

I said basically that the behavior you are observing seems wrong and that I have a small test case that reproduces it so I can file a bug.

Pending Statistics in 11GR2

Rajeshwaran, Jeyabal, April 13, 2011 - 4:30 pm UTC

Tom:

You have to pay me, since i founded this bug for you :)

Pending Statistics in 11GR2

Rajeshwaran, Jeyabal, April 14, 2011 - 11:38 am UTC

Tom:

This Pending statistics stuff work's properly in Index absence. However with Index in place Statistics still remains good, Only Histograms becomes problem

Table Without index - Things goes fine ( Statistics + Histograms )

rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t(
  2     x ,
  3     y
  4  )
  5  as
  6  select rownum, mod(rownum,5)
  7  from dual
  8  connect by level <= 10000;

Table created. 

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     cascade => true,
  7     method_opt=>'for all columns size 1');
  8  end;
  9  /

PL/SQL procedure successfully completed. 

Elapsed: 00:00:00.28
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'; 

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:35:30 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name; 

COLUMN_NAM COUNT(*)
---------- --------
Y                 2
X                 2 

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs(
  3     ownname => user,
  4     tabname => 'T',
  5     pname => 'PUBLISH',
  6     pvalue => 'FALSE');
  7  end;
  8  /

PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> 100,
  6     cascade => true,
  7     method_opt=>'for all columns size 254');
  8  end;
  9  / 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:00.32
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,last_analyzed
  2  from user_tab_pending_stats
  3  where table_name ='T'; 

TABLE_NAME                     LAST_ANALYZED
------------------------------ -----------------------
T                              14-apr-2011 12:36:38 am

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histgrm_pending_stats
  3  where table_name ='T'
  4  group by column_name; 

COLUMN_NAM COUNT(*)
---------- --------
Y                 5
X               255 

Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'; 

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:35:30 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name; 

COLUMN_NAM COUNT(*)
---------- --------
Y                 2
X                 2

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>


Table With Index ( Statistics goes fine but Histograms Fails )

rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t(
  2     x ,
  3     y
  4  )
  5  as
  6  select rownum, mod(rownum,5)
  7  from dual
  8  connect by level <= 10000; 

Table created. 

Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> create index t_ind on t(y); 

Index created. 

Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     cascade => true,
  7     method_opt=>'for all indexed columns size 1');
  8  end;
  9  / 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:00.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'; 

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:41:14 am 

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name; 

COLUMN_NAM COUNT(*)
---------- --------
Y                 2

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs(
  3     ownname => user,
  4     tabname => 'T',
  5     pname => 'PUBLISH',
  6     pvalue => 'FALSE');
  7  end;
  8  / 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> 100,
  6     cascade => true,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  / 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,last_analyzed
  2  from user_tab_pending_stats
  3  where table_name ='T'; 

TABLE_NAME                     LAST_ANALYZED
------------------------------ -----------------------
T                              14-apr-2011 12:41:42 am 

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histgrm_pending_stats
  3  where table_name ='T'
  4  group by column_name; 

COLUMN_NAM COUNT(*)
---------- --------
Y                 5 

Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'; 

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:41:14 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 5

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>

Pended stats on 11.2.0.4

Rajeshwaran Jeyabal, August 23, 2016 - 12:25 pm UTC

Team,

Today we got an production issue, due to Incorrect stats in place, was trying to show my DBA that the issue is due to Incorrect stats in production and not related to SQL.

so thought of using pended stats feature to show the difference in plan, but unfortunately it doesn't work in 11.2.0.4

Could you please, correct me if i am wrong.

http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94751

See the cardinality in plans doesn't change when "optimizer_use_pending_statistics" set to TRUE.

demo@ORA11G>
demo@ORA11G> create table t
  2  partition by range(x)(
  3     partition p1 values less than (2),
  4     partition p2 values less than (3),
  5     partition p3 values less than (4),
  6     partition p4 values less than (5),
  7     partition p5 values less than (6) )
  8  as
  9  select a.*, mod(rownum,5)+1 x
 10  from all_objects a
 11  where rownum <=10;

Table created.

demo@ORA11G>
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA11G>
demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |   154 |    11   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |   154 |    11   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    |     2 |   154 |    11   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=2)

demo@ORA11G> set autotrace off
demo@ORA11G>
demo@ORA11G> insert into t
  2  select a.*, 2 x
  3  from all_objects a;

84869 rows created.

demo@ORA11G> commit;

Commit complete.

demo@ORA11G>
demo@ORA11G> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
----------------------------------------------------------------------------------------------------
TRUE

1 row selected.

demo@ORA11G>
demo@ORA11G> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

demo@ORA11G> show parameter optimizer_use_pend

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
optimizer_use_pending_statistics              boolean     FALSE
demo@ORA11G> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
----------------------------------------------------------------------------------------------------
FALSE

1 row selected.

demo@ORA11G>
demo@ORA11G> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANALYZ
---------- ---------- -----------
         2         19 23-AUG-2016

1 row selected.

demo@ORA11G>
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T',partname=>'P2');

PL/SQL procedure successfully completed.

demo@ORA11G>
demo@ORA11G> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANALYZ
---------- ---------- -----------
         2         19 23-AUG-2016

1 row selected.

demo@ORA11G>
demo@ORA11G> select num_rows,blocks
  2  from user_tab_pending_stats
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS
---------- ----------
     84871       2032

1 row selected.

demo@ORA11G> alter session set optimizer_use_pending_statistics=true;

Session altered.

demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |   154 |    11   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |   154 |    11   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    |     2 |   154 |    11   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=2)

demo@ORA11G> set autotrace off
demo@ORA11G>
demo@ORA11G>

Chris Saxon
August 23, 2016 - 1:38 pm UTC

I get the same results as you. It's not picking up pending stats on the partition. So it looks like you're going to have to take this up with support.

Chris

Pended stats on 11.2.0.4

Rajeshwaran Jeyabal, August 23, 2016 - 1:47 pm UTC

Thanks Chris.

Sorry, i don't have access to support, and my DBA team won't help me on this.

Could you please file a bug with support on this - using this test case? this behavior is still reproducible in 12c(12.1.0.2) too.
Connor McDonald
August 30, 2016 - 2:22 am UTC

This is a variant of Bug 21170718.

I've reproduced on 11.2.0.4 and 12.1.0.2.

And its fixed in 12.2.


Pended stats on 11.2.0.4

Rajeshwaran Jeyabal, August 30, 2016 - 2:31 am UTC

Thank you.
Connor McDonald
August 30, 2016 - 2:47 am UTC


You could always chat to Support about a backport.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here