Skip Headers
Previous
Previous
 
Next
Next

Viewing Execution Plans

You can generate and view execution plans for queries against cubes and dimensions the same as for those against relational tables.

The SQL EXPLAIN PLAN command creates a table with the content of the explain plan. The default table name is PLAN_TABLE.


See Also:

Oracle Database SQL Tuning Guide for a complete discussion of execution plans

Generating Execution Plans

The following command creates an execution plan for a basic query on a cube:

EXPLAIN PLAN FOR
  SELECT t.long_description time,
     p.long_description product,
     cu.long_description customer,
     ch.long_description channel,
     f.sales sales
  FROM time_calendar_view t,
     product_primary_view p,
     customer_shipments_view cu,
     channel_primary_view ch,
     units_cube_view f
  WHERE t.level_name = 'CALENDAR_YEAR'
     AND p.level_name = 'TOTAL'
     AND cu.level_name = 'TOTAL'
     AND ch.level_name = 'TOTAL'
     AND t.dim_key = f.TIME
     AND p.dim_key = f.product
     AND cu.dim_key = f.customer
     AND ch.dim_key = f.channel
  ORDER BY t.end_date;

The DISPLAY table function of the DBMS_XPLAN PL/SQL package formats and displays information from an execution plan, as shown in Example: Execution Plan for a Cube Query.

Execution Plan for a Cube Query

SQL> SELECT plan_table_output FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1667678335
 
----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |            |     1 |   100 |   104   (3)| 00:00:02 |
|   1 |  SORT ORDER BY                  |            |     1 |   100 |   104   (3)| 00:00:02 |
|   2 |   JOINED CUBE SCAN PARTIAL OUTER|            |       |       |            |          |
|   3 |    CUBE ACCESS                  | UNITS_CUBE |       |       |            |          |
|   4 |    CUBE ACCESS                  | CHANNEL    |       |       |            |          |
|   5 |    CUBE ACCESS                  | CUSTOMER   |       |       |            |          |
|   6 |    CUBE ACCESS                  | PRODUCT    |       |       |            |          |
|*  7 |    CUBE ACCESS                  | TIME       |     1 |   100 |   103   (2)| 00:00:02 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - filter(SYS_OP_ATG(VALUE(KOKBF$),12,13,2)='CALENDAR_YEAR' AND
              SYS_OP_ATG(VALUE(KOKBF$),43,44,2)='TOTAL' AND
              SYS_OP_ATG(VALUE(KOKBF$),33,34,2)='TOTAL' AND
              SYS_OP_ATG(VALUE(KOKBF$),23,24,2)='TOTAL')
 
22 rows selected.

Example: Execution Plan for a Cube Join shows an execution plan for a query that joins a cube and a table. See "Joining Cubes to Tables and Views" for the query.

Execution Plan for a Cube Join

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3634608218
-------------------------------------------------------------------------------------------------
| Id  | Operation                        |Name        |Rows | Bytes |TempSpc|Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |1464 |   128K|       | 1524  (94)| 00:00:19|
|   1 |  SORT ORDER BY                   |            |1464 |   128K|   152K| 1524  (94)| 00:00:19|
|*  2 |   CUBE JOIN                      |            |1464 |   128K|       | 1422 (100)| 00:00:18|
|   3 |    TABLE ACCESS FULL             |CUSTOMER_DIM|  61 |  2379 |       |    4   (0)| 00:00:01|
|   4 |    JOINED CUBE SCAN PARTIAL OUTER|            |     |       |       |           |         |
|   5 |     CUBE ACCESS                  |UNITS_CUBE  |     |       |       |           |         |
|   6 |     CUBE ACCESS                  |CHANNEL     |     |       |       |           |         |
|   7 |     CUBE ACCESS                  |PRODUCT     |     |       |       |           |         |
|*  8 |     CUBE ACCESS                  |TIME        |2520 |   125K|       | 1417 (100)| 00:00:18|
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CU"."SHIP_TO_ID"=SYS_OP_ATG(VALUE(KOKBF$),76,77,2))
   8 - filter(SYS_OP_ATG(VALUE(KOKBF$),32,33,2)='CY2006' AND
              SYS_OP_ATG(VALUE(KOKBF$),85,86,2)='TOTAL' AND 
              SYS_OP_ATG(VALUE(KOKBF$),65,66,2)='TOTAL')

22 rows selected.

Types of Execution Plans

Table: Descriptions of Execution Plans for Cubes and Dimensions describes the types of execution plans for cubes.

Descriptions of Execution Plans for Cubes and Dimensions

Operation Option Description

CUBE JOIN

--

Joins a table or view on the left and a cube on the right.

CUBE JOIN

ANTI

Uses an antijoin for a table or view on the left and a cube on the right.

CUBE JOIN

ANTI SNA

Uses an antijoin (Single-sided Null Aware) for a table or view on the left and a cube on the right. The join column on the right (cube side) is NOT NULL. For example:

SELECT cols FROM table 
   WHERE table.c1 NOT IN 
      (SELECT col FROM cube 
         WHERE cube.col IS NOT NULL)

CUBE JOIN

OUTER

Uses an outer join for a table or view on the left and a cube on the right.

CUBE JOIN

RIGHT SEMI

Uses a right semijoin for a table or view on the left and a cube on the right.

CUBE SCAN

--

Uses inner joins for all cube access.

CUBE SCAN

PARTIAL OUTER

Uses an outer join for least one dimension, and inner joins for the other dimensions.

CUBE SCAN

OUTER

Uses outer joins for all cube access.



See Also:

Oracle Database SQL Language Reference for descriptions of these join types.