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

All Categories

Call Fusion BIP Report2 Change Password1 Code Combinations2 Compute Instance2 CTE1 Customer1 Data Aggregation2 Database5 Date Conversion1 DB Adapter2 Decryption1 Development1 EBS4 Encryption1 ESS Jobs3 Examine1 FBDI3 Fusion APIs1 Fusion BIP7 GIT2 GL3 GL Journals1 GL_DAILY_CONVERSION_TYPES1 GL_DAILY_RATES1 ICS1 Identity Domain1 Integrations1 Java1 Journal Import1 Keys1 Legal Entity1 LookupTypeLOV1 LOV1 LOVs1 MultiPartAPIs1 Networking1 NVL2 NVL in OIC2 OCI11 OCI Billing1 OCI Compute5 OCI Cost Management1 OCI Events Service1 OCI Free Tier3 OCI Notifification Service1 OCI Security3 OIC4 OIC Mapper2 Oracle26 Oracle ADF17 Oracle APEX1 Oracle Apps59 Oracle Apps R126 Oracle ATP1 Oracle BIP8 Oracle Cloud12 Oracle Cloud Free Tier1 Oracle cloud Infrastructure9 Oracle Cloud Security2 Oracle Cloud VM1 Oracle DB4 oracle ebs5 Oracle ERP4 Oracle ERP Adapter2 Oracle ERP Cloud7 Oracle financials2 Oracle Forms1 Oracle Fusion57 Oracle Fusion BIP4 Oracle Fusion ERP17 Oracle Fusion Financials18 Oracle Integration Cloud3 Oracle OAF17 Oracle OCI14 Oracle OIC22 Oracle SOA 12c10 Oracle SQL17 Oracle VBCS1 Oracle VBS2 Oracle Visual Builder Cloud Service1 Oracle Visual Builder Studio2 Oracle Workflow Notifications1 Others10 Payables2 Payables Import1 Properties1 R121 Register BIP as ESS Job1 Reset Password1 Responsibility1 REST4 Security List1 Site Map1 SOAP2 SOAP API2 SOAP UI3 SQL16 SQL Functions3 SQL Queries14 SQL Query8 SQL Tips3 SSH1 TCA1 Value Sets1 VBCS1 Virtual Machine2 Virtual Machines1 XML1 XSLT1
Show more