SQL to get performance metrics shown in EM Express 12C Resources

Question

I would like to start recording some historical performance metrics on my Oracle database server. I can use these metrics to evaluate growth of database resource requirements over time. When it's time to upgrade or replace the server, I can use this data to choose the right sized server. The important metrics for me to collect are CPU percentage used, memory usage broken out by process, and although it isn't shown in EM, disk latency metrics would also help. Below is a screenshot: We don't have any of the additional performance packs licensed, but I have to figure if EM can query it every minute then I should be able to get it too. Can someone give an example of a query or queries that can provide this data?

Answer 1

STATSPACK comes out of the box with Oracle Database Standard Edition. It works by comparing two snapshots.

https://docs.oracle.com/cd/E13160_01/wli/docs10gr3/dbtuning/statsApdx.html

Install:

cd $ORACLE_HOME/rdbms/admin/
sqlplus "/ as sysdba" @spcreate.sql

Take a snapshot:

sqlplus perfstat/<password>
execute statspack.snap(i_snap_level=>7);

Generate a report:

cd $ORACLE_HOME/rdbms/admin/
sqlplus perfstat/<password> @spreport

Answer 2

For CPU would the following work?

CREATE GLOBAL TEMPORARY TABLE TS_SYSTEM_EVENT ON COMMIT DELETE ROWS AS   
SELECT   
EVENT,   
TOTAL_WAITS,   
TOTAL_TIMEOUTS,   
TIME_WAITED,   
TIME_WAITED_MICRO   
FROM  
V$SYSTEM_EVENT  
WHERE 0=1; 

CREATE GLOBAL TEMPORARY TABLE TE_SYSTEM_EVENT ON COMMIT DELETE ROWS AS   
SELECT   
EVENT,   
TOTAL_WAITS,   
TOTAL_TIMEOUTS,   
TIME_WAITED,   
TIME_WAITED_MICRO   
FROM  
V$SYSTEM_EVENT  
WHERE 0=1;

CREATE GLOBAL TEMPORARY TABLE TS_OSSTAT ON COMMIT DELETE ROWS AS
SELECT STAT_NAME,VALUE
FROM V$OSSTAT
WHERE 0=1;

CREATE GLOBAL TEMPORARY TABLE TE_OSSTAT ON COMMIT DELETE ROWS AS
SELECT
STAT_NAME,
VALUE
FROM V$OSSTAT
WHERE 0=1;

CREATE GLOBAL TEMPORARY TABLE TS_SYS_TIME_MODEL ON COMMIT DELETE ROWS AS
SELECT   
STAT_NAME,   
VALUE   
FROM   
V$SYS_TIME_MODEL   
WHERE   
0=1;

CREATE GLOBAL TEMPORARY TABLE TE_SYS_TIME_MODEL ON COMMIT DELETE ROWS AS   
SELECT   
STAT_NAME,   
VALUE   
FROM   
V$SYS_TIME_MODEL   
WHERE   
0=1;

CREATE GLOBAL TEMPORARY TABLE TS_SYSSTAT ON COMMIT DELETE ROWS AS   
SELECT   
NAME,   
VALUE   
FROM   
V$SYSSTAT   
WHERE 0=1;

CREATE GLOBAL TEMPORARY TABLE TE_SYSSTAT ON COMMIT DELETE ROWS AS   
SELECT   
NAME,   
VALUE   
FROM   
V$SYSSTAT   
WHERE 0=1; 

COMMIT;

DEFINE CAPTURE_SECONDS=600

INSERT INTO TS_SYSTEM_EVENT   
SELECT   
EVENT,   
TOTAL_WAITS,   
TOTAL_TIMEOUTS,   
TIME_WAITED,   
TIME_WAITED_MICRO   
FROM V$SYSTEM_EVENT;

INSERT INTO TS_OSSTAT
SELECT 
STAT_NAME,
VALUE
FROM V$OSSTAT;

INSERT INTO TS_SYS_TIME_MODEL   
SELECT   
STAT_NAME,   
VALUE   
FROM V$SYS_TIME_MODEL;

INSERT INTO TS_SYSSTAT   
SELECT   
NAME,   
VALUE   
FROM V$SYSSTAT; 

EXEC DBMS_LOCK.SLEEP(&&CAPTURE_SECONDS); 

INSERT INTO TE_SYSTEM_EVENT   
SELECT   
EVENT,   
TOTAL_WAITS,   
TOTAL_TIMEOUTS,   
TIME_WAITED,   
TIME_WAITED_MICRO   
FROM V$SYSTEM_EVENT;

INSERT INTO TE_OSSTAT
SELECT   
STAT_NAME,
VALUE   
FROM
V$OSSTAT;

INSERT INTO TE_SYS_TIME_MODEL   
SELECT   
STAT_NAME,   
VALUE   
FROM   
V$SYS_TIME_MODEL;

INSERT INTO TE_SYSSTAT   
SELECT   
NAME,   
VALUE   
FROM V$SYSSTAT

SPOOL quickcheck.txt 

SET PAGESIZE 200 
COL WAIT_CLASS FORMAT A15 TRU 
COL DELTA FORMAT 9999999990 
COL DELTA_SEC FORMAT 99999990.00 
COL WAIT_PERCENT FORMAT 990.00

/* Ranked wait time by wait class, including CPU used – note that the ‘CPU used
by this session’ statistic is not posted until in-process queries complete */ 

SELECT   
WAIT_CLASS,   
DELTA,   
DELTA_SEC,   
ROUND(RATIO_TO_REPORT(DELTA) OVER () * 100,2) WAIT_PERCENT   
FROM   
(SELECT   
EN.WAIT_CLASS,   
SUM(TE.TIME_WAITED-NVL(TS.TIME_WAITED,0)) DELTA,  
SUM(ROUND((TE.TIME_WAITED-NVL(TS.TIME_WAITED,0))/ &&CAPTURE_SECONDS,2)) DELTA_SEC  
FROM  
TE_SYSTEM_EVENT TE,  
TS_SYSTEM_EVENT TS,  
V$EVENT_NAME EN  
WHERE  
TE.EVENT=TS.EVENT(+)  
AND TE.TIME_WAITED-NVL(TS.TIME_WAITED,0)>0  
AND TE.EVENT=EN.NAME  
AND EN.WAIT_CLASS<>'Idle'  
GROUP BY  
EN.WAIT_CLASS  
UNION ALL  
SELECT  
'CPU' WAIT_CLASS,  
ROUND(SUM((TE.VALUE-NVL(TS.VALUE,0))/10000),0) DELTA,  
ROUND(SUM((TE.VALUE-NVL(TS.VALUE,0))/10000)/ &&CAPTURE_SECONDS,2) DELTA_SEC  
FROM  
TE_SYS_TIME_MODEL TE,  
TS_SYS_TIME_MODEL TS  
WHERE  
TE.STAT_NAME IN ('DB CPU', 'background cpu time')  
AND TE.STAT_NAME=TS.STAT_NAME(+) )  
ORDER BY  
WAIT_CLASS; 

COL EVENT FORMAT A30 TRU 

/* Wait time by wait event name */
SELECT   
TE.EVENT,   
TE.TOTAL_WAITS-NVL(TS.TOTAL_WAITS,0) WAITS,   
TE.TOTAL_TIMEOUTS-NVL(TS.TOTAL_TIMEOUTS,0) T_OUTS,   
TE.TIME_WAITED-NVL(TS.TIME_WAITED,0) DELTA,   
ROUND((TE.TIME_WAITED-NVL(TS.TIME_WAITED,0))/ &&CAPTURE_SECONDS,2) DELTA_SEC   
FROM 
TE_SYSTEM_EVENT TE,   
TS_SYSTEM_EVENT TS  
WHERE  
TE.EVENT=TS.EVENT(+)  
AND TE.TIME_WAITED-NVL(TS.TIME_WAITED,0)>0  
ORDER BY  
TE.EVENT; 

COL STAT_NAME FORMAT A25 TRU 

/* Operating system statistics */
SELECT   
TE.STAT_NAME,   
TE.VALUE END_VALUE,   
TE.VALUE-NVL(TS.VALUE,0) DELTA,   
ROUND((TE.VALUE-NVL(TS.VALUE,0))/ &&CAPTURE_SECONDS,2) DELTA_SEC   
FROM   
TE_OSSTAT TE,   
TS_OSSTAT TS   
WHERE  
TE.STAT_NAME=TS.STAT_NAME(+)  
ORDER BY  
TE.STAT_NAME;

COL STAT_NAME FORMAT A50 TRU

/* CPU time and elapsed time for Oracle activities */ 
SELECT   
TE.STAT_NAME,   
TE.VALUE-NVL(TS.VALUE,0) DELTA,   
ROUND((TE.VALUE-NVL(TS.VALUE,0))/ &&CAPTURE_SECONDS,2) DELTA_SEC   
FROM   
TE_SYS_TIME_MODEL TE,   
TS_SYS_TIME_MODEL TS   
WHERE   
TE.STAT_NAME=TS.STAT_NAME(+)  
ORDER BY  
TE.STAT_NAME; 

COL NAME FORMAT A50 TRU 

/* System level statistics */ 
SELECT   
TE.NAME,   
TE.VALUE-NVL(TS.VALUE,0) DELTA,   
ROUND((TE.VALUE-NVL(TS.VALUE,0))/ &&CAPTURE_SECONDS,2) DELTA_SEC   
FROM   
TE_SYSSTAT TE,   
TS_SYSSTAT TS   
WHERE   
TE.NAME=TS.NAME(+)
AND TE.VALUE-NVL(TS.VALUE,0)<>0  
ORDER BY  
TE.NAME;

SELECT   
BUSY_TIME/(BUSY_TIME+IDLE_TIME)*100 PERCENT_BUSY   
FROM   
(SELECT   
MAX(DECODE(TE.STAT_NAME,'BUSY_TIME',TE.VALUE-NVL(TS.VALUE,0),NULL)) BUSY_TIME,   
MAX(DECODE(TE.STAT_NAME,'IDLE_TIME',TE.VALUE-NVL(TS.VALUE,0),NULL)) IDLE_TIME   
FROM   
TE_OSSTAT TE,   
TS_OSSTAT TS  
WHERE  
TE.STAT_NAME=TS.STAT_NAME(+)  
AND TE.STAT_NAME IN ('BUSY_TIME','IDLE_TIME'));

SELECT   
ROUND(S.VALUE/O.VALUE/100,2) ORACLE_CPU_PERCENT   
FROM   
(SELECT   
SUM(TE.VALUE-NVL(TS.VALUE,0)) VALUE   
FROM   
TE_SYS_TIME_MODEL TE,   
TS_SYS_TIME_MODEL TS   
WHERE  
TE.STAT_NAME IN ('DB CPU', 'background cpu time')  
AND TE.STAT_NAME=TS.STAT_NAME(+) ) S,  
(SELECT  
TE.VALUE-NVL(TS.VALUE,0) VALUE  
FROM  
TE_OSSTAT TE,  
TS_OSSTAT TS  
WHERE  
TE.STAT_NAME='BUSY_TIME'  
AND TE.STAT_NAME=TS.STAT_NAME(+) ) O;

For Memory you can use the following:

    SELECT s.sid,p.spid,pm.*
    FROM  v$session s, v$process p, v$process_memory pm
    WHERE s.paddr = p.addr AND p.pid = pm.pid
    ORDER BY sid, category

You also might want to check: activating V$PROCESS_MEMORY_DETAIL:

    - To activate this view can one of following commands executed: 
    SQL> alter session set events'immediate trace name PGA_DETAIL_GET level 
    <PID>'; 
    From ORADEBUG: 
    SQL> ORADEBUG SETMYPID; 
    SQL> ORADEBUG DUMP PGA_DETAIL_GET <PID>; 
    - To remove all rows in the view run following command: 
    SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL 
    level <PID>'; 
    From ORADEBUG: 
    SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL <PID>;

Once activated you can run:

    SQL> Select * from v$process_memory_detail;