- To Find the Init Parameters for a monitored database use the following SQL
select host_name, target_name, collection_timestamp,
name, isdefault, value, datatype
from sysman.MGMT$DB_INIT_PARAMS
where target_name like 'yourdbname%';
- To find the number of data files configured and the actual number of data files for the monitored database.
select a.target_name, db1.db_name, db2.db_files "Configured DB Files", count(*) "Actual # of Datafiles" from MGMT$DB_DATAFILES a, ( select value db_name, target_name from sysman.mgmt$db_init_params a1 where a1.name = 'db_name') db1 , ( select value db_files, target_name from sysman.mgmt$db_init_params a1 where a1.name = 'db_files') db2 where a.target_name = db1.target_name and a.target_name = db2.target_name group by a.target_name, db1.db_name, db2.db_files order by 4 desc;
- To find the characterset used across all the monitored database
select * from sysman.MGMT$TARGET_PROPERTIES
where property_name = 'ConvertFromCharset';
-- MGMT$TARGET_PROPERTIES corresponds to database_properties view
- To find the SGA parameters for a monitored database use the following SQL
select host_name, target_name, collection_timestamp, sganame, sgasize
from sysman.mgmt$db_sga
where target_name like 'yourdbname%';
- To find outstanding Alerts for a monitored database
select t.type_display_name, t.target_name, t.host_name, m.metric_name, m.metric_column, m.column_label, m.key_value, m.collection_timestamp, m.message, m.alert_state from sysman.MGMT$ALERT_CURRENT m, sysman.mgmt$target t where t.target_guid = m.target_guid and t.target_name like 'yourdbname%' order by m.collection_timestamp desc;
- To find out the Target up and down status
select t.target_name, m.start_timestamp, m.end_timestamp, m.availability_status from sysman.MGMT$AVAILABILITY_HISTORY m, sysman.mgmt$target t where t.target_guid = m.target_guid and t.target_name like 'yourdbname%' order by start_timestamp desc;
- Other useful information from Grid
MGMT$OS_SUMMARY - to find the OS patch, swap and version
-- Issue this query to see several other tables that holds useful info and you can query against it to see what you get.
select *
from dba_objects
where objecT_name like 'MGMT%'
and owner = 'SYSMAN'
and object_type in ('TABLE', 'VIEW');
- To find out the Interconnect network details
select *
from sysman.MGMT$CLUSTER_INTERCONNECTS
where host_name = 'your agent host name fqdn';
- Detailed Interconnect Traffic for RAC Instances (Past 7 Days)
select m.target_name, m.metric_name, m.metric_column, m.collection_timestamp,
m.value*1024 "Traffic KB/s", m.key_value
from mgmt$metric_details m,
mgmt$target t
where (t.target_type='rac_database' OR
(t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 = 'RACINST'))
and m.metric_column='interconnect_rate'
and m.target_guid = t.target_guid
and m.collection_timestamp >= sysdate -7
and m.target_name like 'yourdbname%';
- Historical Interconnet Traffic for RAC instances (Average, Min, Max)
select m.target_name, m.metric_name, m.metric_column, m.rollup_timestamp, round(m.average*1024,2) "Average Traffic KB/s", round(m.minimum*1024,2) "Minimum Traffic KB/s", round(m.maximum*1024,2) "Maximum Traffic KB/s" from mgmt$metric_daily m, mgmt$target t where (t.target_type='rac_database' OR (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 = 'RACINST')) and m.metric_column='interconnect_rate' and m.target_guid = t.target_guid and m.rollup_timestamp >= sysdate - 60 and m.target_name like 'yourdbname%';
The Grid Console stores bulk of its collected metrics in the following tables
MGMT$METRIC_DETAILS - Stores details upto 7 days MGMT$METRIC_CURRENT - Stores the current metrics from the past 24 hours MGMT$METRIC_HOURLY - Stores details upto 30 days in a hourly snapshot with AVG, MIN and MAX values. MGMT$METRIC_DAILY - Stores the metrics in a daily snapshot format with AVG, MIN and MAX values.
The following are the details captured in the metrics tables and you can use the above query appropriately as needed by replacing the metric_column WHERE clause.
1. Interconnect Traffic for RAC (metric_column = 'interconnect_rate')
2. Archive Space Usage ( metric_column = 'archTotal' for Total Archive Space, metric_column = 'archUsed' for actual used space +
metric_name='archFull')
3. Tablespace Usage ( metric_column = 'spaceAllocated', 'spaceUsed' + metric_name='tbspAllocation')
This table covers the other metrics you can use for current and historical analysis.
| COLUMN_LABEL | METRIC_NAME | METRIC_COLUMN |
| Number of Capture Processes | streams_processes_count | CAPTURE_COUNT |
| Tablespace Allocated Space (MB) | tbspAllocation | spaceAllocated |
| Physical Writes Direct (per transaction) | instance_throughput | physwritesdir_pt |
| Global Cache Blocks Corrupt | rac_global_cache | corrupt |
| Enqueue Deadlocks (per second) | instance_throughput | enqdeadlocks_ps |
| Physical Reads Direct Lobs (per transaction) | instance_throughput | physreadslob_pt |
| Physical Writes Direct (per second) | instance_throughput | physwritesdir_ps |
| Archive Area Used (KB) | archFull | archUsed |
| Cursor Cache Hit (%) | instance_efficiency | cursorcachehit_pct |
| Redo Log Allocation Hit (%) | instance_efficiency | redologalloc_hit_pct |
| Database CPU Time (%) | instance_efficiency | cpu_time_pct |
| Physical Reads Direct (per transaction) | instance_throughput | physreadsdir_pt |
| User Limit Usage (%) | Database_Resource_Usage | user_limit |
| Library Cache Miss (%) | instance_efficiency | libcache_miss_pct |
| Physical Writes Direct Lobs (per transaction) | instance_throughput | physwriteslob_pt |
| User Rollbacks (per transaction) | instance_throughput | rollbacks_pt |
| Total Archive Area (KB) | archFull | archTotal |
| Physical Writes Direct Lobs (per second) | instance_throughput | physwriteslob_ps |
| User Commits (per transaction) | instance_throughput | commits_pt |
| Row Cache Miss Ratio (%) | instance_efficiency | dictionarymiss_pct |
| Parallel Execution Downgraded to Serial (per second) | instance_efficiency | pxdwngrdserial_ps |
| Data Block Corruption Alert Log Error | alertLog | blockCorruptErrStack |
| Number of Propagation Jobs | streams_processes_count | PROP_COUNT |
| Usable Flash Recovery Area (%) | ha_flashrecovery | usable_area |
| Media Failure Alert Log Error Status | alertLogStatus | mediaFailureErrors |
| PGA Cache Hit (%) | instance_efficiency | pgacachehit_pct |
| Enqueue Timeout (per transaction) | instance_throughput | enqtimeouts_pt |
| Parallel Execution Downgraded to Serial (per transaction) | instance_efficiency | pxdwngrdserial_pt |
| Service Response Time (per user call) (microseconds) | service | elapsed_cs |
| Session Limit Usage (%) | Database_Resource_Usage | session_usage |
| Response Time (per transaction) | instance_efficiency | response_time_pt |
| Total Index Scans (per second) | instance_throughput | indxscanstotal_ps |
| Session Logical Reads (per transaction) | instance_throughput | logreads_pt |
| User Calls (%) | instance_throughput | usercall_pct |
| Database Block Changes (per second) | instance_throughput | dbblkchanges_ps |
| Redo Writes (per second) | instance_throughput | redowrites_ps |
| Open Cursors (per second) | instance_throughput | opncurs_ps |
| Average Instance CPU (%) | wait_bottlenecks | avg_user_cpu_time_pct |
| Network Bytes (per second) | instance_throughput | networkbytes_ps |
| Parse Failure Count (per transaction) | instance_throughput | failedparses_pt |
| Consistent Read Blocks Created (per transaction) | instance_throughput | crblks_pt |
| Enqueue Timeout (per second) | instance_throughput | enqtimeouts_ps |
| Connections Refused (per min) | Load | refConns |
| Tablespace Space Used (%) | problemTbsp | pctUsed |
| Full Index Scans (per second) | instance_throughput | indxscansfull_ps |
| Free Archive Area (KB) | archFull | archAvail |
| Enqueue Deadlocks (per transaction) | instance_throughput | enqdeadlocks_pt |
| User Rollback Undo Records Applied (per second) | instance_throughput | userrollbackundorec_ps |
| Total Parses (per second) | instance_throughput | parses_ps |
| Database Block Gets (per second) | instance_throughput | dbblkgets_ps |
| Current Open Cursors Count | Database_Resource_Usage | opencursors |
| Active Sessions Waiting: I/O | wait_bottlenecks | userio_wait_cnt |
| Library Cache Hit (%) | instance_efficiency | libcache_hit_pct |
| Transfer Rate (MB/s) | ha_rac_intrconn_traffic | interconnect_rate |
| Hard Parses (per transaction) | instance_throughput | hardparses_pt |
| Total Dump Area (KB) | dumpFull | dumpTotal |
| Total Table Scans (per second) | instance_throughput | tabscanstotal_ps |
| Global Cache Average CR Block Request Time (centi-seconds) | rac_global_cache | cr_request_cs |
| Process Limit Usage (%) | Database_Resource_Usage | process_usage |
| Active Sessions Waiting: Other | wait_bottlenecks | other_wait_cnt |
| User Calls (per transaction) | instance_throughput | usercalls_pt |
| Session Terminated Alert Log Error Status | alertLogStatus | sessTerminateErrors |
| Java Pool Free (%) | sga_pool_wastage | java_free_pct |
| Session Logical Reads (per second) | instance_throughput | logreads_ps |
| Audited User Session Count | UserAudit | Session Count |
| Response Time (msec) | Response | tnsPing |
| Connections Established (per min) | Load | estConns |
| Audited User Host | UserAudit | machine |
| Total Table Scans (per transaction) | instance_throughput | tabscanstotal_pt |
| Physical Reads (per second) | instance_throughput | physreads_ps |
| Leaf Node Splits (per second) | instance_throughput | leafnodesplits_ps |
| Redo Writes (per transaction) | instance_throughput | redowrites_pt |
| Recursive Calls (per second) | instance_throughput | recurscalls_ps |
| Physical Writes (per second) | instance_throughput | physwrites_ps |
| Consistent Read Changes (per transaction) | instance_throughput | consistentreadchanges_pt |
| Global Cache Average Current Block Request Time (centi-seconds) | rac_global_cache | currentgets_cs |
| Host CPU Utilization (%) | wait_bottlenecks | host_cpu_usage_pct |
| Soft Parse (%) | instance_throughput | softparse_pct |
| Database Block Changes (per transaction) | instance_throughput | dbblkchanges_pt |
| Hard Parses (per second) | instance_throughput | hardparses_ps |
| Physical Reads Direct (per second) | instance_throughput | physreadsdir_ps |
| Parse Failure Count (per second) | instance_throughput | failedparses_ps |
| Scans on Long Tables (per second) | instance_throughput | tabscanslong_ps |
| Branch Node Splits (per second) | instance_throughput | branchnodesplits_ps |
| Global Cache Blocks Lost | rac_global_cache | lost |
| Archiver Hung Alert Log Error Status | alertLogStatus | archiveHungErrors |
| Consistent Read Gets (per transaction) | instance_throughput | consistentreadgets_pt |
| Data Dictionary Hit (%) | instance_efficiency | dictionaryhit_pct |
| Consistent Read Blocks Created (per second) | instance_throughput | crblks_ps |
| Generic Alert Log Error | alertLog | genericErrStack |
| Number of recommendations | segment_advisor_count | recommendations |
| Flash Recovery Area | ha_flashrecovery | flash_recovery_area |
| Number of Apply Processes | streams_processes_count | APPLY_COUNT |
| Tablespace Used Space (MB) | tbspAllocation | spaceUsed |
| Cumulative Logons (per transaction) | instance_throughput | logons_pt |
| Dump Area Used (KB) | dumpFull | dumpUsed |
| Parallel Execution Downgraded 75% or more (per second) | instance_efficiency | pxdwngrd75_ps |
| Service CPU Time (per user call) (microseconds) | service | cpu_cs |
| Full Index Scans (per transaction) | instance_throughput | indxscansfull_pt |
| Executes Performed without Parses (%) | instance_throughput | executeswoparse_pct |
| System Response Time (centi-seconds) | system_response_time_per_call | time_cs |
| Data Block Corruption Alert Log Error Status | alertLogStatus | blockCorruptErrors |
| CPU Usage (per transaction) | instance_efficiency | cpuusage_pt |
| Large Pool Free (%) | sga_pool_wastage | large_free_pct |
| User Rollback Undo Records Applied (per transaction) | instance_throughput | userrollbackundorec_pt |
| Sorts in Memory (%) | instance_efficiency | inmem_sort_pct |
| CPU Usage (per second) | instance_efficiency | cpuusage_ps |
| Consistent Read Gets (per second) | instance_throughput | consistentreadgets_ps |
| Baseline SQL Response Time | sql_response | baselineTime |
| Active Sessions Using CPU | wait_bottlenecks | user_cpu_time_cnt |
| Alert Log Error Trace File | alertLog | traceFileName |
| Media Failure Alert Log Error | alertLog | mediaFailureErrStack |
| Capture Processes Having Errors | streams_processes_count | CAPTURE_ERROR_COUNT |
| Apply Processes Having Errors | streams_processes_count | APPLY_ERROR_COUNT |
| Datafiles Need Media Recovery | ha_recovery | datafiles_need_recovery |
| Enqueue Requests (per second) | instance_throughput | enqreqs_ps |
| Sorts to Disk (per second) | instance_throughput | sortsdisk_ps |
| Generic Alert Log Error Status | alertLogStatus | genericErrors |
| Redo Generated (per second) | instance_throughput | redosize_ps |
| Rows Processed (per sort) | instance_throughput | rows_psort |
| Redo Generated (per transaction) | instance_throughput | redosize_pt |
| Enqueue Waits (per transaction) | instance_throughput | enqwaits_pt |
| BG Checkpoints (per second) | instance_throughput | bgcheckpoints_ps |
| Enqueue Waits (per second) | instance_throughput | enqwaits_ps |
| Current SQL Response Time | sql_response | currentTime |
| Current Logons Count | Database_Resource_Usage | logons |
| Database Block Gets (per transaction) | instance_throughput | dbblkgets_pt |
| Physical Writes (per transaction) | instance_throughput | physwrites_pt |
| Cumulative Logons (per second) | instance_throughput | logons_ps |
| Executes (per second) | instance_throughput | executions_ps |
| Archiver Hung Alert Log Error | alertLog | archiveHungErrStack |
| Alert Log Name | alertLog | alertLogName |
| Tablespace Free Space (MB) | problemTbsp | bytesFree |
| SQL Response Time (%) | sql_response | time |
| Leaf Node Splits (per transaction) | instance_throughput | leafnodesplits_pt |
| Open Cursors (per transaction) | instance_throughput | opncurs_pt |
| Consistent Read Undo Records Applied (per transaction) | instance_throughput | crundorecs_pt |
| Parallel Execution Downgraded 25% or more (per second) | instance_efficiency | pxdwngrd25_ps |
| Branch Node Splits (per transaction) | instance_throughput | branchnodesplits_pt |
| DBWR Checkpoints (per second) | instance_throughput | dbwrcheckpoints_ps |
| Free Dump Area (KB) | dumpFull | dumpAvail |
| Wait Time (%) | wait_bottlenecks | user_wait_time_pct |
| Parallel Execution Downgraded 50% or more (per second) | instance_efficiency | pxdwngrd50_ps |
| Dump Area Used (%) | dumpFull | dumpUsedPercent |
| Consistent Read Changes (per second) | instance_throughput | consistentreadchanges_ps |
| Session Terminated Alert Log Error | alertLog | sessTerminateErrStack |
| Propagation Errors | streams_processes_count | PROP_ERROR_COUNT |
| Corrupt Data Blocks | ha_recovery | corrupt_data_blocks |
| Datafiles with Error | ha_recovery | missing_media_files |
| Consistent Read Undo Records Applied (per second) | instance_throughput | crundorecs_ps |
| User Commits (per second) | instance_throughput | commits_ps |
| Shared Pool Free (%) | sga_pool_wastage | shared_free_pct |
| User Calls (per second) | instance_throughput | usercalls_ps |
| Status | Response | Status |
| Recursive Calls (per transaction) | instance_throughput | recurscalls_pt |
| Database Time (centiseconds per second) | instance_throughput | dbtime_ps |
| Total Index Scans (per transaction) | instance_throughput | indxscanstotal_pt |
| Average Users Waiting Count | wait_sess_cls | avg_users_waiting_on_class |
| User Rollbacks (per second) | instance_throughput | rollbacks_ps |
| Total Parses (per transaction) | instance_throughput | parses_pt |
| Physical Reads (per transaction) | instance_throughput | physreads_pt |
| Scans on Long Tables (per transaction) | instance_throughput | tabscanslong_pt |
| Sorts to Disk (per transaction) | instance_throughput | sortsdisk_pt |
| Parallel Execution Downgraded (per second) | instance_efficiency | pxdwngrd_ps |
| Archive Area Used (%) | archFull | archUsedPercent |
| Physical Reads Direct Lobs (per second) | instance_throughput | physreadslob_ps |
| Buffer Cache Hit (%) | instance_efficiency | bufcachehit_pct |
| Number of Transactions (per second) | instance_throughput | transactions_ps |
| Database Time Spent Waiting (%) | wait_sess_cls | dbtime_waitclass_pct |
| Enqueue Requests (per transaction) | instance_throughput | enqreqs_pt |
| User Logon Time (msec) | Response | userLogon |
| Audited User | UserAudit | username |
No comments:
Post a Comment