SQL Response Time Baseline SQL Response Time Baseline Response Current SQL Response Time Current Response SQL Response Time (%) Response OpenMode MCResponse 3 SQL_CURSOR MachineName Port SID UserName password Role Collect SQL Response Time SQL Response Time (%) Response OpenMode MCResponse 3 SQL_CURSOR MachineName Port SID UserName password Role Throughput All Sessions All Sessions logons cumulative parse count (total) parse count (hard) execution count user calls recursive calls sorts (memory) sorts (disk) sorts (rows) session logical reads physical reads table scans (long tables) index fast full scans (full) physical writes bytes sent via SQL*Net to client bytes received via SQL*Net from client bytes sent via SQL*Net to dblink bytes received via SQL*Net from dblink user commits DBWR checkpoints user rollbacks redo size redo writes transactions Number of Transactions (per second) Transactions/s Physical Reads (per second) Physical Reads/s Physical Reads (per transaction) Physical Writes (per second) Phys Writes/sec Physical Writes (per transaction) Redo Generated (per second) Redo Size/sec Redo Generated (per transaction) Cumulative Logons (per second) Logons/sec Cumulative Logons (per transaction) User Commits (per second) User Commits (per transaction) User Rollbacks (per second) User Rollbacks (per transaction) User Calls (per second) User Calls (per transaction) Recursive Calls (per second) Recursive Calls (per transaction) Session Logical Reads (per second) Logical Reads/s Session Logical Reads (per transaction) DBWR Checkpoints (per second) Redo Writes (per second) Redo Writes (per transaction) Scans on Long Tables (per second) Scans on Long Tables (per transaction) Total Parses (per second) Total Parses (per transaction) Hard Parses (per second) Hard Parses (per transaction) Executes (per second) SQL Executions/s Sorts to Disk (per second) Sorts to Disk (per transaction) Rows Processed (per sort) Executes Performed without Parses (%) Soft Parse (%) User Calls (%) Total Table Scans (per second) Total Table Scans (per transaction) Network Bytes (per second) 1 SQL_CURSOR MachineName Port SID UserName password Role Efficiency sorts (memory) sorts (disk) session logical reads physical reads physical reads direct redo entries redo log space requests libcache pins libcache pinhits dictionary gets dictionary misses Buffer Cache Hit (%) Library Cache Hit (%) Data Dictionary Hit (%) Sorts in Memory (%) Redo Log Allocation Hit (%) stat_names(curr_stat#_ord) THEN ---- -- we've had a miss, so post-process the array, setting our NULL -- statistic#s to our pseudo-statistic# -- this can only happen if -- a statistic is deprecated by the server (which it doesn't do), or -- a misspelling of a valid statistic name in the list, or -- an incorrect alphabetical ordering of the list ---- ordered_stat#s.EXTEND; ordered_stat#s(curr_stat#_ord) := NULL; curr_stat#_ord := curr_stat#_ord + 1; statname_misses := statname_misses + 1; END IF; END LOOP; IF statname_misses > 0 THEN -- (post-process the misses) FOR stat_ord IN 1..ordered_stat#s.COUNT LOOP IF ordered_stat#s(stat_ord) IS NULL THEN ordered_stat#s(stat_ord) := max_stat# + 2; END IF; END LOOP; END IF; ---- -- query data into sysstat_vals, indexed by statistic#, leaving a zeroed -- slot at the end for misses ---- -- (supply a value for misses) sysstat_vals.EXTEND(max_stat# + 2); sysstat_vals(max_stat# + 2) := 0; -- (populate the stat# table) FOR stat#_val_pair IN (SELECT statistic# stat#, value FROM v$sysstat) LOOP sysstat_vals(stat#_val_pair.stat# + 1) := stat#_val_pair.value; END LOOP; ---- -- populate the SYSSTAT regions of the output record -- -- Note: The order of this list must match the alphabetical order of -- statistic names! ---- i := 1; physical_reads := sysstat_vals(ordered_stat#s(i)); i := i + 1; physical_reads_direct := sysstat_vals(ordered_stat#s(i)); i := i + 1; redo_entries := sysstat_vals(ordered_stat#s(i)); i := i + 1; redo_space_requests := sysstat_vals(ordered_stat#s(i)); i := i + 1; logical_reads := sysstat_vals(ordered_stat#s(i)); i := i + 1; sorts_disk := sysstat_vals(ordered_stat#s(i)); i := i + 1; sorts_memory := sysstat_vals(ordered_stat#s(i)); i := i + 1; ---- -- get library cache pin data ---- SELECT SUM(pins), SUM(pinhits) INTO libcache_pins, libcache_pinhits FROM v$librarycache; ---- -- get dictionary gets data ---- SELECT SUM(gets), SUM(getmisses) INTO dictionary_gets, dictionary_misses FROM v$rowcache; -- open the cursor to return OPEN data_cursor FOR SELECT sorts_memory, sorts_disk, logical_reads, physical_reads, physical_reads_direct, redo_entries, redo_space_requests, libcache_pins, libcache_pinhits, dictionary_gets, dictionary_misses FROM dual; :1 := data_cursor; END; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role SGA Pool Wastage Shared Pool Free (%) Large Pool Free (%) Java Pool Free (%) 1 SQL_CURSOR MachineName Port SID UserName password Role Wait Bottlenecks local_write_wait_time net_reset_to_client_time net_reset_to_dblink_time enqueue_dml_time enqueue_row_lock_time enqueue_userdef_time log_file_sync_time buffer_busy_waits_time latch_cache_buffer_chains_time latch_library_cache_time library_cache_load_lock_time library_cache_lock_time library_cache_pin_time pipe_put_time row_cache_lock_time enqueue_hw_time enqueue_st_time enqueue_alloc_itl_time free_buffer_waits_time latch_redo_copy_time latch_shared_pool_time log_buffer_space_time log_file_switch_arch_time log_file_switch_ckpt_time log_file_switch_complete_time log_switch_arch_time write_complete_waits_time net_msg_to_client_time net_msg_to_dblink_time net_more_from_client_time net_more_from_dblink_time net_more_to_client_time net_more_to_dblink_time db_file_scattered_read_time db_file_sequential_read_time db_file_single_write_time direct_path_read_time direct_path_read_lob_time direct_path_write_time direct_path_write_lob_time latch_free_time enqueue_time Wait Time (sec) CPU Time (sec) User I/O Wait Time (sec) CPU Count Service Time Delta (sec) Local write wait (%) SQL*Net break/reset to client (%) SQL*Net break/reset to dblink (%) Enqueue: DML - contention (%) Enqueue: TM, TX, Transaction - row lock contention (%) Enqueue: TM, TX, Txn (%) Enqueue: UL: User-defined - contention (%) Enqueue: UL (%) Log file sync (%) Buffer busy waits (%) Latch: cache buffer chains (%) Latch: library cache (%) Library cache load lock (%) Library cache lock (%) Library cache pin (%) Pipe put (%) Row cache lock (%) Enqueue: HW, Segment High Water Mark - contention (%) Enqueue: HW Segment HWM (%) Enqueue: ST, Space Transaction - contention (%) Enqueue: ST, Space Txn (%) Enqueue: TX mode 4, Transaction - allocate ITL entry (%) Enqueue: TX mode 4, Txn (%) Free buffer waits (%) Latch: redo copy (%) Latch: shared pool (%) Log buffer space (%) Log file switch (archiving needed) (%) Log file switch (checkpoint complete) (%) Log file switch (ckpt complete) (%) Log file switch completion (%) Log switch/archive (%) Write complete waits (%) SQL*Net message to client (%) SQL*Net message to dblink (%) SQL*Net more data from client (%) SQL*Net more data from dblink (%) SQL*Net more data to client (%) SQL*Net more data to dblink (%) DB file scattered read (%) DB file sequential read (%) DB file single write (%) Direct path read (%) Direct path read (lob) (%) Direct path write (%) Direct path write (lob) (%) Latch free - other (%) Enqueue - other (%) Wait Time (%) Wait Time % CPU Time Delta (sec) CPU (sec) Wait Time Delta (sec) User I/O Wait Time Delta (sec) Active Sessions Active Sessions Waiting: I/O Sessions: I/O Active Sessions Using CPU Sessions: CPU Active Sessions Waiting: Other Sessions: Other Average Instance CPU (%) Instance CPU (%) CPUCount TimedStatistics 3 SQL_CURSOR MachineName Port SID UserName password Role Dump Area Type of Dump Area Dump Area Directory Total Dump Area (KB) Dump Area Used (KB) Dump Area Used (%) Free Dump Area (KB) perlBin scriptsDir background_dump_dest core_dump_dest user_dump_dest %perlBin%/perl %scriptsDir%/db/dbdumpfull.pl %background_dump_dest% %core_dump_dest% %user_dump_dest% em_result= | NAME Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Archive Area Archive Area Destination Total Archive Area (KB) Archive Area Used (KB) Archive Area Used (%) Archive Area % Free Archive Area (KB) perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/dbarchfull.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Response User Logon Time (msec) Logon Time(ms) Status State ORA- Error perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/dbresp.pl em_result= | NAME UserName password Role MachineName Port SID OracleHome (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) Database Limits Lock Limit Usage (%) Process Limit Usage (%) Session Limit Usage (%) User Limit Usage (%) TRUE MachineName Port SID UserName password Role Alert Log Alert Time Time/Line Number Generic Alert Log Error Archiver Hung Alert Log Error Data Block Corruption Alert Log Error Session Terminated Alert Log Error Media Failure Alert Log Error Alert Log Error Trace File Witnessed Error Codes Alert Log Name perlBin scriptsDir em_result= | alert_log_file background_dump_dest user_dump_dest limitSwitch ignorePattern %perlBin%/perl %scriptsDir%/alertlog.pl %log_file_absolute% '%log_timestamp_format%' 'ORA' %background_dump_dest% %user_dump_dest% %limitSwitch% %ignorePattern% ConvertFromCharset needCharsetConvert GUID OracleHome SID agentStateDir 4 00257,16038 01157,01578,27048 00603 01242,01243 Alert Log Error Status Generic Alert Log Error Status Archiver Hung Alert Log Error Status Data Block Corruption Alert Log Error Status Data Block Corruption Error Status Session Terminated Alert Log Error Status Session Terminated Error Status Media Failure Alert Log Error Status Media Failure Error Status perlBin scriptsDir em_result= | AdrHome limitSwitch %perlBin%/perl %scriptsDir%/alertlogAdr.pl %AdrHome% %limitSwitch% ConvertFromCharset needCharsetConvert GUID agentStateDir 17 Incident Time/Line Number Generic Incident Generic Internal Error Session Terminated Internal SQL Error Access Violation Redo Log Corruption File Access Error Inconsistent DB State Oracle Data Block Corruption Deadlock Out of Memory Cluster Error Alert Log Error Trace File Witnessed Error Codes Alert Log Name Impact Incident ID incident Operational Error Time/Line Number Generic Operational Error Archiver Hung Data Block Corruption Media Failure Alert Log Error Trace File Witnessed Error Codes Alert Log Name operational Data Failure Time/Line Number Data Failure Detected Number of Failures Alert Log Name dataFailure Incident Status Generic Incident Status Generic Internal Error Status Session Terminated Status Internal SQL Error Status Access Violation Status Redo Log Corruption Status File Access Error Status Inconsistent DB State Status Oracle Data Block Corruption Status Deadlock Status Out of Memory Status Cluster Error Status Operational Error Status Generic Operational Error Status Archiver Hung Status Data Block Corruption Status Media Failure Status Alert Log Content Content perlBin scriptsDir em_result= SID OracleHome background_dump_dest ConvertFromCharset needCharsetConvert sizeToView startTime endTime %perlBin%/perl %scriptsDir%/alertlogViewer.pl %OracleHome% %GetDumpDestination% %SID% %sizeToView% %startTime% %endTime% perlBin scriptsDir em_result= ConvertFromCharset needCharsetConvert GUID agentStateDir AdrHome ARGS LARGE_ARGS %perlBin%/perl %scriptsDir%/alertlogAdrViewer.pl %AdrHome% "%ARGS%" User Block Blocking Session ID Blocking Session Count 0 GROUP BY id1, id2) GROUP BY blocking_sid ]]> MachineName Port SID UserName password Role User Audit Audited User Audited User Host Audited User Session Count Username_Machine 'BACKGROUND' and username is not null ) group by uname, mname ]]> MachineName Port SID UserName password Role mgmt_sql_metric_helper Data Available 1 SQL_CURSOR MachineName Port SID UserName password Role mgmt_sql_summary Snap ID Collection Timestamp SQL Statement Piece Sharable Memory Sorts Module Loaded Versions Executions Loads Invalidations Parse Calls Disk Reads Buffer Gets Rows Processed Command Type Address Hash Value Sql Version Count CPU Time Elapsed Time Outline SID Outline Category Plan Hash Value Optimizer Mode Optimizer Cost v_b_snap_time THEN v_b_snap_id := -1; v_b_snap_time := v_e_startup_time; END IF; v_time_interval := v_e_snap_time - v_b_snap_time; IF v_time_interval is NULL THEN v_time_interval := 1; END IF; IF v_e_snap_id <> -1 THEN IF v_db_version >= db_version_9i THEN OPEN get_stat_total_value('CPU used when call started', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_cpu_time; CLOSE get_stat_total_value; END IF; OPEN get_stat_total_value('session logical reads', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_total_buf_gets; CLOSE get_stat_total_value; OPEN get_stat_total_value('physical reads', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_total_disk_reads; CLOSE get_stat_total_value; OPEN get_stat_total_value('parse count (total)', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_parse_calls; CLOSE get_stat_total_value; OPEN get_stat_total_value('execute count', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_executions; CLOSE get_stat_total_value; END IF; IF v_b_snap_id <> -1 THEN IF v_db_version >= db_version_9i THEN OPEN get_stat_total_value('CPU used when call started', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_cpu_time; CLOSE get_stat_total_value; END IF; OPEN get_stat_total_value('session logical reads', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_total_buf_gets; CLOSE get_stat_total_value; OPEN get_stat_total_value('physical reads', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_total_disk_reads; CLOSE get_stat_total_value; OPEN get_stat_total_value('parse count (total)', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_parse_calls; CLOSE get_stat_total_value; OPEN get_stat_total_value('execute count', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_executions; CLOSE get_stat_total_value; END IF; v_total_disk_reads := v_e_total_disk_reads - v_b_total_disk_reads; v_total_buf_gets := v_e_total_buf_gets - v_b_total_buf_gets; v_pect_disk_reads := c_phy_read_pct * v_total_disk_reads; v_pect_buf_gets := c_log_read_pct * v_total_buf_gets; v_pect_cpu_time := c_cpu_time_pct * (v_e_cpu_time - v_b_cpu_time) * 10000; v_pect_parse_calls := c_par_call_pct * (v_e_parse_calls - v_b_parse_calls); v_pect_executions := c_exec_pct * (v_e_executions - v_b_executions); IF v_db_version < db_version_9i THEN -- 8i case v_sql_stmt_8 := 'select e.snap_id, :end_snap_time_dis, st.sql_text, st.piece, e.sharable_mem, ' || 'e.sorts - nvl(b.sorts,0), e.module, e.loaded_versions, ' || 'e.executions - nvl(b.executions,0), e.loads - nvl(b.loads,0), ' || 'e.invalidations - nvl(b.invalidations,0), ' || 'e.parse_calls - nvl(b.parse_calls,0), ' || 'e.disk_reads - nvl(b.disk_reads,0), ' || 'e.buffer_gets - nvl(b.buffer_gets,0), ' || 'e.rows_processed - nvl(b.rows_processed,0), st.command_type, ' || 'RAWTOHEX(e.address), e.hash_value, ' || 'e.version_count, -1 cpu_time, ' || '-1 elapsed_time, -1 outline_sid, NULL outline_category, -1 plan_hash_value, ' || ''' '' optimizer, 0 cost ' || 'from perfstat.stats$sql_summary e, perfstat.stats$sql_summary b, perfstat.stats$sqltext st ' || 'where b.snap_id(+) = :b_snap_id ' || ' and b.dbid(+) = e.dbid ' || ' and b.instance_number(+) = e.instance_number ' || ' and st.piece =0 ' || ' and b.hash_value(+) = e.hash_value ' || ' and b.address(+) = e.address ' || ' and b.text_subset(+) = e.text_subset ' || ' and e.snap_id = :e_snap_id ' || ' and e.dbid = :dbid ' || ' and e.instance_number = :instance_number ' || ' and e.hash_value = st.hash_value ' || ' and e.text_subset = st.text_subset ' || ' and e.executions - nvl(b.executions,0) > 0 ' || ' and (e.buffer_gets - nvl(b.buffer_gets,0) >= :pect_buf_gets ' || ' or e.disk_reads - nvl(b.disk_reads,0) >= :pect_disk_reads ' || ' or e.parse_calls - nvl(b.parse_calls,0) >= :pect_parse_calls ' || ' or e.executions - nvl(b.executions,0) >= :pect_executions)' || ' UNION ALL ' || 'select :end_snap_id, :snap_time_dis, ''DUMMY_STMT_FOR_SUMMARY_STATISTICS'', -1, -1, ' || '-1, '' '', -1, -1, -1, -1, -1, :total_disk_reads total_disk_reads, ' || ':total_buf_gets total_buffer_gets, -1, -1, ''0'', 0, -1, -1 cpu_time, ' || ':time_interval elapsed_time, -1 outline_sid, ' || 'NULL outline_category, -1 plan_id, '' '' optimizer_mode, 0 optimizer_cost ' || 'from dual'; OPEN v_stat_cursor_8i FOR v_sql_stmt_8 USING v_e_snap_time_dis, v_b_snap_id, v_e_snap_id, v_dbid, v_instance_number, v_pect_buf_gets, v_pect_disk_reads, v_pect_parse_calls, v_pect_executions, v_e_snap_id, v_e_snap_time_dis, v_total_disk_reads, v_total_buf_gets, v_time_interval; :6 := v_stat_cursor_8i; ELSIF v_db_version < db_version_92 THEN -- 901 case v_sql_stmt_90 := 'select e.snap_id, :e_snap_time_dis, st.sql_text, st.piece, e.sharable_mem, ' || 'e.sorts - nvl(b.sorts,0), e.module, e.loaded_versions, ' || 'e.executions - nvl(b.executions,0), e.loads - nvl(b.loads,0), ' || 'e.invalidations - nvl(b.invalidations,0), ' || 'e.parse_calls - nvl(b.parse_calls,0), ' || 'e.disk_reads - nvl(b.disk_reads,0), ' || 'e.buffer_gets - nvl(b.buffer_gets,0), ' || 'e.rows_processed - nvl(b.rows_processed,0), st.command_type, ' || 'RAWTOHEX(e.address), e.hash_value, ' || 'e.version_count, ' || 'e.cpu_time - nvl(b.cpu_time,0), ' || 'e.elapsed_time - nvl(b.elapsed_time,0), ' || 'e.outline_sid, e.outline_category, nvl(pu.plan_hash_value, -1), ' || 'pu.optimizer, pu.cost ' || 'from perfstat.stats$sql_summary e, perfstat.stats$sql_summary b, perfstat.stats$sqltext st, ' || '(select u.plan_hash_value,max(u.optimizer) optimizer, max(u.cost) cost, s.hash_value,s.text_subset ' || 'from perfstat.stats$sql_plan_usage u, perfstat.stats$sql_summary s ' || 'where s.snap_id = :e_snap_id and s.dbid = :dbid and s.instance_number = :instance_number and ' || 'u.hash_value(+) = s.hash_value and u.text_subset(+) = s.text_subset ' || 'group by s.hash_value, s.text_subset, u.plan_hash_value) pu ' || 'where b.snap_id(+) = :b_snap_id ' || ' and b.dbid(+) = e.dbid ' || ' and b.instance_number(+) = e.instance_number ' || ' and st.piece =0 ' || ' and b.hash_value(+) = e.hash_value ' || ' and b.address(+) = e.address ' || ' and b.text_subset(+) = e.text_subset ' || ' and e.snap_id = :end_snap_id ' || ' and e.dbid = :dbid_2 ' || ' and e.instance_number = :instance_number_2 ' || ' and e.hash_value = st.hash_value ' || ' and e.text_subset = st.text_subset ' || ' and e.hash_value = pu.hash_value ' || ' and e.text_subset = pu.text_subset ' || ' and e.executions - nvl(b.executions,0) > 0 ' || ' and (e.disk_reads - nvl(b.disk_reads,0) >= :pect_disk_reads ' || ' or e.cpu_time - nvl(b.cpu_time,0) >= :pect_cpu_time ' || ' or e.parse_calls - nvl(b.parse_calls,0) >= :pect_parse_calls ' || ' or e.executions - nvl(b.executions,0) >= :pect_executions)' || ' UNION ALL ' || 'select :e_snap_id_3, :e_snap_time_dis_2, ''DUMMY_STMT_FOR_SUMMARY_STATISTICS'', -1, -1, ' || '-1, '' '', -1, -1, -1, -1, -1, :total_disk_reads total_disk_reads, ' || ':total_buf_gets total_buffer_gets, -1, -1, ''0'', 0, -1, -1 cpu_time, ' || ':time_interval elapsed_time, -1 outline_sid, ' || 'NULL outline_category, -1 plan_id, '' '' optimizer_mode, 0 optimizer_cost ' || 'from dual'; OPEN v_stat_cursor_9i FOR v_sql_stmt_90 USING v_e_snap_time_dis, v_e_snap_id, v_dbid, v_instance_number, v_b_snap_id, v_e_snap_id, v_dbid, v_instance_number, v_pect_disk_reads, v_pect_cpu_time, v_pect_parse_calls, v_pect_executions, v_e_snap_id, v_e_snap_time_dis, v_total_disk_reads, v_total_buf_gets, v_time_interval; :6 := v_stat_cursor_9i; ELSE v_sql_stmt_92 := 'select e.snap_id, :e_snap_time_dis, st.sql_text, st.piece, e.sharable_mem, ' || 'e.sorts - nvl(b.sorts,0), e.module, e.loaded_versions, ' || 'e.executions - nvl(b.executions,0), e.loads - nvl(b.loads,0), ' || 'e.invalidations - nvl(b.invalidations,0), ' || 'e.parse_calls - nvl(b.parse_calls,0), ' || 'e.disk_reads - nvl(b.disk_reads,0), ' || 'e.buffer_gets - nvl(b.buffer_gets,0), ' || 'e.rows_processed - nvl(b.rows_processed,0), st.command_type, ' || 'RAWTOHEX(e.address), e.hash_value, ' || 'e.version_count, ' || 'e.cpu_time - nvl(b.cpu_time,0), ' || 'e.elapsed_time - nvl(b.elapsed_time,0), ' || 'e.outline_sid, e.outline_category, nvl(pu.plan_hash_value, -1), ' || 'pu.optimizer, pu.cost ' || 'from perfstat.stats$sql_summary e, perfstat.stats$sql_summary b, perfstat.stats$sqltext st, ' || '(select u.plan_hash_value,max(u.optimizer) optimizer, max(u.cost) cost, s.hash_value, ' || 's.text_subset, max(s.dbid) dbid, max(s.instance_number) instance_number ' || 'from perfstat.stats$sql_plan_usage u, perfstat.stats$sql_summary s ' || 'where u.snap_id(+) = s.snap_id and u.dbid (+)= s.dbid and u.instance_number (+)= s.instance_number and ' || 's.snap_id = :e_snap_id and s.dbid = :dbid and s.instance_number = :instance_number and ' || 'u.hash_value(+) = s.hash_value and u.text_subset(+) = s.text_subset ' || 'group by s.hash_value, s.text_subset, u.plan_hash_value) pu ' || 'where b.snap_id(+) = :b_snap_id ' || ' and b.dbid(+) = e.dbid ' || ' and b.instance_number(+) = e.instance_number ' || ' and st.piece =0 ' || ' and b.hash_value(+) = e.hash_value ' || ' and b.address(+) = e.address ' || ' and b.text_subset(+) = e.text_subset ' || ' and e.snap_id = :e_snap_id_2 ' || ' and e.dbid = :dbid_2 ' || ' and e.instance_number = :instance_number_2 ' || ' and e.hash_value = st.hash_value ' || ' and e.text_subset = st.text_subset ' || ' and e.hash_value = pu.hash_value ' || ' and e.text_subset = pu.text_subset ' || ' and e.dbid = pu.dbid ' || ' and e.instance_number = pu.instance_number ' || ' and e.executions - nvl(b.executions,0) > 0 ' || ' and (e.disk_reads - nvl(b.disk_reads,0) >= :pect_disk_reads ' || ' or e.cpu_time - nvl(b.cpu_time,0) >= :pect_cpu_time ' || ' or e.parse_calls - nvl(b.parse_calls,0) >= :pect_parse_calls ' || ' or e.executions - nvl(b.executions,0) >= :pect_executions)' || ' UNION ALL ' || 'select :e_snap_id_3, :e_snap_time_dis_2, ''DUMMY_STMT_FOR_SUMMARY_STATISTICS'', -1, -1, ' || '-1, '' '', -1, -1, -1, -1, -1, :total_disk_reads total_disk_reads, ' || ':total_buf_gets total_buffer_gets, -1, -1, ''0'', 0, -1, -1 cpu_time, ' || ':time_interval elapsed_time, -1 outline_sid, ' || 'NULL outline_category, -1 plan_id, '' '' optimizer_mode, 0 optimizer_cost ' || 'from dual'; OPEN v_stat_cursor_9i FOR v_sql_stmt_92 USING v_e_snap_time_dis, v_e_snap_id, v_dbid, v_instance_number, v_b_snap_id, v_e_snap_id, v_dbid, v_instance_number, v_pect_disk_reads, v_pect_cpu_time, v_pect_parse_calls, v_pect_executions, v_e_snap_id, v_e_snap_time_dis, v_total_disk_reads, v_total_buf_gets, v_time_interval; :6 := v_stat_cursor_9i; END IF; END; ]]> PHY_READ_PCT LOG_READ_PCT CPU_TIME_PCT PAR_CALL_PCT EXEC_PCT 6 SQL_CURSOR MachineName Port SID UserName password Role mgmt_sql_plan Snap ID Collection Timestamp Plan Hash Value Explain Plan Operation Explain Plan Options Explain Plan Object Node Explain Plan Object Owner Explain Plan Object Name Explain Plan Optimizer Mode Explain Plan Id Explain Plan Parent Id Explain Plan Position Explain Plan Cost Explain Plan Cardinality Explain Plan Bytes Explain Plan Other Tag Explain Plan Partition Start Explain Plan Partition Stop Explain Plan Partition Id Explain Plan Other Explain Plan Distribution Explain Plan CPU Cost Explain Plan I-O Cost Explain Plan Temporary Space Explain Plan Object # Explain Plan Depth Address Hash Value v_b_snap_time THEN v_b_snap_id := -1; v_b_snap_time := v_e_startup_time; END IF; IF v_e_snap_id <> -1 THEN IF v_db_version >= db_version_9i THEN OPEN get_stat_total_value('CPU used when call started', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_cpu_time; CLOSE get_stat_total_value; END IF; OPEN get_stat_total_value('session logical reads', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_total_buf_gets; CLOSE get_stat_total_value; OPEN get_stat_total_value('physical reads', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_total_disk_reads; CLOSE get_stat_total_value; OPEN get_stat_total_value('parse count (total)', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_parse_calls; CLOSE get_stat_total_value; OPEN get_stat_total_value('execute count', v_e_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_e_executions; CLOSE get_stat_total_value; END IF; IF v_b_snap_id <> -1 THEN IF v_db_version >= db_version_9i THEN OPEN get_stat_total_value('CPU used when call started', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_cpu_time; CLOSE get_stat_total_value; END IF; OPEN get_stat_total_value('session logical reads', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_total_buf_gets; CLOSE get_stat_total_value; OPEN get_stat_total_value('physical reads', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_total_disk_reads; CLOSE get_stat_total_value; OPEN get_stat_total_value('parse count (total)', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_parse_calls; CLOSE get_stat_total_value; OPEN get_stat_total_value('execute count', v_b_snap_id, v_dbid, v_instance_number); FETCH get_stat_total_value INTO v_b_executions; CLOSE get_stat_total_value; END IF; v_total_disk_reads := v_e_total_disk_reads - v_b_total_disk_reads; v_total_buf_gets := v_e_total_buf_gets - v_b_total_buf_gets; v_pect_disk_reads := c_phy_read_pct * v_total_disk_reads; v_pect_buf_gets := c_log_read_pct * v_total_buf_gets; v_pect_cpu_time := c_cpu_time_pct * (v_e_cpu_time - v_b_cpu_time)*10000; v_pect_parse_calls := c_par_call_pct * (v_e_parse_calls - v_b_parse_calls); v_pect_executions := c_exec_pct * (v_e_executions - v_b_executions); IF v_db_version < db_version_92 THEN -- 901 case v_sql_stmt_9 := 'select :e_snap_id, :e_snap_time_str, ' || 'nvl(sp.plan_hash_value, -1), sp.operation, sp.options, sp.object_node, ' || 'sp.object_owner, sp.object_name, sp.optimizer, sp.id, sp.parent_id, ' || 'sp.position, sp.cost, sp.cardinality, sp.bytes, sp.other_tag, ' || 'sp.partition_start, sp.partition_stop, sp.partition_id, ' || 'sp.other, sp.distribution, sp.cpu_cost, sp.io_cost, ' || 'sp.temp_space, sp.object#, sp.depth, plans.address, plans.hash_value ' || 'from perfstat.stats$sql_plan sp, ' || '(SELECT u.plan_hash_value, MAX(u.cost) cost, MAX(s.address) address, u.hash_value ' || 'FROM perfstat.stats$sql_plan_usage u, perfstat.stats$sql_summary s, perfstat.stats$sql_summary b ' || 'WHERE s.snap_id = :e_snap_id ' || ' and s.dbid = :dbid ' || ' and s.instance_number = :instance_number ' || ' and u.hash_value = s.hash_value and u.text_subset = s.text_subset ' || ' and b.snap_id(+) = :b_snap_id ' || ' and b.dbid(+) = s.dbid ' || ' and b.instance_number(+) = s.instance_number ' || ' and b.hash_value(+) = s.hash_value ' || ' and b.address(+) = s.address ' || ' and b.text_subset(+) = s.text_subset ' || ' and s.executions - nvl(b.executions,0) > 0 ' || ' and (s.disk_reads - nvl(b.disk_reads,0) >= :pect_disk_reads ' || ' or s.cpu_time - nvl(b.cpu_time,0) >= :pect_cpu_time ' || ' or s.parse_calls - nvl(b.parse_calls,0) >= :pect_parse_calls ' || ' or s.executions - nvl(b.executions,0) >= :pect_executions)' || ' GROUP BY u.plan_hash_value, u.text_subset, u.hash_value) plans ' || 'where plans.plan_hash_value = sp.plan_hash_value'; OPEN v_plan_cursor_9i FOR v_sql_stmt_9 USING v_e_snap_id, v_e_snap_time_dis, v_e_snap_id, v_dbid, v_instance_number, v_b_snap_id, v_pect_disk_reads, v_pect_cpu_time, v_pect_parse_calls, v_pect_executions; :6 := v_plan_cursor_9i; ELSE -- 92 case v_sql_stmt_9 := 'select :e_snap_id, :e_snap_time_str, ' || 'nvl(sp.plan_hash_value, -1), sp.operation, sp.options, sp.object_node, ' || 'sp.object_owner, sp.object_name, sp.optimizer, sp.id, sp.parent_id, ' || 'sp.position, sp.cost, sp.cardinality, sp.bytes, sp.other_tag, ' || 'sp.partition_start, sp.partition_stop, sp.partition_id, ' || 'sp.other, sp.distribution, sp.cpu_cost, sp.io_cost, ' || 'sp.temp_space, sp.object#, sp.depth, plans.address, plans.hash_value ' || 'from perfstat.stats$sql_plan sp, ' || '(SELECT u.plan_hash_value, MAX(u.cost) cost, MAX(s.address) address, u.hash_value, MAX(s.snap_id) snap_id ' || 'FROM perfstat.stats$sql_plan_usage u, perfstat.stats$sql_summary s, perfstat.stats$sql_summary b ' || 'WHERE u.dbid = :dbid ' || ' AND u.snap_id = :e_snap_id_2 ' || ' AND u.instance_number = :instance_number ' || ' and s.snap_id = u.snap_id ' || ' and s.dbid = u.dbid ' || ' and s.instance_number = u.instance_number ' || ' and u.hash_value = s.hash_value and u.text_subset = s.text_subset ' || ' and b.snap_id(+) = :b_snap_id ' || ' and b.dbid(+) = s.dbid ' || ' and b.instance_number(+) = s.instance_number ' || ' and b.hash_value(+) = s.hash_value ' || ' and b.address(+) = s.address ' || ' and b.text_subset(+) = s.text_subset ' || ' and s.executions - nvl(b.executions,0) > 0 ' || ' and (s.disk_reads - nvl(b.disk_reads,0) >= :pect_disk_reads ' || ' or s.cpu_time - nvl(b.cpu_time,0) >= :pect_cpu_time ' || ' or s.parse_calls - nvl(b.parse_calls,0) >= :pect_parse_calls ' || ' or s.executions - nvl(b.executions,0) >= :pect_executions)' || ' GROUP BY u.dbid, u.snap_id, u.instance_number, u.plan_hash_value, u.text_subset, u.hash_value) plans ' || 'where plans.plan_hash_value = sp.plan_hash_value'; OPEN v_plan_cursor_9i FOR v_sql_stmt_9 USING v_e_snap_id, v_e_snap_time_dis, v_dbid, v_e_snap_id, v_instance_number, v_b_snap_id, v_pect_disk_reads, v_pect_cpu_time, v_pect_parse_calls, v_pect_executions; :6 := v_plan_cursor_9i; END IF; END IF; END; ]]> PHY_READ_PCT LOG_READ_PCT CPU_TIME_PCT PAR_CALL_PCT EXEC_PCT 6 SQL_CURSOR MachineName Port SID UserName password Role Suspended Session Suspended Session Count MachineName Port SID UserName password Role Wait by Session Count Wait Event Session Waiting for Event Count MachineName Port SID UserName password Role System Sessions Waiting Waiting Session Count Waiting Sessions Global Cache Statistics Global Cache Blocks Lost Global Cache Blocks Corrupt Global Cache Convert Time Global Cache Converts Global Cache CR Receive Time Global Cache CR Receives Global Cache Current Receive Time Global Cache Current Receives Global Cache Get Time Global Cache Gets Global Cache Average Convert Time (centi-seconds) GC Average Convert Time (cs) Global Cache Average CR Block Request Time (centi-seconds) GC Average CR Block Get Time (cs) Global Cache Average Current Block Request Time (centi-seconds) GC Average Current Block Get Time (cs) Global Cache Average Get Time (centi-seconds) GC Average Get Time (cs) MachineName Port SID UserName password Role Global Cache Statistics Time Global Cache Blocks Corrupt Global Cache Blocks Lost Global Cache Average CR Block Request Time (centi-seconds) GC Average CR Block Get Time (cs) Global Cache Average Current Block Request Time (centi-seconds) GC Average Current Block Get Time (cs) StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName Throughput Time Number of Transactions (per second) Transactions/s Physical Reads (per second) Physical Reads/s Physical Reads (per transaction) Physical Writes (per second) Phys Writes/sec Physical Writes (per transaction) Physical Reads Direct (per second) Physical Reads Direct (per transaction) Physical Writes Direct (per second) Physical Writes Direct (per transaction) Physical Reads Direct Lobs (per second) Physical Reads Direct Lobs (per transaction) Physical Reads Direct Lobs (per txn) Physical Writes Direct Lobs (per second) Physical Writes Direct Lobs (per transaction) Physical Writes Direct Lobs (per txn) Redo Generated (per second) Redo Size/sec Cumulative Logons (per second) Logons/sec Cumulative Logons (per transaction) Open Cursors (per second) Open Cursors (per transaction) User Commits (per second) User Commits (per transaction) User Rollbacks (per second) User Rollbacks (per transaction) User Calls (per second) User Calls (per transaction) Recursive Calls (per second) Recursive Calls (per transaction) Session Logical Reads (per second) Logical Reads/s Session Logical Reads (per transaction) DBWR Checkpoints (per second) BG Checkpoints (per second) Redo Writes (per second) Redo Writes (per transaction) Scans on Long Tables (per second) Scans on Long Tables (per transaction) Total Table Scans (per second) Total Table Scans (per transaction) Full Index Scans (per transaction) Full Index Scans (per second) Total Index Scans (per second) Total Index Scans (per transaction) Total Parses (per second) Total Parses (per transaction) Hard Parses (per second) Hard Parses (per transaction) Parse Failure Count (per second) Parse Failure Count (per transaction) Sorts to Disk (per second) Sorts to Disk (per transaction) Rows Processed (per sort) Executes Performed without Parses (%) Soft Parse (%) User Calls (%) Network Bytes (per second) Enqueue Timeout (per second) Enqueue Timeout (per transaction) Enqueue Waits (per second) Enqueue Waits (per transaction) Enqueue Deadlocks (per second) Enqueue Deadlocks (per transaction) Enqueue Requests (per second) Enqueue Requests (per transaction) Database Block Gets (per second) Database Block Gets (per transaction) Consistent Read Gets (per second) Consistent Read Gets (per transaction) Database Block Changes (per second) Database Block Changes (per transaction) Consistent Read Changes (per second) Consistent Read Changes (per transaction) Cons Read Changes (per txn) Consistent Read Blocks Created (per second) Cons Read Blocks Created (per sec) Consistent Read Blocks Created (per transaction) Cons Read Blocks Created (per txn) Consistent Read Undo Records Applied (per transaction) Cons Read Undo Applied (per txn) User Rollback Undo Records Applied (per second) Rollback Undo Applied (per sec) User Rollback Undo Records Applied (per transaction) Rollback Undo Applied (per txn) Leaf Node Splits (per second) Leaf Node Splits (per transaction) Branch Node Splits (per second) Branch Node Splits (per transaction) Redo Generated (per transaction) Consistent Read Undo Records Applied (per second) Cons Read Undo Applied (per sec) StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName Throughput Time Number of Transactions (per second) Transactions/s Physical Reads (per second) Physical Reads/s Physical Reads (per transaction) Physical Writes (per second) Phys Writes/sec Physical Writes (per transaction) Physical Reads Direct (per second) Physical Reads Direct (per transaction) Physical Writes Direct (per second) Physical Writes Direct (per transaction) Physical Reads Direct Lobs (per second) Physical Reads Direct Lobs (per transaction) Physical Reads Direct Lobs (per txn) Physical Writes Direct Lobs (per second) Physical Writes Direct Lobs (per transaction) Physical Writes Direct Lobs (per txn) Redo Generated (per second) Redo Size/sec Cumulative Logons (per second) Logons/sec Cumulative Logons (per transaction) Open Cursors (per second) Open Cursors (per transaction) User Commits (per second) User Commits (per transaction) User Rollbacks (per second) User Rollbacks (per transaction) User Calls (per second) User Calls (per transaction) Recursive Calls (per second) Recursive Calls (per transaction) Session Logical Reads (per second) Logical Reads/s Session Logical Reads (per transaction) DBWR Checkpoints (per second) BG Checkpoints (per second) Redo Writes (per second) Redo Writes (per transaction) Scans on Long Tables (per second) Scans on Long Tables (per transaction) Total Table Scans (per second) Total Table Scans (per transaction) Full Index Scans (per transaction) Full Index Scans (per second) Total Index Scans (per second) Total Index Scans (per transaction) Total Parses (per second) Total Parses (per transaction) Hard Parses (per second) Hard Parses (per transaction) Parse Failure Count (per second) Parse Failure Count (per transaction) Executes (per second) SQL Executions/s Sorts to Disk (per second) Sorts to Disk (per transaction) Rows Processed (per sort) Executes Performed without Parses (%) Soft Parse (%) User Calls (%) Network Bytes (per second) Enqueue Timeout (per second) Enqueue Timeout (per transaction) Enqueue Waits (per second) Enqueue Waits (per transaction) Enqueue Deadlocks (per second) Enqueue Deadlocks (per transaction) Enqueue Requests (per second) Enqueue Requests (per transaction) Database Block Gets (per second) Database Block Gets (per transaction) Consistent Read Gets (per second) Consistent Read Gets (per transaction) Database Block Changes (per second) Database Block Changes (per transaction) Consistent Read Changes (per second) Consistent Read Changes (per transaction) Cons Read Changes (per txn) Consistent Read Blocks Created (per second) Cons Read Blocks Created (per sec) Consistent Read Blocks Created (per transaction) Cons Read Blocks Created (per txn) Consistent Read Undo Records Applied (per transaction) Cons Read Undo Applied (per txn) User Rollback Undo Records Applied (per second) Rollback Undo Applied (per sec) User Rollback Undo Records Applied (per transaction) Rollback Undo Applied (per txn) Leaf Node Splits (per second) Leaf Node Splits (per transaction) Branch Node Splits (per second) Branch Node Splits (per transaction) Redo Generated (per transaction) Consistent Read Undo Records Applied (per second) Cons Read Undo Applied (per sec) Database Time (centiseconds per second) Database Time (per second) StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName Throughput Time Number of Transactions (per second) Transactions/s Physical Reads (per second) Physical Reads/s Physical Reads (per transaction) Physical Writes (per second) Phys Writes/sec Physical Writes (per transaction) Physical Reads Direct (per second) Physical Reads Direct (per transaction) Physical Writes Direct (per second) Physical Writes Direct (per transaction) Physical Reads Direct Lobs (per second) Physical Reads Direct Lobs (per transaction) Physical Reads Direct Lobs (per txn) Physical Writes Direct Lobs (per second) Physical Writes Direct Lobs (per transaction) Physical Writes Direct Lobs (per txn) Redo Generated (per second) Redo Size/sec Cumulative Logons (per second) Logons/sec Cumulative Logons (per transaction) Open Cursors (per second) Open Cursors (per transaction) User Commits (per second) User Commits (per transaction) User Rollbacks (per second) User Rollbacks (per transaction) User Calls (per second) User Calls (per transaction) Recursive Calls (per second) Recursive Calls (per transaction) Session Logical Reads (per second) Logical Reads/s Session Logical Reads (per transaction) DBWR Checkpoints (per second) BG Checkpoints (per second) Redo Writes (per second) Redo Writes (per transaction) Scans on Long Tables (per second) Scans on Long Tables (per transaction) Total Table Scans (per second) Total Table Scans (per transaction) Full Index Scans (per transaction) Full Index Scans (per second) Total Index Scans (per second) Total Index Scans (per transaction) Total Parses (per second) Total Parses (per transaction) Hard Parses (per second) Hard Parses (per transaction) Parse Failure Count (per second) Parse Failure Count (per transaction) Executes (per second) SQL Executions/s Sorts to Disk (per second) Sorts to Disk (per transaction) Rows Processed (per sort) Executes Performed without Parses (%) Soft Parse (%) User Calls (%) Network Bytes (per second) Enqueue Timeout (per second) Enqueue Timeout (per transaction) Enqueue Waits (per second) Enqueue Waits (per transaction) Enqueue Deadlocks (per second) Enqueue Deadlocks (per transaction) Enqueue Requests (per second) Enqueue Requests (per transaction) Database Block Gets (per second) Database Block Gets (per transaction) Consistent Read Gets (per second) Consistent Read Gets (per transaction) Database Block Changes (per second) Database Block Changes (per transaction) Consistent Read Changes (per second) Consistent Read Changes (per transaction) Cons Read Changes (per txn) Consistent Read Blocks Created (per second) Cons Read Blocks Created (per sec) Consistent Read Blocks Created (per transaction) Cons Read Blocks Created (per txn) Consistent Read Undo Records Applied (per transaction) Cons Read Undo Applied (per txn) User Rollback Undo Records Applied (per second) Rollback Undo Applied (per sec) User Rollback Undo Records Applied (per transaction) Rollback Undo Applied (per txn) Leaf Node Splits (per second) Leaf Node Splits (per transaction) Branch Node Splits (per second) Branch Node Splits (per transaction) Redo Generated (per transaction) Consistent Read Undo Records Applied (per second) Cons Read Undo Applied (per sec) Database Time (centiseconds per second) Database Time (per second) Average Active Sessions Average Active Sessions Average Synchronous Single-Block Read Latency (ms) Average Synchronous Single-Block Read Latency (ms) I/O Megabytes (per second) I/O Mb (per sec) I/O Requests (per second) I/O Reqs (per sec) StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName Wait Bottlenecks Time Wait Time (%) Wait Time % Host CPU Utilization (%) Active Sessions Using CPU Sessions: CPU Active Sessions Waiting: I/O Sessions: I/O Active Sessions Active Sessions Waiting: Other Sessions: Other Average Instance CPU (%) Instance CPU (%) = SYSDATE - 15/(60*24)) wcd, (SELECT intsize_csec, end_time, SUM(CASE WHEN metric_name = 'Database Wait Time Ratio' THEN value ELSE 0 END) user_wait_time_pct, SUM(CASE WHEN metric_name = 'Database Time Per Sec' THEN value / 100 ELSE 0 END) db_time_ps, SUM(CASE WHEN metric_name = 'CPU Usage Per Sec' THEN value / 100 ELSE 0 END) cpu_time_ps, SUM(CASE WHEN metric_name = 'Host CPU Utilization (%)' THEN value ELSE 0 END) host_cpu_usage_pct FROM v$sysmetric_history WHERE metric_name IN ('Database Wait Time Ratio', 'Database Time Per Sec', 'CPU Usage Per Sec', 'Host CPU Utilization (%)') AND group_id = 2 AND end_time >= SYSDATE - 15/(60*24) GROUP BY intsize_csec, end_time) md WHERE wcn.wait_class_id = wcd.wait_class_id AND wcd.intsize_csec = md.intsize_csec AND wcd.end_time = md.end_time AND :4 != 'BASIC' ORDER BY md.end_time ASC ]]> CPUTimingEnabled CPUCount TimedStatistics StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName Database Files Time File Name Average File Read Time (centi-seconds) Average File Write Time (centi-seconds) StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME file_name Database Services Time Service Name Service Response Time (per user call) (microseconds) Service CPU Time (per user call) (microseconds) = SYSDATE - 15/(60*24) AND :1 != 'BASIC' AND d.group_id = 6 GROUP BY d.end_time, d.service_name ORDER BY d.end_time ASC ]]> StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME name 'NONE') AND (m.end_time >= TO_DATE(start_date_var, 'MM-DD-YYYY HH24')) AND (m.end_time <= TO_DATE(end_date_var, 'MM-DD-YYYY HH24'))) OR ((start_date_var = 'NONE') AND (m.end_time >= SYSDATE - days_var))) ORDER BY m.end_time ASC; ELSIF metric_category_var = 'problemTbsp' THEN OPEN values_cursor FOR SELECT TO_CHAR(CAST(to_timestamp(rtime, 'MM/DD/YYYY HH24:MI:SS') AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD'), tsname, -- do case three times for min/max/avg CASE WHEN (:2 = 'bytesFree') THEN mb_free WHEN (:2 = 'pctUsed') THEN pctUsed END, CASE WHEN (:2 = 'bytesFree') THEN mb_free WHEN (:2 = 'pctUsed') THEN pctUsed END, CASE WHEN (:2 = 'bytesFree') THEN mb_free WHEN (:2 = 'pctUsed') THEN pctUsed END FROM (SELECT rtime, ts.tsname, (tsu.tablespace_usedsize / tsu.tablespace_maxsize)*100 pctUsed, (tablespace_maxsize - tablespace_usedsize)*t.block_size/1024/1024 mb_free FROM (SELECT UNIQUE ts#, tsname FROM dba_hist_tablespace_stat WHERE tsname = :3 OR 'NONE' = :3) ts, dba_hist_tbspc_space_usage tsu, dba_tablespaces t WHERE ts.ts# = tsu.tablespace_id AND ts.tsname = t.tablespace_name AND (((start_date_var is not NULL) AND (start_date_var <> 'NONE') AND (to_timestamp(rtime, 'MM/DD/YYYY HH24:MI:SS') >= TO_DATE(start_date_var, 'MM-DD-YYYY HH24')) AND (to_timestamp(rtime, 'MM/DD/YYYY HH24:MI:SS') <= TO_DATE(end_date_var, 'MM-DD-YYYY HH24'))) OR ((start_date_var = 'NONE') AND (to_timestamp(rtime, 'MM/DD/YYYY HH24:MI:SS') >= SYSDATE - days_var))) ) ORDER BY tsname, rtime ASC; END IF; :7 := values_cursor; END; ]]> MetricCategory MetricName KeyValue Duration StartDate EndDate 7 SQL_CURSOR MachineName Port SID UserName password Role md.db_time_ps) THEN null ELSE md.db_time_ps END active_sessions, CASE WHEN (0 > wcd.users) THEN null ELSE wcd.users END userio_wait_cnt, CASE WHEN (0 > md.cpu_time_ps) OR (md.cpu_time_ps > TO_NUMBER(:5) ) THEN null ELSE md.cpu_time_ps END user_cpu_time_cnt FROM (SELECT DISTINCT wait_class_id FROM v$event_name WHERE wait_class = 'User I/O' AND :6 = 'TRUE') wcn, (SELECT wait_class_id, intsize_csec, end_time, time_waited / intsize_csec users FROM v$waitclassmetric_history) wcd, (SELECT intsize_csec, end_time, SUM(CASE WHEN metric_name = 'Database Wait Time Ratio' THEN value ELSE 0 END) user_wait_time_pct, SUM(CASE WHEN metric_name = 'Database Time Per Sec' THEN value / 100 ELSE 0 END) db_time_ps, SUM(CASE WHEN metric_name = 'CPU Usage Per Sec' THEN value / 100 ELSE 0 END) cpu_time_ps, SUM(CASE WHEN metric_name = 'Host CPU Utilization (%)' THEN value ELSE 0 END) host_cpu_usage_pct FROM v$sysmetric_history WHERE metric_name IN ('Database Wait Time Ratio', 'Database Time Per Sec', 'CPU Usage Per Sec', 'Host CPU Utilization (%)') AND group_id = 2 GROUP BY intsize_csec, end_time) md WHERE wcn.wait_class_id = wcd.wait_class_id AND wcd.intsize_csec = md.intsize_csec AND wcd.end_time = md.end_time AND :7 != 'BASIC' ) ORDER BY time ASC; ELSIF metric_category_var = 'UserBlock' THEN OPEN values_cursor FOR SELECT /*+ ORDERED */ TO_CHAR(CAST(d.end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD') time, 'SID: '||d.entity_id||' Serial#: '||d.entity_sequence id, d.value FROM v$alert_types a, v$threshold_types t, v$metric_history d WHERE a.internal_metric_category = 'UserBlock' AND a.internal_metric_name = 'count' AND a.reason_id = t.alert_reason_id AND t.metrics_id = d.metric_id AND d.group_id = 4 AND :7 != 'BASIC' AND (('SID: '||d.entity_id||' Serial#: '||d.entity_sequence) = :3 OR 'NONE' = :3) ORDER BY id, d.end_time ASC; ELSIF metric_category_var = 'wait_sess_cls' THEN OPEN values_cursor FOR SELECT TO_CHAR(CAST(wcd.end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD') time, wcn.wait_class, CASE WHEN (:2 = 'avg_users_waiting_on_class') THEN wcd.average_waiter_count WHEN (:2 = 'dbtime_waitclass_pct') THEN wcd.dbtime_in_wait END FROM (SELECT DISTINCT wait_class, wait_class_id FROM v$event_name) wcn, (SELECT end_time, wait_class_id, average_waiter_count, dbtime_in_wait FROM v$waitclassmetric_history) wcd WHERE wcn.wait_class_id = wcd.wait_class_id AND (wcn.wait_class = :3 OR 'NONE' = :3) AND :7 != 'BASIC' ORDER BY wcn.wait_class, wcd.end_time ASC; ELSIF metric_category_var = 'file' THEN OPEN values_cursor FOR SELECT TO_CHAR(CAST(d.end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD'), n.name, CASE WHEN (:2 = 'read_cs') THEN d.average_read_time WHEN (:2 = 'write_cs') THEN d.average_write_time END FROM v$dbfile n, v$filemetric_history d WHERE d.file_id = n.file# AND (n.name = :3 OR 'NONE' = :3) AND :7 != 'BASIC' ORDER BY n.name, d.end_time ASC; ELSIF metric_category_var = 'service' THEN OPEN values_cursor FOR SELECT TO_CHAR(CAST(end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD') time, service_name, CASE WHEN (:2 = 'elapsed_cs') THEN elapsedpercall WHEN (:2 = 'cpu_cs') THEN cpupercall END FROM v$servicemetric_history WHERE :7 != 'BASIC' AND group_id = 6 AND (service_name = :3 OR 'NONE' = :3) ORDER BY service_name, end_time ASC; END IF; :8 := values_cursor; END; ]]> MetricCategory MetricName KeyValue CPUTimingEnabled CPUCount TimedStatistics StatisticsLevel 8 SQL_CURSOR MachineName Port SID UserName password Role ((A.value / 100) - (B.value / 100) - C.users) THEN null ELSE (A.value / 100) - (B.value / 100) - C.users END FROM v$sysmetric A, v$sysmetric B, (SELECT wcd.users FROM (SELECT DISTINCT wait_class_id FROM v$event_name WHERE wait_class = 'User I/O') wcn, (SELECT wait_class_id, intsize_csec, end_time, time_waited / intsize_csec users FROM v$waitclassmetric) wcd WHERE wcn.wait_class_id = wcd.wait_class_id) C WHERE A.metric_name = 'Database Time Per Sec' AND B.metric_name = 'CPU Usage Per Sec' AND A.group_id = 2 AND B.group_id = 2 UNION ALL -- avg user cpu time pct SELECT TO_CHAR(CAST(end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD'), 'wait_bottlenecks', 'avg_user_cpu_time_pct' ,null, group_id, CASE WHEN (0 > value) THEN null WHEN ((value / 100) > TO_NUMBER(:2)) THEN null ELSE 100 * (value / 100) / TO_NUMBER(:2) END FROM v$sysmetric WHERE metric_name = 'CPU Usage Per Sec' AND group_id = 2 UNION ALL -- userblock SELECT /*+ ORDERED */ TO_CHAR(CAST(d.end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD') time, 'UserBlock', 'count', 'SID: '||d.entity_id||' Serial#: '||d.entity_sequence id, d.group_id, d.value FROM v$alert_types a, v$threshold_types t, v$metric d WHERE a.internal_metric_category = 'UserBlock' AND a.internal_metric_name = 'count' AND a.reason_id = t.alert_reason_id AND t.metrics_id = d.metric_id AND d.group_id = 4 UNION ALL -- wait_sess_cls SELECT TO_CHAR(CAST(wcd.end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD') time, 'wait_sess_cls', label, wcn.wait_class, 0, wcd.value FROM (SELECT DISTINCT wait_class, wait_class_id FROM v$event_name) wcn, (SELECT end_time, wait_class_id,'avg_users_waiting_on_class' as label ,average_waiter_count as value FROM v$waitclassmetric UNION ALL SELECT end_time, wait_class_id,'dbtime_waitclass_pct' as label , dbtime_in_wait as value FROM v$waitclassmetric) wcd WHERE wcn.wait_class_id = wcd.wait_class_id UNION ALL -- file: read SELECT TO_CHAR(CAST(d.end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD'), 'file', 'read_cs', n.name, 0, d.average_read_time FROM v$dbfile n, v$filemetric d WHERE d.file_id = n.file# UNION ALL -- file: write SELECT TO_CHAR(CAST(d.end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD'), 'file', 'write_cs', n.name, 0, d.average_write_time FROM v$dbfile n, v$filemetric d WHERE d.file_id = n.file# UNION ALL -- service: elaspsed cs SELECT TO_CHAR(CAST(end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD') time, 'service', 'elapsed_cs', service_name, group_id, elapsedpercall FROM v$servicemetric UNION ALL -- service cpu SELECT TO_CHAR(CAST(end_time AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS TZD') time, 'service', 'cpu_cs', service_name, group_id, cpupercall FROM v$servicemetric; :5 := values_cursor; END; ]]> CPUTimingEnabled CPUCount TimedStatistics StatisticsLevel 5 SQL_CURSOR MachineName Port SID UserName password Role System Response Time Per Call Time Response Time (centi-seconds per call) = sysdate - 15 / (60 * 24) AND :1 != 'BASIC' ORDER BY m.end_time ASC ]]> StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID Role UserName password InstanceName Database Limits Time Current Logons Count Current Open Cursors Count User Limit Usage (%) Process Limit Usage (%) Session Limit Usage (%) StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID Role UserName password InstanceName Efficiency Time Buffer Cache Hit (%) Sorts in Memory (%) Redo Log Allocation Hit (%) Cursor Cache Hit (%) CPU Usage (per second) CPU Usage (per transaction) Parallel Execution Downgraded 25% or more (per second) PX Downgrade >=25% (per sec) Parallel Execution Downgraded 50% or more (per second) PX Downgrade >=50% (per sec) Parallel Execution Downgraded 75% or more (per second) PX Downgrade >=75% (per sec) Parallel Execution Downgraded (per second) PX Downgrade (per sec) Parallel Execution Downgraded to Serial (per second) PX Serial Downgrade (per sec) Parallel Execution Downgraded to Serial (per transaction) PX Serial Downgrade (per txn) Database CPU Time (%) Response Time (per transaction) Data Dictionary Hit (%) Library Cache Hit (%) Library Cache Miss (%) PGA Cache Hit (%) Row Cache Miss Ratio (%) StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID Role UserName password InstanceName Waits by Wait Class Time Wait Class Average Users Waiting Count Database Time Spent Waiting (%) = SYSDATE - 15/(60*24) AND :1 != 'BASIC' GROUP BY wcd.end_time, wcn.wait_class ORDER BY wcd.end_time ASC ]]> StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME class User Block Blocking Session ID Blocking Session Count = SYSDATE - 15/(60*24) AND d.value > 0 AND :1 != 'BASIC' ORDER BY d.end_time ASC ]]> StatisticsLevel MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME sid User Block Chain ID(lockID1_lockID2_sesID_sesSerial#) Blocking Session Info Blocking Session DB Time Blocking Session Count 0 group by id1,id2 ) ,blockers AS (select L.* ,BR.blocked_secs ,BR.blocked_count from v$lock L ,blocked_resources BR where BR.id1 = L.id1 and BR.id2 = L.id2 and L.lmode > 0 and L.block <> 0 ) select B.id1||'_'||B.id2||'_'||S.sid||'_'||S.serial# as id ,'SID,SERIAL:'||S.sid||','||S.serial#||',LOCK_TYPE:'||B.type||',PROGRAM:'||S.program||',MODULE:'||S.module||',ACTION:'||S.action||',MACHINE:'||S.machine||',OSUSER:'||S.osuser||',USERNAME:'||S.username as info ,B.blocked_secs ,B.blocked_count from v$session S ,blockers B where B.sid = S.sid ]]> MachineName Port SID UserName password Role SGA Pool Wastage Shared Pool Free (%) Large Pool Free (%) Java Pool Free (%) 1 SQL_CURSOR MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName Metrics with Adaptive Thresholds Target Type Metric Name Metric Column Key Value Adaptive Threshold (0/1) MachineName Port SID UserName password Role MachineName Port SID UserName password Role InstanceName Audit Syslog Level audit_syslog_level_max MachineName Port SID UserName password Role umask_setting perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/umaskSetting.pl umask_setting_max em_result= | NAME Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome osauth_prefix_domain perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/dbOsAuthentication.pl osauth_prefix_domain_max em_result= | NAME Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Log Archive Dest perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/logArchiveDest.pl log_archive_dest_perm_max nt_log_archive_dest_perm_max log_archive_dest_owner_max log_archive_duplex_dest_perm_max nt_log_archive_duplex_dest_perm_max log_archive_duplex_dest_owner_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome OracleHome/Bin Files Owner perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/ohBinFilesOwner.pl oh_bin_files_owner_max em_result= | NAME Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome OracleHome Folder's File Permission perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/ohFilesPerm.pl oh_files_perm_max nt_oh_files_perm_max em_result= | NAME Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome File Permission and Owner perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/files_perm_owner.pl em_result= | NAME Role tkprof_max sqlplus_max nt_tkprof_max nt_sqlplus_max tkprof_owner_max sqlplus_owner_max webcache_max nt_webcache_max webcache_owner_max snmp_ro_max nt_snmp_ro_max snmp_ro_owner_max snmp_rw_max nt_snmp_rw_max snmp_rw_owner_max wdbsvr_max nt_wdbsvr_max wdbsvr_owner_max htperm_max nt_htperm_max htowner_max xsql_max nt_xsql_max xsql_owner_max MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome OracleHome/otrace/admin/ has *.dat file[s] perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/otrace.pl em_result= | NAME Role MachineName otrace_max Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome OracleHome/Bin Executables Permission perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/ohExeBinPerm.pl oh_exebin_files_perm_max nt_oh_exebin_files_perm_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Oracle Installed on which windows platform perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/winplat.pl win_plat_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Oracle Installed drive/partition permissions perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/installationdrv.pl drive_perm_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Domain Users group in local users group perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/domainusergrp.pl domain_user_grp_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome windows tools permissions perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/wintools.pl win_tools_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Data Base parameter collection trace_files_public_max remote_listener_max audit_trail_max os_authent_prefix_max sql92_security_max db_link_global_name_max MachineName Port SID UserName password Role DB InitParameter File Permissions perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/dbInitParamFilesPerm.pl spfile_perm initora_perm nt_spfile_perm nt_initora_perm em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome DB Audit Files Permissions perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/dbAuditPerm.pl audit_file_dest_max user_dump_dest_max background_dump_dest_max core_dump_dest_max nt_audit_file_dest_max nt_user_dump_dest_max nt_background_dump_dest_max nt_core_dump_dest_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Utl File Directroy Setting perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/utlFileDir.pl utl_file_dir_max utl_file_dir_9i_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome IFiles Permissions perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/iFileRefPerm.pl oh_ifile_perm_max nt_oh_ifile_perm_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome Remote OS Roles and Authentication remote_os_roles_max remote_os_authent_max MachineName Port SID UserName password Role Remote Password File remote_login_passwordfile_max MachineName Port SID UserName password Role Statistics from Direct SGA Attach Data from the SGA METRIC_QUERY_NAME OracleHome Port SID NAME (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) Health Check Instance Status Unmounted Mounted Unavailable Maintenance State Description Instance State OracleHome SID perlBin scriptsDir OracleHome SID analysisAreaOption %perlBin%/perl %scriptsDir%/db/hanganalyze.pl %OracleHome% %SID% %scriptsDir%/db/ %analysisAreaOption% em_result= | perlBin scriptsDir MachineName Port SID analysisAreaOption (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome SYSDBAUserName SYSDBAPassword %perlBin%/perl %scriptsDir%/db/waitChains.pl %OracleHome% %SID% %analysisAreaOption% em_result= | perlBin scriptsDir MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome SYSDBAUserName SYSDBAPassword %perlBin%/perl %scriptsDir%/db/getHangTraces.pl %OracleHome% %SID% em_result= | %OracleHome% Port SID OracleHome UserName password perlBin scriptsDir VersionCategory NAME (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) %AgentOracleHome%/%MachineName%_%SID%/sysman/log %perlBin%/perl %scriptsDir%/db/sgastart.pl DSGA %ConnStr% %MachineName% %Port% %SID% %AgentOracleHome% %ADVERT_DIR% em_result= | rdbmsGroupOwner %OracleHome% Port SID OracleHome UserName password perlBin scriptsDir VersionCategory NAME (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) %AgentOracleHome%/%MachineName%_%SID%/sysman/log %perlBin%/perl %scriptsDir%/db/sgastart.pl DSGA %ConnStr% %MachineName% %Port% %SID% %AgentOracleHome% %ADVERT_DIR% stop em_result= | %OracleHome% Port SID OracleHome UserName password perlBin scriptsDir VersionCategory NAME (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) %AgentOracleHome%/%MachineName%_%SID%/sysman/log %perlBin%/perl %scriptsDir%/db/sgastart.pl DSGA %ConnStr% %MachineName% %Port% %SID% %AgentOracleHome% %ADVERT_DIR% resume em_result= | %OracleHome% Port SID OracleHome UserName password perlBin scriptsDir VersionCategory NAME (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) %AgentOracleHome%/%MachineName%_%SID%/sysman/log %perlBin%/perl %scriptsDir%/db/sgastart.pl DSGA %ConnStr% %MachineName% %Port% %SID% %AgentOracleHome% %ADVERT_DIR% status em_result= | NAME perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/gethostname.pl %HostName% em_result= | perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/getphysicalcpu.pl %perlBin% %scriptsDir% em_result= | mgmt_ha_mttr Estimated MTTR MachineName Port SID UserName password Role mgmt_ha_rac_intrconn Interconnect Name IP Address Public Source perlBin scriptsDir %perlBin%/perl %scriptsDir%/rac/dbIntrconn.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) Interconnect Traffic Instance Name db_block_size gc cr blocks received gc current blocks received gc cr blocks served gc current blocks served messages sent directly messages sent indirectly messages received actual gc cr blocks received (per sec) gc current blocks received (per sec) gc cr blocks served (per sec) gc current blocks served (per sec) messages sent directly (per sec) messages sent indirectly (per sec) messages received actual (per sec) Transfer Rate (MB/s) MachineName Port SID UserName password Role Interconnect Interface Name Interface Type perlBin scriptsDir %perlBin%/perl %scriptsDir%/rac/dbIntrconnType.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) Latest HDM Findings Task ID Finding ID Rec Type Rec Count Impact (%) Finding Message Start Time End Time = (sysdate - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = SYS_CONTEXT('USERENV','INSTANCE') AND T.TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = to_char(db_id) ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1; BEGIN SELECT dbid INTO db_id from v$database; OPEN get_latest_task_id; FETCH get_latest_task_id INTO latest_task_id; CLOSE get_latest_task_id; FOR param_info IN (SELECT parameter_value, parameter_name FROM dba_advisor_parameters_proj WHERE task_id= latest_task_id AND (parameter_name='START_TIME' OR parameter_name='END_TIME' OR parameter_name='DB_ELAPSED_TIME') ORDER BY 2) LOOP IF param_info.parameter_name = 'DB_ELAPSED_TIME' THEN db_elapsed_time:= param_info.parameter_value; ELSIF param_info.parameter_name = 'END_TIME' THEN end_time := param_info.parameter_value; ELSIF param_info.parameter_name = 'START_TIME' THEN start_time := param_info.parameter_value; END IF; END LOOP; -- open the cursor to return OPEN data_cursor FOR SELECT latest_task_id task_id, f.finding_id finding_id,DECODE(recInfo.type,NULL, 'Uncategorized',recInfo.type) rec_type, recInfo.recCount rec_count, f.impact*100/db_elapsed_time impact_pct, f.message message, TO_DATE(start_time ,'MM-DD-YYYY HH24:MI:SS') start_time, TO_DATE(end_time,'MM-DD-YYYY HH24:MI:SS') end_time FROM dba_advisor_findings f, (SELECT finding_id, count(r.rec_id) recCount,r.type FROM dba_advisor_recommendations r WHERE task_id=latest_task_id GROUP BY r.finding_id, r.type) recInfo WHERE f.task_id=latest_task_id AND f.type<>'INFORMATION' AND f.type<>'WARNING' AND f.parent=0 AND f.finding_id=recInfo.finding_id (+) AND :1 != 'BASIC' ORDER BY f.finding_id; :2 := data_cursor; END; ]]> StatisticsLevel 2 SQL_CURSOR MachineName Port SID UserName password Role Latest HDM Findings Task ID Finding ID Rec Type Rec Count Impact (%) Finding Message Start Time End Time Finding Count Finding Name Finding Count = (sysdate - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = SYS_CONTEXT('USERENV','INSTANCE') AND T.TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = to_char(db_id) ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1; BEGIN SELECT dbid INTO db_id from v$database; OPEN get_latest_task_id; FETCH get_latest_task_id INTO latest_task_id; CLOSE get_latest_task_id; FOR param_info IN (SELECT parameter_value, parameter_name FROM dba_advisor_parameters_proj WHERE task_id= latest_task_id AND (parameter_name='START_TIME' OR parameter_name='END_TIME') ORDER BY 2) LOOP IF param_info.parameter_name = 'END_TIME' THEN end_time := param_info.parameter_value; ELSIF param_info.parameter_name = 'START_TIME' THEN start_time := param_info.parameter_value; END IF; END LOOP; -- open the cursor to return OPEN data_cursor FOR SELECT latest_task_id task_id, f.finding_id finding_id,DECODE(recInfo.type,NULL, 'Uncategorized',recInfo.type) rec_type, recInfo.recCount rec_count, f.perc_active_sess impact_pct, f.message message, TO_DATE(start_time ,'MM-DD-YYYY HH24:MI:SS') start_time, TO_DATE(end_time,'MM-DD-YYYY HH24:MI:SS') end_time, history.finding_count finding_count, f.finding_name finding_name, f.active_sessions active_sessions FROM dba_addm_findings f, (SELECT finding_id, count(r.rec_id) recCount,r.type FROM dba_advisor_recommendations r WHERE task_id=latest_task_id GROUP BY r.finding_id, r.type) recInfo, (select count(f_all.task_id) finding_count, f_curr.finding_name FROM (select finding_name from dba_advisor_findings where task_id=latest_task_id) f_curr, (select t.task_id,i.local_task_id,t.end_time, t.begin_time from dba_addm_tasks t,dba_addm_instances i where t.end_time>sysdate -1 AND t.task_id=i.task_id AND i.instance_number=sys_context('USERENV','INSTANCE') AND t.requested_analysis='INSTANCE' ) tasks, dba_advisor_findings f_all WHERE f_all.task_id=tasks.task_id AND f_all.finding_name=f_curr.finding_name AND f_all.type<>'INFORMATION' AND f_all.type<>'WARNING' AND f_all.parent=0 GROUP BY f_curr.finding_name) history WHERE f.task_id=latest_task_id AND f.type<>'INFORMATION' AND f.type<>'WARNING' AND f.filtered<>'Y' AND f.parent=0 AND f.finding_id=recInfo.finding_id (+) AND f.finding_name=history.finding_name ORDER BY f.finding_id; :2 := data_cursor; END; ]]> StatisticsLevel 2 SQL_CURSOR MachineName Port SID UserName password Role latest_hdm_metric_helper Task Available 1 SQL_CURSOR MachineName Port SID UserName password Role latest_hdm_metric_helper Task Available Task Count sysdate -1 AND i.instance_number=sys_context('USERENV','INSTANCE' ) AND requested_analysis='INSTANCE') latest_tasks; :1 := data_cursor; END; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role Text Index Log Content Content perlBin scriptsDir em_result= ConvertFromCharset needCharsetConvert logFileToView sizeToView startTime endTime %perlBin%/perl %scriptsDir%/txt/textIndexLogViewer.pl %logFileToView% %sizeToView% %startTime% %endTime% Text Index Log Remover Content perlBin scriptsDir em_result= ConvertFromCharset needCharsetConvert logFileToRemove %perlBin%/perl %scriptsDir%/txt/textIndexLogRemover.pl %logFileToRemove% perlBin scriptsDir emd_email_address emd_from_email_address emd_email_gateway REPOSITORY_URL agentVersion NAME TYPE UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) %perlBin%/perl %scriptsDir%/pwdexpiry.pl %REPOSITORY_URL% %emd_email_address% %emd_from_email_address% %emd_email_gateway% %agentVersion% MachineName Port SID UserName password Role Streams Pool Usage Streams Pool Full ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME streams_pool_full