Oracle 收集分区统计信息

最新推荐文章于 2024-08-01 23:39:22 发布
其实我是程序员
最新推荐文章于 2024-08-01 23:39:22 发布
阅读量1.5k
点赞数
分类专栏： Oracle 文章标签： oracle
本文链接：https://blog.csdn.net/sbcsdn2015123/article/details/115235039
版权
Oracle 专栏收录该内容
1 篇文章
订阅专栏
--删除表
DROP TABLE TEST3;
--1.创建按日分区测试表
SQL> CREATE TABLE TEST3(ID INT, PARDATE DATE)
  2  PARTITION BY RANGE (PARDATE) INTERVAL (NUMTODSINTERVAL (1,'DAY'))
  3  (
  4    PARTITION P_20180101 VALUES LESS THAN (TO_DATE('2018-01-02', 'YYYY-MM-DD'))
  5  );
表已创建。
--2.插入2天的数据，每天100条数据
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210324','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210325','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
COMMIT;
--3.模拟生产，先收集下全局统计信息，避免动态采集
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST3');
--4.继续插入3天的数据，每天100条数据
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210326','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210327','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210328','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
COMMIT;
--3.仅收集新增partition的统计信息
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P261',granularity => 'PARTITION');
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P262',granularity => 'PARTITION');
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P263',granularity => 'PARTITION');
--4.查看分区的统计信息
SQL> set linesize 200
SQL> SELECT P.PARTITION_NAME, P.NUM_ROWS, P.LAST_ANALYZED
  2    FROM DBA_TAB_PARTITIONS P
  3   WHERE TABLE_NAME = 'TEST3';

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------
P_20180101                              0 26-3月 -21
SYS_P259                              100 26-3月 -21
SYS_P260                              100 26-3月 -21
SYS_P261                              100 26-3月 -21
SYS_P262                              100 26-3月 -21
SYS_P263                              100 26-3月 -21
--5.查看全局的统计信息, 发现全局统计信息仍陈旧(200条)
SQL> SELECT P.NUM_ROWS, P.LAST_ANALYZED
  2    FROM DBA_TABLES P
  3   WHERE TABLE_NAME = 'TEST3';

  NUM_ROWS LAST_ANALYZED
---------- --------------
       200 26-3月 -21

--6.查看oracle预估返回行数是否正确,单分区准确，跨分区就不准确
SQL> set autotrace traceonly
SQL> SELECT COUNT(*)
  2    FROM TEST3
  3   WHERE PARDATE = TO_DATE('20210326', 'YYYYMMDD');
-------------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE         |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|       |   100 |   800 |     3   (0)| 00:00:01 |  1181 |  1181 |
|*  3 |    TABLE ACCESS FULL    | TEST3 |   100 |   800 |     3   (0)| 00:00:01 |  1181 |  1181 |
-------------------------------------------------------------------------------------------------
SQL> SELECT COUNT(*)
  2    FROM TEST3
  3   WHERE PARDATE = TO_DATE('20210327', 'YYYYMMDD');
-------------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE         |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|       |   100 |   800 |     3   (0)| 00:00:01 |  1182 |  1182 |
|*  3 |    TABLE ACCESS FULL    | TEST3 |   100 |   800 |     3   (0)| 00:00:01 |  1182 |  1182 |
-------------------------------------------------------------------------------------------------
--跨分区统计信息就不准确
SQL> SELECT COUNT(*)
  2    FROM TEST3
  3   WHERE PARDATE IN
  4         (TO_DATE('20210326', 'YYYYMMDD'), TO_DATE('20210327', 'YYYYMMDD'));
-------------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |     8 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE         |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE INLIST|       |     1 |     8 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  3 |    TABLE ACCESS FULL    | TEST3 |     1 |     8 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------------

--7.新插入两个分区数据，
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210329','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210330','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
COMMIT
--8.采用AUTO收集，仅收集增加的一个分区统计信息，另外一个不收集
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P264',granularity => 'AUTO');

--9.查看分区的统计信息(SYS_P265为空)
SQL> SELECT P.PARTITION_NAME, P.NUM_ROWS, P.LAST_ANALYZED
  2   FROM DBA_TAB_PARTITIONS P
  3  WHERE TABLE_NAME = 'TEST3';

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------
P_20180101                              0 26-3月 -21
SYS_P259                              100 26-3月 -21
SYS_P260                              100 26-3月 -21
SYS_P261                              100 26-3月 -21
SYS_P262                              100 26-3月 -21
SYS_P263                              100 26-3月 -21
SYS_P264                              100 26-3月 -21
SYS_P265
--10.查看全局统计信息，发现条数为700条。意味着全局统计信息不仅仅是将新收集分区的统计
--信息直接加到全局上去，而是重新全部收集了。
SQL> SELECT P.NUM_ROWS, P.LAST_ANALYZED
  2    FROM DBA_TABLES P
  3   WHERE TABLE_NAME = 'TEST3';

  NUM_ROWS LAST_ANALYZED
---------- --------------
       700 26-3月 -21
--收集掉另外一个分区再继续测试
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P265',granularity => 'AUTO');

--11.再插入两个分区数据
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210331','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210401','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
COMMIT
       
--11.通过测试可以通过指定granularity => 'APPROX_GLOBAL AND PARTITION'，机制是
--仅将指定分区的统计信息增加到全局信息中。
SQL> exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P266',granularity => 'APPROX_GLOBAL AND PARTITION');
--可以看到本次全局统计信息仅将SYS_P266的100条增加到全局统计信息中，而不是全部收集
SQL> SELECT P.NUM_ROWS, P.LAST_ANALYZED
  2    FROM DBA_TABLES P
  3   WHERE TABLE_NAME = 'TEST3';

  NUM_ROWS LAST_ANALYZED
---------- --------------
       800 26-3月 -21
SQL> exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P267',granularity => 'APPROX_GLOBAL AND PARTITION');
--查看分区统计信息
SQL> SELECT P.PARTITION_NAME, P.NUM_ROWS, P.LAST_ANALYZED
  2   FROM DBA_TAB_PARTITIONS P
  3  WHERE TABLE_NAME = 'TEST3';

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------
P_20180101                              0 26-3月 -21
SYS_P259                              100 26-3月 -21
SYS_P260                              100 26-3月 -21
SYS_P261                              100 26-3月 -21
SYS_P262                              100 26-3月 -21
SYS_P263                              100 26-3月 -21
SYS_P264                              100 26-3月 -21
SYS_P265                              100 26-3月 -21
SYS_P266                              100 26-3月 -21
SYS_P267                              100 26-3月 -21
--查看全局统计信息
SQL> SELECT P.NUM_ROWS, P.LAST_ANALYZED
  2    FROM DBA_TABLES P
  3   WHERE TABLE_NAME = 'TEST3';

  NUM_ROWS LAST_ANALYZED
---------- --------------
       900 26-3月 -21
--12.再测试下跨分区的准确性,答案是准确的
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT * FROM TEST3 WHERE PARDATE = TO_DATE('20210331', 'YYYYMMDD');
----------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |   100 |  1100 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |   100 |  1100 |     3   (0)| 00:00:01 |  1186 |  1186 |
|*  2 |   TABLE ACCESS FULL    | TEST3 |   100 |  1100 |     3   (0)| 00:00:01 |  1186 |  1186 |
------------------------------------------------------------------------------------------------

SQL> SELECT * FROM TEST3 WHERE PARDATE = TO_DATE('20210401', 'YYYYMMDD');
------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |   100 |  1100 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |   100 |  1100 |     3   (0)| 00:00:01 |  1187 |  1187 |
|*  2 |   TABLE ACCESS FULL    | TEST3 |   100 |  1100 |     3   (0)| 00:00:01 |  1187 |  1187 |
------------------------------------------------------------------------------------------------


SQL> SELECT * FROM TEST3 WHERE PARDATE IN (TO_DATE('20210331', 'YYYYMMDD'),TO_DATE('20210401', 'YYYYMMDD'));
------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |   200 |  2200 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE INLIST|       |   200 |  2200 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL    | TEST3 |   200 |  2200 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------