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

easyfree

[php]
* Tuning Sort Operations
        Diagnosing Problems
                如果数据曾经sort,避免重新进行sort操作
                当排序操作不太大,sort area过小的结果是导致增加与disk进行交换的性能开销,要保障排序操作发生在内存中
                使用大量内存进行sorting可能导致paging and swapping,降低整个系统的性能                ????
                经常性地在permanent tablespace上发生临时段的分配和回收
        Tuning Goals
                尽可能地避免不必要的排序操作
                        Using NOSORT to create indexes        -- 在单CPU机器上使用SQL*Loader时, 如果数据已经按升序预排序
                                -- 在一个多CPU机器,尽管没有按次序load,并行load数据也可能快些,故可使用parallel index creation来加速sort


                        Using UNION ALL instead of UNION        -- UNION ALL不必去除重复项,所以也不必排序
                        Using index access for table joins        -- Nested Loop Joins  等值连接使用索引
                                示例:
                                        select dept_id, id
                                        from s_emp, s_dept
                                        where s_emp.dept_id = s_dept.id;
                                -- Oracle优化器选择nested loop join代替sort-merge join,前者不请求sort操作
                                1. Perform a full table scan of the S_EMP table.
                                2. 对每个DEPT_ID,在索引PK_ID上寻找唯一匹配值,返回ROWID
                                3. 使用ROWID从S_DEPT表中定位匹配的row
                                4. 对于S_EMP的每一行,将按上述方式匹配的S_DEPT中的row合并
                        Creating indexes on columns referenced in the ORDER BY clause
                                -- 对于经常order by的列建立索引
                        Selecting the columns for analysis
                                ANALYZE... FOR COLUMNS
                                ANALYZE...FOR ALL INDEXED COLUMNS
                                ANALYZE... SIZE n        -- 对指定的列创建一个柱状图(表示数据分布)
                        Using ESTIMATE rather than COMPUTE for large objects
                                ANALYZE ESTIMATE        -- 对于大表或大cluster,使用ESTIMATE子句,因为COMPUTE尽管精确但需要大量的sort空间
                优化内存sort和disk overhead
                避免对临时段的空间分配和回收
        Diagnostic Tools for Tuning Sort Operations
                V$SYSSTAT        -- 显示所有sorts (memory),sorts (disk),sorts (rows)的数量(since instance startup)
                        - sorts (disk): Number of sorts requiring I/O to temporary segments
                        - sorts (memory): Number of sorts performed entirely in memory
                        - sorts (rows): Total rows sorted in the period being monitored
                        select * from v$sysstat where name like '%sorts%';
                        STATISTIC# NAME                  CLASS      VALUE
                        ---------- ---------------- ---------- ----------
                               180 sorts (memory)           64     989221
                               181 sorts (disk)             64         16
                               182 sorts (rows)             64      78077
                report.txt中提供了和V$SYSSTAT类似的信息
                        Statistic         Total         Per Transact  Per Logon    Per Second
                        -------------- --------- ------------- ------------ -----------
                        sorts (disk)               4        .02              .41           .01
                        sorts (memory)              154        .27         5.77           .12
                        sorts (rows)           571768      39.62       862.59         18.19
                V$SORT_SEGMENT and V$SORT_USAGE                显示临时段的使用和使用的user
                OEM --> Performance Manager—>Database_Instance—>System Statistics
                        Performance Manager—>Load—>Sort Rows Rate

[/php]

easyfree

[php]
* Diagnostics and Guidelines
        Ratio                -- The ratio of disk sorts to memory sorts should be less than 5%.
                select disk.value "Disk", mem.value "Mem",
                (disk.value/mem.value)*100 "Ratio"
                from v$sysstat mem, v$sysstat disk
                where mem.name = 'sorts (memory)'
                and disk.name = 'sorts (disk)';

                      Disk        Mem      Ratio                -- 应小于5%,否则应增加SORT_AREA_SIZE.
                ---------- ---------- ----------
                        16     989589 .001616833

                Performance Manager—>Memory—>Memory Sort Hit%
        Guidelines
                增加SORT_AREA_SIZE,将增加每个run的size并减少runs和merge的总数量
        Performance Trade-Offs for Large Sort Areas
                增大SORT_AREA_SIZE会使每个server process获取更多的内存,可能影响OS的内存
                如果增大SORT_AREA_SIZE,可以考虑降低SORT_AREA_RETAINED_SIZE,但减少内存使用的同时会增加从disk读写临时段的I/O
* Monitoring Temporary Tablespaces
        V$SORT_SEGMENT        -- 包含instance中临时表空间中每个sort segment的信息
                TABLESPACE_NAME       VARCHAR2(31)
                SEGMENT_FILE          NUMBER
                SEGMENT_BLOCK         NUMBER
                EXTENT_SIZE           NUMBER
                CURRENT_USERS         NUMBER        当前在此sort segment上活动user的数量                -- 重点
                        -- 一个sort一个user,如果一个sql有3个sort,则有3个user,在V$SORT_USAGE中看到都是'SYSTEM'
                TOTAL_EXTENTS         NUMBER        exents的总数量
                TOTAL_BLOCKS          NUMBER
                USED_EXTENTS          NUMBER        当前正在使用的extents
                USED_BLOCKS           NUMBER
                FREE_EXTENTS          NUMBER
                FREE_BLOCKS           NUMBER
                ADDED_EXTENTS         NUMBER
                EXTENT_HITS           NUMBER        unused extent was found in the pool的次数
                FREED_EXTENTS         NUMBER
                FREE_REQUESTS         NUMBER
                MAX_SIZE              NUMBER
                MAX_BLOCKS            NUMBER        used blocks的最大数量
                MAX_USED_SIZE         NUMBER
                MAX_USED_BLOCKS       NUMBER
                MAX_SORT_SIZE         NUMBER        单个sort的最大使用block数量
                MAX_SORT_BLOCKS       NUMBER
                RELATIVE_FNO          NUMBER
        V$SORT_USAGE
                USER                  VARCHAR2(30)        请求临时空间的用户        -- 测试发现显示查询此视图的用户???
                SESSION_ADDR          RAW(4)
                SESSION_NUM           NUMBER                执行sql的session的serial number
                SQLADDR               RAW(4)                shared sql游标的address
                SQLHASH               NUMBER
                TABLESPACE            VARCHAR2(31)
                CONTENTS              VARCHAR2(9)        TEMPORARY/PERMANENT
                SEGTYPE               VARCHAR2(9)
                SEGFILE#              NUMBER
                SEGBLK#               NUMBER
                EXTENTS               NUMBER                该sort获得的extents数
                BLOCKS                NUMBER
                SEGRFNO#              NUMBER
        临时表空间的storage参数适用于sort segments,但它的extents不受MAXEXTENTS的限制,为unlimited

[/php]

easyfree

[php]
* Configuring Temporary Tablespaces
        Set appropriate storage values.
                INITIAL和NEXT值设置为SORT_AREA_SIZE的整数倍,增加一个block留给segment header使用
                PCTINCREASE设置为0
        Set up different TEMPORARY tablespaces based on sorting needs.
                为不同的用户指定不同的临时表空间
                从V$SORT_USAGE和V$SESSION中找出哪个user经常使用较大的临时段,为其单独建立一个临时表空间并设置较大的extent size

                        SELECT s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
                        FROM v$session s,v$sort_usage u
                        WHERE s.saddr=u.session_addr AND u.contents='TEMPORARY';

                        USERNAME USER   TABLESPACE CONTENTS  EXTENTS BLOCKS
                        -------- -----  ---------- --------- ------- ------
                        STAT     SYSTEM TEMP       TEMPORARY      20   1000
                        SCOTT    SYSTEM TEMP       TEMPORARY       2    100
        Striping        将临时表空间分布在多个disk上,以提高I/O能力,可以显著提高sort性能
-- finish---
[/php]