oracle 11g SQL profile惹的祸

前端之家收集整理的这篇文章主要介绍了oracle 11g SQL profile惹的祸前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
接到电话系统有时间会比较慢,先把数据库报告拿回来分析了一下:
1. 可以基本上是并行产生的等待。

Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
latch: parallel query alloc buffer 1,642,487 87.7K 53 69.9 Other
latch: shared pool 732,726 21.9K 30 17.5 Concurrency
PX Deq: Slave Session Stats 7,895 17.7K 2241 14.1 Other
DB cpu
6297.9
5.0
PX Deq: Table Q Get Keys 2,520 4623.5 1835 3.7 Other
IPC send completion sync 100,121 3347.1 33 2.7 Other
latch: row cache objects 38,311 1500.4 39 1.2 Concurrency
cursor: pin S wait on X 22 1406.5 63932 1.1 Concurrency
db file sequential read 3,071,486 874.2 0 .7 User I/O
SGA: allocation forcing component growth 4,725 589.3 125 .5 Other

2.看到第一条sql,执行了很长时间,但是消耗cpu比较少,此sql等待的时间与排名第一的等待事件时间相当。
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %cpu %IO sql Id sql Module sql Text
112,120.06
89.39 3.54 0.50 cm0p4r4768f1s JDBC Thin Client SELECT * FROM(SELECT * FROM (S...
1,702.83 3,982 0.43 1.36 16.77 0.01 8dx0r89m8g1s2 JDBC Thin Client select p.* from b_def_proc...
cpu Time (s) cpu per Exec (s) 63.09 SELECT * FROM(SELECT * F

3.查找此sql的执行计划

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%cpu)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 212 (100)| | | | |
|* 1 | VIEW | | 5043K| 16G| | 212 (1)| 00:00:03 | | | |
| 2 | COUNT | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | |
| 4 | PX SEND QC (ORDER) | :TQ10001 | 5043K| 16G| | 212 (1)| 00:00:03 | Q1,01 | P->S | QC (ORDER) |
| 5 | VIEW | | 5043K| 16G| | 212 (1)| 00:00:03 | Q1,01 | PCWP | |
| 6 | SORT ORDER BY | | 5043K| 3385M| 4377M| 212 (1)| 00:00:03 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 5043K| 3385M| | 211 (0)| 00:00:03 | Q1,01 | PCWP | |
| 8 | PX SEND RANGE | :TQ10000 | 5043K| 3385M| | 211 (0)| 00:00:03 | Q1,00 | P->P | RANGE |
| 9 | PX BLOCK ITERATOR | | 5043K| 3385M| | 211 (0)| 00:00:03 | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL| GG_MARK_RFID | 5043K| 3385M| | 211 (0)| 00:00:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------
................................省略 ................................
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- sql profile SYS_sqlPROF_015667ca8e5a0000 used for this statement

4.清除此profile即可
select * from dba_sql_profiles s where s.name='SYS_sqlPROF_015667ca8e5a0000'; exec dbms_sqltune.drop_sql_profile('SYS_sqlPROF_015667ca8e5a0000');
原文链接:https://www.f2er.com/oracle/212845.html

猜你在找的Oracle相关文章