-- 生成AWR報告
DECLARE
   v_dbid           NUMBER;
   v_inst_num       NUMBER;
   v_report_type    VARCHAR2(1) := 'HTML';
   v_report_text    CLOB;
BEGIN
   -- 獲取數(shù)據(jù)庫ID和實例號
   SELECT dbid, instance_number INTO v_dbid, v_inst_num FROM v$instance;

   -- 生成AWR報告
   v_report_text := DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
      l_dbid => v_dbid,
      l_inst_num => v_inst_num,
      l_bid => 100,  -- 開始快照ID
      l_eid => 110,  -- 結束快照ID
      l_rpt_options => 0
   );

   -- 輸出報告
   DBMS_OUTPUT.PUT_LINE(v_report_text);
END;
/

SELECT sample_time, session_id, user_id, sql_id, event, wait_time
FROM v$active_session_history
WHERE sample_time BETWEEN SYSDATE - INTERVAL '1' HOUR AND SYSDATE;

SELECT stat_name, value
FROM v$osstat
WHERE stat_name IN ('NUM_CPUS', 'IDLE_TIME', 'USER_TIME', 'SYS_TIME');

-- SGA內(nèi)存使用情況
SELECT * FROM v$sgainfo;

-- PGA內(nèi)存使用情況
SELECT * FROM v$pgastat;

SELECT file#, phyrds, phywrts, readtim, writetim
FROM v$filestat;

SELECT name, value
FROM v$sysstat
WHERE name IN ('bytes received via SQL*Net from client', 'bytes sent via SQL*Net to client');

SELECT sid, serial#, username, status, osuser, machine
FROM v$session
WHERE username IS NOT NULL;

SELECT sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC;

SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

DECLARE
   v_cpu_usage       NUMBER;
   v_memory_usage    NUMBER;
   v_disk_io         NUMBER;
   v_network_io      NUMBER;
   v_active_sessions NUMBER;
BEGIN
   -- 獲取CPU使用率
   SELECT value INTO v_cpu_usage
   FROM v$osstat
   WHERE stat_name = 'USER_TIME';

   -- 獲取內(nèi)存使用情況
   SELECT SUM(bytes) INTO v_memory_usage
   FROM v$sgainfo;

   -- 獲取磁盤I/O
   SELECT SUM(phyrds + phywrts) INTO v_disk_io
   FROM v$filestat;

   -- 獲取網(wǎng)絡I/O
   SELECT SUM(value) INTO v_network_io
   FROM v$sysstat
   WHERE name IN ('bytes received via SQL*Net from client', 'bytes sent via SQL*Net to client');

   -- 獲取活動會話數(shù)
   SELECT COUNT(*) INTO v_active_sessions
   FROM v$session
   WHERE status = 'ACTIVE';

   -- 輸出監(jiān)控結果
   DBMS_OUTPUT.PUT_LINE('CPU Usage: ' || v_cpu_usage);
   DBMS_OUTPUT.PUT_LINE('Memory Usage: ' || v_memory_usage);
   DBMS_OUTPUT.PUT_LINE('Disk I/O: ' || v_disk_io);
   DBMS_OUTPUT.PUT_LINE('Network I/O: ' || v_network_io);
   DBMS_OUTPUT.PUT_LINE('Active Sessions: ' || v_active_sessions);
END;
/