Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 174

--------------------------------------------------------------------------------------
| Id | Operation | Name |Rows |Bytes |TempSpc|Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 150 | 4950 | | 259K (1)| 00:51:54 |
| 1 | HASH GROUP BY | | 150 | 4950 | | 259K (1)| 00:51:54 |
|* 2 | HASH JOIN | | 42M| 1340M| 94M| 257K (1)| 00:51:36 |
| 3 | INDEX FAST FULL SCAN|ORDERS_I2|4299K| 45M| | 5458 (1)| 00:01:06 |
| 4 | INDEX FAST FULL SCAN|IORDL | 42M| 902M| | 178K (1)| 00:35:42 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OL"."OL_O_ID"="ORD"."O_ID" AND "OL"."OL_W_ID"="ORD"."O_W_ID" AND
"OL"."OL_D_ID"="ORD"."O_D_ID")
Statistics
----------------------------------------------------------
727 recursive calls
0 db block gets
455937 consistent gets
455700 physical reads
0 redo size
11180 bytes sent via SQL*Net to client
733 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
300 rows processed

■ For an explanation on how to interpret the EXPLAIN PLAN for a sQl query, please refer to Metalink note
number 46234.1.

Note

DBMS_SQLTUNE
Similar to the explain plan output generated above, a new feature introduced in Oracle Database 11g Release 1 is the
DBMS_SQLTUNE package, which contains several procedures providing more opportunities for generating explain plan
outputs with more details and, in some cases, output in a graphical form using HTML.

DBMS_SQLTUNE.REPORT_SQL_MONITOR •

The report from this procedure is generated using the following commands:

SET LONG 1000000
SET LONGCHUNKSIZE 10000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR FROM DUAL;