Oracle Enterprise Manager
Oracle SQL Explain Plan January 23, 2008 10:22:36 PM IST 

Target:

    AVSEDWP

 

Version: Oracle 10.2.0.2.0

 

Database: AVSEDWP

 

Schema: SYS

 

Date: Jan 23, 2008 12:00:00 AM


SQL Statement:

  
SELECT sum(al1.rental_count), sum(al1.time_and_mileage_amt_net_usd), 
       al2.month_desc, al2.year_desc, al2.month_of_year, 
       al5.distribution_channel_desc, al5.channel_group, 
       al3.rental_loc_mnemonic, al3.rental_loc_desc, 
       al1.driver_license_country_code, sum(al1.length_of_rental_actual), 
       sum(al1.gps_chg_amount_usd), sum(al1.ins_ldw_amt_usd)
    FROM eisuser.eis_fact al1, eisuser.eis_time al2, eisuser.eis_loc al3, 
         eisuser.eis_distribution_channel al5
    WHERE al1.entering_checkin_date = al2.date_value
      AND al1.dw_co_loc_id = al3.rental_dw_loc_id
      AND al1.distribution_channel = al5.distribution_channel_id
      AND al1.non_rev_ind = 'NONE'
      AND al2.year_desc IN ('2006', '2007')
      AND al3.rental_loc_mnemonic IN ('HNL', 'IAH', 'JFK', 'LAX', 'MCO', 'MIA', 
           'ORD', 'SAN', 'SFO', 'YYZ')
    GROUP BY al2.month_desc, al2.year_desc, al2.month_of_year, 
             al5.distribution_channel_desc, al5.channel_group, 
             al3.rental_loc_mnemonic, al3.rental_loc_desc, 
             al1.driver_license_country_code

Optimizer Mode Used:

  COST ALL ROWS (optimizer: CHOOSE)

Total Cost:

  1,334

Execution Steps:

Step #Step Name
27SELECT STATEMENT
26PX COORDINATOR
25SYS.:TQ10005 PX SEND [QC (RANDOM)]
24SORT [GROUP BY]
23PX RECEIVE
22SYS.:TQ10004 PX SEND [HASH]
21HASH JOIN [BUFFERED]
4BUFFER [SORT]
3PX RECEIVE
2SYS.:TQ10001 PX SEND [HASH]
1EISUSER.EIS_DISTRIBUTION_CHANNEL TABLE ACCESS [FULL]
20PX RECEIVE
19SYS.:TQ10003 PX SEND [HASH]
18HASH JOIN
8PX RECEIVE
7SYS.:TQ10002 PX SEND [HASH]
6PX BLOCK [ITERATOR]
5EISUSER.EIS_TIME TABLE ACCESS [FULL]
17BUFFER [SORT]
16PX RECEIVE
15SYS.:TQ10000 PX SEND [HASH]
14EISUSER.EIS_FACT TABLE ACCESS [BY GLOBAL INDEX ROWID]
13NESTED LOOPS
11INLIST ITERATOR
10EISUSER.EIS_LOC_CCRG TABLE ACCESS [BY INDEX ROWID]
9EISUSER.EIS_LOC_RNT_LOC_MNEMONIC INDEX [RANGE SCAN]
12EISUSER.EISFACT_DW_CO_LOC_ID_IX INDEX [RANGE SCAN]

Step #DescriptionEst. CostEst. Rows ReturnedEst. KBytes Returned
  1   This plan step retrieves all rows from table EIS_DISTRIBUTION_CHANNEL.3170.515
  2   This plan step has no supplementary description information.3170.515
  3   This plan step has no supplementary description information.
  4   This plan step sorts the buffer row source.------
  5   This plan step retrieves all rows from table EIS_TIME.373017.822
  6   This plan step has no supplementary description information.
  7   This plan step has no supplementary description information.373017.822
  8   This plan step has no supplementary description information.
  9   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index EIS_LOC_RNT_LOC_MNEMONIC.210--
  10   This plan step retrieves rows from table EIS_LOC_CCRG through ROWID(s) returned by an index.4100.313
  11   This plan step loops through the query's IN list predicate, executing its child step for each value found.
  12   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index EISFACT_DW_CO_LOC_ID_IX.271,748--
  13   This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.1,327553,49743,241.953
  14   This plan step retrieves rows from table EIS_FACT through ROWID(s) returned by a global partitioned index.13455,9942,624.719
  15   This plan step has no supplementary description information.13455,9942,624.719
  16   This plan step has no supplementary description information.
  17   This plan step sorts the buffer row source.------
  18   This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.1,330317,15332,520.571
  19   This plan step has no supplementary description information.1,330317,15332,520.571
  20   This plan step has no supplementary description information.
  21   This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.1,332317,15342,121.883
  22   This plan step has no supplementary description information.1,332317,15342,121.883
  23   This plan step has no supplementary description information.
  24   This plan step accepts a set of rows from its child node, and sorts them into groups based on the columns specified in the query's GROUP BY clause.1,334317,15342,121.883
  25   This plan step has no supplementary description information.1,334317,15342,121.883
  26   This plan step has no supplementary description information.
  27   This plan step designates this statement as a SELECT statement.1,334317,15342,121.883