Oracle - expensive DML operation ＆性能如何优化

How to improve:

build index

if query view by view(即多张view), materialzied view

限定查询条件 - 近一个月

分析数据块大小 / 数据表数据量，及时housekeeping

本文链接：https://blog.csdn.net/Paulmin/article/details/9978851

提问如何做数据库优化
1. 硬件和操作系统的影响，内存分配，网速
1. 数据库配置优化，如共享池，数据缓冲区
2. 分析表和索引
3. SQL语句优化，使用binding 减少无必要的hard parse。减少动态SQL的使用。。。
1. index 索引安排到不同的表空间
2. partition 分区到不同的表空间 - this can reduce I/O blocking to improve throughput

create table schema1.test ( 
	msg_id number(12,0) not null enable,  
	ctry_cde varchar2(2) not null enable, 
	msg_type varchar2(20),
	capture_date date,
	constraint test_pk primary key (msg_id) using index tablespace "app01s001" --use diff space (for all index) against data space
) enable primary key using index 
  partition by hash (ctry_cde) (
	partition "n1" tablespace "app01s011",
	partition "n2" tablespace "app01s012" --data is in the 2 spaces
  )
;
--start to create index with no logging mode
--about nologging mode, it can save redo log and fast when indexing is BIG, please refer to http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes002.htm#ADMIN11719
CREATE INDEX SCHEMA1.TEST_MSG_TYPE_IDX ON SCHEMA1.TEST (MSG_TYPE) NOLOGGING TABLESPACE "APP01S001";
CREATE INDEX SCHEMA1.TEST_CAPTURE_DATE_IDX ON SCHEMA1.TEST (CAPTURE_DATE) NOLOGGING TABLESPACE "APP01S002";
alter schema1.test_msg_type_idx rebuild tablespace "APP01S002" nologging;

3. 分析ststspack 统计数据
4. Schedule housekeeping job during non-business hours:

-- Display the existing USER_JOBS
SELECT * FROM USER_JOBS; 


DECLARE
   jobno   NUMBER;
BEGIN
	
	-- DBMS_JOB to execute PL_DAILY_HOUSEKEEP
	DBMS_JOB.SUBMIT (JOB  => jobno,
		WHAT => 'BEGIN PK_APP_UTILITY.PL_DAILY_HOUSEKEEP; END;',
		NEXT_DATE => TRUNC(SYSDATE)+22/24,
		INTERVAL  => 'TRUNC(SYSDATE) + 1 + 22/24');


	COMMIT;
	  
END;
/

（提示，可以写plsql 每100条commit一次。）

This use many minutes to complete. Try to rollback , it spends 61.666 seconds.

insert, delete is expensive which will cause DB performance issue when you are doing BIG data set manipulation .
example:
insert into app_msg_log_lisa
select * from app_msg_log_02
where 1 = 1 
and log_key like '%OS%TKY%SAT%%Snd_TKY_RealtimeMessage%'
or log_key  like '%TKY%SAT%%Rec_TKY_RealtimeMessage%%'
or log_key  like '%TKY%SAT%%Rec_SSS_%%'
--and UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(inpt_msg,2000,1)) like '%3799366%'
and capture_ts > to_date('14AUG 08:00','DDMON HH24:MI');--116,707 rows inserted.

1. DB redolog increase

2. DB server memory issue
With sufficient privilege, you can run below SQLs to check memory / DB rubbish if your DML really impacts:
select * from V$MYSTAT;
select * from v$session;

--11-Nov-13 : take notes at HZ

About how to find the solution? - analysis the DDL, DML SQL
1. Check the counts of each table
2. If counts big, check the where clause - any column has no index?
3. Try to build index for those has no index
4. Check the application logic, to see if any subsequent aggreate operation. Such as sum() range table, surely cost a lot!

http://space.itpub.net/17203031/viewspace-696917