Anuj Singh Oracle DBA: DBMS_STATS.GATHER_SCHEMA

Saturday, 15 October 2011

DBMS_STATS.GATHER_SCHEMA_STATS

Schema stats

DBMS_STATS state gather
DBMS_STATS

declare
olist dbms_stats.objecttab ;
begin

declare
olist dbms_stats.objectTab;

begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);

dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;

end;

method_opt=>'for all indexed columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for columns size auto'

There are several values for the OPTIONS parameter that we need to know about:

GATHER - Reanalyzes the whole schema

GATHER EMPTY - only analyzes tables that have no existing statistics

GATHER STALE - only reanalyzes tables with more than 10 percent modifications (inserts, updates, deletes)

GATHER AUTO - will reanalyze objects that currently have no statistics and objects with stale statistics. Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.

Example :

exec DBMS_STATS.gather_schema_stats(
ownname => 'ANUJ',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
cascade => true )

SQL> show user
USER is "SCOTT"
SQL> /
Objects Analyzed: 0

PL/SQL procedure successfully completed.

SQL> r
1 declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14* end;
Objects Analyzed: 0

PL/SQL procedure successfully completed.

ORA-20001: Illegal option GATHER REANALYZES: must be GATHER | GATHER AUTO | GATHER STALE | GATHER EMPTY | LIST AUTO | LIST STALE | LIST EMPTY

'GATHER REANALYZES' reanalyzes

declare
olist dbms_stats.objecttab ;
begin

declare
olist dbms_stats.objectTab;

begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);

dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;

end;

SQL> show user
USER is "SCOTT"
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15
16
17 /
Objects Analyzed: 15
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: SALGRADE
Object Name: MYEMP_IDX
Object Name: PK_DEPT
Object Name: SYS_C0022541
Object Name: SYS_C0022542
Object Name: SYS_C0022543

PL/SQL procedure successfully completed.

declare
olist dbms_stats.objectTab;

begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);

dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;

end;

SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15 /
Objects Analyzed: 7
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: SALGRADE

PL/SQL procedure successfully completed.

SQL> declare
2 a dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_database_stats (OPTIONS=>'LIST EMPTY',OBJLIST=>a);
5
6 for i in 1 .. a.count
7 loop
8 dbms_output.put_line( a(i).ownname );
9 dbms_output.put_line( a(i).objType );
10 dbms_output.put_line( a(i).objName );
11 dbms_output.put_line( a(i).PartName );
12 dbms_output.put_line( a(i).subPartName );
13 dbms_output.put_line ( '-------------------------' );
15 end loop;
16 end;
17 /
SH
INDEX
SYS_IOT_TOP_74375
-------------------------
SH
INDEX
SYS_IOT_TOP_74380
-------------------------

PL/SQL procedure successfully completed.

===============

#!/bin/bash

. /home/oracle/.bash_profile

export ORACLE_HOME=/u01/app/oracle/product/12.2/db_home

export ORACLE_BASE=/u01/app/oracle

export ORACLE_SID=orcl

export DATE=$(date +%y-%m-%d_%H%M%S)

# Gather ANUJ schema stats

sqlplus / as sysdba << EOF > /tmp/ANUJ_stats_gather_$DATE.log

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

EXEC DBMS_STATS.gather_schema_stats('ANUJ');

EOF

echo "Stats gathered succeeded"

********

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

/home/oracle/bin/run_gather_database_stats.sh orcl &> /dev/null

cat /home/oracle/bin/run_gather_database_stats.sh

#! /bin/bash

source /home/oracle/.bash_profile

sid=$1

export OUTFILE=/var/log/oracle/gather_database_stats_$sid.log

export ORACLE_SID=${sid}

$echo $ORACLE_SID

sqlplus /nolog << EOF

connect / as sysdba

set line 999

set pagesize 999

set trimspool on

set echo on

spool $OUTFILE

@/home/oracle/bin/gather_databse_stats.sql

spool off

exit

cat /home/oracle/bin/gather_databse_stats.sql

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sysdate from dual;

exec DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER EMPTY');

select sysdate from dual;

exec DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER STALE');

select sysdate from dual;

[oracle@lon1-rac01 ~]$

=============

on root and pdbs !!!!

===================

set verify off time on timing on

alter session set nls_date_format='dd-mon-yyyy';

select sysdate from dual;