Analyze Tables and Indexes


Analyze Tables and Indexes (for Better Performance)
Below, I have shown an example on how to pass parameters to a script:
###########################
## analyze_table.sh ##
#########################
#!/bin/ksh #
input parameter: 1: password # 2: SID if (($#<1)) then echo "Please enter 
'oracle'
user password as the first parameter !" exit 0 fi if (($#<2)) then echo 
"Please enter
instance name as the second parameter!" exit 0 fi
To execute the script with parameters, type:
$ analyze_table.sh manager oradb1
The first part of script generates a file analyze.sql, 
which contains the syntax for analyzing table. 
The second part of script analyzes all the tables:
###############################
## analyze_table.sh ##
###############################
sqlplus -s <<!
oracle/$1@$2
set heading off
set feed off
set pagesize 200
set linesize 100
spool analyze_table.sql
select 'ANALYZE TABLE ' || owner || '.' || segment_name || 
 ' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'
from dba_segments
where segment_type = 'TABLE'
and owner not in ('SYS', 'SYSTEM');
spool off
exit
!
sqlplus -s <<!
oracle/$1@$2
@./analyze_table.sql
exit
! 
Here is an example of analyze.sql:
$ cat analyze.sql
ANALYZE TABLE HIRWIN.JANUSAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE HIRWIN.JANUSER_PROFILE ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE APPSSYS.HIST_SYSTEM_ACTIVITY ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE HTOMEH.QUEST_IM_VERSION ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE JSTENZEL.HIST_SYS_ACT_0615 ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE JSTENZEL.HISTORY_SYSTEM_0614 ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE JSTENZEL.CALC_SUMMARY3 ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE IMON.QUEST_IM_LOCK_TREE ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE APPSSYS.HIST_USAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE PATROL.P$LOCKCONFLICTTX ESTIMATE STATISTICS SAMPLE 10 PERCENT;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: