某客户系统Oracle数据运行慢分析

原创于 2026-03-12 00:08:52 发布 · 536 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#oracle #数据库

数据库负载分析

AAS=dbtime/elapsed/cpus=294.59/60/32=0.15,说明在采样周期内数据库整体负载很小。

数据库等待事件分析

从上面列表可以看到前十的等待事件中，user i/o 等待占了21%，gc类等待占了24%。i/o 等待可以通过优化sql来降低，gc类等待最好能节点间应用分离。db file scattered read 为多块读，多与全表扫描有关。由于平均等待时间很小，因此数据库的性能不差。

内存命中率分析

从上面可以看到buffer hit、soft parse、library hit的命中率明显很低，但经过查看buffer pool advisory,share pool advisory 等建议，确定数据库的各组件内存分配是足够的。因此可以判断是由于数据库访问一些不在内存中的数据，且跑了一些新的sql,进行了硬解析。

性能sql分析

从上面可以看出，前四个sql的物理读特别高，且第一个和第四个sql都没有执行成功。这些sql 为手工执行，sql_module 均为sqlplus。查看第二个和第三个sql的执行计划如下：

Sql_id: byfu51bw4prwv

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time	Pstart	Pstop
0	SELECT STATEMENT				32 (100)
1	SORT AGGREGATE		1	7
2	VIEW	TG_CDR04	16	112	32 (0)	00:00:01
3	UNION-ALL
4	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
5	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
6	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
7	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
8	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
9	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
10	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
11	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
12	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
13	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
14	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
15	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
16	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
17	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
18	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
19	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
20	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
21	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
22	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
23	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
24	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
25	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
26	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
27	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
28	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
29	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
30	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
31	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
32	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
33	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
34	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
35	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31

SQL ID: d800u904vrj57

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time	Pstart	Pstop
0	SELECT STATEMENT				32 (100)
1	SORT AGGREGATE		1	7
2	VIEW	TG_CDR04	16	112	32 (0)	00:00:01
3	UNION-ALL
4	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
5	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
6	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
7	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
8	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
9	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
10	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
11	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
12	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
13	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
14	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
15	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
16	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
17	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
18	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
19	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
20	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
21	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
22	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
23	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
24	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
25	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
26	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
27	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
28	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
29	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
30	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
31	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
32	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
33	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31
34	PARTITION RANGE ALL		1	7	2 (0)	00:00:01	1	31
35	TABLE ACCESS FULL	TG_CDR04	1	7	2 (0)	00:00:01	1	31

可以看出，上述语句对分区表进行了全表扫描，这必定会引起大量的物理读，而导致数据库i/o升高，性能下降。

五、总结

由于数据库整体负载不高，如出现运行缓慢，多为低效sql导致。建议对相关sql进行调优。

附：低效sql 文本整理

1.sql_id: d2tv6w66b0ymh

declare v_cursor integer; v_desc_tab dbms_sql.desc_tab; v_cols integer; v_starttime varchar2(25); v_endtime varchar2(25); v_rows integer; v_index binary_integer; v_value varchar2(255); begin v_cursor := dbms_sql.open_cursor; dbms_sql.parse(v_cursor, 'select count(*) as COLUMN1 from umt_bil1.tg_cdr04 where long_type1=''3'' and partition_id=21', dbms_sql.v7); dbms_sql.describe_columns(v_cursor, v_cols, v_desc_tab); dbms_output.put('<performance time="'); dbms_output.put('$TIME'); dbms_output.put('" column_count="'); dbms_output.put(v_cols+2); dbms_output.put('" key_column="0">'); v_index := v_desc_tab.first; loop dbms_output.put(v_desc_tab(v_index).col_name); if v_index <> v_desc_tab.last then dbms_output.put_line('@'); else dbms_output.put_line(''); end if; dbms_sql.define_column(v_cursor, v_index, v_value, 255); exit when v_index = v_desc_tab.last; v_index := v_desc_tab.next(v_index); end loop; dbms_output.put_line('@STARTTIME@'); dbms_o utput.put_line('ENDTIME'); select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into v_starttime from dual; v_rows := dbms_sql.execute(v_cursor); loop if dbms_sql.fetch_rows(v_cursor) = 0 then exit; end if; select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into v_endtime from dual; for v_index in 1..v_cols loop dbms_sql.column_value(v_cursor, v_index, v_value); dbms_output.put('@'); dbms_output.put_line(v_value); end loop; dbms_output.put('@'); dbms_output.put_line(v_starttime); dbms_output.put('@'); dbms_output.put_line(v_endtime); end loop; dbms_sql.close_cursor(v_cursor); dbms_output.put_line('</performance>'); exception when others then dbms_sql.close_cursor(v_cursor); dbms_output.put_line(SQLERRM); end;

2.sql_id: byfu51bw4prwv

select count(*) as COLUMN1 from umt_bil1.tg_cdr04 where long_type1='3' and partition_id=21

3.sql_id: d800u904vrj57

select count(*) as COLUMN1 from umt_bil1.tg_cdr04 where roam_type='6' and partition_id=21

4. sql_id: ggm2fv8f15jzt

declare v_cursor integer; v_desc_tab dbms_sql.desc_tab; v_cols integer; v_starttime varchar2(25); v_endtime varchar2(25); v_rows integer; v_index binary_integer; v_value varchar2(255); begin v_cursor := dbms_sql.open_cursor; dbms_sql.parse(v_cursor, 'select count(*) as COLUMN1 from umt_bil1.tg_cdr04 where roam_type=''6'' and partition_id=21', dbms_sql.v7); dbms_sql.describe_columns(v_cursor, v_cols, v_desc_tab); dbms_output.put('<performance time="'); dbms_output.put('$TIME'); dbms_output.put('" column_count="'); dbms_output.put(v_cols+2); dbms_output.put('" key_column="0">'); v_index := v_desc_tab.first; loop dbms_output.put(v_desc_tab(v_index).col_name); if v_index <> v_desc_tab.last then dbms_output.put_line('@'); else dbms_output.put_line(''); end if; dbms_sql.define_column(v_cursor, v_index, v_value, 255); exit when v_index = v_desc_tab.last; v_index := v_desc_tab.next(v_index); end loop; dbms_output.put_line('@STARTTIME@'); dbms_ou tput.put_line('ENDTIME'); select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into v_starttime from dual; v_rows := dbms_sql.execute(v_cursor); loop if dbms_sql.fetch_rows(v_cursor) = 0 then exit; end if; select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into v_endtime from dual; for v_index in 1..v_cols loop dbms_sql.column_value(v_cursor, v_index, v_value); dbms_output.put('@'); dbms_output.put_line(v_value); end loop; dbms_output.put('@'); dbms_output.put_line(v_starttime); dbms_output.put('@'); dbms_output.put_line(v_endtime); end loop; dbms_sql.close_cursor(v_cursor); dbms_output.put_line('</performance>'); exception when others then dbms_sql.close_cursor(v_cursor); dbms_output.put_line(SQLERRM); end;