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 


 

 

 


 

 

 
 

v$sess_time_model scripts

Oracle Tips by Burleson Consulting

v$sess_time_model scripts

 

It is possible to write an exception script that will give a warning if the database workload exceeds some predefined threshold. For example, the script below lists sessions that experience high workload and consume significant processing resources:

 

SELECT

   s.sid,

   s.username,

   s.module,

   round(t.value/1000000,2) "Elapsed Processing Time (Sec)"

FROM

   v$sess_time_model t,

   v$session s

WHERE

   t.sid = s.sid

AND

   t.stat_name = 'DB time'

AND

   s.username IS NOT NULL

AND

   t.value/1000000 >= 1;

 

The output, showing the elapsed time used by specific modules, looks like the following:

 

       SID USER  MODULE       Elapsed Processing Time (Sec)

---------- ----- ------------ -----------------------------

       137 DABR  SQL*Plus                              5,93

       133 SPV   spvent.exe                           26,47

       135 SYS   SQL*Plus                              6,36

       141 DABR  SpMon.exe                            23,42

       150 DABR  spvent.exe                             8,3

       152 SYS   SQL*Plus                              8,13

       156 SYS   SQL*Plus                              1,43

 

In the script above, the (WHERE t.value/1000000>1) clause shows user sessions that consume processing time of more than one second.

 

Once the sessions consuming high resources have been identified, the DBA reviews what particular type of processing causes such a high workload. For example, the query below reveals time model statistics for a particular session:

 

select

   *

from

   v$sess_time_model

where

   sid = 137

order by

   value desc; 

 

The output of the above query, showing all important statistics for a particular session, might look like:

 

SID STAT_ID       STAT_NAME                                            VALUE

--- ------------- ----------------------------------------------- ----------

137 3,649,082,374 DB time                                          7,244,647

137 2,821,698,184 sql execute elapsed time                         7,035,683

137 2,748,282,437 DB CPU                                           6,827,842

137 1,431,595,225 parse time elapsed                                 529,294

137   372,226,525 hard parse elapsed time                            512,333

137 1,311,180,441 PL/SQL compilation elapsed time                     65,472

137 1,990,024,365 connection management call elapsed time             18,180

137 2,643,905,994 PL/SQL execution elapsed time                        1,225

137 4,157,170,894 background elapsed time                                  0

137   751,169,994 Java execution elapsed time                              0

137   290,749,718 inbound PL/SQL rpc elapsed time                          0

137   268,357,648 hard parse (bind mismatch) elapsed time                  0

137 2,451,517,896 background cpu time                                      0

137 1,824,284,809 failed parse elapsed time                                0

137 3,138,706,091 hard parse (sharing criteria) elapsed time               0

137 4,125,607,023 failed parse (out of shared memory) elapsed time         0

 

From the listing above, it is apparent that a significant part of processing time for this database is consumed by SQL execution. This could clue the DBA that an investigation into SQL statements to identify high CPU consuming SQL statements is required. For example, such SQL statements could have a large number of buffer gets that were taken in consistent mode, and this requires a large amount of CPU processing time to reconstruct data blocks from rollback segments (UNDO logs).

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


 

 

��  
 
 
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