Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> DBMS_STATS.GATHER_TABLE_STATS, Use of this procedure
ashishprem
post Feb 7 2006, 02:47 AM
Post #1


Member
**

Group: Members
Posts: 12
Joined: 27-January 06
From: Chennai
Member No.: 3,587



Hi All,
Please tell me what's the use of this stored procedure. As the name sounds it is for gathering the statistics of the table.If so please give me the code to use this. Are there any other advantages of that procedure.

Regards,
Ashish
Go to the top of the page
 
+Quote Post
dave
post Feb 7 2006, 04:35 AM
Post #2


Advanced Member
***

Group: Members
Posts: 5,207
Joined: 8-October 04
Member No.: 785



time to read the manuals for you, learn about the cbo

http://download-west.oracle.com/docs/cd/B1...stats.htm#33861
Go to the top of the page
 
+Quote Post
Seshat
post May 13 2008, 08:31 AM
Post #3


Member
**

Group: Members
Posts: 12
Joined: 12-May 08
Member No.: 23,276



QUOTE (ashishprem @ Feb 7 2006, 07:48 AM) *
Hi All,
Please tell me what's the use of this stored procedure. As the name sounds it is for gathering the statistics of the table.If so please give me the code to use this. Are there any other advantages of that procedure.

Regards,
Ashish



Hi ashishprem;

I used this procedure and yes, this procedure is for gathering statistics of the table. I give you a code to use this with one or more tables. You have to use the results of this code:

select 'exec dbms_stats.gather_table_stats(' || CHR(39)|| owner || CHR(39) || ',' || CHR(39) ||table_name || CHR(39) || ',' || 'cascade => TRUE' || ')' || ';'
from dba_tables
where tablespace_name like 'USERS' -- you can use wherever you want in the where clause
order by owner, table_name;

You'll obtain something like that:

exec dbms_stats.gather_table_stats('owner','table1',cascade => TRUE);
exec dbms_stats.gather_table_stats('owner','table2',cascade => TRUE);
exec dbms_stats.gather_table_stats('owner','table3',cascade => TRUE);

I hope that I help you.

Greetings,

Seshat.-
Go to the top of the page
 
+Quote Post
burleson
post May 13 2008, 08:56 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,706
Joined: 26-January 04
Member No.: 13



Hi Ashish,

Try here, to understand the procedure in detail:

http://www.dba-oracle.com/concepts/tables_..._statistics.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd November 2014 - 04:35 AM