Oracle 性能相关常用脚本(SQL)

今天药忘吃喽~ 2022-08-10 16:55 238阅读 0赞

在缺乏的可视化工具来监控数据库性能的情形下，常用的脚本就派上用场了，下面提供几个关于Oracle性能相关的脚本供大家参考。以下脚本均在Oracle 10g测试通过，Oracle 11g可能要做相应调整。

1、寻找最多BUFFER\_GETS开销的SQL 语句

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--filename: top\_sql\_by\_buffer\_gets.sql  
2.  \--Identify heavy SQL (Get the SQL with heavy BUFFER\_GETS)  
3.  **SET** LINESIZE 190  
4.  COL sql\_text FORMAT a100 WRAP  
5.  **SET** PAGESIZE 100  
6.    
7.  **SELECT** \*  
8.    **FROM** (  **SELECT** sql\_text,  
9.                   sql\_id,  
10.                  executions,  
11.                  disk\_reads,  
12.                  buffer\_gets  
13.             **FROM** v$sqlarea  
14.            **WHERE** DECODE (executions, 0, buffer\_gets, buffer\_gets / executions) >  
15.                     (**SELECT** AVG (DECODE (executions, 0, buffer\_gets, buffer\_gets / executions))  
16.                             + STDDEV (DECODE (executions, 0, buffer\_gets, buffer\_gets / executions))  
17.                        **FROM** v$sqlarea)  
18.                  AND parsing\_user\_id != 3D  
19.         **ORDER** **BY** 5 **DESC**) x  /\*更正@20140613,原来为**order** **by** 4,感谢网友lmalds指正\*/  
20.  **WHERE** ROWNUM <= 10;

2、寻找最多DISK\_READS开销的SQL 语句

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--filename:top\_sql\_disk\_reads.sql  
2.  \--Identify heavy SQL (Get the SQL with heavy DISK\_READS)  
3.  **SET** LINESIZE 190  
4.  COL sql\_text FORMAT a100 WRAP  
5.  **SET** PAGESIZE 100  
6.    
7.  **SELECT** \*  
8.    **FROM** (  **SELECT** sql\_text,  
9.                   sql\_id,  
10.                  executions,  
11.                  disk\_reads,  
12.                  buffer\_gets  
13.             **FROM** v$sqlarea  
14.            **WHERE** DECODE (executions, 0, disk\_reads, disk\_reads / executions) >  
15.                     (**SELECT** AVG (DECODE (executions, 0, disk\_reads, disk\_reads / executions))  
16.                             + STDDEV (DECODE (executions, 0, disk\_reads, disk\_reads / executions))  
17.                        **FROM** v$sqlarea)  
18.                  AND parsing\_user\_id != 3D  
19.         **ORDER** **BY** 4 **DESC**) x  /\* 更正@20140613,原来为**order** **by** 3,谢谢网友lmalds指正\*/  
20.  **WHERE** ROWNUM <= 10;

3、寻找最近30分钟导致资源过高开销的事件

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--filename:top\_event\_in\_30\_min.sql  
2.  \--Last 30 minutes result those resources that are in high demand on your system.  
3.  **SET** LINESIZE 180  
4.  COL event FORMAT a60  
5.  COL total\_wait\_time FORMAT 999999999999999999  
6.    
7.    **SELECT** active\_session\_history.event,  
8.           SUM (  
9.              active\_session\_history.wait\_time  
10.             + active\_session\_history.time\_waited)  
11.             total\_wait\_time  
12.     **FROM** v$active\_session\_history active\_session\_history  
13.    **WHERE** active\_session\_history.sample\_time BETWEEN SYSDATE - 60 / 2880  
14.                                                 AND SYSDATE  
15.          AND active\_session\_history.event **IS** NOT NULL  
16. **GROUP** **BY** active\_session\_history.event  
17. **ORDER** **BY** 2 **DESC**;

4、查找最近30分钟内等待最多的用户

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--filename:top\_wait\_by\_user.sql  
2.  \--What user is waiting the most?  
3.    
4.  **SET** LINESIZE 180  
5.  COL event FORMAT a60  
6.  COL total\_wait\_time FORMAT 999999999999999999  
7.    
8.    **SELECT** ss.sid,  
9.           NVL (ss.username, 'oracle') **AS** username,  
10.          SUM (ash.wait\_time + ash.time\_waited) total\_wait\_time  
11.     **FROM** v$active\_session\_history ash, v$session ss  
12.    **WHERE** ash.sample\_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE AND ash.session\_id = ss.sid  
13. **GROUP** **BY** ss.sid, ss.username  
14. **ORDER** **BY** 3 **DESC**;

5、查找30分钟消耗最多资源的SQL语句

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--filename:top\_sql\_by\_wait.sql  
2.  \-- What SQL is currently using the most resources?  
3.  **SET** LINESIZE 180  
4.  COL sql\_text FORMAT a90 WRAP  
5.  COL username FORMAT a20 WRAP  
6.  **SET** PAGESIZE 200  
7.    
8.  **SELECT** \*  
9.    **FROM** (  **SELECT** sqlarea.sql\_text,  
10.                  dba\_users.username,  
11.                  sqlarea.sql\_id,  
12.                  SUM (active\_session\_history.wait\_time + active\_session\_history.time\_waited)  
13.                     total\_wait\_time  
14.             **FROM** v$active\_session\_history active\_session\_history, v$sqlarea sqlarea, dba\_users  
15.            **WHERE**     active\_session\_history.sample\_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE  
16.                  AND active\_session\_history.sql\_id = sqlarea.sql\_id  
17.                  AND active\_session\_history.user\_id = dba\_users.user\_id  
18.         **GROUP** **BY** active\_session\_history.user\_id,  
19.                  sqlarea.sql\_text,  
20.                  sqlarea.sql\_id,  
21.                  dba\_users.username  
22.         **ORDER** **BY** 4 **DESC**) x  
23.  **WHERE** ROWNUM <= 11;

6、等待最多的对象

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--filename:top\_object\_by\_wait.sql  
2.  \--What object is currently causing the highest resource waits?  
3.  **SET** LINESIZE 180  
4.  **COLUMN** OBJECT\_NAME FORMAT a30  
5.  **COLUMN** EVENT FORMAT a30  
6.    
7.    **SELECT** dba\_objects.object\_name,  
8.           dba\_objects.object\_type,  
9.           active\_session\_history.event,  
10.          SUM (active\_session\_history.wait\_time + active\_session\_history.time\_waited) ttl\_wait\_time  
11.     **FROM** v$active\_session\_history active\_session\_history, dba\_objects  
12.    **WHERE** active\_session\_history.sample\_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE  
13.          AND active\_session\_history.current\_obj\# = dba\_objects.object\_id  
14. **GROUP** **BY** dba\_objects.object\_name, dba\_objects.object\_type, active\_session\_history.event  
15. **ORDER** **BY** 4 **DESC**;

7、寻找基于指定时间范围内的历史SQL语句

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--注该查询受到awr快照相关参数的影响  
2.  \-- filename:top\_sql\_in\_spec\_time.sql  
3.  \--Top SQLs Elaps time and CPU time in a given time range..  
4.  \--X.ELAPSED\_TIME/1000000 => From Micro second to second  
5.  \--X.ELAPSED\_TIME/1000000/X.EXECUTIONS\_DELTA => How many times the sql ran  
6.    
7.  **SET** PAUSE **ON**  
8.  **SET** PAUSE 'Press Return To Continue'  
9.  **SET** LINESIZE 180  
10. COL sql\_text FORMAT a80 WRAP  
11.   
12.   **SELECT** sql\_text,  
13.          dhst.sql\_id,  
14.          ROUND (x.elapsed\_time / 1000000 / x.executions\_delta, 3) elapsed\_time\_sec,  
15.          ROUND (x.cpu\_time / 1000000 / x.executions\_delta, 3) cpu\_time\_sec,  
16.          x.elapsed\_time,  
17.          x.cpu\_time,  
18.          executions\_delta **AS** exec\_delta  
19.     **FROM** dba\_hist\_sqltext dhst,  
20.          (  **SELECT** dhss.sql\_id sql\_id,  
21.                    SUM (dhss.cpu\_time\_delta) cpu\_time,  
22.                    SUM (dhss.elapsed\_time\_delta) elapsed\_time,  
23.                    CASE SUM (dhss.executions\_delta) **WHEN** 0 **THEN** 1 **ELSE** SUM (dhss.executions\_delta) **END**  
24.                       **AS** executions\_delta  
25.               **FROM** dba\_hist\_sqlstat dhss  
26.              **WHERE** dhss.snap\_id IN  
27.                       (**SELECT** snap\_id  
28.                          **FROM** dba\_hist\_snapshot  
29.                         **WHERE** begin\_interval\_time >= TO\_DATE ('&input\_start\_date', 'YYYYMMDD HH24:MI')  
30.                               AND end\_interval\_time <= TO\_DATE ('&input\_end\_date', 'YYYYMMDD HH24:MI'))  
31.           **GROUP** **BY** dhss.sql\_id) x  
32.    **WHERE** x.sql\_id = dhst.sql\_id  
33. **ORDER** **BY** elapsed\_time\_sec **DESC**;

8、寻找基于指定时间范围内及指定用户的历史SQL语句

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--注该查询受到awr快照相关参数的影响  
2.  \--Author : Robinson  
3.  \--Blog   : http://blog.csdn.net/robinson\_0612  
4.    
5.  **SELECT** DBMS\_LOB.SUBSTR (sql\_text, 4000, 1) **AS** sql,  
6.           ROUND (x.elapsed\_time / 1000000, 2) elapsed\_time\_sec,  
7.           ROUND (x.cpu\_time / 1000000, 2) cpu\_time\_sec,  
8.           x.executions\_delta **AS** exec\_num,  
9.           ROUND ( (x.elapsed\_time / 1000000) / x.executions\_delta, 2) **AS** exec\_time\_per\_query\_sec  
10.     **FROM** dba\_hist\_sqltext dhst,  
11.          (  **SELECT** dhss.sql\_id sql\_id,  
12.                    SUM (dhss.cpu\_time\_delta) cpu\_time,  
13.                    SUM (dhss.elapsed\_time\_delta) elapsed\_time,  
14.                    CASE SUM (dhss.executions\_delta) **WHEN** 0 **THEN** 1 **ELSE** SUM (dhss.executions\_delta) **END**  
15.                       **AS** executions\_delta  
16.               \--DHSS.EXECUTIONS\_DELTA = No of queries execution (per hour)  
17.               **FROM** dba\_hist\_sqlstat dhss  
18.              **WHERE** dhss.snap\_id IN  
19.                       (**SELECT** snap\_id  
20.                          **FROM** dba\_hist\_snapshot  
21.                         **WHERE** begin\_interval\_time >= TO\_DATE ('&input\_start\_date', 'YYYYMMDD HH24:MI')  
22.                               AND end\_interval\_time <= TO\_DATE ('&input\_end\_date', 'YYYYMMDD HH24:MI'))  
23.                    AND dhss.parsing\_schema\_name LIKE UPPER ('%&input\_username%')  
24.           **GROUP** **BY** dhss.sql\_id) x  
25.    **WHERE** x.sql\_id = dhst.sql\_id  
26. **ORDER** **BY** elapsed\_time\_sec **DESC**;

9、SQL语句被执行的次数

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  \--exe\_delta表明在指定时间内增长的次数  
2.  \-- filename: sql\_exec\_num.sql  
3.  \-- How many Times a query executed?  
4.  **SET** LINESIZE 180  
5.  **SET** VERIFY **OFF**  
6.    
7.    **SELECT** TO\_CHAR (s.begin\_interval\_time, 'yyyymmdd hh24:mi:ss'),  
8.           sql.sql\_id **AS** sql\_id,  
9.           sql.executions\_delta **AS** exe\_delta,  
10.          sql.executions\_total  
11.     **FROM** dba\_hist\_sqlstat sql, dba\_hist\_snapshot s  
12.    **WHERE**     sql\_id = '&input\_sql\_id'  
13.          AND s.snap\_id = sql.snap\_id  
14.          AND s.begin\_interval\_time > TO\_DATE ('&input\_start\_date', 'YYYYMMDD HH24:MI')  
15.          AND s.begin\_interval\_time < TO\_DATE ('&input\_end\_date', 'YYYYMMDD HH24:MI')  
16. **ORDER** **BY** s.begin\_interval\_time;

[view plain]: http://blog.csdn.net/leshami/article/details/8904804#
[CODE]: https://code.csdn.net/assets/CODE_ico.png
[CODE_CODE]: https://code.csdn.net/snippets/390911
[ico_fork.svg]: https://code.csdn.net/assets/ico_fork.svg
[ico_fork.svg 1]: https://code.csdn.net/snippets/390911/fork