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