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?

1) v$sqlarea
2) v$session_longops
3) v$session (joined with v$sqlarea)

why the primitive way dear? doesn't oracle give a monitoring screen like OEM?:rolleyes:

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
/

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';

code depot :D

You may take a look at Metalink Note:1019592.6

Regards
Boris

Quote:

---

Originally Posted by dbasan

code depot :D

---

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. :)

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?
:D

Thanks Reydp for those useful codes

sure men. no problem.
lots of codes we can download, but what matters is knowing how and when to use it.
:D

Thank you all for responding all codes and answers. This site is very very useful. Once again thanks a much.