Re: WAIT #1: nam='direct path write' ela= 4809 p1=202 p2=14480 p3=9 - file 202 does not exist!

From: <jsb_at_digistar.com>
Date: Wed, 21 Jul 2004 11:46:08 -0400 (EDT)
Message-ID: <Pine.GSO.4.58.0407211141460.8016@digistar.com>

On Wed, 21 Jul 2004 jsb_at_digistar.com wrote:

> WAIT #1: nam='direct path write' ela= 4809 p1=202 p2=14480 p3=9
>
> The trace file says i'm beating up file# 202 yet I do not have a file#
> 202 - not there in v$tempfile nor v$datafile. I know that I have a lot
> of disk sorting going on by digging into the explain plan.
>
> But I need to know how to match p1=202 to TEMP for The Boss(tm). Does
> the value of p1 get transformed somehow?

I might have answered my own question by digging deeper into the explain plan:

Explain Plan

---

| Operation                 |  Name              |  Rows | Bytes|  Cost  |
--------------------------------------------------------------------------
| SELECT STATEMENT          |                    |    19K|  464K|   2527 |
|  SORT UNIQUE              |                    |    19K|  464K|   2427 |
|   HASH JOIN               |                    |    19K|  464K|   2327 |
|    TABLE ACCESS FULL      |ARTICLE_TAXONOMY    |    19K|  193K|    279 |
|    VIEW                   |index$_join$_001    |   201K|    2M|   2003 |
|     HASH JOIN             |                    |    19K|  464K|   2327 |
|      INDEX RANGE SCAN     |ARTICLE_PUBLISH_DAT |   201K|    2M|   3640 |
|      INDEX FAST FULL SCAN |PK_ARTICLE_IDX      |   201K|    2M|   3640 |
--------------------------------------------------------------------------

Is p1=202 pointing to the "index$_join$_001" which i'm guessing was created on the fly for the hash join? If so that's an interesting numbering method - 001 != 202.

Does the explain plan's temp_space report the number of db_block_size blocks or the number of bytes allocated for the sort?

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 21 2004 - 10:48:50 CDT