创建测试表,索引，INSERT测试数据，并收集统计信息
create table dbmt.test_part(etime date,c1 varchar2(100))
partition by range(etime)
(
PARTITION "PART_202002" VALUES LESS THAN ( TO_DATE(' 2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "PART_202003" VALUES LESS THAN ( TO_DATE(' 2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "PART_202004" VALUES LESS THAN ( TO_DATE(' 2020-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "PART_202005" VALUES LESS THAN ( TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "PART_202006" VALUES LESS THAN ( TO_DATE(' 2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
insert into dbmt.test_part select to_date('20200201','yyyymmdd')+mod(abs(dbms_random.random()),3600*24*20)/(3600*24*20),'test' from dual connect by rownum<200000;
SQL> insert into dbmt.test_part select to_date('20200201','yyyymmdd')+mod(abs(dbms_random.random()),3600*24*20)/(3600*24*20),'test' from dual connect by rownum<200000;
199999 rows created.
Commit complete.
SQL>
create index dbmt.idx_test_part on dbmt.test_part(etime) local;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&TOWN' , tabname => '&TNAME' , cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', degree => 8,no_invalidate=>false);
Enter value for town: DBMT
Enter value for tname: test_part
PL/SQL procedure successfully completed.
---第一次收统计信息时，表上只有2月份有数据，后面的分区统计信息为0,库上禁用了统计信息收集,所以后面大量INSERT后，统计信息也没更新
SET LINES 1000
COL TABLE_NAME FORMAT A30
COL PARTITION_NAME FORMAT A30
SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED,GLOBAL_STATS,STALE_STATS FROM dba_tab_statistics where table_name='TEST_PART';
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED GLO STA
------------------------------ ------------------------------ ---------- ---------- ----------------- --- ---
TEST_PART 199999 622 20200520 15:46:20 YES NO
TEST_PART PART_202004 0 0 20200520 15:46:20 YES NO
TEST_PART PART_202003 0 0 20200520 15:46:20 YES NO
TEST_PART PART_202005 0 0 20200520 15:46:20 YES NO
TEST_PART PART_202006 0 0 20200520 15:46:20 YES NO
TEST_PART PART_202002 199999 622 20200520 15:46:20 YES NO
6 rows selected.
SQL> COL COLUMN_NAME FORMAT A30
SQL> SELECT COLUMN_NAME,LOW_VALUE,HIGH_VALUE,DENSITY FROM dba_tab_col_statistics where table_name='TEST_PART';
COLUMN_NAME LOW_VALUE HIGH_VALUE DENSITY
------------------------------ ---------------------- -------------------------------
ETIME 78780201010101 78780202010101 .000012835
C1 74657374 74657374 1
set serveroutput ons
declare
v_low date;
v_high date;
begin
dbms_stats.convert_raw_value('78780201010101',v_low);
dbms_stats.convert_raw_value('78780202010101',v_high);
dbms_output.put_line(v_low);
dbms_output.put_line(v_high);
end;
/
20200201 00:00:00
20200202 00:00:00
测试SQL走INDEX SCAN
SQL> SELECT * FROM DBMT.TEST_PART WHERE ETIME>SYSDATE-1;
no rows selected
SQL> @x
Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3xd8x4vy1v6gn, child number 0
-------------------------------------
SELECT * FROM DBMT.TEST_PART WHERE ETIME>SYSDATE-1
Plan hash value: 3223941850
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 13 | 4 (0)| KEY | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST_PART | 1 | 13 | 4 (0)| KEY | 5 |
|* 3 | INDEX RANGE SCAN | IDX_TEST_PART | 1 | | 3 (0)| KEY | 5 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ETIME">SYSDATE@!-1)
21 rows selected.
DROP PARTITION PART_202002后，测试SQL变为走FULL SCAN,后面的分区虽然统计信息为0，实际有大量数据
alter table dbmt.test_part drop partition PART_202002;
SET LINES 1000
COL TABLE_NAME FORMAT A30
COL PARTITION_NAME FORMAT A30
SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED,GLOBAL_STATS,STALE_STATS FROM dba_tab_statistics where table_name='TEST_PART';
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED GLO STA
------------------------------ ------------------------------ ---------- ---------- ----------------- --- ---
TEST_PART 199999 622 20200520 15:46:20 YES YES <<<DROP PARTITION STALE_STATS NO=>YES
TEST_PART PART_202004 0 0 20200520 15:46:20 YES NO
TEST_PART PART_202003 0 0 20200520 15:46:20 YES NO
TEST_PART PART_202005 0 0 20200520 15:46:20 YES NO
TEST_PART PART_202006 0 0 20200520 15:46:20 YES NO
COL COLUMN_NAME FORMAT A30
SELECT COLUMN_NAME,LOW_VALUE,HIGH_VALUE,DENSITY FROM dba_tab_col_statistics where table_name='TEST_PART';
COLUMN_NAME LOW_VALUE HIGH_VALUE DENSITY
------------------------------ ----------------- ------------------ ----------
ETIME 78780201010101 78780202010101 .000012835
C1 74657374 74657374 1
set serveroutput on
declare
v_low date;
v_high date;
begin
dbms_stats.convert_raw_value('78780201010101',v_low);
dbms_stats.convert_raw_value('78780202010101',v_high);
dbms_output.put_line(v_low);
dbms_output.put_line(v_high);
end;
/
20200201 00:00:00
20200202 00:00:00
PL/SQL procedure successfully completed.
---DROP 历史分区后，下面的查询走INDEX SCAN
SQL> SELECT * FROM DBMT.TEST_PART WHERE ETIME>SYSDATE-1;
no rows selected
SQL> @x
Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3xd8x4vy1v6gn, child number 0
-------------------------------------
SELECT * FROM DBMT.TEST_PART WHERE ETIME>SYSDATE-1
Plan hash value: 4294476422
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| | |
| 1 | PARTITION RANGE ITERATOR| | 1 | 13 | 3 (0)| KEY | 4 |
|* 2 | TABLE ACCESS FULL | TEST_PART | 1 | 13 | 3 (0)| KEY | 4 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ETIME">SYSDATE@!-1)
20 rows selected.
10053 对比DROP分区前后差异,
DROP分区前FULL SCAN IO 估算为171，DROP分区后IO估算为 3
---DROP分区前
Access path analysis for TEST_PART
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST_PART[TEST_PART]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"TEST_PART"."ETIME">SYSDATE@!-1
Column (#1): ETIME(DATE)
AvgLen: 8 NDV: 77912 Nulls: 0 Density: 0.000013 Min: 2458881.000000 Max: 2458882.000000
Using prorated density: 2.5000e-06 of col #1 as selectivity of out-of-range/non-existent value pred
Table: TEST_PART Alias: TEST_PART
Card: Original: 199999.000000 Rounded: 1 Computed: 0.500000 Non Adjusted: 0.500000
Scan IO Cost (Disk) = 171.000000 <<<<
Scan CPU Cost (Disk) = 34650941.440000
Cost of predicates:
io = NOCOST, cpu = 500.000000, sel = 0.000003 flag = 2048 ("TEST_PART"."ETIME">SYSDATE@!-1)
Total Scan IO Cost = 171.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 201285.167203 (#rows))
= 171.000000
Total Scan CPU Cost = 34650941.440000 (scan (Disk))
+ 100642583.601286 (cpu filter eval) (= 500.000000 (per row) * 201285.167203 (#rows))
= 135293525.041286
Access Path: TableScan
Cost: 176.622676 Resp: 176.622676 Degree: 0
Cost_io: 171.000000 Cost_cpu: 135293525
Resp_io: 171.000000 Resp_cpu: 135293525
****** Costing Index IDX_TEST_PART
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Using prorated density: 2.5000e-06 of col #1 as selectivity of out-of-range/non-existent value pred
Access Path: index (RangeScan)
Index: IDX_TEST_PART
resc_io: 4.000000 resc_cpu: 28856
ix_sel: 2.5000e-06 ix_sel_with_filters: 2.5000e-06
Cost: 4.001199 Resp: 4.001199 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_TEST_PART
Cost: 4.001199 Degree: 1 Resp: 4.001199 Card: 0.500000 Bytes: 0.000000
---DROP分区后
Access path analysis for TEST_PART
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST_PART[TEST_PART]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"TEST_PART"."ETIME">SYSDATE@!-1
Column (#1): ETIME(DATE)
AvgLen: 8 NDV: 77912 Nulls: 0 Density: 0.000013 Min: 2458881.000000 Max: 2458882.000000
Using prorated density: 2.5000e-06 of col #1 as selectivity of out-of-range/non-existent value pred
Table: TEST_PART Alias: TEST_PART
Card: Original: 199999.000000 Rounded: 1 Computed: 0.500000 Non Adjusted: 0.500000
Scan IO Cost (Disk) = 3.000000
Scan CPU Cost (Disk) = 221555.760000
Cost of predicates:
io = NOCOST, cpu = 500.000000, sel = 0.000003 flag = 2048 ("TEST_PART"."ETIME">SYSDATE@!-1)
Total Scan IO Cost = 3.000000 (scan (Disk)) <<<<
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 1286.167203 (#rows))
= 3.000000
Total Scan CPU Cost = 221555.760000 (scan (Disk))
+ 643083.601286 (cpu filter eval) (= 500.000000 (per row) * 1286.167203 (#rows))
= 864639.361286
Access Path: TableScan
Cost: 3.035934 Resp: 3.035934 Degree: 0
Cost_io: 3.000000 Cost_cpu: 864639
Resp_io: 3.000000 Resp_cpu: 864639
****** Costing Index IDX_TEST_PART
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Using prorated density: 2.5000e-06 of col #1 as selectivity of out-of-range/non-existent value pred
Access Path: index (RangeScan)
Index: IDX_TEST_PART
resc_io: 4.000000 resc_cpu: 28856
ix_sel: 2.5000e-06 ix_sel_with_filters: 2.5000e-06
Cost: 4.001199 Resp: 4.001199 Degree: 1
Best:: AccessPath: TableScan
Cost: 3.035934 Degree: 1 Resp: 3.035934 Card: 0.500000 Bytes: 0.000000
最后修改时间：2020-05-20 17:27:33
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
