Script to find the DB activity?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Script to find the DB activity?

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    Script to find the DB activity?

    I wanted to see the below information when one of the PLSQL procedure is running in the database.

    1. Which SQL code is currenlty running?
    2. How much percentage is completed for the current SQL code? Let us say, curretly, the one particular table is scanning. We need to know how much percentage is completed?
    3. How many sessions are running in the database? ( I think, this can be found through v$session). But i wanted to see what code is running in each session?

    I understand that we can see all these through TOAD. Let us say, we do not use TOAD. Where can i find the script to find all the above information?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    1) v$sqlarea
    2) v$session_longops
    3) v$session (joined with v$sqlarea)

  3. #3
    Join Date
    Feb 2007
    Posts
    212
    why the primitive way dear? doesn't oracle give a monitoring screen like OEM?

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Top 10 by Buffer Gets:

    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) sql,
    buffer_gets, executions, buffer_gets/executions "Gets/Exec",
    hash_value,address
    FROM V$SQLAREA
    WHERE buffer_gets > 10000
    ORDER BY buffer_gets DESC)
    WHERE rownum <= 10
    ;

    Top 10 by Physical Reads:

    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) sql,
    disk_reads, executions, disk_reads/executions "Reads/Exec",
    hash_value,address
    FROM V$SQLAREA
    WHERE disk_reads > 1000
    ORDER BY disk_reads DESC)
    WHERE rownum <= 10
    ;

    Top 10 by Executions:

    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) sql,
    executions, rows_processed, rows_processed/executions "Rows/Exec",
    hash_value,address
    FROM V$SQLAREA
    WHERE executions > 100
    ORDER BY executions DESC)
    WHERE rownum <= 10
    ;

    Top 10 by Parse Calls:

    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) sql,
    parse_calls, executions, hash_value,address
    FROM V$SQLAREA
    WHERE parse_calls > 1000
    ORDER BY parse_calls DESC)
    WHERE rownum <= 10
    ;

    Top 10 by Sharable Memory:

    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) sql,
    sharable_mem, executions, hash_value,address
    FROM V$SQLAREA
    WHERE sharable_mem > 1048576
    ORDER BY sharable_mem DESC)
    WHERE rownum <= 10
    ;

    Top 10 by Version Count:

    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) sql,
    version_count, executions, hash_value,address
    FROM V$SQLAREA
    WHERE version_count > 20
    ORDER BY version_count DESC)
    WHERE rownum <= 10
    ;

    col "Machine Name" format a15
    col Program format a20
    col Username format a15
    col "OS User" format a15
    prompt SESSION and PROCESS information
    select a.sid "SID" , a.serial# "Serial #",a.status "Status" ,a.username "Username", a.osuser "OS User",b.spid "OS Process ID",a.machine "Machine Name", a.program "Program",a.logon_time "Logon Time" from v$session a, v$process b where a.paddr=b.addr order by a.sid
    /

    prompt SESSION INFO - HIGH I/O AND CPU CONSUMPTION
    select cpu.sid "SID", cpu.serial# "Serial #", cpu.username "User Name",reads.value "Physical reads", writes.value "Physical writes",decode(connection_time.value,0,0,cpu.value/connection_time.value) "CPU Utilization %" from (select a.sid sid, a.username username, b.name, c.value value,a.serial# serial# from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='CPU used by this session' ) cpu,(select a.sid, a.username, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='session connect time' )connection_time,(select a.sid, a.username, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='physical reads' ) reads,(select a.sid, a.username, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='physical writes' ) writes where cpu.sid = connection_time.sid and connection_time.sid=reads.sid and reads.sid = writes.sid
    /


    prompt TOP 10 SQL -RESOURCE EXTENSIVE
    select 0 - GETM "Gets", ROWC "Rows" , EXES "Number of Executions" , STMT "SQL Statement" from (select distinct GETM, ROWC, EXES, STMT from ( select substr(SQL_TEXT, 1, 512) STMT,sum(ROWS_PROCESSED) ROWC , sum(EXECUTIONS) EXES, sum(0 - BUFFER_GETS) GETM from V$SQL group by substr(SQL_TEXT, 1, 512) ) ) where ROWNUM <= 10 ORDER BY EXES DESC
    /

    col Name format a15
    prompt TOP 10 TABLE -RESOURCE EXTENSIVE
    select CTYP "Command Type", OBJ "Name", 0 - EXEM "Number of Executions" , GETS "Buffer Gets" , ROWP "Rows Processed" from (select distinct EXEM, CTYP, OBJ, GETS, ROWP from ( select decode (S.COMMAND_TYPE , 2, 'Insert into ' , 3,'Select from ', 6, 'Update of ' , 7, 'Delete from ' , 26,'Lock of ') CTYP , O.OWNER || '.' || O.NAME OBJ , sum(0 - S.EXECUTIONS) EXEM , sum(S.BUFFER_GETS) GETS , sum(S.ROWS_PROCESSED) ROWP from V$SQL S , V$OBJECT_DEPENDENCY D , V$DB_OBJECT_CACHE O where S.COMMAND_TYPE in (2,3,6,7,26)and D.FROM_ADDRESS = S.ADDRESS and D.TO_OWNER = O.OWNER and D.TO_NAME= O.NAME and O.TYPE = 'TABLE' group by S.COMMAND_TYPE , O.OWNER , O.NAME ) ) where ROWNUM <= 10
    /
    ---------------

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    estimating time for longops SQL:
    SQL> select OPNAME,TARGET,SOFAR,TOTALWORK,UNITS,START_TIME,TIME_REMAINING,
    2 ELAPSED_SECONDS from v$session_longops where username='SCOTT';
    ---------------

  6. #6
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    code depot

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    You may take a look at Metalink Note:1019592.6

    Regards
    Boris

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by dbasan
    code depot
    Hey... the guy is asking the codes...
    if he's asking explaination and theories then I bet somebody here can still response. So it's still not going to be boring question.
    ---------------

  9. #9
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by Bore
    You may take a look at Metalink Note:1019592.6

    Regards
    Boris
    I'm not sure if he was asking db performance health check script. Did he?
    ---------------

  10. #10
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Thanks Reydp for those useful codes
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width