Oracle性能調優(yōu)：Oracle性能相關常用腳本（SQL）

打印 | 收藏

在缺乏的可視化工具來監(jiān)控數據庫性能的情形下，常用的腳本就派上用場了，下面提供幾個關于Oracle性能相關的腳本供大家參考。以下腳本均在Oracle 10g測試通過，Oracle 11g可能要做相應調整。

　　1、尋找最多BUFFER_GETS開銷的SQL語句

--filename: top_sql_by_buffer_gets.sql
--Identify heavy SQL (Get the SQL with heavy BUFFER_GETS)
SET LINESIZE 190
COL sql_text FORMAT a100 WRAP
SET PAGESIZE 100

SELECT *
FROM ( SELECT sql_text,
                 sql_id,
                 executions,
                 disk_reads,
                 buffer_gets
            FROM v$sqlarea
           WHERE DECODE (executions, 0, buffer_gets, buffer_gets / executions) >
                    (SELECT AVG (DECODE (executions, 0, buffer_gets, buffer_gets / executions))
                            + STDDEV (DECODE (executions, 0, buffer_gets, buffer_gets / executions))
                       FROM v$sqlarea)
                 AND parsing_user_id != 3D
        ORDER BY 4 DESC) x
WHERE ROWNUM <= 10;

　　2、尋找最多DISK_READS開銷的SQL語句

--filename:top_sql_disk_reads.sql
--Identify heavy SQL (Get the SQL with heavy DISK_READS)
SET LINESIZE 190
COL sql_text FORMAT a100 WRAP
SET PAGESIZE 100

SELECT *
FROM ( SELECT sql_text,
                 sql_id,
                 executions,
                 disk_reads,
                 buffer_gets
            FROM v$sqlarea
           WHERE DECODE (executions, 0, disk_reads, disk_reads / executions) >
                    (SELECT AVG (DECODE (executions, 0, disk_reads, disk_reads / executions))
                            + STDDEV (DECODE (executions, 0, disk_reads, disk_reads / executions))
                       FROM v$sqlarea)
                 AND parsing_user_id != 3D
        ORDER BY 3 DESC) x
WHERE ROWNUM <= 10

上一條：Oracle開發(fā)技術：Oracle的監(jiān)聽常用命令
下一條：Oracle備份恢復：oracle存儲過程注意事項

無相關信息

免费看欧美黑人毛片-免费看毛片网站-免费看毛片的网站-免费看久久-中文字幕日韩欧美-中文字幕日韩精品一区

Oracle性能調優(yōu)：Oracle性能相關常用腳本（SQL）

相關文章