Monday, March 26, 2007

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

Tuesday, March 13, 2007

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