1

in OEM 11g, if you see performance, there are graph about cpu utilization, memory utilization, and disk I/O utilization.

my question is:

what is the script to see CPU-Utilization history ?

Thank you

1

This query runs on 11.1.0.7 Enterprise and provides similar results to the the OEM Grid performance page. Both require the diagnostics package.

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,
       ROUND(OTHER / 60, 3) AS OTHER,
       ROUND(CLUST / 60, 3) AS CLUST,
       ROUND(QUEUEING / 60, 3) AS QUEUEING,
       ROUND(NETWORK / 60, 3) AS NETWORK,
       ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
       ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
       ROUND(COMMIT / 60, 3) AS COMMIT,
       ROUND(APPLICATION / 60, 3) AS APPLICATION,
       ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
       ROUND(SIO / 60, 3) AS SYSTEM_IO,
       ROUND(UIO / 60, 3) AS USER_IO,
       ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
       ROUND(CPU / 60, 3) AS CPU,
       ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
  FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,
               DECODE(SESSION_STATE,
                      'ON CPU',
                      DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),
                      WAIT_CLASS) AS WAIT_CLASS
          FROM V$ACTIVE_SESSION_HISTORY
         WHERE SAMPLE_TIME > SYSDATE - INTERVAL '1'
         HOUR
           AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*) 
  FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER,
'User I/O' AS UIO,
'System I/O' AS SIO, 
'Concurrency' AS CONCURRENCY,                                                                               
'Application' AS  APPLICATION,                                                                                  
'Commit' AS  COMMIT,                                                                             
'Configuration' AS CONFIGURATION,                     
'Administrative' AS   ADMINISTRATIVE,                                                                                 
'Network' AS  NETWORK,                                                                                 
'Queueing' AS   QUEUEING,                                                                                  
'Cluster' AS   CLUST,                                                                                      
'Other' AS  OTHER))
ORDER BY 1  

This query is not as good as what Grid displays but it's close.

| improve this answer | |
  • am i allowed to set retention of this database which it used for stored performance? if there's no retention, i assume that this database would take a big size of storage just for stored the information of performance. how if i want to select from 1 week ago? and show timestamp per hour? – Yoga Jun 3 '16 at 1:51
  • @Yoga this is a real time quote. If you want to use this you need the stats package licence and if you want to see what was happening a week ago you might as well install 11g Grid on a separate server. – kevinsky Jun 3 '16 at 9:57
  • @Yoga Refer to this page for lot more useful scripts. – Raj Jun 6 '16 at 19:58
  • @kevinsky i have install 11g Grid, and i can see the graph about performance, but i want the 'number' not just chart. – Yoga Jun 8 '16 at 7:00
  • @Raj i have still not find the query that i means, but thank you raj – Yoga Jun 8 '16 at 7:00
1

If you want to query the OEM Repository, you can try something like this:

SELECT  TARGET_NAME,
        TARGET_TYPE,
        TARGET_GUID,
        METRIC_GUID,
        METRIC_LABEL AS METRIC_GROUP,
        COLUMN_LABEL AS METRIC_NAME,
        TO_CHAR(ROLLUP_TIMESTAMP, 'MM/DD/YYYY HH24:MI:SS') AS ROLLUP_TIMESTAMP,
        AVERAGE,
        MINIMUM,
        MAXIMUM
FROM    SYSMAN.MGMT$METRIC_DAILY
WHERE   TARGET_TYPE = 'host'
AND     METRIC_LABEL = 'Load'
AND     COLUMN_LABEL = 'CPU Utilization (%)'
AND     TARGET_NAME = '<hostname>'
AND     ROLLUP_TIMESTAMP >= TO_DATE('05/31/2016','MM/DD/YYYY')
AND     ROLLUP_TIMESTAMP < SYSDATE
ORDER BY    ROLLUP_TIMESTAMP

There are several "metric" views. I am utilizing the METRIC_DAILY view. There is also a METRIC_HOURLY, METRIC_CURRENT. Just find which one is right for you.

| improve this answer | |
  • i have try this query, but the result is no row. are there something wrong? – Yoga Jun 3 '16 at 8:01
  • It returns results for me. I am running 12.1.0.3 but since it runs I doubt there are any real differences in the view. Did you update the where clause appropriately? – Nick S Jun 3 '16 at 10:55
  • i just changed the <hostname> to be my hostname. and nothing changed again after that. but seems no works with me? :( – Yoga Jun 6 '16 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.