Home » RDBMS Server » Server Administration » UPDATE taking a long time, indication disappeared (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
UPDATE taking a long time, indication disappeared [message #681783] Mon, 31 August 2020 08:41 Go to next message
Andrey_R
Messages: 343
Registered: January 2012
Location: Israel
Senior Member

Hi all,

I am already executing for 4 hours an update on a 25M table ( in PL/Sql Developer, so I cannot ) MYTAB:



UPDATE MYTAB SET COL1=REPLACE(COL1,'X','Y');

After quit a few hours - it is showing nothing in V$SESSION_LONGOPS:

SQL> alter session set nls_date_Format='dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> col units for a12
SQL> col message for a25
SQL> set num 15
SQL> col start_time for a30
SQL> col DONE for a10
SQL> set lines 300 pages 400
SQL> --
SQL> select
  2  sid,
  3  message,
  4  sofar,
  5  totalwork,
  6  --substr((sofar/totalwork * 100),1,5)||'%' DONE,
  7   units,start_time,elapsed_seconds
  8  from gv$session_longops
  9  where sofar!=totalwork;

no rows selected

SQL>
Also no transaction in v$session:

SQL>
SQL> select v.TADDR,program,last_call_et,status from v$session v where sid = 1943;

TADDR            PROGRAM                                             LAST_CALL_ET STATUS
---------------- ------------------------------------------------ --------------- --------
                 PlSqlDev.exe                                                  18 INACTIVE

SQL>


What will happen if I kill this session ?
Will it be rolled back and I will wait around same 4 hours?

Tom Kyte said in https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:698257800346953357- rolling back will take same time, if not more, then the original DML.

But on the other hand - I don't see the transaction anywhere... I do see that it is running in PL/Sql developer GUI...

Am I expected to wait long for a rollback if I kill it, since I see no transaction for this session ?

Many thanks in advance,
Andrey

[Updated on: Mon, 31 August 2020 08:42]

Report message to a moderator

Re: UPDATE taking a long time, indication disappeared [message #681784 is a reply to message #681783] Mon, 31 August 2020 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
repeatedly run SQL below to see if any value is increasing. Seeing increasing value indicate changes are occurring.

SELECT * FROM V$SESS_IO WHERE SID =<UPDATE SESSION ID>
Re: UPDATE taking a long time, indication disappeared [message #681785 is a reply to message #681783] Mon, 31 August 2020 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But on the other hand - I don't see the transaction anywhere...

Did you check v$transaction?

You can also check the execution of your statement using:
select DBMS_SQLTUNE.REPORT_SQL_MONITOR (
         session_id=>&sid,
         report_level=>'ALL') as report 
from dual
/
Re: UPDATE taking a long time, indication disappeared [message #681786 is a reply to message #681784] Mon, 31 August 2020 10:14 Go to previous messageGo to next message
Andrey_R
Messages: 343
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Mon, 31 August 2020 17:50

Quote:
But on the other hand - I don't see the transaction anywhere...
Did you check v$transaction?


/[/code]
I tried, but I cannot link it to a null value TADDR of the v$session....




BlackSwan wrote on Mon, 31 August 2020 17:43
repeatedly run SQL below to see if any value is increasing. Seeing increasing value indicate changes are occurring.

SELECT * FROM V$SESS_IO WHERE SID =<UPDATE SESSION ID>
Thank you very much.
I will check it next time I have this situation.

I do tend to think I'll get some confusing info out of it similar to the STATUS=INACTIVE while LAST_CALL_ET indicates a few or zero idle time for that session,
I believe something got terribly wrong here - some bad communication between the API of PL/SQL Developer and Oracle.


I killed the session and it was super fast. This indicates that it was actually rolled back long ago, but the PL/Sql application was just displaying as if it's in work.

I think what I can learn from is to never rely on PL/Sql Developer or other 3rd party applications when it comes to heavy RAM/I/O operations,
since it is hard to debug and prone to zombie-session type of problems.

Only SQL*Plus from now on...

Thanks both!


[Updated on: Mon, 31 August 2020 10:15]

Report message to a moderator

Re: UPDATE taking a long time, indication disappeared [message #681788 is a reply to message #681786] Mon, 31 August 2020 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I killed the session and it was super fast. This indicates that it was actually rolled back long ago, but the PL/Sql application was just displaying as if it's in work.
Next time check the event in v$session, I bet it was "SQL*Net message from client".
I tend to think the problem comes from PL/SQL Developer.

Re: UPDATE taking a long time, indication disappeared [message #681792 is a reply to message #681788] Mon, 31 August 2020 12:38 Go to previous message
Andrey_R
Messages: 343
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Mon, 31 August 2020 19:55

Quote:
I killed the session and it was super fast. This indicates that it was actually rolled back long ago, but the PL/Sql application was just displaying as if it's in work.
Next time check the event in v$session, I bet it was "SQL*Net message from client".
I tend to think the problem comes from PL/SQL Developer.


I think so too.
I'll share more info and we can reach a more solid insight next time when I notice these things.

Thank you
Previous Topic: histograms in dbms_stats.gather_table_stats
Next Topic: Empty Datafile
Goto Forum:
  


Current Time: Tue Nov 24 17:53:50 CST 2020