SCRIPTS FOR MONITORING ORACLE DATABASES
run { crosscheck archivelog all; delete noprompt expireD archivelog all; } col current_scn form 999999999999999999 col TIMESTAMP_TO_SCN form 9999999999999999 col NOW10G form 99999999999999999999 col NOW9I form 99999999999999999999 col SCN form 99999999999999999999 col TIMESTAMP form 99999999999999999999 **************FOR SQL PLAN *************** select * from table(dbms_xplan.display_awr('&sql_id')); col begin_interval_time for a30 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows from DBA_HIST_SQLSTAT S, DBA_HIST_SNAP