Solved

SQL by Hourly break down

Posted on 2013-06-05
5
667 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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
encrypt SQL Server 2008 port 1433 3 42
connection to SQL 2012 error in windows 10 18 43
Oracle cluster . 1 22
Restore a log backup compressed 3 12
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 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