Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL by Hourly break down

Posted on 2013-06-05
5
Medium Priority
?
672 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 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