经验闲谈

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;

GL模块

Oracle EBS汇总模板建立问题处理

留下评论

当建立总账汇总模板时，报错：

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

GLSTPM module: 增加/删除汇总账户
+---------------------------------------------------------------------------+

当前的系统时间为 24-08-2020 09:41:45

+---------------------------------------------------------------------------+


>> main() 24-08-2020 09:41:45

>> glsini() 24-08-2020 09:41:45
STPM0021: 默认优化系数为 0。

<< glsini() 24-08-2020 09:41:45

>> glstexs() 24-08-2020 09:41:45

<< glstexs() 24-08-2020 09:41:45

>> glsfst() 24-08-2020 09:41:45

>> open_cursor() 24-08-2020 09:41:45

<< open_cursor() 24-08-2020 09:41:45

>> load_segnum_vsid() 24-08-2020 09:41:45

<< load_segnum_vsid() 24-08-2020 09:41:45

>> do_fetch_templates() 24-08-2020 09:41:45
SHRD0108: 从 GL_SUMMARY_TEMPLATES 中取回 1 记录

<< do_fetch_templates() 24-08-2020 09:41:45

>> close_cursor() 24-08-2020 09:41:45

<< close_cursor() 24-08-2020 09:41:45

<< glsfst() 24-08-2020 09:41:45

>> glsltmp() 24-08-2020 09:41:45

>> gludlk() 24-08-2020 09:41:45

<< gludlk() 24-08-2020 09:41:45

<< glsltmp() 24-08-2020 09:41:45

>> glstexs() 24-08-2020 09:41:45

<< glstexs() 24-08-2020 09:41:45

>> glsuab() 24-08-2020 09:41:45

<< glsuab() 24-08-2020 09:41:45

>> glstadd() 24-08-2020 09:41:45

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45
STPM0003: 已验证模板 "D-D-LEV1-D-D-D-D-D-D-D-D"，并已定义全部累计组。

>> glsdsh() 24-08-2020 09:41:45
SHRD0119: 从 GL_SUMMARY_HIERARCHIES 中删除 0 记录。
SHRD0119: 从 GL_SUMMARY_HIERARCHY_INT 中删除 0 记录。

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45

<< glsdsh() 24-08-2020 09:41:45

>> glsdah() 24-08-2020 09:41:45
SHRD0119: 从 GL_CODE_COMBINATIONS 中删除 0 记录。

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45

<< glsdah() 24-08-2020 09:41:45

>> glsash() 24-08-2020 09:41:45
SHRD0117: 把 761 记录插入 GL_SUMMARY_HIERARCHIES

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45

<< glsash() 24-08-2020 09:41:45

>> glsmah() 24-08-2020 09:41:45

>> gls_fill_seg_info() 24-08-2020 09:41:45

<< gls_fill_seg_info() 24-08-2020 09:41:45

>> gls_get_max_ccid() 24-08-2020 09:41:45

>> glucmt() 24-08-2020 09:41:45

<< glucmt() 24-08-2020 09:41:45

<< gls_get_max_ccid() 24-08-2020 09:41:45

>> glstmt() 24-08-2020 09:41:45

<< glstmt() 24-08-2020 09:41:45

>> gls_csa_rollup() 24-08-2020 09:41:45

>> glsgsmi() 24-08-2020 09:41:45
SHRD0114: 正在执行 Pro*C 语句 Insert into gl_sum_int ...

SHRD0043: ERROR: ORA-00001: 违反唯一约束条件 (GL.GL_SUMMARY_INT_2_U1)
         

<x glsgsmi() 24-08-2020 09:41:45

<x gls_csa_rollup() 24-08-2020 09:41:45
SHRD0075: 在 gls_csa_rollup() 的错误.

<x glsmah() 24-08-2020 09:41:45
SHRD0075: 在 glsmah() 的错误.

<x glstadd() 24-08-2020 09:41:45
SHRD0075: 在 glstadd_addtmp() 的错误.

问题处理：

通过脚本检查总账科目设置问题：

applprod:

@/u01/PROD/app/fs1/EBSapps/appl/gl/12.0.0/sql/glxacovl.sql

 

处理完直接的子父级后：

重新删除之前错误的模板，运行：
增加/删除汇总账户

运行完成后，重新新增模板即可。

具体sql如下：
SELECT vs.flex_value_set_name value_set_name,
fvh1.parent_flex_value ancestor,
gl_flexfields_pkg.get_parent_from_children(val.flex_value_set_id,
fvh1.parent_flex_value,
fvh1.child_flex_value_low,
fvh1.child_flex_value_high,
1) parent_flex_value,
fvh1.child_flex_value_low child_flex_value_low,
fvh1.child_flex_value_high child_flex_value_high,
gl_flexfields_pkg.get_parent_from_children(val.flex_value_set_id,
fvh1.parent_flex_value,
fvh2.child_flex_value_low,
fvh2.child_flex_value_high,
2) parent_flex_value2,
fvh2.child_flex_value_low child_flex_value_low2,
fvh2.child_flex_value_high child_flex_value_high2
FROM fnd_flex_values val,
fnd_flex_value_sets vs,
fnd_flex_value_hierarchies fvh1,
fnd_flex_value_hierarchies fvh2
WHERE val.structured_hierarchy_level IS NOT NULL
AND vs.flex_value_set_id = val.flex_value_set_id
AND fvh1.flex_value_set_id = val.flex_value_set_id
AND fvh1.parent_flex_value = val.flex_value
AND fvh2.flex_value_set_id = val.flex_value_set_id
AND fvh2.parent_flex_value = val.flex_value
AND fvh2.child_flex_value_low BETWEEN fvh1.child_flex_value_low AND fvh1.child_flex_value_high
AND (fvh2.child_flex_value_low <> fvh1.child_flex_value_low OR
fvh2.rowid < fvh1.rowid)
AND fvh2.rowid <> fvh1.rowid
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8

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

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 Database PSU/CPU

留下评论
1. 什么是PSU/CPU？

CPU: Critical Patch Update
Oracle对于其产品每个季度发行一次的安全补丁包，通常是为了修复产品中的安全隐患。

PSU: Patch Set Updates
Oracle对于其产品每个季度发行一次的补丁包，包含了bug的修复。Oracle选取被用户下载数量多的，并且被验证过具有较低风险的补丁放入到每个季度的PSU中。在每个PSU中不但包含Bug的修复而且还包含了最新的CPU。

2. 如何查找最新的PSU？
每个数据库版本都有自己的PSU，PSU版本号体现在数据库版本的最后一位，比如最新的10.2.0.5的PSU是10.2.0.5.3，而11.2.0.2的最新PSU则是11.2.0.2.2。
MOS站点中Oracle Recommended Patches — Oracle Database [ID 756671.1] 文档中查到各个产品版本最新的PSU。
如果你记不住这个文档号，那么在MOS中以“PSU”为关键字搜索，通常这个文档会显示在搜索结果的最前面。

注意：必须购买了Oracle基本服务获取了CSI号以后才有权限登陆MOS站点。

3. 如何正确安装PSU？
每个PSU安装包中都包含一个README.html文档，其中描述了如何安装该PSU，有些PSU是可以直接安装的，而有些PSU则必须要求安装了上一 个版本的PSU之后才能继续安装。比如对于10.2.0.4版本的数据库来说，PSU 10.2.0.4.4可以直接安装在最原始的10.2.0.4.0版本中，而最新的PSU 10.2.0.4.8则必须要求先安装10.2.0.4.4。这些信息在README.html中都可以找到，所以请仔细阅读该文档。

通常安装PSU是比较简单的，步骤如下：
1) 安装PSU需要使用到opatch，在README.html中有描述该PSU需要的最低版本opatch，如果当前opatch版本过低，则需要先下载 Patch 6880880，该Patch中包含最新的opatch，只需要解压覆盖原先的$ORACLE_HOME/OPatch目录即可。

查看当前的opatch版本，可以使用opatch version命令。

$ opatch version

Invoking OPatch 10.2.0.5.2

OPatch Version: 10.2.0.5.2

OPatch succeeded.

2）安装PSU，请仔细阅读README.html，确认安装命令，通常是简单的opatch apply。

$opatch apply

3）更新数据库，将修改过的SQL文件应用到数据库中，很多DBA在执行完上述安装命令以后就不再进行这一步，那么实际上PSU是没有完整安装的。

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

SQL> STARTUP

SQL> @catbundle.sql psu apply

SQL> QUIT

注意：如果PSU是overlay PSU，比如10.2.0.4.8，则需要执行@catbundle.sql opsu apply，同样这些在README.html中都有详细描述。

4）重新编译CPU相关视图。该步骤在一个数据库上永远只需要执行一次，是为了完成在2008年1月份第一次发布CPU补丁时的后续工作，如果在安 装以前的PSU或者CPU时执行过这个步骤那么就可以无需再次执行，另外，即使不执行该步骤，数据库也是正常运行的，只不过意味着2008年1月份的 CPU补丁没有正常结束安装。

cd $ORACLE_HOME/cpu/view_recompile

sqlplus / as sysdba

SQL> @recompile_precheck_jan2008cpu.sql

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP UPGRADE

SQL> @view_recompile_jan2008cpu.sql

SQL> SHUTDOWN;

SQL> STARTUP;

SQL> QUIT

注意：该步骤由于需要重新编译大量视图，因此要启动数据库到upgrade状态才可以完成。也就是将引起停机时间。

4. 如何确认当前数据库已经安装了什么PSU/CPU？
无论是从V$VERSION或者DBA_REGISTRY或者PRODUCT_COMPONENT_VERSION视图中，都无法查找到PSU的信息，这些视图中始终显示的是最原始的版本，比如10.2.0.4.0。

最常用的方法是使用opatch命令。在打完最新的PSU 10.2.0.4.8的10.2.0.4数据库中会有以下显示。

$ opatch lsinventory -bugs_fixed | grep -i ‘DATABASE PSU’

9654991 11724977 Wed May 25 16:37:17 CST 2011 DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE

9952234 11724977 Wed May 25 16:37:17 CST 2011 DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE

10248636 11724977 Wed May 25 16:37:17 CST 2011 DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE

11724977 11724977 Wed May 25 16:37:17 CST 2011 DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE

8576156 9352164 Wed May 25 15:10:48 CST 2011 DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)

8833280 9352164 Wed May 25 15:10:48 CST 2011 DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)

9119284 9352164 Wed May 25 15:10:48 CST 2011 DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)

9352164 9352164 Wed May 25 15:10:48 CST 2011 DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)

另外的方法是查看registry$history表。

SQL> select action,comments from registry$history;

ACTION                       COMMENTS

——————————-          ——————–

APPLY                          PSU 10.2.0.4.4

APPLY                          PSU 10.2.0.4.8

CPU                               view recompilation

注意：该表的内容是在上述安装PSU步骤的第三步中运行catbundle.sql才会插入的，因此如果该步骤忘记执行，则此表中无记录。因此我们在作数据库健康检查的时候不但要用opatch检查当前数据库最新的PSU补丁，也要检查registry$history表，以确认其它DBA是否正确地完成了PSU的安装。

如果多个PSU的安装都忘记了执行上述第三步，可以通过以下方法依次补作。

$ ls -l $ORACLE_HOME/psu

total 0

drwxrwxrwx 2 oracle dba 96 Oct 16 2010 10.2.0.4.4

drwxrwxrwx 2 oracle dba 96 Oct 16 2010 10.2.0.4.5

$sqlplus / as sysdba

SQL> @?/psu/10.2.0.4.4/catpsu.sql

SQL> @?/psu/10.2.0.4.5/catopsu.sql

更多关于CPU的信息，可以参看：Maclean的了解Oracle Critical Patch Update。

5. 参考文档。
Oracle Recommended Patches — Oracle Database [ID 756671.1]
Patch Set Updates for Oracle Products [ID 854428.1]
Introduction To Oracle Database catbundle.sql [ID 605795.1]
How to confirm that a Critical Patch Update (CPU) has been installed in Linux / UNIX [ID 821263.1]

查看psu信息（数据库用户）：

[oratest@testfin ~]$ $ORACLE_HOME/OPatch/opatch lsinventory

系统层级、经验闲谈

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;

技术笔记（小潘的技术记录博客）