vivek.gupta4

Performance issue due to a small lookup query

Discussion created by vivek.gupta4 on Jan 15, 2015
Latest reply on Jan 28, 2015 by vivek.gupta4

We have a very simple lookup. The query is:

 

SELECT @SELECT:A.ID:ID@,

       @SELECT:A.D_ID:D_ID@,

       @SELECT:A.H_NAME:H_NAME@,

       @SELECT:A.PROJ_NAME:PROJ_NAME@

FROM ODF_CA_MW_TAB A

WHERE (@WHERE:PARAM:USER_DEF:INTEGER:PROJECT_ID@ = A.PROJ_NAME OR @WHERE:PARAM:USER_DEF:INTEGER:PROJECT_ID@ IS NULL)

AND @FILTER@

 

There are around 150 rows in ODF_CA_MW_TAB table.

This lookup is being used in another custom object (which has a total of only 90 instances).

When we try to navigate to this other custom object's list view, it keeps loading for ever.

 

On database side, the DBA found that the following query is holding the resources:

 

select id pl_proj , h_name pl_proj_caption from (

SELECT A.ID ID,

A.D_ID D_ID,

A.H_NAME H_NAME,

A.PROJ_NAME PROJ_NAME 

FROM ODF_CA_MW_TAB A 

WHERE ( :v0 = A.PROJ_NAME OR  :v1  IS NULL)

AND :"SYS_B_00000"= :v2  and :"SYS_B_00001"=:"SYS_B_00002") q_pl_proj

where rownum = :"SYS_B_00003" and q_pl_proj.id =  :v3

union

select id pl_proj , h_name pl_proj_caption from (

SELECT A.ID ID,

A.D_ID D_ID,

A.H_NAME H_NAME,

A.PROJ_NAME PROJ_NAME 

FROM ODF_CA_MW_TAB A 

WHERE ( :v4 = A.PROJ_NAME OR  :v5  IS NULL)

AND :"SYS_B_00004"= :v6  and :"SYS_B_00005"=:"SYS_B_00006") q_pl_proj

where rownum = :"SYS_B_00007" and q_pl_proj.id =  :v7

union

select id pl_proj , h_name pl_proj_caption from (

SELECT A.ID ID,

A.D_ID D_ID,

A.H_NAME H_NAME,

A.PROJ_NAME PROJ_NAME 

FROM ODF_CA_MW_TAB A 

WHERE ( :v8 = A.PROJ_NAME OR  :v9  IS NULL)

AND :"SYS_B_00008"= :v10  and :"SYS_B_00009"=:"SYS_B_00010") q_pl_proj

where rownum = :"SYS_B_00011" and q_pl_proj.id =  :v11

union

....

....

 

After a while, it terminates with following error in oracle:

 

ORA-20000: ORA-10027: buffer overflow, limit of 50000 bytes

ORA-06512: at "SYS.DBMS_OUTPUT", line 32

ORA-06512: at "SYS.DBMS_OUTPUT", line 97

ORA-06512: at "SYS.DBMS_OUTPUT", line 112

 

Can someone please throw light on how the small lookup query can cause such an issue?

Outcomes