同事在日常巡检中发现一套数据库资源瓶颈严重，如下图所示：
![](http://albertdba.com/"wp-content/uploads"/2020/10/20201005-2-1024x476.png)
活动会话多数在等待read by other session与CPU，根据硬件，多数为SQL问题导致，协助同事最终定位了TOP SQL，该SQL执行计划与执行效率信息如下图所示：
![](http://albertdba.com/"wp-content/uploads"/2020/10/20201005-5-1024x440.png)
![](http://albertdba.com/"wp-content/uploads"/2020/10/20201005-6-1024x260.png)
从执行计划中得知，最消耗资源的为TABLE ACCESS FULL全表扫描这步，该SQL有上百行，截取执行计划中此步SQL_TEXT如下：
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2pzyb63zhr1qp, child number 0
-------------------------------------
SELECT *
FROM
(SELECT iti.owner,
iti.object_name,
iti.object_type,
iti.OBJECT_ID,
iti.DATA_OBJECT_ID,
iti.STATUS,
iti.CREATED,
iti.LAST_DDL_TIME,
iti.SECONDARY,
iti.NAMESPACE,
iti.EDITION_NAME
FROM t iti
WHERE iti.OBJECT_ID > 88
AND iti.object_type != 'INDEX'
ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100
Plan hash value: 3299198703
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 657K(100)| | 100 |00:00:07.90 | 263K|
|* 1 | COUNT STOPKEY | | 1 | | | | 100 |00:00:07.90 | 263K|
| 2 | VIEW | | 1 | 15M| 657K (1)| 02:11:28 | 100 |00:00:07.90 | 263K|
|* 3 | SORT ORDER BY STOPKEY| | 1 | 15M| 657K (1)| 02:11:28 | 100 |00:00:07.90 | 263K|
|* 4 | TABLE ACCESS FULL | T | 1 | 15M| 71738 (1)| 00:14:21 | 17M|00:00:03.36 | 263K|
------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
4 - SEL$2 / iti@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)可以看到，该SQL用了232K个BUFFER，大约用了7秒多的时间。根据SQL逻辑,我们可以建立created,object_id两个字段索引，使得执行计划只扫描100行所需要的buffer，理想执行计划是走索引的INDEX FULL SCAN DESCENDING，A-ROWS只有100：
SQL> create index t_idx01 on t(created,object_id);
Index created.
再次测试SQL:
SQL>
SELECT *
FROM
(SELECT iti.owner,
iti.object_name,
iti.object_type,
iti.OBJECT_ID,
iti.DATA_OBJECT_ID,
iti.STATUS,
iti.CREATED,
iti.LAST_DDL_TIME,
iti.SECONDARY,
iti.NAMESPACE,
iti.EDITION_NAME
FROM t iti
WHERE iti.OBJECT_ID > 88
AND iti.object_type != 'INDEX'
ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2pzyb63zhr1qp, child number 0
-------------------------------------
SELECT *
FROM
(SELECT iti.owner,
iti.object_name,
iti.object_type,
iti.OBJECT_ID,
iti.DATA_OBJECT_ID,
iti.STATUS,
iti.CREATED,
iti.LAST_DDL_TIME,
iti.SECONDARY,
iti.NAMESPACE,
iti.EDITION_NAME
FROM t iti
WHERE iti.OBJECT_ID > 88
AND iti.object_type != 'INDEX'
ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100
Plan hash value: 4274603649
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| | 100 |00:00:00.01 | 110 | 2 |
|* 1 | COUNT STOPKEY | | 1 | | | | 100 |00:00:00.01 | 110 | 2 |
| 2 | VIEW | | 1 | 15M| 4 (0)| 00:00:01 | 100 |00:00:00.01 | 110 | 2 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 15M| 4 (0)| 00:00:01 | 100 |00:00:00.01 | 110 | 2 |
|* 4 | INDEX FULL SCAN DESCENDING| T_IDX01 | 1 | 1 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 10 | 2 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter("iti"."OBJECT_TYPE"<>'INDEX')
4 - access("iti"."OBJECT_ID">88)
filter("iti"."OBJECT_ID">88)
我们看到，执行计划自动选择了INDEX FULL SCAN DESCENDING，无需单独指定降序扫描，优化器会自动选择此方式；A-ROWS只有100行（需求是order by + 小于等于100 row），相对于非全表扫描后过滤出100行的方式，BUFFER GET只需要110个即可。
在生产库上创建索引后性能对比：
![](http://albertdba.com/"wp-content/uploads"/2020/10/20201030-1-1024x357.png)
最终执行计划如下：
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1181 (100)| | 104 |00:00:00.02 | 2476 |
|* 1 | FILTER | | 1 | | | | 104 |00:00:00.02 | 2476 |
| 2 | NESTED LOOPS | | 1 | 496 | 1181 (1)| 00:00:15 | 508 |00:00:00.01 | 1542 |
| 3 | NESTED LOOPS | | 1 | 89 | 673 (0)| 00:00:09 | 100 |00:00:00.01 | 963 |
| 4 | VIEW | | 1 | 100 | 94 (0)| 00:00:02 | 100 |00:00:00.01 | 401 |
|* 5 | COUNT STOPKEY | | 1 | | | | 100 |00:00:00.01 | 401 |
| 6 | VIEW | | 1 | 100 | 94 (0)| 00:00:02 | 100 |00:00:00.01 | 401 |
|* 7 | TABLE ACCESS BY INDEX ROWID| ITINE | 1 | 22M| 94 (0)| 00:00:02 | 100 |00:00:00.01 | 401 |
|* 8 | INDEX SKIP SCAN DESCENDING| ITIN_IDX_01 | 1 | 104 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 301 |
|* 9 | TABLE ACCESS BY INDEX ROWID | SPNR | 100 | 1 | 6 (0)| 00:00:01 | 100 |00:00:00.01 | 562 |
|* 10 | INDEX RANGE SCAN | SPNR_PK | 100 | 9 | 3 (0)| 00:00:01 | 960 |00:00:00.01 | 291 |
|* 11 | TABLE ACCESS BY INDEX ROWID | ITINE | 100 | 6 | 6 (0)| 00:00:01 | 508 |00:00:00.01 | 579 |
|* 12 | INDEX RANGE SCAN | ITINE_PK | 100 | 6 | 3 (0)| 00:00:01 | 508 |00:00:00.01 | 271 |
|* 13 | TABLE ACCESS BY INDEX ROWID | ITINE | 404 | 1 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 934 |
|* 14 | INDEX UNIQUE SCAN | ITINE_PK | 404 | 1 | 3 (0)| 00:00:01 | 404 |00:00:00.01 | 530 |
-----------------------------------------------------------------------------------------------------------------------------------------------![](http://albertdba.com/"wp-content/uploads"/2020/10/20201005-3-1024x380.png)
![](http://albertdba.com/"wp-content/uploads"/2020/09/20200930-1-1024x474.png)
![](http://albertdba.com/"wp-content/uploads"/2020/10/20201005-1-1024x412.png)