update query hangs and does not update

[diehippy]

Hi all,

I was wondering if you could help me I have an update query which hangs when I run it and I am unsure why. Here is the code

UPDATE sdc_destination_data dd
SET (dd.actual_destination_code, dd.actual_destination_desc) =
(SELECT
he.code,
he.adDESC
FROM
(SELECT
a.person_code,
'54'code,
'SDC'||' '||ty.course_code||' - '||ty.course_description ADdesc
from
(SELECT
ly.person_code,
ly.forename,
ly.surname,
ly.unit_instance_code,
ly.rn
from
(SELECT
sd.person_code,
p.forename,
p.surname,
pu.unit_instance_code,
row_number ()
over (partition BY p.person_code
ORDER BY p.person_code,
decode(uio.fes_user_5, 'FT', 1000, 'HE PLY FT', 1000, 'HE EXE FT', 1000, 'HE PLY PT', 500, 'HE EXE PT', 550, 300) desc,
nvl(qa.guided_hours, 0) DESC) rn

FROM
sdc_destination_data sd,
people_units pu,
unit_instance_occurrences uio,
people p,
uio_qual_aims qa
WHERE
p.person_code = sd.person_code
AND p.person_code = pu.person_code
AND sd.YEAR = '08/09'
AND pu.unit_type = 'R'
AND pu.progress_status IN ( 'A', 'F')
AND pu.uio_id = uio.uio_id
and uio.uio_id = qa.uio_id(+)
and qa.funding_year(+) = 15
and qa.default_qa(+) = 'Y'
AND pu.calocc_code = '08/09'
AND uio.qual_end_date < To_Date('31/07/2009', 'DD/MM/YYYY')
AND (uio.fes_user_5 LIKE '%FT%'
or uio.fes_user_5 LIKE '%HE%')) ly
WHERE
rn = 1 ) a,
(SELECT
en.person_code,
en.forename,
en.surname,
en.course_code,
en.course_description,
en.uio_id
FROM
(SELECT
distinct
p.person_code,
p.forename,
p.surname,
pu.unit_instance_code course_code,
uio.long_description course_description,
uio.uio_id,
row_number()
over ( partition by pu.person_code
order by
pu.person_code,
--decode(nvl(ui.fes_source_finance, '99'), 'HE', 1000, 500) desc,
decode(uio.fes_user_5, 'FT', 1000, 'HE PLY FT', 1000, 'HE EXE FT', 1000, 'HE PLY PT', 500, 'HE EXE PT', 550, 300) desc,
nvl(qa.guided_hours, 0) desc
) rn
FROM
people p,
people_units pu,
unit_instance_occurrences uio,
uio_qual_aims qa
WHERE
p.person_code = pu.person_code
AND pu.uio_id = uio.uio_id
and uio.uio_id = qa.uio_id(+)
and qa.funding_year(+) = 16
and qa.default_qa(+) = 'Y'
AND pu.progress_status = 'A'
AND uio.fes_user_5 NOT IN ('TUT', 'KSF', 'ADDF')
AND pu.unit_type = 'R'
AND pu.calocc_code = '09/10'
AND NOT EXISTS (SELECT 1 FROM uio_links WHERE pu.uio_id = uio_id_to))en
WHERE
rn = 1) ty
WHERE
a.person_code = ty.person_code )he
WHERE dd.person_code = he.person_code)
WHERE
dd.person_code IN (
SELECT
a.person_code
from
(SELECT
ly.person_code,
ly.forename,
ly.surname,
ly.unit_instance_code,
ly.rn
from
(SELECT
sd.person_code,
p.forename,
p.surname,
pu.unit_instance_code,
row_number ()
over (partition BY p.person_code
ORDER BY p.person_code,
decode(uio.fes_user_5, 'FT', 1000, 'HE PLY FT', 1000, 'HE EXE FT', 1000, 'HE PLY PT', 500, 'HE EXE PT', 550, 300) desc,
nvl(qa.guided_hours, 0) DESC) rn

FROM
sdc_destination_data sd,
people_units pu,
unit_instance_occurrences uio,
people p,
uio_qual_aims qa
WHERE
p.person_code = sd.person_code
AND p.person_code = pu.person_code
AND sd.YEAR = '08/09'
AND pu.unit_type = 'R'
AND pu.progress_status IN ( 'A', 'F')
AND pu.uio_id = uio.uio_id
and uio.uio_id = qa.uio_id(+)
and qa.default_qa(+) = 'Y'
and qa.funding_year(+) = 15
AND pu.calocc_code = '08/09'
AND uio.qual_end_date < To_Date('31/07/2009', 'DD/MM/YYYY')
AND (uio.fes_user_5 LIKE '%FT%'
or uio.fes_user_5 LIKE '%HE%')) ly
WHERE
rn = 1 ) a,
(SELECT
en.person_code,
en.forename,
en.surname,
en.course_code,
en.course_description,
en.uio_id
FROM
(SELECT
distinct
p.person_code,
p.forename,
p.surname,
pu.unit_instance_code course_code,
uio.long_description course_description,
uio.uio_id,
row_number()
over ( partition by pu.person_code
order by
pu.person_code,
--decode(nvl(ui.fes_source_finance, '99'), 'HE', 1000, 500) desc,
decode(uio.fes_user_5, 'FT', 1000, 'HE PLY FT', 1000, 'HE EXE FT', 1000, 'HE PLY PT', 500, 'HE EXE PT', 550, 300) desc,
nvl(qa.guided_hours, 0) desc
) rn
FROM
people p,
people_units pu,
unit_instance_occurrences uio,
uio_qual_aims qa
WHERE
p.person_code = pu.person_code
AND pu.uio_id = uio.uio_id
and uio.uio_id = qa.uio_id(+)
and qa.funding_year(+) = 16
and qa.default_qa(+) = 'Y'
AND pu.progress_status = 'A'
AND uio.fes_user_5 NOT IN ('TUT', 'KSF', 'ADDF')
AND pu.unit_type = 'R'
AND pu.calocc_code = '09/10'
AND NOT EXISTS (SELECT 1 FROM uio_links WHERE pu.uio_id = uio_id_to))en
WHERE
rn = 1) ty
WHERE
a.person_code = ty.person_code)
AND dd.year = '08/09'

If any one can shed some light on thsi I would be very grateful

Many Thanks

 

[Turkbear]

Hi,
have you tried running an explain plan to see where the bottlenecks may be?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Andrzejek]

I thought you can set one field = something in Update statement, but you have:

[tt]UPDATE sdc_destination_data dd
SET ([blue]dd.actual_destination_code[/blue], [green]dd.actual_destination_desc[/green]) =
(SELECT ....[/tt]

Are you trying to assign value to blue or green field in your SET?

Or am I waaaay off?

Have fun.

---- Andy

 

[SantaMufasa]

DieHippy,

The "appearance" of a hang can occur if another session has even one row locked that you are attempting to update from "sdc_destination_data". Can you determine if there are any rows locked in "sdc_destination_data"?

You can prevent locks from interfering with your update by issuing the following code:

SQL> select * from sdc_destination_data for update nowait;
select * from sdc_destination_data for update nowait
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified.

The above code attempts to lock lock every row in the "sdc_destination_data" table during the SELECT (via the "for update" clause), but since I have another session that already locked at least one row from the "sdc_destination_data" table (and not yet COMMITted), the above code fails immediately since it cannot obtain the requested lock(s). This way, there is no appearance of a hang because of a blocking lock.

Let us know if this proves helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[diehippy]

Thanks everyone! I try an answer all

Mufasa - I checked the table it is not locked by another user it is only locked when I run the update query

Andrzejek - Yes, I am trying to set the blue and green fields from the select statement

Turkbear - I am not sure what an explain plan is I i have check all queries in the update they all run < 1 sec

Many Thanks for all of your help with this, I am very grateful

 

[Andrzejek]

I may be off (again....) but shouldn't be:
[tt]
UPDATE sdc_destination_data dd
SET [blue]dd.actual_destination_code = (Select....)[/blue], [green]dd.actual_destination_desc = (SELECT ....[/green][/tt]

???

Have fun.

---- Andy

 

[diehippy]

Thanks Andy,

it could be possible, I ended up putting into a cursor and looping through each person_code commiting after each run, it took about 40 minutes to update 1256 records but it did work

Many Thanks to you and everyone who commented on the this thread

 

[Beilstwh]

His syntax is correct. If you want to update two (or more) columns from the same query you can do


set (column1,column2) = (select xcol1,xcol2
from....)

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

Bill is absolutely correct with the two-column update syntax, but (WARNING!!!) unless you want to update every single row in your table, you must also specify a WHERE clause for your UPDATE statement, to limit the updated rows to just the ones with which you wish to correlate in your "SET...<correlated subquery>" clause.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

It's impossible to make specific recommendations without knowing more about your data. Some general points, though.

You seem do be selecting data that you don't actually use. For example, your statement starts:

UPDATE sdc_destination_data dd
   SET (dd.actual_destination_code, dd.actual_destination_desc) =
          (SELECT he.code,
                  he.addesc
           FROM   (SELECT a.person_code,
                          '54' code,
                             'SDC'
                          || ' '
                          || ty.course_code
                          || ' - '
                          || ty.course_description addesc
                   FROM   (SELECT ly.person_code,
                                  ly.forename,
                                  ly.surname,
                                  ly.unit_instance_code,
                                  ly.rn
                           FROM   ( ... etc. ...

So what ends up in your table is "54" for the destination code and a description made up of the course code and course description. All the other stuff like forenames and surnames and that complicated ROW_NUMBER() expression don't get copied across. So why select them? All those nested inline views probably don't help much either, it certainly makes it difficult for a human to see what's going on even if the database understands it!

It seems to me that your statement could be rewritten like this:

UPDATE sdc_destination_data dd
SET dd.actual_destination_code = '54',
   dd.actual_destination_desc = (quite complex subquery to
                                 work out the description)
WHERE dd.person_id IN (more complex subquery to find the
                       people to be updated)

Alternatively, if that method is still too slow, it can sometimes help to use PL/SQL to do it iteratively instead:

DECLARE
   CURSOR c_dd IS
      SELECT person_id,
             description
      FROM  (complicated query)
BEGIN
   FOR r_dd IN c_dd LOOP
      UPDATE sdc_destination_data dd
      SET dd.actual_destination_code = '54',
      dd.actual_destination_desc = r_dd.description
      WHERE dd.person_id = r_dd.person_id
   END LOOP;
END;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd