oracle 11g SQL profile惹的祸

发布时间：2020-12-12 16:03:12 所属栏目：百科来源：网络整理

导读：接到电话系统有时间会比较慢，先把数据库报告拿回来分析了一下： 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

latch: parallel query alloc buffer1,642,48787.7K5369.9Otherlatch: shared pool732,72621.9K3017.5ConcurrencyPX Deq: Slave Session Stats7,89517.7K224114.1OtherDB CPU
6297.9
5.0
PX Deq: Table Q Get Keys2,5204623.518353.7OtherIPC send completion sync100,1213347.1332.7Otherlatch: row cache objects38,3111500.4391.2Concurrencycursor: pin S wait on X221406.5639321.1Concurrencydb file sequential read3,071,486874.20.7User I/OSGA: allocation forcing component growth4,725589.3125.5Other
2.看到第一条SQL，执行了很长时间，但是消耗CPU比较少，此SQL等待的时间与排名第一的等待事件时间相当。112,120.06
89.393.540.50cm0p4r4768f1sJDBC Thin ClientSELECT * FROM(SELECT * FROM (S...1,702.833,9820.431.3616.770.018dx0r89m8g1s2JDBC Thin Clientselect p.* from b_def_proc...
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');

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

接到电话系统有时间会比较慢，先把数据库报告拿回来分析了一下： 1. 可以基本上是并行产生的等待。 Top 10 Foreground Events by Total Wait Time
Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
CPU Time (s)	CPU per Exec (s)	63.09 SELECT * FROM(SELECT * F

接到电话系统有时间会比较慢，先把数据库报告拿回来分析了一下： 1. 可以基本上是并行产生的等待。

Top 10 Foreground Events by Total Wait Time

Event

Waits

Total Wait Time (sec)

Wait Avg(ms)

% DB time

Wait Class

Elapsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

SQL Id

SQL Module

SQL Text

CPU Time (s)

CPU per Exec (s)

63.09 SELECT * FROM(SELECT * F