1.
dba_tab_stats_history
해당 뷰는 dba가 액세스할 수 있는 모든 테이블에 대한 테이블 통계 수정 내역(history)을 제공합니다.
해당 테이블에 대해서 통계정보에 대한 수정사항이 있다면 이력이 남게 됩니다.
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
SYS WRH$_SEG_STAT WRH$_SEG_STAT_1627335910_2182 2021/11/10 22:04:46.147679 +09:00
SYS WRH$_SEG_STAT WRH$_SEG_STAT_1627335910_2216 2021/11/10 22:04:45.480635 +09:00
SYS WRH$_SEG_STAT WRH$_SEG_STAT_1627335910_2289 2021/11/10 22:04:44.870872 +09:00
SYS WRH$_SEG_STAT WRH$_SEG_STAT_1627335910_2265 2021/11/10 22:04:44.251050 +09:00
SYS WRH$_SEG_STAT WRH$_SEG_STAT_1627335910_2241 2021/11/10 22:04:43.623993 +09:00
...https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2103.htm#REFRN20490
ALL_TAB_STATS_HISTORY
ALL_TAB_STATS_HISTORY ALL_TAB_STATS_HISTORY provides a history of table statistics modifications for all tables accessible to the current user. Related Views DBA_TAB_STATS_HISTORY provides a history of table statistics modifications for all tables in the d
docs.oracle.com
2.
dba_tab_statistics
데이터베이스에 있는 모든 테이블에 대한 옵티마이저 통계를 표시합니다.
통계정보에 대한 상세한 정보들이 표시됩니다. 각 컬럼에 대한 정리는 링크를 참조하시길 바랍니다.
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO IM_IMCU_COUNT IM_BLOCK_COUNT IM_STAT_UPDATE_TIME SCAN_RATE SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
CIFOWN TB_ATB7120 TABLE 1997884636 181097173 0 0 0 959 0 0 1997884636 2021/10/17 12:17:33 YES NO NO SHARED
CIFOWN TB_ATB7120 PT_ATB7120_201707 13 PARTITION 0 0 0 0 0 0 2021/05/09 10:41:50 YES NO NO SHARED
CIFOWN TB_ATB7120 PT_ATB7120_201906 36 PARTITION 0 0 0 0 0 0 2021/05/09 10:47:33 YES NO NO SHARED
CIFOWN TB_ATB7120 PT_ATB7120_201808 26 PARTITION 0 0 0 0 0 0 2021/05/09 10:41:58 YES NO NO SHARED
CIFOWN TB_ATB7120 PT_ATB7120_202011 53 PARTITION 72821047 5943692 0 0 0 956 72821047 2021/05/09 11:32:09 YES NO NO SHARED
CIFOWN TB_ATB7120 PT_ATB7120_202209 75 PARTITION 75663085 9686665 0 0 0 951 75663085 2021/10/17 11:03:35 YES NO NO SHAREDhttps://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2102.htm#REFRN20376
ALL_TAB_STATISTICS
ALL_TAB_STATISTICS ALL_TAB_STATISTICS displays optimizer statistics for the tables accessible to the current user. Related Views DBA_TAB_STATISTICS displays optimizer statistics for all tables in the database. USER_TAB_STATISTICS displays optimizer statist
docs.oracle.com
3.
dba_part_col_statistics
데이터베이스에 있는 모든 테이블 파티션에 대한 컬럼 통계정보 및 히스토그램 정보를 표시합니다.
OWNER TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS NOTES AVG_COL_LEN HISTOGRAM
CIFOWN TB_ATB7120 PT_ATB7120_202107 SNSR_OCP_CD2 502 30303030 72303139 0.001992032 75375008 1 288077 2021/10/17 10:58:46 YES NO 2 NONE
CIFOWN TB_ATB7120 PT_ATB7120_202112 SNSR_OCP_CD2 502 30303030 72303139 0.001992032 75375008 1 288077 2021/10/17 10:58:46 YES NO 2 NONE
https://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_1110.htm#i1583999
ALL_PART_COL_STATISTICS
ALL_PART_COL_STATISTICS ALL_PART_COL_STATISTICS provides column statistics and histogram information for table partitions accessible to the current user. Related Views DBA_PART_COL_STATISTICS provides such information for all table partitions in the databa
docs.oracle.com
4.dba_ind_statistics
DBMS_STATS 패키지를 사용하여 현재 사용자가 액세스할 수 있는 테이블의 인덱스에 대한 옵티마이저 통계정보를 표시합니다.
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
SYS I_ACCESS1 SYS ACCESS$ INDEX 1 463 112061 1 1 4769 114226 114226 2021/08/03 22:00:54 YES NO NO SHARED
SYS I_ACLMV$_1 SYS ACLMV$ INDEX 0 0 0 0 0 0 0 0 2017/04/01 21:49:12 YES NO NO SHARED
SYS I_ACLMVREFSTAT$_1 SYS ACLMVREFSTAT$ INDEX 0 0 0 0 0 0 0 0 2017/04/01 21:49:12 YES NO NO SHARED
SYS I_ACLMVSUBTBL$_1 SYS ACLMVSUBTBL$ INDEX 0 0 0 0 0 0 0 0 2017/04/01 21:49:12 YES NO NO SHARED
SYS I_ADMINAUTH1 SYS ADMINAUTH$ INDEX 0 1 3 1 1 1 3 3 2021/08/03 22:01:28 YES NO NO SHARED
SYS I_ADOIMSEGTD_ID SYS ADO_IMSEGTASKDETAILS$ INDEX 0 0 0 0 0 0 0 0 2017/04/01 21:49:12 YES NO NO SHARED
SYS I_ADOIMSEGTD_OBJ SYS ADO_IMSEGTASKDETAILS$ INDEX 0 0 0 0 0 0 0 0 2017/04/01 21:49:12 YES NO NO SHARED
SYS I_ADOIMTASKS_ID SYS ADO_IMTASKS$ INDEX 0 0 0 0 0 0 0 0 2017/04/01 21:49:12 YES NO NO SHARED
SYS SYS_IL0000019110C00071$$ SYS ALERT_QT INDEX NO NO ALL SHAREDhttps://docs.oracle.com/database/121/REFRN/GUID-AAC726B8-B9E6-43D8-9C7A-93DC5B4766B9.htm#REFRN20360
ALL_IND_STATISTICS
CLUSTERING_FACTOR NUMBER Indicates the amount of order of the rows in the table based on the values of the index. If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend
docs.oracle.com
추가적으로 딕셔너리뷰 알게되면 추가하겠습니다
이상입니다 ==
'Oracle > 운영' 카테고리의 다른 글
| 통계정보 복사하기 (partition table) (0) | 2021.11.19 |
|---|---|
| BULK INSERT (대량 INSERT 작업) 일반INSERT 작업과 비교 (0) | 2021.11.18 |
| 테이블 파티션 Split,Merge,Coalesce (0) | 2021.11.08 |
| 대용량 테이블 컬럼 순서 변경 테스트(12c new feature column visiable 기능 사용) (0) | 2021.11.03 |
| 다른 스키마(유저) Database link 삭제하기 (0) | 2021.10.27 |
댓글