-
Procedures dying after running to certain record
I have this procs and it's dying after running to certain number. Does anyone have any advises???
Thanks
CREATE OR REPLACE PROCEDURE SPATIAL.DUPLICATE_MULTI_CNTY_TWPS(p_st_code_list IN varchar2) is
cursor all_states is
select grid_lvl2_code
from grid_master
where grid_lvl = 2
--and grid_generatable = 'Y'
and grid_priority = 1;
cursor certain_states(c_st_code_list IN varchar2) is
select grid_lvl2_code
from grid_master
where grid_lvl = 2
and grid_priority = 1
--and grid_generatable = 'Y'
and grid_lvl2_code in (c_st_code_list);
cursor all_counties_by_state(c_st_code_cnty IN char) is
select grid_key
--, grid_lvl3_code, grid_lvl3_name, grid_fips_cnty_code
from grid_master
where grid_lvl = 3
and grid_priority = 1
--and grid_generatable = 'Y'
and grid_lvl2_code in (c_st_code_cnty);
--testing
--and grid_lvl3_name = 'Campbell'
--testing
--order by grid_lvl3_name;
cursor all_twps_near_cnty(c_cnty_grid_key IN varchar2, c_grid_lvl3_code IN spatial.grid_master.grid_lvl3_code%type) is
select a.grid_key
--, a.grid_lvl3_name
from grid_master a, grid_master b
where a.grid_lvl = 4
--and grid_generatable = 'Y'
and a.grid_priority = 1
and a.grid_lvl3_code <> c_grid_lvl3_code
--and grid_lvl2_code in (c_st_code_twp)
and b.grid_key = c_cnty_grid_key
and sdo_filter(a.sob_geometry, b.sob_geometry, 'querytype = WINDOW') = 'TRUE';
--order by a.grid_lvl3_name;
twp_count integer;
new_twp_key spatial.grid_master.grid_key%type;
mask varchar2(40);
v_twp_geometry mdsys.sdo_geometry;
v_cnty_geometry mdsys.sdo_geometry;
v_grid_lvl3_code spatial.grid_master.grid_lvl3_code%type;
begin
for each_state in certain_states(p_st_code_list) loop
for each_cnty in all_counties_by_state(each_state.grid_lvl2_code) loop
/*select sob_geometry
into v_cnty_geometry
from grid_master
where grid_key = each_cnty.grid_key;
*/
select grid_lvl3_code
into v_grid_lvl3_code
from grid_master
where grid_key = each_cnty.grid_key;
for each_twp in all_twps_near_cnty(each_cnty.grid_key,v_grid_lvl3_code) loop
/*
select sob_geometry
into v_twp_geometry
from grid_master
where grid_key = each_twp.grid_key;
*/
insert into dave_temp2(A,B)
values(each_cnty.grid_key,each_twp.grid_key);
SELECT SDO_GEOM.RELATE(a.sob_geometry, m.diminfo, 'DETERMINE', b.sob_geometry, m.diminfo)
into mask
FROM user_sdo_geom_metadata m, grid_master a, grid_master b
WHERE m.table_name = 'GRID_MASTER'
and m.column_name = 'SOB_GEOMETRY'
and a.grid_key = each_twp.grid_key
and b.grid_key = each_cnty.grid_key;
IF mask = 'OVERLAPBDYINTERSECT' THEN
/*
select count(grid_key)
into twp_count
from grid_master
where grid_lvl = 4
and grid_lvl2_code = each_state.grid_lvl2_code
and grid_lvl3_code = each_cnty.grid_lvl3_code
and grid_priority = 1
and grid_generatable = 'Y';
if twp_count = 1 then
*/
insert into dave_temp(A,B)
values(each_cnty.grid_key,each_twp.grid_key);
--exit;
end if;
commit;
--end if;
end loop;
end loop;
end loop;
end;
/
-
any suggestions please??????
-
dont understand what do you mean by die, if you dont get any ora-XXXX then I doubt anyone can help by just looking your code
-
Pando,
Thanks so much for your response. What I mean by dying was when I do a count on Dave_temp2, it insert records into that table and after certain record it hang and not doing anything. I don't get any ORA- at all.
seem like the resource issues b/c on the following cursor:
select a.grid_key
--, a.grid_lvl3_name
from grid_master a, grid_master b
where a.grid_lvl = 4
--and grid_generatable = 'Y'
and a.grid_priority = 1
and a.grid_lvl3_code <> c_grid_lvl3_code
--and grid_lvl2_code in (c_st_code_twp)
and b.grid_key = c_cnty_grid_key
and sdo_filter(a.sob_geometry, b.sob_geometry, 'querytype = WINDOW') = 'TRUE';
--order by a.grid_lvl3_name;
if I comment out more condition, more records will be inserted and if I take the comment out on the order by less record will be inserted
any thoughts???
Thanks
-
when it hangs query v$session_wait and see what event it´s waiting
-
procedure dying
if the procedure is hanging, it may mean that there are some locking issues going on with the procedure. hanging usually indicate resource contention and the fact that commenting out more code seems to work for a little while suggests this too. Check your locking/waiting views for that procedure. Good luck.
-
Thru_dba
This same procedure ran ok on Oracle 9i so I don't think it's locking in the procedure.
Pando,
Below is the info I got from that session when the procedure ran.
26 7983 db file sequential read file# 16 00000010 block# 9201 000023F1 blocks 1 00000001 -2 4 WAITED UNKNOWN TIME
Any thought??? or recommend???
Thanks much
Last edited by mike2000; 11-14-2002 at 11:04 AM.
-
well certainly it has not hanged, it is reading
just leave it running and check v4sesison_wait time to time, if you see enqueue then it means hang otherwise just leave it running
-
Pando,
It's certainly hang b/c it not doing anything when it reach certain number, it just sit there.
Below is second select on V$session_wait.. this is the second one when I already got rid of the first one.
19 3218 direct path read file number 1001 000003E9 first dba 118095 0001CD4F block cnt 1 00000001 -2 68 WAITED UNKNOWN TIME
I have one session ran from last night till this morning and it's not going anywhere, The same procedure ran on 9i and it ran in less than a min.
Any thoughts???
-
its not hanging period
you might as well look yoour cursors explain plan
if it was hanging you would see latch free, enqueue which are blocking mechanisms otherwise it´s doing work but if your cursor is slow then you see what you see
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|