Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL by Hourly break down

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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

595 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