Re: Unit of TIME in dbms_xplan

On Dec 27, 1:25 pm, zigzag..._at_yahoo.com wrote:
> I am 10.2.0.3 on HP UNIX 11i
>
> I  use sqlplus to run a query. I set set autotrace on which I think
> calls dbms_xplan to display the sql plan. Query took 54 seconds
> to complete (using UNIX time x command).
>
> Plan (only few lines are shown), times are lot more.   I do not
> understand TIME for each operation. Is it in HH:MI:SS format.  How can
> time of several operations (00:01:58) – 118 seconds be larger than
> total execution time of the query.
>
> ----------------------------------------------------------
> Plan hash value: 2411281882
>
> ---------------------------------------------------------------------------­-----
> ---------------------------------------
>
> | Id  | Operation                        | Name
> | Rows  |
> Bytes |TempSpc| Cost (%CPU)| Time     |
>
> ---------------------------------------------------------------------------­-----
> ---------------------------------------
>
> |   0 | SELECT STATEMENT                 |
> | 74646 |
>  9622K|       |  9751   (2)| 00:01:58 |
>
> |   1 |  SORT ORDER BY                   |
> | 74646 |
>  9622K|    20M|  9751   (2)| 00:01:58 |
>
> |   2 |   HASH UNIQUE                    |
> | 74646 |
>  9622K|    20M|  7543   (2)| 00:01:31 |
>
> |   3 |    NESTED LOOPS                  |
> | 74646 |
>  9622K|       |  5335   (2)| 00:01:05 |
>
> |   4 |     NESTED LOOPS                 |
> |   212K|
>    23M|       |  5318   (2)| 00:01:04 |
>
> |*  5 |      HASH JOIN                   |
> |   212K|
>    19M|  4688K|  5301   (2)| 00:01:04 |

dbms_xplan apperas to generate estimated time and not actual time, so I am rephrasing my question. Is there a way to ge actaul time for each operation after statement has been executed. Received on Sat Dec 27 2008 - 15:20:48 CST