一个带select的insert语句调优
本帖最后由 yanan 于 2014-6-5 19:05 编辑环境：oracle 11.2.0.3.4, redhat6.5
从awr中看到一条insert语句很慢，这条sql是带select的insert语句(参见附件)。
在awr中看到1小时只执行了125次，cpu time很高，buffer gets很高。附件中附上awr报告与这个sql的执行计划。麻烦各位帮忙看看如何优化。在此谢过~ 这个insert带的SQL的执行计划有问题：------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 52 (100)| | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | | | |
|* 2 | COUNT STOPKEY | | | | | | | | | | |
| 3 | VIEW | | 1 | 81 | 52 (2)| 00:00:01 | | | | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 62 | 52 (2)| 00:00:01 | | | 2048 | 2048 | 2048 (0)|
| 5 | NESTED LOOPS | | | | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 62 | 51 (0)| 00:00:01 | | | | | |
| 7 | PARTITION LIST ALL | | 1 | 28 | 26 (0)| 00:00:01 | 1 | 12 | | | |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| ACCT_BOOK | 1 | 28 | 26 (0)| 00:00:01 | 1 | 12 | | | |
|* 9 | INDEX RANGE SCAN | IDX_AB_ACCT_BOOK_ID | 1 | | 25 (0)| 00:00:01 | 1 | 12 | | | |
| 10 | PARTITION LIST ALL | | 1 | | 24 (0)| 00:00:01 | 1 | 12 | | | |
|* 11 | INDEX RANGE SCAN | IDX_AB_ACCT_BOOK_ID | 1 | | 24 (0)| 00:00:01 | 1 | 12 | | | |
|* 12 | TABLE ACCESS BY LOCAL INDEX ROWID | ACCT_BOOK | 1 | 34 | 25 (0)| 00:00:01 | 1 | 1 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
统计信息不准确，或者预估不准， 基于这2点优化SQL即可
页:
[1]