分类目录归档：经验闲谈

经验闲谈

Oracle设置Shared Pool的大小

留下评论

Shared Pool的大小设置规则如下：

1.查到shared pool设置的合理值，语句如下：

select ‘Shared Pool’ component,

shared_pool_size_for_estimate estd_sp_size,

estd_lc_time_saved_factor parse_time_factor,

case

when current_parse_time_elapsed_s + adjustment_s < 0 THEN

0

ELSE

current_parse_time_elapsed_s + adjustment_s

END response_time

FROM (select shared_pool_size_for_estimate,

shared_pool_size_factor,

estd_lc_time_saved_factor,

a.estd_lc_time_saved,

e.VALUE / 100current_parse_time_elapsed_s,

c.estd_lc_time_saved – a.estd_lc_time_saved adjustment_s

from v$shared_pool_advice a,

(select * from v$sysstat where NAME = ‘parse time elapsed’) e,

(select estd_lc_time_saved

from v$shared_pool_advice

where shared_pool_size_factor = 1) c);

2.设置上个SQL语句中查到的PARSE_TIME_FACTOR首次等于1的记录所在行的ESTD_SP_SIZE的值为shared pool。

3.设置语句如下：

alter system set shared_pool_size=XXX scope=spfile;或者alter system set shared_pool_size=XXX scope=both;

经验闲谈

ORACLE优化查询资源消耗的语句

留下评论

1、SQL ordered by Gets
select *
from (select substr(sql_text, 1, 40) sql,
buffer_gets,
executions,
buffer_gets / executions “Gets/Exec”,
hash_value,
address
from v$sqlarea
where buffer_gets > 0
and executions > 0
order by buffer_gets desc)
where rownum <= 10;

2、SQL ordered by Reads
select *
from (select substr(sql_text, 1, 40) sql,
disk_reads,
executions,
disk_reads / executions “Reads/Exec”,
hash_value,
address
from v$sqlarea
where disk_reads > 0
and executions > 0
order by disk_reads desc)
where rownum <= 10;

3、SQL ordered by Executions
select *
from (select substr(sql_text, 1, 40) sql,
executions,
rows_processed,
rows_processed / executions “Rows/Exec”,
hash_value,
address
from v$sqlarea
where executions > 0
order by executions desc)
where rownum <= 10;

4、SQL ordered by Parse Calls
select *
from (select substr(sql_text, 1, 40) sql,
parse_calls,
executions,
hash_value,
address
from v$sqlarea
where parse_calls > 0
order by parse_calls desc)
where rownum <= 10;

5、Running Time top 10 sql
select *
from (select t.sql_fulltext,
(t.last_active_time –
to_date(t.first_load_time, ‘yyyy – mm – dd hh24 :mi :ss‘)) * 24 * 60,
disk_reads,
buffer_gets,
rows_processed,
t.last_active_time,
t.last_load_time,
t.first_load_time
from v$sqlarea t
order by t.first_load_time desc)
where rownum < 10;

原创程序、系统层级、经验闲谈

Oracle sysaux表空间占用问题处理

留下评论

现象：

oracle sysaux表空间占用96%。通过awrinfo.sql报告核实组件：SM/AWR占用27,317.2 MB；SM/OPTSTAT 390.1 MB，大部分是AWR报告占用。

解决方法：

删除快照，回收表及index。

1、修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除

select dbms_stats.get_stats_history_retention from dual;

exec dbms_stats.alter_stats_history_retention(7);

select dbms_stats.get_stats_history_retention from dual;

2、删除AWR快照，最后再次查看SYSAUX表空间使用率（非业务时间，分段操作，以免引起临时空间增大）

select min(snap_id),max(snap_id) from dba_hist_snapshot;//查询最最小和最大快照ID

begin

dbms_workload_repository.drop_snapshot_range( low_snap_id => 32155, high_snap_id => 32300, dbid => 2950336419);

end;

3、回收降低水位

alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__2950336419_2060;
alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__2950336419_2060;

系统层级、经验闲谈

Oracle ebs R12.2.3 升级数据库后过账变慢（11.0.2.3.0-11.0.2.4.0）

留下评论

背景：
oracle ebs R12.2.3对应库版本11.0.2.3.0，升级数据库版本为：11.0.2.4.0

现象：升级完成后，突然发现过账很缓慢，单个2行的日记账过账7分钟，批量过账同样耗时严重。

方案：metalink 2195917.1

主要变更内容（变更后，一般过账1-2秒完成）：

配置文件：初始化 SQL 语句 – 自定义

在地点层修改为：

begin fnd_ctl.fnd_sess_ctl(”,”,”,”,”,’ALTER SESSION SET”_FIX_CONTROL”=”13704562:OFF”‘);end;