|
在报表数据库的后台alert文件中发现了这个错误简单记录一下问题的诊断和解决过程数据库版本 for Solaris sparc 错误信息如下 Errorsinfile/u/oracle/admin/repdb/bdump/repdb_j_trc: ORA:erroronautoexecuteofjob ORA:outofprocessmemorywhentryingtoallocatebytes(hash joinsubhkllcqas:kllsltba) ORA:atJSGOV_OLDINSERT_HOS_INFOline ORA:atJSGOV_OLDP_GEN_STATline ORA:atline 这个JOB是昨天才添加到数据库中的而运行这个JOB的用户是从其他数据库迁移到当前数据库中的 产生问题的情况有很多种有可能是本地配置和远端配置的区别造成的;也可能是由于源数据库是而当前数据库是版本的差异造成了执行计划改变;还有可能是迁移过程中出现了错误从而引起了问题 从错误本身观察是由于无法为HASH JOIN分配M的内存所导致的观察数据库的PGA内存设置 SQL>SHOWPARAMETERPGA NAMETYPEVALUE pga_aggregate_targetbiginteger 对于一个报表系统来说这个设置确实小了一点但是考虑这个数据库在处理很多比当前数据量大得多的情况都未出现这个问题基本上可以确定不是系统参数设置造成的 如果排除第一种情况那么无论是迁移出现了问题还是版本差异的问题最大的可能性都是执行计划发生了变化那么现在就需要找到出现问题的SQL语句检查执行计划 根据错误信息给出的存储过程名称和位置提示可以轻易的找到出现问题的SQL语句不过由于SQL语句太长而且和问题的关系并不太大这里将SQL语句省略只列出这个SQL语句对应的执行计划 SQL>SELECT*FROMTABLE(DBMS_XPLANDISPLAY); PLAN_TABLE_OUTPUT |Id|Operation|Name|Rows|Bytes|Cost| ||SELECTSTATEMENT||||| ||NESTEDLOOPS||||| ||HASHJOIN||||| ||HASHJOIN||||| ||MERGEJOINCARTESIAN||||| ||VIEW||||| ||SORTGROUPBY||||| ||VIEW||||| ||SORTGROUPBY||||| ||TABLEACCESSFULL|ORD_HIT_COMM|||| ||BUFFERSORT||||| ||VIEW||||| ||SORTGROUPBY||||| ||VIEW||||| ||SORTGROUPBY||||| ||TABLEACCESSFULL|ORD_HIT_COMM|||| ||VIEW||||| ||SORTGROUPBY||||| ||TABLEACCESSFULL|ORD_HIT_COMM|||| ||VIEW||||| ||SORTGROUPBY||||| ||NESTEDLOOPS||||| ||INLISTITERATOR||||| ||TABLEACCESSBYINDEXROWID|SW_PLAT_CAT_ORG|||| ||INDEXRANGESCAN|IDX_SW_PLAT_CAT_ORG_ENABLE|||| ||INLISTITERATOR||||| ||TABLEACCESSBYINDEXROWID|SW_PLAT_CAT_BUYER|||| ||INDEXUNIQUESCAN|PK_SW_PLAT_CAT_BUYER|||| ||INLISTITERATOR||||| ||TABLEACCESSBYINDEXROWID|PLT_PLAT|||| ||INDEXUNIQUESCAN|PK_PLT_PLAT|||| Note:cpucostingisoffPLAN_TABLEisoldversion rowsselected 虽然SQL本身写的有缺点但是绝对不应该产生这种包含笛卡儿积的执行计划检查SQL并没有发现缺少关联条件的情况即问题和SQL本身并不大虽然SQL有很多可以优化的地方但是这并不是产生笛卡儿积的关键因素 观察执行计划本身除了笛卡儿积之外另人比较疑惑的一点就是返回记录数Oracle认为全表扫描ORD_HIT_COMM仅仅返回一条记录这显然是有问题的 SQL>SELECTCOUNT(*)FROMORD_HIT_COMM; COUNT(*)
从这一点上判断可以很容易的断定是统计信息出现了问题检查ORD_HIT_COMM的统计信息 SQL>SELECTTABLE_NAMENUM_ROWSFROMUSER_TABLES WHERETABLE_NAME=ORD_HIT_COMM; TABLE_NAMENUM_ROWS ORD_HIT_COMM 本以为不存在统计信息或者得到一个很小的值没想到统计信息基本上是准确的那么是哪里出现的问题呢 观察SQL语句发现对ORD_HIT_COMM表唯一的限制条件是ENABLE_FLAG=而这个限制条件其实对过滤数据来说没有多大的意义不过检查执行计划 SQL>EXPLAINPLANFORSELECT*FROMORD_HIT_COMM WHEREENABLE_FLAG=; Explained SQL>SELECT*FROMTABLE(DBMS_XPLANDISPLAY); PLAN_TABLE_OUTPUT |Id|Operation|Name|Rows|Bytes|Cost| ||SELECTSTATEMENT||||| ||TABLEACCESSFULL|ORD_HIT_COMM|||| Note:cpucostingisoffPLAN_TABLEisoldversion rowsselected 看来问题多半出现在ENABLE_FLAG列的统计信息上 SQL>SELECTCOLUMN_NAMENUM_DISTINCTNUM_NULLSDENSITYNUM_BUCKETS FROMUSER_TAB_COLUMNS WHERETABLE_NAME=ORD_HIT_COMM ANDCOLUMN_NAME=ENABLE_FLAG; COLUMN_NAMENUM_DISTINCTNUM_NULLSDENSITYNUM_BUCKETS ENABLE_FLAGE 在i的环境Oracle根据DENSITY来确定返回的记录数因此得到条记录的结果是很正常的 SQL>SELECT*EFROMDUAL; *E
显然这时需要删除错误的统计信息并重新收集统计信息 SQL>EXECDBMS_STATSDELETE_TABLE_STATS(USERORD_HIT_COMM) PL/SQLproceduresuccessfullycompleted SQL>EXECDBMS_STATSGATHER_TABLE_STATS(USERORD_HIT_COMM) PL/SQLproceduresuccessfullycompleted 检查统计信息中的DENSITY值 SQL>SELECTCOLUMN_NAMENUM_DISTINCTNUM_NULLSDENSITYNUM_BUCKETS FROMUSER_TAB_COLUMNS WHERETABLE_NAME=ORD_HIT_COMM ANDCOLUMN_NAME=ENABLE_FLAG; COLUMN_NAMENUM_DISTINCTNUM_NULLSDENSITYNUM_BUCKETS ENABLE_FLAG 下面检查访问ORD_HIT_COMM的执行计划检查优化器认为的返回记录数 SQL>EXPLAINPLANFOR SELECT*FROMORD_HIT_COMMWHEREENABLE_FLAG=; Explained SQL>SELECT*FROMTABLE(DBMS_XPLANDISPLAY); PLAN_TABLE_OUTPUT |Id|Operation|Name|Rows|Bytes|Cost| ||SELECTSTATEMENT||K|M|| ||TABLEACCESSFULL|ORD_HIT_COMM|K|M|| Note:cpucostingisoffPLAN_TABLEisoldversion rowsselected 现在统计信息已经恢复正常检查一下出现问题的SQL语句执行计划是否正常 SQL>SELECT*FROMTABLE(DBMS_XPLANDISPLAY); PLAN_TABLE_OUTPUT |Id|Operation|Name|Rows|Bytes|TempSpc|Cost| ||SELECTSTATEMENT|||||| ||HASHJOIN|||||| ||HASHJOIN|||||| ||HASHJOIN|||||| ||MERGEJOINCARTESIAN|||||| ||VIEW|||||| ||SORTGROUPBY|||||| ||NESTEDLOOPS|||||| ||INLISTITERATOR|||||| ||TABLEACCESSBYINDEXROWID|SW_PLAT_CAT_ORG||||| ||INDEXRANGESCAN|IDX_SW_PLAT_CAT_ORG_ENABLE||||| ||INLISTITERATOR|||||| ||TABLEACCESSBYINDEXROWID|SW_PLAT_CAT_BUYER||||| ||INDEXUNIQUESCAN|PK_SW_PLAT_CAT_BUYER||||| ||BUFFERSORT|||||| ||INLISTITERATOR|||||| ||TABLEACCESSBYINDEXROWID|PLT_PLAT||||| ||INDEXRANGESCAN|PK_PLT_PLAT||||| ||VIEW|||K||| ||SORTGROUPBY|||K||| ||TABLEACCESSFULL|ORD_HIT_COMM|K|M||| ||VIEW|||K||| ||SORTGROUPBY|||K||| ||VIEW||K|M||| ||SORTGROUPBY||K|M|M|| ||TABLEACCESSFULL|ORD_HIT_COMM|K|M||| ||VIEW|||K||| ||SORTGROUPBY|||K||| ||VIEW||K|M||| ||SORTGROUPBY||K|M|M|| ||TABLEACCESSFULL|ORD_HIT_COMM|K|M||| Note:cpucostingisoffPLAN_TABLEisoldversion rowsselected 这个执行计划至少已经不那么离谱了下面只需要重新运行JOB就可以了 |
