| General Information |
| Library Note |
| Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can‘t make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources. |
|
| Page Notes |
V$ view are views based on X$ arrays. The GV$ views are Global V$ views that have, as their first column, the instance identifier (INST_ID). You should always use GV$ rather than V$ where-ever possible. To examine their SQL definitions open the file $ORACLE_HOME/rdbms/admin/cdfixed.sql. |
| Dynamic Performance View Growth |
| Version |
Number Views |
| 6 |
23 |
| 7.1 |
72 |
| 8.1 |
185 |
| 9.2 |
259 |
| 10.1 |
340 |
| 10.2 |
372 |
| 11.1 |
484 |
| 11.2 |
525 |
| 12.1 |
606 |
| 12.2 |
746 |
|
| Categories |
| Sections |
| Advanced Queuing (AQ) |
Fixed Views |
Parameters |
| Advisors |
Flashback Database |
Processes |
| Archive Logging |
Flashback Recovery Area |
RAC |
| ASH |
Flashback Transaction |
Redo Logs |
| ASM |
GoldenGate |
Resource Usage |
| Backup & Recovery |
Hang Detection |
Result Cache |
| Buffers |
Health Management |
Rollback/Undo Segments |
| Caches |
I/O |
Rules |
| Connection Pool |
In-Memory Database |
Security & Privileges |
| Container Database |
Instance |
Services |
| Control File |
Keywords |
Sessions |
| Cursors & SQL Statements |
Latches & Locks |
Shared Server |
| Data Guard & Standbys |
Log Miner |
Storage |
| Database Links |
Materialized View Refresh |
Streams |
| Data Pump |
Memory Utilization |
System Statistics |
| Diagnostics |
Metrics |
Temp Space |
| Direct Loader |
Miscellaneous |
Transportable Platform |
| Direct NFS |
OLAP |
Waits |
| Edition Based Redefinition |
Optimizer |
Workload Management |
| Exadata |
Oracle File System (OFS) |
Workload Replay |
| Feature Usage |
Parallel Query |
XStreams |
|
| Query to identify V$ views not duplicated as GV$ views |
set linesize 121
SELECT object_name
FROM dba_objects_ae
WHERE object_name LIKE ‘V%$%‘
AND object_type = ‘VIEW‘
MINUS
SELECT SUBSTR(object_name, 2)
FROM dba_objects_ae
WHERE object_name LIKE ‘GV%$%‘
AND object_type = ‘VIEW‘
ORDER BY 1; |
| |
| Advanced Queuing |
| |
| gv$aq |
gv$aq_server_pool |
| gv$aq1 |
gv$aq_sharded_subscriber_stat  |
| gv$aq_background_coordinator |
gv$aq_subscriber_load |
| gv$aq_bmap_nondur_subscribers |
gv$aq_uncached_subshards |
| gv$aq_cached_subshards |
gv$buffered_publishers |
| gv$aq_cross_instance_jobs |
gv$buffered_queues |
| gv$aq_inactive_subshards |
gv$buffered_subscribers |
| gv$aq_job_coordinator |
gv$persistent_publishers |
| gv$aq_message_cache |
gv$persistent_qmn_cache |
| gv$aq_message_cache_advice |
gv$persistent_queues |
| gv$aq_message_cache_staat |
gv$persistent_subscribers |
| gv$aq_msgbm |
gv$qmon_coordinator_stats |
| gv$aq_nondur_registrations |
gv$qmon_server_stats |
| gv$aq_nondur_subscriber |
gv$qmon_tasks |
| gv$aq_nondur_subscriber_lwm |
gv$qmon_task_stats |
| gv$aq_notification_clients |
gv$queue |
| gv$aq_remote_dequqeue_affinity |
gv$queueing_mth |
|
| |
| Advisors |
| |
| gv$advisor_current_sqlplan |
gv$sga_target_advice |
| gv$advisor_progress |
gv$shared_pool_advice |
| gv$java_pool_advice |
v$stats_advisor_actions |
| gv$memory_target_advice |
v$stats_advisor_findings |
| gv$mttr_target_advice |
v$stats_advisor_rationales |
| gv$pga_target_advice |
v$stats_advisor_recs |
| gv$pga_target_advice_histogram |
v$stats_advisor_rules |
| gv$px_buffer_advice |
|
|
| |
| Archive Logging |
| |
| gv$archive |
gv$archive_processes |
| gv$archived_log |
gv$proxy_archivedlog |
| gv$archive_dest |
v$proxy_archivelog_details |
| gv$archive_dest_status |
v$proxy_archivelog_summary |
| gv$archive_gap |
|
|
| |
| ASH |
| |
| gv$active_session_history |
gv$max_active_sess_target_mth |
| gv$ash_info |
|
|
| |
| ASM |
| |
| gv$asm_acfsrepl |
gv$asm_dbclone_info |
| gv$asm_acfsrepltag |
gv$asm_disk |
| gv$asm_acfssnapshots |
gv$asm_diskgroup |
| gv$asm_acfstag |
gv$asm_diskgroup_sparse |
| gv$asm_acfsvolumes |
gv$asm_diskgroup_stat |
| gv$asm_acfs_encryption_info |
gv$asm_disk_iostat |
| gv$asm_acfs_security_info |
gv$asm_disk_sparse |
| gv$asm_acfs_sec_admin |
gv$asm_disk_sparse_stat |
| gv$asm_acfs_sec_cmdrule |
gv$asm_disk_stat |
| gv$asm_acfs_sec_realm |
gv$asm_estimate |
| gv$asm_acfs_sec_realm_filter |
gv$asm_file |
| gv$asm_acfs_sec_realm_group |
gv$asm_filegroup |
| gv$asm_acfs_sec_realm_user |
gv$asm_filegroup_file |
| gv$asm_acfs_sec_rule |
gv$asm_filegroup_property |
| gv$asm_acfs_sec_ruleset |
gv$asm_filesystem |
| gv$asm_acfs_sec_ruleset_rule |
gv$asm_operation |
| gv$asm_alias |
gv$asm_quotagroup |
| gv$asm_attribute |
gv$asm_template |
| gv$asm_audit_cleanup_jobs |
gv$asm_user |
| gv$asm_audit_clean_events |
gv$asm_usergroup |
| gv$asm_audit_config_params |
gv$asm_usergroup_member |
| gv$asm_audit_last_arch_ts |
gv$asm_volume |
| gv$asm_client |
gv$asm_volume_stat |
|
| |
| Backup and Recovery (RMAN) |
| |
| gv$backup |
gv$db_transportable_platform |
| v$backup_archivelog_details |
gv$deleted_object |
| v$backup_archivelog_summary |
gv$fast_start_servers |
| gv$backup_async_io |
gv$fast_start_transactions |
| v$backup_controlfile_details |
gv$database_block_corruption |
| v$backup_controlfile_summary |
gv$instance_recovery |
| v$backup_copy_details |
v$proxy_copy_details |
| v$backup_copy_summary |
v$proxy_copy_summary |
| gv$backup_corruption |
gv$proxy_datafile |
| gv$backup_datafile |
v$recovery_area_usage |
| v$backup_datafile_details |
v$recovery_file_dest |
| v$backup_datafile_summary |
gv$recovery_file_status |
| gv$backup_device |
gv$recovery_log |
| v$backup_files |
gv$recovery_progress |
| gv$backup_nonlogged |
gv$recovery_slave |
| gv$backup_piece |
gv$recovery_status |
| v$backup_piece_details |
gv$recover_file |
| gv$backup_redolog |
v$restore_range |
| gv$backup_set |
v$rman_backup_job_details |
| v$backup_set_details |
v$rman_backup_subjob_details |
| v$backup_set_summary |
v$rman_backup_type |
| gv$backup_spfile |
gv$rman_compression_algorithm |
| v$backup_spfile_details |
gv$rman_configuration |
| v$backup_spfile_summary |
v$rman_encryption_algorithms |
| gv$backup_sync_io |
gv$rman_output |
| v$block_change_tracking |
gv$tsm_sessions |
| gv$copy_corruption |
v$rman_status |
| gv$copy_nonlogged |
v$sbt_restore_range |
| gv$database_incarnation |
gv$shadow_datafile |
| gv$datafile_copy |
v$unusable_backupfile_details |
|
| |
| Buffers |
| |
| gv$bh |
gv$buffer_pool_statistics |
| gv$buffer_pool |
|
|
| |
| Caches |
| bsp = block server background process |
| gv$bsp |
gv$library_cache_memory |
| gv$cache |
gv$map_comp_list |
| v$cache_lock |
gv$map_element |
| v$cache_transfer |
gv$map_ext_element |
| gv$class_cache_transfer |
gv$map_file |
| gv$class_ping |
gv$map_file_extent |
| gv$cr_block_server |
gv$map_file_io_stack |
| gv$current_block_server |
gv$map_library |
| gv$db_cache_advice |
gv$map_subelement |
| gv$db_object_cache |
gv$propagation_receiver |
| gv$gcshvmaster_info |
gv$propagation_sender |
| gv$gcspfmaster_info |
gv$rowcache |
| gv$hvmaster_info |
gv$rowcache_parent |
| gv$instance_cache_transfer |
gv$rowcache_subordinate |
| gv$java_library_cache_memory |
gv$subcache |
| gv$javapool |
gv$temp_ping |
| gv$librarycache |
|
|
| |
| Connection Pool |
| |
| gv$cpool_cc_info |
gv$cpool_conn_info |
| gv$cpool_cc_stats |
gv$cpool_stats |
|
| |
| Container Database |
| |
| gv$clonedfile |
gv$con_system_wait_class |
| gv$containers |
gv$con_sys_time_model |
| gv$con_event_histogram_micro |
v$diag_pdb_problem |
| gv$con_sysmetric |
v$diag_pdb_space_mgmt |
| gv$con_sysmetric_history |
gv$pdbs |
| gv$con_sysmetric_summary |
gv$pdb_incarnation |
| gv$con_sysstat |
gv$proxy_pdb_targets |
| gv$con_system_event |
gv$zonemap_usage_stats |
|
| |
| Control Files |
| |
| gv$controlfile |
gv$controlfile_record_section |
|
| |
| Cursors and SQL Statements |
| |
| gv$object_dependency |
gv$sql_monitor_sesstat |
| gv$open_cursor |
gv$sql_monitor_statname |
| gv$sql |
gv$sql_plan |
| gv$sqlarea |
gv$sql_plan_monitor |
| gv$sqlarea_plan_hash |
gv$sql_plan_statistics |
| gv$sqlstats |
gv$sql_plan_statistics_all |
| gv$sqlstats_plan_hash |
gv$sql_redirection |
| gv$sqltext |
gv$sql_diag_repository |
| gv$sqltext_with_newlines |
gv$sql_diag_repository_reason |
| gv$sql_bind_capture |
gv$sql_shared_cursor |
| gv$sql_bind_data |
gv$sql_shared_memory |
| gv$sql_bind_metadata |
gv$sql_workarea |
| gv$sql_cursor |
gv$sql_workarea_active |
| gv$sql_join_filter |
gv$sql_workarea_histogram |
| gv$sql_monitor |
|
|
| |
| Data Guard & Standby Databases |
| |
| gv$dataguard_config |
gv$logstdby |
| gv$dataguard_process |
gv$logstdby_process |
| gv$dataguard_stats |
gv$logstdby_progress |
| v$dataguard_status |
gv$logstdby_state |
| gv$dg_broker_config |
gv$logstdby_stats |
| gv$foreign_archived_log |
gv$logstdby_transaction |
| gv$fs_failover_histogram |
gv$managed_standby |
| gv$fs_failover_observers |
gv$rfs_thread |
| gv$fs_failover_stats |
gv$standby_log |
| gv$fs_observer_histogram |
v$standby_event_histogram |
|
| |
| Database Links & Heterogeneous Services |
| |
| gv$dblink |
gv$hs_parameter |
| gv$hs_agent |
gv$hs_session |
|
| |
| Data Pump |
| |
| gv$datapump_job |
gv$datapump_session |
|
| |
| Diagnostics |
| |
| v$diag_adr_control |
v$diag_sess_sql_trace_records |
| v$diag_adr_control_aux |
gv$diag_sql_trace_records |
| v$diag_adr_invalidation |
v$diag_sweeperr |
| v$diag_alert_ext (source is x$dbgalertext) |
gv$diag_trace_file |
| v$diag_ams_xaction |
gv$diag_trace_file_contents |
| gv$diag_app_trace_file |
v$diag_vem_user_actlog |
| v$diag_critical_error |
v$diag_vem_user_actlog1 |
| v$diag_dde_user_action |
v$diag_vhm_run |
| v$diag_dde_user_action_def |
v$diag_view |
| v$diag_dde_usr_act_param |
v$diag_viewcol |
| v$diag_dde_usr_act_param_def |
v$diag_vincident |
| v$diag_dde_usr_inc_act_map |
v$diag_vincident_file |
| v$diag_dde_usr_inc_type |
v$diag_vinc_meter_info |
| v$diag_dfs_purge |
v$diag_vips_file_copy_log |
| v$diag_dfw_config_capture |
v$diag_vips_file_metadata |
| v$diag_dfw_config_item |
v$diag_vips_package_file |
| v$diag_dfw_patch_capture |
v$diag_vips_package_history |
| v$diag_dfw_patch_item |
v$diag_vips_package_main_int |
| v$diag_dfw_purge |
v$diag_vips_package_size |
| v$diag_dfw_purge_item |
v$diag_vips_pkg_file |
| v$diag_diagvincident |
v$diag_vips_pkg_inc_cand |
| v$diag_dir_ext |
v$diag_vips_pkg_inc_dtl |
| v$diag_em_diag_job |
v$diag_vips_pkg_inc_dtl1 |
| v$diag_em_target_info |
v$diag_vips_pkg_main_problem |
| v$diag_em_user_activity |
v$diag_vnot_exist_incident |
| v$diag_incckey |
v$diag_vpdb_problem |
| v$diag_incident |
v$diag_vproblem |
| v$diag_incident_file |
v$diag_vproblem1 |
| v$diag_inc_meter_config |
v$diag_vproblem2 |
| v$diag_inc_meter_impt_def |
v$diag_vproblem_bucket |
| v$diag_inc_meter_info |
v$diag_vproblem_bucket1 |
| v$diag_inc_meter_pk_impts |
v$diag_vproblem_bucket_count |
| v$diag_inc_meter_summary |
v$diag_vproblem_int |
| gv$diag_info |
v$diag_vproblem_lastinc |
| v$diag_ips_configuration |
v$diag_vshowcatview |
| v$diag_ips_file_copy_log |
v$diag_vshowincb |
| v$diag_ips_file_metadata |
v$diag_vshowincb_i |
| v$diag_ips_package |
v$diag_vtest_exists |
| v$diag_ips_package_file |
v$diag_v_actinc |
| v$diag_ips_package_history |
v$diag_v_actprob |
| v$diag_ips_package_incident |
v$diag_v_inccount |
| v$diag_ips_pkg_unpack_hist |
v$diag_v_incfcount |
| v$diag_ips_progress_log |
v$diag_v_inc_meter_info_prob |
| v$diag_ips_remote_package |
v$diag_v_ipsprbcnt |
| v$diag_log_ext |
v$diag_v_ipsprbcnt1 |
| gv$diag_opt_trace_records |
v$diag_v_nfcinc |
| v$diag_pdb_problem |
v$diag_v_swperrcount |
| v$diag_pdb_space_mgmt |
gv$incmeter_config |
| v$diag_pickleerr |
gv$incmeter_info |
| v$diag_problem |
gv$incmeter_summary |
| v$diag_relmd_ext |
gv$plsql_debuggable_sessions |
| v$diag_sess_opt_trace_records |
|
|
| |
| Direct Loader |
| |
| gv$loadistat |
gv$loadpstat |
|
| |
| Direct NFS & NFS |
| |
| gv$dnfs_channels |
gv$nfs_clients |
| gv$dnfs_files |
gv$nfs_locks |
| gv$dnfs_servers |
gv$nfs_open_files |
| gv$dnfs_stats |
|
|
| |
| Edition Based Redefinition |
| |
|
| |
| Exadata |
| |
| gv$cell |
v$cell_ioreason |
| gv$cell_config |
v$cell_ioreason_name |
| v$cell_config_info |
v$cell_metric_desc |
| v$cell_db |
gv$cell_ofl_thread_history |
| v$cell_db_history |
v$cell_open_alerts |
| v$cell_disk |
gv$cell_request_totals |
| v$cell_disk_history |
gv$cell_state |
| v$cell_global |
gv$cell_thread_history |
| v$cell_global_history |
|
|
| |
| Features and Feature Usage |
| |
| gv$sql_feature |
gv$sql_feature_hierarchy |
| gv$sql_feature_dependency |
|
|
| |
| Fixed Views |
| |
| gv$fixed_table |
gv$transportable_platform |
| gv$fixed_view_definition |
gv$version |
| gv$indexed_fixed_column |
|
|
SELECT * FROM v$version;
BANNER CON_ID
---------------------------------------------------------------------------- ------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0 |
| |
| Flashback Database |
| |
| gv$flashback_database_log |
v$flashback_txn_mods |
| gv$flashback_database_logfile |
v$recovery_area_usage |
| gv$flashback_database_stat |
gv$restore_point |
| v$flashback_txn_graph |
|
|
| |
| Flashback Recovery Area |
| |
| v$flash_recovery_area_usage |
v$recovery_area_usage |
|
| |
| Flashback Transaction |
| |
| v$flashback_txn_graph |
v$flashback_txn_mods |
|
| |
| GoldenGate |
| |
| gv$gg_apply_coordinator |
gv$goldengate_capture |
| gv$gg_apply_reader |
gv$goldengate_message_tracking |
| gv$gg_apply_receiver |
gv$goldengate_procedure_stats |
| gv$gg_apply_server |
v$goldengate_table_stats |
| gv$goldengate_capabilities |
v$goldengate_transaction |
|
| |
| Hang Detection |
| |
| v$hang_info |
gv$hang_statistics |
| v$hang_session_info |
|
|
| |
| Health Management |
| |
| v$diag_hm_fdg_set |
gv$hm_info |
| v$diag_hm_finding |
gv$hm_recommendation |
| v$diag_hm_info |
gv$hm_run |
| v$diag_hm_message |
gv$ir_failure |
| v$diag_hm_recommendation |
gv$ir_failure_set |
| v$diag_hm_run |
gv$ir_manual_checklist |
| gv$hm_check |
gv$ir_repair |
| gv$hm_check_param |
gv$session_fix_control |
| gv$hm_finding |
gv$system_fix_control |
|
| |
| In-Memory Database |
| |
| gv$imeu_header |
gv$im_segments |
| gv$im_adoelements |
gv$im_segments_detail |
| gv$im_adotaskdetails |
gv$im_seg_ext_map |
| gv$im_adotasks |
gv$im_smu_chunk |
| gv$im_column_level |
gv$im_smu_head |
| gv$im_col_cu |
gv$im_tbs_ext_map |
| gv$im_header |
gv$im_user_segments |
| gv$im_imecol_cuU |
gv$inmemory_area |
| gv$im_segdict |
gvinmemory_faststart_area |
| gv$im_segdict_piecemap |
gv$inmemory_xmem_area |
| gv$im_segdict_sortorder |
gv$scheduler_inmem_mdinfo |
| gv$im_segdict_version |
gv$scheduler_inmem_rtinfo |
|
| |
| I/O |
| |
| gv$file_histogram |
|
| gv$file_optimized_histogram |
gv$iostat_function |
| gv$filemetric |
gv$iostat_function_detail |
| gv$filemetric_history |
gv$iostat_network |
| gv$filestat |
gv$io_calibration_status |
| gv$iofuncmetric |
gv$io_outlier |
| gv$iofuncmetric_history |
gv$kernel_io_outlier |
| gv$iostat_consumer_group |
gv$lgwrio_outlier |
| gv$iostat_file |
gv$tempstat |
|
CREATE TABLE avg_read_write
(inst_id, avg_read_time, avg_write_time) AS
SELECT inst_id, AVG(average_read_time), AVG(average_write_time)
FROM gv$filemetric_history
WHERE 0=1
GROUP BY inst_id;
CREATE OR REPLACE PROCEDURE file_io_metrics AUTHID DEFINER IS
BEGIN
INSERT INTO avg_read_write
(inst_id, avg_read_time, avg_write_time)
SELECT inst_id, AVG(average_read_time), AVG(average_write_time)
FROM gv$filemetric_history
WHERE average_read_time IS NOT NULL
AND average_write_time IS NOT NULL
GROUP BY inst_id
ORDER BY 1;
COMMIT;
END file_io_metrics;
/
exec file_io_metrics
-- sample output
INST_ID AVG(AVERAGE_READ_TIME) AVG(AVERAGE_WRITE_TIME)
---------- ---------------------- -----------------------
5 .844172725 .027063522
6 .542269401 .029147572
7 .846074066 .186608746
8 1.102195470 .071913735
9 .519971963 .029391265
10 .586967514 .071486951 |
| |
| Instance |
| |
| gv$active_instances |
gv$instance_ping |
| gv$instance |
|
|
| |
| Keywords |
Reserved Words
Warning: This is an incomplete list of reserved words. |
desc gv$reserved_words
set linesize 121
set pagesize 0
SELECT keyword
FROM gv$reserved_words
ORDER BY 1; |
| |
| Latches & Locks |
| |
| gv$dlm_all_locks |
gv$latchholder |
| gv$dlm_convert_local |
gv$latchname |
| gv$dlm_convert_remote |
gv$latch_children |
| gv$dlm_latch |
gv$latch_misses |
| gv$dlm_locks |
gv$latch_parent |
| gv$dlm_misc |
gv$libcache_locks |
| gv$dlm_ress |
gv$lock |
| gv$dlm_traffic_controller |
gv$locked_object |
| gv$enqueue_lock |
gv$locks_with_collisions |
| gv$enqueue_stat |
gv$lock_activity |
| gv$enqueue_statistics |
gv$lock_element |
| gv$event_name |
gv$lock_type |
| gv$file_ping |
gv$mutex_sleep |
| gv$ges_blocking_enqueue |
gv$mutex_sleep_history |
| gv$ges_deadlocks |
gv$process |
| gv$ges_deadlock_sessions |
gv$resource |
| gv$ges_enqueue |
gv$resource_limit |
| gv$global_blocked_locks |
gv$transaction_enqueue |
| gv$latch |
gv_$_lock |
|
| |
| Log Miner |
| |
| gv$logmnr_contents |
gv$logmnr_parameters |
| gv$logmnr_dictionary |
gv$logmnr_process |
| gv$logmnr_dictionary_load |
gv$logmnr_session |
| gv$logmnr_latch |
gv$logmnr_stats |
| gv$logmnr_logfile |
gv$logmnr_transaction |
| gv$logmnr_logs |
|
|
| |
| Materialized View Refresh |
| |
|
