Home » RDBMS Server » Performance Tuning » gather_statistics
gather_statistics [message #248763] Sun, 01 July 2007 13:58 Go to next message
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
Re: gather_statistics [message #248764 is a reply to message #248763] Sun, 01 July 2007 14:14 Go to previous messageGo to next message
BlackSwan
Messages: 25035
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)!
Re: gather_statistics [message #248856 is a reply to message #248764] Mon, 02 July 2007 08:28 Go to previous messageGo to next message
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
Re: gather_statistics [message #248879 is a reply to message #248856] Mon, 02 July 2007 09:50 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
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



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

Re: gather_statistics [message #248905 is a reply to message #248879] Mon, 02 July 2007 11:36 Go to previous messageGo to next message
adee_saleh
Messages: 30
Registered: May 2007
Location: Yemen
Member

i ment that i used
DBMS_STATS package 
to collect statistics of the index and table each by separate procedure but i get the same result just 0.
while when i use
ANALYZE stat
ment it gives me the statistics that i need
any help or possible causes?
Re: gather_statistics [message #248907 is a reply to message #248905] Mon, 02 July 2007 11:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
post your OS
and oracle versions.
Re: gather_statistics [message #249173 is a reply to message #248907] Tue, 03 July 2007 10:04 Go to previous messageGo to next message
adee_saleh
Messages: 30
Registered: May 2007
Location: Yemen
Member

my operating system is Windows xp sp2 and Oracle version is Oracle 10g (10.1.0.2)
Re: gather_statistics [message #249174 is a reply to message #249173] Tue, 03 July 2007 10:06 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
read this link for collecting statisics..
http://www.psoug.org/reference/dbms_stats.html
Previous Topic: Questions about Executing Explain Plan against a query.
Next Topic: How can i tune such a simple sql
Goto Forum:
  


Current Time: Sat Dec 03 20:30:22 CST 2016

Total time taken to generate the page: 0.10748 seconds