| |
 |
|
Automating Oracle
Oracle Tips by Mike Ault |
Automated
Statistics Gathering
OPEN get_stat(stat_name);
FETCH get_stat INTO temp_name,
r_calls;
CLOSE get_stat;
EXCEPTION
WHEN NO_DATA_FOUND THEN
CLOSE get_stat;
END;
.
.
.
BEGIN
calls_u := (r_calls/u_calls);
h_ratio := ((db_gets+con_gets)/(db_gets+con_gets+p_reads));
suhw_cont := (suh_waits/(db_gets+con_gets)*100);
subw_cont := (sub_waits/(db_gets+con_gets)*100);
uhw_cont := (uh_waits/(db_gets+con_gets)*100);
ubw_cont := (ub_waits/(db_gets+con_gets)*100);
stat_name := 'RECURSIVE CALLS PER USER';
OPEN get_delta(calls_u,stat_name,comp_date);
FETCH get_delta INTO delta;
CLOSE get_delta;
write_out
(stat_name,calls_u,3,meas_date,delta,is_interactive);
stat_name := 'CUMMULATIVE HIT RATIO';
write_out
(stat_name,h_ratio,3,meas_date,0,is_interactive);
stat_name := 'SYS UNDO HDR WAIT CONTENTION';
write_out
(stat_name,suhw_cont,3,meas_date,0,is_interactive);
stat_name := 'SYS UNDO BLK WAIT CONTENTION';
write_out
(stat_name,subw_cont,3,meas_date,0,is_interactive);
stat_name := 'UNDO HDR WAIT CONTENTION';
write_out
(stat_name,uhw_cont,3,meas_date,0,is_interactive);
stat_name := 'UNDO BLK WAIT CONTENTION';
write_out
(stat_name,ubw_cont,3,meas_date,0,is_interactive);
stat_name := 'freelist';
OPEN get_count(stat_name);
FETCH get_count INTO temp_name, stat_val;
CLOSE get_count;
stat_name := 'Free List Contention Ratio';
write_out
(stat_name,stat_val,4,meas_date,0,is_interactive);
SEE CODE DEPOT FOR FULL SCRIPTS
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
