Gather statistics for a given oracle session

Gather statistics for a given oracle session

alter session set tracefile_identifier=''; 
-- should be replaced with your own identifying name...
alter session set events='10046 trace name context forever, level 8';
set timing on

PerlTips - 1

to escape particular characters in a given variable $var ->
$var =~ s/([CHARLIST])/\\$1/g;

(http://www.unix.org.ua/orelly/perl/cookbook/ch01_14.htm)

The complete Perl Bookshelf

http://www.unix.org.ua/orelly/perl/index.htm

Delete Statistics from a Schema

-- delete all statistics
DECLARE
CURSOR cOwner IS SELECT DISTINCT owner
               FROM dba_tables
                WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP',);
BEGIN
 -- analyze sOwner,compute
 FOR rOwner IN cOwner LOOP
    DBMS_STATS.DELETE_SCHEMA_STATS
    (ownname => rOwner.owner);
 END LOOP;
END;
/
select 'END_DATE: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') as end_date from dual;
exit

Gather statistics for a schema

-- recreate all statistics
DECLARE
CURSOR cOwner IS SELECT DISTINCT owner
               FROM dba_tables
               WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP',);
BEGIN
 -- analyze sOwner,compute
 FOR rOwner IN cOwner LOOP
    DBMS_STATS.GATHER_SCHEMA_STATS
    (ownname => rOwner.owner,
     estimate_percent => 99,
     block_sample => FALSE,
     method_opt => 'FOR ALL COLUMNS SIZE 150',
     degree => NULL,
     granularity => 'DEFAULT',
     cascade => TRUE);
 END LOOP;
END;
/

select 'END_DATE: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') as end_date from dual;
exit