Oracle 21c 新特性 自动索引优化

2022 年 4 月 21 日 by Leave a Comment



    • 索引的 ADO 策略扩展了现有的自动数据优化 (ADO) 功能，以提供对索引的压缩和优化功能。 Oracle 数据库的客户有兴趣利用压缩分层和存储分层来满足他们的信息生命周期管理 (ILM) 要求。现有的 ADO 功能使您能够设置策略，自动对数据表和分区实施压缩分层和存储分层，而用户干预最少。

    在数据库中，索引也会消耗大量的数据库空间。在不牺牲性能的情况下减少索引的空间需求需要 ILM 操作，类似于现有的数据段自动数据优化功能。使用这种新的索引压缩和优化功能，相同的 ADO 基础架构也可以自动优化索引。与用于数据段的 ADO 类似，这种自动索引压缩和优化功能通过使您能够设置通过压缩、收缩和重建索引等操作自动优化索引的策略来实现索引的 ILM。

    自动索引优化介绍

    此页面提供有关索引的自动数据优化策略的更多详细信息，扩展了表的现有 ADO 功能以提供对索引的段移动、压缩和优化功能。

    幻灯片显示了如何设置热图和自动数据优化 (ADO) 之间的不同步骤，以根据 ADO 策略中定义的某些条件自动将段移动到另一个表空间和/或压缩块或段。

    Oracle Database 21c 允许对索引使用 ADO 策略，扩展现有的表自动数据优化 (ADO) 功能，以提供对索引的段移动、压缩和优化功能。优化过程包括压缩、收缩或重建索引等操作。当指定了 OPTIMIZE 子句时，Oracle 会自动确定哪个操作对索引是最佳的，并将该操作作为优化过程的一部分实施。您不必指定要执行的操作。

    1. DBA 的第一个操作是启用热图，跟踪块和段上的活动。热图激活系统生成的统计信息收集，例如段访问或修改。

    2. 实时统计数据收集在内存中（V HEAT_MAP_SEGMENT 视图），并由计划的 DBMS_SCHEDULER 作业定期刷新到持久 HEAT_MAP_STAT$ 表。使用 DBA_HEAT_MAP_SEG_HISTOGRAM 视图可以看到持久数据。

    3. DBA 的下一个操作是在索引上创建 ADO 策略作为表空间上的默认 ADO 行为。

    4. DBA 的下一步是在默认调度与业务需求不匹配时调度必须进行 ADO 策略评估的时间。 ADO 策略评估依赖于热图统计。 MMON 定期评估行级策略并启动作业以压缩符合条件的块。段级策略仅在维护窗口期间评估和执行。

    5. DBA 可以通过 DBA_ILMEVALUATIONDETAILS 和 DBA_ILMRESULTS 视图查看 ADO 执行结果。

    6. 最后，DBA 可以通过查看 COMPRESSION_STAT$ 表来验证该段是否已移动到另一个表空间并因此存储在 ADO 策略中定义的表空间中，或者索引的块是否被压缩。

    实践：为索引实施存储分层 ADO 策略

    概述 此实践展示了如何根据自动数据优化策略中定义的某些条件自动将索引移动到另一个表空间。

    第一步：搭建测试环境 执行清理任何现有 ADO 策略的 shell 脚本，创建两个表空间以将索引从 ADOTBSINDX 表空间移动到 LOW_COST_STORE_INDX 表空间，并创建具有主键 PK_EMPLOYEE_ID 的 HR.EMP 表，其索引存储在 ADOTBSINDX 中。 它还开始收集热图统计信息。

    $ cd /home/oracle/labs/M104783GC10
    $ /home/oracle/labs/M104783GC10/ADO_setup.sh
    Copyright (c) 1982, 2019, Oracle. All rights reserved.
    ​
    Connected to:
    ​
    SQL> set feedback off
    SQL> delete ilm_results$;
    SQL> delete ilm_execution$;
    SQL> delete ilm_executiondetails$;
    SQL> DROP TABLESPACE adotbsindx INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE adotbsindx INCLUDING CONTENTS AND DATAFILES
    *
    ERROR at line 1:
    ORA-00959: tablespace 'ADOTBSINDX' does not exist
    ...
    SQL> INSERT INTO hr.emp
    2     SELECT employee_id*7, first_name,last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
    3         FROM hr.emp;
    ​
    214 rows created.
    ​
    SQL> COMMIT;
    ​
    Commit complete.
    ​
    SQL> exit

     

    Step 2 : 显示表空间中表索引使用和释放的空间 显示存储 HR.EMP 表的主键索引的表空间以及段使用了多少空间。

    $ sqlplus system@PDB21
    ​
    Copyright (c) 1982, 2019, Oracle. All rights reserved.
    ​
    Enter password:
    ​
    Connected to:
    ​
    SQL> COL tablespace_name FORMAT A20
    SQL> COL index_name FORMAT A20
    SQL> COL owner FORMAT A10
    SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP';
    ​
    TABLESPACE_NAME     INDEX_NAME           OWNER
    -------------------- -------------------- ----------
    ADOTBSINDX           PK_EMPLOYEE_ID       HR
    ​
    SQL>
    SQL> SELECT bytes FROM dba_segments WHERE segment_name='PK_EMPLOYEE_ID';
    ​
    BYTES
    ----------
    65536
    ​
    SQL>

     

    显示存储 HR.EMP 表的主键索引的表空间中已使用和空闲的空间。

    SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace",
    df.bytes / (1024 * 1024) "Size (MB)",
    SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
    Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
    Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,df.bytes
    ORDER BY 4;
    ​
    Tablespace                     Size (MB) Free (MB)     % Free     % Used
    ------------------------------ ---------- ---------- ---------- ----------
    SYSTEM                               310     11.625         4         96
    SYSAUX                               450   25.4375         6         94
    USERS                             671.25   37.8125         6         94
    TBS_FOR_ADO                             4     1.3125         33         67
    ADOTBSINDX                             2     .9375         47         53
    UNDOTBS1                             250     210.75         84         16
    LOW_COST_STORE_INDX                   100         99         99         1
    ​
    7 rows selected.

     

    第 3 步：在索引上创建存储分层 ADO 策略 在索引上创建存储分层 ADO 策略，以便当 ADOTBSINDX 表空间中的空白空间百分比小于 90% 时，正在评估的 ILM 策略触发 ADO 操作以将索引移动到 LOW_COST_STORE_INDX 表空间。

    SQL> ALTER INDEX hr.pk_employee_id ILM ADD POLICY TIER TO low_cost_store_indx;
    ​
    Index altered.

     

    在数据字典视图中显示策略。

    SQL> CONNECT hr@PDB21
    Enter password:
    Connected.
    SQL> SELECT policy_name, action_type, scope,
    tier_tablespace "TIER_TBS"
    FROM   user_ilmdatamovementpolicies
    ORDER BY policy_name;

    POLI ACTION_TYPE SCOPE   TIER_TBS
    ---- ----------- ------- --------------------
    P2   STORAGE     SEGMENT LOW_COST_STORE_INDX

     

    第 4 步：测试存储分层 ADO 策略 将行插入 HR.EMP 直到插入的索引条目将 ADOTBSINDX 表空间中的空白空间百分比提高到小于 90%。

    SQL> INSERT INTO hr.emp
    SELECT employee_id*101, first_name,last_name, email,
    phone_number, hire_date, job_id, salary, commission_pct,
    manager_id, department_id
    FROM hr.emp;
    ​
    428 rows created.
    ​
    SQL> INSERT INTO hr.emp
    SELECT employee_id+436926 , first_name,last_name, email,
    phone_number, hire_date, job_id, salary, commission_pct,
    manager_id, department_id
    FROM hr.emp;
    ​
    856 rows created.
    ​
    SQL> COMMIT;
    ​
    Commit complete.
    ​
    SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace",
    df.bytes / (1024 * 1024) "Size (MB)",
    SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
    Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
    Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,df.bytes
    ORDER BY 4;
    ​
    Tablespace                     Size (MB) Free (MB)     % Free     % Used
    ------------------------------ ---------- ---------- ---------- ----------
    SYSTEM                               310     11.625         4         96
    SYSAUX                               450   25.4375         6         94
    USERS                             671.25   37.8125         6         94
    TBS_FOR_ADO                             4       1.25         31         69
    ADOTBSINDX                             2       .875         44         56
    UNDOTBS1                             250     210.75         84         16
    LOW_COST_STORE_INDX                   100         99         99         1
    ​
    7 rows selected.

     

    插入的索引条目将 ADOTBSINDX 表空间中的空白空间百分比提高到低于 90%。

    显示现在存储 HR.EMP 表的主键索引的表空间。 索引是否移动到 LOW_COST_STORE_INDX 表空间？

    SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP';
    ​
    TABLESPACE_NAME     INDEX_NAME           OWNER
    -------------------- -------------------- ----------
    ADOTBSINDX           PK_EMPLOYEE_ID       HR

     

    尽管 ADOTBSINDX 表空间中的空白空间百分比低于 90%，但索引尚未移动到其他表空间。

    移动段的 ADO 决定还取决于在数据库级别为所有用户定义的表空间定义的默认阈值。

    将 TBS_PERCENT_FREE 阈值设置为 90% ，将 TBS_PERCENT_USED 阈值设置为 30% 。

    SQL> CONNECT sys@PDB21 AS SYSDBA
    Enter password:
    Connected.
    SQL> COL name FORMAT A40
    SQL> SELECT * FROM dba_ilmparameters;
    ​
    NAME                                         VALUE
    ---------------------------------------- ----------
    ENABLED                                           1
    RETENTION TIME                                   30
    JOB LIMIT                                         2
    EXECUTION MODE                                   2
    EXECUTION INTERVAL                               15
    TBS PERCENT USED                                 85
    TBS PERCENT FREE                                 25
    POLICY TIME                                       0
    ​
    8 rows selected.
    ​
    SQL> EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,90)
    ​
    PL/SQL procedure successfully completed.
    ​
    SQL> EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_USED,30)
    ​
    PL/SQL procedure successfully completed.
    ​
    SQL> SELECT * FROM dba_ilmparameters;
    ​
    NAME                                         VALUE
    ---------------------------------------- ----------
    ENABLED                                           1
    RETENTION TIME                                   30
    JOB LIMIT                                         2
    EXECUTION MODE                                   2
    EXECUTION INTERVAL                               15
    TBS PERCENT USED                                 30
    TBS PERCENT FREE                                 90
    POLICY TIME                                       0
    ​
    8 rows selected.

    此外，指定应使用秒（而不是天）来快速测试 ADO 策略评估，而不是等待策略持续时间。

    SQL> EXEC dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_SECONDS)
    ​
    PL/SQL procedure successfully completed.
    ​
    SQL> SELECT * FROM dba_ilmparameters;
    ​
    NAME                                         VALUE
    ---------------------------------------- ----------
    ENABLED                                           1
    RETENTION TIME                                   30
    JOB LIMIT                                         2
    EXECUTION MODE                                   2
    EXECUTION INTERVAL                               15
    TBS PERCENT USED                                 30
    TBS PERCENT FREE                                 90
    POLICY TIME                                       1
    ​
    8 rows selected.

     

    出于练习的目的，您不会等待维护窗口打开来触发 ADO 策略作业。 相反，您将执行以下命令和 PL/SQL 块，以 ADO 策略所有者 HR 的身份连接。

    SQL> CONNECT hr@PDB21
    Enter password:
    Connected.
    SQL> ALTER SESSION SET nls_date_format='dd-mon-yy hh:mi:ss';
    ​
    Session altered.
    ​
    SQL> DECLARE
    v_executionid number;
    BEGIN
    dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
    execution_mode => dbms_ilm.ilm_execution_offline,
    task_id   => v_executionid);
    END;
    /
    ​
    PL/SQL procedure successfully completed.

     

    再次检查索引是否已移至 LOW_COST_STORE_INDX 表空间。

    SQL> COL object_type FORMAT A10
    SQL> COL object_name FORMAT A14
    SQL> COL selected_for_execution FORMAT A28
    SQL> COL job_name FORMAT A9
    SQL> SELECT OBJECT_TYPE, OBJECT_NAME, SELECTED_FOR_EXECUTION, JOB_NAME
    FROM   user_ilmevaluationdetails;
    ​
    OBJECT_TYP OBJECT_NAME   SELECTED_FOR_EXECUTION       JOB_NAME
    ---------- -------------- ---------------------------- ---------
    INDEX     PK_EMPLOYEE_ID SELECTED FOR EXECUTION       ILMJOB100
    ​
    SQL> SELECT task_id, job_name, job_state FROM user_ilmresults;
    ​
    TASK_ID JOB_NAME   JOB_STATE
    ------- ---------- -----------------------------------
    1 ILMJOB100 COMPLETED SUCCESSFULLY

     

    显示现在存储 HR.EMP 表的主键索引的表空间。 它是否已移至 LOW_COST_STORE_INDX 表空间？

    SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP';
    ​
    TABLESPACE_NAME     INDEX_NAME           OWNER
    -------------------- -------------------- ----------
    LOW_COST_STORE_INDX PK_EMPLOYEE_ID       HR

    索引已移动到另一个表空间。

     

    第 5 步：删除 ADO 策略 删除索引上的 ADO 策略。

    SQL> CONNECT system@PDB21
    Enter password:
    Connected.
    SQL> ALTER INDEX hr.pk_employee_id ILM DELETE POLICY p2;
    ​
    Index altered.

     

    停止热图统计数据收集并清理所有热图统计数据。

    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> ALTER SYSTEM SET heat_map=off SCOPE=BOTH;
    ​
    System altered.
    ​
    SQL> EXEC dbms_ilm_admin.clear_heat_map_all
    ​
    PL/SQL procedure successfully completed.
    ​
    SQL> EXIT
    Filed Under: Database, Oracle

    Speak Your Mind

    *

    京ICP备14059771号-2