This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Feb 2, 2013 8:06 AM by sb92075 Go to original post RSS
  • 30. Re: V$sessions
    Catfive Lander Explorer
    Currently Being Moderated
    You do have to start doing some work yourself, at some point. This is now pretty basic SQL writing.

    The code I supplied (sb merely re-formatted it) is correct for 11gR2. I've also checked the documentation, and that column is there for 10gR2, too, as this link shows

    So what version of Oracle are you using?

    You can, of course, always remove the Q table from anywhere in that query, since it is the join from S to Q on SQL_CHILD_NUMBER that is causing the problem. Remove Q from the "from" clause, and remove all mention of q.<fields> from the WHERE clause and you will still have a query that correctly joins SESSIONS to LONGOPS and PROCESSES.
  • 31. Re: V$sessions
    jgarry Guru
    Currently Being Moderated
    >
    So what version of Oracle are you using?
    He should tell that every time, but get a clue from his Recent Questions.
  • 32. Re: V$sessions
    Catfive Lander Explorer
    Currently Being Moderated
    I'm afraid I've just worked that out :-(
  • 33. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    Sorry I forgot to mention... we are using 9.2.0.6 database.

    Hi Cat, Jq, Sb and all....can you give me the counterpart sql for 9.2.0.6 V$session?


    Another question :)

    Can this script be used to identify what the batch job is doing?

    Our users often run a form batch job. Most of the time it gets completed average in 1 minute or less. But sometimes it takes more than 1 hr or hanging. I want to check if the job is still running or hanging. I want to see if it has i/o, cpu, memory activity. So if not I can kill it or abort it.


    Thanks

    Edited by: yxes2013 on 23.1.2013 1:10
  • 34. Re: V$sessions
    jgarry Guru
    Currently Being Moderated
    It's been way too long since I've used the 9.2 OEM, but I seem to recall it did have some usefulness.
  • 35. Re: V$sessions
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    Sorry I forgot to mention... we are using 9.2.0.6 database.

    Hi Cat, Jq, Sb and all....can you give me the counterpart sql for 9.2.0.6 V$session?


    Another question :)

    Can this script be used to identify what the batch job is doing?

    Our users often run a form batch job. Most of the time it gets completed average in 1 minute or less. But sometimes it takes more than 1 hr or hanging. I want to check if the job is still running or hanging. I want to see if it has i/o,
    SQL> desc v$sess_io
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     SID                                                NUMBER
     BLOCK_GETS                                         NUMBER
     CONSISTENT_GETS                                    NUMBER
     PHYSICAL_READS                                     NUMBER
     BLOCK_CHANGES                                      NUMBER
     CONSISTENT_CHANGES                                 NUMBER
     OPTIMIZED_PHYSICAL_READS                           NUMBER
    when any session is "doing something", one or more of the columns in this view will be increasing in value
  • 36. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    Thanks Sb :) hmmm another tips learned.

    What is the counterpart V$ views to check if the program has still CPU or Memory activity?


    By the way ....I have been asking this questions before in the forum but I did not get direct answer yet.

    In linux to check how much memory is used by the OS or server itself the command is:

    # cat /proc/meminfo | grep Active , then you just less it from the Total Ram to get the free memory.

    What is the corresponding command in the Oracle database? To show free and used memory from SGA?


    Thanks a lot,

    Edited by: yxes2013 on 2.2.2013 7:53
  • 37. Re: V$sessions
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    Thanks Sb :) hmmm another tips learned.

    What is the counterpart V$ views to check if the program has still CPU or Memory activity?
    V_$ARCHIVE_DEST_STATUS
    V_$ASM_DISKGROUP_STAT
    V_$ASM_DISK_IOSTAT
    V_$ASM_DISK_STAT
    V_$ASM_VOLUME_STAT
    V_$BUFFER_POOL_STATISTICS
    V_$CELL_STATE
    V_$CLIENT_STATS
    V_$CPOOL_CC_STATS
    V_$CPOOL_STATS
    V_$MYSTAT
    V_$OSSTAT
    V_$PGASTAT
    V_$PQ_SESSTAT
    V_$PQ_SYSSTAT
    V_$PQ_TQSTAT
    V_$PX_PROCESS_SYSSTAT
    V_$PX_SESSTAT
    V_$QMON_COORDINATOR_STATS
    V_$QMON_SERVER_STATS
    V_$QMON_TASK_STATS
    V_$RECOVERY_FILE_STATUS
    V_$RECOVERY_STATUS
    V_$RESULT_CACHE_STATISTICS
    V_$RMAN_STATUS
    V_$ROLLSTAT
    V_$RULE_SET_AGGREGATE_STATS
    V_$SEGMENT_STATISTICS
    V_$SEGSTAT
    V_$SEGSTAT_NAME
    V_$SERVICE_STATS
    V_$SERV_MOD_ACT_STATS
    V_$SESSTAT
    V_$SGASTAT
    V_$SQLSTATS
    V_$SQLSTATS_PLAN_HASH
    V_$SQL_CS_STATISTICS
    V_$SQL_PLAN_STATISTICS
    V_$SQL_PLAN_STATISTICS_ALL
    V_$STATISTICS_LEVEL
    V_$STATNAME
    V_$STREAMS_POOL_STATISTICS
    V_$SUBSCR_REGISTRATION_STATS
    V_$SYSSTAT
    V_$TEMPSTAT
    V_$UNDOSTAT
    V_$WAITSTAT
    V_$WLM_PC_STATS
    V_$DNFS_STATS
    V_$DYNAMIC_REMASTER_STATS
    V_$ENQUEUE_STAT
    V_$ENQUEUE_STATISTICS
    V_$FILESTAT
    V_$FLASHBACK_DATABASE_STAT
    V_$FS_FAILOVER_STATS
    V_$IOSTAT_CONSUMER_GROUP
    V_$IOSTAT_FILE
    V_$IOSTAT_FUNCTION
    V_$IOSTAT_FUNCTION_DETAIL
    V_$IOSTAT_NETWORK
    V_$IO_CALIBRATION_STATUS
    V_$LOADISTAT
    V_$LOADPSTAT
    V_$LOBSTAT
    V_$LOGMNR_STATS
    V_$LOGSTDBY_STATE
    V_$LOGSTDBY_STATS
    V_$DATAGUARD_STATS
    V_$DATAGUARD_STATUS
    [oracle@localhost ~]$


    >
    >
    By the way ....I have been asking this questions before in the forum but I did not get direct answer yet.

    In linux to check how much memory is used by the OS or server itself the command is:
    [oracle@localhost ~]$ free
                 total       used       free     shared    buffers     cached
    Mem:        909120     758824     150296          0      63368     397864
    -/+ buffers/cache:     297592     611528
    Swap:      1735012     179944    1555068
    >
    # cat /proc/meminfo | grep Active , then you just less it from the Total Ram to get the free memory.

    What is the corresponding command in the Oracle database? To show free and used memory from SGA?
    V_$SGASTAT

    >
    Thanks a lot,

    Edited by: yxes2013 on 2.2.2013 7:53
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points