| Gathering statistics with different size parameter [message #173599] |
Tue, 23 May 2006 06:43  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
I am using - Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
i want to create a generic script for gathering statistics of all tables and indexes.
So that i can create a scheduled job using it.
However, if i create procedure like the following one, it will create histograms with same number of buckets (e.g. 250 here).
If a table is having primary key then its statistics will be hampered with this method (fixed "size")of statistics.
Please suggest, how to make it dynamic?
Procedure i have created is as follows:
CREATE OR REPLACE PROCEDURE table_stats
AS
BEGIN
FOR c_tbl_nm IN (SELECT table_name
FROM user_tables)
LOOP
DBMS_STATS.gather_table_stats
('HSASYS',
UPPER (c_tbl_nm.table_name),
method_opt => 'For all indexed columns size 250',
CASCADE => TRUE
);
END LOOP;
END;
/
Thanks in Advance,
Pratap
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
