Waiting for user Action - Review "Status" of SQL

SQL Optimizer SQL Optimizer for Oracle
1

select -------------------------------------------------------------------------------

–

– System AMCAS2.0 Admissions System

– Name APPLICANT_COLLEGE_V

– Type View

– Desc

–

– Created 02/26/2007

– Created by Stace Cameron

–

– * * * * * * * * * * R E V I S I O N S * * * * * * * * * *

– 02/26/2007 - slc - add program level description

A.APPL_YEAR,

A.APPL_PERSON_ID,

A.SSN,

A.LEGAL_LNAME,

A.ADMISSION_ACTION_CD,

A.ADMISSION_ACTION_DESC,

B.SCHOOL_ATTENDED_ID,

B.COLLEGE_INST_ID,

E.COLL_OCC,

I.COLL_ORDER,

B.MOD_SCHOOL_DESC,

C.SHORT_DESC,

D.SCHL_DESC --,NVL(FC1.GOOD_CITY, B.CITY) as city

,

B.CITY,

B.STATE_CD,

B.COUNTRY_CD,

J.PRGM_TYPE_DESC,

F.SHORT_DESC as COLL_MAJORD, G.DEGREE_CD

G.DEGREE_DT,

B.ATTEND_START_DT,

B.ATTEND_FINISH_DT,

B.PRIMARY_UNDERGRAD_INST_IND,

B.HIGHEST_DEGREE_INST_IND

SCHOOL_ATTENDED_V B,

REF_COLLEGE_INSTITUTION C,

C08.C08SCHL D,

APPL_PERSON_ID,

COLLEGE_INST_ID,

ATTEND_START_DT,

ATTEND_FINISH_DT,

APPL_PERSON_ID,

COLLEGE_INST_ID

APPL_PERSON_ID,

COLLEGE_INST_ID,

ATTEND_START_DT,

from SCHOOL_ATTENDED

AMCAS.MAJOR_V F,

a.school_attended_id,

a.DEGREE_CD,

a.DEGREE_NAME,

a.DEGREE_DT

school_attended_id,

max(highest_degree_ind) as maxhi

group by appl_year, school_attended_id) b

and a.school_attended_id = b.school_attended_id

C08.C08TOWN H,

APPL_PERSON_ID,

COLLEGE_INST_ID,

ATTEND_START_DT,

ATTEND_FINISH_DT,

APPL_PERSON_ID

APPL_PERSON_ID,

COLLEGE_INST_ID,

ATTEND_START_DT,

from SCHOOL_ATTENDED

and PRIMARY_UNDERGRAD_INST_IND <> 1) I, (

from AMCAS.REF_PROGRAM_TYPE

where A.APPL_PERSON_ID = B.APPL_PERSON_ID (+)

and B.PRGM_TYPE_CD <> ‘H’

and B.COLLEGE_INST_ID = C.COLLEGE_INST_ID

and B.APPL_YEAR = E.APPL_YEAR (+)

and B.COLLEGE_INST_ID = E.COLLEGE_INST_ID (+)

and B.ATTEND_FINISH_DT = E.ATTEND_FINISH_DT (+)

and B.SCHOOL_ATTENDED_ID = F.SCHOOL_ATTENDED_ID (+)

and B.SCHOOL_ATTENDED_ID = G.SCHOOL_ATTENDED_ID (+)

and B.APPL_YEAR = I.APPL_YEAR (+)

and B.COLLEGE_INST_ID = I.COLLEGE_INST_ID (+)

and B.ATTEND_FINISH_DT = I.ATTEND_FINISH_DT (+)

and B.PRGM_TYPE_CD = J.PRGM_TYPE_CD (+)

order by A.APPL_YEAR, A.SSN, C.FICE_CD, E.COLL_OCC

Using the Quest SQL Optimizer 7.2 for Oracle Trial, I have a single job in the Batch Optimizer that runs for hours and then shows Waiting for user Action - Review “Status” of SQL. There are no bind variables. What gives?

Here is the SQL:

C.FICE_CD as COLL_CODE, decode(B.STATE_CD, ‘IL’, H.TOWN_CODE, NULL) as SCHL_TOWN, B.PRGM_TYPE_CD as COLL_PROG, F.MAJOR_CD as COLL_MAJOR, as COLL_DEGREE, G.DEGREE_NAME as COLL_DEGREED, to_char(G.DEGREE_DT, ‘YYYY’) as COLL_DEG_YR, to_char(B.ATTEND_START_DT, ‘YYYY’) as COLL_ENT_YR, to_char(B.ATTEND_FINISH_DT, ‘YYYY’) as COLL_END_YR, from APPLICANT_PERSON_V A, (select APPL_YEAR, RANK() OVER (partition by APPL_YEAR, ORDER BY APPL_YEAR, ATTEND_FINISH_DT) as COLL_OCC where PRGM_TYPE_CD <> ‘H’) E, (select a.appl_year, from degree a, (select appl_year, from degree where a.appl_year = b.appl_year and a.highest_degree_ind = b.maxhi) G, (select APPL_YEAR, RANK() OVER (partition by APPL_YEAR, ORDER BY APPL_YEAR, ATTEND_FINISH_DT) as COLL_ORDER where PRGM_TYPE_CD <> ‘H’ select * where active_ind = 1) J and A.APPL_YEAR = B.APPL_YEAR (+) and A.APPL_YEAR = C.APPL_YEAR and C.FICE_CD = D.SCHL_CODE (+) and B.APPL_PERSON_ID = E.APPL_PERSON_ID (+) and B.ATTEND_START_DT = E.ATTEND_START_DT (+) and B.APPL_YEAR = F.APPL_YEAR (+) and B.APPL_YEAR = G.APPL_YEAR (+) and B.CITY = H.TOWN_DESC (+) and B.APPL_PERSON_ID = I.APPL_PERSON_ID (+) and B.ATTEND_START_DT = I.ATTEND_START_DT (+) and B.APPL_YEAR = J.APPL_YEAR (+)

2

What’s the status show from the section of ‘SQL in Selected Job’?

Does it show any error?

3

I have the same problem with at least 3 queries. In the “SQL in Selected Jobs” section, the “Action for User” column says “Input Variables”. There are no bind variables and the queries run fine from SQL Plus. I also ran one through Toad without problems.

4

The batch optimizer will scan the whole PL/SQL script and than cut out the SQL to optimize and execute as independent statement, so the definition of any bind variable will be no longer available. However, if any of these SQL (not the whole PL/SQL script) is allowed to execute in SQL Plus or Toad, it could be caused by using incorrect schema. Please check if the schema using of optimization and execution.

If not on the reasons above, it could be program bug.

5

invalid_up,

bounce_up) = (SELECT mc.bounced,

AND (c.bounced + 1 >= (SELECT bounce_limit

WHERE ci.client_id = c.client_id))

ELSE 1 END invalid_up,

ELSE 1 END bounce_up

emtadmin_e3.contacts c

AND mes_cont_id = (SELECT MAX(mes_cont_id)

WHERE mc1.contact_id = am.contact_id

WHERE am.action = ‘standard’

I have the same issue. A very simple sql statement and the tool is asking for binds.

UPDATE agillespie.bnc_wrk_agtest am SET (prevbounce, CASE WHEN (mc.bounced = 1 FROM emtadmin_e3.clientinfo ci OR SUBSTR(email, INSTR(email, ‘@’, 1, 1) + 1) = ‘aol.com’) THEN 2 CASE WHEN mc.bounced = 1 THEN c.bounced + 1 FROM emtadmin_e3.message_contacts mc, WHERE mc.contact_id = c.contact_id FROM emtadmin_e3.message_contacts mc1 AND mc1.mes_cont_id < am.mes_cont_id))

6

Hi,

In the SQL List window in the Batch Optimizer, would you click on the SQL Status field where it says “Input Bind Variables…” and select “Input Bind Variables” from the menu to open the Setting Bind Variables window. Look at the “Variable Name” column and tell us which text in your SQL statement it thinks represents a bind variable that is not variable?

Rene Woody