Analyze Oracle Database schema

Why is it important to analyze , and what is analyze anyway ?
well analyze is a method to gather statistics on table objects in order for the optimizer
to choose the best way for executing queries .
for example the optimizer may choose to use full table scan or to use table index ,
it does so by looking at the table statistics .
Oracle doesn’t gather statistics on schema’s all by it self and the DBA must do it
as part of database maintenance . its is wise to analyze your schema on regular basis’s ,
depend on the data changes . i will show you a small script that can help you analyze your schema .

 

#!/bin/sh
#
# This script will call dbms_stats to Analyze SCHEMA_OWNER schema

ORACLE_SID=<sid name>
ORACLE_BASE=<path to oracle base>
ORACLE_HOME=<path to oracle home>
export ORACLE_SID ORACLE_BASE ORACLE_HOME

$ORACLE_HOME/bin/sqlplus -s ” / as sysdba” <<eof1
spool /tmp/Analyzing.txt
exec dbms_stats.gather_schema_stats(ownname=>’SCHEMA_OWNER’,estimate_percent=>5,cascade=>true);
exit;
eof1

Posted in database tips Tagged with: , , ,