DBMS_STATS Package
Description
The PL/SQL package DBMS_STATS lets you generate and manage statistics for
cost-based optimisation. You can use this package to gather, modify, view,
export, import, and delete statistics.
The DBMS_STATS package can gather statistics on indexes, tables, columns,
and partitions, as well as statistics on all schema objects in a schema or
database. The statistics-gathering operations can run either serially or in
parallel (DATABASE/SCHEMA/TABLE only)
SQL Source
set echo on set feed on set timing on
execute dbms_stats.gather_table_stats (ownname => 'SCOTT'
, tabname => 'DEPT'
, partname=> null
, estimate_percent => 20
, degree => 5
, cascade => true);
execute dbms_stats.gather_schema_stats (ownname => 'SCOTT'
, estimate_percent => 20
, degree => 5
, cascade => true);
execute dbms_stats.gather_database_stats (estimate_percent => 20
, degree => 5
, cascade => true);
SQL Source - Dynamic Method
DECLARE
sql_stmt VARCHAR2(1024);
BEGIN
FOR tab_rec IN (SELECT owner,table_name
FROM all_tables WHERE owner like UPPER('&1')
) LOOP
sql_stmt := 'BEGIN dbms_stats.gather_table_stats (ownname => :1, tabname
=> :2,partname=> null, estimate_percent => 20, degree => 5 ,cascade => true); END;'
;
EXECUTE IMMEDIATE sql_stmt USING tab_rec.owner, tab_rec.table_name ;
END LOOP;
END;
/
Method 2 - Dynamic SQL Method
Description
This is a simple technique of using SQL to
generate SQL. The main benefit of using this technique as oppose
to Method 1 is that since it is a SQL query, you have control
down to table level about which tables you analyze. It is not
uncommon that in a single schema some tables will need to be
analyzed on a more frequent basis that other tables in the same
schema For example, frequently changing OLTP tables may need
analyzing daily whereas static/historical/archive data will only
need analyzing once just after population.
SQL Source
spool analyze.lst
set pages 0 set verify off
select 'ANALYZE TABLE '||owner||'.'||table_name||' ESTIMATE STATISTICS;'
from dba_tables
where owner not in ('SYS','SYSTEM')
/
spool off
start analyze.lst
Ref:
====
http://www.blacksheepnetworks.com/security/resources/www.think-forward.com/sql/analyze8.htm
http://www.blacksheepnetworks.com/security/resources/www.think-forward.com/sql/analyze.htm
