We've a 11gr2 database with cursor sharing set to similar. One of our load runs wherein we pick records from a table (DEVICE_AGE_AT_OCCURRENCE) and update matching records in table (ACCOUNT_PROFILE_DATA_MART).. something like:
declare
cursor c1 is
select device_id, return_nbr, device_age_at_occurance
from DEVICE_AGE_AT_OCCURRENCE;
begin
for v_c1 in c1 loop
execute immediate 'update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURANCE = v_c1.device_age_at_occurance where device_id = v_c1.devie_id and return_nbr = v_c1.return_nbr;
etc.
Now, when I join these 2 tables, I get 66180 odd records:
select count(*) from ddsowner.DEVICE_AGE_AT_OCCURRENCE a , ddsowner.ACCOUNT_PROFILE_DATA_MART b
where a.DEVICE_ID = b.DEVICE_ID
and a.RETURN_NBR = b.RETURN_NBR;
COUNT(*)
--------
66817
But there are like over 2 million executions for this statement.. when I lookup v$sql_shared_cursors I see over 9000 cild cursors..
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
######### 681,101 0.70 557.9 ######### 2,945,355 3125442085
Module: SQL*Plus
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE =
:"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B
_2"
Why there are som many executions when it should execute only 66180 times.. are there so many child cursors as this table has histograms & oracle get a different plan for popular values.. why is ACS not kicking in (how do I make ACS kick in)?
The sql_text shows all alike:
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"
update ACCOUNT_PROFILE_DATA_MART set DEVICE_AGE_AT_OCCURRENCE = :"SYS_B_0" where DEVICE_ID = :"SYS_B_1" and RETURN_NBR = :"SYS_B_2"