分类目录归档:经验闲谈

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;

财务系统生产环境统计收集模式错误(从未成功)

错误描述:

描述

————————————————————————-
应用对象程序库: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

FNDGSCST module: 统计数据收集模式
————————————————————————-

当前的系统时间为 02-04-2020 00:01:15

————————————————————————-

Starts**02-04-2020 00:01:15
*Ends*02-04-2020 00:46:07
ORA-0000: normal, successful completion
————————————————————————-
FND_FILE 中日志消息开始
————————————————————————-
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 60 degree = 32 internal_flag= NOBACKUP
stats on table AQ$_WF_CONTROL_P is locked
stats on table FND_CP_GSM_IPC_AQTBL is locked
stats on table FND_SOA_JMS_IN is locked
stats on table FND_SOA_JMS_OUT is locked
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP
ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt**
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
————————————————————————-
FND_FILE 中日志消息结束
————————————————————————-
成功地重新提交具有请求编号 532437 的并发程序 FNDGSCST,以在 02-05-2020 00:01:01 启动。 (ROUTINE=AFPSRS)

————————————————————————-
正在执行请求完成选项…

Output file size:
0

已完成执行请求完成选项。

————————————————————————-
此请求发布的例外:
Concurrent Request for “统计数据收集模式” has completed with error.

————————————————————————-
已完成并发请求
当前的系统时间为 02-04-2020 00:46:08

 


 

解决方案:
参考文件:11i – 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)

具体操作:

通过以下命令查询出有2条及以上条数的记录,

select table_name, column_name, count()
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(
) > 1;

备份后删除即可。

delete from FND_HISTOGRAM_COLS
where table_name = ‘&TABLE_NAME’
and column_name = ‘&COLUMN_NAME’
and rownum=1;

— Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name =’&TABLE_NAME’
and hc.table_name= tc.table_name ()
and hc.column_name = tc.column_name (
)
and tc.column_name is null
);

commit;

特别说明:日志中只报了3个表的错误,实际上如果只处理这3个表的问题,依然不能正常完成请求,需要将所有大于等于2的条数的数据删除。