1. From within Oracle SQL*Plus session.
2. Using the userenv(‘SESSIONID’) to retrieve current sid and serial#.
select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);
SQL> select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);
SID SERIAL#
———- ———-
408 16328
SQL>
3. Note: That logging out of SQL*Plus session or reconnecting will change your SID and SERIAL#.
Larry Catt
OCP
The Oracle RDBMS provides the use of the following view to determine most costly wait events since instance startup: V$SESSION_WAIT_HISTORY. This view can be used to determine possible problem areas in the database. This article covers how to determine the top wait events in the Oracle database since system startup. This procedure will work regardless of OS.
1. Logon to you Oracle database server.
2. Logon to SQLPLUS with sysdba privileges.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tues Jan 26 20:41:52 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
3. Execute the following SQL to extract the top wait events in the database since startup.
select distinct(a.event) as event, (select count(*) from v$session_wait_history
b where a.event=b.event) as tot_waits
from v$session_wait_history a order by tot_waits desc;
SQL> select distinct(a.event) as event, (select count(*) from
v$session_wait_history b where a.event=b.event) as tot_waits
from v$session_wait_history a order by tot_waits desc; 2
EVENT TOT_WAITS
————————– ———-
jobq slave wait 90
rdbms ipc message 81
PX Deq: Execution Msg 54
PX Deq: Table Q Normal 20
…
…
…
15 rows selected.
SQL>
4. This information can be used to further inspect the system to determine the cause of performance loss.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com
The Oracle RDBMS provides the use of the following view to determine most costly wait events currently being experience by users: V$SESSION_WAIT. V$SESSION_WAIT contains all the current wait events which are active. This view can be used to determine what is causing an issue while experiencing a system slow down. This article covers how to determine the top wait events in the Oracle database. This procedure will work regardless of OS.
1. Logon to you Oracle database server.
2. Logon to SQLPLUS with sysdba privileges.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jan 10 10:28:02 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
3. Execute the following SQL to extract the current top wait events in the database.
select distinct(a.event) as event, (select count(*) from v$session_wait b where
a.event=b.event) as tot_waits
from v$session_wait a order by tot_waits desc;
SQL> select distinct(a.event) as event, (select count(*) from v$session_wait b
where a.event=b.event) as tot_waits
from v$session_wait a order by tot_waits desc; 2
EVENT TOT_WAITS
————————- ———-
rdbms ipc message 9
Streams AQ: waiting for time management or cleanup tasks 2
SQL*Net message to client 1
…
…
…
7 rows selected.
SQL>
4. This information can be used to further inspect the system to determine the cause of performance loss.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com
The Oracle RDBMS provides the DBA with the ability to kill a user’s session. On occasion you will find a particular account which is causing performance issues or performing an action which may be harmful to the database. In this situation, it may be best to kill the session. This article covers the use of “alter system” command to kill a users session.
1. Logon to you Oracle database server.
2. Logon to SQLPLUS with sysdba privileges.
mylinux:> sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 02 08:15:39 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
3. In this example, we know the session causing the issue is owned by the user LJCATT. To kill the session we must identify the SID and SERIAL# of the session owned by LJCATT. Execute the following SQL to get the SID and SERIAL# from his session.
SQL> select SID, SERIAL#, username from v$session where username=’LJCATT’;
SID SERIAL# USERNAME
———- ———- ——————————
3979 108 LJCATT
SQL>
4. To kill the session use the following command: alter system kill session ‘SID,SERIAL#’ immediate;
SQL> alter system kill session ‘3979, 108′ immediate;
System altered.
SQL>
5. Re-execute the SQL in step 3 to verify that the session has been killed.
SQL> select SID, SERIAL#, username from v$session where username=’LJCATT’;
no rows selected
SQL>
NOTE: If the user has performed a large amount of updates, insert, or deletes; the kill operation may take awhile to complete, due to rollback operations.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com