Performance issues with dynamic action (PL/SQL)

Ivan2405

Hi!

I'm having perfomance issues with a dynamic action that is triggered on a button click.

I have 5 drop down lists to select columns which the users want to filter, 5 drop down lists to select an operation and 5 boxes to input values.

After that, there is a filter button that just submits the page based on the selected filters.

This part works fine, the data is filtered almost instantaneously.

After this, I have 3 column selectors and 3 boxes where users put values they wish to update the filtered rows to,

There is an update button that calls the dynamic action (procedure that is written below).

It should be straight out, the only performance issue could be the decode section, because I need to cover cases when user wants to set a value to null (@) and when he doesn't want update 3 columns, but less (he leaves '').

Hence P99_X_UC1 || ' = decode('  || P99_X_UV1 ||','''','|| P99_X_UC1  ||',''@'',null,'|| P99_X_UV1  ||')

However when I finally click the update button, my browser freezes and nothing happens on the table.

Can anyone help me solve this and improve the speed of the update?

Regards,

Ivan

P.S. The code for the procedure is below:

create or replace

PROCEDURE DWP.PROC_UPD

(P99_X_UC1 in VARCHAR2,

P99_X_UV1 in VARCHAR2,

P99_X_UC2 in VARCHAR2,

P99_X_UV2 in VARCHAR2,

P99_X_UC3 in VARCHAR2,

P99_X_UV3 in VARCHAR2,

P99_X_COL in VARCHAR2,

P99_X_O in VARCHAR2,

P99_X_V in VARCHAR2,

P99_X_COL2 in VARCHAR2,

P99_X_O2 in VARCHAR2,

P99_X_V2 in VARCHAR2,

P99_X_COL3 in VARCHAR2,

P99_X_O3 in VARCHAR2,

P99_X_V3 in VARCHAR2,

P99_X_COL4 in VARCHAR2,

P99_X_O4 in VARCHAR2,

P99_X_V4 in VARCHAR2,

P99_X_COL5 in VARCHAR2,

P99_X_O5 in VARCHAR2,

P99_X_V5 in VARCHAR2,

P99_X_CD in VARCHAR2,

P99_X_VD in VARCHAR2

) IS

l_sql_stmt varchar2(32600);

p_table_name varchar2(30) := 'DWP.IZV_SLOG_DET'; 

BEGIN

l_sql_stmt := 'update ' || p_table_name || ' set '

|| P99_X_UC1 || ' = decode('  || P99_X_UV1 ||','''','|| P99_X_UC1  ||',''@'',null,'|| P99_X_UV1  ||'),'

|| P99_X_UC2 || ' = decode('  || P99_X_UV2 ||','''','|| P99_X_UC2  ||',''@'',null,'|| P99_X_UV2  ||'),'

|| P99_X_UC3 || ' = decode('  || P99_X_UV3 ||','''','|| P99_X_UC3  ||',''@'',null,'|| P99_X_UV3  ||') where '||

P99_X_COL  ||' '|| P99_X_O  ||' ' || P99_X_V  || ' and ' ||

P99_X_COL2 ||' '|| P99_X_O2 ||' ' || P99_X_V2 || ' and ' ||

P99_X_COL3 ||' '|| P99_X_O3 ||' ' || P99_X_V3 || ' and ' ||

P99_X_COL4 ||' '|| P99_X_O4 ||' ' || P99_X_V4 || ' and ' ||

P99_X_COL5 ||' '|| P99_X_O5 ||' ' || P99_X_V5 || ' and ' ||

P99_X_CD   ||       ' = '         || P99_X_VD ;

--dbms_output.put_line(l_sql_stmt); 

EXECUTE IMMEDIATE l_sql_stmt;

END;

Christian Neumueller-Oracle

Hi Ivan,

I do not think that the decode is performance relevant. Maybe the update hangs because some other transaction has uncommitted changes to one of the affected rows or the where clause is not selective enough and needs to update a huge amount of records.

Besides that - and I might be wrong, because I only know some part of your app - the code here looks like you have a huge sql injection vulnerability here. Maybe you should consider re-writing your logic in static sql. If that is not possible, you should make sure that the user input only contains allowed values, e.g. by white-listing P99_X_On (i.e. make sure they only contain known values like '=', '<', ...), and by using dbms_assert.enquote_name/enquote_literal on the other P99_X_nnn parameters.

Regards,

Christian

Christian Neumueller-Oracle

Hi Ivan,

I do not think that the decode is performance relevant. Maybe the update hangs because some other transaction has uncommitted changes to one of the affected rows or the where clause is not selective enough and needs to update a huge amount of records.

Besides that - and I might be wrong, because I only know some part of your app - the code here looks like you have a huge sql injection vulnerability here. Maybe you should consider re-writing your logic in static sql. If that is not possible, you should make sure that the user input only contains allowed values, e.g. by white-listing P99_X_On (i.e. make sure they only contain known values like '=', '<', ...), and by using dbms_assert.enquote_name/enquote_literal on the other P99_X_nnn parameters.

Regards,

Christian

Ivan2405

Tnx!