KfWiki: Compute statistics

HomePage » Database » Oracle » OracleStatistics

Compute statistics

Analyze all tables - can sometimes help performance as Oracle can obtain updated information about your tables. This would help especially on fast-changing tables. Indexes should be analyzed too. Oracle comes with a DBMS_UTILITY package which allows DBA to do this task quickly. Alternatively, DBA can also do 'analyze table TABLE_NAME compute statistics;'

exec dbms_utility.analyze_schema('SOME_SCHEMA', 'COMPUTE');
exec dbms_utility.analyze_schema('SOME_SCHEMA', 'ESTIMATE', estimate_rows => 1024);
exec dbms_utility.analyze_schema('SOME_SCHEMA', 'ESTIMATE', estimate_percent => 10);

Viewing analyze results

First query database block size

show parameters BLOCK

Then select analysis results

# assuming block size is 8192 byte
select table_name,
NUM_ROWS,
BLOCKS*8192/1024/1024 as SizeMb,
EMPTY_BLOCKS, LAST_ANALYZED
from dba_tables
where owner = 'SOME_OWNER';

Create AWR snapshot

If you happens to need to generate AWR snapshots from a script, here's what I did -

export ORACLE_SID=foo
sqlplus -s system/cheap as sysdba << EOF
set pagesize 120
set linesize 120
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time" from dual;
select 'Last SNAP_ID: ' || max(snap_id) from dba_hist_snapshot;
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
select 'Last SNAP_ID: ' || max(snap_id) from dba_hist_snapshot;
exit;

EOF

Generate AWR report

A bash script to gather inputs and then run the awr report function.

#!/bin/bash

if [ $# -lt 1 ]; then

        echo "Usage:gen-awr.sh <bid> <eid> <SID>"

        exit 1

fi

export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

export ORACLE_SID=$3

reportBegin=$1

reportEnd=$2

reportName=AWR-$ORACLE_SID-$reportBegin-$reportEnd.html

reportInst=${ORACLE_SID:6:1}

if [ ${ORACLE_SID:0:6} == "SAFARI" ]; then

        reportDbid="3827977577"

        dbPwd="xxx"

else

        reportDbid="1910233441"

        dbPwd="yyy"

fi

sqlplus -S system/$dbPwd@$ORACLE_SID << EOF

set veri off;

set feedback off;

set heading off;

set linesize 1500;

set termout on;

spool $reportName;

select output from table(dbms_workload_repository.AWR_REPORT_HTML( $reportDbid, $reportInst, $reportBegin, $reportEnd, 0));

spool off;

exit;

<<EOF

KfWiki : OracleStatistics

Compute statistics

Viewing analyze results

Create AWR snapshot

Generate AWR report