[bbk2342] 第47集 - Chapter 09-Optimizing Sore Perations(02)
总结
Table statistics ---> DBA_TABLES;
获取Table statistics，通过DBA_TABLES 数据字典
SQL> cldesc DBA_TABLES; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) FLASH_CACHE VARCHAR2(7) CELL_FLASH_CACHE VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8) COMPRESSION VARCHAR2(8) COMPRESS_FOR VARCHAR2(12) DROPPED VARCHAR2(3) READ_ONLY VARCHAR2(3) SEGMENT_CREATED VARCHAR2(3) RESULT_CACHE VARCHAR2(7)
Index statistics ---> DBA_INDEXES;
获取Index statistics,通过DBA_INDEXES数据字典
SQL> cldesc DBA_INDEXES; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) INDEX_NAME NOT NULL VARCHAR2(30) INDEX_TYPE VARCHAR2(27) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) UNIQUENESS VARCHAR2(9) COMPRESSION VARCHAR2(8) PREFIX_LENGTH NUMBER TABLESPACE_NAME VARCHAR2(30) INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER PCT_THRESHOLD NUMBER INCLUDE_COLUMN NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER PCT_FREE NUMBER LOGGING VARCHAR2(3) BLEVEL NUMBER LEAF_BLOCKS NUMBER DISTINCT_KEYS NUMBER AVG_LEAF_BLOCKS_PER_KEY NUMBER AVG_DATA_BLOCKS_PER_KEY NUMBER CLUSTERING_FACTOR NUMBER STATUS VARCHAR2(8) NUM_ROWS NUMBER SAMPLE_SIZE NUMBER LAST_ANALYZED DATE DEGREE VARCHAR2(40) INSTANCES VARCHAR2(40) PARTITIONED VARCHAR2(3) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) BUFFER_POOL VARCHAR2(7) FLASH_CACHE VARCHAR2(7) CELL_FLASH_CACHE VARCHAR2(7) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) PCT_DIRECT_ACCESS NUMBER ITYP_OWNER VARCHAR2(30) ITYP_NAME VARCHAR2(30) PARAMETERS VARCHAR2(1000) GLOBAL_STATS VARCHAR2(3) DOMIDX_STATUS VARCHAR2(12) DOMIDX_OPSTATUS VARCHAR2(6) FUNCIDX_STATUS VARCHAR2(8) JOIN_INDEX VARCHAR2(3) IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3) DROPPED VARCHAR2(3) VISIBILITY VARCHAR2(9) DOMIDX_MANAGEMENT VARCHAR2(14) SEGMENT_CREATED VARCHAR2(3)
Column statistics ---> ALL_TAB_COL_STATISTICS
获取Column statistics,通过ALL_TAB_COL_STATISTICS
SQL> desc ALL_TAB_COL_STATISTICS; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(30) NUM_DISTINCT NUMBER LOW_VALUE RAW(32) HIGH_VALUE RAW(32) DENSITY NUMBER NUM_NULLS NUMBER NUM_BUCKETS NUMBER LAST_ANALYZED DATE SAMPLE_SIZE NUMBER GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) AVG_COL_LEN NUMBER HISTOGRAM VARCHAR2(15)
Segment-Level Statistics
Statistics collected and the method of collection:
- Logical reads:Sampled
- Buffer busy waits:Continuous
- Db block changes:Sampled
- Physical reads:Continuous
- Physical writes:Continuous
- Physical reads direct:Continuous(直接读，读了之后存放在PGA或者UGA，不会存放在buffer cache中的.)
- Physical writes direct:Continuous(同上)
- Global cache consistency read blocks served:Continuous(read consistency 读一致性)
- Global cache current block served:Continuous
- ITL waits:Continuous
- Row lock waits:Continuous
解读上述统计信息中的含义:sampled | Continuous
sampled：统计指标信息的取样
Continuous:自instance 启动以来，统计数据就会不断累积.
Query the Segment-Level Statistics
Segment-level statistics are queried using:
- v$segstat_name:Lists the segment statistics being collected.
- v$segstat:Displays the statistic value,statistic name,and other basic information
- v$segment_statistics:Displays the segment owner and tablespace name in addition to all the rows contained in v$segstat.
v$segstat_name
SQL> desc v$segstat_name; Name Null? Type ----------------------------------------- -------- ---------------------------- STATISTIC# NUMBER NAME VARCHAR2(64) SAMPLED VARCHAR2(3) SQL> col name format a30 SQL> select * from v$segstat_name; STATISTIC# NAME SAM ---------- ------------------------------ --- 0 logical reads YES 1 buffer busy waits NO 2 gc buffer busy NO 3 db block changes YES 4 physical reads NO 5 physical writes NO 6 physical read requests NO 7 physical write requests NO 8 physical reads direct NO 9 physical writes direct NO 11 optimized physical reads NO STATISTIC# NAME SAM ---------- ------------------------------ --- 12 gc cr blocks received NO 13 gc current blocks received NO 14 ITL waits NO 15 row lock waits NO 17 space used NO 18 space allocated NO 20 segment scans NO 18 rows selected.
exm:根据Object#查询一张表在segment-level的统计数据信息
SQL> desc dba_objects; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) SQL> select object_id from dba_objects where owner='HR' and object_name='T'; OBJECT_ID ---------- 79428 SQL> col statistic_name format a40 SQL> select statistic_name,statistic#,value from v$segstat where dataobj#=79428; STATISTIC_NAME STATISTIC# VALUE ---------------------------------------- ---------- ---------- logical reads 0 64 buffer busy waits 1 0 gc buffer busy 2 0 db block changes 3 16 physical reads 4 0 physical writes 5 8 physical read requests 6 0 physical write requests 7 1 physical reads direct 8 0 physical writes direct 9 0 optimized physical reads 11 0 STATISTIC_NAME STATISTIC# VALUE ---------------------------------------- ---------- ---------- gc cr blocks received 12 0 gc current blocks received 13 0 ITL waits 14 0 row lock waits 15 0 space used 17 0 space allocated 18 65536 segment scans 20 0 18 rows selected.
Using Dynamic Sampling
这个新特性，是在oracle 9i以后才有的.
- Dynamic sampling should be used when:
- A better plan could be found
- The cost of collecting the statistics is minimal compared to the execution time
- The query is executed many times.
Enabling Dynamic Sampling
Dynamic sampling is set using:(0-10 level，通常情况下，9i版本 OPTIMIZER_DYNAMIC_SAMPLING参数值为1,10g、11g下此参数值为2-3)
Histograms
- Historgrams describe the data distribution of a particular column in more deatil.
- The give better predicate selectivity estimates for unevenly distributed data.
- You create histograms with the dbms_stats.gather_table_stats procedure.
- Data dictionary views:dba_histograms,dba_tab_histograms
Histograms的功能与作用就是，能够更加详细的分析出某一列中的数据分布情况.
