?
Solved

SQL by Hourly break down

Posted on 2013-06-05
5
Medium Priority
?
676 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 21

Accepted Solution

by:
flow01 earned 2000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

621 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