优化

最新推荐文章于 2021-04-07 11:04:32 发布

vict_

最新推荐文章于 2021-04-07 11:04:32 发布

阅读量944

点赞数

本文链接：https://blog.csdn.net/vict_/article/details/42873945

版权

生产或测试跑批完成后需要执行awr报告。

awr报告时间间隔默认为一小时，可调整，也可以手工生成snapshot，11g版本默认保留8天。

执行大概过程：
sqlplus / as sysdba
@?/rdbms/admin/awrgrpt.sql --awrRAC全局报告(生产环境执行此脚本即可 "?"的意思是指 $ORACLE_HOME路径)
@?/rdbms/admin/awrrpt.sql --单个实例报告(需要制定取哪个实例的报告)
之后交互制定输出选项，取默认输出格式 html，取默认天数，根据snapshot时间，指定开始snapshot和结束snapshot。之后取默认文件名。

AWR报告中重点需要查看sql统计信息。
Main Report -> SQL Statistics -> SQL ordered by Elapsed Time --按SQL执行时间排序

观察大表相关索引、分区情况。

-- 查询大表
select table_name,partitioned,num_rows ,logging from user_tables
where num_rows !='0' and num_rows is not null  order by num_rows desc;
--大于1000000数据无索引
select a.table_name,a.partitioned,a.num_rows,a.logging from user_tables a left outer join user_indexes b 
on  a.TABLE_NAME = b.table_name 
where a.num_rows > 1000000  
and b.table_name is null;
--涉及存储过程
select * from user_source where upper(text) like '%..%';

部分修改建议

1. 统计信息

跑批前收集用户级别统计信息。或者收集表级别的统计信息。

统计信息完善后，oracle在执行SQL时，会走更好的执行计划。

  exec dbms_stats.gather_schema_stats('SRP');
  exec dbms_stats.gather_table_stats('SRP','table_name',method_opt=>'...',estimate_percent=>'...');

2 任务切分

部分大任务切分为同级别小任务，充分利用多进程资源，并行执行。如数据导入 P04_IMP_CODETABLE_P3。

3 /**+append*/ HINT

每次数据加载前需要 truncate的表，insert(针对insert into .. select)时全部加 /*+append*/ 的HINT.

注意 /*+append*/不要拼错，不要随意加空格。部分存储过程此HINT没写"+",此时HINT失效。
没有进行truncate的表，不要加 /*+append*/，因为加上后，此后对该表的查询，可能变慢。

4 表的logging属性

建议将系统中中间表改为 nologging属性。

select 'alter table ' ||table_name||' nologging;' from tabs where TABLE_NAME like .. ;
此时对该表进行的操作写日志会大大降低，加快dml速度。
此查询结果执行完成后，将所用存储过程涉及 execute immediate 'alter table ... nologging';等语句全部去掉。
select * from user_source where lower(text) like '%execute%immediate%alter%table%';
附：确认生产库是否非归档 select log_mode from v$database ；
查询表logging属性 select table_name,logging from user_tables;

5 DDL命令

执行 truncate ，alter table 等DDL命令后，无需commit。程序中的可以去掉。

6 并行DML

程序中需要并行dml 如存储过程 P04_IMP_S03_LOAN_BNW，如需实现并行。需要在session级别开启。

执行 execute immediate 'alter session enable parallel dml';
之后执行 insert /*+parallel */ .... 等语句。

7 全局临时表

系统中，每次执行需清空，只在单个session需要用到的表，建议改为全局临时表。

P04_IMP_S03_LOAN_BNW中S03_LOAN_BNW_MID表建议改为全局临时表。

 drop table S03_LOAN_BNW_MID;
 create  global temporary table S03_LOAN_BNW_MID
(
  cust_id       VARCHAR2(32),
  cust_name     VARCHAR2(256),
  jgm           VARCHAR2(12),
  ywpz_dl       VARCHAR2(11),
  ywpz_dl_name  VARCHAR2(256),
  ywpz          VARCHAR2(11),
  ywpz_name     VARCHAR2(256),
  hbh           VARCHAR2(11),
  dkje          NUMBER(22,4),
  dkje_cny      NUMBER(22,4),
  dkje_usd      NUMBER(22,4),
  bnye          NUMBER(22,4),
  bnye_cny      NUMBER(22,4),
  bnye_usd      NUMBER(22,4),
  corp_type_cd  VARCHAR2(10),
  dbfs          VARCHAR2(11),
  trade_cd      VARCHAR2(10),
  trade_name    VARCHAR2(256),
  kmh           VARCHAR2(12),
  qdrq          VARCHAR2(10),
  zdrq          VARCHAR2(10),
  custterm      VARCHAR2(3),
  dkzffs        NUMBER,
  htbh          VARCHAR2(21),
  jjbh          VARCHAR2(17),
  five_class_cd VARCHAR2(5),
  bs            VARCHAR2(3),
  statisticdate VARCHAR2(10)
)on commit preserve rows;

create index S03_LOAN_BNW_MID_Cust_Id on S03_LOAN_BNW_MID(Cust_Id);

8 代码中可重用的部分，建立中间结果表。或合并成一条SQL执行。避免执行多次的多表关联与过滤。

P04_IMP_S03_LOAN_BNW存储过程，将针对S03_LOAN_BNW_MID表从相同源表插入的多条insert合并。

也可以考虑单独新建一张临时表。insert时，从临时表中过滤结果集，避免多次表连接与源表扫描。.
例如其中第2、3、4条SQL可以合并，具体更改参照修改后的P04_IMP_S03_LOAN_BNW.sql。
alter table S03_LOAN_BNW nologging;

9 大表的数据加载
1.并行dml
2.数据加载完成后并行重建索引
olap环境优化时，优先考虑并行。如过程 PRF_SS0_PT_TRAN_MSG_NEW_V 。
实现应用单进程，但oracle后台多个并行进程工作，效果明显。注意系统CPU使用率。
并行dml 仍需开启， execute immediate 'alter session enable parallel dml';
insert /*+parallel*/
针对数据加载时索引情况，可以考虑数据加载前，disable索引，之后重建。
execute immediate 'alter index .... unusable';
--数据加载 insert 完成后
execute immediate 'alter index .. rebuild nologging parallel 2'; --针对耗时长的，部分大表insert时可考虑，没必要每个存储过程都修改
同理 PRF_SS0_TBS_DZCDJB
PRF_SS0_FZHGLB
PRF_SS0_CRD_ZWTZ_DKMX
PRF_SS0_TRAN PRF_SS0_JJKKHSJ
PRO_TZ_FZRMX(数据量大) 等导入过程。

建议修改前后进行测试对比。

针对 PRF_SS0_PT_TRAN_MSG_NEW_V 过程进行测试，修改后执行语句：

 TRUNCATE TABLE SS0_PT_TRAN_MSG_NEW_V;
 alter index SS0_PT_TRAN_MSG_NEW_BRANCH unusable;
 alter index SS0_PT_TRAN_MSG_NEW_COM unusable;
 alter index IND_SS0_STATUS unusable;
 INSERT /*+append*/ INTO SS0_PT_TRAN_MSG_NEW_V
    (tran_date,
     branch,
     drec_branch,
     acct_branch,
     ref_no,
     tran_time_stamp,
     busi_code,
     module_id,
     source_type,
     tran_class,
     direction,
     format,
     source_module,
     branch_flag,
     source_reference,
     source_date,
     orig_ref_no,
     tran_type,
     msg_type,
     msg_prefix,
     msg_code,
     operator,
     operator_termi_mark,
     operator_time_stamp,
     auth_opr,
     auth_termi_mark,
     auth_time_stamp,
     approval_opr,
     approval_termi_mark,
     approval_time_stamp,
     reject_opr,
     reject_termi_mark,
     reject_time_stamp,
     reject_reason,
     confirm_opr,
     confirm_termi_mark,
     confirm_time_stamp,
     send_times,
     send_opr,
     send_termi_mark,
     send_time_stamp,
     last_operator,
     last_termi_mark,
     last_time_stamp,
     print_times,
     remark,
     internal_key,
     operate_type,
     auto_appr,
     auto_confirm,
     settle_type,
     msg_seq_no,
     consign_date,
     send_bank_code,
     send_settle_bank,
     rcv_bank_code,
     rcv_settle_bank,
     acct_no,
     acct_name,
     acct_type,
     oth_acct_no,
     oth_acct_name,
     oth_acct_type,
     ccy,
     tran_amt,
     value_date,
     orig_msg_code,
     orig_msg_seq_no,
     orig_consign_date,
     orig_send_bank,
     orig_send_settle,
     orig_rcv_bank,
     orig_rcv_settle,
     settle_date,
     xchg_times,
     xchg_date,
     xchg_type,
     resend_flag,
     response_code,
     response_message,
     response_method,
     response_msg_type,
     response_msg_code,
     orig_busi_code,
     orig_tran_class,
     queue_no,
     instruct_type,
     check_flag,
     send_bank_node,
     rcv_bank_node,
     msg_id,
     msg_ref_id,
     error_reason,
     pkg_no,
     collate_date,
     tran_valid_flag,
     related_ref_no,
     busi_ref_no,
     pt_msg_id,
     sendbank_sys,
     rcvbank_sys,
     busi_type,
     tran_category,
     tndays,
     dr_cr_ind,
     status,
     START_DT,
     END_DT
     )
    SELECT  to_char(TRAN_DATE , 'yyyy-mm-dd'),
           BRANCH,
           DREC_BRANCH,
           ACCT_BRANCH,
           REF_NO,
           TRAN_TIME_STAMP,
           BUSI_CODE,
           MODULE_ID,
           SOURCE_TYPE,
           TRAN_CLASS,
           DIRECTION,
           FORMAT,
           SOURCE_MODULE,
           BRANCH_FLAG,
           SOURCE_REFERENCE,
           SOURCE_DATE,
           ORIG_REF_NO,
           TRAN_TYPE,
           MSG_TYPE,
           MSG_PREFIX,
           MSG_CODE,
           OPERATOR,
           OPERATOR_TERMI_MARK,
           OPERATOR_TIME_STAMP,
           AUTH_OPR,
           AUTH_TERMI_MARK,
           AUTH_TIME_STAMP,
           APPROVAL_OPR,
           APPROVAL_TERMI_MARK,
           APPROVAL_TIME_STAMP,
           REJECT_OPR,
           REJECT_TERMI_MARK,
           REJECT_TIME_STAMP,
           REJECT_REASON,
           CONFIRM_OPR,
           CONFIRM_TERMI_MARK,
           CONFIRM_TIME_STAMP,
           SEND_TIMES,
           SEND_OPR,
           SEND_TERMI_MARK,
           SEND_TIME_STAMP,
           LAST_OPERATOR,
           LAST_TERMI_MARK,
           LAST_TIME_STAMP,
           PRINT_TIMES,
           REMARK,
           INTERNAL_KEY,
           OPERATE_TYPE,
           AUTO_APPR,
           AUTO_CONFIRM,
           SETTLE_TYPE,
           MSG_SEQ_NO,
           CONSIGN_DATE,
           SEND_BANK_CODE,
           SEND_SETTLE_BANK,
           RCV_BANK_CODE,
           RCV_SETTLE_BANK,
           ACCT_NO,
           ACCT_NAME,
           ACCT_TYPE,
           OTH_ACCT_NO,
           OTH_ACCT_NAME,
           OTH_ACCT_TYPE,
           CCY,
           TRAN_AMT,
           VALUE_DATE,
           ORIG_MSG_CODE,
           ORIG_MSG_SEQ_NO,
           ORIG_CONSIGN_DATE,
           ORIG_SEND_BANK,
           ORIG_SEND_SETTLE,
           ORIG_RCV_BANK,
           ORIG_RCV_SETTLE,
           SETTLE_DATE,
           XCHG_TIMES,
           XCHG_DATE,
           XCHG_TYPE,
           RESEND_FLAG,
           RESPONSE_CODE,
           RESPONSE_MESSAGE,
           RESPONSE_METHOD,
           RESPONSE_MSG_TYPE,
           RESPONSE_MSG_CODE,
           ORIG_BUSI_CODE,
           ORIG_TRAN_CLASS,
           QUEUE_NO,
           INSTRUCT_TYPE,
           CHECK_FLAG,
           SEND_BANK_NODE,
           RCV_BANK_NODE,
           MSG_ID,
           MSG_REF_ID,
           ERROR_REASON,
           PKG_NO,
           COLLATE_DATE,
           TRAN_VALID_FLAG,
           RELATED_REF_NO,
           BUSI_REF_NO,
           PT_MSG_ID,
           SENDBANK_SYS,
           RCVBANK_SYS,
           BUSI_TYPE,
           TRAN_CATEGORY,
           TNDAYS,
           DR_CR_IND,
           STATUS,
           START_DT,
           END_DT
      FROM JGBS.sjk_pt_tran_msg_new_v_v@TBDATA;
      
    alter index SS0_PT_TRAN_MSG_NEW_BRANCH rebuild nologging parallel 2;  -- 并行可适当去掉
    alter index SS0_PT_TRAN_MSG_NEW_COM  rebuild nologging parallel 2;
    alter index IND_SS0_STATUS  rebuild nologging;

62环境测试环境修改前耗时4分50秒，修改后耗时3分30秒，测试导入数据量6894535。系统压力不大时，可以再加上并行dml试试效果。
附: --检查索引状态与并行度 select status,degree from user_indexes where index_name = '';

10 涉及存储过程 PS0_PAY_SYS_TRANS_INFO_INDIC
-- create bitmap index ind_SS0_PT_TRAN_DIRECTION on SS0_PT_TRAN_MSG_NEW_V(DIRECTION); 往来账查询是否较多？
create index ind_SS0_status on SS0_PT_TRAN_MSG_NEW_V(status);
最后一条SQL 涉及大表关联
tz_fzrmx 表改为按交易日期分区 jyrq 涉及存错过程：select * from user_source where upper(text) like '%TZ_FZRMX%';

--改分区键具体操作见:TZ_FZRMX.sql

11 针对 P04_IMP_S03_LOAN 存储过程
增加 /*+append*/ 多条insert 无法合并，针对某一条SQL耗时过长，再调整。

12 SS0_ACCT1表数据量大是按START_DT分区的。涉及此表的存储过程很多。
start_date end_date是否涉及查询都会用。建议索引 IDX_SS0_ACCT1_SE 改成 global index 针对全局与本地索引查看建议17

13 存储过程 PBC_CAL_A1433N
12月30日修改脚本中有对 s03_loan 索引的修改？
大部分SQL类似，新建临时表，将多表关联及公共过滤后的结果保存。避免在多条SQL中多次进行表关联。
drop index TZ_CUSTINFO_REGIONALISM;
create index TZ_CUSTINFO_REGIONALISM on TZ_CUSTINFO( substr(REGIONALISM, 1, 6));
drop index IDX_TZ_CRD_CDTZ_IDX1;

14 针对大表索引及分区情况更改。
以下为部分大表
分区行数 logging
1 TZ_FZRMX_1119 NO 124533193 YES
2 TZ_SJK_KZRLSB NO 28093741 NO
3 TZ_DJK_WJFL NO 26688170 NO
4 SS0_ACCT1 YES 18156242
5 SS0_ACCT1_DJK NO 13649452 NO --基本被注释掉了
6 TZ_FZRMX YES 12334515
7 SS0_FKXFJFMX YES 11962830
8 TZ_XBHKYC NO 10951238 NO
9 TZ_DCKJTQD NO 8906466 NO
10 TZ_FFHZ YES 6773132
11 S03_AGREEMENT NO 6773117 YES
12 TZ_FFHXX NO 6773117 NO
13 SS0_TBS_DZCDJB NO 6270671 NO
14 TZ_CUX_GL_DAILY_BALANCE NO 5620643 NO

--其中大于5000000数据无索引

select a.table_name, a.partitioned, a.num_rows, a.logging
  from user_tables a
  left outer join user_indexes b
    on a.TABLE_NAME = b.table_name
 where a.num_rows > 5000000
   and b.table_name is null;

1 SS0_TBS_DZCDJB NO 6270671 NO PRF_SS0_TBS_DZCDJB PS0_ELE_PAY_SIGN_CUST_INDIC
2 SS0_FKXFJFMX YES 11962830 PRF_SS0_FKXFJFMX
3 TZ_XBHKYC NO 10951238 NO PRO_TZ_IMP_P3(PRO_TZ_XBHKYC) PRO_TZ_XBHKYC_HIS
4 TZ_DCKJTQD NO 8906466 NO P04_CAL_G01_04_3_A PRO_TZ_DCKJTQD PRO_TZ_DCKJTQD_HIS PRO_TZ_IMP_P1
5 TZ_CUX_GL_DAILY_BALANCE NO 5620643 NO P04_CAL_G01_09_13 P04_CAL_G01_09_7 P04_CAL_G01_09_9 PRO_TZ_CUX_GL_DAILY_BALANCE
PRO_TZ_CUX_GL_DAILY_BAL__HIS PRO_TZ_IMP_P3
6 S03_AGREEMENT NO 6773117 YES P04_CAL_G21_19 P04_CAL_G21_19F P04_CAL_G21_20 P04_CAL_G21_21 P04_CAL_G21_21A
P04_CAL_G21_21F P04_IMP_CODETABLE_P1 P04_IMP_DEL_CODE P04_IMP_S03_AGREEMENT
7 TZ_FZRMX_1119 NO 124533193 YES

--针对无索引大表更改
SS0_TBS_DZCDJB暂不更改

      create index  CUX_GL_DAILY_BALANCE_trandate on TZ_CUX_GL_DAILY_BALANCE(transaction_date);
      create index  CUX_GL_DAILY_BALANCE_segment3 on TZ_CUX_GL_DAILY_BALANCE(segment3);
      create bitmap index   s03_agreement_BIZ_TYPE_CD on s03_agreement(BIZ_TYPE_CD);
      create index  s03_agreement_subjectkey on s03_agreement(substr(t1.subjectkey,1,4));
      create index  s03_agreement_acct_org_num on s03_agreement(acct_org_num);

-- 注意s03_agreement表以下写法是无法走索引的。如果mature_dt字段有索引，需要注意
-- to_date(t1.mature_dt, 'yyyy-mm-dd') -TO_DATE(dtstatisticdate, 'YYYY-MM-DD')>(STARTNUM-1)

--其他一些常用大表
drop index IDX_TZ_DJK_WJFL_CW;
create index TZ_DJK_WJFL_WJFL on TZ_DJK_WJFL(WJFL) compress;

15 修改后续无需dml操作的表。更改属性 pctfree 为 0 例如：

        alter table SS0_TBS_DZCDJB pctfree 0;
	alter table SS0_FKXFJFMX pctfree 0;
	alter table TZ_XBHKYC pctfree 0;
	alter table TZ_DCKJTQD pctfree 0;
	alter table TZ_CUX_GL_DAILY_BALANCE pctfree 0;
	alter table TZ_SJK_KZRLSB pctfree 0;

注意：经常需要更新，或者前台需要大并发操作的表，pctfree不要随意更改。

16 针对SF6301 S6301的表T41_SYSTEMCAL_UPDAY索引有更改。在12月30日的修改中说明。生产未应用。

17 索引的使用。
针对表经常使用的谓词列添加索引。针对常用的多个查询条件，建立组合索引要比创建多个独立索引效率更高。

创建组合索引时，列顺序需要格外注意。将区分性更好的列，最常使用的查询列放在第一位。

创建组合索引时，可以将查询列，也放在索引中，避免回表。

某列不同值有限，如往来账，借贷记等。可以考虑建bitmap 索引。 create bitmap index ... on ()....; 这种列建普通索引效果不好。

在dml的过程中，索引也会维护，索引经常需要维护的表，索引数目不易过多。

全局索引与本地索引

针对分区表的查询，查询条件最好加上分区列，否则会扫描所有分区，效率低下，可能不如不分区。
全局索引适用于针对全表的查询，而非单独针对某个分区的查询。
所以一些日期列，需要经常查询几个月的数据的SQL. 该日期列最好是全局索引，而非本地索引。

18 针对 P04_D06_ALL P04_IMP_S03_LOAN_BNW 修改
P04_IMP_S03_LOAN_BNW.sql

19 -- P04_D06_ALL存储过程 62环境测试结果);

SRPTEST>exec P04_C02_CUST_CRDT_BAD('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.97


SRPTEST>exec P04_C01_CRDT_AGT_BAD('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.61


SRPTEST>exec P04_D06_CUST_CRDT_LIMIT63('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.79


SRPTEST>exec P04_D06_CUST_CRDT_LIMIT3302('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.96


SRPTEST>exec P04_D06_CORP_CRDT_SMALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.68


SRPTEST>exec P04_D06_CORP_CRDT_MICRO('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.63


SRPTEST>exec P04_D06_CORP_CRDT_MS('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.02


SRPTEST>exec P04_D06_CORP_CRDT_LARGE('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62


SRPTEST>exec P04_D06_CORP_CRDT_MEDIUM('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.65


SRPTEST>exec P04_D06_INDI_OPER_LN('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.38


SRPTEST>exec P04_D06_S6302('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.43


SRPTEST>exec P04_D06_S6402('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.53


SRPTEST>exec P04_D06_CUST_CRDT_LMT63_ALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.64


SRPTEST>exec P04_D06_CUST_CRDT_LMT3302_ALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.00


SRPTEST>exec P04_D06_CORP_CRDT_SMALL_ALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.82


SRPTEST>exec P04_D06_CORP_CRDT_MICRO_ALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.08


SRPTEST>exec P04_D06_CORP_CRDT_MS_ALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:22:01.61


SRPTEST>exec P04_D06_CORP_CRDT_MEDIUM_ALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.53


SRPTEST>exec P04_D06_INDI_OPER_LN_ALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.86


SRPTEST>exec P04_D06_S6301('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.08


SRPTEST>exec P04_D06_CORP_CRDT_BILL_LARGE('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.02


SRPTEST>exec P04_D06_CORP_CRDT_BILL_MEDIUM('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.25


SRPTEST>exec P04_D06_CORP_CRDT_BILL_SMALL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.32


SRPTEST>exec P04_D06_CORP_CRDT_BILL_MICRO('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.84


SRPTEST>exec P04_D06_CORP_CRDT_BILL_MS('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 08:10:43.52


SRPTEST>exec P04_D06_INDI_OPER_LN_BILL('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:01:25.25


SRPTEST>exec P04_D06_LN_DISTR('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.65


SRPTEST>exec P04_D06_S6401('2014-12-31');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.23

--其中存储过程 P04_D06_CORP_CRDT_MS_ALL P04_D06_CORP_CRDT_BILL_MS 耗时较长

-- 这两个存储过程加并行 dml

    create index ind_CRDT_MICRO_ALL_com on D06_CORP_CRDT_MICRO_ALL(CUST_ID，BELONG_BANK_CD);
    create index ind_CRDT_SMALL_ALL_com on D06_CORP_CRDT_SMALL_ALL(CUST_ID，BELONG_BANK_CD);
    create index ind_CRDT_LMT63_ALL_com  on D06_CUST_CRDT_LMT63_ALL(CUST_ID，BELONG_BANK_CD);
    create index ind__CRDT_LMT3302_ALLL_com  on D06_CUST_CRDT_LMT3302_ALL(CUST_ID，BELONG_BANK_CD);
    create index ind_CRDT_BILL_SMALL_com on D06_CORP_CRDT_BILL_SMALL T1(CUST_ID，BELONG_BANK_CD);
    create index ind_CRDT_BILL_MICRO_com on D06_CORP_CRDT_BILL_MICRO T1(CUST_ID，BELONG_BANK_CD);

    alter table D06_CORP_CRDT_3410 nologging;
    alter table D06_CORP_CRDT_BILL_LARGE nologging;
    alter table D06_CORP_CRDT_BILL_MEDIUM nologging;
    alter table D06_CORP_CRDT_BILL_MICRO nologging;
    alter table D06_CORP_CRDT_BILL_MS nologging;
    alter table D06_CORP_CRDT_BILL_SMALL nologging;
    alter table D06_CORP_CRDT_LARGE nologging;
    alter table D06_CORP_CRDT_LARGE_ALL nologging;
    alter table D06_CORP_CRDT_MEDIUM nologging;
    alter table D06_CORP_CRDT_MEDIUM_ALL nologging;
    alter table D06_CORP_CRDT_MICRO nologging;
    alter table D06_CORP_CRDT_MICRO_ALL nologging;
    alter table D06_CORP_CRDT_MS nologging;
    alter table D06_CORP_CRDT_MS_ALL nologging;
    alter table D06_CORP_CRDT_SMALL nologging;
    alter table D06_CORP_CRDT_SMALL_ALL nologging;

vict_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
优化

生产或测试跑批完成后需要执行awr报告。awr报告时间间隔默认为一小时，可调整，也可以手工生成snapshot，11g版本默认保留8天。执行大概过程：sqlplus / as sysdba@?/rdbms/admin/awrgrpt.sql --awrRAC全局报告(生产环境执行此脚本即可 "?"的意思是指 $ORACLE_HOME路径)@?/rdbms/admin/aw
复制链接

扫一扫