开启辅助访问

Bo's Oracle Station

Bo's Oracle Station › 博客首页 ›Oracle技术› SQL Tuning › 查看内容

【博客文章2022】SPA结合SPM进行SQL调优

2022-4-9 15:03| 发布者: admin| 查看: 3134| 评论: 0|原作者: Bo Tang

摘要: SPA结合SPM进行SQL调优。
【博客文章2022】SPA结合SPM进行SQL调优

Author: Bo Tang

1. 准备工作负载和SQL调优集：

    将用SPA先后两次真实地连接12c数据库来测试同一份Oracle EBS套件工作负载。在第2次测试时，我们改变"APPS"."FACT_PD_OUT_ITM_293"的DDL并行度为80和parallel_degree_policy为auto 。

1.1 向12c数据库（库名：orcl）导入APPS用户方案（脱敏的EBS套件的APPS用户方案供下载：https://www.botangdb.com/myupload/202204/ebs_workload.dmp）：

[oracle@station76 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@station76 ~]$ mkdir dir1

事先下载好上面的ebs_workload.dmp

[oracle@station76 ~]$ mv ebs_workload.dmp  dir1
[oracle@station76 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 1 19:29:29 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>  create directory dir1 as '/home/oracle/dir1';

Directory created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@station76 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=ebs_workload.dmp

Import: Release 12.1.0.2.0 - Production on Sat Apr 1 19:36:25 2022

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dir1 dumpfile=ebs_workload.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "APPS"."FACT_PD_OUT_ITM_293"                175.0 MB 1501663 rows
. . imported "APPS"."ADM_PG_FEATUREVALUE"                14.26 MB  172392 rows
. . imported "APPS"."LU_ELEMENTGROUP_REL"                3.477 MB   84468 rows
. . imported "APPS"."LU_OUTLET_293"                      3.398 MB   22289 rows
. . imported "APPS"."LU_ITEM_293"                        1.274 MB    5355 rows
. . imported "APPS"."LU_ELEMENTRANGE_REL"                767.1 KB    7957 rows
. . imported "APPS"."LU_ELEMENTRANGE_REL_Q2"             767.1 KB    7957 rows
. . imported "APPS"."ADM_CC_FEATUREVALUE"                248.8 KB    3047 rows
. . imported "APPS"."STS_JFV_TAB"                        48.75 KB       7 rows
. . imported "APPS"."LU_PG_FEATUREVALUE_15_Q2"           122.9 KB    2505 rows
. . imported "APPS"."STS_JFV_TAB_CPLANS"                 85.02 KB     132 rows
. . imported "APPS"."STS_JFV_TAB_CBINDS"                 9.476 KB       0 rows
. . imported "APPS"."LU_PERIOD_293"                      8.031 KB      31 rows
. . imported "APPS"."PLAN_TABLE"                             0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 1 19:37:57 2022 elapsed 0 00:01:27
 
    查看导入的APPS用户方案包含的对象（EBS套件最有代表性的业务用户就是APPS）：

SQL>  select object_name, object_type, status from dba_objects where owner='APPS' order by object_type;
-----------------------------------------------------------------------------------------------------------------------------------------------
SYS_IL0000106068C00006$$    INDEX    VALID
LU_ITEM_274_PG_IDX    INDEX    VALID
LU_ITEM_274_ITM_IDX    INDEX    VALID
LU_OUTLET_274_PER_IDX    INDEX    VALID
LU_OUTLET_274_OUTL_IDX    INDEX    VALID
LU_OUTLET_274_PROJECT_IDX    INDEX    VALID
LU_OUTLET_274_COUNTRY_IDX    INDEX    VALID
LU_OUTLET_274_CC_IDX    INDEX    VALID
FACT_274_PER_IDX    INDEX    VALID
FACT_274_ITEM_IDX    INDEX    VALID
FACT_274_COUNTRY_IDX    INDEX    VALID
FACT_274_CHANNEL_IDX    INDEX    VALID
FACT_274_CC_IDX    INDEX    VALID
STS_JFV_TAB_CPLANS_IDX    INDEX    VALID
STS_JFV_TAB_CBINDS_IDX    INDEX    VALID
ADM_PG_FEATUREVALUE_PK    INDEX    VALID
ADM_PG_FEATUREVALUE_IDX2    INDEX    VALID
ADM_PG_FEATUREVALUE_IDX1    INDEX    VALID
ADM_CC_FEATUREVALUE_IDX1    INDEX    VALID
ADM_CC_FEATUREVALUE_PK    INDEX    VALID
SYS_IL0000106069C00014$$    INDEX    VALID
SYS_C0017534    INDEX    VALID
SYS_IL0000106068C00036$$    INDEX    VALID
SYS_IL0000106068C00035$$    INDEX    VALID
SYS_IL0000106068C00029$$    INDEX    VALID
LU_ITEM_274_FV01_IDX    INDEX    VALID
SYS_IL0000106072C00038$$    INDEX    VALID
SYS_C0017535    INDEX    VALID
SYS_LOB0000106072C00038$$    LOB    VALID
SYS_LOB0000106068C00035$$    LOB    VALID
SYS_LOB0000106069C00014$$    LOB    VALID
SYS_LOB0000106068C00006$$    LOB    VALID
SYS_LOB0000106068C00029$$    LOB    VALID
SYS_LOB0000106068C00036$$    LOB    VALID
LU_PERIOD_293    TABLE    VALID
LU_OUTLET_293    TABLE    VALID
LU_ITEM_293    TABLE    VALID
LU_ELEMENTRANGE_REL    TABLE    VALID
LU_ELEMENTGROUP_REL    TABLE    VALID
FACT_PD_OUT_ITM_293    TABLE    VALID
ADM_PG_FEATUREVALUE    TABLE    VALID
PLAN_TABLE    TABLE    VALID
LU_PG_FEATUREVALUE_15_Q2    TABLE    VALID
ADM_CC_FEATUREVALUE    TABLE    VALID
STS_JFV_TAB    TABLE    VALID
STS_JFV_TAB_CBINDS    TABLE    VALID
STS_JFV_TAB_CPLANS    TABLE    VALID
LU_ELEMENTRANGE_REL_Q2    TABLE    VALID
LU_CH_FEATUREVALUE_01    VIEW    VALID
LU_CH_FEATUREVALUE_02    VIEW    VALID
LU_CH_FEATUREVALUE_03    VIEW    VALID
LU_CH_FEATUREVALUE_04    VIEW    VALID
LU_CH_FEATUREVALUE_05    VIEW    VALID
LU_CH_FEATUREVALUE_06    VIEW    VALID
LU_CH_FEATUREVALUE_07    VIEW    VALID
LU_CH_FEATUREVALUE_08    VIEW    VALID
LU_CH_FEATUREVALUE_09    VIEW    VALID
LU_CH_FEATUREVALUE_10    VIEW    VALID
LU_CH_FEATUREVALUE_11    VIEW    VALID
LU_CH_FEATUREVALUE_12    VIEW    VALID
LU_CH_FEATUREVALUE_13    VIEW    VALID
LU_CH_FEATUREVALUE_14    VIEW    VALID
LU_CH_FEATUREVALUE_15    VIEW    VALID
LU_CH_FEATUREVALUE_16    VIEW    VALID
LU_CH_FEATUREVALUE_17    VIEW    VALID
LU_CH_FEATUREVALUE_18    VIEW    VALID
LU_CH_FEATUREVALUE_19    VIEW    VALID
LU_CH_FEATUREVALUE_20    VIEW    VALID
LU_CH_FEATUREVALUE_21    VIEW    VALID
LU_CH_FEATUREVALUE_22    VIEW    VALID
LU_CH_FEATUREVALUE_23    VIEW    VALID
LU_CH_FEATUREVALUE_24    VIEW    VALID
LU_CH_FEATUREVALUE_25    VIEW    VALID
LU_CH_FEATUREVALUE_26    VIEW    VALID
LU_CH_FEATUREVALUE_27    VIEW    VALID
LU_CH_FEATUREVALUE_28    VIEW    VALID
LU_CH_FEATUREVALUE_29    VIEW    VALID
LU_CH_FEATUREVALUE_30    VIEW    VALID
LU_CH_FEATUREVALUE_31    VIEW    VALID
LU_CH_FEATUREVALUE_32    VIEW    VALID
LU_CH_FEATUREVALUE_33    VIEW    VALID
LU_CH_FEATUREVALUE_34    VIEW    VALID
LU_CH_FEATUREVALUE_35    VIEW    VALID
LU_CH_FEATUREVALUE_36    VIEW    VALID
LU_CH_FEATUREVALUE_37    VIEW    VALID
LU_CH_FEATUREVALUE_38    VIEW    VALID
LU_CH_FEATUREVALUE_39    VIEW    VALID
LU_CH_FEATUREVALUE_40    VIEW    VALID
LU_CH_FEATUREVALUE_41    VIEW    VALID
LU_CH_FEATUREVALUE_42    VIEW    VALID
LU_CH_FEATUREVALUE_43    VIEW    VALID
LU_CH_FEATUREVALUE_44    VIEW    VALID
LU_CH_FEATUREVALUE_45    VIEW    VALID
LU_CH_FEATUREVALUE_46    VIEW    VALID
LU_CH_FEATUREVALUE_47    VIEW    VALID
LU_CH_FEATUREVALUE_48    VIEW    VALID
LU_CH_FEATUREVALUE_49    VIEW    VALID
LU_CH_FEATUREVALUE_50    VIEW    VALID
LU_PG_FEATUREVALUE_01    VIEW    VALID
LU_PG_FEATUREVALUE_02    VIEW    VALID
LU_PG_FEATUREVALUE_03    VIEW    VALID
LU_PG_FEATUREVALUE_04    VIEW    VALID
LU_PG_FEATUREVALUE_05    VIEW    VALID
LU_PG_FEATUREVALUE_06    VIEW    VALID
LU_PG_FEATUREVALUE_07    VIEW    VALID
LU_PG_FEATUREVALUE_08    VIEW    VALID
LU_PG_FEATUREVALUE_09    VIEW    VALID
LU_PG_FEATUREVALUE_10    VIEW    VALID
LU_PG_FEATUREVALUE_11    VIEW    VALID
LU_PG_FEATUREVALUE_12    VIEW    VALID
LU_PG_FEATUREVALUE_13    VIEW    VALID
LU_PG_FEATUREVALUE_14    VIEW    VALID
LU_PG_FEATUREVALUE_15    VIEW    VALID
LU_PG_FEATUREVALUE_16    VIEW    VALID
LU_PG_FEATUREVALUE_17    VIEW    VALID
LU_PG_FEATUREVALUE_18    VIEW    VALID
LU_PG_FEATUREVALUE_19    VIEW    VALID
LU_PG_FEATUREVALUE_20    VIEW    VALID
LU_PG_FEATUREVALUE_21    VIEW    VALID
LU_PG_FEATUREVALUE_22    VIEW    VALID
LU_PG_FEATUREVALUE_23    VIEW    VALID
LU_PG_FEATUREVALUE_24    VIEW    VALID
LU_PG_FEATUREVALUE_25    VIEW    VALID
LU_PG_FEATUREVALUE_26    VIEW    VALID
LU_PG_FEATUREVALUE_27    VIEW    VALID
LU_PG_FEATUREVALUE_28    VIEW    VALID
LU_PG_FEATUREVALUE_29    VIEW    VALID
LU_PG_FEATUREVALUE_30    VIEW    VALID
LU_PG_FEATUREVALUE_31    VIEW    VALID
LU_PG_FEATUREVALUE_32    VIEW    VALID
LU_PG_FEATUREVALUE_33    VIEW    VALID
LU_PG_FEATUREVALUE_34    VIEW    VALID
LU_PG_FEATUREVALUE_35    VIEW    VALID
LU_PG_FEATUREVALUE_36    VIEW    VALID
LU_PG_FEATUREVALUE_37    VIEW    VALID
LU_PG_FEATUREVALUE_38    VIEW    VALID
LU_PG_FEATUREVALUE_39    VIEW    VALID
LU_PG_FEATUREVALUE_40    VIEW    VALID
LU_PG_FEATUREVALUE_41    VIEW    VALID
LU_PG_FEATUREVALUE_42    VIEW    VALID
LU_PG_FEATUREVALUE_43    VIEW    VALID
LU_PG_FEATUREVALUE_44    VIEW    VALID
LU_PG_FEATUREVALUE_45    VIEW    VALID
LU_PG_FEATUREVALUE_46    VIEW    VALID
LU_PG_FEATUREVALUE_47    VIEW    VALID
LU_PG_FEATUREVALUE_48    VIEW    VALID
LU_PG_FEATUREVALUE_49    VIEW    VALID
LU_PG_FEATUREVALUE_50    VIEW    VALID
 
1.2 针对于以上方案的工作负载脚本：
    脱敏的EBS套件示例工作负载脚本1供下载：https://www.botangdb.com/myupload/202204/ebs_workload1.sh

[oracle@station90 ~]$ wc -l ebs_workload1.sh
2172 ebs_workload1.sh
[oracle@station90 ~]$ head -n 40 ebs_workload1.sh
#!/bin/sh

export ORACLE_SID=rcat

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=/u01/app/oracle/product/12.1.0/dbhome_1/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin

sqlplus / as sysdba <
-- as sysdba flush the shared pool
ALTER system flush buffer_cache;
ALTER system flush shared_pool;


-- connect as dwh_test
CONNECT apps/apps;
set echo on;
spool /tmp/ebs_workload1.log;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY:MON-DD:HH24:MI:SS';
SET LINES 80;
set wrap on
SET PAGES 0;
rem SET TRIMS ON;
SET HEADING OFF;
SELECT '@Start', sysdate from dual;
rem SET TERMOUT ON;
rem set ECHO OFF;
SET ECHO ON;
rem SET FEEDBACK OFF;
rem alter session set tracefile_identifier='noprof';
rem alter session set sql_trace=true;
alter system set cursor_sharing=exact;
set autotrace traceonly statistics;

PROMPT @Statement 1 
execute dbms_application_info.set_module('DWH_TEST','1');

SET TIMING ON;
SELECT DISTINCT
......
 
     脱敏的EBS套件示例工作负载脚本2供下载：https://www.botangdb.com/myupload/202204/ebs_workload2.sh

[oracle@station90 ~]$ wc -l ebs_workload2.sh
5962 ebs_workload2.sh
[oracle@station90 ~]$ head -n 40 ebs_workload2.sh
#!/bin/ksh

export ORACLE_SID=rcat

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=/u01/app/oracle/product/12.1.0/dbhome_1/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin

sqlplus sys/oracle_4U as sysdba <
-- as sysdba flush the shared pool
ALTER system flush buffer_cache;
ALTER system flush shared_pool;


-- connect as dwh_test
CONNECT apps/apps;
set echo on;
spool /tmp/ebs_workload2.log;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY:MON-DD:HH24:MI:SS';
SET LINES 80;
set wrap on
SET PAGES 0;
rem SET TRIMS ON;
SET HEADING OFF;
SELECT '@Start', sysdate from dual;
rem SET TERMOUT ON;
rem set ECHO OFF;
SET ECHO ON;
rem SET FEEDBACK OFF;
rem alter session set tracefile_identifier='noprof';
rem alter session set sql_trace=true;
alter system set cursor_sharing=exact;
set autotrace traceonly statistics;

PROMPT @Statement 1 
execute dbms_application_info.set_module('DWH_TEST','1');

SET TIMING ON;
SELECT DISTINCT
......
 
     脱敏的EBS套件示例工作负载脚本3供下载：https://www.botangdb.com/myupload/202204/ebs_workload3.sh

[oracle@station90 ~]$ wc -l ebs_workload3.sh
2395 ebs_workload3.sh
[oracle@station90 ~]$ head -n 40 ebs_workload3.sh
#!/bin/ksh

export ORACLE_SID=rcat

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=/u01/app/oracle/product/12.1.0/dbhome_1/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin

sqlplus sys/oracle as sysdba <
-- as sysdba flush the shared pool
ALTER system flush buffer_cache;
ALTER system flush shared_pool;


-- connect as dwh_test
CONNECT apps/apps;
set echo on;
spool /tmp/ebs_workload3.log;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY:MON-DD:HH24:MI:SS';
SET LINES 80;
set wrap on
SET PAGES 0;
rem SET TRIMS ON;
SET HEADING OFF;
SELECT '@Start', sysdate from dual;
rem SET TERMOUT ON;
rem set ECHO OFF;
SET ECHO ON;
rem SET FEEDBACK OFF;
rem alter session set tracefile_identifier='noprof';
rem alter session set sql_trace=true;
alter system set cursor_sharing=exact;
set autotrace traceonly statistics;

PROMPT @Statement 1 
execute dbms_application_info.set_module('DWH_TEST','1');

SET TIMING ON;
SELECT DISTINCT
......
 
1.3 同时运行上述3个工作负载脚本，并同时在Cloud Control12c上收集以上3个工作负载：
    打开3个终端窗口，分别运行ebs_workload1.sh：
   
[oracle@station90 ~]$ ./ebs_workload1.sh

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 10 07:30:29 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> SQL> SQL>
System altered.

SQL>
System altered.

SQL> SQL> SQL> SQL> Connected.
SQL> SQL> SQL>
Session altered.

SQL> SQL> SQL> SQL> SQL> SQL> @Start 2022:APR-10:07:30:31

SQL> SQL> SQL> SQL> SQL> SQL> SQL>
System altered.

SQL> SQL> SQL> @Statement 1
SQL>
PL/SQL procedure successfully completed.

SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14 
Elapsed: 00:00:00.04

Statistics
----------------------------------------------------------
    161  recursive calls
      0  db block gets
    300  consistent gets
    145  physical reads
    204  redo size
    558  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     13  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> @1 Results in 15 Ticks
SQL> SQL> SQL> SQL> @Statement 2
SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14 
Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
      8  recursive calls
      0  db block gets
    118  consistent gets
     16  physical reads
      0  redo size
    559  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
......
  
    运行ebs_workload2.sh：

[oracle@station90 ~]$ ./ebs_workload2.sh

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 10 07:30:33 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> SQL> SQL>
System altered.

SQL>
System altered.

SQL> SQL> SQL> SQL> Connected.
SQL> SQL> SQL>
Session altered.

SQL> SQL> SQL> SQL> SQL> SQL> @Start 2022:APR-10:07:30:33

SQL> SQL> SQL> SQL> SQL> SQL> SQL>
System altered.

SQL> SQL> SQL> @Statement 1
SQL>
PL/SQL procedure successfully completed.

SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14 
Elapsed: 00:00:00.03

Statistics
----------------------------------------------------------
    161  recursive calls
      0  db block gets
    299  consistent gets
     21  physical reads
    124  redo size
    558  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     13  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> @1 Results in 15 Ticks
SQL> SQL> SQL> SQL> @Statement 2
SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14 
Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
      8  recursive calls
      0  db block gets
    119  consistent gets
      0  physical reads
      0  redo size
    559  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> @1 Results in 0 Ticks
SQL> SQL> SQL> SQL> @Statement 3
SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14 
Elapsed: 00:00:00.01
......
 
    运行ebs_workload3.sh：

[oracle@station90 ~]$ ./ebs_workload3.sh

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 10 07:30:36 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> SQL> SQL>
System altered.

SQL>
System altered.

SQL> SQL> SQL> SQL> Connected.
SQL> SQL> SQL>
Session altered.

SQL> SQL> SQL> SQL> SQL> SQL> @Start 2022:APR-10:07:30:37

SQL> SQL> SQL> SQL> SQL> SQL> SQL>
System altered.

SQL> SQL> SQL> @Statement 1
SQL>
PL/SQL procedure successfully completed.

SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14 
Elapsed: 00:00:00.03

Statistics
----------------------------------------------------------
    161  recursive calls
      0  db block gets
    299  consistent gets
     20  physical reads
    124  redo size
    558  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     13  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> @1 Results in 15 Ticks
SQL> SQL> SQL> SQL> @Statement 2
SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14 
Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
      8  recursive calls
      0  db block gets
    119  consistent gets
      0  physical reads
     80  redo size
    559  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> @1 Results in 0 Ticks
SQL> SQL> SQL> SQL> @Statement 3
SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14 
Elapsed: 00:00:00.01
......
 
    同时在Cloud Control12c上收集以上3个工作负载的SQL调优集，进入Cloud Control 12c，在“Performance”选项卡里找到“SQL”再找到“SQL Tuning Sets”：


     填写SQL Tuning Sets的名字, 采用每隔5秒增量收集30分钟的方式：



    过滤条件为：

    a. APPS用户

    b. sql文本包含事实表FACT_PD_OUT_ITM_293

    c. ebs套件中的DWH_TEST模块（见工作负载3个脚本中的：execute dbms_application_info.set_module('DWH_TEST','1'); 这些标记模块的语句）：  



    开始收集,    刚开始只有7条语句被收集,     最后收集到59条SQL语句：



2. 进行SPA测试：

   SQL性能分析器（简称SPA）：
    概要：
    Oracle Database 11g引入了SQL性能分析器，目标用户：DBA、QA、应用程序开发人员。使用该工具可以准确地评估更改对组成工作量的SQL语句的影响。这种功能可向DBA提供有关SQL语句性能的详细信息。例如，执行前后的统计信息，提高或降低性能的语句。它通过建立不同版本的SQL工作量性能（即SQL执行计划和执行统计信息），以串行方式执行SQL（不考虑并发性）来分析性能差异，提供对单个SQL的细粒度性能分析。
    使用情形：
SQL 性能分析器可用于预测和防止会影响SQL执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：
    1. 数据库升级
    2. 实施优化建议
    3. 更改方案
    4. 收集统计信息
    5. 更改数据库参数
    6. 更改操作系统和硬件
    操作步骤：
    1. 收集SQL：在这个阶段中，将收集用于表示生产系统中的SQL工作量的SQL语句集。可以使用SQL优化集或自动工作量资料档案库 (AWR) 来捕获要传送的信息。因为AWR本质上是捕获高负载的SQL，所以应考虑修改默认的AWR快照设置和捕获的顶级SQL，以确保AWR捕获最大数量的SQL语句。这可以确保捕获更加完整的SQL工作量。
    2. 传送：在这个阶段中，应将得到的工作量结果传送到测试系统。从生产系统导出STS，然后将 STS 导入到测试系统。（如果在同一套环境中测试，可以跳过此步骤）
    3. 计算“之前版本”性能：在进行任何更改之前，执行SQL语句，收集评估将来的更改对工作量性能的可能影响所需的基线信息。在此阶段收集的信息给出了系统工作量当前状态的一个快照。性能数据包括：
        -执行计划（如由解释计划生成的计划）
        -执行统计信息（如由占用时间、缓冲获取次数、磁盘读取次数和已处理的行数组成的信息）
    4. 进行更改：获得了之前版本数据后，可以实施计划的更改，然后开始查看对性能的影响。
    5. 计算“之后版本”性能：在数据库环境中进行了更改之后才执行此步骤。SQL工作量的每个语句都在虚拟执行（仅收集统计信息）模式下运行，收集与步骤3所捕获的信息相同的信息。
    6. 比较和分析SQL性能：在获得了两个版本的SQL工作量性能数据后，可以通过比较之后版本与之前版本的数据来进行性能分析。比较的根据是执行统计信息，如所用时间、CPU时间和缓冲区获取次数等。
    7. 优化回归的SQL：在此阶段中，已经准确地确认了哪些SQL语句在进行数据库更改时可能导致性能问题（回归SQL）。在此阶段中可以使用任何一种数据库工具来优化系统。例如，可以对确认的语句使用SQL优化指导或访问指导，然后实施相应的建议。也可以使用在步骤 3 中捕获的计划植入 SQL 计划管理 (SPM) 以确保计划保持不变。在实施了任何优化操作后，应重复该过程来创建新的之后版本，然后分析性能差异以确保新的性能是可接受的。
默认情况下SPA若涉及到DML语句则只有查询部分会被执行，但是需要加入参数EXECUTE_FULLDML可以执行DML，但是该参数目前有一些BUG:
Bug 10428438 : WITH EXECUTE_FULLDML ROWS IS ALWAYS SET TO 0 11.2.0.1
Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3
执行方法如下：
execute DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name   => 'TASK_21137', -
                                               parameter   => 'EXECUTE_FULLDML', -
                                               value       => 'TRUE');
当将EXECUTE_FULLDML设置为TRUE的时候, SQL Performance Analyzer将会在DML操作后做回滚以防止数据被这些DML操作永久改动。SPA是不会真的改动表中的数据的。
    在CLoud Control12c图形界面上找到Advisor Central界面上的SQL Performance Analyze Home：


    选择最通用的Guided Workload：


    进入Step1，


    定位到刚才收集的SQL Tuning Set：


    进入关键的Step2，进行首轮运行：


    对事实表和自动并行度策略做修改：

SQL> select table_name, degree from dba_tables where owner='APPS' and table_name='FACT_PD_OUT_ITM_293';
--------------------------------------------------------------------------------------------------
FACT_PD_OUT_ITM_293            1

SQL> alter table APPS.fact_pd_out_itm_293 parallel 80;
Table altered.

SQL> show parameter parallel_degree_policy；
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      MANUAL

SQL> alter system set parallel_degree_policy=auto;
System Altered.

    进入第2轮测试：


    进入两轮执行结果比较过程：


    查看报告：


    报告页面上出现两种调优手段：Create SQL Plan Baselines和Run SQL Tuning Advisor。前者的调优思路是面对回归的SQL语句直接固定并接受第1轮中好的执行计划，放弃第2轮中的执行计划，这种手段非常直接有效。后者是建议添加SQL Profile来对新环境所有的语句进行调整。可以多轮迭代进行SPA，并分别接受以上两种调优手段。首先进入SPM界面（为以下这4条回归的SQL语句Create SQL Plan Baseline）：


    实际上SPM通过手工添加的方式（注意以下界面上的：MANUAL-LOAD）创建了4条SQL基线：


    接着我们来按“Run SQL Tuning Advisor”按钮：


    等待SQL Tuning Advisor运行结束：



    接受所有的SQL Profile：



    最后选择去进化刚才的SQL基线：










路过
雷人
握手
鲜花
鸡蛋
收藏 邀请
上一篇：【活动演讲2017】SECOUG中国东南大企业行ORACLE技术分享首站：福建龙岩移动2017.06.03下一篇：【博客文章2021】SQL基线常用操作

QQ|手机版|Bo's Oracle Station   

GMT+8, 2022-6-2 16:29 , Processed in 0.025331 second(s), 21 queries .

返回顶部