BUG: Beautifier locking PC resources at 100%

[Cedric Simon]

Hi,

I have a problem with a PL/SQL code causing a lock of computer resources to 100% for a long time (forever?). I need to kill the program to be able to do anything else.

The below code causes the problem. I suppose the recursive SQL (not very beautifull, I know) is causing the problem. Maybe too many sublevels?

As I have a small bug in this SQL (requiring 3 more subqueries!) I will disable the Beautifier for this function.

------

create or replace
FUNCTION func_chk_hgbst_elm (
p_List VARCHAR2, -- List name
p_Elm1 VARCHAR2, -- Level 1 value
p_Elm2 VARCHAR2 :=NULL, -- Level 2 value
p_Elm3 VARCHAR2 :=NULL, -- Level 3 value
p_Elm4 VARCHAR2 :=NULL, -- Level 4 value
p_Level1_title VARCHAR2 :='Level 1') -- Level 1 title
return VARCHAR2 is
lv_cnt PLS_INTEGER;
gv_return varchar2(5);
BEGIN

IF p_Elm4 IS NOT NULL THEN -- 4 levels
select count(*) INTO lv_cnt from sa.table_hgbst_elm tel4 where tel4.s_title like upper(p_Elm4)
and objid in
(select mtm4.hgbst_elm2hgbst_show from sa.mtm_hgbst_elm0_hgbst_show1 mtm4 where mtm4.hgbst_show2hgbst_elm in
(select mtm3.hgbst_show2hgbst_elm from sa.mtm_hgbst_elm0_hgbst_show1 mtm3 where mtm3.hgbst_elm2hgbst_show in
(select objid from sa.table_hgbst_elm tel1 where tel1.s_title like upper(p_Elm3) and objid in
(select mtm4.hgbst_elm2hgbst_show from sa.mtm_hgbst_elm0_hgbst_show1 mtm4 where mtm4.hgbst_show2hgbst_elm in
(select mtm3.hgbst_show2hgbst_elm from sa.mtm_hgbst_elm0_hgbst_show1 mtm3 where mtm3.hgbst_elm2hgbst_show in
(select objid from sa.table_hgbst_elm tel1 where tel1.s_title like upper(p_Elm2) and objid in
(select mtm4.hgbst_elm2hgbst_show from sa.mtm_hgbst_elm0_hgbst_show1 mtm4 where mtm4.hgbst_show2hgbst_elm in
(select mtm3.hgbst_show2hgbst_elm from sa.mtm_hgbst_elm0_hgbst_show1 mtm3 where mtm3.hgbst_elm2hgbst_show in
(select objid from sa.table_hgbst_elm tel1 where tel1.s_title like upper(p_Elm1)
)
and exists
(
select 1 from sa.table_hgbst_show ts where ts.title=p_Level1_title
and exists
(
select 1 from sa.table_hgbst_lst ls where ls.title like p_List and ls.hgbst_lst2hgbst_show=ts.objid
))))
)))
)));
-- Elsif ......
END IF;

IF lv_cnt>0 THEN -- no check if >1
RETURN ('OK');
ELSE -- Dependecy not found
RETURN ('CT02');
END IF;

EXCEPTION
WHEN OTHERS THEN
BEGIN
pk_logging_gtts.proc_process_error_logging ( p_return => gv_return,
p_error_source => 'PK_CLARIFY_TOOLS_GTTS',
p_primary_key => p_List,
p_error_id => 'ORA-ERROR',
p_error_comment => sqlerrm,
p_procedure => 'func_chk_hgbst_elm',
p_error_level => 'N/A');
RETURN ('ORA-ERROR');
END;
END func_chk_hgbst_elm;

Many thanks in advance for your help!

Cedric Simon

 

[whip]

My suggestion: try to rephrase your query to use joins instead of IN and also insted of EXISTS ( SELECT 1 ...). I don't know your data model but I would bet this is possible and also not very difficult if you get the idea.

One more thing that may help: do not count the records that are returned. Instead, change the statement to "SELECT NULL FROM ...", declare a cursor with this SELECT statement, fetch once into a dummy variable and check %FOUND. That way the RDBMS needs to find the first record only and does not have to create the full result set. You can even try to use the FIRST_ROWS hint and see if that helps.

Tom

 

[whip]

Oh, sorry. I just realized it is the beautifier that causes the lockup and not the actual execution.

My suggestions still apply though

Toma

 

[Marco Kalter]

It does indeed hang. We'll fix it.