This topic provides examples of statistics collection.
Prepare data, create a table, and insert data into the table.
obclient> CREATE TABLE t_subpart (c1 INT, c2 INT, c3 INT) PARTITION BY HASH(c1) SUBPARTITION BY HASH(c2) SUBPARTITION TEMPLATE ( SUBPARTITION sp0, SUBPARTITION sp1, SUBPARTITION sp2 ) PARTITIONS 3; Query OK, 0 rows affected obclient> INSERT INTO t_subpart SELECT LEVEL-1, MOD(LEVEL-1, 1000), MOD(LEVEL-1, 2000) FROM DUAL CONNECT BY LEVEL <= 10000; Query OK, 10000 rows affected Records: 10000 Duplicates: 0 Warnings: 0 obclient> SELECT COUNT(*) FROM t_subpart; +----------+ | COUNT(*) | +----------+ | 10000 | +----------+ 1 row in setCollect statistics.
obclient> CALL dbms_stats.gather_table_stats('test', 't_subpart', method_opt=>'FOR ALL COLUMNS SIZE 5', granularity=>'ALL', degree=>4); Query OK, 0 rows affectedQuery the statistics.
obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 't_subpart' AND OWNER = 'test' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | +------------+--------------+----------+-------------+ | T_SUBPART | PARTITION | 2504 | 60 | | T_SUBPART | PARTITION | 2530 | 60 | | T_SUBPART | PARTITION | 4966 | 60 | | T_SUBPART | SUBPARTITION | 549 | 60 | | T_SUBPART | SUBPARTITION | 560 | 60 | | T_SUBPART | SUBPARTITION | 829 | 60 | | T_SUBPART | SUBPARTITION | 830 | 60 | | T_SUBPART | SUBPARTITION | 1122 | 60 | | T_SUBPART | SUBPARTITION | 1125 | 60 | | T_SUBPART | SUBPARTITION | 1130 | 60 | | T_SUBPART | SUBPARTITION | 1141 | 60 | | T_SUBPART | SUBPARTITION | 2714 | 60 | | T_SUBPART | TABLE | 10000 | 60 | +------------+--------------+----------+-------------+ 13 rows in set obclient> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE table_name ='t_subpart' AND OWNER = 'test' ORDER BY 1, 2, 3; +------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+ | TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | SAMPLE_SIZE | AVG_COL_LEN | HISTOGRAM | +------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+ | T_SUBPART | C1 | 10191 | 0 | 9999 | .000097999999999999997 | 0 | 5 | 10000 | 20 | HYBRID | | T_SUBPART | C2 | 1031 | 0 | 999 | .00097000000000000005 | 0 | 5 | 10000 | 20 | HYBRID | | T_SUBPART | C3 | 1973 | 0 | 1999 | .00050699999999999996 | 0 | 5 | 10000 | 20 | HYBRID | +------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+ 3 rows in set obclient> SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE, ENDPOINT_ACTUAL_VALUE_RAW, ENDPOINT_REPEAT_COUNT from DBA_PART_HISTOGRAMS WHERE table_name ='t_subpart' AND OWNER = 'test' ORDER BY 1, 2, 3; +------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+ | TABLE_NAME | PARTITION_NAME | COLUMN_NAME | ENDPOINT_NUMBER | ENDPOINT_VALUE | ENDPOINT_ACTUAL_VALUE | ENDPOINT_ACTUAL_VALUE_RAW | ENDPOINT_REPEAT_COUNT | +------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+ | T_SUBPART | P0 | C1 | 634 | NULL | 2468 | 2468 | 1 | | T_SUBPART | P0 | C1 | 1 | NULL | 2 | 2 | 1 | | T_SUBPART | P0 | C1 | 2530 | NULL | 9998 | 9998 | 1 | | T_SUBPART | P0 | C1 | 1267 | NULL | 5010 | 5010 | 1 | | T_SUBPART | P0 | C1 | 1899 | NULL | 7555 | 7555 | 1 | | T_SUBPART | P0 | C2 | 4 | NULL | 0 | 0 | 4 | | T_SUBPART | P0 | C2 | 636 | NULL | 245 | 245 | 1 | | T_SUBPART | P0 | C2 | 1269 | NULL | 496 | 496 | 3 | | T_SUBPART | P0 | C2 | 1901 | NULL | 753 | 753 | 3 | | T_SUBPART | P0 | C2 | 2530 | NULL | 999 | 999 | 3 | | T_SUBPART | P0 | C3 | 1899 | NULL | 1493 | 1493 | 1 | | T_SUBPART | P0 | C3 | 2530 | NULL | 1999 | 1999 | 1 | | T_SUBPART | P0 | C3 | 1267 | NULL | 1004 | 1004 | 2 | | T_SUBPART | P0 | C3 | 634 | NULL | 502 | 502 | 1 | | T_SUBPART | P0 | C3 | 1 | NULL | 0 | 0 | 1 | | T_SUBPART | P1 | C1 | 1 | NULL | 0 | 0 | 1 | | T_SUBPART | P1 | C1 | 1243 | NULL | 2531 | 2531 | 1 | | T_SUBPART | P1 | C1 | 2485 | NULL | 5028 | 5028 | 1 | | T_SUBPART | P1 | C1 | 3726 | NULL | 7459 | 7459 | 1 | | T_SUBPART | P1 | C1 | 4966 | NULL | 9999 | 9999 | 1 | | T_SUBPART | P1 | C2 | 4966 | NULL | 999 | 999 | 4 | | T_SUBPART | P1 | C2 | 3730 | NULL | 744 | 744 | 3 | | T_SUBPART | P1 | C2 | 2489 | NULL | 498 | 498 | 6 | | T_SUBPART | P1 | C2 | 6 | NULL | 0 | 0 | 6 | | T_SUBPART | P1 | C2 | 1248 | NULL | 245 | 245 | 5 | | T_SUBPART | P1 | C3 | 4 | NULL | 0 | 0 | 4 | | T_SUBPART | P1 | C3 | 1246 | NULL | 507 | 507 | 2 | | T_SUBPART | P1 | C3 | 2490 | NULL | 1012 | 1012 | 5 | | T_SUBPART | P1 | C3 | 3729 | NULL | 1502 | 1502 | 2 | | T_SUBPART | P1 | C3 | 4966 | NULL | 1999 | 1999 | 3 | | T_SUBPART | P2 | C1 | 2504 | NULL | 9995 | 9995 | 1 | | T_SUBPART | P2 | C1 | 1879 | NULL | 7535 | 7535 | 1 | | T_SUBPART | P2 | C1 | 1253 | NULL | 4933 | 4933 | 1 | | T_SUBPART | P2 | C1 | 627 | NULL | 2489 | 2489 | 1 | | T_SUBPART | P2 | C1 | 1 | NULL | 3 | 3 | 1 | | T_SUBPART | P2 | C2 | 3 | NULL | 1 | 1 | 3 | | T_SUBPART | P2 | C2 | 629 | NULL | 267 | 267 | 6 | | T_SUBPART | P2 | C2 | 1256 | NULL | 509 | 509 | 2 | | T_SUBPART | P2 | C2 | 1882 | NULL | 758 | 758 | 3 | | T_SUBPART | P2 | C2 | 2504 | NULL | 999 | 999 | 3 | | T_SUBPART | P2 | C3 | 1 | NULL | 1 | 1 | 1 | | T_SUBPART | P2 | C3 | 627 | NULL | 488 | 488 | 1 | | T_SUBPART | P2 | C3 | 1253 | NULL | 974 | 974 | 2 | | T_SUBPART | P2 | C3 | 1879 | NULL | 1502 | 1502 | 3 | | T_SUBPART | P2 | C3 | 2504 | NULL | 1999 | 1999 | 1 | +------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+ 45 rows in setPrepare the statistics user table.
obclient> CALL dbms_stats.create_stat_table('test', 'test_stat'); Query OK, 0 rows affected obclient> DESC test_stat; +----------+-----------------------------+------+-----+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +----------+-----------------------------+------+-----+---------+-------+ | STATID | VARCHAR2(128) | YES | NULL | NULL | NULL | | TYPE | CHAR(1) | YES | NULL | NULL | NULL | | VERSION | NUMBER | YES | NULL | NULL | NULL | | FLAGS | NUMBER | YES | NULL | NULL | NULL | | C1 | VARCHAR2(128) | YES | NULL | NULL | NULL | | C2 | VARCHAR2(128) | YES | NULL | NULL | NULL | | C3 | VARCHAR2(128) | YES | NULL | NULL | NULL | | C4 | VARCHAR2(128) | YES | NULL | NULL | NULL | | C5 | VARCHAR2(128) | YES | NULL | NULL | NULL | | C6 | VARCHAR2(128) | YES | NULL | NULL | NULL | | N1 | NUMBER | YES | NULL | NULL | NULL | | N2 | NUMBER | YES | NULL | NULL | NULL | | N3 | NUMBER | YES | NULL | NULL | NULL | | N4 | NUMBER | YES | NULL | NULL | NULL | | N5 | NUMBER | YES | NULL | NULL | NULL | | N6 | NUMBER | YES | NULL | NULL | NULL | | N7 | NUMBER | YES | NULL | NULL | NULL | | N8 | NUMBER | YES | NULL | NULL | NULL | | N9 | NUMBER | YES | NULL | NULL | NULL | | N10 | NUMBER | YES | NULL | NULL | NULL | | N11 | NUMBER | YES | NULL | NULL | NULL | | N12 | NUMBER | YES | NULL | NULL | NULL | | N13 | NUMBER | YES | NULL | NULL | NULL | | D1 | DATE | YES | NULL | NULL | NULL | | T1 | TIMESTAMP(6) WITH TIME ZONE | YES | NULL | NULL | NULL | | R1 | RAW(1000) | YES | NULL | NULL | NULL | | R2 | RAW(1000) | YES | NULL | NULL | NULL | | R3 | RAW(1000) | YES | NULL | NULL | NULL | | CH1 | VARCHAR2(1000) | YES | NULL | NULL | NULL | | CL1 | CLOB | YES | NULL | NULL | NULL | | BL1 | BLOB | YES | NULL | NULL | NULL | | OB_SPEC1 | NUMBER | YES | NULL | NULL | NULL | | OB_SPEC2 | NUMBER | YES | NULL | NULL | NULL | | OB_SPEC3 | VARCHAR2(4096) | YES | NULL | NULL | NULL | +----------+-----------------------------+------+-----+---------+-------+ 34 rows in setExport statistics.
obclient> CALL dbms_stats.export_table_stats('test', 't_subpart', stattab=>'test_stat', statown=>'test'); Query OK, 0 rows affected obclient> SELECT COUNT(*) FROM test_stat; +----------+ | COUNT(*) | +----------+ | 208 | +----------+ 1 row in setDelete statistics.
obclient> CALL dbms_stats.delete_table_stats('test', 't_subpart'); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM DBA_TAB_STATISTICS WHERE table_name = 't_subpart' and OWNER = 'test' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | +------------+--------------+----------+-------------+ | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | TABLE | NULL | NULL | +------------+--------------+----------+-------------+ 13 rows in setSet statistics.
obclient> CALL dbms_stats.set_table_stats('test', 't_subpart', numrows=>10000); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM DBA_TAB_STATISTICS WHERE table_name = 't_subpart' AND OWNER = 'test' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | +------------+--------------+----------+-------------+ | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | PARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | SUBPARTITION | NULL | NULL | | T_SUBPART | TABLE | 10000 | 0 | +------------+--------------+----------+-------------+ 13 rows in setLock statistics.
obclient> CALL dbms_stats.lock_table_stats('test', 't_subpart'); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE table_name = 't_subpart' and OWNER = 'test' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+-----------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | STATTYPE_LOCKED | +------------+--------------+----------+-------------+-----------------+ | T_SUBPART | PARTITION | 0 | 0 | ALL | | T_SUBPART | PARTITION | 0 | 0 | ALL | | T_SUBPART | PARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | SUBPARTITION | 0 | 0 | ALL | | T_SUBPART | TABLE | 10000 | 0 | ALL | +------------+--------------+----------+-------------+-----------------+ 13 rows in set obclient> CALL dbms_stats.gather_table_stats('test', 't_subpart', method_opt=>'FOR ALL COLUMNS SIZE 5', granularity=>'ALL', degree=>4); ORA-20001: object statistics are locked at oceanbase.DBMS_STATS.GATHER_TABLE_STATS , line : 1, col : 0Unlock statistics.
obclient> CALL dbms_stats.unlock_table_stats('test', 't_subpart'); Query OK, 0 rows affected obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE table_name = 't_subpart' AND OWNER = 'test' ORDER BY 1, 2, 3; +------------+--------------+----------+-------------+-----------------+ | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | STATTYPE_LOCKED | +------------+--------------+----------+-------------+-----------------+ | T_SUBPART | PARTITION | 0 | 0 | NULL | | T_SUBPART | PARTITION | 0 | 0 | NULL | | T_SUBPART | PARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | SUBPARTITION | 0 | 0 | NULL | | T_SUBPART | TABLE | 10000 | 0 | NULL | +------------+--------------+----------+-------------+-----------------+ 13 rows in setImport statistics.
obclient> CALL dbms_stats.import_table_stats('test', 't_subpart', stattab=>'test_stat', statown=>'test');
Query OK, 0 rows affected
obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE table_name = 't_subpart' AND OWNER = 'test' ORDER BY 1, 2, 3;
+------------+--------------+----------+-------------+-----------------+
| TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | STATTYPE_LOCKED |
+------------+--------------+----------+-------------+-----------------+
| T_SUBPART | PARTITION | 2504 | 60 | NULL |
| T_SUBPART | PARTITION | 2530 | 60 | NULL |
| T_SUBPART | PARTITION | 4966 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 549 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 560 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 829 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 830 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 1122 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 1125 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 1130 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 1141 | 60 | NULL |
| T_SUBPART | SUBPARTITION | 2714 | 60 | NULL |
| T_SUBPART | TABLE | 10000 | 60 | NULL |
+------------+--------------+----------+-------------+-----------------+
13 rows in set
- Restore historical statistics.
obclient> SELECT * FROM dba_tab_stats_history WHERE table_name = 't_subpart' AND OWNER = 'test' ORDER BY STATS_UPDATE_TIME;
+-------+------------+----------------+-------------------+------------------------------------+
| OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | STATS_UPDATE_TIME |
+-------+------------+----------------+-------------------+------------------------------------+
| TEST | T_SUBPART | NULL | NULL | 2021-09-26 21:46:26.530287 +08:00 |
| TEST | T_SUBPART | NULL | NULL | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P2 | P2sp2 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P2 | P2sp1 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P2 | P2sp0 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P1 | P1sp2 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P1 | P1sp1 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P1 | P1sp0 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P0 | P0sp2 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P0 | P0sp1 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P0 | P0sp0 | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P2 | NULL | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P1 | NULL | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P0 | NULL | 2021-09-26 21:48:06.837279 +08:00 |
| TEST | T_SUBPART | P2 | P2sp0 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P1 | P1sp0 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P1 | P1sp1 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P0 | P0sp2 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P1 | P1sp2 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P0 | P0sp1 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P0 | NULL | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P0 | P0sp0 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P2 | P2sp1 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P2 | NULL | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | NULL | NULL | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P2 | P2sp2 | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P1 | NULL | 2021-09-26 21:48:19.986693 +08:00 |
| TEST | T_SUBPART | P2 | P2sp0 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P1 | P1sp0 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | NULL | NULL | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P1 | P1sp2 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P2 | P2sp1 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P1 | P1sp1 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P2 | P2sp2 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P1 | NULL | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P0 | P0sp2 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P0 | P0sp1 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P2 | NULL | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P0 | P0sp0 | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | P0 | NULL | 2021-09-26 21:56:10.418878 +08:00 |
| TEST | T_SUBPART | NULL | NULL | 2021-09-26 21:58:36.743517 +08:00 |
| TEST | T_SUBPART | NULL | NULL | 2021-09-26 21:59:29.930730 +08:00 |
| TEST | T_SUBPART | NULL | NULL | 2021-09-26 22:01:57.232698 +08:00 |
| TEST | T_SUBPART | P0 | P0sp0 | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P2 | P2sp2 | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P1 | NULL | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P0 | NULL | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P2 | P2sp1 | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P2 | NULL | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P2 | P2sp0 | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | NULL | NULL | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P1 | P1sp2 | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P0 | P0sp1 | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P1 | P1sp1 | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P0 | P0sp2 | 2021-09-26 22:03:22.234808 +08:00 |
| TEST | T_SUBPART | P1 | P1sp0 | 2021-09-26 22:03:22.234808 +08:00 |
+-------+------------+----------------+-------------------+------------------------------------+
56 rows in set
obclient> CALL dbms_stats.restore_table_stats('test', 't_subpart', to_timestamp('2021-09-26 22:03:22.234808', 'YYYY-MM-DD HH24:MI:SS.FF'));
Query OK, 0 rows affected
obclient> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE table_name = 't_subpart' and OWNER = 'test' ORDER BY 1, 2, 3;
+------------+--------------+----------+-------------+-----------------+
| TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | STATTYPE_LOCKED |
+------------+--------------+----------+-------------+-----------------+
| T_SUBPART | PARTITION | 0 | 0 | NULL |
| T_SUBPART | PARTITION | 0 | 0 | NULL |
| T_SUBPART | PARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | SUBPARTITION | 0 | 0 | NULL |
| T_SUBPART | TABLE | 10000 | 0 | NULL |
+------------+--------------+----------+-------------+-----------------+
13 rows in set
- Delete historical statistics.
obclient> CALL dbms_stats.purge_stats(to_timestamp('2021-09-26 22:08:42.115370', 'YYYY-MM-DD HH24:MI:SS.FF'));
Query OK, 0 rows affected
obclient> SELECT * FROM dba_tab_stats_history WHERE table_name = 't_subpart' AND OWNER = 'test' ORDER BY STATS_UPDATE_TIME;
+-------+------------+----------------+-------------------+-------------------+
| OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | STATS_UPDATE_TIME |
+-------+------------+----------------+-------------------+-------------------+
| TEST | T_SUBPART | NULL | NULL | NULL |
| TEST | T_SUBPART | P0 | NULL | NULL |
| TEST | T_SUBPART | P1 | NULL | NULL |
| TEST | T_SUBPART | P2 | NULL | NULL |
| TEST | T_SUBPART | P0 | P0sp0 | NULL |
| TEST | T_SUBPART | P0 | P0sp1 | NULL |
| TEST | T_SUBPART | P0 | P0sp2 | NULL |
| TEST | T_SUBPART | P1 | P1sp0 | NULL |
| TEST | T_SUBPART | P1 | P1sp1 | NULL |
| TEST | T_SUBPART | P1 | P1sp2 | NULL |
| TEST | T_SUBPART | P2 | P2sp0 | NULL |
| TEST | T_SUBPART | P2 | P2sp1 | NULL |
| TEST | T_SUBPART | P2 | P2sp2 | NULL |
+-------+------------+----------------+-------------------+-------------------+
13 rows in set