Solved

SQL by Hourly break down

Posted on 2013-06-05
5
664 Views
Last Modified: 2013-06-05
Experts -

How can I convert the output of this SQL into a hourly format based on logon time or start_exec time? Is that possible to convert the CPU_THIS_CALL to %CPU utilization?

Thanks,
Sve.

  SELECT   s.USER#,
           s.USERNAME,
           s.STATUS,
           s.OSUSER,
           s.PROCESS,
           s.MACHINE,
           UPPER (s.PROGRAM) PROGRAM,
           s.LOGON_TIME,
           s.sql_exec_start stime,
           s.LAST_CALL_ET,
           stat.cpu - stat.CPU_this_call_start cpu_this_call,
           stat.CPU
    FROM   V$SESSION S,
           (  SELECT   ss.sid stat_sid,
                       SUM(DECODE (sn.name,
                                   'CPU used when call started', ss.VALUE,
                                   0))
                          CPU_this_call_start,
                       SUM(DECODE (sn.name,
                                   'CPU used by this session', ss.VALUE,
                                   0))
                          CPU,
                       SUM (DECODE (sn.name, 'session uga memory', ss.VALUE, 0))
                          uga_memory,
                       SUM (DECODE (sn.name, 'session pga memory', ss.VALUE, 0))
                          pga_memory,
                       SUM (DECODE (sn.name, 'user commits', ss.VALUE, 0))
                          commits,
                       SUM (DECODE (sn.name, 'user rollbacks', ss.VALUE, 0))
                          rollbacks
                FROM   v$sesstat ss, v$statname sn
               WHERE   ss.STATISTIC# = sn.STATISTIC#
                       AND (   sn.name = 'CPU used when call started'
                            OR sn.name = 'CPU used by this session'
                            OR sn.name = 'session uga memory'
                            OR sn.name = 'session pga memory'
                            OR sn.name = 'user commits'
                            OR sn.name = 'user rollbacks')
            GROUP BY   ss.sid) stat
   WHERE   (    (s.USERNAME IS NOT NULL)
            AND (NVL (s.osuser, 'x') <> 'SYSTEM')
            AND (s.TYPE <> 'BACKGROUND'))
           AND (stat.stat_sid = s.sid)
and s.username = 'username'
ORDER BY   "USERNAME", OWNERID

Open in new window

0
Comment
Question by:sventhan
5 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39222648
Providing the procedure isn't enough to evaluate your request.  You have several functions with unknown use and without some insight into your data I'd be fumbling around in the dark.  I'm sure that grouping by HOUR(LOGON_TIME) is possible but don't have enough to go on at this point.
0
 
LVL 18

Author Comment

by:sventhan
ID: 39222671
Thanks for your time. This SQL can run on any oracle environment, if you would like to know about the data.
0
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39223970
What do you mean by "a hourly format" ?

a single session belongs only to 1 hour ( to_char(trunc(LOGON_TIME),'HH24')
so i expect you want some accumulation of  data per hour : what accumulation ?

what percentage do you mean ?
round((stat.cpu - stat.CPU_this_call_start) * 100 / stat.cpu),0)  ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39224238
This SQL can run on any oracle environment, if you would like to know about the data.
Then please request that the SQL Server topic be removed.  It just creates confusion.
0
 
LVL 18

Author Closing Comment

by:sventhan
ID: 39224277
Thanks for your time.  That works for me.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question