Re: What is expected behavior in v$session_longops for simple update

From: <p.santos000_at_gmail.com>
Date: 13 Dec 2006 08:02:55 -0800
Message-ID: <1166025775.137280.133380@j72g2000cwa.googlegroups.com>

DA Morgan wrote:
> p.santos000_at_gmail.com wrote:
>
> > Have you ever heard of a case where oracle might have to FTS a table
> > more than once to complete a statement similar to the one I listed?
> >
> > -peter
>
> ALTER TABLE <table_name> PARALLEL 2;
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Daniel, I think I've got this mystery partially solved. Don't yet know if this is a bug or
a feature.

Last night the same customer did a multi-column clearing. The SQL was something like this:

UPDATE TABLE_NAME
SET COLUMN_1 = NULL,
COLUMN_2 = NULL,
...
COLUMN_25 = NULL
WHERE
COLUMN_1 IS NOT NULL or
COLUMN_2 IS NOT NULL or
...
COLUMN_25 IS NOT NULL and ROWNUM <= 500000;

When I looked at the execution plan for this I noticed that there was no
"COUNT STOPKEY" operation .. and I noticed this because during the first
 pass on the table caused 9 milllion records to get updated .. so the ROWNUM was ignored. ...

---

| Id | Operation |Name |Rows|Bytes | Cost (%CPU)|

---

|   0 | UPDATE STATEMENT     |           |33M | 1641M| 80509  (20)|
|   1 |  UPDATE              |TABLE_NAME |    |      |            |
|   2 |   COUNT              |           |    |      |            |
|*  3 |    FILTER            |           |    |      |            |
|   4 |     TABLE ACCESS FULL|TABLE_NAME |33M | 1641M| 80509  (20)|
--------------------------------------------- ---------------------

3 - filter("COLUMN_1" IS NOT NULL OR "COLUMN_2" IS NOT NULL OR "COLUMN_3" IS NOT NULL OR "COLUMN_4" IS NOT NULL OR "COLUMN_5" IS NOT
...

"COLUMN_20" IS NOT NULL OR "COLUMN_21" IS NOT NULL OR
"COLUMN_22" IS NOT NULL OR "COLUMN_23" IS NOT NULL OR
"COLUMN_24" IS NOT NULL OR

ROWNUM<=TO_NUMBER(:P1) AND "COLUMN_25" IS NOT NULL)

When I add parenthesis around all the IS NOT NULL predicates and exclude
the ROWNUM as a separate predicate I get this

---

| Id | Operation |Name |Rows | Bytes | Cost (%CPU)|

---

| 0 | UPDATE STATEMENT | | 24M| 1208M| 188K (66)|
| 1 | UPDATE |TABLE_NAME| | | |
|* 2 | COUNT STOPKEY | | | | |
|* 3 | TABLE ACCESS FULL|TABLE_NAME| 24M| 1208M| 188K (66)|

2 - filter(ROWNUM<=TO_NUMBER(:P1))
3 - filter("COLUMN_1" IS NOT NULL OR "COLUMN_2" IS NOT NULL OR "COLUMN_3" IS NOT NULL OR "COLUMN_4" IS NOT NULL OR "COLUMN_5" IS NOT
...

"COLUMN_20" IS NOT NULL OR "COLUMN_21" IS NOT NULL OR
"COLUMN_22" IS NOT NULL OR "COLUMN_23" IS NOT NULL OR
"COLUMN_24" IS NOT NULL OR "COLUMN_25" IS NOT NULL)

In the above plan, the STOPKEY operation generates a separate filter for
the ROWNUM predicate. I'm guessing this is probably the way it's designed
and not a bug right? In any case, this should be a simple change that we
can make in our app.

-peter Received on Wed Dec 13 2006 - 10:02:55 CST