What is the unit of time in dbms_xplan …?
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 |