OraFAQ Forum: Performance Tuning » gather

Home » RDBMS Server » Performance Tuning » gather_statistics

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

gather_statistics [message #248763]

Sun, 01 July 2007 13:58

adee_saleh
Messages: 30
Registered: May 2007
Location: Yemen

Member

another question please
is there any difference between using DBMS_STATS package or using ANALYZE command as the following
begin DBMS_STATS.GATHER_TABLE_STATS(ownname=>ownname,tabname=>tabname,estimate_percent=>estimate_percent);
end;

or using the following

ANALYZE TABLE SCOTT.ORDERDETAILS COMPUTE STATISTICS;

because when i have a table that have like 2 million records doing and after i delete about 27% of its records, when i gather the statistics by using DBMS_STATS package i saw that the empty blocks=0.
but using ANALYZE command i get a value that should be there using DBMS_STATS package

also i tried to use GATHER_INDEX procedure in to collect statistics but nothing work even when i set estimate_percent=>100 the procedure executed but when i show statistics information from ALL_IND_STATISTICS it gives me nothing as never gatherd any statistics.
the point is when i use ANALYZE command the statistics is shown in the ALL_IND_STATISTICS

any help please?
with thanks

Report message to a moderator

Re: gather_statistics [message #248764 is a reply to message #248763]

Sun, 01 July 2007 14:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>any help please?
How can we help you when you don't provide enough details for a meaningful answer?
You did not read & FOLLOW ALL the posting guideline in the #1 STICKY post at the top of this forum.

You describe what you think is happening as opposed to using CUT & PASTE so we can see for ourselves.

We have NO way to reproduce your environment so can't test & provide a real solution for you.

You're On Your Own (YOYO)!

Report message to a moderator

Re: gather_statistics [message #248856 is a reply to message #248764]

Mon, 02 July 2007 08:28

adee_saleh
Messages: 30
Registered: May 2007
Location: Yemen

Member

i am sorry
have a table that have like 2 million records doing and after i delete about 27% of its records, when i gather the statistics by using DBMS_STATS package i saw that the empty blocks=0 and other statistics values=0.

i use the following code and it works and gives me a logical values

ANALYZE TABLE SCOTT.ORDERDETAILS COMPUTE STATISTICS;

but the following code doesn't

begin 
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'ORDERDETAILS',estimate_percent=>NULL);
end;

also i tried to use

GATHER_INDEX

procedure in to collect statistics but nothing work even when i set

estimate_percent=>100

the procedure executed but when i show statistics information from ALL_IND_STATISTICS it gives me nothing as never gathered any statistics.
the point is when i use ANALYZE command the statistics is shown in the ALL_IND_STATISTICS

any help please?
with thanks

Report message to a moderator

Re: gather_statistics [message #248879 is a reply to message #248856]

Mon, 02 July 2007 09:50

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Quote:

You collecting table stats not indexes stats.

SQL> drop table test
  2  purge;

Table dropped.

SQL> create table test
  2  as
  3  select * from v$session;

Table created.

SQL> insert into test
  2  select * from v$session;

25 rows created.

SQL> /

25 rows created.

SQL> /

25 rows created.

SQL> /

25 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
       125

SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,LAST_ANALYZED
  2  from dba_tables
  3  where table_name='TEST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANALYZED
---------- ---------- ------------ -------------------


SQL> alter table test compute statistics;
alter table test compute statistics
                                  *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


SQL> analyze table test compute statistics;

Table analyzed.

SQL>  select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,LAST_ANALYZED
  2   from dba_tables
  3   where table_name='TEST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANALYZED
---------- ---------- ------------ -------------------
       125         12            3 07-02-2007 14:39:37
SQL> select LEAF_BLOCKS,AVG_LEAF_BLOCKS_PER_KEY,NUM_ROWS,LAST_ANALYZED
  2  from dba_indexes
  3  where INDEX_NAME='TEST';

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY   NUM_ROWS LAST_ANALYZED
----------- ----------------------- ---------- -------------------
          1                       1        125 07-02-2007 14:46:43

SQL> delete from test
  2  where rownum<35;

34 rows deleted.

SQL> commit;

Commit complete.

SQL>  select LEAF_BLOCKS,AVG_LEAF_BLOCKS_PER_KEY,NUM_ROWS,LAST_ANALYZED
  2   from dba_indexes
  3   where INDEX_NAME='TEST';

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY   NUM_ROWS LAST_ANALYZED
----------- ----------------------- ---------- -------------------
          1                       1        125 07-02-2007 14:46:43

SQL> analyze index test compute statistics;

Index analyzed.

SQL>  select LEAF_BLOCKS,AVG_LEAF_BLOCKS_PER_KEY,NUM_ROWS,LAST_ANALYZED
  2   from dba_indexes
  3  where INDEX_NAME='TEST';

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY   NUM_ROWS LAST_ANALYZED
----------- ----------------------- ---------- -------------------
          1                       1        91 07-02-2007 14:49:10

[Updated on: Mon, 02 July 2007 09:51]

Report message to a moderator