RE: wait events not as v$sql_plan

How did you get your plan?
>From "explain" or from 10046 tracing?

"expalin" doesn't allways show the plan being used at execution time.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ganesh Raja Sent: Monday, March 21, 2005 11:37 AM
To: Oracle-L
Subject: wait events not as v$sql_plan

I have got a Plan as below

| Operation                              |  Name                |
-----------------------------------------------------------------
| SELECT STATEMENT                       |                      |
|  SORT UNIQUE                           |                      |
|   NESTED LOOPS                         |                      |
|    NESTED LOOPS                        |                      |
|     NESTED LOOPS                       |                      |
|      NESTED LOOPS                      |                      |
|       HASH JOIN                        |                      |
|        TABLE ACCESS FULL               | KDD_BREAK_BINDING    |
|        PARTITION RANGE ALL             |                      |
|         TABLE ACCESS FULL              | FIRM_ACCT_POSN       |
|       TABLE ACCESS BY INDEX ROWID      | KDD_BREAK            |
|        INDEX UNIQUE SCAN               | PK_KDD_BREAK         |
|      TABLE ACCESS BY INDEX ROWID       | KDD_BREAK_BINDING    |
|       INDEX UNIQUE SCAN                | PK_BREAK_BINDING     |
|     INDEX RANGE SCAN                   | PK_KDD_BREAK_MTCHS   |
|    VIEW                                | TRADE_EXCTN_DTLS_VW  |
|     UNION-ALL PARTITION                |                      |
|      TABLE ACCESS BY GLOBAL INDEX ROWID| TRADE                |
|       INDEX UNIQUE SCAN                | PK_TRADE             |
|      TABLE ACCESS BY GLOBAL INDEX ROWID| EXECUTION            |
|       INDEX UNIQUE SCAN                | PK_EXECUTION         |

According to the plan the Trade table is being Scanned using an Index. But the Wait events show a DB file scattered read on the Trade Table which indicates a Full Table scan.

These are also Paralel processes Trampling on Each Other i.e. i have Got p001 trying to read the same block as P002 and P001 waiting on Buffer Busy waits.

Any help is greatly appreciated.

Rgds,
Ganesh

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l