How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters.

SQL> alter session set sql_trace=true;
Session altered.

SQL> alter session set optimizer_mode=first_rows;
Session altered.

-- connect to session
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump modified_parameters 1;
Statement processed.
SQL> oradebug tracefile_name;
i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc

Contents of the tracefile:

*** 2012-05-28 14:35:25.005
Processing Oradebug command ‘dump modified_parameters 1’
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
optimizer_mode = FIRST_ROWS

*** 2012-05-28 14:35:25.005
Oradebug command ‘dump modified_parameters 1’ console output:

How to see current utilization of processes/sessions and max utilization?

Using the following SQL one can find the current number of processes and sessions connected and also max utilization so one can check if you need to increase the values of the parameter

SQL> select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in (‘processes’,’sessions’);

Output:
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
—————————— ——————- —————
processes 146 196
sessions 157 210

How to list current active roles for a user in a session?

Using SESSION_ROLES it will retrieve the current roles that are active for a user in a session.

Example:
SQL> connect test/…
SQL> select * from session_roles;

ROLE
——————————
DELETE_CATALOG_ROLE

Using “SET ROLE” one can change the roles active in the current session.
Example:
SQL> select * from session_roles;
ROLE
——————————
TEST11

Enable specific set of roles in the current session
SQL> set role test11, delete_catalog_role;
Role set.

SQL> select * from session_roles;
ROLE
——————————
DELETE_CATALOG_ROLE
TEST11

Enable all roles in the current session
SQL> set role all;
Role set.

Global temporary tables

— Global temporary table which keeps records till session
create global temporary table temp1( year number ) on commit preserve rows;

SQL> insert into temp1 values ( 11 );

1 row created.

SQL> select * from temp1;

YEAR
———-
11

— reconnect to session and check records
SQL> connect /as sysdba
Connected.
SQL> select * from temp1;

no rows selected

— Global temporary table which keeps records till session does a commit/rollback
Example:
— create global temporary table based on commit
create global temporary table temp2( year number ) on commit delete rows;
SQL> insert into temp2 values ( 12 );

1 row created.

SQL> select * from temp2;

YEAR
———-
12

SQL> commit;

Commit complete.

— shows records disappear after commit/rollback
SQL> select * from temp2;

no rows selected

SQL> insert into temp2 values ( 12 );

1 row created.

SQL> rollback;

Rollback complete.

SQL> select * from temp2;

no rows selected

— By checking the value of DURATION in user_tables one can tell if the table is session based on transaction based, if value is SYS#SESSION then it is session based and if the value is SYS$TRANSACTION then it is transaction based
SQL> select duration from user_tables where table_name = ‘TEMP1’;

DURATION
—————
SYS$SESSION

SQL> select duration from dba_tables where table_name = ‘TEMP2’;

DURATION
—————
SYS$TRANSACTION

List locks held by a session

To list locks held by a current session

select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
object_name(rsc_objid) tablename,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type ='LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and rsc_dbid = db_id()
and req_spid = @@spid
order by spid

Example
BEGIN TRANSACTION
INSERT INTO TEST VALUES ( 1 )

Output
spid dbid ObjId tablename IndId Type Resource Mode Status
—— —— ———– ——————————— —— —- ————– ——– ——
52 5 0 NULL 0 DB S GRANT
52 5 2105058535 TEST 0 RID 1:143:0 X GRANT
52 5 2105058535 TEST 0 PAG 1:143 IX GRANT
52 5 2105058535 TEST 0 TAB IX GRANT

How to find the last time a session performed any activity?

In v$session the column last_call_et has value which tells us the last time (seconds) ago when the session performed any activity within the database.

select username, floor(last_call_et / 60) "Minutes", status
from v$session
where username is not null –– to ignore background process
order by last_call_et;

USERNAME Minutes STATUS
—————————— ———- ——–
SYS 0 ACTIVE
SCOTT 0 INACTIVE
SYSTEM 34 INACTIVE
..

How to suspend/resume a process using oradebug?

Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.

Resume a process
SQL> oradebug resume
Statement processed.

If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2010-07-13 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321

How to monitor temp tablespace usage?

At times you may have a scenario where you may want to monitor temp space usage in a running session. So using the following PL/SQL code loops which inserts every interval on temp usage for a specific sid (session id).

create table monitor_usage (
RUN_DATE DATE,
BLOCKS NUMBER(38),
BYTES NUMBER(38),
APP VARCHAR2(10)
);

declare
cursor l_cur IS SELECT sysdate, b.blocks, b.blocks*d.value bytes
FROM v$session a, (select session_addr, sum(blocks) blocks from v$tempseg_usage group by session_addr) b, v$sqlarea c,
(select value from v$parameter where name=’db_block_size’) d
WHERE a.saddr = b.session_addr
and a.sid = &sid
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*d.value > 1024;
l_rec l_cur%rowtype;
app varchar2(2) := ‘&1’;
interval integer := 10;
begin
while true loop
open l_cur;
fetch l_cur into l_rec;
If l_cur%found THEN
insert into monitor_usage values (l_rec.sysdate, l_rec.blocks, l_rec.bytes, app);
commit;
end if;
DBMS_LOCK.sleep(seconds => interval);
close l_cur;
end loop;
end;
/

Example:

SQL> alter session set nls_Date_format=’DD-MON-YYY

Session altered.

SQL> select * from monitor_usage where app = ‘R1’
RUN_DATE BLOCKS BYTES APP
——————– ———- ———- ——-
15-JUN-2010 10:21:21 1792 14680064 R1
15-JUN-2010 10:21:32 8576 70254592 R1

15-JUN-2010 10:27:52 119808 981467136 R1
15-JUN-2010 10:28:04 122240 1001390080 R1
15-JUN-2010 10:28:15 124672 1021313024 R1

15-JUN-2010 10:29:59 33920 277872640 R1
15-JUN-2010 10:30:10 34048 278921216 R1

How to check/find session information (wait, io, lock, time)

Find SQL currently executing in a session:
https://oraclespin.wordpress.com/2010/05/09/how-to-find-sql-running-for-a-usersid/

Session running waiting on
SYS@TESTDB> SELECT event, state, p1, p2, p3, seconds_in_wait FROM v$session_wait where sid = 247;

EVENT STATE
P1 P2 P3 SECONDS_IN_WAIT
—————————————————————- —————
—- ———- ———- ———- ——————-
db file scattered read WAITED KNOWN TIME
122 1188369 128 30

1 row selected.

SYS@TESTDB> SELECT * FROM v$event_name WHERE name = ‘db file scattered read’;

EVENT# EVENT_ID NAME
PARAMETER1 PARAMETE
R2 PARAMETER3
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
———- ———- ———————————————————-
—— —————————————————————- ——–
——————————————————– ———————–
—————————————– ————- ———– ————
—————————————————-
117 506183215 db file scattered read
file# block#
blocks
1740759767 8 User I/O

So Parameter #1: file#=122, Parameter #2: block#=1188369, Parameter #3: blocks=128

IO information consistents gets – reads from db buffers, physical reads – Physical reads, block changes – block changes
SQL> select * from V$sess_io where sid = 247;

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
———- ———- ————— ————– ————- ——————
247 6 1355 25 4 0

Detail user session information:

SQL> select vsn.name, vst.value
from v$sesstat vst, v$statname vsn
where vsn.statistic# = vst.statistic#
and vst.value 0
and vst.sid = 247
order by vst.value;

NAME VALUE
—————————————————————- ————-
logons cumulative 1
sorts (memory) 1
logons current 1
enqueue releases 1
enqueue requests 1

Find current locks by session:
— ID1 – is the object id the locks is on
— CTIME – Time elapsed since the object was locked in seconds
SQL> SELECT * FROM V$lock where SID = 247;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
000000006934A5B0 000000006934A5D8 247 TM 13828 0 3 0 72989 0
0000000069376700 0000000069376888 24 TX 65539 2262 6 0 72989 0

List session time for various operations
Using V$SESS_TIME_MODEL it displays the session time for various operations. The time reported is the total elapsed or CPU time (in microseconds).
Example:
SQL> select stat_name, value
from V$SESS_TIME_MODEL
where sid = 247
order by value desc;

STAT_NAME VALUE
—————————————————————- ———-
DB time 111174
DB CPU 107996
parse time elapsed 90664
hard parse elapsed time 64358
connection management call elapsed time 53179
sql execute elapsed time 32974
failed parse elapsed time 963
repeated bind elapsed time 148
background elapsed time 0
RMAN cpu time (backup/restore) 0