Expandmenu Shrunk


  • Category Archives oracle tunning
  • DBMS_STATS解析

    
    
    
    一、统计信息分类
    Database statistics are produced by:
    – Using the DBMS_STATS package (recommended)
    – Running the ANALYZE command
    • System statistics are produced by using the
      DBMS_STATS package.
    • Statistics are stored in the data dictionary tables
       and cached in the shared pool.
    • CBO converts statistics into costs
        CBO优化器将统计信息转化为成本计算
    
    prior to use in costing execution plans.
    • You can gather statistics for tables, indexes, and
       table columns separately.
    为了基于成本CBO计算,需要对tables,indexes,table columns进行单独的数据统计
    
    
    
    二、.DBMS_STAT包含如下内容的统计信息
    DBMS_STATS statistics that are generated include the following:
    • Table statistics
      Number of rows:表行数
      Number of blocks:
      Average row length:avg_lenth
      Global statistics (if requested)
      User statistics (if requested)
      Sample size
       Last analyzed
    • Column statistics
      Number of distinct values (NDV) in column: 列的位置值
      Number of nulls in column:列里面包含的null值
      Data distribution (histogram):数据的分布(直方图)
    
    • Index statistics
      Number of leaf blocks:叶节点block数
      Levels                         二元高度
      Clustering factor          集簇因子
    • System statistics (discussed later in this lesson)
    
      I/O performance and utilization
      CPU performance and utilization
    
    2.1Table表统计信息
    
    
    DBA_TABLES and DBA_TAB_[SUB]PARTITIONS
    • Used to determine:
    – Table and (sub)partition access cost
    – Join cardinality
    – Join order
    • Some of the statistics generated are:
    – Row count (NUM_ROWS)
    – Block count (BLOCKS) Exact
    
    dba_tables
    1)NUM_ROWS行数量
    This is the basis of cardinality computations. Row count is especially important if the
    table is the driving table of a nested loops join; the row count defines how many times
    the inner table will be probed
    2)BLOCKS
    Blocks is the number of used data blocks. Block count in combination with
    DB_FILE_MULTIBLOCK_READ_COUNT gives the base table access cost as
    described in an earlier lesson.
    3)AVG_ROW_LEN
    This is the average length of a row in the table in bytes.
    
    4)EMPTY_BLOCKS(HWK高水位线下,空闲的blocks)
    Number of empty (never used) data blocks in the table. This is the number of blocks
    between the used data blocks and the high-water mark.
    5)AVG_SPACE(空闲block空间的平均大小)
    This is the average amount of free space, in bytes, in a data block allocated to the
    table.
    6)CHAIN_CNT
    This is the number of rows in the table that are chained from one data block to
    another, or that have migrated to a new block, requiring a link to preserve the old
    ROWID.
    
    
    2.2Index Statistics(索引统计信息)
    DBA_INDEXES and DBA_IND_[SUB]PARTITIONS
    • Used to decide:
    – Full table scan versus index scan
    • Statistics generated are:
    – B*-tree level (BLEVEL) Exact
    – Leaf block count (LEAF_BLOCKS)
    – Clustering factor (CLUSTERING_FACTOR)
    – Distinct keys (DISTINCT_KEYS)
    
    
    In general, in order to select an index access, the CBO requires a predicate on the
    prefix of the index columns. However, in case there is no predicate and all columns
    referenced in the query are present in an index, the CBO considers using a full index
    scan versus a full table scan.
    1)一般情况下,CBO为了走索引,必须要求谓词(where)中带有一个前导的索引的字段;
        比如t(id,name,loc),ind on(id)
        select id,name from t where id=xxx  (id为谓词)
    2)特殊的情况,查询语句没有谓词(没有where),同时查询的所有列都包含在一个索引中(可能是组合索引),那么CBO
      会根据具体情况走Index Full Scan或者Table Full Scan(取决于全扫表索引叶子的成本和Table Full Scan的成本)
    注:Index Range Scan就是叶子节点到叶子节点的范围扫描
    如:
    --表
     OBJECT_ID OBJECT_NAME                                                                                                                      OBJECT_TYPE         STATUS
    ---------- -------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
            20 ICOL$                                                                                                                                                    TABLE               VALID
    --索引
    
     OWNER      TABLE_NAME                INDEX_NAME                COLUMN_NAME          COL_LE DESC UNIQUENESS BLEVEL LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
    ---------- ------------------------- ------------------------- -------------------- ------ ---- ---------- ------ ----------- ---------- ------------- -----------------
    TEST       T_OBJ                     IND_OBJ                   OBJECT_ID                  22     ASC  NONUNIQUE  1              234      34271         34270               422
    TEST       T_OBJ                     IND_OBJ                   OBJECT_NAME          128    ASC  NONUNIQUE  1              234      34271         34270               422
    TEST       T_OBJ                     IND_OBJ                   OBJECT_TYPE          19       ASC  NONUNIQUE  1              234      34271         34270               422
    
    
    
    (object_id包含null值,null不能对索引产生影响导致走全表)
    
    1)
    SQL> select object_id from t_obj;
    
    68539 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 172510092
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       | 34271 |   167K|    55   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| T_OBJ | 34271 |   167K|    55   (0)| 00:00:01 |              ---全表扫描
    ---------------------------------------------------------------------------
    
    
    2)Alter table t_obj modify object_id not null;
    演示对于组合索引的各个字段查询,都是ind full scan
    select obj_id from t_obj;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2699271081
    
    --------------------------------------------------------------------------------
    | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |         | 34271 |   167K|    53   (0)| 00:00:01 |
    |   1 |  INDEX FAST FULL SCAN| IND_OBJ | 34271 |   167K|    53   (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    select object_name from t_obj;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2699271081
    
    --------------------------------------------------------------------------------
    | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |         | 34271 |   301K|    53   (0)| 00:00:01 |
    |   1 |  INDEX FAST FULL SCAN| IND_OBJ | 34271 |   301K|    53   (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    
    
    select object_type from t_obj;
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2699271081
    
    --------------------------------------------------------------------------------
    | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |         | 34271 |   301K|    53   (0)| 00:00:01 |
    |   1 |  INDEX FAST FULL SCAN| IND_OBJ | 34271 |   301K|    53   (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    
    SQL> select object_id,object_name,object_type from t_obj
      2  where object_id=3444 and object_type='TABLE';
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3193802408
    
    ----------------------------------------------------------------------------
    | Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |         |     1 |    39 |     2   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN| IND_OBJ |     1 |    39 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    SQL> select * from t_obj  where object_id=10237 and object_name='T' and object_type='TABLE';
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2263293454
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |     1 |    43 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ   |     1 |    43 |     2   (0)| 00:00:01 |       (组合索引所有字段都用到,类似唯一定位?)
    |*  2 |   INDEX RANGE SCAN          | IND_OBJ |     1 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    
    2.3Column Statistics
    DBA_TAB_COL_STATISTICS and DBA_TAB_HISTOGRAMS
    DBA_[SUB]PART_COL_STATISTICS
    DBA_[SUB]PART_HISTOGRAMS
    • Count of distinct values of the column
       (NUM_DISTINCT)
    • Low value (LOW_VALUE) Exact
    • High value (HIGH_VALUE) Exact
    • Number of NULLS (NUM_NULLS)
    
    NUM_DISTINCT
    Used in selectivity calculations (for example, 1/NDV)
    LOW_VALUE and HIGH_VALUE
    The CBO assumes uniform distribution of values between low and high values for all
    data types. These values are used to determine range selectivities.
    NUM_NULLS
    Helps with selectivity of nullable columns and IS NULL and IS NOT NULL
    predicates
    DENSITY
    Only relevant for histograms; used as selectivity estimate for nonpopular values
    Above columns remain for backward compatibility with Oracle7. This information is
    now in the {TAB|PART}_COL_STATISTICS views. This view now picks up these
    values from HIST_HEAD$ rather than COL$.
    NUM_BUCKETS
    The number of buckets in the column histogram
    
    
    
    2.4Default Statistics
    
      
    
    Default statistics can be important if the CBO is forced when insufficient statistics
    have been gathered or some objects are missing statistics. With bind variables, you
    often have no alternative to the use of the defaults due to the lack of other
    information. Because the statistics are hard coded, there is a high likelihood that they
    are inaccurate. All objects should be sufficiently analyzed prior to use of the CBO.
    默认的统计信息对于CBO会很重要,尤其是在当前没有足够的统计信息的场合&局部统计信息丢失的场景。
    Default Statistics (from kke.h) for Oracle7.3.3 (and later)
    Selectivity for relations on indexed columns .009
    Selectivity for = on indexed columns .        004
    Multiblock read factor                          8
    Multiblock write factor                         8
    Remote table average row length               100
    # of blocks                                   100
    Scan cost                                      13
    Index levels                                    1
    Number leaf blocks/key                          1
    
    


  • Oracle优化之-插入性能飙升记

    最近优化一个项目,使得数据插入速度由20row/s提升到900+row/s,性能飙升45倍左右。详细介绍如下:

     1

     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
      2
     
     
     
     
     
     
     
     
     
     
     
     
    如下SQL被诊断为可疑SQL
     
    3
     
     
    Latch命中率60%,比较低下
     
     
    问题分析:
    频繁插入,select查询效率低下,logical read高.
    解决问题思路:
    减少日志产生,direct insert,parallel,降低latch pct miss(提高cache buffer chains),提升写数据进程(Mysql不能动态调节)
     
    解决办法:
    1)SQL优化(一)
    创建查询对应索引,搜集统计信息(histogram),添加hint
    2)SQL优化(二)
    调节insert插入
    insert   /*+ append parallel  */  into acctrans_account(account_no, user_id, account_alias, account_titleId, independent_account_no, title_name, balance_direction, currency, balance, account_type, STATUS, paymodel, credit_amount, credit_balance, freeze_amount, system_amount, memo, raw_add_time, history_payee, history_payer, history_deposit, history_withdraw, history_deposit_back, history_withdraw_back) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24) 
     
     3)添加dbwr进程数默认为8,增加到16
     
    4)减少cache buffers chains争用(略)
     
     
    结果:
    1插入效率:900+rows/s,之前仅仅为20row/s,提升45倍
    2Latch命中率:(有一定提升,还有较大的提升空间)
    latch_hitratio
    —————
            84.87 %
     
    3.
    4
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
    5
     
     


  • 关于Oracle Reverse Index,Block 争用一点探索

    在Oracle b-tree索引插入时,最为容易导致热点快的问题,之前也有一些国内大师探索类似问题,因为索引插入会分裂(99-1,5-5),本人觉得比较常见的办法有:

    1.减少index block的index entry条目数量(pct free决定),但是会使得索引的存储空间加大,甚至浪费

    2.创建Reverse Index,这里探索一下Reverse Index的一些见解。

     

    一、相反存储的示例:

    —————————————————-
    反键存储
    DUMP(‘ABC’)
    ———————-
    Typ=96 Len=3: 97,98,99

    DUMP(REVERSE(‘ABC’))
    ———————-
    Typ=96 Len=3: 99,98,97

     

     

    1.创建基础表

    ——————————————————————–
    表值:ab,bc,cd,de,ef,fg,ge (不能使单个字符,必须为2个字符以上的才能体现Reverse)
    2.创建正常的索引,dump

    ————————
    正常索引
    row#0[8020] flag: ——, lock: 0, len=12
    col 0; len 2; (2):  61 62                                                                                 —key:ab,为ASCII码,附录为转换规则
    col 1; len 6; (6):  02 80 02 8a 00 07
    row#1[8008] flag: ——, lock: 0, len=12
    col 0; len 2; (2):  62 63
    col 1; len 6; (6):  02 80 02 8a 00 08
    row#2[7996] flag: ——, lock: 0, len=12
    col 0; len 2; (2):  63 64
    col 1; len 6; (6):  02 80 02 8a 00 09
    row#3[7984] flag: ——, lock: 0, len=12
    col 0; len 2; (2):  64 65
    col 1; len 6; (6):  02 80 02 8a 00 0a
    row#4[7972] flag: ——, lock: 0, len=12
    col 0; len 2; (2):  65 66
    col 1; len 6; (6):  02 80 02 8a 00 0b
    row#5[7960] flag: ——, lock: 0, len=12
    col 0; len 2; (2):  66 67
    col 1; len 6; (6):  02 80 02 8a 00 0c
    row#6[7948] flag: ——, lock: 0, len=12
    col 0; len 2; (2):  67 65
    col 1; len 6; (6):  02 80 02 8a 00 0d
    —– end of leaf block dump —–
    End dump data blocks tsn: 10 file#: 10 minblk 778 maxblk 778

    ———————-
    Reverse索引
    row#0[8020] flag: ——, lock: 2, len=12
    col 0; len 2; (2):  62 61                                                                       —反键key:ba
    col 1; len 6; (6):  02 80 02 8a 00 07
    row#1[8008] flag: ——, lock: 2, len=12
    col 0; len 2; (2):  63 62
    col 1; len 6; (6):  02 80 02 8a 00 08
    row#2[7996] flag: ——, lock: 2, len=12
    col 0; len 2; (2):  64 63
    col 1; len 6; (6):  02 80 02 8a 00 09
    row#3[7984] flag: ——, lock: 2, len=12
    col 0; len 2; (2):  65 64
    col 1; len 6; (6):  02 80 02 8a 00 0a
    row#4[7948] flag: ——, lock: 2, len=12
    col 0; len 2; (2):  65 67
    col 1; len 6; (6):  02 80 02 8a 00 0d
    row#5[7972] flag: ——, lock: 2, len=12
    col 0; len 2; (2):  66 65
    col 1; len 6; (6):  02 80 02 8a 00 0b
    row#6[7960] flag: ——, lock: 2, len=12
    col 0; len 2; (2):  67 66
    col 1; len 6; (6):  02 80 02 8a 00 0c
    —– end of leaf block dump —–
    End dump data blocks tsn: 10 file#: 10 minblk 778 maxblk 778

     

    结论:

    1)Reverse Index只有在多’字符?’key的时候才会表现与Normal Index存储相反,对于单’字符?’在索引中Norma与Reverse存储时一样的。

    2)对于ASSM管理方式,Index对应的Key被删除后重新插入,实际的dba位置和以保持一致,及可以立即Reuse删除的空间。

    3)对于RAC环境的requence争用,可以采用非sequence的方式,直接在程序集采用程序的唯一值来生成pk,减少争用。

    4).减少表争用(多个free lists)

    create table t1(id number) storage(freelists 5);
     alter table t1 storage(freelists 5);

    5).减少Leaf Block争用 (reverse key)

    6).减少插入争用 (freelist group,NO ASSM)
    RAC集群中采取多个freelist group可以减少表插入争用
    storage(freelist  groups  5)                        

     ==================================

    附录一,Index dump内容详解:

    Leaf Entries (Unique)–唯一索引
    Richard Foote -Index Internals
     
    row#0[8025] flag: –D–, lock: 0, len=11, data:(6): 01 40 00 7a 00 2d
    col 0; len 2; (2): c1 03

    第一行:
    Row number (starting at #0) followed by [starting location within block] (行号、Flag标记、锁、索引长度、表行rowid)
    Followed by various flags (deletion flag, locking information etc.),这里表面为删除的(D)
    Followed by total length of index entry followed by the rowid
    第二行:
    Index column number (starting at 0) followed by column length followed by column value(索引序列号、表列长度、表Key值)
    Repeated for each indexed column
    Repeated for each index entryNote: Total overhead is 3 bytes for each leaf index entry (unique index)

     

    Leaf Entries (Non-Unique)
    Richard Foote -Index Internals
    row#0[8019] flag: –D–, lock: 0, len=17
    col 0; len 7; (7): 41 43 43 45 53 53 24
    col 1; len 6; (6): 01 40 00 0b 00 1d

    第一行
    Row number (starting at #0) followed by [starting location within block] (行号、Flag标记、锁、索引长度、表行rowid)
    Followed by various flags (deletion flag, locking information etc.),这里表面为删除的(D)
    Followed by total length of index entry followed by the rowid

    第二行
    Index column number (starting at 0) followed by column length followed by column value

    第三行
    Repeated for each indexed column with last column in non-unique index being the rowid of index entry (hence making the index entry effectively unique anyways)[辅助行:保持非唯一所以一直唯一](索引序列号、表列长度、表Key值)
    Repeated for each index entry
    Note: Total overhead is 4 bytes, 1 more than unique index

     

    ===================

    附录二:

     

     

     

     

     

     

     

     

     

     

     




香港马会开奖记录|香港马会开奖资料|香港马会开奖现场|香港马会走势图|香港马会开奖结果直播|香港马会n730|