How can you tell if an Oracle statement is actually doing anything?

[Former Member]

I have a large table (660 million rows) & I want to update 3 columns.

I can can see the update statement in st04.

How can I tell on the Oracle level ifthe table is actually getting updated.

After 50 hours I killed the process because I did not believe the table was being updated.

[Former Member · Accepted solution]

Hi,

well, it's the session monitor in 1st place.

If you are on Oracle 10.2 check in a 2nd step the Active session history in "DB02 * Wait event analysis * Active session history " (ASH).

Here you can see not only what statement is executed but WICH step in the statement Oracle is currently working on.

ASH samples every second from the Sessions and gives you a good start WHATs causing contention.

A step further would be to check the AWR (Automatic workload repository) to see what was going on in the last hour up to

7 days (default) in the past (mostly if you get to know of a performance issue the session already has disconncected - or , in your case was aborted - you only find the session's workload data in the AWR inlc. execution plan and waits statistics).

If you don't know any of these Oracle components you shoul invest some time in the Oracle manuals.

For us it's the vital information to correlate the SAP processes (i.e. form SAP BI 7) to the Oracle workload.

bye

yk

[stefan_koehler]

Hello Bill,

> How can I tell on the Oracle level ifthe table is actually getting updated.

What do you mean? I don't understand this statement.

> After 50 hours I killed the process because I did not believe the table was being updated

What was the wait event of the SQL statement in ST04? "Believe" is not "knowing".

Regards

Stefan

[Former Member]

<<How can I tell on the Oracle level ifthe table is actually getting updated.What do you mean? I don't understand this statement.>>

I looked in st04 and saw the update statement. It was running for hours.

<<After 50 hours I killed the process because I did not believe the table was being updatedWhat was the wait event of the SQL statement in ST04? "Believe" is not "knowing".>>

After 50 hours the person running the transport killed the process, because they belive nothing was happening.

So ho can I tell what was really happening Oracle? Was it actually updating the table? It doesn't appera that anything got updated.

[stefan_koehler]

Hello Bill,

> So ho can I tell what was really happening Oracle? Was it actually updating the table?

Again .. what was the wait event of the SQL statement .. maybe it was just waiting on a row level lock or something else. Without that information it is impossible to help you.

Regards

Stefan

[Former Member]

I do not know what the wait event was.

In general what do I look at in Oracle to see if the statement is processing?

[stefan_koehler]

Hello Bill,

> In general what do I look at in Oracle to see if the statement is processing?

The wait event and the corresponding parameters.

The following columns are interesting ... SQL_ID, EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#i1414383

Regards

Stefan

[Former Member]

Shouldn't the table be v$SESSION_WAIT

Will table show me if the statement is actually processing?