資料庫效能SQL監控指令碼

--1、消耗時間最多的前10條SQL語句；
SELECT round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
       executions,
       round(elapsed_time / executions / 1000, 2) "Elapsed Time per Exec (ms)",
       parsing_schema_name "username",
       sql_id,
       sql_fulltext
  FROM (SELECT parsing_schema_name,
               sql_id,
               sql_fulltext,
               executions,
               elapsed_time,
               rank() over(ORDER BY elapsed_time DESC NULLS LAST) elapsed_time_rank
          FROM v$sql
         WHERE parsing_schema_name NOT IN
               ('SYS',
                'SYSTEM',
                'SYSMAN',
                'ORACLE_OCM',
                'EXFSYS',
                'DBSNMP',
                'MDSYS',
                'SINO_DBA',
                'ADMIN',
                'APEX_030200'))
 WHERE elapsed_time_rank <= 10;


--2、消耗記憶體資源最多的前10條SQL語句；
SELECT buffer_gets,
       executions,
       round(buffer_gets / decode(executions, 0, 1, executions), 0) "Gets per Exec",
       round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
       parsing_schema_name "username",
       sql_fulltext,
       sql_id
  FROM (SELECT parsing_schema_name,
               sql_id,
               sql_fulltext,
               executions,
               buffer_gets,
               elapsed_time,
               dense_rank() over(ORDER BY buffer_gets DESC NULLS LAST) buffer_gets_rank
          FROM v$sql t
         WHERE parsing_schema_name NOT IN
               ('SYS',
                'SYSTEM',
                'SYSMAN',
                'ORACLE_OCM',
                'EXFSYS',
                'DBSNMP',
                'MDSYS',
                'SINO_DBA',
                'ADMIN',
                'APEX_030200'))
 WHERE buffer_gets_rank <= 10;


--3、消耗I/O資源最多的前10條SQL語句；
SELECT disk_reads,
       executions,
       round(disk_reads / decode(executions, 0, 1, executions), 0) "Reads per Exec",
       round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
       parsing_schema_name "username",
       sql_fulltext,
       sql_id
  FROM (SELECT parsing_schema_name,
               sql_id,
               sql_fulltext,
               executions,
               disk_reads,
               elapsed_time,
               dense_rank() over(ORDER BY disk_reads DESC NULLS LAST) disk_reads_rank
          FROM v$sql t
         WHERE parsing_schema_name NOT IN
               ('SYS',
                'SYSTEM',
                'SYSMAN',
                'ORACLE_OCM',
                'EXFSYS',
                'DBSNMP',
                'MDSYS',
                'SINO_DBA',
                'ADMIN',
                'APEX_030200'))
 WHERE disk_reads_rank <= 10;


--4、執行頻率最多的前10條SQL語句；
SELECT executions,
       rows_processed,
       round(rows_processed / decode(executions, 0, 1, executions), 0) "Rows per Exec",
       round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
       parsing_schema_name "username",
       sql_fulltext,
       sql_id
  FROM (SELECT sql_id,
               sql_fulltext,
               executions,
               rows_processed,
               elapsed_time,
               parsing_schema_name,
               rank() over(ORDER BY executions DESC NULLS LAST) exec_rank
          FROM v$sql
         WHERE parsing_schema_name NOT IN
               ('SYS',
                'SYSTEM',
                'SYSMAN',
                'ORACLE_OCM',
                'EXFSYS',
                'DBSNMP',
                'MDSYS',
                'SINO_DBA',
                'ADMIN',
                'APEX_030200'))
 WHERE exec_rank <= 10;


---5、解析比最高的前10條SQL語句；
SELECT parse_calls,
       executions,
       round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
       parsing_schema_name "username",
       sql_fulltext,
       sql_id
  FROM (SELECT sql_id,
               sql_fulltext,
               executions,
               parse_calls,
               elapsed_time,
               parsing_schema_name,
               rank() over(ORDER BY parse_calls DESC NULLS LAST) parse_calls_rank
          FROM v$sql
         WHERE parsing_schema_name NOT IN
               ('SYS',
                'SYSTEM',
                'SYSMAN',
                'ORACLE_OCM',
                'EXFSYS',
                'DBSNMP',
                'MDSYS',
                'SINO_DBA',
                'ADMIN',
                'APEX_030200'))
 WHERE parse_calls_rank <= 10;


--6、排序次數最多的前10條SQL語句；
SELECT sorts,
       executions,
       round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
       parsing_schema_name "username",
       sql_fulltext,
       sql_id
  FROM (SELECT sql_id,
               sql_fulltext,
               executions,
               sorts,
               elapsed_time,
               parsing_schema_name,
               rank() over(ORDER BY sorts DESC NULLS LAST) sorts_rank
          FROM v$sql
         WHERE parsing_schema_name NOT IN
               ('SYS',
                'SYSTEM',
                'SYSMAN',
                'ORACLE_OCM',
                'EXFSYS',
                'DBSNMP',
                'MDSYS',
                'SINO_DBA',
                'ADMIN',
                'APEX_030200'))
 WHERE sorts_rank <= 10;


--7、Cost成本值最大的前10條SQL語句；
SELECT optimizer_cost,
       executions,
       round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
       parsing_schema_name "username",
       sql_fulltext,
       sql_id
  FROM (SELECT sql_id,
               sql_fulltext,
               executions,
               optimizer_cost,
               elapsed_time,
               parsing_schema_name,
               rank() over(ORDER BY optimizer_cost DESC NULLS LAST) optimizer_cost_rank
          FROM v$sql
         WHERE parsing_schema_name NOT IN
               ('SYS',
                'SYSTEM',
                'SYSMAN',
                'ORACLE_OCM',
                'EXFSYS',
                'DBSNMP',
                'MDSYS',
                'SINO_DBA',
                'ADMIN',
                'APEX_030200'))
 WHERE optimizer_cost_rank <= 10;


--8、檢視錶空間分配與空閒空間情況；
SELECT a.tablespace_name,
       a.file_name,
       round(a.bytes / 1024 / 1024, 0) allocated_mbytes,
       b.free_mbytes
  FROM dba_data_files a,
       (SELECT file_id, round(SUM(bytes) / 1024 / 1024, 0) free_mbytes
          FROM dba_free_space b
         GROUP BY file_id) b
 WHERE a.file_id = b.file_id
 ORDER BY a.tablespace_name;


--9、顯示錶物件鎖的會話資訊；
SELECT o.owner,
       o.object_name,
       o.object_type,
       s.sid,
       s.serial#,
       s.machine,
       s.program,
       s.osuser
  FROM v$session s, v$locked_object l, dba_objects o
 WHERE l.session_id = s.sid
   AND l.object_id = o.object_id(+);


--10、顯示資料庫歸檔日誌定時備份情況資訊；
SELECT btype,
       btype_key,
       sequence#,
       first_change#,
       next_change#,
       filesize,
       filesize_display
  FROM v$backup_archivelog_details t
 ORDER BY sequence#;


--11、顯示資料庫資料檔案定時備份情況資訊； 
SELECT btype,
       btype_key,
       file#,
       incremental_level,
       checkpoint_time,
       filesize,
       tsname,
       filesize_display
  FROM v$backup_datafile_details
 ORDER BY btype_key,file#;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29439655/viewspace-1378538/，如需轉載，請註明出處，否則將追究法律責任。