ANBOB™

一套oracle 11gR2数据库环境， SQL监控工具在采集SQL性能指标时，报错 ORA-00600: internal error code, arguments: [733], [1258291280], [top call heap], 记录一下该问题。

报告信息

Dump continued from file: /opt/app/oracle/diag/rdbms/stdtadb/TADB/trace/TADB_ora_3257.trc
ORA-00600: internal error code, arguments: [733], [1258291280], [top call heap], [], [], [], [], [], [], [], [], []


SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
                       /* EXEC_FROM_DBMS_XPLAN */ id, position, depth , operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost, cpu_cost, time, partition_start, partition_stop, object_node, other_tag, distribution, null, access_predicates, filter_predicates, other, null, null, other_xml, sql_profile, sql_plan_baseline, null, null, null, null, null, null, null,
                        null, null, null, null, null, null, null, null, null from (select /*+ no_merge(vp) */ 
                          vp.id id, vp.depth depth, vp.position position, 
                          vp.operation operation, vp.options options,
                          vp.cost cost, vp.time time, 
                          vp.cardinality cardinality, vp.bytes bytes, 
                          vp.object_node object_node,
                          vp.object_name object_name, vp.other_tag other_tag, 
                          vp.partition_start partition_start,
                          vp.partition_stop partition_stop, 
                          vp.distribution distribution, 
                          vp.temp_space temp_space, vp.io_cost io_cost,
                          vp.cpu_cost cpu_cost, 
                          vp.filter_predicates filter_predicates, 
                          vp.access_predicates access_predicates, 
                          vp.other other, vp.projection projection, 
                          vp.qblock_name qblock_name, 
                          vp.object_alias object_alias, 
                          vp.other_xml other_xml, 
                          v$sql.sql_profile sql_profile, 
                          v$sql.sql_plan_baseline sql_plan_baseline, 0 starts, 0 outrows, 0 crgets, 0 cugets,
                                     0 reads,
                                     0 writes, 0 etime,
                                     0 mem_opt,
                                     0 mem_one,
                                     null last_mem_used,
                                     null last_mem_usage,
                                     0 opt_cnt,
                                     0 one_cnt,
                                     0 multi_cnt,
                                     0 max_tmp,
                                     0 last_tmp
               from V$SQL_PLAN vp, v$sql where vp.SQL_ID = '5d222pxun5gnb' and vp.child_number=7 and vp.SQL_ID = v$sql.SQL_ID and v$sql.is_obsolete = 'N' and v$sql.address = vp.address and v$sql.child_number=7) order by id

ORA-600 [733]

Format: ORA-600 [733] [a] [b]

VERSIONS:
versions 7.0 to 12.1

DESCRIPTION:

When allocating an extent in the PGA, if the minimum size requested is greater than what is allowed for this port, ORA-600 [733] is reported.

This is a Program Global Area (PGA) heap/memory allocation error.

ARGUMENTS:
Arg [a] The minimum extent size requested, in bytes.
Arg [b] A comment supplied by the caller.

FUNCTIONALITY:
MEMORY IMPLEMENATION

IMPACT:
PROCESS FAILURE
NON CORRUPTIVE – No corruption to underlying data.

SUGGESTIONS:

If receiving this during a query with complex JOIN, for example, consider lowering sort_area_size or hash_area_size (if hash_join_enabled = true)
and rerun the query. This will reduce the PGA memory requirements and may clear the problem.

Known Issue

诊断内存使用

 alter system set events '600 {errarg:arg1=600}  heapdump (13)';
 select * from table(dbms_xplan.display_cursor('xxxxx', NULL, 'ALL'));

检查投影和谓词

 select ACCESS_PREDICATES from v$sql_plan where plan_hash_value = xxx;
 select FILTER_PREDICATES from v$sql_plan where plan_hash_value = xxx;
 select PROJECTION        from v$sql_plan where plan_hash_value = xxx;
 select lengthb(ACCESS_PREDICATES) from v$sql_plan where plan_hash_value = xxx;
 select lengthb(FILTER_PREDICATES) from v$sql_plan where plan_hash_value = xxx;
 select lengthb(PROJECTION)        from v$sql_plan where plan_hash_value = xxx;

select count(FILTER_PREDICATES),PLAN_HASH_VALUE, sql_id from V$sql_plan  
where sql_id=xxx
group  by PLAN_HASH_VALUE,sql_id order by 1 desc; 

该问题可能发生在大的谓词或投影列，超过了某限制。仅发生在12.1之前,  改变SQL 执行计划或列个数可能会绕过该问题。已知Bug 19846328 : ORA-00600 [733] [TOP CALL HEAP] WHILE SELECTING FROM V$SQL_PLAN

打赏

微信扫一扫，打赏作者吧～