The dynamic performance (V$) view contains database statistics and is commonly used for performance analysis and tuning.
In the Oracle RAC environment, a global (GV$) view corresponds to each V$ view. V$ views contain statistics for one instance, whereas GV$ views contain information from all the active instances.
Each GV$ view contains an INST_ID column of type NUMBER, which can be used to identify the instance associated with the row data.
The difference between GV$SESSION and V$SESSION is that, in a RAC environment, GV$SESSION will show you data on all instances in the cluster.
Use the below query to get the view definitions
SELECT VIEW_DEFINITION FROM v$fixed_view_definition WHERE view_name='V$SESSION';
SELECT saddr, sid, serial#, audsid, paddr, user#, username, command, ownerid, taddr, lockwait, status, server, schema#, schemaname, osuser, process, machine, port, terminal, program, type, sql_address, sql_hash_value, sql_id, sql_child_number, sql_exec_start, sql_exec_id, prev_sql_addr, prev_hash_value, prev_sql_id, prev_child_number, prev_exec_start, prev_exec_id, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, module, module_hash, action, action_hash, client_info, fixed_table_sequence, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, top_level_call#, logon_time, last_call_et, pdml_enabled, failover_type, failover_method, failed_over, resource_consumer_group, pdml_status, pddl_status, pq_status, current_queue_duration, client_identifier, blocking_session_status, blocking_instance, blocking_session, final_blocking_session_status, final_blocking_instance, final_blocking_session, seq#, event#, event, p1text, p1, p1raw, p2text, p2, p2raw, p3text, p3, p3raw, wait_class_id, wait_class#, wait_class, wait_time, seconds_in_wait, state, wait_time_micro, time_remaining_micro, time_since_last_wait_micro, service_name, sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats, session_edition_id, creator_addr, creator_serial#, ecid, sql_translation_profile_id, pga_tunable_mem, con_id, external_name FROM gv$session WHERE inst_id = userenv('Instance');
Now you would see that V$SESSION is actually based out of GV$SESSION with only one filter condition as inst_id = userenv('Instance')
Comments
Post a Comment