AWR Snapshot Collection Failed Due To Slow Insert Into WRH$_ACTIVE_SESSION_HISTORY

(Doc ID 2375265.1)

Last updated on APRIL 13, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

ORA-12751 was due the SQL,6ubbsr2wustzc, over 600 seconds limit. 
********************************************************************************
SQL ID: 6ubbsr2wustzc Plan Hash: 1248717572

INSERT /*+ APPEND LEADING(@"SEL$F5BB74E1" "H"@"SEL$2" "A"@"SEL$1")
USE_NL(@"SEL$F5BB74E1" "A"@"SEL$1") */ INTO
WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_number, sample_id,
sample_time , session_id, session_serial#, session_type , flags , user_id ,
sql_id, sql_child_number, sql_opcode, force_matching_signature ,
top_level_sql_id, top_level_sql_opcode , sql_plan_hash_value,
sql_plan_line_id , sql_plan_operation#, sql_plan_options# , sql_exec_id,
sql_exec_start , plsql_entry_object_id, plsql_entry_subprogram_id ,
plsql_object_id, plsql_subprogram_id , qc_instance_id, qc_session_id,
qc_session_serial#, px_flags , event_id, seq#, p1, p2, p3 , wait_time,
time_waited , blocking_session, blocking_session_serial#, blocking_inst_id ,
current_obj#, current_file#, current_block#, current_row# ,
top_level_call#, consumer_group_id, xid, remote_instance#, time_model ,
service_hash, program, module, action, client_id, machine, port, ecid ,
dbreplay_file_id, dbreplay_call_counter , con_dbid , tm_delta_time,
tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests,
delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes,
delta_interconnect_io_bytes, pga_allocated, temp_space_allocated ,
dbop_name, dbop_exec_id ) (SELECT /*+ PARAM('_module_action_old_length',0)
*/:snap_id, :dbid, :instance_number, a.sample_id, a.sample_time ,
a.session_id, a.session_serial#, a.session_type , decode(a.flags, 0,
to_number(NULL), a.flags) , a.user_id , a.sql_id, a.sql_child_number,
a.sql_opcode, a.force_matching_signature , a.top_level_sql_id,
a.top_level_sql_opcode , a.sql_plan_hash_value, a.sql_plan_line_id ,
a.sql_plan_operation#, a.sql_plan_options# , a.sql_exec_id,
a.sql_exec_start , a.plsql_entry_object_id, a.plsql_entry_subprogram_id ,
a.plsql_object_id, a.plsql_subprogram_id , a.qc_instance_id,
a.qc_session_id, a.qc_session_serial#, a.px_flags , a.event_id, a.seq#,
a.p1, a.p2, a.p3 , a.wait_time, a.time_waited , a.blocking_session,
a.blocking_session_serial#, a.blocking_inst_id , a.current_obj#,
a.current_file#, a.current_block#, a.current_row# , a.top_level_call#,
a.consumer_group_id, a.xid, a.remote_instance# , a.time_model ,
a.service_hash , substrb(a.program, 1, 64) , a.module, a.action,
a.client_id, a.machine, a.port, a.ecid , a.dbreplay_file_id,
a.dbreplay_call_counter , a.con_dbid , decode(a.tm_delta_time, 0,
to_number(null), a.tm_delta_time), decode(a.tm_delta_time, 0,
to_number(null), a.tm_delta_cpu_time), decode(a.tm_delta_time, 0,
to_number(null), a.tm_delta_db_time), decode(a.delta_time, 0,
to_number(null), a.delta_time), decode(a.delta_time, 0,
to_number(null), decode(a.delta_read_io_requests, 0,
to_number(null), a.delta_read_io_requests)),
decode(a.delta_time, 0, to_number(null),
decode(a.delta_write_io_requests, 0, to_number(null),
a.delta_write_io_requests)), decode(a.delta_time, 0, to_number(null),
decode(a.delta_read_io_bytes, 0, to_number(null),
a.delta_read_io_bytes)), decode(a.delta_time, 0, to_number(null),
decode(a.delta_write_io_bytes, 0, to_number(null),
a.delta_write_io_bytes)), decode(a.delta_time, 0, to_number(null),
decode(a.delta_interconnect_io_bytes, 0, to_number(null),
a.delta_interconnect_io_bytes)), decode(a.pga_allocated, 0, to_number(null),
a.pga_allocated), decode(a.pga_allocated, 0, to_number(null),
decode(a.temp_space_allocated, 0, to_number(null),
a.temp_space_allocated)) , a.dbop_name, a.dbop_exec_id FROM x$ash a,
(SELECT h.sample_addr, h.sample_id FROM x$kewash h
WHERE ( (h.sample_id >= :begin_flushing) and
(h.sample_id < :latest_sample_id) ) and (h.is_awr_sample =
'Y') ) shdr WHERE shdr.sample_addr = a.sample_addr and
shdr.sample_id = a.sample_id and a.need_awr_sample = 'Y')

 

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 605.76 605.79 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 605.76 605.79 0 0 0 0

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=103 us)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=12 us)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=10 us cost=1784279 size=17016804 card=39852)
762 762 762 FIXED TABLE FULL X$KEWASH (cr=0 pr=0 pw=0 time=10177 us cost=22 size=597780 card=39852)
0 0 0 FIXED TABLE FULL X$ASH (cr=0 pr=0 pw=0 time=604877937 us cost=45 size=412 card=1)

********************************************************************************

 

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms