Difference between GV$SESSION and V$SESSION


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