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	0		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)	Executions	CPU per Exec (s)	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
3,973.48	0		63.09	112,120.06	3.54	0.50	cm0p4r4768f1s	JDBC Thin Client	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');