近日分析优化了一个TOP SQL，从这个SQL中可以得出，开发规范的重要性，数据库的性能平稳与数据库SQL开发与应用规范密不可分。
--原SQL代码较长，文本处理后的SQL如下：
SELECT ROWNUM, T.*
FROM (SELECT OWNER, OBJECT_NAME, NAMESPACE.....
FROM T1_PART TB
where TB.OBJECT_TYPE = :1
and TB.CREATED = :2
ORDER BY TB.OBJECT_ID) T
WHERE ROWNUM BETWEEN 1 AND 1000SQL执行统计信息如下：
![](http://albertdba.com/"wp-content/uploads"/2020/07/20200719-1-1024x490.png)
此条SQL存在两个执行计划，平均行数都是0行，但是buffer get却非常之大，最差的执行计划逻辑读平均要3百万+，此表示一个分区表，并且按照CREATED日期字段做了INTERVAL按天分区，并且CREATED字段上存在索引。
![](http://albertdba.com/"wp-content/uploads"/2020/07/20200719-2-1024x293.png)
从执行计划中获悉，虽然谓词部分包含了分区键，但是仍然走了PARTITION RANG ALL的执行计划，并且以全表扫描扫描了所有分区，没有跨分区访问，为何没有分区裁剪呢？
表定义中，CREATED字段为DATE类型，可是绑定变量传值为TIMESTAMP类型,发生隐式类型转换：
CREATE TABLE "SYS"."T1_PART"
( "OBJECT_ID" NUMBER,
"CREATED" DATE,
"OWNER" VARCHAR2(255),
"OBJECT_NAME" VARCHAR2(255),
"OBJECT_TYPE" VARCHAR2(255),
"NAMESPACE" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
PARTITION BY RANGE ("CREATED")
(PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING![](http://albertdba.com/"wp-content/uploads"/2020/07/20200719-4-1024x375.png)
模拟问题SQL：
SELECT ROWNUM, T.*
FROM (SELECT OWNER, OBJECT_NAME, NAMESPACE
FROM T1_PART TB
where TB.OBJECT_TYPE = 'TABLE'
and TB.CREATED = to_timestamp('20181122', 'yyyymmdd')
ORDER BY TB.OBJECT_ID) T
WHERE ROWNUM BETWEEN 1 AND 1000
Plan hash value: 2018194493
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5236 (100)| | | | 0 |00:00:00.07 | 19239 |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 0 |00:00:00.07 | 19239 |
|* 2 | FILTER | | 1 | | | | | | 0 |00:00:00.07 | 19239 |
| 3 | VIEW | | 1 | 484 | 5236 (1)| 00:01:03 | | | 0 |00:00:00.07 | 19239 |
| 4 | SORT ORDER BY | | 1 | 484 | 5236 (1)| 00:01:03 | | | 0 |00:00:00.07 | 19239 |
| 5 | PARTITION RANGE ALL| | 1 | 484 | 5235 (1)| 00:01:03 | 1 | 13 | 0 |00:00:00.07 | 19239 |
|* 6 | TABLE ACCESS FULL | T1_PART | 13 | 484 | 5235 (1)| 00:01:03 | 1 | 13 | 0 |00:00:00.07 | 19239 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1000)
2 - filter(ROWNUM>=1)
6 - filter(("TB"."OBJECT_TYPE"='TABLE' AND INTERNAL_FUNCTION("TB"."CREATED")=TIMESTAMP' 2018-11-22 00:00:00.000000000'))
---------------------------------------------------------------------
SELECT ROWNUM, T.*
FROM (SELECT OWNER, OBJECT_NAME, NAMESPACE
FROM T1_PART TB
where TB.OBJECT_TYPE = 'TABLE'
and TB.CREATED = to_date('20181122', 'yyyymmdd')
ORDER BY TB.OBJECT_ID) T
WHERE ROWNUM BETWEEN 1 AND 1000
Plan hash value: 119350508
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5052 (100)| | | | 0 |00:00:00.06 | 18550 |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 0 |00:00:00.06 | 18550 |
|* 2 | FILTER | | 1 | | | | | | 0 |00:00:00.06 | 18550 |
| 3 | VIEW | | 1 | 32 | 5052 (1)| 00:01:01 | | | 0 |00:00:00.06 | 18550 |
| 4 | SORT ORDER BY | | 1 | 32 | 5052 (1)| 00:01:01 | | | 0 |00:00:00.06 | 18550 |
| 5 | PARTITION RANGE SINGLE| | 1 | 32 | 5051 (1)| 00:01:01 | 1 | 1 | 0 |00:00:00.06 | 18550 |
|* 6 | TABLE ACCESS FULL | T1_PART | 1 | 32 | 5051 (1)| 00:01:01 | 1 | 1 | 0 |00:00:00.06 | 18550 |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1000)
2 - filter(ROWNUM>=1)
6 - filter(("TB"."CREATED"=TO_DATE(' 2018-11-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TB"."OBJECT_TYPE"='TABLE'))
55 rows selected.
这是这个SQL的第一个问题，已找到根因。
第二个问题，这个SQL CREATED谓词部分（分区键）是等值，而不是常见的范围条件，这就难怪每次的平均返回行数是0,sql_monitor中的谓词换算为DATE类型为：2020-07-17 00:00:00，但是应用意图为查询2020-07-17一天的数据。
SQL> set serveroutput on
SQL> declare
2 v_x date;
3 v_result varchar2(100);
4 begin
5 dbms_stats.convert_raw_value('78780711010101',v_x);
6 select to_char(v_x,'yyyy-mm-dd hh24:mi:ss') into v_result
7 from dual;
8 dbms_output.put_line(v_result);
9 end;
10 /
2020-07-17 00:00:00
PL/SQL procedure successfully completed.
SQL>
改SQL应该改写为：
SELECT ROWNUM, T.*
FROM (SELECT OWNER, OBJECT_NAME, NAMESPACE
FROM T1_PART TB
where TB.OBJECT_TYPE = 'TABLE'
and TB.CREATED >= to_date('20181122', 'yyyymmdd')
and TB.CREATED <to_date('20181122', 'yyyymmdd') + 1
ORDER BY TB.OBJECT_ID) T
WHERE ROWNUM BETWEEN 1 AND 1000第二个应用逻辑问题，解决完毕，第三个问题：该SQL才用了分页框架TOP N框架，缺失关键索引，应创建如下索引：
create index tb_idx01 on t1_part(object_type,object_id,created) local;
SELECT ROWNUM, T.*
FROM (SELECT OWNER, OBJECT_NAME, NAMESPACE
FROM T1_PART TB
where TB.OBJECT_TYPE = 'TABLE'
and TB.CREATED >= to_date('20181122', 'yyyymmdd')
and TB.CREATED <to_date('20181122', 'yyyymmdd') + 1
ORDER BY TB.OBJECT_ID) T
WHERE ROWNUM BETWEEN 1 AND 1000
Plan hash value: 1741453033
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1008 (100)| | | | 1000 |00:00:00.01 | 1073 |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 1000 |00:00:00.01 | 1073 |
|* 2 | FILTER | | 1 | | | | | | 1000 |00:00:00.01 | 1073 |
| 3 | VIEW | | 1 | 1000 | 1008 (0)| 00:00:13 | | | 1000 |00:00:00.01 | 1073 |
| 4 | PARTITION RANGE SINGLE | | 1 | 1000 | 1008 (0)| 00:00:13 | 1 | 1 | 1000 |00:00:00.01 | 1073 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| T1_PART | 1 | 1000 | 1008 (0)| 00:00:13 | 1 | 1 | 1000 |00:00:00.01 | 1073 |
|* 6 | INDEX RANGE SCAN | TB_IDX01 | 1 | | 7 (0)| 00:00:01 | 1 | 1 | 1000 |00:00:00.01 | 73 |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1000)
2 - filter(ROWNUM>=1)
6 - access("TB"."OBJECT_TYPE"='TABLE' AND "TB"."CREATED">=TO_DATE(' 2018-11-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TB"."CREATED"<TO_DATE(' 2018-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(("TB"."CREATED"<TO_DATE(' 2018-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TB"."CREATED">=TO_DATE(' 2018-11-22 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
60 rows selected.SQL buffer get可由原来的18000+降至1000+。
总结一下，从此SQL中发现的问题：
1.此条SQL共存在应用绑定变量传值与字段类型不一致导致隐式类型转换进而无法进行分区裁剪，扫描了所有的分区。
2.开发不规范导致SQL逻辑与业务实际逻辑不符
3.分页框架中，缺失关键索引