[TIP][024] 1Z0-024 StudyNotes Lesson 5-10

ora-1

谢

mugen

有没有全部的啊

lhyyzh

斑竹辛苦了，希望我也能为论坛出力

easyfree

[php]
9        Using Oracle Blocks Efficiently

* After completing this lesson, you should be able to do the following:
        Determine an appropriate block size
        Optimize space usage within blocks
        Detect and resolve row migration
        Monitor and tune indexes

* Database Storage Hierarchy        数据存储等级
        Tablespace --> Segment --> Extents --> Block
        一个Oracle Block包括一个或多个连续的OS Blocks
        一个Extent由一组连续的Blocks的逻辑单元
        一个Segment是一个或多个包含数据和逻辑存储结构的extents的集合
* Allocating an Extent
        当数据增长导致segment增长,此时Oracle Server执行几个递归的sql语句来寻找free space,并分配给segment,此过程叫动态增长,会降低性能
        避免动态扩展的方法
                Creating locally managed tablespaces
                        CREATE TABLESPACE user_data_1
                        DATAFILE 'oracle8/oradata/db1/lm_1.dbf'
                        SIZE 100M
                        EXTENT MANAGEMENT LOCAL
                        UNIFORM SIZE 2M;
                        一个本地化管理的表空间管理自己的extents并对每个datafile维护一个bitmap来跟踪其中block的use和free情况
                        当一个extent被分配或者free for reuse,Oracle Server更改bitmap值来显示block的新状态,这种更改因没有修改数据字典而不会产生回滚信息
                Sizing segments appropriately
                        决定object的最大size
                        通过storage参数分配足够大的extent

                        SELECT owner, table_name, blocks, empty_blocks
                        FROM dba_tables
                        WHERE empty_blocks / (blocks+empty_blocks) < 0.1;
                        找出segment中小于10%的free block, 手工扩展
                        命令: ALTER TABLE/INDEX/CLUSTER
                Monitoring and preallocating extents.        监控并预分配extent

[/php]

easyfree

9 -1
[php]
* Large Extents                通常原则,Large Extents优先于小的extents
        Advantages of Large Extents
                较小的动态增长可能性
                对提高性能有细微的好处:Oracle Server从disk读取一个large extent比读取很多小的extent减少了些multiblock reads
                        为避免局部的multiblock reads,设置extent size为5 × DB_FILE_MULTIBLOCK_READ_COUNT的倍数(5是five-block boundaries)
                        通过对I/O和space的分配尺寸匹配相应的的extent size,可以使得包含很多extents的segment的cost最小化
                        对于full table scan,仅包含一个extent还是多个extent,对查询性能无影响
                        对于很大的表,OS的限制使object必须有多个extents
                        使用索引查询,不受index包含一个或者多个extent的影响        -- 重点
                        Extent maps列出某个segment的所有extent,对MAXEXTENTS UNLIMITED,这些map位于多个block中,若能在单个I/O中读出extent map
                                则对性能有好处,若在全表扫描中还需多个I/O去读extent map,性能就会降低很多,且多个extent加重字典的负担
        Disadvantages of Large Extents
                大的扩展段需要很多连续的block,在分配extent时,获取足够的连续空间比较困难
                初始时会造成空间浪费
* Database Block Size
        database tuning的目标之一就是: 将访问block的数量减少到最小
        开发者调整应用和sql语句
        DBA则:
                Using a larger block size                        -- 使用较大的Block Size
                Packing rows as closely as possible into blocks        -- Block中装尽可能多的row
                Preventing row migration                        -- 防止行迁移        -- 不幸的是,后两条矛盾
        Oracle Block Size
                特征
                        1. 数据库创建时由参数DB_BLOCK_SIZE设置,无法修改,除非重建或复制数据库,这给测试不同block size的应用带来困难
                        2. 数据文件I/O操作的最小单位
                        3. 多数平台上缺省为2k/4k,最大可到64k,与平台相关
                        4. 如果OS连续读next block,而应用平台有很多full table scan,则OS I/O size应大于等于数据库block size        ????
                影响性能,一般原则:
                        1. OLTP环境中,随机地访问large objects,建议用小些的block size
                        2. DSS环境中,大数据量的顺序访问,建议用大的block size
                small block size的优缺点
                        优点
                                因每个block中包含的row少,故减少block的争用
                                对于小的行,小的block size较好
                                小的block size对随机访问较好,因为某个block被读入内存后,reuse的可能性较小,小的block size可使buffer size
                                        利用率更高,特别是在内存有限制的情况下
                        缺点
                                small block的管理代价相对高                -- ???why???
                                每个block中只能存放很少的row,可能导致更多的I/O
                                Small blocks can cause more index blocks to be read.        ???
                Large Block Size 的优缺点
                        优点
                                较小的管理代价(更多空间存数据)
                                对顺序读取有利
                                适合于large row
                                提高了读index的性能,Large block中能存放更多的index entries,减少了index搜索树的层数(level),减少I/O
                        缺点
                                不适合OLTP环境,因为增加了index leaf block的争用
                                当随机访问Large Block中的small row,造成buffer cache的空间浪费(即小row浪费访问时的cache)
                若调整block size而无额外的内存, 也需调整DB_BLOCK_BUFFERS,这将影响buffer cache的命中率

[/php]

easyfree

9-2
[php]
* Block Packing Factors
        参数PCTFREE and PCTUSED也称为packing factors, 通过storage子句中定义, 它们指的空余空间指除了:row数据,block header管理空间,overhead
        PCTFREE
                Free Space大于该百分比的block将作为update该block中row时使用,进入free list
        PCTUSED
                Used Space小于该百分比的block将作为insert(增加新row)考虑使用,进入free list
        DELETE和UPDATE语句将影响free space, 释放的空间可能是不连续的, 比如处于block中间的row被删除
        Oracle Server接合free sapce,当:
                一个INSERT or UPDATE使用某个block,它包含有足够容纳下新row的free space
                free space碎片没有一个连续的段(section),可以容纳row piece

                由于对free space连续的压缩将影响性能,所以Oracle Server只有在上述情况下才进行接合操作
        设置原则
                PCTFREE
                        缺省=10, 如果没有update,可设置为0
                        PCTFREE = 100 × upd/(upd + ins)
                PCTUSED
                        缺省=40
                        PCTUSED = 100 – PCTFREE – 100 × rows × (ins + upd) / blocksize
                注:         upd        -- update时平均增加的字节数
                        ins        -- insert时平均初始row length
                        rows        -- is the number of rows to be deleted before free list maintenance occurs
                                -- 对free list维护发生之前删除的row数量                ??如何计算??
                PCTUSED公式表明: 当block中有足够空间为多于一个row的update时,允许插入表                -- ???
                对有很多insert的table,增大PCTUSED将改进block存储性能,blocks的密集存储可能引起free list竞争,但将使得请求的block更少,
                        table更小,读操作更快                -- 重点
                对现存的table更改PCTFREE,PCTUSED将不能立刻起作用,将来的DML语句将按新的规则处理
* Migration and Chaining
        Chaining: insert或update时,如果一个empty block无法容纳一个完整row,则发生row chaining,比如一个包含LOB的行,这种情况无法避免
                        加大DB_BLOCK_SIZE可以减少chain的发生
        Migration: 当update增加数据时,如果原block无法容纳,则整个block移动到另一个有足够free space容纳下该行的block中,同时在原block中保留
                   original row piece,将一个链接指向实际存储block,这个migrated row的ROWID没有改变,所以index没有改变,仍指向原来的block
                   一般是PCTFREE过小引起的,使得UPDATE没有足够的空间
        行迁移和行链接对性能都有很大影响
                Insert,UPdate语句将引起额外的处理
                查询语句将产生额外的I/O
        Detecting Chaining and Migration
                1. ANALYZE table COMPUTE STATISTICS;                -- 分析存在迁移和链接的行数和占总行数的百分比
                        SELECT num_rows, chain_cnt FROM dba_tables                -- 这里chain_cnt是chained和migrated的合计
                        WHERE table_name='...';
                2. 使用UTLCHAIN.SQL创建表: chained_rows                -- 使用下列语句直接创建也可以,列名必须相同
                        create table CHAINED_ROWS (
                        owner_name varchar2(30),
                        table_name varchar2(30),
                        cluster_name varchar2(30),
                        partition_name varchar2(30),
                        head_rowid rowid,
                        analyze_timestampdate );
                   ANALYZE table LIST CHAINED ROWS;        -- 分析哪些行存在chained和migrated
                           SELECT owner_name, table_name, head_rowid
                        FROM chained_rows
                        WHERE table_name = '...';

                        OWNER_NAME TABLE_NAME HEAD_ROWID
                        ---------- ---------- ------------------
                        SALES            ORDER_HIST AAAAluAAHAAAAA1AAA
                        SALES            ORDER_HIST AAAAluAAHAAAAA1AAB
                3. V$SYSSTAT view or in report.txt
                        统计指标: table fetch continued row
        Eliminating Migrated Rows
                1. ANALYZE TABLE ... LIST CHAINED ROWS;
                2. Copy the rows to another table.
                3. Delete the rows from the original table.
                4. Insert the rows from step 2 back into the original table.
                Step 4 eliminates migrated rows because migration only occurs during an UPDATE operation.
                        -- 无法排除chained row

                脚本示例:        -- 注意,如果涉及外键约束,需先disable

                /* Get the name of the table with migrated rows */
                accept table_name prompt 'Enter the name of the table with migrated rows: '
                /* Clean up from last execution */
                set echo off
                drop table migrated_rows;
                drop table chained_rows;
                /* Create the CHAINED_ROWS table */
                @ $ORACLE_HOME/rdbms/admin/utlchain
                set echo on
                spool fix_mig
                /* List the chained & migrated rows */
                analyze table &table_name list chained rows;
                /* Copy the chained/migrated rows to another table */
                create table migrated_rows as
                select orig.* from &table_name orig, chained_rows cr
                where orig.rowid = cr.head_rowid
                and cr.table_name = upper('&table_name');
                /* Delete the chained/migrated rows from the original table */
                delete from &table_name
                where rowid in ( select head_rowid from chained_rows );
                /* Copy the chained/migrated rows back into the original table */
                insert into &table_name select * from migrated_rows;
                spool off

[/php]

easyfree

9-3
[php]
* The High-Water Mark
        Recorded in segment header block        -- 记录在segment的header block中
        Set to the beginning of the segment on creation                -- segment创建时设置
        Incremented in five-block increments as rows are inserted        --  row insert时,以5个block的增量增长
        Reset by the TRUNCATE command and not DELETE        -- Tuncate时复位,delete不复位
        Full table scan时候,从第一个block扫描至HWM处,其上部分为never unused block,浪费空间,但对性能没有影响
        HWM之上的空间可以用命令: ALTER TABLE <table_name> DEALLOCATE UNUSED... 回收
* Table Statistics
        analyze table test compute statistics;                -- 可以分析tables, indexes, and clusters
                NUM_ROWS         Number of rows in the table
                BLOCKS                 Number of blocks below the table high-water mark
                EMPTY_BLOCKS         Number of blocks above the table high-water mark        -- 注意是HWM之上的never unused blocks
                AVG_SPACE         Average free space in bytes in the blocks below high-water mark
                AVG_ROW_LEN         Average row length, including row overhead
                CHAIN_CNT         Number of chained or migrated rows in the table
        SELECT num_rows, blocks, empty_blocks as empty,
        avg_space, chain_cnt, avg_row_len
        FROM dba_tables
        WHERE owner = 'TEST'
        AND table_name = 'TEST'

          NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
        ---------- ---------- ---------- ---------- ---------- -----------
                15          2         13       7793          0          39
* DBMS_SPACE Package        -- 由catproc.sql调用dbmsutil.sql生成
        PROCEDURE FREE_BLOCKS                -- 返回object (table,index, or cluster)的free blocks信息
        自变量名称                       类型                    输入/输出默认值？
        ------------------------------ ----------------------- ------ --------
         SEGMENT_OWNER                  VARCHAR2                IN
         SEGMENT_NAME                   VARCHAR2                IN
         SEGMENT_TYPE                   VARCHAR2                IN
         FREELIST_GROUP_ID              NUMBER                  IN                -- 这个参数必须提供,使用1, 除非是OPS
         FREE_BLKS                      NUMBER                  OUT
         SCAN_LIMIT                     NUMBER                  IN     DEFAULT
         PARTITION_NAME                 VARCHAR2                IN     DEFAULT

        PROCEDURE UNUSED_SPACE                -- 返回object (table,index, or cluster)的unused space信息
        自变量名称                       类型                    输入/输出默认值？
        ------------------------------ ----------------------- ------ --------
         SEGMENT_OWNER                  VARCHAR2                IN
         SEGMENT_NAME                   VARCHAR2                IN
         SEGMENT_TYPE                   VARCHAR2                IN
         TOTAL_BLOCKS                   NUMBER                  OUT
         TOTAL_BYTES                    NUMBER                  OUT
         UNUSED_BLOCKS                  NUMBER                  OUT
         UNUSED_BYTES                   NUMBER                  OUT
         LAST_USED_EXTENT_FILE_ID       NUMBER                  OUT
         LAST_USED_EXTENT_BLOCK_ID      NUMBER                  OUT
         LAST_USED_BLOCK                NUMBER                  OUT
         PARTITION_NAME                 VARCHAR2                IN     DEFAULT

         一个示例脚本:

         declare
                owner varchar2(30);
                name varchar2(30);
                seg_type varchar2(30);
                tblock number;
                tbyte number;
                uBlock number;
                ubyte number;
                lue_fid number;
                lue_bid number;
                lublock number;
        BEGIN
                dbms_space.unused_space('&owner','&table_name','TABLE',
                tblock,tbyte,ublock,ubyte,lue_fid,lue_bid,lublock);
                dbms_output.put_line('Total blocks allocated to table = '
                || to_char(tblock));
                dbms_output.put_line('Total bytes allocated to table = '
                ||to_char(tbyte));
                dbms_output.put_line('Unused blocks(above HWM) = '
                ||to_char(ublock));
                dbms_output.put_line('Unused bytes(above HWM) = '
                ||to_char(ubyte));
                dbms_output.put_line('Last extent used file id = '
                ||to_char(lue_fid));
                dbms_output.put_line('Last extent used begining block id = '
                ||to_char(lue_bid));
                dbms_output.put_line('Last used block in last extent = '
                ||to_char(lublock));
        END;
        /
        输入owner的值： TEST
        输入table_name的值： TEST
        原值  13：  dbms_space.unused_space('&owner','&table_name','TABLE',
        新值  13：  dbms_space.unused_space('TEST','TEST','TABLE',
        Total blocks allocated to table = 16
        Total bytes allocated to table = 131072
        Unused blocks(above HWM) = 13
        Unused bytes(above HWM) = 106496
        Last extent used file id = 3
        Last extent used begining block id = 211
        Last used block in last extent = 3

        PL/SQL 过程已成功完成。


[/php]

easyfree

9-5
[php]
* Index Reorganization
        概述
                Indexes on volatile tables cause a performance problem.                -- 在DML较多的table上,所有可能引起性能问题
                        -- 在数据块中,Oracle server将插入的row替代删除的row
                        -- 但在index block中,entries必须是顺序的,值必须放在指定的block中,以和其他entries在同一range中
                        -- 经常的delete和insert table,会造成index segment中有很多碎片,需要定期重建索引
                Empty index blocks go to the free list.
                Even if a block contains only one entry, it must be maintained.
                        -- 只有当index block中所有entries被删除后,它才返回free list中(所以容易留下很多碎片)
                You may need to rebuild indexes.
        Monitoring Indexes
                ANALYZE INDEX index_name VALIDATE STRUCTURE;                -- 只有当前session可以看到结果

                SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100  index_usage
                FROM index_stats;
                INDEX_USAGE
                -----------
                         24

                INDEX_STATS
                        HEIGHT                  NUMBER
                        BLOCKS                  NUMBER
                        NAME                    VARCHAR2(30)
                        PARTITION_NAME          VARCHAR2(30)
                        LF_ROWS                 NUMBER                Number of values currently in the index
                        LF_BLKS                 NUMBER
                        LF_ROWS_LEN             NUMBER                Sum in bytes of the length of all values
                        LF_BLK_LEN              NUMBER
                        BR_ROWS                 NUMBER
                        BR_BLKS                 NUMBER
                        BR_ROWS_LEN             NUMBER
                        BR_BLK_LEN              NUMBER
                        DEL_LF_ROWS             NUMBER                Number of values deleted from the index
                        DEL_LF_ROWS_LEN         NUMBER                Length of all deleted values
                        DISTINCT_KEYS           NUMBER
                        MOST_REPEATED_KEY       NUMBER
                        BTREE_SPACE             NUMBER
                        USED_SPACE              NUMBER
                        PCT_USED                NUMBER
                        ROWS_PER_KEY            NUMBER
                        BLKS_GETS_PER_ACCESS    NUMBER
                        PRE_ROWS                NUMBER
                        PRE_ROWS_LEN            NUMBER

        Rebuilding Indexes
                若delete entries占当前entries20%以上,则可重建索引
                ALTER INDEX ... REBUILD
                        可调整所有存储参数,如:storage, tablespace, initrans等
                        PARALLEL or NOPARALLEL (NOPARALLEL is the default)
                        RECOVERABLE or UNRECOVERABLE (RECOVERABLE is the default)
                                -- UNRECOVERABLE在索引创建和重建时不写redo log,速度快        -- 仅对创建时有效
                                -- 此子句不是object的永久属性,只有创建时有效,所以不体现在数据字典中
                                -- 此属性不能更改
                                -- 隐含有LOGGING,意味着additional inserts are logged.        ???
                                -- The index is not recoverable if it needs to be re-created during a recovery operation.
                        LOGGING or NOLOGGING
                                -- NOLOGGING速度快
                                -- 此属性是永久的,体现在字典中
                                -- 影响到index整个生命周期,除非人工改变(ALTER INDEX NOLOGGING/LOGGING)
                注意:        UNRECOVERABLE and LOGGING are not compatible.
                        UNRECOVERABLE是为了兼容以前版本,8i中推荐使用LOGGING
                        UNRECOVERABLE相当于: CREATE ...NOLOGGING, 创建完成后手工改为 LOGGING
                        RECOVERABLE相当于: CREATE ... LOGGING
                        Rebuild一般比drop/recreate快,因为它使用fast full scan,它multiblock I/O读所有index blocks,然后丢弃branch blocks
                        8i支持online的rebuild(即rebuild时允许并行操作),这样对那些不能有downtime的高要求的系统有用
-- finish ---
[/php]

easyfree

[php]
10        Optimizing Sort Operations

* After completing this lesson, you should be able to do the following:
        Identify the SQL operations that require sorting
        Ensure that sorting is done in memory where possible
        Reduce the number of I/Os required for the sort runs
        Allocate temporary space appropriately

* Sort Operations
        需要排序的操作:
                Index creation                -- 在building B-tree之前sort indexed values
                Parallel insert operation involving index maintenance        -- 并行插入操作涉及索引维护
                ORDER BY or GROUP BY clauses
                DISTINCT values selection        -- 为排除重复行
                UNION, INTERSECT, or MINUS operators        -- 排除重复行
                Sort-merge joins        -- 执行一个涉及到两个表连接,而连接字段没有索引
                        -- 两个表作full table scan
                        -- 分别对两个表的没行进行排序
                        -- 将排序内容合并,对其中一个表的每一个row同另外一张表的每个row匹配
                        -- 从V$SYSSTAT中查询select name, value from v$sysstat where name = 'sorts (rows)';
                ANALYZE command execution
                        sys.dbms_utility.analyze_schema('schema','method');
                        分析某个schema中的所有table/clusters/indexes, method: ESTIMATE/COMPUTE/DELETE
        Sort Process        -- 当sort需要的内存小于参数SORT_AREA_SIZE,则在内存中进行,否则需要更多的空间
                1. 数据分成小的pieces,称作sort runs,每个sort sun单独排序
                2. Server process将sort run写到临时段,当server对另一个sort run这些段保留sort run的中间结果
                3. sorted pieces最后合并成最终结果, 如果SORT_AREA_SIZE不够大,不能一次合并所有sort runs,则分成多个合并操作,每次一部分
        参数SORT_MULTIBLOCK_READ_COUNT
                缺省=2
                该参数意味着: 每次sort从a temporary segment中读取的block的number
                通过提高该参数强制sort process在每个merge pass(合并操作)过程中从disk上读取sort run更多的section到内存中
                如果系统在sort操作过程中每秒执行了过多的I/O,而CPU相对空闲,考虑增加此参数强制sort操作执行较少的较大的I/O
                同时也减少合并width,或减少runs数量,这样可以在一次merge pass中完成合并; 而且增多了merge passes的数量
                每次Merge Pass也会产生一个中间的run在disk上,使用此参数节省的I/O可能被增加Merge Pass的次数抵消掉
                注意:当runs的数量,也就是Merge width比参数SORT_AREA_SIZE的值小的情况下,sort进程也可能需要一次读的block超过此参数的值
* Sort Area and Parameters
        Sort Area
                The sort space is a part of:
                The PGA when connected with a dedicated server                -- 专用服务器的PGA
                The shared pool or large pool when connected with MTS        -- 当连接MTS时的shared pool or large pool
        Parameters
                SORT_AREA_SIZE
                        决定sort area的大小,在init.ora中设置,可以动态修改ALTER SESSION or ALTER SYSTEM DEFERRED
                        缺省值依赖于OS,对于OLTP,一般缺省值够用,但对DSS应用/batch jobs/large operations需要增大该值
                SORT_AREA_RETAINED_SIZE
                        排序完成后,sort area中仍保留sorted row, sort area可以收缩到该参数指定的值
                        当最后一个row从sort space取出后,内存释放返回给User Global Area (UGA)给同样的Oracle Server进程使用
                        缺省值=SORT_AREA_SIZE的值
                注:         PGA = Stack Space + UGA
                        UGA = User Session Data + Cursor State + Sort Area

[/php]

easyfree

[php]
* Memory Requirements
        Single Server Process
                一个执行计划可能包含多个sort操作,如两表关联(无索引)加上order by,则有3个sort
                If a single server is working on the sort process, while it performs the ORDER BY sort it uses:
                        An area of SORT_AREA_SIZE, in bytes, for the active sort
                        Two areas of the size specified by SORT_AREA_RETAINED_SIZE, for the join sorts
        Parallel Query Processes
                若并行查询,每个查询server都需要大小相当于SORT_AREA_SIZE的内存
                With parallel query, two sets of servers can be working at once, so you should:                        ????
                        计算 SORT_AREA_SIZE * 2 * degree of parallelism                -- 并行度???
                        如果可能,增加 SORT_AREA_RETAINED_SIZE * degree of parallelism * number of sorts above two
                若内存充足,并行查询的SORT_AREA_SIZE/SORT_AREA_RETAINED_SIZE的理想值为1M,测试发现更高的值对提高性能作用不明显
        SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE通常设为相同,除非:
                内存较少
                使用 multithreaded server
* Sort Process and Temporary Space
        使用临时表空间的优点:        有效地避免了空间管理中复杂的分配/回收sort space的操作
        Temporary Space Segment
                创建:        CREATE TABLESPACE name... DATAFILE ... TEMPORARY;
                        ALTER TABLESPACE name... TEMPORARY;
                特点:        不能包含任何永久性的object
                        专门指定用于sort
                        在OPS中,每个instance包含一个独立的sort segment
        The Sort Segment
                在第一次sort操作使用临时表空间时创建
                当数据库关闭时删除drop
                需要时自动增长
                其中每个extent可能被不同的sort操作使用
                在SGA结构中称为sort extent pool (SEP),当某个进程需要sort spaces时,就在SEP中寻找free extent

[/php]