Document Type | Technical Information
Category | Tuning
Document Number | TTUTI011
Overview
The statistics collection procedure is a process in the DBMS for collecting and updating statistics information.
Statistics information can be used to optimize query execution plans, and using a procedure allows for more efficient automated collection of this information.
Method
Table
CREATE OR REPLACE PROCEDURE SYS.STATS_NOR_TABLE
(
owner_in IN VARCHAR
)
IS
CURSOR stats_loop IS
select
'call dbms_stats.gather_table_stats('''||owner||''', '''||table_name||''''||
', estimate_percent=>5,'||' method_opt=>'''||'FOR ALL INDEXED COLUMNS SIZE 124'''||
', degree=>8);' as stats_list
from dba_tables
where owner = owner_in
and partitioned = 'NO';
BEGIN
FOR stats_msg in stats_loop LOOP
DBMS_OUTPUT.PUT_LINE('stats msg : ' || stats_msg.stats_list);
execute immediate stats_msg.stats_list;
END LOOP;
END STATS_NOR_TABLE;
Partition Table
CREATE OR REPLACE PROCEDURE SYS.STATS_PART_TABLE
(
owner_in IN VARCHAR
)
IS
CURSOR stats_loop IS
select
'call dbms_stats.gather_table_stats('''||owner||''', '''||table_name||''''||', partname=>'||''''||partition_name||''''||
', estimate_percent=>5,'||' method_opt=>'''||'FOR ALL INDEXED COLUMNS SIZE 124'''||', degree=>8'||
', granularity=>''PARTITION'''||');' as stats_list
from dba_tab_partitions
where owner = owner_in;
BEGIN
FOR stats_msg in stats_loop LOOP
DBMS_OUTPUT.PUT_LINE('stats msg : ' || stats_msg.stats_list);
execute immediate stats_msg.stats_list;
END LOOP;
END STATS_PART_TABLE;