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