Database Reference
In-Depth Information
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2798 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | NESTED LOOPS | | 913K| 26M| 2798 (27)| 00:00:12 |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 4 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 39950 | 351K| 121 (27)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 39950 | 351K| 121 (27)| 00:00:01 |
------------------------------------------------------------------------------------
Plan 2(PHV: 265338492)
----------------------
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 14,736 3,684.0 44.6
CPU Time (ms) 14,565 3,641.2 46.1
Executions 4 N/A N/A
Buffer Gets 6,755 1,688.8 3.7
Disk Reads 6,485 1,621.3 3.8
Parse Calls 1 0.3 0.5
Rows 4 1.0 N/A
--------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | HASH JOIN | | 913K| 26M| 139 (33)| 00:00:01 |
| 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 4 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 918K| 8075K| 123 (27)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 8075K| 123 (27)| 00:00:01 |
------------------------------------------------------------------------------------
Tracing Facilities
Several tracing facilities provide information about execution plans. Unfortunately, except for SQL trace (see Chapter 3),
none of them is either officially supported or documented. But they may turn out to be useful, so I cover two of them
briefly.
Event 10053
If you're in serious difficulty because of the decisions made by the query optimizer and you want to understand what
is going on, a query optimizer trace may help you. Let me warn you, though, that reading the trace files it generates
isn't an easy task. Luckily, you won't need to read those often—and then only if you're really interested in the internal
workings of the query optimizer.
