Oracle-12c

SQL 獲取 EM Express 12C 資源中顯示的性能指標

  • June 13, 2017

我想開始在我的 Oracle 數據庫伺服器上記錄一些歷史性能指標。我可以使用這些指標來評估數據庫資源需求隨時間的增長。當需要升級或更換伺服器時，我可以使用這些數據來選擇合適大小的伺服器。我要收集的重要指標是 CPU 使用百分比、按程序劃分的記憶體使用情況，雖然它沒有在 EM 中顯示，但磁碟延遲指標也會有所幫助。下面是一個截圖： 在此處輸入圖像描述 我們沒有任何額外的性能包許可，但我必須弄清楚如果 EM 可以每分鐘查詢一次，那麼我也應該能夠得到它。有人可以舉一個可以提供此數據的查詢範例嗎？

STATSPACK 隨 Oracle 數據庫標準版開箱即用。它通過比較兩個快照來工作。

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

安裝：

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

拍攝快照：

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

生成報告：

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

對於 CPU，以下工作是否可行？

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;

對於記憶體，您可以使用以下內容：

   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

您可能還想檢查：啟動 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>;

啟動後，您可以執行：

   SQL> Select * from v$process_memory_detail;

引用自：https://dba.stackexchange.com/questions/175809

相關問答

Oracle

Oracle 12c - 邏輯上有重做組但物理上沒有 - 數據庫無法打開

  • January 5, 2022
檢視問答
Oracle

Oracle 12c Express Enterprise Manager 網頁無法載入

  • June 3, 2020
檢視問答
Oracle

Centos 6.5 上的 Oracle 12c Express Enterprise Manager 無法訪問

  • May 10, 2019
檢視問答
Oracle

Oracle 12c Enterprise Manager Database Express 的“無法顯示頁面”

  • September 24, 2018
檢視問答
Oracle

我可以在標準版上配置企業管理器雲控制嗎

  • February 18, 2018
檢視問答
Oracle

VM 模板中 Oracle Enterprise Manager 的登錄名/密碼？

  • January 13, 2018
檢視問答