Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

session_stats.sql and system_stats.sql


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

The session_stats.sql script uses the v$sesstat and v$statname view to display all statistics associated the current session.  The sys_context function is used to identify the current session, but the query could easily be altered to use the v$mystats view to achieve the same goal.

session_stats.sql

SET VERIFY OFF 

SELECT sn.name, ss.value
FROM   v$sesstat ss,
       v$statname sn,
       v$session s
WHERE  ss.statistic# = sn.statistic#
AND    s.sid = ss.sid
AND    s.audsid = SYS_CONTEXT('USERENV','SESSIONID')
AND    sn.name LIKE '%' || DECODE(LOWER('&1'), 'all', '', LOWER('&1')) || '%';

The script displays only those statistics with names similar to the specified parameter, or all statistics if the word “all” is specified.  The output below lists the values for all statistics containing the word physical.

SQL> @session_stats physical

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads                                                           90
physical reads cache                                                     90
physical reads direct                                                     0
physical read IO requests                                                43
physical writes                                                           0
physical writes direct                                                    0
physical writes from cache                                                0
physical write IO requests                                                0
physical writes non checkpoint                                            0
physical reads cache prefetch                                            47
physical reads prefetch warmup                                            0
physical reads retry corrupt                                              0
physical reads direct (lob)                                               0
physical reads direct temporary tablespace                                0
physical writes direct (lob)                                              0
physical writes direct temporary tablespace                               0
physical reads for flashback new                                          0 

17 rows selected.

Like the previous script, this script helps identify problem areas within the code associated with the session, helping to focus your attention.

The system_stats.sql script is similar to the session_stats.sql script, except this time the statistics are sourced from the v$sysstat view, which represents the values for the whole instance rather than for individual sessions.

system_stats.sql

SET VERIFY OFF 

COLUMN name FORMAT A50
COLUMN value FORMAT 99999999999999999999 

SELECT sn.name, ss.value
FROM   v$sysstat ss,
       v$statname sn
WHERE  ss.statistic# = sn.statistic#
AND    sn.name LIKE '%' || DECODE(LOWER('&1'), 'all', '', LOWER('&1')) || '%'; 

The expected output is listed below.

SQL> @system_stats physical

NAME                                                               VALUE
-------------------------------------------------- ---------------------
physical reads                                                      6582
physical reads cache                                                6567
physical reads direct                                                 15
physical read IO requests                                           5419
physical writes                                                     1632
physical writes direct                                                 4
physical writes from cache                                          1628
physical write IO requests                                           676
physical writes non checkpoint                                      1544
physical reads cache prefetch                                       1163
physical reads prefetch warmup                                        81 

NAME                                                               VALUE
-------------------------------------------------- ---------------------
physical reads retry corrupt                                           0
physical reads direct (lob)                                            0
physical reads direct temporary tablespace                             3
physical writes direct (lob)                                           0
physical writes direct temporary tablespace                            0
physical reads for flashback new                                       0 

17 rows selected.

session_io.sql

The session_io.sql script uses the v$sess_io view to display the I/O statistics for each session, simplifying the identification of sessions causing excessive I/O.

* session_io.sql

SET LINESIZE 500
SET PAGESIZE 1000 

COLUMN username FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       si.block_gets,
       si.consistent_gets,
       si.physical_reads,
       si.block_changes,
       si.consistent_changes
FROM   v$session s,
       v$sess_io si
WHERE  s.sid = si.sid
ORDER BY s.username, s.osuser;

SET PAGESIZE 14 

The output from this script is shown below.

SQL> @session_io

USERNAME  OSUSER     SID  SERIAL# BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
--------- --------- ---- -------- ---------- --------------- -------------- ------------- TEST      tim_hall   147        4          7           16237             90             9                  0
(oracle)  SYSTEM     146       16          0              11              0             0                  0
(oracle)  SYSTEM     159        1          0               6              0             0                  0
(oracle)  SYSTEM     157        1          4              28              0             0                  0
(oracle)  SYSTEM     160        7        163            7385            275     107                1
(oracle)  SYSTEM     164        1          0              70              2             0                  0
(oracle)  SYSTEM     166        1          0               0              0             0                  0
(oracle)  SYSTEM     168        1          0               0              5             0                  0
(oracle)  SYSTEM     170        1          0               0              0             0                  0
(oracle)  SYSTEM     169        1          0               0              0             0                  0
(oracle)  SYSTEM     167        1          0               0              5             0                  0
(oracle)  SYSTEM     165        1       2679            5828            869          3195               0
(oracle)  SYSTEM     163        1          0            2183             25             0                  0
(oracle)             145        3          0               0              0             0                  0
(oracle)             153        3          0               0              0             0                  0
(oracle)             155       53          0               0              0             0                  0

16 rows selected.

SQL>

open_cursors_by_sid.sql

The open_cursors_by_sid.sql script uses the v$open_cursor view to display the open cursors associated with a particular session.

open_cursors_by_sid.sql 

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF 

SELECT oc.sql_text
FROM   v$open_cursor oc
WHERE  oc.sid = &1; 

PROMPT
SET PAGESIZE 14

The SQL text associated with cursor is truncated, as seen below.

SQL> @open_cursors_by_sid 147

SQL_TEXT
------------------------------------------------------------
UPDATE emp SET    sal = sal * 0.1 WHERE  deptno = 10 AND 

1 row selected.

The open_cursors_full_by_sid.sql script combines the v$open_cursor and v$sqltext views to produce the full SQL statement associated with each cursor.

open_cursors_full_by_sid.sql

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF 

SELECT st.sql_text
FROM   v$sqltext st,
       v$open_cursor oc
WHERE  st.address = oc.address
AND    st.hash_value = oc.hash_value
AND    oc.sid = &1
ORDER BY st.piece; 

PROMPT
SET PAGESIZE 14 

The full text associated with the cursor is shown below.

SQL> @open_cursors_full_by_sid 147

SQL_TEXT
----------------------------------------------------------------
UPDATE emp SET    sal = sal * 0.1 WHERE  deptno = 10 AND    sal
   < 10000 AND    comm   IS NOT NULL 

2 rows selected.

locked_objects.sql

The locked_objects.sql script uses the v$locked_object and dba_objects views to display all locked objects associated with each session along with the mode in which they are currently locked.

locked_objects.sql

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF 

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15 

SELECT b.session_id AS sid,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name
FROM   dba_objects a,
       v$locked_object b
WHERE  a.object_id = b.object_id
ORDER BY 1, 2, 3, 4; 

SET PAGESIZE 14
SET VERIFY ON 

The output from the script is shown below.

SQL> @locked_objects.sql

       SID USERNAME             OBJECT_OWNER         OBJECT_NAME                    LOCKED_MODE     OS_USER_NAME------------ -------------------- -------------------- ------------------------------ --------------- ------------         141 SCOTT                SCOTT                EMP                            Row-X (SX)      tim_hall

1 row selected.

The next section looks at how STATSPACK can be used to identify problem code in the system.

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational