每天查询三百万次的TOP SQL，数据竟是空的？

原创 codePanda 云和恩墨 2024-08-25

981

背景

这周三在监控里看到一条top sql，一天执行三百万次，平均每秒34.7次， 但是分区表里没有数据，每次逻辑读78M左右，很奇怪。我下面尽可能还原一下生产的情况。
脱敏信息如下图：
复制

问题

我梳理了一些这次试验的疑问，如果有兴趣可以继续往下看，仅个人主观想法，欢迎指正讨论

一天执行三百万次，平均每秒34次，分区表里还没有数据正常吗？
没有数据逻辑读却很大，平均每次78M？高水位线造成的吗？但是每张分区表只有4M大小，还需要降吗？
SQL语句能不能优化？SQL涉及的表有没有索引？走没有索引？索引是不是最优的？索引选择性怎么样？
怎么改执行计划走索引和走全表的区别？怎么优化？

是不是可有选择单表，废掉分区表

分析

1. 一天执行三百万次，平均每秒34次，分区表里还没有数据正常吗？

每秒34次的执行频率并不算特别高，但是正常情况下，这种情况可能是由于这是一张中间缓存一些中间数据，或者SQL执行计划被频繁重用，即使没有数据，也可能因为计划的重用导致大量逻辑读。这个应该算正常。

2. 没有数据逻辑读却很大，平均每次78M？高水位线造成的吗？但是每张分区表只有4M大小，还需要降吗？

如果分区表中没有数据，逻辑读的数量应该很小，高逻辑读而没有数据，应该与高水位线（HWM）有关。高水位线指的是表或分区在物理层面上曾经达到过的数据量，即使数据被删除，HWM并不会自动降低。这可能导致即使表或分区中没有实际数据，但Oracle仍然会继续扫描这些块，从而产生大量的逻辑读。那每个分区4M的大小值不值得去降水位线呢？

**高水位线：**数据被删除后，仍然去扫描数据块为空的块。

是否需要降高水位线：
如果分区表中确实没有数据，但逻辑读仍然很高，那么可以考虑降低高水位线。你可以通过以下步骤完成：

收缩分区表：

ALTER TABLE <table_name> MOVE PARTITION <partition_name>;
复制

重建索引：

ALTER INDEX <index_name> REBUILD;
复制

在移动分区或表之后，索引通常需要重建，以确保索引块与表数据块的一致性。

收缩空闲空间：

ALTER TABLE <table_name> SHRINK SPACE;
复制

扩展一：高水位线操作相关
4M应该就是分区表段的初始大小了，应该也没有问题。分区裁剪按照地市去读，有10个左右的地市，大概40M+数据，逻辑读读到78M也算正常吧，这个开销也能接受吧。

3. SQL语句能不能优化？SQL涉及的表有没有索引？走没有索引？索引是不是最优的？索引选择性怎么样？

SQL优化：首先，你需要查看SQL的执行计划（使用 EXPLAIN PLAN 或者 DBMS_XPLAN）。检查执行计划时应注意以下几点：

是否使用索引：如果SQL没有使用索引，而是走了全表扫描（Full Table Scan），那么可能需要创建或优化索引。
索引的选择性：选择性越高的索引效果越好。可以通过分析 v$segment_statistics 或 DBA_HIST_SQLSTAT 来查看索引的使用情况。
索引是否最优：在创建索引时，确保索引的列是查询条件中最常使用的，并且顺序和组合要合理。

这里可能出不知道各类的信息去哪里找？没事，作者整理一份思维导图，优化语句少不了要了解信息
扩展二：SQL优化涉及的信息思维导图

4. 怎么改执行计划，走索引和走全表的区别？怎么优化？

改执行计划：要优化执行计划并强制走索引：

使用提示（Hint）：在SQL中直接使用 INDEX 提示强制使用索引。

SELECT /*+ INDEX(table_name index_name) */ column_list FROM table_name WHERE condition;
复制

调整统计信息：确保表和索引的统计信息是最新的。你可以使用以下命令收集统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');
复制

避免全表扫描：如果表数据量较大，但SQL总是走全表扫描，可以尝试通过：
- 增加筛选条件，减少全表扫描的必要性。
- 使用分区裁剪，确保查询只扫描必要的分区。
索引覆盖：如果查询中包含的列全部都在索引中，那么Oracle可以直接使用索引覆盖查询，避免访问表数据块。

除了索引还能怎么优化呢？走了索引效果不一定就不全表扫效果要好，我们生产上不宜操作，我们先模拟一个环境，在测试环境分析吧！

实验测试

我们先创建一个分区表有几个地市的数据，按照‘地市-时间’联合字段分区，TOPSQL是一个查询语句，要查询其中一个地市的数据，但是数据是空的，执行次数和逻辑读很高。
模拟一个类似的环境，看看有什么值得剖析的地方。

1、环境模拟

-- 模拟不存在数据但是逻辑读很大，索引能不能优化语句，每天执行三百万次
 
 --查询表空间位置
select name from v$datafile;
--创建表空间
create tablespace tab2_r1_p310 datafile '/u01/oradata/tab2_r1_p310.dbf' size 20m autoextend on;
create tablespace tab2_r1_p311 datafile '/u01/oradata/tab2_r1_p311.dbf' size 20m autoextend on;
create tablespace tab2_r1_p312 datafile '/u01/oradata/tab2_r1_p312.dbf' size 20m autoextend on;
create tablespace tab2_r1_pmax datafile '/u01/oradata/tab2_r1_pmax.dbf' size 20m autoextend on;
 
 -- 删除表空间
 drop tablespase tab2_r1_p310; --文件系统物理上不会删除/ASM会自动删除
 drop TABLESPACE tab2_r1_p311;
 drop TABLESPACE tab2_r1_p312;
 drop TABLESPACE tab2_r1_pmax;
 DROP TABLESPACE tab2_r1_p310 INCLUDING CONTENTS AND DATAFILES;
-- 删除表空间内的所有内容及其相关的数据文件,这个操作是不可逆的
 
 --创建分区表
CREATE TABLE tab2 (
    ACCOUNTID NUMBER(20),
    region NUMBER(20),
    name VARCHAR2(100),
    sex VARCHAR2(100),
    created_time DATE
)
PARTITION BY RANGE (region, created_time) (
    PARTITION PART_310_202304 VALUES LESS THAN (310, TO_DATE('2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p310,
    PARTITION PART_310_202305 VALUES LESS THAN (310, TO_DATE('2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p310,
    PARTITION PART_310_202306 VALUES LESS THAN (310, TO_DATE('2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p310,
	PARTITION PART_311_202304 VALUES LESS THAN (311, TO_DATE('2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p311,
    PARTITION PART_311_202305 VALUES LESS THAN (311, TO_DATE('2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p311,
    PARTITION PART_311_202306 VALUES LESS THAN (311, TO_DATE('2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p311,
	PARTITION PART_312_202304 VALUES LESS THAN (312, TO_DATE('2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p312,
    PARTITION PART_312_202305 VALUES LESS THAN (312, TO_DATE('2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p312,
    PARTITION PART_312_202306 VALUES LESS THAN (312, TO_DATE('2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE TABLESPACE tab2_r1_p312,
    PARTITION pmax VALUES LESS THAN (MAXVALUE, MAXVALUE) 
);


-- 310
-- 插入 2023 年 4 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum, 'test' || rownum, 310, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-04-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;
-- 插入 2023 年 5 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum + 100000, 'test' || rownum, 310, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-05-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;
-- 插入 2023 年 6 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum + 200000, 'test' || rownum, 310, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-06-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;


-- 311
-- 插入 2023 年 4 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum, 'test' || rownum, 311, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-04-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;
-- 插入 2023 年 5 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum + 100000, 'test' || rownum, 311, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-05-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;
-- 插入 2023 年 6 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum + 200000, 'test' || rownum, 311, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-06-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;


-- 312
-- 插入 2023 年 4 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum, 'test' || rownum, 312, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-04-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;
-- 插入 2023 年 5 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum + 100000, 'test' || rownum, 312, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-05-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;
-- 插入 2023 年 6 月的数据
INSERT INTO tab2 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum + 200000, 'test' || rownum, 312, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-06-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;
复制

2、模拟的查询语句TOPSQL

需要从‘地市-时间’联合字段的分区表中查询一个分区的数据

SELECT TO_CHAR(ACCOUNTID) AS ACCOUNTID,
       TO_CHAR(created_time, 'YYYY-MM-DD HH24:MI:SS') AS INTIME,
       name,
       region
  FROM TAB2
 WHERE region = 311
 ORDER BY created_time ASC;
复制

3、查看执行计划

–查询计划 set autotrace traceonly 同 set autotrace on，但是不显示查询输出。

SYS@orcl> SELECT TO_CHAR(ACCOUNTID) AS ACCOUNTID,
  2         TO_CHAR(created_time, 'YYYY-MM-DD HH24:MI:SS') AS INTIME,
  3         name,
  4         region
  5    FROM TAB2
  6   WHERE region = 311
  7   ORDER BY created_time ASC;

300000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3230056249
----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |   299K|    24M|       |  6763   (3)| 00:01:22 |       |       |
|   1 |  SORT ORDER BY            |      |   299K|    24M|    28M|  6763   (3)| 00:01:22 |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |   299K|    24M|       |   761  (21)| 00:00:10 |     4 |     7 |
|*  3 |    TABLE ACCESS FULL      | TAB2 |   299K|    24M|       |   761  (21)| 00:00:10 |     4 |     7 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("REGION"=311)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2068  consistent gets
          0  physical reads
          0  redo size
    6566052  bytes sent via SQL*Net to client
       7109  bytes received via SQL*Net from client
        601  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     300000  rows processed
复制

没删除以前逻辑读大概有2068

– 找一下真实的执行计划重新执行 SQL 语句,我们要知道执行过SQL的sql_id:

SELECT TO_CHAR(ACCOUNTID) AS ACCOUNTID,
       TO_CHAR(created_time, 'YYYY-MM-DD HH24:MI:SS') AS INTIME,
       name,
       region
  FROM TAB2
 WHERE region = 311
 ORDER BY created_time ASC;

-- 查询 SQL_ID
SELECT SQL_ID
  FROM V$SQL
 WHERE SQL_FULLTEXT LIKE '%SELECT TO_CHAR(ACCOUNTID) AS ACCOUNTID,
                               TO_CHAR(created_time, ''YYYY-MM-DD HH24:MI:SS'') AS INTIME,
                               name,
                               region
                          FROM TAB2
                         WHERE region = 311
                         ORDER BY created_time ASC%';
--检查 SQL 执行历史
SELECT SQL_ID, SQL_TEXT
  FROM DBA_HIST_SQLTEXT
 WHERE SQL_TEXT LIKE '%SELECT TO_CHAR(ACCOUNTID)%FROM TAB2%WHERE region = 311%ORDER BY created_time ASC%';
--V$SQLAREA						 
SELECT SQL_ID, SQL_TEXT
  FROM V$SQLAREA
 WHERE SQL_TEXT LIKE '%SELECT TO_CHAR(ACCOUNTID)%FROM TAB2%WHERE region = 311%ORDER BY created_time ASC%';

复制

找到SQL_id: av8kv7900f6np

4、对比地市索引的差异

–查看真实的执行计划

# 根据sql_id查看执行计划
select * from table(dbms_xplan.display_cursor('av8kv7900f6np',0,'ALLSTATS LAST'));
# 根据sql_id查看执行计划及其子游标的执行计划
select * from table(dbms_xplan.display_cursor('av8kv7900f6np',NULL,'ALLSTATS LAST'));

SYS@orcl> select * from table(dbms_xplan.display_cursor('av8kv7900f6np',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  av8kv7900f6np, child number 0
-------------------------------------
SELECT TO_CHAR(ACCOUNTID) AS ACCOUNTID,        TO_CHAR(created_time,
'YYYY-MM-DD HH24:MI:SS') AS INTIME,        name,       region   FROM
TAB2  WHERE region = 311  ORDER BY created_time ASC

Plan hash value: 3230056249

------------------------------------------------------------------------------
| Id  | Operation                 | Name | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |        |       |       |          |
|   1 |  SORT ORDER BY            |      |    299K|    22M|  1753K|   20M (0)|
|   2 |   PARTITION RANGE ITERATOR|      |    299K|       |       |          |
|*  3 |    TABLE ACCESS FULL      | TAB2 |    299K|       |       |          |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("REGION"=311)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
29 rows selected.
复制

– 删除统计信息

 analyze table TAB2 delete statistics;
-- dbms_stats收集表的统计信息
begin
  dbms_stats.gather_table_stats(ownname          => 'SYS',
                                tabname          => 'TAB2',
                                estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,
                                method_opt       => 'for all indexed columns',
                                cascade          => true,
                                degree           => 2);
end;
/
复制

按地市创建一个索引

-- 创建索引
create  index ind_tab2_region on TAB2(region);
复制

再看执行计划，走上建立的索引了，但是逻辑读2286，比之前的全表扫描还高出200，说明在内存中读取的数据块增加了，也没有用上分区裁剪。

接着用hint改回全表扫描，发现用上了分区裁剪，而且逻辑读降低了200

说明地市索引的性能不如全表扫描，一个是选择性不太好，而且这里涉及到一个分区裁剪技术，有兴趣的可以了解：Partition Pruning。大概意思就是秒如果你的where条件中有一个字段是要查询表的分区字段那么才查询中可以直接去找你要的这个分区中去查，其他各个字段像这里的3100，312是不会读的。在一个这里全表扫面可以更直接多块读得到结果，但搜索引还需要回表扫描增加IO，这里除了地市索引的选择性不太好以外，在下面可以在试试选择性好一点的索引看看效果。

5、探究高水位线

上面说生产环境是没有数据的，逻辑读很高，那我们也模拟一下把数据删除之后的场景，

SYS@orcl> DELETE FROM tab2 WHERE region = 311;

300000 rows deleted.

SYS@orcl> COMMIT;

Commit complete.

SYS@orcl> SELECT TO_CHAR(ACCOUNTID) AS ACCOUNTID,
  2         TO_CHAR(created_time, 'YYYY-MM-DD HH24:MI:SS') AS INTIME,
  3         name,
  4         region
  5    FROM TAB2
  6   WHERE region = 311
  7   ORDER BY created_time ASC;

no rows selected
复制

重新收一下统计信息，再看执行计划他还是一样的逻辑读

那可以初步判定原因之一是由于高水位引起的，但是每个分区4M大小好像又不需要在降低了，因为在测试环境我建立的测试表初始大小就是8M

在创建数据库对象或表空间时通过storage子句中的initial参数来设置INITIAL_EXTENT。

至于怎么降低高水位线线，请看最后的扩展一。

扩展一：SQL优化涉及的信息思维导图

扩展二：高水位线操作相关

-- 收集表统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCHEMA_NAME',tabname=> 'TABLE_NAME');
-- 确定碎片程度
SELECT table_name,
         ROUND ( (blocks * 8), 2) "高水位空间 k",
         ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
         ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
         ROUND (
            (  blocks * 8
             - (num_rows * avg_row_len / 1024)
             - blocks * 8 * 10 / 100),
            2)
            "浪费空间 k"
    FROM user_tables
   WHERE temporary = 'N'
ORDER BY 5 DESC;
-- 查看表上次收集统计信息时间
select table_name,last_analyzed from dba_tables where owner = 'SCOTT';
-- 收集整个 Schema 中对象的统计信息
exec dbms_stats.gather_schema_stats(ownname=>'SCOTT');
-- 降低表的高水位线
select 'alter table '||TABLE_NAME||' move tablespace '||TABLESPACE_NAME||';' from user_tables where table_name='EMP';
-- 重建表上的索引
select 'alter index '||index_name||' rebuild online;' from user_indexes where table_name='EMP';
--  收集表上的统计信息
select 'analyze table '||TABLE_NAME||' compute statistics;' from user_tables where table_name='EMP';
--  收集索引上的统计信息
select 'analyze index '||index_name||' compute statistics;' from user_indexes where table_name='EMP';
-- 整理表，不影响DML操作
alter table TABLE_NAME enable ROW MOVEMENT;--启动行移动功能
alter table TABLE_NAME shrink space compact;  --只整理碎片 不回收空间
alter table TABLE_NAME shrink space; --整理碎片并回收空间，并调整水位线。业务少时执行
alter table TABLE_NAME disable ROW MOVEMENT;--关闭行移动
-- 一步到位
alter table TABLE_NAME shrink space;

复制

分区表 sql优化 oracle分区表索引墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

DataBase（共17篇）

DataBase合集

背景
问题
分析
- 1. 一天执行三百万次，平均每秒34次，分区表里还没有数据正常吗？
- 2. 没有数据逻辑读却很大，平均每次78M？高水位线造成的吗？但是每张分区表只有4M大小，还需要降吗？
- 3. SQL语句能不能优化？SQL涉及的表有没有索引？走没有索引？索引是不是最优的？索引选择性怎么样？
- 4. 怎么改执行计划，走索引和走全表的区别？怎么优化？
实验测试
- 1、环境模拟
- 2、模拟的查询语句TOPSQL
- 3、查看执行计划
- 4、对比地市索引的差异
- 5、探究高水位线
扩展一：SQL优化涉及的信息思维导图
扩展二：高水位线操作相关