Time critical DB call on 'insert into temp'

book

Article ID: 192352

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine CA Automic One Automation

Issue/Introduction

The queries with "insert into uc_temp" can be long due to statistic, this can be temp, temp1, temp2, temp3 etc..

15 - 20200311/080058.998 - U00003524 UCUDB: ===> Time critical DB call!       OPC: 'EXEC' time: '23:049.089.999'
15 - 20200311/080058.998 - U00003525 UCUDB: ===> 'insert into uc_temp2 (temp2_key1,temp2_key2,temp2_key3,temp2_string1,temp2_string2, temp2_num1) select * from (select  1405151, oh_idnr, 210, oh_name, upper(dbms_lob.substr(OT_Content, 2000, 1)) as OT_Content, OT_Lnr from oh, ot where upper(dbms_lob.substr(ot_content,2000,1)) like upper(?) escape '+'  and oh_idnr = ot_oh_idnr and oh_client = ? and oh_deleteflag = 0) where ((regexp_like (upper(ot_content), upper(?)))  )'
15 - 20200311/080059.912 - U00003434 Server routine  'UCDS_R' required '0' minutes and '26,482' seconds for processing.

Cause

This can be caused by the statistic on the temp* tables

Environment

Release : v12.2 / v12.3

Component : Automation Engine

Resolution

This can be improved by removing the statistic of these tables (UC_TEMP, UC_TEMP1, UC_TEMP2, UC_TEMP3,...)

See here how to remove statistics or ask your DBA:
https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS059

The command to remove and block statistics on Oracle look like this:

exec dbms_stats.delete_table_stats('AE','UC_TEMP');

exec dbms_stats.lock_table_stats('AE','UC_TEMP');

exec dbms_stats.delete_table_stats('AE','UC_TEMP1');

exec dbms_stats.lock_table_stats('AE','UC_TEMP1');

exec dbms_stats.delete_table_stats('AE','UC_TEMP2');

exec dbms_stats.lock_table_stats('AE','UC_TEMP2');

exec dbms_stats.delete_table_stats('AE','UC_TEMP3'); 

exec dbms_stats.lock_table_stats('AE','UC_TEMP3');