Procedures dying after running to certain record
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Procedures dying after running to certain record

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    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;
    /

  2. #2
    Join Date
    Sep 2002
    Posts
    411
    any suggestions please??????

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  4. #4
    Join Date
    Sep 2002
    Posts
    411
    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

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    when it hangs query v$session_wait and see what event itīs waiting

  6. #6
    Join Date
    Nov 2002
    Posts
    5

    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.

  7. #7
    Join Date
    Sep 2002
    Posts
    411
    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.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  9. #9
    Join Date
    Sep 2002
    Posts
    411
    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???

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
  •  


Click Here to Expand Forum to Full Width