Different Performance from Standard Edition and Enterprise Edition? 4

24 11 2010

November 24, 2010

(Back to the Previous Post in the Series)

The previous articles in this series showed potential differences in performance between the Enterprise Edition of Oracle Database and the Standard Edition, as well as changes from release 10.2.0.5 to 11.2.0.1 for the same edition, even when the execution plans appeared to be the same.  The queries in the earlier articles were quite simple, yet demonstrated some of the types of problems that could be encountered if the servers used for the development or test do not use the same release version or edition as the production database, or even if the workload system (CPU) statistics differ.

Today’s blog article builds a more complicated test case – if you decide to try the test case, be prepared to spend between one and three hours building the test tables.  The tables that are constructed by the script are intended to model a portion of the data model employed by an ERP package, complete with foreign key constraints and regular check constraints.  The test script includes queries that are intended to answer questions that might be posed by a user of the ERP package, although the generated, reproducible test data likely does not match what would be found in a typical ERP application.  It might also be interesting to see how the different release versions and editions behave when pummeled by database agnostic code that might be found in certain applications – I wondr if that might be a topic for a future blog article.

Before we start, the commands to drop the generated tables and indexes are listed below - if you have any tables with these names in your database (schema), you probably do not want to execute the scripts in this blog article:

DROP TABLE PO_LINE PURGE;
DROP TABLE PO_HEADER PURGE;
DROP TABLE PO_LINE_TEMP PURGE;
DROP TABLE PO_HEADER_TEMP PURGE;
DROP TABLE PARTS PURGE;
DROP TABLE PARTS_TEMP PURGE;
DROP TABLE VENDORS PURGE;
DROP TABLE VENDORS_TEMP PURGE;
DROP TABLE LOCATIONS PURGE;
DROP TABLE UMS PURGE;

DROP INDEX IND_LOCATIONS_1;
DROP INDEX IND_PARTS_1;
DROP INDEX IND_PARTS_2;
DROP INDEX IND_PARTS_3;
DROP INDEX IND_PARTS_4;
DROP INDEX IND_PARTS_5;
DROP INDEX IND_PARTS_6;
DROP INDEX IND_PARTS_7;
DROP INDEX IND_PO_HEADER_1;
DROP INDEX IND_PO_HEADER_2;
DROP INDEX IND_PO_HEADER_3;
DROP INDEX IND_PO_HEADER_4;
DROP INDEX IND_PO_LINE_1;
DROP INDEX IND_PO_LINE_2;
DROP INDEX IND_PO_LINE_3;
DROP INDEX IND_PO_LINE_4;

As in the earlier blog articles, the following system (CPU) statistics will be used in all of the databases:

EXEC DBMS_STATS.SET_SYSTEM_STATS('MBRC',16)
EXEC DBMS_STATS.SET_SYSTEM_STATS('CPUSPEED',2664)
EXEC DBMS_STATS.SET_SYSTEM_STATS('SREADTIM',8)
EXEC DBMS_STATS.SET_SYSTEM_STATS('MREADTIM',10)
EXEC DBMS_STATS.SET_SYSTEM_STATS('MAXTHR',19181568) 

The initial table setup script follows, expect this script to take one to three hours to complete.  A 10053 and 10046 trace will be enabled, just to see if we receive different performance behavior from the four databases.  The defaults for table and index statistics collection will be used in the script, the SGA_TARGET will be set to 8000M, and the PGA_AGGREGATE_TARGET will be set to 1800M for the test runs.  (The redo logs were left at the default of 50MB in the 10.2.0.5 Standard Edition Database, while the other databases had five 512MB redo logs – this will affect the results of this script that are displayed later in this article.  Archiving of redo logs was disabled in all databases.)

SET ARRAYSIZE 1000

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'CREATE_TABLES';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

CREATE TABLE LOCATIONS(
  LOCATION_ID VARCHAR2(15) NOT NULL ENABLE,
  WAREHOUSE_ID VARCHAR2(15) NOT NULL ENABLE,
  DESCRIPTION VARCHAR2(80),
  LOCATION_TYPE CHAR(1) NOT NULL ENABLE,
  CONSTRAINT "CHK_LOCATIONS" CHECK (
    (LOCATION_TYPE = 'T' Or LOCATION_TYPE = 'R' Or LOCATION_TYPE = 'F')) ENABLE,
  PRIMARY KEY (WAREHOUSE_ID, LOCATION_ID));

CREATE INDEX IND_LOCATIONS_1 ON LOCATIONS (LOCATION_ID);

CREATE TABLE UMS (
  UNIT_OF_MEASURE VARCHAR2(15) NOT NULL ENABLE,
  DESCRIPTION VARCHAR2(40),
  UOM_SCALE NUMBER NOT NULL ENABLE,
  CONSTRAINT "CHK_UOM_SCALE" CHECK (
    (UOM_SCALE >= 0 And UOM_SCALE <= 4)) ENABLE,
  PRIMARY KEY ("UNIT_OF_MEASURE"));

CREATE TABLE VENDORS (
  VENDOR_ID VARCHAR2(15) NOT NULL ENABLE,
  VENDOR_NAME VARCHAR2(50),
  ADDR_1 VARCHAR2(50),
  ADDR_2 VARCHAR2(50),
  ADDR_3 VARCHAR2(50),
  CITY VARCHAR2(30),
  STATE VARCHAR2(10),
  ZIPCODE VARCHAR2(10),
  COUNTRY VARCHAR2(50),
  CONTACT_FIRST_NAME VARCHAR2(30),
  CONTACT_LAST_NAME VARCHAR2(30),
  CONTACT_INITIAL VARCHAR2(2),
  CONTACT_POSITION VARCHAR2(20),
  CONTACT_HONORIFIC VARCHAR2(4),
  CONTACT_SALUTATION VARCHAR2(60),
  CONTACT_PHONE VARCHAR2(20),
  CONTACT_FAX VARCHAR2(20),
  REMIT_TO_NAME VARCHAR2(50),
  REMIT_TO_ADDR_1 VARCHAR2(50),
  REMIT_TO_ADDR_2 VARCHAR2(50),
  REMIT_TO_ADDR_3 VARCHAR2(50),
  REMIT_TO_CITY VARCHAR2(30),
  REMIT_TO_STATE VARCHAR2(10),
  REMIT_TO_ZIPCODE VARCHAR2(10),
  REMIT_TO_COUNTRY VARCHAR2(50),
  FREE_ON_BOARD VARCHAR2(25),
  SHIP_VIA VARCHAR2(40),
  BUYER VARCHAR2(15),
  REPORT_1099_MISC CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  TERMS_NET_TYPE CHAR(1) DEFAULT ' ' NOT NULL ENABLE,
  TERMS_NET_DAYS NUMBER,
  TERMS_NET_DATE DATE,
  TERMS_DISC_TYPE CHAR(1) DEFAULT ' ' NOT NULL ENABLE,
  TERMS_DISC_DAYS NUMBER,
  TERMS_DISC_DATE DATE,
  TERMS_DISC_PERCENT NUMBER(5,3),
  TERMS_DESCRIPTION VARCHAR2(50),
  USER_1 VARCHAR2(80),
  USER_2 VARCHAR2(80),
  USER_3 VARCHAR2(80),
  USER_4 VARCHAR2(80),
  USER_5 VARCHAR2(80),
  USER_6 VARCHAR2(80),
  USER_7 VARCHAR2(80),
  USER_8 VARCHAR2(80),
  USER_9 VARCHAR2(80),
  USER_10 VARCHAR2(80),
  CONSTRAINT "CHK_VENDORS" CHECK (
    (REPORT_1099_MISC = 'Y' Or REPORT_1099_MISC = 'N')
    AND (TERMS_NET_TYPE = 'A'
      Or TERMS_NET_TYPE = 'M'
      Or TERMS_NET_TYPE = 'D'
      Or TERMS_NET_TYPE = 'N'
      Or TERMS_NET_TYPE = 'E')
    AND (TERMS_DISC_TYPE = 'A'
      Or TERMS_DISC_TYPE = 'M'
      Or TERMS_DISC_TYPE = 'D'
      Or TERMS_DISC_TYPE = 'N'
      Or TERMS_DISC_TYPE = 'E')) ENABLE,
  PRIMARY KEY (VENDOR_ID));

CREATE TABLE VENDORS_TEMP AS
SELECT
  *
FROM
  VENDORS;

CREATE TABLE PARTS (
  PART_ID VARCHAR2(30) NOT NULL ENABLE,
  DESCRIPTION VARCHAR2(40),
  STOCK_UM VARCHAR2(15) NOT NULL ENABLE,
  PLANNING_LEADTIME NUMBER DEFAULT 0 NOT NULL ENABLE,
  ORDER_POLICY CHAR(1) DEFAULT 'M' NOT NULL ENABLE,
  ORDER_POINT NUMBER(14,4),
  SAFETY_STOCK_QTY NUMBER(14,4),
  FIXED_ORDER_QTY NUMBER(14,4),
  DAYS_OF_SUPPLY NUMBER,
  MINIMUM_ORDER_QTY NUMBER(14,4),
  MAXIMUM_ORDER_QTY NUMBER(14,4),
  ENGINEERING_MSTR VARCHAR2(3),
  PRODUCT_CODE VARCHAR2(15),
  COMMODITY_CODE VARCHAR2(15),
  MFG_NAME VARCHAR2(30),
  MFG_PART_ID VARCHAR2(30),
  FABRICATED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  PURCHASED CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,
  STOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  DETAIL_ONLY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  DEMAND_HISTORY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  TOOL_OR_FIXTURE CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  INSPECTION_REQD CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  WEIGHT NUMBER(14,4),
  WEIGHT_UM VARCHAR2(15),
  DRAWING_ID VARCHAR2(15),
  DRAWING_REV_NO VARCHAR2(8),
  PREF_VENDOR_ID VARCHAR2(15),
  PRIMARY_WHS_ID VARCHAR2(15),
  PRIMARY_LOC_ID VARCHAR2(15),
  BACKFLUSH_WHS_ID VARCHAR2(15),
  BACKFLUSH_LOC_ID VARCHAR2(15),
  INSPECT_WHS_ID VARCHAR2(15),
  INSPECT_LOC_ID VARCHAR2(15),
  MRP_REQUIRED CHAR(1) DEFAULT 'N',
  MRP_EXCEPTIONS CHAR(1) DEFAULT 'N',
  PRIVATE_UM_CONV CHAR(1) DEFAULT 'N',
  AUTO_BACKFLUSH CHAR(1) DEFAULT 'Y',
  PLANNER_USER_ID VARCHAR2(20),
  BUYER_USER_ID VARCHAR2(20),
  ABC_CODE CHAR(1),
  ANNUAL_USAGE_QTY NUMBER(15,4),
  INVENTORY_LOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  UNIT_MATERIAL_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_LABOR_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_BURDEN_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_SERVICE_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
  BURDEN_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  PURC_BUR_PERCENT NUMBER(6,3) DEFAULT 0 NOT NULL ENABLE,
  PURC_BUR_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  UNIT_PRICE NUMBER(20,6),
  NEW_MATERIAL_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_LABOR_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_SERVICE_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_PERUNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  NEW_FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  MAT_GL_ACCT_ID VARCHAR2(30),
  LAB_GL_ACCT_ID VARCHAR2(30),
  BUR_GL_ACCT_ID VARCHAR2(30),
  SER_GL_ACCT_ID VARCHAR2(30),
  QTY_ON_HAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_AVAILABLE_ISS NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_AVAILABLE_MRP NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_ON_ORDER NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_IN_DEMAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  USER_1 VARCHAR2(80),
  USER_2 VARCHAR2(80),
  USER_3 VARCHAR2(80),
  USER_4 VARCHAR2(80),
  USER_5 VARCHAR2(80),
  USER_6 VARCHAR2(80),
  USER_7 VARCHAR2(80),
  USER_8 VARCHAR2(80),
  USER_9 VARCHAR2(80),
  USER_10 VARCHAR2(80),
  LT_PLUS_DAYS NUMBER,
  LT_MINUS_DAYS NUMBER,
  STATUS CHAR(1),
  USE_SUPPLY_BEF_LT CHAR(1),
  QTY_COMMITTED NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  PRT_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,
  PRT_CREATE_DATE DATE DEFAULT SYSDATE,
  CONSTRAINT "CHK_PART1" CHECK (
    (PLANNING_LEADTIME >= 0)
    AND (ORDER_POLICY = 'N'
      Or ORDER_POLICY = 'M'
      Or ORDER_POLICY = 'F'
      Or ORDER_POLICY = 'E'
      Or ORDER_POLICY = 'D'
      Or ORDER_POLICY = 'P')
    AND (ORDER_POINT >= 0)
    AND (SAFETY_STOCK_QTY >= 0)
    AND (FIXED_ORDER_QTY >= 0)
    AND (DAYS_OF_SUPPLY >= 0)
    AND (MINIMUM_ORDER_QTY >= 0)
    AND (MAXIMUM_ORDER_QTY >= 0)
    AND (FABRICATED = 'Y' Or FABRICATED = 'N')
    AND (PURCHASED = 'Y' Or PURCHASED = 'N')
    AND (STOCKED = 'Y' Or STOCKED = 'N')
    AND (DETAIL_ONLY = 'Y' Or DETAIL_ONLY = 'N')
    AND (DEMAND_HISTORY = 'Y' Or DEMAND_HISTORY = 'N')
    AND (TOOL_OR_FIXTURE = 'Y' Or TOOL_OR_FIXTURE = 'N')
    AND (MRP_REQUIRED = 'Y' Or MRP_REQUIRED = 'N')
    AND (MRP_EXCEPTIONS = 'Y' Or MRP_EXCEPTIONS = 'N')
    AND (PRIVATE_UM_CONV = 'Y' Or PRIVATE_UM_CONV = 'N')
    AND (INVENTORY_LOCKED = 'Y' Or INVENTORY_LOCKED = 'N')
    AND (INSPECTION_REQD = 'Y' Or INSPECTION_REQD = 'N')) ENABLE,
  PRIMARY KEY (PART_ID),
  CONSTRAINT "FKEY_INSP" FOREIGN KEY (INSPECT_WHS_ID, INSPECT_LOC_ID)
    REFERENCES LOCATIONS (WAREHOUSE_ID, LOCATION_ID) ENABLE,
  CONSTRAINT "FKEY_PREF_VENDOR" FOREIGN KEY (PREF_VENDOR_ID)
    REFERENCES VENDORS (VENDOR_ID) ENABLE,
  CONSTRAINT "FKEY_UM" FOREIGN KEY (WEIGHT_UM)
    REFERENCES UMS (UNIT_OF_MEASURE) ENABLE,
  CONSTRAINT "FKEY_STOCK_UM" FOREIGN KEY (STOCK_UM)
    REFERENCES UMS (UNIT_OF_MEASURE) ENABLE);

CREATE INDEX IND_PARTS_1 ON PARTS (MRP_EXCEPTIONS);
CREATE INDEX IND_PARTS_2 ON PARTS (MFG_NAME, MFG_PART_ID);
CREATE INDEX IND_PARTS_3 ON PARTS (WEIGHT_UM);
CREATE INDEX IND_PARTS_4 ON PARTS (MRP_REQUIRED);
CREATE INDEX IND_PARTS_5 ON PARTS (PREF_VENDOR_ID);
CREATE INDEX IND_PARTS_6 ON PARTS (STOCK_UM);
CREATE INDEX IND_PARTS_7 ON PARTS (ORDER_POINT);

CREATE TABLE PARTS_TEMP AS
SELECT
  *
FROM
  PARTS;

CREATE TABLE PO_HEADER (
  PURC_ORDER_ID VARCHAR2(15) NOT NULL ENABLE,
  VENDOR_ID VARCHAR2(15) NOT NULL ENABLE,
  CONTACT_FIRST_NAME VARCHAR2(30),
  CONTACT_LAST_NAME VARCHAR2(30),
  CONTACT_INITIAL VARCHAR2(2),
  CONTACT_POSITION VARCHAR2(20),
  CONTACT_HONORIFIC VARCHAR2(4),
  CONTACT_SALUTATION VARCHAR2(60),
  CONTACT_PHONE VARCHAR2(20),
  CONTACT_FAX VARCHAR2(20),
  PURC_ORD_ADDR_NO NUMBER,
  SHIPTO_ADDR_NO NUMBER,
  ORDER_DATE DATE DEFAULT SYSDATE NOT NULL ENABLE,
  DESIRED_RECV_DATE DATE DEFAULT TRUNC(SYSDATE),
  BUYER VARCHAR2(15),
  FREE_ON_BOARD VARCHAR2(25),
  SHIP_VIA VARCHAR2(40),
  SALES_TAX_GROUP_ID VARCHAR2(15),
  PO_STATUS CHAR(1) DEFAULT ' ' NOT NULL ENABLE,
  BACK_ORDER CHAR(1) DEFAULT ' ' NOT NULL ENABLE,
  SELL_RATE NUMBER(15,8) NOT NULL ENABLE,
  BUY_RATE NUMBER(15,8) NOT NULL ENABLE,
  ENTITY_ID VARCHAR2(5) NOT NULL ENABLE,
  POSTING_CANDIDATE CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,
  LAST_RECEIVED_DATE DATE,
  TOTAL_AMT_ORDERED NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  TOTAL_AMT_RECVD NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  MARKED_FOR_PURGE CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  EXCH_RATE_FIXED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  PROMISE_DATE DATE,
  PRINTED_DATE DATE,
  TERMS_DISC_TYPE CHAR(1),
  EDI_BLANKET_FLAG CHAR(1),
  EDI_BLANKET_PO_NO VARCHAR2(30),
  CONTRACT_ID VARCHAR2(30),
  SHIPTO_ID VARCHAR2(20),
  TERMS_NET_TYPE CHAR(1) DEFAULT ' ' NOT NULL ENABLE,
  TERMS_NET_DAYS NUMBER,
  TERMS_NET_DATE DATE,
  TERMS_DISC_DAYS NUMBER,
  TERMS_DISC_DATE DATE,
  TERMS_DISC_PERCENT NUMBER(5,3),
  TERMS_DESCRIPTION VARCHAR2(50),
  CURRENCY_ID VARCHAR2(15),
  WAREHOUSE_ID VARCHAR2(15),
  CREATE_DATE DATE DEFAULT SYSDATE NOT NULL ENABLE,
  CONTACT_MOBILE VARCHAR2(20),
  CONTACT_EMAIL VARCHAR2(50),
  USER_1 VARCHAR2(80),
  USER_2 VARCHAR2(80),
  USER_3 VARCHAR2(80),
  USER_4 VARCHAR2(80),
  USER_5 VARCHAR2(80),
  USER_6 VARCHAR2(80),
  USER_7 VARCHAR2(80),
  USER_8 VARCHAR2(80),
  USER_9 VARCHAR2(80),
  USER_10 VARCHAR2(80),
  UDF_LAYOUT_ID VARCHAR2(15),
  PO_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,
  CONSTRAINT "CHK_PO" CHECK (
    (PO_STATUS = 'F' Or PO_STATUS = 'R' Or PO_STATUS = 'C' Or PO_STATUS = 'X')
    AND (BACK_ORDER = 'Y' Or BACK_ORDER = 'N')
    AND (POSTING_CANDIDATE = 'Y' Or POSTING_CANDIDATE = 'N')
    AND (MARKED_FOR_PURGE = 'Y' Or MARKED_FOR_PURGE = 'N')
    AND (TERMS_DISC_TYPE = 'A' Or TERMS_DISC_TYPE = 'M' Or TERMS_DISC_TYPE = 'D' Or TERMS_DISC_TYPE = 'N' Or TERMS_DISC_TYPE = 'E')
    AND (TERMS_NET_TYPE = 'A' Or TERMS_NET_TYPE = 'M' Or TERMS_NET_TYPE = 'D' Or TERMS_NET_TYPE = 'N' Or TERMS_NET_TYPE = 'E')) ENABLE,
  PRIMARY KEY (PURC_ORDER_ID));

CREATE INDEX IND_PO_HEADER_1 ON PO_HEADER (VENDOR_ID, PURC_ORD_ADDR_NO);
CREATE INDEX IND_PO_HEADER_2 ON PO_HEADER (VENDOR_ID);
CREATE INDEX IND_PO_HEADER_3 ON PO_HEADER (SHIPTO_ADDR_NO);
CREATE INDEX IND_PO_HEADER_4 ON PO_HEADER (POSTING_CANDIDATE);

CREATE TABLE PO_HEADER_TEMP AS
SELECT
  *
FROM
  PO_HEADER;

CREATE TABLE PO_LINE (
  PURC_ORDER_ID VARCHAR2(15) NOT NULL ENABLE,
  LINE_NO NUMBER NOT NULL ENABLE,
  PART_ID VARCHAR2(30),
  VENDOR_PART_ID VARCHAR2(30),
  SERVICE_ID VARCHAR2(15),
  USER_ORDER_QTY NUMBER(14,4) NOT NULL ENABLE,
  ORDER_QTY NUMBER(14,4) NOT NULL ENABLE,
  PURCHASE_UM VARCHAR2(15),
  UNIT_PRICE NUMBER(20,6) NOT NULL ENABLE,
  TRADE_DISC_PERCENT NUMBER(6,3) DEFAULT 0 NOT NULL ENABLE,
  FIXED_CHARGE NUMBER(15,2),
  EST_FREIGHT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  GL_EXPENSE_ACCT_ID VARCHAR2(30),
  SALES_TAX_GROUP_ID VARCHAR2(15),
  PRODUCT_CODE VARCHAR2(15),
  COMMODITY_CODE VARCHAR2(15),
  DESIRED_RECV_DATE DATE,
  LINE_STATUS CHAR(1) DEFAULT ' ' NOT NULL ENABLE,
  LAST_RECEIVED_DATE DATE,
  TOTAL_ACT_FREIGHT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  TOTAL_USR_RECD_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  TOTAL_RECEIVED_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  TOTAL_AMT_RECVD NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  TOTAL_AMT_ORDERED NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  MFG_NAME VARCHAR2(30),
  MFG_PART_ID VARCHAR2(30),
  PROMISE_DATE DATE,
  PIECE_COUNT NUMBER(14,4),
  LENGTH NUMBER(14,4),
  WIDTH NUMBER(14,4),
  HEIGHT NUMBER(14,4),
  DIMENSIONS_UM VARCHAR2(15),
  VAT_CODE VARCHAR2(15),
  TOTAL_DISPATCH_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  TOTAL_USR_DISP_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  MINIMUM_CHARGE NUMBER(15,2),
  LAST_DISPATCH_DATE DATE,
  EDI_BLANKET_QTY NUMBER(14,4),
  EDI_BLANKET_USRQTY NUMBER(14,4),
  EDI_ACCUM_QTY_REL NUMBER(14,4),
  EDI_ACCUM_USR_REL NUMBER(14,4),
  EDI_ACCUM_QTY_REC NUMBER(14,4),
  EDI_ACCUM_USR_REC NUMBER(14,4),
  EDI_LAST_REC_DATE DATE,
  EDI_RELEASE_NO VARCHAR2(3),
  EDI_RELEASE_DATE DATE,
  EDI_QTY_RELEASED NUMBER(14,4),
  EDI_USR_QTY_REL NUMBER(14,4),
  EDI_REQ_REL_DATE DATE,
  SHIPTO_ID VARCHAR2(20),
  WAREHOUSE_ID VARCHAR2(15),
  WIP_VAS_REQUIRED CHAR(1),
  ALLOCATED_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  FULFILLED_QTY NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  HTS_CODE VARCHAR2(20),
  ORIG_COUNTRY_ID VARCHAR2(15),
  USER_1 VARCHAR2(80),
  USER_2 VARCHAR2(80),
  USER_3 VARCHAR2(80),
  USER_4 VARCHAR2(80),
  USER_5 VARCHAR2(80),
  USER_6 VARCHAR2(80),
  USER_7 VARCHAR2(80),
  USER_8 VARCHAR2(80),
  USER_9 VARCHAR2(80),
  USER_10 VARCHAR2(80),
  UDF_LAYOUT_ID VARCHAR2(15),
  POL_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,
  POL_CREATE_DATE DATE DEFAULT SYSDATE,
  CONSTRAINT "CHK_PO_LINE" CHECK ((LINE_STATUS = 'A' Or LINE_STATUS = 'C')) ENABLE,
  PRIMARY KEY (PURC_ORDER_ID, LINE_NO),
  CONSTRAINT "FKEY_PO_HEADER" FOREIGN KEY (PURC_ORDER_ID)
    REFERENCES PO_HEADER (PURC_ORDER_ID) ON DELETE CASCADE ENABLE,
  CONSTRAINT "FKEY_PART_ID" FOREIGN KEY (PART_ID)
    REFERENCES PARTS (PART_ID) ENABLE,
  CONSTRAINT "FKEY_PURC_UM" FOREIGN KEY (PURCHASE_UM)
   REFERENCES UMS (UNIT_OF_MEASURE) ENABLE);

CREATE INDEX IND_PO_LINE_1 ON PO_LINE (WAREHOUSE_ID);
CREATE INDEX IND_PO_LINE_2 ON PO_LINE (SERVICE_ID);
CREATE INDEX IND_PO_LINE_3 ON PO_LINE (PART_ID);
CREATE INDEX IND_PO_LINE_4 ON PO_LINE (VENDOR_PART_ID);

CREATE TABLE PO_LINE_TEMP AS
SELECT
  *
FROM
  PO_LINE;

INSERT INTO
  LOCATIONS
SELECT /*+ ORDERED */
  LOC.LOCATION_ID,
  WH.WAREHOUSE_ID,
  RPAD(WH.WAREHOUSE_ID||'-'||LOC.LOCATION_ID,60),
  DECODE(MOD(ROWNUM,5),0,'T',1,'R','F')
FROM
  (SELECT
    TRIM(TO_CHAR(ABS(ROUND(COS(ROWNUM*3.1415/180*1.2)*1000000,0))))||'LOC' LOCATION_ID,
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=200) LOC,
  (SELECT
    TRIM(TO_CHAR(ABS(ROUND(SIN(ROWNUM*3.1415/180*10.1)*1000000,0))))||'WH' WAREHOUSE_ID,
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=20) WH
WHERE
  (MOD(WH.RN,10)*20+1) <= LOC.RN;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'LOCATIONS',CASCADE=>TRUE);

INSERT INTO
  UMS
SELECT
  DECODE(ROWNUM,1,'EA',2,'PC',3,'FT',4,'METER',5,'KG',6,'CASE',7,'LBS',8,'DOZEN'),
  NULL,
  4
FROM
  DUAL
CONNECT BY
  LEVEL<=8;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'UMS',CASCADE=>TRUE);

INSERT INTO
  VENDORS_TEMP
SELECT
  TRIM(TO_CHAR(ABS(ROUND(COS(ROWNUM*3.14159265/180*51.491976)*10000000,0))))||'VEN' VENDOR_ID,
  TRIM(TO_CHAR(ABS(ROUND(COS(ROWNUM*3.1415/180*.49)*1000000,0))))||'VENDOR NAME' VENDOR_NAME,
  RPAD('ADDR_1',40) ADDR_1,
  RPAD('ADDR_2',35) ADDR_2,
  NULL ADDR_3,
  RPAD('CITY',20) CITY,
  'CA' STATE,
  LPAD(TO_CHAR(ROWNUM),6) ZIPCODE,
  'NONE' COUNTRY,
  NULL CONTACT_FIRST_NAME,
  NULL CONTACT_LAST_NAME,
  NULL CONTACT_INITIAL,
  NULL CONTACT_POSITION,
  NULL CONTACT_HONORIFIC,
  NULL CONTACT_SALUTATION,
  NULL CONTACT_PHONE,
  NULL CONTACT_FAX,
  TRIM(TO_CHAR(ABS(ROUND(COS(ROWNUM*3.1415/180*4.491976)*1000000,0))))||'VENDOR NAME' REMIT_TO_NAME,
  RPAD('ADDR_1',40) REMIT_TO_ADDR_1,
  RPAD('ADDR_2',35) REMIT_TO_ADDR_2,
  NULL REMIT_TO_ADDR_3,
  RPAD('CITY',20) REMIT_TO_CITY,
  'CA' REMIT_TO_STATE,
  LPAD(TO_CHAR(ROWNUM),6) REMIT_TO_ZIPCODE,
  'NONE' REMIT_TO_COUNTRY,
  'NONE' FREE_ON_BOARD,
  'SPECIAL DEL' SHIP_VIA,
  'UNKNOWN' BUYER,
  'N' REPORT_1099_MISC,
  DECODE(MOD(ROWNUM,6),0,'A',1,'M',2,'D',3,'N','E') TERMS_NET_TYPE,
  ROWNUM TERMS_NET_DAYS,
  NULL TERMS_NET_DATE,
  DECODE(MOD(ROWNUM,6),0,'A',1,'M',2,'D',3,'N','E') TERMS_DISC_TYPE,
  MOD(ROWNUM,100)+10 TERMS_DISC_DAYS,
  NULL TERMS_DISC_DATE,
  3.5 TERMS_DISC_PERCENT,
  'STANDARD' TERMS_DESCRIPTION,
  'X' USER_1,
  TO_CHAR(TRUNC(SYSDATE,'YYYY'),'MON DD, YYYY') USER_2,
  NULL USER_3,
  NULL USER_4,
  NULL USER_5,
  NULL USER_6,
  NULL USER_7,
  NULL USER_8,
  NULL USER_9,
  NULL USER_10
FROM
  DUAL
CONNECT BY
  LEVEL<=50000;

DELETE FROM
  VENDORS_TEMP
WHERE
  (VENDOR_ID,TERMS_NET_DAYS) IN
    (SELECT
      V.VENDOR_ID,
      V.TERMS_NET_DAYS
    FROM
      VENDORS_TEMP V,
      (SELECT
        VENDOR_ID,
        MIN(TERMS_NET_DAYS) TERMS_NET_DAYS
      FROM
        VENDORS_TEMP
      GROUP BY
        VENDOR_ID
      HAVING
        COUNT(*)>1) M
    WHERE
      V.VENDOR_ID=M.VENDOR_ID
      AND V.TERMS_NET_DAYS>M.TERMS_NET_DAYS);

INSERT INTO
  VENDORS
SELECT
  *
FROM
  VENDORS_TEMP;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'VENDORS',CASCADE=>TRUE);

INSERT INTO
  PARTS_TEMP
SELECT
  TRIM(TO_CHAR(ABS(ROUND(SIN(ROWNUM*3.14159265/180*10.191976)*10000000,0))))||'PART' PART_ID,
  TRIM(TO_CHAR(ABS(ROUND(SIN(ROWNUM*3.14159265/180*10.191976)*10000000,0))))||'DESCRIPTION' DESCRIPTION,
  DECODE(MOD(ROWNUM,20),2,'PC',3,'FT',4,'METER',5,'KG',6,'CASE',7,'LBS',8,'DOZEN','EA') STOCK_UM,
  1 PLANNING_LEADTIME,
  'M' ORDER_POLICY,
  ROWNUM ORDER_POINT,
  1 SAFETY_STOCK_QTY,
  1 FIXED_ORDER_QTY,
  1 DAYS_OF_SUPPLY,
  1 MINIMUM_ORDER_QTY,
  9999 MAXIMUM_ORDER_QTY,
  '0' ENGINEERING_MSTR,
  DECODE(MOD(ROWNUM,20),1,'SHOP',2,'OFFICE',3,'JANITOR',4,'INVENTORY',5,'INVENTORY','FG') PRODUCT_CODE,
  DECODE(MOD(ROWNUM,7),1,'SHOP',2,'OFFICE',3,'JANITOR',4,'INVENTORY',5,'INVENTORY','FG') COMMODITY_CODE,
  'UNKNOWN' MFG_NAME,
  'UNKNOWN' MFG_PART_ID,
  DECODE(MOD(ROWNUM,3),1,'Y','N') FABRICATED,
  DECODE(MOD(ROWNUM,3),1,'N','Y') PURCHASED,
  'N' STOCKED,
  'N' DETAIL_ONLY,
  'N' DEMAND_HISTORY,
  'N' TOOL_OR_FIXTURE,
  'N' INSPECTION_REQD,
  0 WEIGHT,
  DECODE(MOD(ROWNUM,20),2,'PC',3,'FT',4,'METER',5,'KG',6,'CASE',7,'LBS',8,'DOZEN','EA') WEIGHT_UM,
  NULL DRAWING_ID,
  NULL DRAWING_REV_NO,
  NULL PREF_VENDOR_ID,
  NULL PRIMARY_WHS_ID,
  NULL PRIMARY_LOC_ID,
  NULL BACKFLUSH_WHS_ID,
  NULL BACKFLUSH_LOC_ID,
  NULL INSPECT_WHS_ID,
  NULL INSPECT_LOC_ID,
  'Y' MRP_REQUIRED,
  'N' MRP_EXCEPTIONS,
  'N' PRIVATE_UM_CONV,
  'Y' AUTO_BACKFLUSH,
  NULL PLANNER_USER_ID,
  NULL BUYER_USER_ID,
  DECODE(MOD(ROWNUM,7),1,'A',2,'B',3,'B','C') ABC_CODE,
  ROWNUM-100000 ANNUAL_USAGE_QTY,
  'N' INVENTORY_LOCKED,
  0 UNIT_MATERIAL_COST,
  0 UNIT_LABOR_COST,
  0 UNIT_BURDEN_COST,
  0 UNIT_SERVICE_COST,
  0 BURDEN_PERCENT,
  0 BURDEN_PER_UNIT,
  0 PURC_BUR_PERCENT,
  0 PURC_BUR_PER_UNIT,
  0 FIXED_COST,
  0 UNIT_PRICE,
  0 NEW_MATERIAL_COST,
  0 NEW_LABOR_COST,
  0 NEW_BURDEN_COST,
  0 NEW_SERVICE_COST,
  0 NEW_BURDEN_PERCENT,
  0 NEW_BURDEN_PERUNIT,
  0 NEW_FIXED_COST,
  '1111111' MAT_GL_ACCT_ID,
  '2222222' LAB_GL_ACCT_ID,
  '3333333' BUR_GL_ACCT_ID,
  '4444444' SER_GL_ACCT_ID,
  ABS(ROUND(SIN(ROWNUM*3.14159265/180*2)*100000,3)) QTY_ON_HAND,
  ABS(ROUND(SIN(ROWNUM*3.14159265/180*2)*100000,3)) QTY_AVAILABLE_ISS,
  ABS(ROUND(SIN(ROWNUM*3.14159265/180*2)*100000,3)) QTY_AVAILABLE_MRP,
  0 QTY_ON_ORDER,
  0 QTY_IN_DEMAND,
  RPAD('USER_1',30) USER_1,
  RPAD('USER_2',30) USER_2,
  RPAD('USER_3',30) USER_3,
  NULL USER_4,
  NULL USER_5,
  NULL USER_6,
  NULL USER_7,
  NULL USER_8,
  NULL USER_9,
  NULL USER_10,
  0 LT_PLUS_DAYS,
  0 LT_MINUS_DAYS,
  'A' STATUS,
  'Y' USE_SUPPLY_BEF_LT,
  0 QTY_COMMITTED,
  'TESTING' PRT_CREATE_USER_ID,
  SYSDATE PRT_CREATE_DATE
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

DELETE FROM
  PARTS_TEMP
WHERE
  (PART_ID,ORDER_POINT) IN
    (SELECT
      V.PART_ID,
      V.ORDER_POINT
    FROM
      PARTS_TEMP V,
      (SELECT
        PART_ID,
        MIN(ORDER_POINT) ORDER_POINT
      FROM
        PARTS_TEMP
      GROUP BY
        PART_ID
      HAVING
        COUNT(*)>1) M
    WHERE
      V.PART_ID=M.PART_ID
      AND V.ORDER_POINT>M.ORDER_POINT);

INSERT INTO
  PARTS
SELECT
  *
FROM
  PARTS_TEMP;

UPDATE
  PARTS P
SET
  (PRIMARY_WHS_ID,PRIMARY_LOC_ID)=(
    SELECT
      WAREHOUSE_ID,
      LOCATION_ID
    FROM
      (SELECT
        WAREHOUSE_ID,
        LOCATION_ID,
        ROWNUM RN
      FROM
        LOCATIONS)
    WHERE
      MOD(P.ORDER_POINT,2000)=RN);

UPDATE
  PARTS
SET
  PREF_VENDOR_ID=TRIM(TO_CHAR(ABS(ROUND(COS((MOD(ROWNUM,9000)*2+1)*3.14159265/180*51.491976)*10000000,0))))||'VEN'
WHERE
  PURCHASED='Y';

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'PARTS',CASCADE=>TRUE);

INSERT INTO
  PO_HEADER_TEMP
SELECT
  'PO'||TO_CHAR(ROWNUM) PURC_ORDER_ID,
  TRIM(TO_CHAR(ABS(ROUND(COS((MOD(ROWNUM,9000)*2+1)*3.14159265/180*51.491976)*10000000,0))))||'VEN' VENDOR_ID,
  NULL CONTACT_FIRST_NAME,
  NULL CONTACT_LAST_NAME,
  NULL CONTACT_INITIAL,
  NULL CONTACT_POSITION,
  NULL CONTACT_HONORIFIC,
  NULL CONTACT_SALUTATION,
  NULL CONTACT_PHONE,
  NULL CONTACT_FAX,
  1 PURC_ORD_ADDR_NO,
  1 SHIPTO_ADDR_NO,
  TRUNC(SYSDATE-(COS(ROWNUM*3.14159265/180)*1000)) ORDER_DATE,
  TRUNC(SYSDATE-(COS(ROWNUM*3.14159265/180)*1000))+10 DESIRED_RECV_DATE,
  'MY_BUYER' BUYER,
  NULL FREE_ON_BOARD,
  'BEST WAY' SHIP_VIA,
  'REGULAR' SALES_TAX_GROUP_ID,
  DECODE(MOD(ROWNUM,6),1,'F',2,'R',3,'X','C') PO_STATUS,
  'N' BACK_ORDER,
  1 SELL_RATE,
  1 BUY_RATE,
  '1' ENTITY_ID,
  DECODE(MOD(ROWNUM,3),1,'Y','N') POSTING_CANDIDATE,
  NULL LAST_RECEIVED_DATE,
  0 TOTAL_AMT_ORDERED,
  0 TOTAL_AMT_RECVD,
  'N' MARKED_FOR_PURGE,
  'Y' EXCH_RATE_FIXED,
  TRUNC(SYSDATE-(COS(ROWNUM*3.14159265/180)*1000))+10 PROMISE_DATE,
  SYSDATE PRINTED_DATE,
  DECODE(MOD(ROWNUM,6),0,'A',1,'M',2,'D',3,'N','E') TERMS_DISC_TYPE,
  NULL EDI_BLANKET_FLAG,
  NULL EDI_BLANKET_PO_NO,
  1 CONTRACT_ID,
  1 SHIPTO_ID,
  DECODE(MOD(ROWNUM,6),0,'A',1,'M',2,'D',3,'N','E') TERMS_NET_TYPE,
  1 TERMS_NET_DAYS,
  NULL TERMS_NET_DATE,
  1 TERMS_DISC_DAYS,
  NULL TERMS_DISC_DATE,
  3 TERMS_DISC_PERCENT,
  'ON TIME' TERMS_DESCRIPTION,
  'USD' CURRENCY_ID,
  NULL WAREHOUSE_ID,
  SYSDATE CREATE_DATE,
  NULL CONTACT_MOBILE,
  NULL CONTACT_EMAIL,
  NULL USER_1,
  NULL USER_2,
  NULL USER_3,
  NULL USER_4,
  NULL USER_5,
  NULL USER_6,
  NULL USER_7,
  NULL USER_8,
  NULL USER_9,
  NULL USER_10,
  'DEFAULT' UDF_LAYOUT_ID,
  'TESTING' PO_CREATE_USER_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

INSERT INTO
  PO_HEADER
SELECT
  *
FROM
  PO_HEADER_TEMP;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'PO_HEADER',CASCADE=>TRUE);

INSERT INTO PO_LINE_TEMP (
  PURC_ORDER_ID,
  LINE_NO,
  PART_ID,
  VENDOR_PART_ID,
  SERVICE_ID,
  USER_ORDER_QTY,
  ORDER_QTY,
  PURCHASE_UM,
  UNIT_PRICE,
  FIXED_CHARGE,
  GL_EXPENSE_ACCT_ID,
  SALES_TAX_GROUP_ID,
  PRODUCT_CODE,
  COMMODITY_CODE,
  DESIRED_RECV_DATE,
  TRADE_DISC_PERCENT,
  EST_FREIGHT,
  LINE_STATUS,
  TOTAL_ACT_FREIGHT,
  TOTAL_USR_RECD_QTY,
  TOTAL_RECEIVED_QTY,
  TOTAL_AMT_RECVD,
  TOTAL_AMT_ORDERED,
  TOTAL_DISPATCH_QTY,
  TOTAL_USR_DISP_QTY,
  ALLOCATED_QTY,
  FULFILLED_QTY)
SELECT /*+ ORDERED */
  PURC_ORDER_ID,
  ORDER_POINT-START_LINE+1,
  PART_ID,
  PART_ID,
  NULL,
  10,
  10,
  DECODE(MOD(ROWNUM,20),2,'PC',3,'FT',4,'METER',5,'KG',6,'CASE',7,'LBS',8,'DOZEN','EA'),
  1099.99,
  0,
  NULL,
  NULL,
  PRODUCT_CODE,
  COMMODITY_CODE,
  TRUNC(SYSDATE-1000+ROWNUM/1000),
  0,
  0,
  'A',
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0
FROM
  (SELECT
    'PO'||TO_CHAR(ROWNUM) PURC_ORDER_ID,
    ROWNUM RN,
    ABS(SIN(ROWNUM*3.14159265/180))*90000 START_LINE,
    MOD(ROWNUM,50)+1 LINES
  FROM
    DUAL
  CONNECT BY
    LEVEL<=500000) POL,
  PARTS P
WHERE
  P.ORDER_POINT BETWEEN START_LINE AND (START_LINE+LINES-1);

INSERT INTO
  PO_LINE
SELECT
  *
FROM
  PO_LINE_TEMP;

COMMIT;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'PO_LINE',CASCADE=>TRUE); 

The second half of the test script, where the generated data is queried, should complete much faster than the table creation portion of the script.  The buffer cache will be flushed at the start of the script, so we might be able to see how the different data access strategies (using the buffer cache or bypassing the buffer cache through direct path reads during full table scans) from previous queries will affect the performance of later queries.  While reviewing the output, watch for changes in the number of blocks read from disk, number of consistent gets, the number of rows returned, elapsed time, and wait event statistics - in some cases those values will differ even if the execution plans are identical.

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SET ARRAYSIZE 1000
SET AUTOTRACE TRACEONLY STATISTICS
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_FROM_TABLES';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT
  PO.VENDOR_ID,
  P.PRODUCT_CODE,
  P.STOCK_UM,
  SUM(POL.ORDER_QTY) ORDER_QTY
FROM
  PO_HEADER PO,
  PO_LINE POL,
  PARTS P
WHERE
  PO.ORDER_DATE BETWEEN TRUNC(SYSDATE-90) AND TRUNC(SYSDATE)
  AND PO.PURC_ORDER_ID=POL.PURC_ORDER_ID
  AND POL.PART_ID=P.PART_ID
GROUP BY
  PO.VENDOR_ID,
  P.PRODUCT_CODE,
  P.STOCK_UM;

SELECT
  POL.PART_ID,
  P.DESCRIPTION,
  MAX(DESIRED_RECV_DATE) LAST_RECEIVE_DATE
FROM
  PO_LINE POL,
  PARTS P
WHERE
  P.PRODUCT_CODE='FG'
  AND P.ABC_CODE='C'
  AND P.PART_ID=POL.PART_ID
GROUP BY
  POL.PART_ID,
  P.DESCRIPTION;

SELECT
  PRODUCT_CODE,
  COUNT(*) PARTS_LARGE_WH
FROM
  (SELECT
    WAREHOUSE_ID
  FROM
    LOCATIONS
  GROUP BY
    WAREHOUSE_ID
  HAVING
    COUNT(*)>160) W,
  PARTS P
WHERE
  W.WAREHOUSE_ID=P.PRIMARY_WHS_ID
GROUP BY
  PRODUCT_CODE
ORDER BY
  PRODUCT_CODE;

SELECT
  COUNT(*)
FROM
  PARTS
WHERE
  QTY_ON_HAND>1000;

SELECT
  COUNT(*)
FROM
  VENDORS
WHERE
  ZIPCODE>' 44444';

SELECT
  COUNT(*)
FROM
  PO_LINE POL,
  PARTS P
WHERE
  POL.PURC_ORDER_ID BETWEEN '10000' AND '20000'
  AND POL.PART_ID=P.PART_ID;

SELECT
  PART_ID,
  ABC_CODE,
  PRODUCT_CODE,
  MAX(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE,ABC_CODE) MAX_QTY_PRD_ABC,
  MIN(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE,ABC_CODE) MIN_QTY_PRD_ABC,
  DENSE_RANK() OVER (PARTITION BY PRODUCT_CODE,ABC_CODE ORDER BY QTY_ON_HAND) DR_QTY_PRD_ABC,
  DENSE_RANK() OVER (PARTITION BY PREF_VENDOR_ID ORDER BY ORDER_POINT) DR_OP_VEND
FROM
  PARTS
ORDER BY
  PART_ID;

SELECT
  V.VENDOR_ID,
  V.VENDOR_NAME
FROM
  VENDORS V,
  (SELECT DISTINCT
    PO.VENDOR_ID
  FROM
    PO_HEADER PO,
    PO_LINE POL,
    PARTS P
  WHERE
    PO.PURC_ORDER_ID=POL.PURC_ORDER_ID
    AND POL.PART_ID=P.PART_ID
    AND P.PRODUCT_CODE='FG') PV
WHERE
  V.VENDOR_ID=PV.VENDOR_ID(+)
  AND PV.VENDOR_ID IS NULL
ORDER BY
  V.VENDOR_ID;

SELECT
  P.PART_ID,
  P.DESCRIPTION PART_DESCRIPTION,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  U.DESCRIPTION UMS_DESCRIPTION,
  U.UOM_SCALE,
  L.DESCRIPTION LOC_DESCRIPTION,
  V.VENDOR_ID,
  V.VENDOR_NAME,
  V.ADDR_1,
  V.ADDR_2,
  V.CITY,
  V.STATE
FROM
  UMS U,
  LOCATIONS L,
  PARTS P,
  VENDORS V
WHERE
  P.PRODUCT_CODE IN ('OFFICE','SHOP')
  AND P.PURCHASED='Y'
  AND P.STOCK_UM=U.UNIT_OF_MEASURE
  AND U.UNIT_OF_MEASURE IN ('EA','PC','CASE')
  AND P.PREF_VENDOR_ID=V.VENDOR_ID
  AND P.PRIMARY_WHS_ID=L.WAREHOUSE_ID
  AND P.PRIMARY_LOC_ID=L.LOCATION_ID;

SELECT
  VENDOR_ID,
  PART_ID,
  DESIRED_RECV_DATE
FROM
  (SELECT
    PO.VENDOR_ID,
    POL.PART_ID,
    POL.DESIRED_RECV_DATE,
    ROW_NUMBER() OVER (PARTITION BY POL.PART_ID ORDER BY POL.DESIRED_RECV_DATE DESC) RN
  FROM
    PO_HEADER PO,
    PO_LINE POL
  WHERE
    PO.PURC_ORDER_ID=POL.PURC_ORDER_ID)
WHERE
  RN=1
ORDER BY
  PART_ID;

SELECT
  P.PART_ID,
  P.DESCRIPTION PART_DESCRIPTION,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  U.DESCRIPTION UMS_DESCRIPTION,
  U.UOM_SCALE,
  L.DESCRIPTION LOC_DESCRIPTION,
  V.VENDOR_ID,
  V.VENDOR_NAME,
  V.ADDR_1,
  V.ADDR_2,
  V.CITY,
  V.STATE,
  LV.VENDOR_ID LAST_VENDOR_ID,
  V2.VENDOR_NAME LAST_VENDOR_NAME,
  V2.STATE LAST_VENDOR_STATE,
  LV.DESIRED_RECV_DATE LAST_DES_RECV_DATE
FROM
  UMS U,
  LOCATIONS L,
  PARTS P,
  VENDORS V,
  (SELECT
    VENDOR_ID,
    PART_ID,
    DESIRED_RECV_DATE
  FROM
    (SELECT
      PO.VENDOR_ID,
      POL.PART_ID,
      POL.DESIRED_RECV_DATE,
      ROW_NUMBER() OVER (PARTITION BY POL.PART_ID ORDER BY POL.DESIRED_RECV_DATE DESC) RN
    FROM
      PO_HEADER PO,
      PO_LINE POL
    WHERE
      PO.PURC_ORDER_ID=POL.PURC_ORDER_ID)
  WHERE
    RN=1) LV,
  VENDORS V2
WHERE
  P.PRODUCT_CODE IN ('OFFICE','SHOP')
  AND P.PURCHASED='Y'
  AND P.STOCK_UM=U.UNIT_OF_MEASURE
  AND U.UNIT_OF_MEASURE IN ('EA','PC','CASE')
  AND P.PREF_VENDOR_ID=V.VENDOR_ID
  AND P.PRIMARY_WHS_ID=L.WAREHOUSE_ID
  AND P.PRIMARY_LOC_ID=L.LOCATION_ID
  AND P.PART_ID=LV.PART_ID
  AND LV.VENDOR_ID=V2.VENDOR_ID;

SELECT
  P.PART_ID,
  P.DESCRIPTION PART_DESCRIPTION,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  U.DESCRIPTION UMS_DESCRIPTION,
  U.UOM_SCALE,
  L.DESCRIPTION LOC_DESCRIPTION,
  V.VENDOR_ID,
  V.VENDOR_NAME,
  V.ADDR_1,
  V.ADDR_2,
  V.CITY,
  V.STATE,
  LV.VENDOR_ID LAST_VENDOR_ID,
  V2.VENDOR_NAME LAST_VENDOR_NAME,
  V2.STATE LAST_VENDOR_STATE,
  LV.DESIRED_RECV_DATE LAST_DES_RECV_DATE
FROM
  UMS U,
  LOCATIONS L,
  PARTS P,
  VENDORS V,
  (SELECT
    VENDOR_ID,
    PART_ID,
    DESIRED_RECV_DATE
  FROM
    (SELECT
      PO.VENDOR_ID,
      POL.PART_ID,
      POL.DESIRED_RECV_DATE,
      ROW_NUMBER() OVER (PARTITION BY POL.PART_ID ORDER BY POL.DESIRED_RECV_DATE DESC) RN
    FROM
      PO_HEADER PO,
      PO_LINE POL
    WHERE
      PO.PURC_ORDER_ID=POL.PURC_ORDER_ID)
  WHERE
    RN=1) LV,
  VENDORS V2
WHERE
  (P.PRODUCT_CODE NOT IN ('OFFICE','SHOP')
     OR U.UNIT_OF_MEASURE NOT IN ('EA','PC','CASE'))
  AND P.PURCHASED='Y'
  AND P.STOCK_UM=U.UNIT_OF_MEASURE
  AND P.PREF_VENDOR_ID=V.VENDOR_ID
  AND P.PRIMARY_WHS_ID=L.WAREHOUSE_ID
  AND P.PRIMARY_LOC_ID=L.LOCATION_ID
  AND P.PART_ID=LV.PART_ID
  AND LV.VENDOR_ID=V2.VENDOR_ID;

SELECT
  P.PART_ID,
  P.DESCRIPTION,
  P.PREF_VENDOR_ID,
  V.VENDOR_NAME PREF_VENDOR_NAME,
  PV.VENDOR_ID,
  V2.VENDOR_NAME LAST_VENDOR_NAME,
  PV.ORDER_LINES,
  PV.FIRST_RECV_DATE,
  PV.LAST_RECV_DATE
FROM
  (SELECT
    PO.VENDOR_ID,
    POL.PART_ID,
    COUNT(*) ORDER_LINES,
    MIN(POL.DESIRED_RECV_DATE) FIRST_RECV_DATE,
    MAX(POL.DESIRED_RECV_DATE) LAST_RECV_DATE
  FROM
    PO_HEADER PO,
    PO_LINE POL
  WHERE
    PO.PURC_ORDER_ID=POL.PURC_ORDER_ID
  GROUP BY
    PO.VENDOR_ID,
    POL.PART_ID) PV,
  PARTS P,
  VENDORS V,
  VENDORS V2
WHERE
  P.PART_ID=PV.PART_ID
  AND P.PREF_VENDOR_ID != PV.VENDOR_ID
  AND P.PREF_VENDOR_ID=V.VENDOR_ID
  AND PV.VENDOR_ID=V2.VENDOR_ID;

SELECT
  *
FROM
  PO_HEADER PO,
  PO_LINE POL,
  PARTS P,
  VENDORS V
WHERE
  PO.VENDOR_ID LIKE '100%'
  AND PO.POSTING_CANDIDATE='N'
  AND PO.PURC_ORDER_ID=POL.PURC_ORDER_ID
  AND POL.PART_ID BETWEEN '80' AND '999999'
  AND POL.PART_ID=P.PART_ID
  AND PO.VENDOR_ID=V.VENDOR_ID;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
SET AUTOTRACE OFF 

Just for fun, I also ran the test script on my laptop, which was running Oracle Database Enterprise 11.2.0.1 configured with a 12000M SGA_TARGET and a 2000M PGA_AGGREGATE_TARGET.

Simple insert into a table:

INSERT INTO
  LOCATIONS
SELECT /*+ ORDERED */
  LOC.LOCATION_ID,
  WH.WAREHOUSE_ID,
  RPAD(WH.WAREHOUSE_ID||'-'||LOC.LOCATION_ID,60),
  DECODE(MOD(ROWNUM,5),0,'T',1,'R','F')
FROM
  (SELECT
... 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.14       0.13          0        141       3259        2200
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.17       0.15          0        141       3259        2200

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
         1          1          1   COUNT  (cr=0 pr=0 pw=0 time=0 us)
         1          1          1    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=4 size=72 card=1)
         1          1          1     VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
         1          1          1      COUNT  (cr=0 pr=0 pw=0 time=0 us)
         1          1          1       CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
         1          1          1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
         1          1          1     VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
        10         10         10      COUNT  (cr=0 pr=0 pw=0 time=18 us)
        10         10         10       CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=18 us)
         1          1          1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  rdbms ipc reply                                 2        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.07          0          0          0           0
Execute      1      0.09       0.12          0        258       3443        2200
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.10       0.20          0        258       3443        2200

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
   2200  COUNT  (cr=0 pr=0 pw=0 time=116495 us)
   2200   NESTED LOOPS  (cr=0 pr=0 pw=0 time=77138 us)
    200    VIEW  (cr=0 pr=0 pw=0 time=19754 us)
    200     COUNT  (cr=0 pr=0 pw=0 time=210 us)
    200      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=11 us)
      1       FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)
   2200    VIEW  (cr=0 pr=0 pw=0 time=52757 us)
   4000     COUNT  (cr=0 pr=0 pw=0 time=691 us)
   4000      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=593 us)
    200       FAST DUAL  (cr=0 pr=0 pw=0 time=48 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01 

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.14          0          0          0           0
Execute      1      0.10       0.10          0        259       3434        2200
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.12       0.25          0        259       3434        2200

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
   2200  COUNT  (cr=0 pr=0 pw=0 time=101335 us)
   2200   NESTED LOOPS  (cr=0 pr=0 pw=0 time=61984 us)
    200    VIEW  (cr=0 pr=0 pw=0 time=2423 us)
    200     COUNT  (cr=0 pr=0 pw=0 time=210 us)
    200      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=209 us)
      1       FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
   2200    VIEW  (cr=0 pr=0 pw=0 time=54837 us)
   4000     COUNT  (cr=0 pr=0 pw=0 time=693 us)
   4000      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=620 us)
    200       FAST DUAL  (cr=0 pr=0 pw=0 time=52 us) 

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.07       0.07          0        155       3447        2200
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.10       0.10          0        155       3447        2200

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=190 pr=0 pw=0 time=0 us)
   2200   COUNT  (cr=0 pr=0 pw=0 time=76598 us)
   2200    NESTED LOOPS  (cr=0 pr=0 pw=0 time=76476 us cost=4 size=72 card=1)
    200     VIEW  (cr=0 pr=0 pw=0 time=2288 us cost=2 size=36 card=1)
    200      COUNT  (cr=0 pr=0 pw=0 time=99 us)
    200       CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
   2200     VIEW  (cr=0 pr=0 pw=0 time=69004 us cost=2 size=36 card=1)
   4000      COUNT  (cr=0 pr=0 pw=0 time=2719 us)
   4000       CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=2026 us)
    200        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.12       0.12          0        158       3547        2200
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.15       0.14          0        158       3547        2200

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   COUNT  (cr=0 pr=0 pw=0 time=0 us)
      1    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=4 size=72 card=1)
      1     VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
      1      COUNT  (cr=0 pr=0 pw=0 time=0 us)
      1       CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
      1     VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
     10      COUNT  (cr=0 pr=0 pw=0 time=45 us)
     10       CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=36 us)
      1        FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   1        0.01          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Deleting from the temporary holding table for the vendors, before inserting into the VENDORS table with the constraints:

DELETE FROM
  VENDORS_TEMP
WHERE
  (VENDOR_ID,TERMS_NET_DAYS) IN
    (SELECT
      V.VENDOR_ID,
      V.TERMS_NET_DAYS
    FROM
      VENDORS_TEMP V,
      (SELECT
        VENDOR_ID,
        MIN(TERMS_NET_DAYS) TERMS_NET_DAYS
      FROM
        VENDORS_TEMP
      GROUP BY
        VENDOR_ID
      HAVING
        COUNT(*)>1) M
    WHERE
      V.VENDOR_ID=M.VENDOR_ID
      AND V.TERMS_NET_DAYS>M.TERMS_NET_DAYS) 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.03          0         16          0           0
Execute      1      0.10       0.10          0       7521        244         214
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.12       0.14          0       7537        244         214

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  VENDORS_TEMP (cr=7521 pr=0 pw=0 time=0 us)
       214        214        214   HASH JOIN RIGHT SEMI (cr=7521 pr=0 pw=0 time=64219 us cost=603 size=2061136 card=46844)
       214        214        214    VIEW  VW_NSO_1 (cr=5014 pr=0 pw=0 time=63367 us cost=403 size=51524 card=2342)
       214        214        214     HASH JOIN  (cr=5014 pr=0 pw=0 time=63367 us cost=403 size=103048 card=2342)
       135        135        135      VIEW  (cr=2507 pr=0 pw=0 time=936392 us cost=202 size=1030568 card=46844)
       135        135        135       FILTER  (cr=2507 pr=0 pw=0 time=936392 us)
     49786      49786      49786        SORT GROUP BY (cr=2507 pr=0 pw=0 time=12670 us cost=202 size=1030568 card=46844)
     50000      50000      50000         TABLE ACCESS FULL VENDORS_TEMP (cr=2507 pr=0 pw=0 time=26597 us cost=199 size=1030568 card=46844)
     50000      50000      50000      TABLE ACCESS FULL VENDORS_TEMP (cr=2507 pr=0 pw=0 time=19820 us cost=199 size=1030568 card=46844)
     50000      50000      50000    TABLE ACCESS FULL VENDORS_TEMP (cr=2507 pr=0 pw=0 time=22250 us cost=199 size=1030568 card=46844)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0         12          0           0
Execute      1      0.10       0.11          0       7554        244         214
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.14       0.14          0       7566        244         214

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  VENDORS_TEMP (cr=7554 pr=0 pw=0 time=111433 us)
    214   HASH JOIN RIGHT SEMI (cr=7554 pr=0 pw=0 time=122775 us)
    214    VIEW  VW_NSO_1 (cr=5036 pr=0 pw=0 time=104690 us)
    214     HASH JOIN  (cr=5036 pr=0 pw=0 time=104688 us)
    135      VIEW  (cr=2518 pr=0 pw=0 time=1007463 us)
    135       FILTER  (cr=2518 pr=0 pw=0 time=1007462 us)
  49786        SORT GROUP BY (cr=2518 pr=0 pw=0 time=58205 us)
  50000         TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=17 us)
  50000      TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=23 us)
  50000    TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=20 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.02          0.02 

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.05          0         12          0           0
Execute      1      0.10       0.11          0       7554        244         214
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.14       0.16          0       7566        244         214

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  VENDORS_TEMP (cr=7554 pr=0 pw=0 time=112308 us)
    214   HASH JOIN RIGHT SEMI (cr=7554 pr=0 pw=0 time=229889 us)
    214    VIEW  VW_NSO_1 (cr=5036 pr=0 pw=0 time=210415 us)
    214     HASH JOIN  (cr=5036 pr=0 pw=0 time=210414 us)
    135      VIEW  (cr=2518 pr=0 pw=0 time=1052458 us)
    135       FILTER  (cr=2518 pr=0 pw=0 time=1052458 us)
  49786        SORT GROUP BY (cr=2518 pr=0 pw=0 time=58712 us)
  50000         TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=25 us)
  50000      TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=23 us)
  50000    TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=24 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.04          0         16          0           0
Execute      1      0.10       0.11          0       7554        242         214
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.12       0.15          0       7570        242         214

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  VENDORS_TEMP (cr=7554 pr=0 pw=0 time=0 us)
    214   HASH JOIN RIGHT SEMI (cr=7554 pr=0 pw=0 time=88501 us cost=661 size=2807068 card=63797)
    214    VIEW  VW_NSO_1 (cr=5036 pr=0 pw=0 time=0 us cost=459 size=70180 card=3190)
    214     HASH JOIN  (cr=5036 pr=0 pw=0 time=0 us cost=459 size=140360 card=3190)
  50000      TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=19564 us cost=201 size=1403534 card=63797)
    135      VIEW  (cr=2518 pr=0 pw=0 time=987714 us cost=204 size=1403534 card=63797)
    135       FILTER  (cr=2518 pr=0 pw=0 time=987580 us)
  49786        SORT GROUP BY (cr=2518 pr=0 pw=0 time=12414 us cost=204 size=1403534 card=63797)
  50000         TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=25191 us cost=201 size=1403534 card=63797)
  50000    TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=18925 us cost=201 size=1403534 card=63797)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0         16          0           0
Execute      1      0.10       0.10          0       7554        242         214
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.15       0.14          0       7570        242         214

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  VENDORS_TEMP (cr=7554 pr=0 pw=0 time=0 us)
    214   HASH JOIN RIGHT SEMI (cr=7554 pr=0 pw=0 time=44730 us cost=607 size=2526348 card=57417)
    214    VIEW  VW_NSO_1 (cr=5036 pr=0 pw=0 time=44623 us cost=406 size=66638 card=3029)
    214     HASH JOIN  (cr=5036 pr=0 pw=0 time=44623 us cost=406 size=133276 card=3029)
    135      VIEW  (cr=2518 pr=0 pw=0 time=934047 us cost=204 size=1263174 card=57417)
    135       FILTER  (cr=2518 pr=0 pw=0 time=934047 us)
  49786        SORT GROUP BY (cr=2518 pr=0 pw=0 time=10494 us cost=204 size=1263174 card=57417)
  50000         TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=25191 us cost=201 size=1263174 card=57417)
  50000      TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=20587 us cost=201 size=1263174 card=57417)
  50000    TABLE ACCESS FULL VENDORS_TEMP (cr=2518 pr=0 pw=0 time=18030 us cost=201 size=1263174 card=57417)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00  

Deleting from the temporary holding table for the PARTS before inserting into the PARTS table:

DELETE FROM
  PARTS_TEMP
WHERE
  (PART_ID,ORDER_POINT) IN
    (SELECT
      V.PART_ID,
      V.ORDER_POINT
    FROM
      PARTS_TEMP V,
      (SELECT
        PART_ID,
        MIN(ORDER_POINT) ORDER_POINT
      FROM
        PARTS_TEMP
      GROUP BY
        PART_ID
      HAVING
        COUNT(*)>1) M
    WHERE
      V.PART_ID=M.PART_ID
      AND V.ORDER_POINT>M.ORDER_POINT) 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.04          0         16          0           0
Execute      1      0.21       0.21          0      15028        374         306
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.28       0.26          0      15044        374         306

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  PARTS_TEMP (cr=15035 pr=0 pw=0 time=0 us)
       306        306        306   HASH JOIN RIGHT SEMI (cr=15027 pr=0 pw=0 time=29280 us cost=1274 size=4953360 card=82556)
       306        306        306    VIEW  VW_NSO_1 (cr=10018 pr=0 pw=0 time=26535 us cost=878 size=123840 card=4128)
       306        306        306     HASH JOIN  (cr=10018 pr=0 pw=0 time=26433 us cost=878 size=247680 card=4128)
       193        193        193      VIEW  (cr=5009 pr=0 pw=0 time=2787264 us cost=398 size=2476680 card=82556)
       193        193        193       FILTER  (cr=5009 pr=0 pw=0 time=2787168 us)
     99694      99694      99694        SORT GROUP BY (cr=5009 pr=0 pw=0 time=27386 us cost=398 size=2476680 card=82556)
    100000     100000     100000         TABLE ACCESS FULL PARTS_TEMP (cr=5009 pr=0 pw=0 time=46418 us cost=395 size=2476680 card=82556)
    100000     100000     100000      TABLE ACCESS FULL PARTS_TEMP (cr=5009 pr=0 pw=0 time=30945 us cost=395 size=2476680 card=82556)
    100000     100000     100000    TABLE ACCESS FULL PARTS_TEMP (cr=5009 pr=0 pw=0 time=31713 us cost=395 size=2476680 card=82556)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.04          0         12          0           0
Execute      1      0.20       0.20          0      15120        362         306
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.20       0.25          0      15132        362         306

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  PARTS_TEMP (cr=15120 pr=0 pw=0 time=203889 us)
    306   HASH JOIN RIGHT SEMI (cr=15120 pr=0 pw=0 time=186251 us)
    306    VIEW  VW_NSO_1 (cr=10080 pr=0 pw=0 time=158060 us)
    306     HASH JOIN  (cr=10080 pr=0 pw=0 time=158059 us)
    193      VIEW  (cr=5040 pr=0 pw=0 time=2709664 us)
    193       FILTER  (cr=5040 pr=0 pw=0 time=2709471 us)
  99694        SORT GROUP BY (cr=5040 pr=0 pw=0 time=115549 us)
 100000         TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=15 us)
 100000      TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=25 us)
 100000    TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.02          0.02 

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0         12          0           0
Execute      1      0.21       0.21          0      15120        362         306
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.24       0.24          0      15132        362         306

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  PARTS_TEMP (cr=15120 pr=0 pw=0 time=218918 us)
    306   HASH JOIN RIGHT SEMI (cr=15120 pr=0 pw=0 time=201432 us)
    306    VIEW  VW_NSO_1 (cr=10080 pr=0 pw=0 time=152694 us)
    306     HASH JOIN  (cr=10080 pr=0 pw=0 time=152694 us)
 100000      TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=27 us)
    193      VIEW  (cr=5040 pr=0 pw=0 time=2975280 us)
    193       FILTER  (cr=5040 pr=0 pw=0 time=2975279 us)
  99694        SORT GROUP BY (cr=5040 pr=0 pw=0 time=113516 us)
 100000         TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=19 us)
 100000    TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=32 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0         16          0           0
Execute      1      0.21       0.21          0      15081        362         306
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.24       0.25          0      15097        362         306

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  PARTS_TEMP (cr=15081 pr=0 pw=0 time=0 us)
    306   HASH JOIN RIGHT SEMI (cr=15081 pr=0 pw=0 time=15250 us cost=1289 size=5367480 card=89458)
    306    VIEW  VW_NSO_1 (cr=10054 pr=0 pw=0 time=203 us cost=891 size=134190 card=4473)
    306     HASH JOIN  (cr=10054 pr=0 pw=0 time=203 us cost=891 size=268380 card=4473)
 100000      TABLE ACCESS FULL PARTS_TEMP (cr=5027 pr=0 pw=0 time=30306 us cost=398 size=2683740 card=89458)
    193      VIEW  (cr=5027 pr=0 pw=0 time=3036768 us cost=402 size=2683740 card=89458)
    193       FILTER  (cr=5027 pr=0 pw=0 time=3036480 us)
  99694        SORT GROUP BY (cr=5027 pr=0 pw=0 time=26618 us cost=402 size=2683740 card=89458)
 100000         TABLE ACCESS FULL PARTS_TEMP (cr=5027 pr=0 pw=0 time=40536 us cost=398 size=2683740 card=89458)
 100000    TABLE ACCESS FULL PARTS_TEMP (cr=5027 pr=0 pw=0 time=34015 us cost=398 size=2683740 card=89458)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0         16          0           0
Execute      1      0.21       0.20          0      15120        362         306
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.24       0.24          0      15136        362         306

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  PARTS_TEMP (cr=15120 pr=0 pw=0 time=0 us)
    306   HASH JOIN RIGHT SEMI (cr=15120 pr=0 pw=0 time=20028 us cost=1327 size=7380240 card=123004)
    306    VIEW  VW_NSO_1 (cr=10080 pr=0 pw=0 time=19215 us cost=927 size=184500 card=6150)
    306     HASH JOIN  (cr=10080 pr=0 pw=0 time=19215 us cost=927 size=369000 card=6150)
    193      VIEW  (cr=5040 pr=0 pw=0 time=2823264 us cost=404 size=3690120 card=123004)
    193       FILTER  (cr=5040 pr=0 pw=0 time=2823264 us)
  99694        SORT GROUP BY (cr=5040 pr=0 pw=0 time=29434 us cost=404 size=3690120 card=123004)
 100000         TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=44245 us cost=398 size=3690120 card=123004)
 100000      TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=34015 us cost=398 size=3690120 card=123004)
 100000    TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=33119 us cost=398 size=3690120 card=123004)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Inserting into the PARTS table with the foreign key constraints and other constraints, as well as existing indexes:

INSERT INTO
  PARTS
SELECT
  *
FROM
  PARTS_TEMP 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          1          0           0
Execute      1     10.40      10.47          0      22215     471969       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     10.42      10.49          0      22216     471969       99694

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
         1          1          1   TABLE ACCESS FULL PARTS_TEMP (cr=4 pr=0 pw=0 time=0 us cost=401 size=100456986 card=82477)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  rdbms ipc reply                                 8        0.00          0.00
  asynch descriptor resize                        8        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01  

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          1          0           0
Execute      1      3.10      11.63          0      26941     515900       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.10      11.64          0      26942     515900       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
  99694  TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=39 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch completion                      7        1.01          3.35
  log file switch (checkpoint incomplete)        10        1.01          4.51
  log buffer space                                3        0.02          0.05
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.07          0.07

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          1          0           0
Execute      1      3.15       4.06          0      26675     516513       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.16       4.07          0      26676     516513       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
  99694  TABLE ACCESS FULL PARTS_TEMP (cr=5040 pr=0 pw=0 time=46 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log buffer space                                4        0.38          0.49
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          1          0           0
Execute      1      2.85       3.87          0      23790     513495       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.88       3.89          0      23791     513495       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=25258 pr=0 pw=0 time=0 us)
  99694   TABLE ACCESS FULL PARTS_TEMP (cr=5027 pr=0 pw=0 time=58484 us cost=404 size=108570084 card=89138)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log buffer space                                9        0.17          0.76
  log file sync                                   1        0.05          0.05
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          1          0           0
Execute      1      2.91       4.13          0      23688     517295       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.93       4.16          0      23689     517295       99694

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS FULL PARTS_TEMP (cr=4 pr=0 pw=0 time=0 us cost=407 size=149527770 card=122765)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        8        0.00          0.00
  log buffer space                               17        0.18          1.14
  log file sync                                   1        0.02          0.02
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Setting the warehouse and location for the parts in the PARTS table:

UPDATE
  PARTS P
SET
  (PRIMARY_WHS_ID,PRIMARY_LOC_ID)=(
    SELECT
      WAREHOUSE_ID,
      LOCATION_ID
    FROM
      (SELECT
        WAREHOUSE_ID,
        LOCATION_ID,
        ROWNUM RN
      FROM
        LOCATIONS)
    WHERE
      MOD(P.ORDER_POINT,2000)=RN) 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          1          0           0
Execute      1    111.50     111.65          0    2131063     220890       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    111.52     111.67          0    2131064     220890       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  PARTS (cr=2003912 pr=0 pw=0 time=0 us)
    205731     205731     205731   TABLE ACCESS FULL PARTS (cr=10314 pr=0 pw=0 time=233967 us cost=396 size=3436164 card=110844)
    105985     105985     105985   VIEW  (cr=2120740 pr=0 pw=0 time=0 us cost=3 size=68200 card=2200)
 233281400  233281400  233281400    COUNT  (cr=2120740 pr=0 pw=0 time=49388820 us)
 233281400  233281400  233281400     INDEX FAST FULL SCAN SYS_C0024962 (cr=2120740 pr=0 pw=0 time=27487736 us cost=3 size=41800 card=2200)(object id 82045)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          1          0           0
Execute      1    185.62     185.65          0    3273130     490125       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    185.64     185.66          0    3273131     490125       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

  Rows     Row Source Operation
  -------  ---------------------------------------------------
        0  UPDATE  PARTS (cr=1704883 pr=0 pw=0 time=96171788 us)
   291367   TABLE ACCESS FULL PARTS (cr=14687 pr=0 pw=0 time=291444 us)
   191580   VIEW  (cr=3258441 pr=0 pw=0 time=181333700 us)
421680600    COUNT  (cr=3258441 pr=0 pw=0 time=1555388 us)
421680600     INDEX FAST FULL SCAN SYS_C004567 (cr=3258441 pr=0 pw=0 time=1482697 us)(object id 47888)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch completion                      2        0.02          0.03
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.04          0.04

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1    101.22     101.26          0    1800041     223772       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    101.24     101.27          0    1800042     223772       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

  Rows     Row Source Operation
  -------  ---------------------------------------------------
        0  UPDATE  PARTS (cr=1704883 pr=0 pw=0 time=96199569 us)
   204969   TABLE ACCESS FULL PARTS (cr=10364 pr=0 pw=0 time=69 us)
   105224   VIEW  (cr=1789675 pr=0 pw=0 time=99082917 us)
231605000    COUNT  (cr=1789675 pr=0 pw=0 time=920840 us)
231605000     INDEX FAST FULL SCAN SYS_C0012074 (cr=1789675 pr=0 pw=0 time=882985 us)(object id 51693)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch completion                      2        0.02          0.04
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          1          0           0
Execute      1    185.57     185.57          0    3300666     462095       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    185.60     185.59          0    3300667     462095       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

  Rows     Row Source Operation
  -------  ---------------------------------------------------
        0  UPDATE  PARTS (cr=1804551 pr=0 pw=0 time=0 us)
   282275   TABLE ACCESS FULL PARTS (cr=14206 pr=0 pw=0 time=358592 us cost=401 size=3466482 card=111822)
   182491   VIEW  (cr=3286458 pr=0 pw=0 time=0 us cost=3 size=68200 card=2200)
401678200    COUNT  (cr=3286458 pr=0 pw=0 time=81721128 us)
401678200     INDEX FAST FULL SCAN SYS_C009200 (cr=3286458 pr=0 pw=0 time=43336740 us cost=3 size=41800 card=2200)(object id 20300)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          1          0           0
Execute      1    199.94     199.95          0    3426213     483539       99694
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    199.97     199.98          0    3426214     483539       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

  Rows     Row Source Operation
  -------  ---------------------------------------------------
        0  UPDATE  PARTS (cr=1804577 pr=0 pw=0 time=0 us)
   289229   TABLE ACCESS FULL PARTS (cr=14581 pr=0 pw=0 time=375661 us cost=400 size=3020795 card=97445)
   189443   VIEW  (cr=3411630 pr=0 pw=0 time=0 us cost=3 size=68200 card=2200)
416977000    COUNT  (cr=3411630 pr=0 pw=0 time=86533192 us)
416977000     INDEX FAST FULL SCAN SYS_C0024649 (cr=3411630 pr=0 pw=0 time=46295536 us cost=3 size=41800 card=2200)(object id 83195)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Inserting into the header level detail table for purchase orders from the temporary holding table that was initially set up, several check constraints on this table:

INSERT INTO
  PO_HEADER
SELECT
  *
FROM
  PO_HEADER_TEMP 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          1          0           0
Execute      1     60.52      60.68          0      75970    3853984      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     60.54      60.70          0      75971    3853984      500000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
         1          1          1   TABLE ACCESS FULL PO_HEADER_TEMP (cr=4 pr=0 pw=0 time=0 us cost=1060 size=402093498 card=412827)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  rdbms ipc reply                                 5        0.00          0.00
  asynch descriptor resize                        5        0.00          0.00
  log file switch completion                      1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1     13.97      56.05          0      75785    3954396      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     13.99      56.06          0      75786    3954396      500000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
 500000  TABLE ACCESS FULL PO_HEADER_TEMP (cr=13190 pr=0 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch completion                     26        1.01         12.10
  log file switch (checkpoint incomplete)        55        1.01         27.12
  log buffer space                                5        0.12          0.25
  rdbms ipc reply                                34        0.00          0.00
  control file sequential read                   68        0.36          0.84
  db file sequential read                         8        0.02          0.02
  Data file init write                           44        0.01          0.13
  direct path sync                                4        0.00          0.00
  db file single write                            4        0.00          0.00
  control file parallel write                    12        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.02          0.02 

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1     13.93      29.44          0      73557    3946136      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     13.94      29.45          0      73558    3946136      500000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
 500000  TABLE ACCESS FULL PO_HEADER_TEMP (cr=13190 pr=0 pw=0 time=22 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log buffer space                               44        0.99          9.17
  log file switch completion                     13        0.44          1.42
  log file switch (checkpoint incomplete)         8        1.01          3.10
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          1          0           0
Execute      1     12.71      15.53          0      72026    3962055      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     12.72      15.55          0      72027    3962055      500000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=73940 pr=0 pw=0 time=0 us)
 500000   TABLE ACCESS FULL PO_HEADER_TEMP (cr=13190 pr=0 pw=0 time=222932 us cost=1144 size=474746106 card=487419)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log buffer space                               36        0.18          2.01
  log file switch completion                      4        0.27          0.56
  log file sync                                   1        0.15          0.15
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          1          0           0
Execute      1     12.65      17.11          0      71508    3958260      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     12.68      17.13          0      71509    3958260      500000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS FULL PO_HEADER_TEMP (cr=4 pr=0 pw=0 time=0 us cost=1147 size=516390450 card=530175)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        5        0.00          0.00
  log buffer space                               62        0.29          3.91
  log file switch (private strand flush incomplete)
                                                  2        0.10          0.19
  log file switch completion                      1        0.06          0.06
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00   

Inserting more than 12 million rows into a table before inserting those rows into the table with the constraints and indexes:

INSERT INTO PO_LINE_TEMP (
  PURC_ORDER_ID,
  LINE_NO,
  PART_ID,
...
  FULFILLED_QTY)
SELECT /*+ ORDERED */
  PURC_ORDER_ID,
  ORDER_POINT-START_LINE+1,
  PART_ID,
...
FROM
  (SELECT
    'PO'||TO_CHAR(ROWNUM) PURC_ORDER_ID,
    ROWNUM RN,
    ABS(SIN(ROWNUM*3.14159265/180))*90000 START_LINE,
    MOD(ROWNUM,50)+1 LINES
  FROM
    DUAL
  CONNECT BY
    LEVEL<=500000) POL,
  PARTS P
WHERE
  P.ORDER_POINT BETWEEN START_LINE AND (START_LINE+LINES-1) 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1     93.07      95.64          0    1924137    1376229    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     93.10      95.67          0    1924137    1376229    12205347

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
         1          1          1   COUNT  (cr=3 pr=0 pw=0 time=0 us)
         1          1          1    NESTED LOOPS  (cr=3 pr=0 pw=0 time=0 us)
         1          1          1     NESTED LOOPS  (cr=2 pr=0 pw=0 time=0 us cost=26 size=19422 card=249)
         1          1          1      VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=49 card=1)
         1          1          1       COUNT  (cr=0 pr=0 pw=0 time=0 us)
         1          1          1        CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
         1          1          1         FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
         1          1          1      INDEX RANGE SCAN IND_PARTS_7 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=449)(object id 82060)
         1          1          1     TABLE ACCESS BY INDEX ROWID PARTS (cr=1 pr=0 pw=0 time=0 us cost=24 size=7221 card=249)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch (private strand flush incomplete)
                                                  2        0.00          0.01
  latch: object queue header operation            5        0.00          0.00
  log file switch completion                      4        0.04          0.09
  log buffer space                                2        1.41          1.45
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       53.66         53.66 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.04          0          0          0           0
Execute      1     91.10     231.44          0    2573862    2165886    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     91.12     231.49          0    2573862    2165886    12205347

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
12205347  COUNT  (cr=2136585 pr=0 pw=0 time=24410743 us)
12205347   NESTED LOOPS  (cr=2136585 pr=0 pw=0 time=24425909 us)
  500000    VIEW  (cr=0 pr=0 pw=0 time=8000015 us)
  500000     COUNT  (cr=0 pr=0 pw=0 time=500012 us)
  500000      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=500354 us)
       1       FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
12205347    TABLE ACCESS BY INDEX ROWID PARTS (cr=2136585 pr=0 pw=0 time=15311808 us)
12205347     INDEX RANGE SCAN IND_PARTS_7 (cr=532876 pr=0 pw=0 time=2172989 us)(object id 47903)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch completion                     97        1.01         39.20
  log file switch (checkpoint incomplete)       168        1.01         95.13
  control file sequential read                  187        0.72          2.68
  db file sequential read                        22        0.06          0.12
  Data file init write                           66        0.04          0.49
  direct path sync                               11        0.00          0.00
  db file single write                           11        0.00          0.00
  control file parallel write                    33        0.00          0.00
  rdbms ipc reply                                11        0.00          0.00
  log buffer space                                2        0.48          0.56
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.04          0.04

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0          0          0           0
Execute      1     88.98     142.98          4    2615674    2163468    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     89.01     143.02          4    2615674    2163468    12205347

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
12205347  COUNT  (cr=2137204 pr=4 pw=0 time=24410741 us)
12205347   NESTED LOOPS  (cr=2137204 pr=4 pw=0 time=24426124 us)
  500000    VIEW  (cr=0 pr=0 pw=0 time=8000012 us)
  500000     COUNT  (cr=0 pr=0 pw=0 time=500012 us)
  500000      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=500341 us)
       1       FAST DUAL  (cr=0 pr=0 pw=0 time=1 us)
12205347    TABLE ACCESS BY INDEX ROWID PARTS (cr=2137204 pr=4 pw=0 time=16373725 us)
12205347     INDEX RANGE SCAN IND_PARTS_7 (cr=533368 pr=0 pw=0 time=2101123 us)(object id 51708)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch completion                     52        1.00         14.60
  log buffer space                              118        1.00         18.55
  log file switch (checkpoint incomplete)        30        1.01         13.35
  db file sequential read                         4        0.60          1.23
  latch: object queue header operation            1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          0          0           0
Execute      1     83.00     100.61          0    2554010    2164473    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     83.03     100.65          0    2554010    2164473    12205347

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
       0  LOAD TABLE CONVENTIONAL  (cr=2555697 pr=0 pw=0 time=0 us)
12205347   COUNT  (cr=2070136 pr=0 pw=0 time=30568852 us)
12205347    NESTED LOOPS  (cr=2070136 pr=0 pw=0 time=28919458 us)
12205347     NESTED LOOPS  (cr=521689 pr=0 pw=0 time=15529864 us cost=44 size=19422 card=249)
  500000      VIEW  (cr=0 pr=0 pw=0 time=8116185 us cost=2 size=49 card=1)
  500000       COUNT  (cr=0 pr=0 pw=0 time=828383 us)
  500000        CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=744494 us)
       1         FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
12205347      INDEX RANGE SCAN IND_PARTS_7 (cr=521689 pr=0 pw=0 time=4129164 us cost=2 size=0 card=449)(object id 20315)
12205347     TABLE ACCESS BY INDEX ROWID PARTS (cr=1548447 pr=0 pw=0 time=0 us cost=42 size=7221 card=249)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch completion                      4        0.49          0.80
  log buffer space                               43        1.55         15.66
  latch: object queue header operation            1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          0          0           0
Execute      1     84.80     107.84          0    2613657    2162650    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     84.83     107.87          0    2613657    2162650    12205347

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   COUNT  (cr=3 pr=0 pw=0 time=0 us)
      1    NESTED LOOPS  (cr=3 pr=0 pw=0 time=0 us)
      1     NESTED LOOPS  (cr=2 pr=0 pw=0 time=0 us cost=47 size=19422 card=249)
      1      VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=49 card=1)
      1       COUNT  (cr=0 pr=0 pw=0 time=0 us)
      1        CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      1         FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
      1      INDEX RANGE SCAN IND_PARTS_7 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=449)(object id 83210)
      1     TABLE ACCESS BY INDEX ROWID PARTS (cr=1 pr=0 pw=0 time=0 us cost=45 size=7221 card=249)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log buffer space                              189        0.56         20.65
  log file switch (private strand flush incomplete)
                                                  2        0.73          0.79
  log file switch completion                      3        0.15          0.37
  latch: object queue header operation            1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00   

Inserting more than 12 million rows into a table with several foreign keys, check constraints, and indexes:

INSERT INTO
  PO_LINE
SELECT
  *
FROM
  PO_LINE_TEMP 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          1          0           0
Execute      1   1136.71    1156.64      28932    1733923  139706464    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2   1136.71    1156.66      28932    1733924  139706464    12205347

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
         1          1          1   TABLE ACCESS FULL PO_LINE_TEMP (cr=4 pr=0 pw=0 time=0 us cost=20365 size=12823639038 card=11626146)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  rdbms ipc reply                               172        0.10          0.71
  asynch descriptor resize                        5        0.00          0.00
  log file switch completion                     27        0.02          0.27
  latch: object queue header operation            1        0.00          0.00
  Disk file operations I/O                      476        0.00          0.23
  control file sequential read                 3318        0.05          0.63
  db file sequential read                       696        0.07          0.40
  Data file init write                          158        0.00          0.00
  db file single write                          158        0.00          0.05
  control file parallel write                   474        0.00          0.17
  reliable message                                9        0.00          0.00
  db file scattered read                        240        0.05          0.36
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1    353.56    2568.62     124584    1578922  141542735    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    353.57    2568.63     124584    1578923  141542735    12205347

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
12205347  TABLE ACCESS FULL PO_LINE_TEMP (cr=249230 pr=122013 pw=0 time=305133674 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                 3043        3.50         76.00
  db file sequential read                      2991        3.22         39.92
  Data file init write                         6414        1.84         64.88
  direct path sync                              179        0.00          0.00
  db file single write                          179        0.00          0.01
  control file parallel write                   537        0.10          0.32
  rdbms ipc reply                               199        1.49          2.48
  log file switch completion                    420        1.01        262.79
  log file switch (checkpoint incomplete)      1523        1.01       1032.99
  enq: CF - contention                            2        0.36          0.42
  log buffer space                              125        1.01         62.52
  latch: object queue header operation            3        0.00          0.00
  latch: cache buffers lru chain                  2        0.00          0.00
  db file scattered read                       1098        3.99        123.07
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.02          0.02
 

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          1          0           0
Execute      1    353.09    1648.72     258026    1511940  141720375    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    353.09    1648.73     258026    1511941  141720375    12205347

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
12205347  TABLE ACCESS FULL PO_LINE_TEMP (cr=249562 pr=248589 pw=0 time=915409964 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                       2055        4.47        345.49
  db file sequential read                      9591        4.02        434.71
  log buffer space                              111        1.01         46.34
  log file switch completion                    272        1.01         64.43
  log file switch (checkpoint incomplete)       169        1.01         94.82
  latch: object queue header operation            2        0.00          0.00
  rdbms ipc reply                               176        0.30          0.75
  control file sequential read                 1258        3.65         58.57
  Data file init write                         4959        1.85         69.14
  direct path sync                               74        0.00          0.00
  db file single write                           74        0.40          0.40
  control file parallel write                   222        1.19          1.90
  enq: CF - contention                           12        1.95          4.11
  buffer exterminate                            182        0.01          2.79
  latch: cache buffers lru chain                  2        0.00          0.00
  latch free                                      1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          1          0           0
Execute      1    331.28     901.42     131243    1484367  141539241    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    331.29     901.44     131243    1484368  141539241    12205347

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
       0  LOAD TABLE CONVENTIONAL  (cr=1490464 pr=131243 pw=0 time=0 us)
12205347   TABLE ACCESS FULL PO_LINE_TEMP (cr=249198 pr=128481 pw=0 time=745579520 us cost=20775 size=14111383817 card=12793639)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log buffer space                              522        6.87        145.23
  log file switch completion                     31        4.22         11.47
  reliable message                               20        0.38          0.38
  rdbms ipc reply                                78        1.22          1.65
  Disk file operations I/O                      176        1.20          1.60
  control file sequential read                 1218        2.85         54.69
  db file sequential read                      2895        2.72         88.80
  Data file init write                           58        1.10          1.10
  db file single write                           58        0.00          0.00
  control file parallel write                   174        0.07          0.44
  latch: redo allocation                          2        0.00          0.00
  enq: CF - contention                           12        0.83          6.59
  db file scattered read                       1046        2.71        128.56
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          1          0           0
Execute      1    327.08     777.27     260153    1483975  141436854    12205347
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    327.11     777.29     260153    1483976  141436854    12205347

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS FULL PO_LINE_TEMP (cr=4 pr=5 pw=0 time=0 us cost=20566 size=10857297775 card=9843425)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                       2055        0.82        195.44
  asynch descriptor resize                        5        0.00          0.00
  db file sequential read                     11123        0.94        222.31
  log buffer space                              115        0.38          6.49
  log file switch completion                     30        0.36          2.98
  latch: object queue header operation            1        0.00          0.00
  latch free                                      2        0.00          0.00
  reliable message                               10        0.00          0.00
  rdbms ipc reply                                29        0.09          0.15
  Disk file operations I/O                       59        0.03          0.06
  control file sequential read                  399        0.64          4.11
  Data file init write                           19        0.00          0.00
  db file single write                           19        0.00          0.00
  control file parallel write                    57        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00   

Determining how many of each part was ordered in the last 90 days:

SELECT
  PO.VENDOR_ID,
  P.PRODUCT_CODE,
  P.STOCK_UM,
  SUM(POL.ORDER_QTY) ORDER_QTY
FROM
  PO_HEADER PO,
  PO_LINE POL,
  PARTS P
WHERE
  PO.ORDER_DATE BETWEEN TRUNC(SYSDATE-90) AND TRUNC(SYSDATE)
  AND PO.PURC_ORDER_ID=POL.PURC_ORDER_ID
  AND POL.PART_ID=P.PART_ID
GROUP BY
  PO.VENDOR_ID,
  P.PRODUCT_CODE,
  P.STOCK_UM 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.09         28        975          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      3.18       4.54     267202     267225          0        2245
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      3.27       4.63     267230     268200          0        2245

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      2245       2245       2245  HASH GROUP BY (cr=267225 pr=267202 pw=0 time=132 us cost=25911 size=12588193 card=172441)
    399249     399249     399249   FILTER  (cr=267225 pr=267202 pw=0 time=3877952 us)
    399249     399249     399249    HASH JOIN  (cr=267225 pr=267202 pw=0 time=3818192 us cost=21791 size=50701858 card=694546)
     99694      99694      99694     TABLE ACCESS FULL PARTS (cr=4996 pr=4989 pw=0 time=44919 us cost=394 size=2093574 card=99694)
    399249     399249     399249     HASH JOIN  (cr=262229 pr=262213 pw=0 time=3642370 us cost=20823 size=36116392 card=694546)
     16668      16668      16668      TABLE ACCESS FULL PO_HEADER (cr=13173 pr=13163 pw=0 time=905107 us cost=1068 size=785568 card=28056)
  12205347   12205347   12205347      TABLE ACCESS FULL PO_LINE (cr=249056 pr=249050 pw=0 time=2135535 us cost=19697 size=292928328 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  db file sequential read                         3        0.00          0.00
  db file scattered read                        172        0.00          0.32
  direct path read                              678        0.00          0.34
  asynch descriptor resize                        3        0.00          0.00
  SQL*Net message from client                     4        0.00          0.00
  SQL*Net more data to client                     5        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.26         26        917          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      4.13      22.17     267421     283033          0        2245
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      4.13      22.43     267447     283950          0        2245

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    2245  HASH GROUP BY (cr=283033 pr=267421 pw=0 time=22168922 us)
  399249   FILTER  (cr=283033 pr=267421 pw=0 time=19262355 us)
  399249    HASH JOIN  (cr=283033 pr=267421 pw=0 time=19262351 us)
   99694     TABLE ACCESS FULL PARTS (cr=5040 pr=5033 pw=0 time=7895521 us)
  399249     HASH JOIN  (cr=277993 pr=262388 pw=0 time=18091601 us)
   16668      TABLE ACCESS FULL PO_HEADER (cr=17931 pr=13180 pw=0 time=1189286 us)
12205347      TABLE ACCESS FULL PO_LINE (cr=260062 pr=249208 pw=0 time=12216877 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  db file sequential read                         8        0.04          0.17
  db file scattered read                       2148        0.06         17.70
  SQL*Net message from client                     4        0.00          0.00
  SQL*Net more data to client                    27        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.27         39        950          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      4.05      30.56     267371     278582          0        2245
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      4.14      30.83     267410     279532          0        2245

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    2245  HASH GROUP BY (cr=278582 pr=267371 pw=0 time=30561759 us)
  399249   FILTER  (cr=278582 pr=267371 pw=0 time=14361948 us)
  399249    HASH JOIN  (cr=278582 pr=267371 pw=0 time=14361943 us)
   99694     TABLE ACCESS FULL PARTS (cr=5040 pr=5033 pw=0 time=4535877 us)
  399249     HASH JOIN  (cr=273542 pr=262338 pw=0 time=13730984 us)
   16668      TABLE ACCESS FULL PO_HEADER (cr=13190 pr=13180 pw=0 time=4023636 us)
12205347      TABLE ACCESS FULL PO_LINE (cr=260352 pr=249158 pw=0 time=12247459 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  db file sequential read                         8        0.04          0.19
  db file scattered read                       2145        0.83         24.42
  SQL*Net message from client                     4        0.00          0.00
  SQL*Net more data to client                    27        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.16         26        948          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      3.05      19.97     267756     267779          0        2245
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      3.13      20.14     267782     268727          0        2245

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    2245  HASH GROUP BY (cr=267779 pr=267756 pw=0 time=528 us cost=26366 size=12586733 card=172421)
  399249   FILTER  (cr=267779 pr=267756 pw=0 time=16199360 us)
  399249    HASH JOIN  (cr=267779 pr=267756 pw=0 time=16159946 us cost=22250 size=50646378 card=693786)
   99694     TABLE ACCESS FULL PARTS (cr=5027 pr=5020 pw=0 time=7047105 us cost=399 size=2093574 card=99694)
  399249     HASH JOIN  (cr=262752 pr=262736 pw=0 time=16006774 us cost=21279 size=36076872 card=693786)
   16668      TABLE ACCESS FULL PO_HEADER (cr=13190 pr=13180 pw=0 time=3079960 us cost=1148 size=784700 card=28025)
12205347      TABLE ACCESS FULL PO_LINE (cr=249562 pr=249556 pw=0 time=19074670 us cost=20073 size=292928328 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                        10        0.02          0.15
  db file scattered read                        179        0.06          2.35
  direct path read                             1961        0.05         14.05
  asynch descriptor resize                        3        0.00          0.00
  SQL*Net message from client                     4        0.00          0.00
  SQL*Net more data to client                     5        0.00          0.00
 

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.12       0.47         30        979          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      2.90      19.77     267379     267391          0        2245
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      3.02      20.24     267409     268370          0        2245

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    2245  HASH GROUP BY (cr=267391 pr=267379 pw=0 time=792 us cost=26322 size=12589653 card=172461)
  399249   FILTER  (cr=267391 pr=267379 pw=0 time=15617891 us)
  399249    HASH JOIN  (cr=267391 pr=267379 pw=0 time=15576303 us cost=22201 size=50757922 card=695314)
   99694     TABLE ACCESS FULL PARTS (cr=5036 pr=5033 pw=0 time=3415029 us cost=399 size=2093574 card=99694)
  399249     HASH JOIN  (cr=262355 pr=262346 pw=0 time=15423001 us cost=21229 size=36156328 card=695314)
   16668      TABLE ACCESS FULL PO_HEADER (cr=13183 pr=13180 pw=0 time=6721890 us cost=1148 size=786436 card=28087)
12205347      TABLE ACCESS FULL PO_LINE (cr=249172 pr=249166 pw=0 time=3846626 us cost=20023 size=292928328 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                         7        0.03          0.13
  direct path read                             2030        0.08         16.32
  asynch descriptor resize                        6        0.00          0.00
  db file scattered read                          2        0.02          0.04
  SQL*Net message from client                     4        0.00          0.00
  SQL*Net more data to client                     5        0.00          0.00   

Determining the last date each C level (defines the frequency of purchase with the purchase cost) for parts with a product code of FG:

SELECT
  POL.PART_ID,
  P.DESCRIPTION,
  MAX(DESIRED_RECV_DATE) LAST_RECEIVE_DATE
FROM
  PO_LINE POL,
  PARTS P
WHERE
  P.PRODUCT_CODE='FG'
  AND P.ABC_CODE='C'
  AND P.PART_ID=POL.PART_ID
GROUP BY
  POL.PART_ID,
  P.DESCRIPTION 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          9          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      4.61       5.10     249049     254052          0        1815
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      4.64       5.13     249049     254061          0        1815

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      1815       1815       1815  HASH GROUP BY (cr=254052 pr=249049 pw=0 time=388 us cost=20819 size=250514 card=4246)
      1815       1815       1815   HASH JOIN  (cr=254052 pr=249049 pw=0 time=1814 us cost=20818 size=250514 card=4246)
      4246       4246       4246    VIEW  VW_GBC_5 (cr=249056 pr=249049 pw=0 time=998 us cost=20420 size=89166 card=4246)
      4246       4246       4246     HASH GROUP BY (cr=249056 pr=249049 pw=0 time=499 us cost=20420 size=84920 card=4246)
  12205347   12205347   12205347      TABLE ACCESS FULL PO_LINE (cr=249056 pr=249049 pw=0 time=2001392 us cost=19811 size=244106940 card=12205347)
     42727      42727      42727    TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=21874 us cost=398 size=252548 card=6646)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  direct path read                               84        0.00          0.04
  asynch descriptor resize                        3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
  SQL*Net more data to client                     7        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          9          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        3      6.25       6.26          0     254270          0        1815
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      6.25       6.30          0     254279          0        1815

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    1815  HASH GROUP BY (cr=254270 pr=0 pw=0 time=6274965 us)
 5203352   HASH JOIN  (cr=254270 pr=0 pw=0 time=35124 us)
   42727    TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=24 us)
12205347    TABLE ACCESS FULL PO_LINE (cr=249230 pr=0 pw=0 time=19 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
  SQL*Net more data to client                    36        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          9          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      6.41       9.64     109023     254220          0        1815
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      6.41       9.65     109023     254229          0        1815

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    1815  HASH GROUP BY (cr=254220 pr=109023 pw=0 time=9646123 us)
 5203352   HASH JOIN  (cr=254220 pr=109023 pw=0 time=5238045 us)
   42727    TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=26 us)
12205347    TABLE ACCESS FULL PO_LINE (cr=249180 pr=109023 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  db file sequential read                        10        0.01          0.02
  db file scattered read                        906        0.04          3.16
  SQL*Net message from client                     3        0.00          0.00
  SQL*Net more data to client                    36        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.05          1          9          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      4.64      17.70     249545     254589          0        1815
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      4.64      17.76     249546     254598          0        1815

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    1815  HASH GROUP BY (cr=254589 pr=249545 pw=0 time=259 us cost=21200 size=250514 card=4246)
    1815   HASH JOIN  (cr=254589 pr=249545 pw=0 time=967 us cost=21199 size=250514 card=4246)
    4246    VIEW  VW_GBC_5 (cr=249562 pr=249545 pw=0 time=1747 us cost=20796 size=89166 card=4246)
    4246     HASH GROUP BY (cr=249562 pr=249545 pw=0 time=1123 us cost=20796 size=84920 card=4246)
12205347      TABLE ACCESS FULL PO_LINE (cr=249562 pr=249545 pw=0 time=16761216 us cost=20188 size=244106940 card=12205347)
   42727    TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=24305 us cost=402 size=252548 card=6646)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  direct path read                             1955        0.05         12.83
  asynch descriptor resize                        3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
  SQL*Net more data to client                     7        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.05          1          9          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      4.92      15.73     254188     254212          0        1815
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      4.92      15.79     254189     254221          0        1815

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    1815  HASH GROUP BY (cr=254212 pr=254188 pw=0 time=388 us cost=21149 size=250514 card=4246)
    1815   HASH JOIN  (cr=254212 pr=254188 pw=0 time=8465 us cost=21148 size=250514 card=4246)
    4246    VIEW  VW_GBC_5 (cr=249172 pr=249156 pw=0 time=1123 us cost=20746 size=89166 card=4246)
    4246     HASH GROUP BY (cr=249172 pr=249156 pw=0 time=374 us cost=20746 size=84920 card=4246)
12205347      TABLE ACCESS FULL PO_LINE (cr=249172 pr=249156 pw=0 time=3973985 us cost=20138 size=244106940 card=12205347)
   42727    TABLE ACCESS FULL PARTS (cr=5040 pr=5032 pw=0 time=18548 us cost=402 size=252548 card=6646)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  direct path read                             1889        0.08         10.13
  asynch descriptor resize                        4        0.00          0.00
  db file scattered read                         55        0.04          0.94
  SQL*Net message from client                     3        0.00          0.00
  SQL*Net more data to client                     7        0.00          0.00   

For those parts in warehouses with more than 160 warehouse locations, determines the number of parts with each distinct product code that resides in the warehouse: 

SELECT
  PRODUCT_CODE,
  COUNT(*) PARTS_LARGE_WH
FROM
  (SELECT
    WAREHOUSE_ID
  FROM
    LOCATIONS
  GROUP BY
    WAREHOUSE_ID
  HAVING
    COUNT(*)>160) W,
  PARTS P
WHERE
  W.WAREHOUSE_ID=P.PRIMARY_WHS_ID
GROUP BY
  PRODUCT_CODE
ORDER BY
  PRODUCT_CODE 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.04         16       5016          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.07       0.07         16       5016          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         5          5          5  SORT GROUP BY (cr=5016 pr=16 pw=0 time=0 us cost=402 size=115 card=5)
     37891      37891      37891   HASH JOIN  (cr=5016 pr=16 pw=0 time=35338 us cost=401 size=120612 card=5244)
         4          4          4    VIEW  (cr=20 pr=16 pw=0 time=6 us cost=4 size=9 card=1)
         4          4          4     FILTER  (cr=20 pr=16 pw=0 time=6 us)
        20         20         20      HASH GROUP BY (cr=20 pr=16 pw=0 time=19 us cost=4 size=9 card=1)
      2200       2200       2200       INDEX FAST FULL SCAN SYS_C0024962 (cr=20 pr=16 pw=0 time=244 us cost=3 size=19800 card=2200)(object id 82045)
     99694      99694      99694    TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=39416 us cost=396 size=1395716 card=99694)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                          2        0.00          0.00
  asynch descriptor resize                        1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.09          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.09         14       5057          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.18         14       5057          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  SORT GROUP BY (cr=5057 pr=14 pw=0 time=90608 us)
  36233   HASH JOIN  (cr=5057 pr=14 pw=0 time=83180 us)
      4    VIEW  (cr=17 pr=14 pw=0 time=46755 us)
      4     FILTER  (cr=17 pr=14 pw=0 time=46754 us)
     20      HASH GROUP BY (cr=17 pr=14 pw=0 time=46769 us)
   2200       INDEX FAST FULL SCAN SYS_C004567 (cr=17 pr=14 pw=0 time=25602 us)(object id 47888)
  99694    TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=18 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.02          0.02
  db file scattered read                          2        0.02          0.02
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.14         14       5057          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.16         14       5057          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  SORT GROUP BY (cr=5057 pr=14 pw=0 time=147128 us)
  36229   HASH JOIN  (cr=5057 pr=14 pw=0 time=140062 us)
      4    VIEW  (cr=17 pr=14 pw=0 time=103639 us)
      4     FILTER  (cr=17 pr=14 pw=0 time=103639 us)
     20      HASH GROUP BY (cr=17 pr=14 pw=0 time=103633 us)
   2200       INDEX FAST FULL SCAN SYS_C0012074 (cr=17 pr=14 pw=0 time=58040 us)(object id 51693)
  99694    TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=20 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.01          0.01
  db file scattered read                          2        0.04          0.08
  SQL*Net message from client                     2        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.08         14       5045          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.11         14       5045          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  SORT GROUP BY (cr=5045 pr=14 pw=0 time=0 us cost=406 size=115 card=5)
  37881   HASH JOIN  (cr=5045 pr=14 pw=0 time=42870 us cost=405 size=120612 card=5244)
      4    VIEW  (cr=18 pr=14 pw=0 time=6 us cost=4 size=9 card=1)
      4     FILTER  (cr=18 pr=14 pw=0 time=3 us)
     20      HASH GROUP BY (cr=18 pr=14 pw=0 time=19 us cost=4 size=9 card=1)
   2200       INDEX FAST FULL SCAN SYS_C009200 (cr=18 pr=14 pw=0 time=0 us cost=3 size=19800 card=2200)(object id 20300)
  99694    TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=36473 us cost=400 size=1395716 card=99694)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.01          0.01
  db file scattered read                          2        0.01          0.02
  asynch descriptor resize                        1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.06         14       5058          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.07       0.10         14       5058          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  SORT GROUP BY (cr=5058 pr=14 pw=0 time=0 us cost=406 size=115 card=5)
  37897   HASH JOIN  (cr=5058 pr=14 pw=0 time=31261 us cost=405 size=127328 card=5536)
      4    VIEW  (cr=18 pr=14 pw=0 time=6 us cost=4 size=9 card=1)
      4     FILTER  (cr=18 pr=14 pw=0 time=3 us)
     20      HASH GROUP BY (cr=18 pr=14 pw=0 time=19 us cost=4 size=9 card=1)
   2200       INDEX FAST FULL SCAN SYS_C0024649 (cr=18 pr=14 pw=0 time=244 us cost=3 size=19800 card=2200)(object id 83195)
  99694    TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=37368 us cost=400 size=1395716 card=99694)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.01          0.01
  db file scattered read                          2        0.00          0.00
  asynch descriptor resize                        2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

Simple query that finds the number of parts for which there are more than 1000 in inventory:

SELECT
  COUNT(*)
FROM
  PARTS
WHERE
  QTY_ON_HAND>1000 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.02          0       4996          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.04          0       4996          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=4996 pr=0 pw=0 time=0 us)
     98586      98586      98586   TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=29025 us cost=400 size=690879 card=98697)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.02          0       5040          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0       5040          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5040 pr=0 pw=0 time=29574 us)
  98586   TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.03          0       5040          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0       5040          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5040 pr=0 pw=0 time=30228 us)
  98586   TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.03          0       5027          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.04          0       5027          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5027 pr=0 pw=0 time=0 us)
  98586   TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=34396 us cost=404 size=690879 card=98697)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.02          0       5040          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.04          0       5040          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5040 pr=0 pw=0 time=0 us)
  98586   TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=29665 us cost=404 size=690879 card=98697)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00   

Simple query that determines the vendors that have a zipcode larger than 44444:

SELECT
  COUNT(*)
FROM
  VENDORS
WHERE
  ZIPCODE>' 44444' 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.05       2492       2497          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.07       2492       2497          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=2497 pr=2492 pw=0 time=0 us)
      5528       5528       5528   TABLE ACCESS FULL VENDORS (cr=2497 pr=2492 pw=0 time=376 us cost=198 size=16289 card=2327)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                         35        0.00          0.03
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.61       2513       2518          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.62       2513       2518          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=2518 pr=2513 pw=0 time=616794 us)
   5528   TABLE ACCESS FULL VENDORS (cr=2518 pr=2513 pw=0 time=559645 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                         35        0.05          0.59
  SQL*Net message from client                     2        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.24       2513       2518          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.25       2513       2518          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=2518 pr=2513 pw=0 time=247456 us)
   5528   TABLE ACCESS FULL VENDORS (cr=2518 pr=2513 pw=0 time=227501 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.01          0.01
  db file scattered read                         35        0.03          0.22
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.45       2500       2505          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.47       2500       2505          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=2505 pr=2500 pw=0 time=0 us)
   5528   TABLE ACCESS FULL VENDORS (cr=2505 pr=2500 pw=0 time=628 us cost=200 size=16289 card=2327)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         4        0.01          0.01
  db file scattered read                         37        0.04          0.42
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.50       2513       2518          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.52       2513       2518          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=2518 pr=2513 pw=0 time=0 us)
   5528   TABLE ACCESS FULL VENDORS (cr=2518 pr=2513 pw=0 time=502 us cost=200 size=16289 card=2327)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                         35        0.04          0.46
  SQL*Net message from client                     2        0.00          0.00   

Find the number of purchase order lines for purchase orders in the range from 10000 to 20000.  The query contains an unnecessary join to the PARTS table, which the optimizer should eliminate:

SELECT
  COUNT(*)
FROM
  PO_LINE POL,
  PARTS P
WHERE
  POL.PURC_ORDER_ID BETWEEN '10000' AND '20000'
  AND POL.PART_ID=P.PART_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          3          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          3          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=3 pr=3 pw=0 time=0 us)
         0          0          0   TABLE ACCESS BY INDEX ROWID PO_LINE (cr=3 pr=3 pw=0 time=0 us cost=4 size=525 card=25)
         0          0          0    INDEX RANGE SCAN SYS_C0025101 (cr=3 pr=3 pw=0 time=0 us cost=3 size=0 card=25)(object id 82070)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         3        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.09          6          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.10          6          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=6 pw=0 time=91072 us)
      0   TABLE ACCESS BY INDEX ROWID PO_LINE (cr=4 pr=6 pw=0 time=91066 us)
      0    INDEX RANGE SCAN SYS_C004706 (cr=4 pr=6 pw=0 time=91066 us)(object id 47913)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         2        0.04          0.05
  db file scattered read                          1        0.03          0.03
  SQL*Net message from client                     2        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.06          6          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.07          6          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=6 pw=0 time=64632 us)
      0   TABLE ACCESS BY INDEX ROWID PO_LINE (cr=4 pr=6 pw=0 time=64623 us)
      0    INDEX RANGE SCAN SYS_C0012213 (cr=4 pr=6 pw=0 time=64623 us)(object id 51718)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         2        0.03          0.06
  db file scattered read                          1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00 

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.06          7          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.08          7          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=7 pw=0 time=0 us)
      0   TABLE ACCESS BY INDEX ROWID PO_LINE (cr=4 pr=7 pw=0 time=0 us cost=5 size=525 card=25)
      0    INDEX RANGE SCAN SYS_C009339 (cr=4 pr=7 pw=0 time=0 us cost=3 size=0 card=25)(object id 20325)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         3        0.02          0.06
  db file scattered read                          1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.04          6          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.06          6          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=6 pw=0 time=0 us)
      0   TABLE ACCESS BY INDEX ROWID PO_LINE (cr=4 pr=6 pw=0 time=0 us cost=5 size=525 card=25)
      0    INDEX RANGE SCAN SYS_C0024788 (cr=4 pr=6 pw=0 time=0 us cost=3 size=0 card=25)(object id 83220)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         2        0.01          0.01
  db file scattered read                          1        0.02          0.02
  SQL*Net message from client                     2        0.00          0.00   

Analytic example to find statistics about related parts:

SELECT
  PART_ID,
  ABC_CODE,
  PRODUCT_CODE,
  MAX(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE,ABC_CODE) MAX_QTY_PRD_ABC,
  MIN(QTY_ON_HAND) OVER (PARTITION BY PRODUCT_CODE,ABC_CODE) MIN_QTY_PRD_ABC,
  DENSE_RANK() OVER (PARTITION BY PRODUCT_CODE,ABC_CODE ORDER BY QTY_ON_HAND) DR_QTY_PRD_ABC,
  DENSE_RANK() OVER (PARTITION BY PREF_VENDOR_ID ORDER BY ORDER_POINT) DR_OP_VEND
FROM
  PARTS
ORDER BY
  PART_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.42       0.42          0       4996          0       99694
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.45       0.44          0       4996          0       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     99694      99694      99694  SORT ORDER BY (cr=4996 pr=0 pw=0 time=43439 us cost=2902 size=3888066 card=99694)
     99694      99694      99694   WINDOW SORT (cr=4996 pr=0 pw=0 time=78321 us cost=2902 size=3888066 card=99694)
     99694      99694      99694    WINDOW SORT (cr=4996 pr=0 pw=0 time=89710 us cost=2902 size=3888066 card=99694)
     99694      99694      99694     TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=43511 us cost=399 size=3888066 card=99694)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     101        0.00          0.00
  SQL*Net message from client                   101        0.00          0.16
  SQL*Net more data to client                   398        0.00          0.01 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.06          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.45       0.41          0       5040          0       99694
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.46       0.47          0       5040          0       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
  99694  SORT ORDER BY (cr=5040 pr=0 pw=0 time=299155 us)
  99694   WINDOW SORT (cr=5040 pr=0 pw=0 time=217786 us)
  99694    WINDOW SORT (cr=5040 pr=0 pw=0 time=662142 us)
  99694     TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=29 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     101        0.00          0.00
  SQL*Net message from client                   101        0.00          0.14
  SQL*Net more data to client                  1595        0.00          0.02

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.49       0.41          0       5040          0       99694
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.49       0.45          0       5040          0       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
  99694  SORT ORDER BY (cr=5040 pr=0 pw=0 time=295718 us)
  99694   WINDOW SORT (cr=5040 pr=0 pw=0 time=211922 us)
  99694    WINDOW SORT (cr=5040 pr=0 pw=0 time=64268 us)
  99694     TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=20 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     101        0.00          0.00
  SQL*Net message from client                   101        0.00          0.14
  SQL*Net more data to client                  1595        0.00          0.02

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.40       0.41          0       5027          0       99694
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.42       0.43          0       5027          0       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
  99694  SORT ORDER BY (cr=5027 pr=0 pw=0 time=42414 us cost=2906 size=3888066 card=99694)
  99694   WINDOW SORT (cr=5027 pr=0 pw=0 time=83568 us cost=2906 size=3888066 card=99694)
  99694    WINDOW SORT (cr=5027 pr=0 pw=0 time=91758 us cost=2906 size=3888066 card=99694)
  99694     TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=51062 us cost=403 size=3888066 card=99694)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     101        0.00          0.00
  SQL*Net message from client                   101        0.00          0.16
  SQL*Net more data to client                   396        0.00          0.00 

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.39       0.40          0       5040          0       99694
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.40       0.42          0       5040          0       99694

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
  99694  SORT ORDER BY (cr=5040 pr=0 pw=0 time=47668 us cost=2906 size=3888066 card=99694)
  99694   WINDOW SORT (cr=5040 pr=0 pw=0 time=83184 us cost=2906 size=3888066 card=99694)
  99694    WINDOW SORT (cr=5040 pr=0 pw=0 time=91630 us cost=2906 size=3888066 card=99694)
  99694     TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=40824 us cost=403 size=3888066 card=99694)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     101        0.00          0.00
  SQL*Net message from client                   101        0.00          0.17
  SQL*Net more data to client                   398        0.00          0.00   

Query to find those vendors from which we have never ordered parts with a FG product code:

SELECT
  V.VENDOR_ID,
  V.VENDOR_NAME
FROM
  VENDORS V,
  (SELECT DISTINCT
    PO.VENDOR_ID
  FROM
    PO_HEADER PO,
    PO_LINE POL,
    PARTS P
  WHERE
    PO.PURC_ORDER_ID=POL.PURC_ORDER_ID
    AND POL.PART_ID=P.PART_ID
    AND P.PRODUCT_CODE='FG') PV
WHERE
  V.VENDOR_ID=PV.VENDOR_ID(+)
  AND PV.VENDOR_ID IS NULL
ORDER BY
  V.VENDOR_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       43      8.65       8.76     249049     269722          0       41120
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       45      8.70       8.81     249049     269722          0       41120

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     41120      41120      41120  MERGE JOIN ANTI (cr=269722 pr=249049 pw=0 time=34201 us cost=27359 size=1632120 card=40803)
     49786      49786      49786   SORT JOIN (cr=2497 pr=0 pw=0 time=15357 us cost=531 size=1443794 card=49786)
     49786      49786      49786    TABLE ACCESS FULL VENDORS (cr=2497 pr=0 pw=0 time=13566 us cost=197 size=1443794 card=49786)
      8666       8666       8666   SORT UNIQUE (cr=267225 pr=249049 pw=0 time=0 us cost=26828 size=98813 card=8983)
      8666       8666       8666    VIEW  (cr=267225 pr=249049 pw=0 time=1529 us cost=26827 size=98813 card=8983)
      8666       8666       8666     HASH UNIQUE (cr=267225 pr=249049 pw=0 time=637 us cost=26827 size=521014 card=8983)
   9176048    9176048    9176048      HASH JOIN  (cr=267225 pr=249049 pw=0 time=7566066 us cost=26218 size=707903050 card=12205225)
     74768      74768      74768       TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=16231 us cost=395 size=338963 card=19939)
  12205347   12205347   12205347       HASH JOIN  (cr=262229 pr=249049 pw=0 time=5527253 us cost=25766 size=500419227 card=12205347)
    500000     500000     500000        TABLE ACCESS FULL PO_HEADER (cr=13173 pr=0 pw=0 time=63731 us cost=1038 size=10000000 card=500000)
  12205347   12205347   12205347        TABLE ACCESS FULL PO_LINE (cr=249056 pr=249049 pw=0 time=1461748 us cost=19651 size=256312287 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      43        0.00          0.00
  direct path read                                7        0.00          0.01
  asynch descriptor resize                        4        0.00          0.00
  SQL*Net message from client                    43        0.00          0.02
  SQL*Net more data to client                   124        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.09          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       43      8.01       8.01          0     269978          0       41120
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       45      8.01       8.11          0     269978          0       41120

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
   41120  MERGE JOIN ANTI (cr=269978 pr=0 pw=0 time=7967223 us)
   49786   SORT JOIN (cr=2518 pr=0 pw=0 time=17624 us)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=20 us)
    8666   SORT UNIQUE (cr=267460 pr=0 pw=0 time=7964103 us)
    8666    VIEW  (cr=267460 pr=0 pw=0 time=7943712 us)
    8666     HASH UNIQUE (cr=267460 pr=0 pw=0 time=7943710 us)
 9176048      HASH JOIN  (cr=267460 pr=0 pw=0 time=218654 us)
   74768       TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=25 us)
12205347       HASH JOIN  (cr=262420 pr=0 pw=0 time=186465 us)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=23 us)
12205347        TABLE ACCESS FULL PO_LINE (cr=249230 pr=0 pw=0 time=22 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      43        0.00          0.00
  SQL*Net message from client                    43        0.00          0.02
  SQL*Net more data to client                   610        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       43      8.48      11.12     109794     269928          0       41120
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       45      8.50      11.16     109794     269928          0       41120

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
   41120  MERGE JOIN ANTI (cr=269928 pr=109794 pw=0 time=11070193 us)
   49786   SORT JOIN (cr=2518 pr=0 pw=0 time=17744 us)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=20 us)
    8666   SORT UNIQUE (cr=267410 pr=109794 pw=0 time=11066943 us)
    8666    VIEW  (cr=267410 pr=109794 pw=0 time=11046105 us)
    8666     HASH UNIQUE (cr=267410 pr=109794 pw=0 time=11046103 us)
 9176048      HASH JOIN  (cr=267410 pr=109794 pw=0 time=221402 us)
   74768       TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=24 us)
12205347       HASH JOIN  (cr=262370 pr=109794 pw=0 time=188692 us)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=21 us)
12205347        TABLE ACCESS FULL PO_LINE (cr=249180 pr=109794 pw=0 time=20 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      43        0.00          0.00
  db file sequential read                       196        0.03          0.11
  db file scattered read                        941        0.04          2.61
  SQL*Net message from client                    43        0.00          0.01
  SQL*Net more data to client                   610        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       43      8.33      17.85     249545     270284          0       41120
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       45      8.39      17.91     249545     270284          0       41120

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
   41120  MERGE JOIN ANTI (cr=270284 pr=249545 pw=0 time=29462 us cost=27821 size=1632120 card=40803)
   49786   SORT JOIN (cr=2505 pr=0 pw=0 time=12798 us cost=533 size=1443794 card=49786)
   49786    TABLE ACCESS FULL VENDORS (cr=2505 pr=0 pw=0 time=14589 us cost=199 size=1443794 card=49786)
    8666   SORT UNIQUE (cr=267779 pr=249545 pw=0 time=0 us cost=27288 size=98813 card=8983)
    8666    VIEW  (cr=267779 pr=249545 pw=0 time=2293 us cost=27287 size=98813 card=8983)
    8666     HASH UNIQUE (cr=267779 pr=249545 pw=0 time=1146 us cost=27287 size=521014 card=8983)
 9176048      HASH JOIN  (cr=267779 pr=249545 pw=0 time=21976024 us cost=26678 size=707903050 card=12205225)
   74768       TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=19426 us cost=399 size=338963 card=19939)
12205347       HASH JOIN  (cr=262752 pr=249545 pw=0 time=18076660 us cost=26222 size=500419227 card=12205347)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=63347 us cost=1118 size=10000000 card=500000)
12205347        TABLE ACCESS FULL PO_LINE (cr=249562 pr=249545 pw=0 time=14012692 us cost=20028 size=256312287 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      43        0.00          0.00
  direct path read                             1950        0.05          9.21
  asynch descriptor resize                        4        0.00          0.00
  SQL*Net message from client                    43        0.00          0.03

  SQL*Net more data to client                   124        0.00          0.00
 

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       43      8.62      17.57     262329     269920          0       41120
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       45      8.67      17.62     262329     269920          0       41120

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
   41120  MERGE JOIN ANTI (cr=269920 pr=262329 pw=0 time=30486 us cost=27771 size=1632120 card=40803)
   49786   SORT JOIN (cr=2518 pr=0 pw=0 time=14206 us cost=533 size=1443794 card=49786)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=16125 us cost=199 size=1443794 card=49786)
    8666   SORT UNIQUE (cr=267402 pr=262329 pw=0 time=0 us cost=27238 size=98813 card=8983)
    8666    VIEW  (cr=267402 pr=262329 pw=0 time=2038 us cost=27237 size=98813 card=8983)
    8666     HASH UNIQUE (cr=267402 pr=262329 pw=0 time=1146 us cost=27237 size=521014 card=8983)
 9176048      HASH JOIN  (cr=267402 pr=262329 pw=0 time=13292648 us cost=26628 size=707903050 card=12205225)
   74768       TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=19554 us cost=399 size=338963 card=19939)
12205347       HASH JOIN  (cr=262362 pr=262329 pw=0 time=6456142 us cost=26172 size=500419227 card=12205347)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=13173 pw=0 time=3462112 us cost=1118 size=10000000 card=500000)
12205347        TABLE ACCESS FULL PO_LINE (cr=249172 pr=249156 pw=0 time=2428525 us cost=19977 size=256312287 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      43        0.00          0.00
  db file scattered read                        120        0.10          2.49
  direct path read                             1218        0.07          6.37
  asynch descriptor resize                        4        0.00          0.00
  SQL*Net message from client                    43        0.00          0.02
  SQL*Net more data to client                   124        0.00          0.00   

Determine the storage location and preferred vendor for all office and shop products that have a unit of measure that is EA, PC, or CASE:

SELECT
  P.PART_ID,
  P.DESCRIPTION PART_DESCRIPTION,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  U.DESCRIPTION UMS_DESCRIPTION,
  U.UOM_SCALE,
  L.DESCRIPTION LOC_DESCRIPTION,
  V.VENDOR_ID,
  V.VENDOR_NAME,
  V.ADDR_1,
  V.ADDR_2,
  V.CITY,
  V.STATE
FROM
  UMS U,
  LOCATIONS L,
  PARTS P,
  VENDORS V
WHERE
  P.PRODUCT_CODE IN ('OFFICE','SHOP')
  AND P.PURCHASED='Y'
  AND P.STOCK_UM=U.UNIT_OF_MEASURE
  AND U.UNIT_OF_MEASURE IN ('EA','PC','CASE')
  AND P.PREF_VENDOR_ID=V.VENDOR_ID
  AND P.PRIMARY_WHS_ID=L.WAREHOUSE_ID
  AND P.PRIMARY_LOC_ID=L.LOCATION_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.06       0.06         31       7534          0        6647
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.10       0.10         31       7534          0        6647

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      6647       6647       6647  HASH JOIN  (cr=7534 pr=31 pw=0 time=9643 us cost=602 size=720148 card=2518)
      6647       6647       6647   HASH JOIN  (cr=5030 pr=31 pw=0 time=28501 us cost=404 size=392808 card=2518)
      2200       2200       2200    TABLE ACCESS FULL LOCATIONS (cr=31 pr=29 pw=0 time=366 us cost=4 size=176000 card=2200)
      6647       6647       6647    HASH JOIN  (cr=4999 pr=2 pw=0 time=26328 us cost=399 size=191368 card=2518)
         3          3          3     TABLE ACCESS FULL UMS (cr=3 pr=2 pw=0 time=0 us cost=2 size=21 card=3)
      6647       6647       6647     TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=23900 us cost=397 size=463335 card=6715)
     49786      49786      49786   TABLE ACCESS FULL VENDORS (cr=2504 pr=0 pw=0 time=16808 us cost=197 size=6472180 card=49786)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  db file sequential read                         3        0.00          0.00
  db file scattered read                          4        0.00          0.00
  asynch descriptor resize                        3        0.00          0.00
  SQL*Net message from client                     8        0.00          0.01
  SQL*Net more data to client                   108        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.11          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.06       0.12         31       7602          0        6647
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.09       0.23         31       7602          0        6647

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
   6647  HASH JOIN  (cr=7602 pr=31 pw=0 time=96852 us)
   6647   HASH JOIN  (cr=5077 pr=31 pw=0 time=88946 us)
   2200    TABLE ACCESS FULL LOCATIONS (cr=31 pr=29 pw=0 time=16541 us)
   6647    HASH JOIN  (cr=5046 pr=2 pw=0 time=49703 us)
      3     INLIST ITERATOR  (cr=6 pr=2 pw=0 time=22888 us)
      3      TABLE ACCESS BY INDEX ROWID UMS (cr=6 pr=2 pw=0 time=22913 us)
      3       INDEX UNIQUE SCAN SYS_C004571 (cr=3 pr=1 pw=0 time=332 us)(object id 47891)
   6647     TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=26601 us)
  49786   TABLE ACCESS FULL VENDORS (cr=2525 pr=0 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  db file sequential read                         3        0.02          0.03
  db file scattered read                          4        0.01          0.01
  SQL*Net message from client                     8        0.00          0.01
  SQL*Net more data to client                   455        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.06       0.14         31       7602          0        6647
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.12       0.19         31       7602          0        6647

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
   6647  HASH JOIN  (cr=7602 pr=31 pw=0 time=106066 us)
   6647   HASH JOIN  (cr=5077 pr=31 pw=0 time=98872 us)
   2200    TABLE ACCESS FULL LOCATIONS (cr=31 pr=29 pw=0 time=23994 us)
   6647    HASH JOIN  (cr=5046 pr=2 pw=0 time=30133 us)
      3     INLIST ITERATOR  (cr=6 pr=2 pw=0 time=10008 us)
      3      TABLE ACCESS BY INDEX ROWID UMS (cr=6 pr=2 pw=0 time=9997 us)
      3       INDEX UNIQUE SCAN SYS_C0012078 (cr=3 pr=1 pw=0 time=2124 us)(object id 51696)
   6647     TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=19957 us)
  49786   TABLE ACCESS FULL VENDORS (cr=2525 pr=0 pw=0 time=19 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  db file sequential read                         3        0.02          0.03
  db file scattered read                          4        0.02          0.03
  SQL*Net message from client                     8        0.00          0.01
  SQL*Net more data to client                   454        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.04       0.12         31       7573          0        6647
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.07       0.16         31       7573          0        6647

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
   6647  HASH JOIN  (cr=7573 pr=31 pw=0 time=6124 us cost=608 size=712426 card=2491)
   6647   HASH JOIN  (cr=5061 pr=31 pw=0 time=33102 us cost=408 size=388596 card=2491)
   2200    TABLE ACCESS FULL LOCATIONS (cr=31 pr=29 pw=0 time=244 us cost=4 size=176000 card=2200)
   6647    HASH JOIN  (cr=5030 pr=2 pw=0 time=30801 us cost=403 size=189316 card=2491)
      3     INLIST ITERATOR  (cr=3 pr=2 pw=0 time=32 us)
      3      TABLE ACCESS BY INDEX ROWID UMS (cr=3 pr=2 pw=0 time=0 us cost=2 size=21 card=3)
      3       INDEX UNIQUE SCAN SYS_C009204 (cr=2 pr=1 pw=0 time=0 us cost=1 size=0 card=3)(object id 20303)
   6647     TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=28245 us cost=401 size=458367 card=6643)
  49786   TABLE ACCESS FULL VENDORS (cr=2512 pr=0 pw=0 time=18091 us cost=199 size=6472180 card=49786)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  db file sequential read                         3        0.01          0.01
  db file scattered read                          4        0.02          0.04
  asynch descriptor resize                        3        0.00          0.00
  SQL*Net message from client                     8        0.00          0.01
  SQL*Net more data to client                   109        0.00          0.00  

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.06       0.12         31       7598          0        6647
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.10       0.17         31       7598          0        6647

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
   6647  HASH JOIN  (cr=7598 pr=31 pw=0 time=8470 us cost=608 size=710424 card=2484)
   6647   HASH JOIN  (cr=5074 pr=31 pw=0 time=32974 us cost=408 size=387504 card=2484)
   2200    TABLE ACCESS FULL LOCATIONS (cr=31 pr=29 pw=0 time=122 us cost=4 size=176000 card=2200)
   6647    HASH JOIN  (cr=5043 pr=2 pw=0 time=29140 us cost=403 size=188784 card=2484)
      3     INLIST ITERATOR  (cr=3 pr=2 pw=0 time=34 us)
      3      TABLE ACCESS BY INDEX ROWID UMS (cr=3 pr=2 pw=0 time=0 us cost=2 size=21 card=3)
      3       INDEX UNIQUE SCAN SYS_C0024653 (cr=2 pr=1 pw=0 time=0 us cost=1 size=0 card=3)(object id 83198)
   6647     TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=26328 us cost=401 size=456987 card=6623)
  49786   TABLE ACCESS FULL VENDORS (cr=2524 pr=0 pw=0 time=21299 us cost=199 size=6472180 card=49786)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  db file sequential read                         3        0.01          0.01
  db file scattered read                          4        0.02          0.05
  asynch descriptor resize                        3        0.00          0.00
  SQL*Net message from client                     8        0.00          0.01
  SQL*Net more data to client                   109        0.00          0.00   

Locating the most recent vendor for each part:

SELECT
  VENDOR_ID,
  PART_ID,
  DESIRED_RECV_DATE
FROM
  (SELECT
    PO.VENDOR_ID,
    POL.PART_ID,
    POL.DESIRED_RECV_DATE,
    ROW_NUMBER() OVER (PARTITION BY POL.PART_ID ORDER BY POL.DESIRED_RECV_DATE DESC) RN
  FROM
    PO_HEADER PO,
    PO_LINE POL
  WHERE
    PO.PURC_ORDER_ID=POL.PURC_ORDER_ID)
WHERE
  RN=1
ORDER BY
  PART_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6     25.44      25.67     252854     262229         63        4246
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8     25.45      25.69     252854     262229         63        4246

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      4246       4246       4246  VIEW  (cr=262229 pr=252854 pw=3805 time=837165 us cost=150135 size=585856656 card=12205347)
    519965     519965     519965   WINDOW SORT PUSHED RANK (cr=262229 pr=252854 pw=3805 time=844973 us cost=150135 size=598062003 card=12205347)
  12205347   12205347   12205347    HASH JOIN  (cr=262229 pr=249049 pw=0 time=6314575 us cost=27081 size=598062003 card=12205347)
    500000     500000     500000     TABLE ACCESS FULL PO_HEADER (cr=13173 pr=0 pw=0 time=61684 us cost=1038 size=10000000 card=500000)
  12205347   12205347   12205347     TABLE ACCESS FULL PO_LINE (cr=249056 pr=249049 pw=0 time=1914609 us cost=19811 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  direct path read                                8        0.05          0.06
  Disk file operations I/O                        1        0.00          0.00
  asynch descriptor resize                        4        0.00          0.00
  direct path write temp                          1        0.00          0.00
  direct path read temp                         128        0.00          0.08
  SQL*Net message from client                     6        0.00          0.00
  SQL*Net more data to client                    13        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6     27.79      28.59       3624     262420        112        4246
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8     27.79      28.62       3624     262420        112        4246

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    4246  VIEW  (cr=262420 pr=3624 pw=3622 time=28575597 us)
    8895   WINDOW SORT PUSHED RANK (cr=262420 pr=3624 pw=3622 time=28491688 us)
12205347    HASH JOIN  (cr=262420 pr=0 pw=0 time=174567 us)
  500000     TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=28 us)
12205347     TABLE ACCESS FULL PO_LINE (cr=249230 pr=0 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  db file sequential read                         2        0.00          0.00
  local write wait                               78        0.00          0.01
  direct path read temp                          98        0.03          0.49
  SQL*Net message from client                     6        0.00          0.00
  SQL*Net more data to client                    68        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6     28.92      30.65     116653     262370         59        4246
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8     28.93      30.66     116653     262370         59        4246

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    4246  VIEW  (cr=262370 pr=116653 pw=3563 time=30646863 us)
    8928   WINDOW SORT PUSHED RANK (cr=262370 pr=116653 pw=3563 time=30551159 us)
12205347    HASH JOIN  (cr=262370 pr=113090 pw=0 time=176948 us)
  500000     TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=29 us)
12205347     TABLE ACCESS FULL PO_LINE (cr=249180 pr=113090 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  db file sequential read                       222        0.01          0.23
  db file scattered read                       1229        0.04          1.31
  i/o slave wait                                163        0.03          0.36
  direct path write temp                          3        0.00          0.00
  direct path read temp                         118        0.03          0.30
  SQL*Net message from client                     6        0.00          0.00
  SQL*Net more data to client                    68        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6     27.33      31.14     253289     262752        116        4246
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8     27.34      31.16     253289     262752        116        4246

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    4246  VIEW  (cr=262752 pr=253289 pw=3742 time=659004 us cost=150591 size=585856656 card=12205347)
  511557   WINDOW SORT PUSHED RANK (cr=262752 pr=253289 pw=3742 time=1603029 us cost=150591 size=598062003 card=12205347)
12205347    HASH JOIN  (cr=262752 pr=249545 pw=0 time=8244288 us cost=27537 size=598062003 card=12205347)
  500000     TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=66035 us cost=1118 size=10000000 card=500000)
12205347     TABLE ACCESS FULL PO_LINE (cr=249562 pr=249545 pw=0 time=3445861 us cost=20188 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  direct path read                              157        0.04          1.88
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         2        0.03          0.05
  local write wait                               81        0.00          0.01
  asynch descriptor resize                        4        0.00          0.00
  direct path read temp                         102        0.03          0.50
  SQL*Net message from client                     6        0.00          0.00
  SQL*Net more data to client                    13        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6     26.52      30.65     252898     262362         62        4246
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8     26.55      30.68     252898     262362         62        4246

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
   4246  VIEW  (cr=262362 pr=252898 pw=3742 time=655016 us cost=150541 size=585856656 card=12205347)
  511304   WINDOW SORT PUSHED RANK (cr=262362 pr=252898 pw=3742 time=699361 us cost=150541 size=598062003 card=12205347)
12205347    HASH JOIN  (cr=262362 pr=249156 pw=0 time=10892972 us cost=27487 size=598062003 card=12205347)
  500000     TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=67570 us cost=1118 size=10000000 card=500000)
 12205347     TABLE ACCESS FULL PO_LINE (cr=249172 pr=249156 pw=0 time=6413774 us cost=20138 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  direct path read                              218        0.06          3.72
  Disk file operations I/O                        1        0.00          0.00
  asynch descriptor resize                        4        0.00          0.00
  direct path write temp                          2        0.00          0.01
  direct path read temp                         126        0.04          0.44
  SQL*Net message from client                     6        0.00          0.00
  SQL*Net more data to client                    13        0.00          0.00   

Comparing the most recent vendor for each part with the expected vendor for each purchased part that has either an OFFICE or SHOP product code:

SELECT
  P.PART_ID,
  P.DESCRIPTION PART_DESCRIPTION,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  U.DESCRIPTION UMS_DESCRIPTION,
  U.UOM_SCALE,
  L.DESCRIPTION LOC_DESCRIPTION,
  V.VENDOR_ID,
  V.VENDOR_NAME,
  V.ADDR_1,
  V.ADDR_2,
  V.CITY,
  V.STATE,
  LV.VENDOR_ID LAST_VENDOR_ID,
  V2.VENDOR_NAME LAST_VENDOR_NAME,
  V2.STATE LAST_VENDOR_STATE,
  LV.DESIRED_RECV_DATE LAST_DES_RECV_DATE
FROM
  UMS U,
  LOCATIONS L,
  PARTS P,
  VENDORS V,
  (SELECT
    VENDOR_ID,
    PART_ID,
    DESIRED_RECV_DATE
  FROM
    (SELECT
      PO.VENDOR_ID,
      POL.PART_ID,
      POL.DESIRED_RECV_DATE,
      ROW_NUMBER() OVER (PARTITION BY POL.PART_ID ORDER BY POL.DESIRED_RECV_DATE DESC) RN
    FROM
      PO_HEADER PO,
      PO_LINE POL
    WHERE
      PO.PURC_ORDER_ID=POL.PURC_ORDER_ID)
  WHERE
    RN=1) LV,
  VENDORS V2
WHERE
  P.PRODUCT_CODE IN ('OFFICE','SHOP')
  AND P.PURCHASED='Y'
  AND P.STOCK_UM=U.UNIT_OF_MEASURE
  AND U.UNIT_OF_MEASURE IN ('EA','PC','CASE')
  AND P.PREF_VENDOR_ID=V.VENDOR_ID
  AND P.PRIMARY_WHS_ID=L.WAREHOUSE_ID
  AND P.PRIMARY_LOC_ID=L.LOCATION_ID
  AND P.PART_ID=LV.PART_ID
  AND LV.VENDOR_ID=V2.VENDOR_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.39       0.39          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     25.56      25.78     252854     272253         63         278
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     25.95      26.18     252854     272253         63         278

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       278        278        278  HASH JOIN  (cr=272253 pr=252854 pw=3805 time=535302 us cost=165495 size=1675035600 card=4576600)
      2200       2200       2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=488 us cost=4 size=176000 card=2200)
       278        278        278   HASH JOIN  (cr=272222 pr=252854 pw=3805 time=535164 us cost=165469 size=1308907600 card=4576600)
     49786      49786      49786    TABLE ACCESS FULL VENDORS (cr=2497 pr=0 pw=0 time=13822 us cost=197 size=1593152 card=49786)
       278        278        278    HASH JOIN  (cr=269725 pr=252854 pw=3805 time=534887 us cost=150791 size=1162456400 card=4576600)
      6647       6647       6647     HASH JOIN  (cr=7496 pr=0 pw=0 time=5879 us cost=598 size=518708 card=2518)
      6647       6647       6647      HASH JOIN  (cr=4999 pr=0 pw=0 time=25689 us cost=399 size=191368 card=2518)
         3          3          3       TABLE ACCESS FULL UMS (cr=3 pr=0 pw=0 time=2 us cost=2 size=21 card=3)
      6647       6647       6647       TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=23261 us cost=397 size=463335 card=6715)
     49786      49786      49786      TABLE ACCESS FULL VENDORS (cr=2497 pr=0 pw=0 time=14717 us cost=197 size=6472180 card=49786)
      4246       4246       4246     VIEW  (cr=262229 pr=252854 pw=3805 time=777459 us cost=150135 size=585856656 card=12205347)
    519965     519965     519965      WINDOW SORT PUSHED RANK (cr=262229 pr=252854 pw=3805 time=786536 us cost=150135 size=598062003 card=12205347)
  12205347   12205347   12205347       HASH JOIN  (cr=262229 pr=249049 pw=0 time=6259791 us cost=27081 size=598062003 card=12205347)
    500000     500000     500000        TABLE ACCESS FULL PO_HEADER (cr=13173 pr=0 pw=0 time=61812 us cost=1038 size=10000000 card=500000)
  12205347   12205347   12205347        TABLE ACCESS FULL PO_LINE (cr=249056 pr=249049 pw=0 time=1906545 us cost=19811 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  asynch descriptor resize                        9        0.00          0.00
  direct path read                                7        0.00          0.01
  direct path write temp                          1        0.00          0.00
  direct path read temp                         100        0.00          0.06
  SQL*Net message from client                     2        0.00          0.00
  SQL*Net more data to client                     6        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.60       0.81          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     28.04      28.91       3622     272533         60         278
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     28.65      29.73       3622     272533         60         278

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
     278  HASH JOIN  (cr=272533 pr=3622 pw=3622 time=28652546 us)
    2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=35 us)
     278   HASH JOIN  (cr=272502 pr=3622 pw=3622 time=28651159 us)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=13 us)
     278    HASH JOIN  (cr=269984 pr=3622 pw=3622 time=28629675 us)
    6647     HASH JOIN  (cr=7564 pr=0 pw=0 time=38331 us)
    6647      HASH JOIN  (cr=5046 pr=0 pw=0 time=26841 us)
       3       INLIST ITERATOR  (cr=6 pr=0 pw=0 time=47 us)
       3        TABLE ACCESS BY INDEX ROWID UMS (cr=6 pr=0 pw=0 time=38 us)
       3         INDEX UNIQUE SCAN SYS_C004571 (cr=3 pr=0 pw=0 time=15 us)(object id 47891)
    6647       TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=26610 us)
   49786      TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=24 us)
    4246     VIEW  (cr=262420 pr=3622 pw=3622 time=28787059 us)
    8895      WINDOW SORT PUSHED RANK (cr=262420 pr=3622 pw=3622 time=28681926 us)
12205347       HASH JOIN  (cr=262420 pr=0 pw=0 time=178699 us)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=24 us)
12205347        TABLE ACCESS FULL PO_LINE (cr=249230 pr=0 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  direct path write temp                          1        0.00          0.00
  direct path read temp                         119        0.04          0.78
  SQL*Net message from client                     2        0.00          0.00
  SQL*Net more data to client                    25        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.73       1.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     28.71      30.86     116593     272483         59         278
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     29.45      31.89     116593     272483         59         278

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
     278  HASH JOIN  (cr=272483 pr=116593 pw=3563 time=30547001 us)
    2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=32 us)
     278   HASH JOIN  (cr=272452 pr=116593 pw=3563 time=30545857 us)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=13 us)
     278    HASH JOIN  (cr=269934 pr=116593 pw=3563 time=30524105 us)
    6647     HASH JOIN  (cr=7564 pr=0 pw=0 time=31940 us)
    6647      HASH JOIN  (cr=5046 pr=0 pw=0 time=20180 us)
       3       INLIST ITERATOR  (cr=6 pr=0 pw=0 time=46 us)
       3        TABLE ACCESS BY INDEX ROWID UMS (cr=6 pr=0 pw=0 time=37 us)
       3         INDEX UNIQUE SCAN SYS_C0012078 (cr=3 pr=0 pw=0 time=16 us)(object id 51696)
    6647       TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=19965 us)
   49786      TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=22 us)
    4246     VIEW  (cr=262370 pr=116593 pw=3563 time=30599938 us)
    8928      WINDOW SORT PUSHED RANK (cr=262370 pr=116593 pw=3563 time=30473211 us)
12205347       HASH JOIN  (cr=262370 pr=113030 pw=0 time=179051 us)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=25 us)
12205347        TABLE ACCESS FULL PO_LINE (cr=249180 pr=113030 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                       241        0.03          0.25
  db file scattered read                       1145        0.04          1.17
  i/o slave wait                                152        0.04          0.57
  direct path write temp                          1        0.00          0.00
  direct path read temp                         102        0.04          0.48
  SQL*Net message from client                     2        0.00          0.00
  SQL*Net more data to client                    25        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.42       0.41          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     26.66      29.77     253287     272823         62         278
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     27.08      30.18     253287     272823         62         278

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
     278  HASH JOIN  (cr=272823 pr=253287 pw=3742 time=459543 us cost=165959 size=1675042188 card=4576618)
    2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=122 us cost=4 size=176000 card=2200)
     278   HASH JOIN  (cr=272792 pr=253287 pw=3742 time=459404 us cost=165933 size=1308912748 card=4576618)
   49786    TABLE ACCESS FULL VENDORS (cr=2505 pr=0 pw=0 time=17789 us cost=199 size=1593152 card=49786)
     278    HASH JOIN  (cr=270287 pr=253287 pw=3742 time=459127 us cost=151253 size=1162460972 card=4576618)
    6647     HASH JOIN  (cr=7535 pr=0 pw=0 time=5240 us cost=604 size=513146 card=2491)
    6647      HASH JOIN  (cr=5030 pr=0 pw=0 time=31951 us cost=403 size=189316 card=2491)
       3       INLIST ITERATOR  (cr=3 pr=0 pw=0 time=16 us)
       3        TABLE ACCESS BY INDEX ROWID UMS (cr=3 pr=0 pw=0 time=0 us cost=2 size=21 card=3)
       3         INDEX UNIQUE SCAN SYS_C009204 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=3)(object id 20303)
    6647       TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=29523 us cost=401 size=458367 card=6643)
   49786      TABLE ACCESS FULL VENDORS (cr=2505 pr=0 pw=0 time=16765 us cost=199 size=6472180 card=49786)
    4246     VIEW  (cr=262752 pr=253287 pw=3742 time=688439 us cost=150591 size=585856656 card=12205347)
  511557      WINDOW SORT PUSHED RANK (cr=262752 pr=253287 pw=3742 time=675760 us cost=150591 size=598062003 card=12205347)
12205347       HASH JOIN  (cr=262752 pr=249545 pw=0 time=7621317 us cost=27537 size=598062003 card=12205347)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=82032 us cost=1118 size=10000000 card=500000)
12205347        TABLE ACCESS FULL PO_LINE (cr=249562 pr=249545 pw=0 time=3013736 us cost=20188 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  asynch descriptor resize                        9        0.00          0.00
  direct path read                              178        0.06          2.38
  direct path write temp                          1        0.00          0.00
  direct path read temp                         125        0.02          0.53
  SQL*Net message from client                     2        0.00          0.00

  SQL*Net more data to client                     6        0.00          0.00
 

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.43       0.44          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     26.44      29.82     252898     272472         62         278
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     26.87      30.26     252898     272472         62         278

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
     278  HASH JOIN  (cr=272472 pr=252898 pw=3742 time=475747 us cost=165907 size=1674935682 card=4576327)
    2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=244 us cost=4 size=176000 card=2200)
     278   HASH JOIN  (cr=272441 pr=252898 pw=3742 time=475470 us cost=165881 size=1308829522 card=4576327)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=16125 us cost=199 size=1593152 card=49786)
     278    HASH JOIN  (cr=269923 pr=252898 pw=3742 time=475193 us cost=151203 size=1162387058 card=4576327)
    6647     HASH JOIN  (cr=7561 pr=0 pw=0 time=6773 us cost=604 size=511704 card=2484)
    6647      HASH JOIN  (cr=5043 pr=0 pw=0 time=26584 us cost=403 size=188784 card=2484)
       3       INLIST ITERATOR  (cr=3 pr=0 pw=0 time=16 us)
       3        TABLE ACCESS BY INDEX ROWID UMS (cr=3 pr=0 pw=0 time=0 us cost=2 size=21 card=3)
       3         INDEX UNIQUE SCAN SYS_C0024653 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=3)(object id 83198)
    6647       TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=24666 us cost=401 size=456987 card=6623)
   49786      TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=15101 us cost=199 size=6472180 card=49786)
    4246     VIEW  (cr=262362 pr=252898 pw=3742 time=713534 us cost=150541 size=585856656 card=12205347)
  511304      WINDOW SORT PUSHED RANK (cr=262362 pr=252898 pw=3742 time=688947 us cost=150541 size=598062003 card=12205347)
12205347       HASH JOIN  (cr=262362 pr=249156 pw=0 time=6328527 us cost=27487 size=598062003 card=12205347)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=68978 us cost=1118 size=10000000 card=500000)
12205347        TABLE ACCESS FULL PO_LINE (cr=249172 pr=249156 pw=0 time=1858929 us cost=20138 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  asynch descriptor resize                        9        0.00          0.00
  direct path read                              174        0.05          2.85
  direct path write temp                          1        0.00          0.00
  direct path read temp                         101        0.01          0.39
  SQL*Net message from client                     2        0.00          0.00
  SQL*Net more data to client                     6        0.00          0.00   

Comparing the most recent vendor for each part with the expected vendor for each purchased part that was excluded from the previous query:

SELECT
  P.PART_ID,
  P.DESCRIPTION PART_DESCRIPTION,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  U.DESCRIPTION UMS_DESCRIPTION,
  U.UOM_SCALE,
  L.DESCRIPTION LOC_DESCRIPTION,
  V.VENDOR_ID,
  V.VENDOR_NAME,
  V.ADDR_1,
  V.ADDR_2,
  V.CITY,
  V.STATE,
  LV.VENDOR_ID LAST_VENDOR_ID,
  V2.VENDOR_NAME LAST_VENDOR_NAME,
  V2.STATE LAST_VENDOR_STATE,
  LV.DESIRED_RECV_DATE LAST_DES_RECV_DATE
FROM
  UMS U,
  LOCATIONS L,
  PARTS P,
  VENDORS V,
  (SELECT
    VENDOR_ID,
    PART_ID,
    DESIRED_RECV_DATE
  FROM
    (SELECT
      PO.VENDOR_ID,
      POL.PART_ID,
      POL.DESIRED_RECV_DATE,
      ROW_NUMBER() OVER (PARTITION BY POL.PART_ID ORDER BY POL.DESIRED_RECV_DATE DESC) RN
    FROM
      PO_HEADER PO,
      PO_LINE POL
    WHERE
      PO.PURC_ORDER_ID=POL.PURC_ORDER_ID)
  WHERE
    RN=1) LV,
  VENDORS V2
WHERE
  (P.PRODUCT_CODE NOT IN ('OFFICE','SHOP')
     OR U.UNIT_OF_MEASURE NOT IN ('EA','PC','CASE'))
  AND P.PURCHASED='Y'
  AND P.STOCK_UM=U.UNIT_OF_MEASURE
  AND P.PREF_VENDOR_ID=V.VENDOR_ID
  AND P.PRIMARY_WHS_ID=L.WAREHOUSE_ID
  AND P.PRIMARY_LOC_ID=L.LOCATION_ID
  AND P.PART_ID=LV.PART_ID
  AND LV.VENDOR_ID=V2.VENDOR_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.60       0.60          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4     25.75      26.00     252854     272253         63        2550
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6     26.36      26.61     252854     272253         63        2550

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      2550       2550       2550  HASH JOIN  (cr=272253 pr=252854 pw=3805 time=917103 us cost=190663 size=3936278028 card=10754858)
      2200       2200       2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=366 us cost=4 size=176000 card=2200)
      2551       2551       2551   HASH JOIN  (cr=272222 pr=252854 pw=3805 time=703263 us cost=190608 size=3075889388 card=10754858)
     49786      49786      49786    TABLE ACCESS FULL VENDORS (cr=2497 pr=0 pw=0 time=14973 us cost=197 size=6472180 card=49786)
      2551       2551       2551    HASH JOIN  (cr=269725 pr=252854 pw=3805 time=700981 us cost=168838 size=1677757848 card=10754858)
     49786      49786      49786     TABLE ACCESS FULL VENDORS (cr=2497 pr=0 pw=0 time=13950 us cost=197 size=1593152 card=49786)
      2551       2551       2551     HASH JOIN  (cr=267228 pr=252854 pw=3805 time=700310 us cost=151200 size=1333602392 card=10754858)
         8          8          8      TABLE ACCESS FULL UMS (cr=3 pr=0 pw=0 time=0 us cost=2 size=56 card=8)
      2829       2829       2829      MERGE JOIN  (cr=267225 pr=252854 pw=3805 time=817292 us cost=151140 size=1428005592 card=12205176)
      4246       4246       4246       VIEW  (cr=262229 pr=252854 pw=3805 time=800432 us cost=150135 size=585856656 card=12205347)
    519965     519965     519965        WINDOW SORT PUSHED RANK (cr=262229 pr=252854 pw=3805 time=822060 us cost=150135 size=598062003 card=12205347)
  12205347   12205347   12205347         HASH JOIN  (cr=262229 pr=249049 pw=0 time=6367055 us cost=27081 size=598062003 card=12205347)
    500000     500000     500000          TABLE ACCESS FULL PO_HEADER (cr=13173 pr=0 pw=0 time=63219 us cost=1038 size=10000000 card=500000)
  12205347   12205347   12205347          TABLE ACCESS FULL PO_LINE (cr=249056 pr=249049 pw=0 time=1935473 us cost=19811 size=353955063 card=12205347)
      2829       2829       2829       SORT JOIN (cr=4996 pr=0 pw=0 time=0 us cost=1005 size=3089061 card=44769)
     66462      66462      66462        TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=22494 us cost=398 size=3089061 card=44769)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  direct path read                                8        0.04          0.05
  asynch descriptor resize                        8        0.00          0.00
  direct path write temp                          1        0.00          0.00
  direct path read temp                         128        0.00          0.08
  SQL*Net message from client                     4        0.00          0.00
  SQL*Net more data to client                    56        0.00          0.00 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.99       1.20          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4     28.00      28.65       3627     272534         60        2550
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6     29.00      29.86       3627     272534         60        2550

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    2550  HASH JOIN  (cr=272534 pr=3627 pw=3622 time=28590189 us)
    2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=35 us)
    2551   HASH JOIN  (cr=272503 pr=3627 pw=3622 time=28551033 us)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=15 us)
    2551    HASH JOIN  (cr=269985 pr=3627 pw=3622 time=28522524 us)
   49786     TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=23 us)
    2551     HASH JOIN  (cr=267467 pr=3627 pw=3622 time=28498973 us)
       8      TABLE ACCESS FULL UMS (cr=7 pr=5 pw=0 time=20191 us)
    2829      MERGE JOIN  (cr=267460 pr=3622 pw=3622 time=28472228 us)
    4246       VIEW  (cr=262420 pr=3622 pw=3622 time=28456925 us)
    8895        WINDOW SORT PUSHED RANK (cr=262420 pr=3622 pw=3622 time=28377263 us)
12205347         HASH JOIN  (cr=262420 pr=0 pw=0 time=173557 us)
  500000          TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=17 us)
12205347          TABLE ACCESS FULL PO_LINE (cr=249230 pr=0 pw=0 time=20 us)
    2829       SORT JOIN (cr=5040 pr=0 pw=0 time=62158 us)
   66462        TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=49 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  db file sequential read                         1        0.01          0.01
  db file scattered read                          2        0.00          0.00
  direct path write temp                          1        0.00          0.00
  direct path read temp                         100        0.03          0.61
  SQL*Net message from client                     4        0.00          0.01
  SQL*Net more data to client                   229        0.00          0.00

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.99       1.80          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4     28.93      30.98     116717     272484         59        2550
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6     29.93      32.78     116717     272484         59        2550

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    2550  HASH JOIN  (cr=272484 pr=116717 pw=3563 time=30946899 us)
    2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=31 us)
    2551   HASH JOIN  (cr=272453 pr=116717 pw=3563 time=31958392 us)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=13 us)
    2551    HASH JOIN  (cr=269935 pr=116717 pw=3563 time=31926556 us)
   49786     TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=21 us)
    2551     HASH JOIN  (cr=267417 pr=116717 pw=3563 time=31902630 us)
       8      TABLE ACCESS FULL UMS (cr=7 pr=5 pw=0 time=39672 us)
    2829      MERGE JOIN  (cr=267410 pr=116712 pw=3563 time=30871775 us)
    4246       VIEW  (cr=262370 pr=116712 pw=3563 time=30771072 us)
    8928        WINDOW SORT PUSHED RANK (cr=262370 pr=116712 pw=3563 time=30671567 us)
12205347         HASH JOIN  (cr=262370 pr=113149 pw=0 time=173557 us)
  500000          TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=18 us)
12205347          TABLE ACCESS FULL PO_LINE (cr=249180 pr=113149 pw=0 time=21 us)
    2829       SORT JOIN (cr=5040 pr=0 pw=0 time=60695 us)
   66462        TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=47 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  db file sequential read                       265        0.02          0.27
  db file scattered read                       1076        0.05          1.33
  i/o slave wait                                162        0.03          0.45
  direct path write temp                          1        0.00          0.00
  direct path read temp                         118        0.03          0.38
  SQL*Net message from client                     4        0.00          0.01
  SQL*Net more data to client                   229        0.00          0.00

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.63       0.64          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4     26.87      30.38     253292     272827         62        2550
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6     27.51      31.03     253292     272827         62        2550

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    2550  HASH JOIN  (cr=272827 pr=253292 pw=3742 time=707146 us cost=191123 size=3936296694 card=10754909)
    2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=0 us cost=4 size=176000 card=2200)
    2551   HASH JOIN  (cr=272796 pr=253292 pw=3742 time=617905 us cost=191068 size=3075903974 card=10754909)
   49786    TABLE ACCESS FULL VENDORS (cr=2505 pr=0 pw=0 time=17533 us cost=199 size=6472180 card=49786)
    2551    HASH JOIN  (cr=270291 pr=253292 pw=3742 time=616563 us cost=169295 size=1677765804 card=10754909)
   49786     TABLE ACCESS FULL VENDORS (cr=2505 pr=0 pw=0 time=15741 us cost=199 size=1593152 card=49786)
    2551     HASH JOIN  (cr=267786 pr=253292 pw=3742 time=614550 us cost=151655 size=1333608716 card=10754909)
       8      TABLE ACCESS FULL UMS (cr=7 pr=5 pw=0 time=0 us cost=2 size=56 card=8)
    2829      MERGE JOIN  (cr=267779 pr=253287 pw=3742 time=1493184 us cost=151595 size=1428012378 card=12205234)
    4246       VIEW  (cr=262752 pr=253287 pw=3742 time=660596 us cost=150591 size=585856656 card=12205347)
  511557        WINDOW SORT PUSHED RANK (cr=262752 pr=253287 pw=3742 time=1379383 us cost=150591 size=598062003 card=12205347)
12205347         HASH JOIN  (cr=262752 pr=249545 pw=0 time=8177985 us cost=27537 size=598062003 card=12205347)
  500000          TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=65267 us cost=1118 size=10000000 card=500000)
12205347          TABLE ACCESS FULL PO_LINE (cr=249562 pr=249545 pw=0 time=3676259 us cost=20188 size=353955063 card=12205347)
    2829       SORT JOIN (cr=5027 pr=0 pw=0 time=0 us cost=1003 size=3056010 card=44290)
   66462        TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=27734 us cost=402 size=3056010 card=44290)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  db file sequential read                         2        0.01          0.01
  db file scattered read                          1        0.00          0.00
  direct path read                              202        0.07          2.80
  asynch descriptor resize                        8        0.00          0.00
  direct path write temp                          1        0.00          0.00
  direct path read temp                         101        0.04          0.51
  SQL*Net message from client                     4        0.00          0.00
  SQL*Net more data to client                    56        0.00          0.00

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.67       0.66          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4     26.75      30.20     252903     272476         62        2550
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6     27.42      30.86     252903     272476         62        2550

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
    2550  HASH JOIN  (cr=272476 pr=252903 pw=3742 time=712244 us cost=191070 size=3936284616 card=10754876)
    2200   TABLE ACCESS FULL LOCATIONS (cr=31 pr=0 pw=0 time=244 us cost=4 size=176000 card=2200)
    2551   HASH JOIN  (cr=272445 pr=252903 pw=3742 time=614550 us cost=191015 size=3075894536 card=10754876)
   49786    TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=17661 us cost=199 size=6472180 card=49786)
    2551    HASH JOIN  (cr=269927 pr=252903 pw=3742 time=613476 us cost=169243 size=1677760656 card=10754876)
   49786     TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=14973 us cost=199 size=1593152 card=49786)
    2551     HASH JOIN  (cr=267409 pr=252903 pw=3742 time=611597 us cost=151602 size=1333604624 card=10754876)
       8      TABLE ACCESS FULL UMS (cr=7 pr=5 pw=0 time=0 us cost=2 size=56 card=8)
    2829      MERGE JOIN  (cr=267402 pr=252898 pw=3742 time=705457 us cost=151543 size=1428008049 card=12205197)
    4246       VIEW  (cr=262362 pr=252898 pw=3742 time=659098 us cost=150541 size=585856656 card=12205347)
  511304        WINDOW SORT PUSHED RANK (cr=262362 pr=252898 pw=3742 time=711906 us cost=150541 size=598062003 card=12205347)
12205347         HASH JOIN  (cr=262362 pr=249156 pw=0 time=9688757 us cost=27487 size=598062003 card=12205347)
  500000          TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=61939 us cost=1118 size=10000000 card=500000)
12205347          TABLE ACCESS FULL PO_LINE (cr=249172 pr=249156 pw=0 time=5162968 us cost=20138 size=353955063 card=12205347)
   2829       SORT JOIN (cr=5040 pr=0 pw=0 time=0 us cost=1001 size=3046557 card=44153)
  66462        TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=26200 us cost=402 size=3046557 card=44153)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                          2        0.00          0.00
  direct path read                              187        0.07          2.90
  asynch descriptor resize                        8        0.00          0.00
  direct path write temp                          1        0.00          0.00
  direct path read temp                         127        0.03          0.44
  SQL*Net message from client                     4        0.00          0.00
  SQL*Net more data to client                    56        0.00          0.00   

For all parts, determine the number of purchase order line exceptions where a part was purchase from a vendor other than the preferred vendor:

SELECT
  P.PART_ID,
  P.DESCRIPTION,
  P.PREF_VENDOR_ID,
  V.VENDOR_NAME PREF_VENDOR_NAME,
  PV.VENDOR_ID,
  V2.VENDOR_NAME LAST_VENDOR_NAME,
  PV.ORDER_LINES,
  PV.FIRST_RECV_DATE,
  PV.LAST_RECV_DATE
FROM
  (SELECT
    PO.VENDOR_ID,
    POL.PART_ID,
    COUNT(*) ORDER_LINES,
    MIN(POL.DESIRED_RECV_DATE) FIRST_RECV_DATE,
    MAX(POL.DESIRED_RECV_DATE) LAST_RECV_DATE
  FROM
    PO_HEADER PO,
    PO_LINE POL
  WHERE
    PO.PURC_ORDER_ID=POL.PURC_ORDER_ID
  GROUP BY
    PO.VENDOR_ID,
    POL.PART_ID) PV,
  PARTS P,
  VENDORS V,
  VENDORS V2
WHERE
  P.PART_ID=PV.PART_ID
  AND P.PREF_VENDOR_ID != PV.VENDOR_ID
  AND P.PREF_VENDOR_ID=V.VENDOR_ID
  AND PV.VENDOR_ID=V2.VENDOR_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.08          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      151      9.98      10.16     249049     272219          0      149403
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      153     10.07      10.24     249049     272219          0      149403

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    149403     149403     149403  HASH JOIN  (cr=272219 pr=249049 pw=0 time=244266 us cost=161133 size=1879414152 card=12203988)
     49786      49786      49786   TABLE ACCESS FULL VENDORS (cr=2497 pr=0 pw=0 time=13182 us cost=197 size=1443794 card=49786)
    149403     149403     149403   HASH JOIN  (cr=269722 pr=249049 pw=0 time=166941 us cost=160877 size=1525498500 card=12203988)
     66462      66462      66462    JOIN FILTER CREATE :BF0000 (cr=7493 pr=0 pw=0 time=62115 us cost=594 size=4519416 card=66462)
     66462      66462      66462     HASH JOIN  (cr=7493 pr=0 pw=0 time=51123 us cost=594 size=4519416 card=66462)
     49786      49786      49786      TABLE ACCESS FULL VENDORS (cr=2497 pr=0 pw=0 time=12286 us cost=197 size=1443794 card=49786)
     66462      66462      66462      TABLE ACCESS FULL PARTS (cr=4996 pr=0 pw=0 time=31824 us cost=396 size=2592018 card=66462)
    150920     150920     150920    VIEW  (cr=262229 pr=249049 pw=0 time=99204 us cost=150135 size=695704779 card=12205347)
    150920     150920     150920     HASH GROUP BY (cr=262229 pr=249049 pw=0 time=65027 us cost=150135 size=598062003 card=12205347)
   8219280    8219280    8219280      JOIN FILTER USE :BF0000 (cr=262229 pr=249049 pw=0 time=5918254 us cost=27081 size=598062003 card=12205347)
  12205347   12205347   12205347       HASH JOIN  (cr=262229 pr=249049 pw=0 time=5812435 us cost=27081 size=598062003 card=12205347)
    500000     500000     500000        TABLE ACCESS FULL PO_HEADER (cr=13173 pr=0 pw=0 time=58612 us cost=1038 size=10000000 card=500000)
  12205347   12205347   12205347        TABLE ACCESS FULL PO_LINE (cr=249056 pr=249049 pw=0 time=1746674 us cost=19811 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     151        0.00          0.00
  asynch descriptor resize                        6        0.00          0.00
  direct path read                                9        0.00          0.01
  SQL*Net message from client                   151        0.00          0.23
  SQL*Net more data to client                  1643        0.00          0.07 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.10       0.14          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      151     12.05      12.14          0     272496          0      149395
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      153     12.16      12.29          0     272496          0      149395

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
  149395  HASH JOIN  (cr=272496 pr=0 pw=0 time=11778583 us)
   49786   TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=36 us)
  149395   HASH JOIN  (cr=269978 pr=0 pw=0 time=11761016 us)
   66462    HASH JOIN  (cr=7558 pr=0 pw=0 time=17370 us)
   49786     TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=20 us)
   66462     TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=18 us)
  224290    VIEW  (cr=262420 pr=0 pw=0 time=11532768 us)
  224290     HASH GROUP BY (cr=262420 pr=0 pw=0 time=11532765 us)
12205347      HASH JOIN  (cr=262420 pr=0 pw=0 time=178331 us)
  500000       TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=23 us)
12205347       TABLE ACCESS FULL PO_LINE (cr=249230 pr=0 pw=0 time=20 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     151        0.00          0.00
  SQL*Net message from client                   151        0.00          0.24
  SQL*Net more data to client                  7448        0.00          0.10

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.21          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      151     12.65      14.34     113149     272446          0      149407
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      153     12.71      14.56     113149     272446          0      149407

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
  149407  HASH JOIN  (cr=272446 pr=113149 pw=0 time=13979365 us)
   49786   TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=25 us)
  149407   HASH JOIN  (cr=269928 pr=113149 pw=0 time=13961727 us)
   66462    HASH JOIN  (cr=7558 pr=0 pw=0 time=17388 us)
   49786     TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=20 us)
   66462     TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=19 us)
  224290    VIEW  (cr=262370 pr=113149 pw=0 time=13733144 us)
  224290     HASH GROUP BY (cr=262370 pr=113149 pw=0 time=13733143 us)
12205347      HASH JOIN  (cr=262370 pr=113149 pw=0 time=172881 us)
  500000       TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=26 us)
12205347       TABLE ACCESS FULL PO_LINE (cr=249180 pr=113149 pw=0 time=21 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     151        0.00          0.00
  db file sequential read                       245        0.02          0.25
  db file scattered read                       1215        0.04          1.33
  SQL*Net message from client                   151        0.00          0.24
  SQL*Net more data to client                  7458        0.00          0.10 

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.09          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      151      9.96      18.19     249545     272789          0      149400
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      153     10.06      18.28     249545     272789          0      149400

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
  149400  HASH JOIN  (cr=272789 pr=249545 pw=0 time=200990 us cost=161597 size=1879414152 card=12203988)
   49786   TABLE ACCESS FULL VENDORS (cr=2505 pr=0 pw=0 time=15101 us cost=199 size=1443794 card=49786)
  149400   HASH JOIN  (cr=270284 pr=249545 pw=0 time=130068 us cost=161339 size=1525498500 card=12203988)
   66462    JOIN FILTER CREATE :BF0000 (cr=7532 pr=0 pw=0 time=61092 us cost=600 size=4519416 card=66462)
   66462     HASH JOIN  (cr=7532 pr=0 pw=0 time=50101 us cost=600 size=4519416 card=66462)
   49786      TABLE ACCESS FULL VENDORS (cr=2505 pr=0 pw=0 time=13182 us cost=199 size=1443794 card=49786)
   66462      TABLE ACCESS FULL PARTS (cr=5027 pr=0 pw=0 time=25817 us cost=400 size=2592018 card=66462)
  150920    VIEW  (cr=262752 pr=249545 pw=0 time=80131 us cost=150591 size=695704779 card=12205347)
  150920     HASH GROUP BY (cr=262752 pr=249545 pw=0 time=53506 us cost=150591 size=598062003 card=12205347)
 8219280      JOIN FILTER USE :BF0000 (cr=262752 pr=249545 pw=0 time=20817762 us cost=27537 size=598062003 card=12205347)
12205347       HASH JOIN  (cr=262752 pr=249545 pw=0 time=16388226 us cost=27537 size=598062003 card=12205347)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=65651 us cost=1118 size=10000000 card=500000)
12205347        TABLE ACCESS FULL PO_LINE (cr=249562 pr=249545 pw=0 time=12278561 us cost=20188 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     151        0.00          0.00
  asynch descriptor resize                        6        0.00          0.00
  direct path read                             1943        0.04          7.84
  SQL*Net message from client                   151        0.00          0.26
  SQL*Net more data to client                  1792        0.00          0.03 

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.09          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      151     10.34      15.04     249156     272438          0      149408
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      153     10.43      15.13     249156     272438          0      149408

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

 Rows     Row Source Operation
 -------  ---------------------------------------------------
  149408  HASH JOIN  (cr=272438 pr=249156 pw=0 time=202794 us cost=161546 size=1879414152 card=12203988)
   49786   TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=12798 us cost=199 size=1443794 card=49786)
  149408   HASH JOIN  (cr=269920 pr=249156 pw=0 time=133531 us cost=161289 size=1525498500 card=12203988)
   66462    JOIN FILTER CREATE :BF0000 (cr=7558 pr=0 pw=0 time=60198 us cost=600 size=4519416 card=66462)
   66462     HASH JOIN  (cr=7558 pr=0 pw=0 time=48567 us cost=600 size=4519416 card=66462)
   49786      TABLE ACCESS FULL VENDORS (cr=2518 pr=0 pw=0 time=12286 us cost=199 size=1443794 card=49786)
   66462      TABLE ACCESS FULL PARTS (cr=5040 pr=0 pw=0 time=23900 us cost=400 size=2592018 card=66462)
  150920    VIEW  (cr=262362 pr=249156 pw=0 time=82819 us cost=150541 size=695704779 card=12205347)
  150920     HASH GROUP BY (cr=262362 pr=249156 pw=0 time=51970 us cost=150541 size=598062003 card=12205347)
 8219280      JOIN FILTER USE :BF0000 (cr=262362 pr=249156 pw=0 time=6369576 us cost=27487 size=598062003 card=12205347)
12205347       HASH JOIN  (cr=262362 pr=249156 pw=0 time=6154577 us cost=27487 size=598062003 card=12205347)
  500000        TABLE ACCESS FULL PO_HEADER (cr=13190 pr=0 pw=0 time=65779 us cost=1118 size=10000000 card=500000)
12205347        TABLE ACCESS FULL PO_LINE (cr=249172 pr=249156 pw=0 time=2004720 us cost=20138 size=353955063 card=12205347)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     151        0.00          0.00
  asynch descriptor resize                        6        0.00          0.00
  direct path read                              578        0.06          4.78
  SQL*Net message from client                   151        0.00          0.26
  SQL*Net more data to client                  1793        0.00          0.03   

A give me every column type query, were the user doesn’t know exactly what vendor they are looking for, or exactly what part, but they know that the purchase order line has been posted to the GL:

SELECT
  *
FROM
  PO_HEADER PO,
  PO_LINE POL,
  PARTS P,
  VENDORS V
WHERE
  PO.VENDOR_ID LIKE '100%'
  AND PO.POSTING_CANDIDATE='N'
  AND PO.PURC_ORDER_ID=POL.PURC_ORDER_ID
  AND POL.PART_ID BETWEEN '80' AND '999999'
  AND POL.PART_ID=P.PART_ID
  AND PO.VENDOR_ID=V.VENDOR_ID 

Laptop running 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6      0.12       0.64       1410      11322          0        4629
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.18       0.69       1410      11322          0        4629

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 286 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      4629       4629       4629  NESTED LOOPS  (cr=11322 pr=1410 pw=0 time=707312 us)
      4629       4629       4629   NESTED LOOPS  (cr=6693 pr=1410 pw=0 time=724219 us cost=12 size=2964 card=3)
      4629       4629       4629    NESTED LOOPS  (cr=2058 pr=1343 pw=0 time=606393 us cost=9 size=1875 card=3)
       387        387        387     NESTED LOOPS  (cr=571 pr=6 pw=0 time=2991 us cost=6 size=505 card=1)
        33         33         33      TABLE ACCESS BY INDEX ROWID VENDORS (cr=40 pr=2 pw=0 time=224 us cost=3 size=342 card=1)
        33         33         33       INDEX RANGE SCAN SYS_C0024972 (cr=7 pr=2 pw=0 time=128 us cost=2 size=0 card=1)(object id 82050)
       387        387        387      TABLE ACCESS BY INDEX ROWID PO_HEADER (cr=531 pr=4 pw=0 time=3040 us cost=3 size=163 card=1)
       497        497        497       INDEX RANGE SCAN IND_PO_HEADER_1 (cr=34 pr=4 pw=0 time=1057 us cost=2 size=0 card=1)(object id 82064)
      4629       4629       4629     TABLE ACCESS BY INDEX ROWID PO_LINE (cr=1487 pr=1337 pw=0 time=117812 us cost=3 size=360 card=3)
     10634      10634      10634      INDEX RANGE SCAN SYS_C0025101 (cr=901 pr=756 pw=0 time=24583 us cost=2 size=0 card=25)(object id 82070)
      4629       4629       4629    INDEX UNIQUE SCAN SYS_C0025012 (cr=4635 pr=67 pw=0 time=0 us cost=0 size=0 card=1)(object id 82053)
      4629       4629       4629   TABLE ACCESS BY INDEX ROWID PARTS (cr=4629 pr=0 pw=0 time=0 us cost=1 size=363 card=1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net more data to client                   125        0.00          0.00
  db file sequential read                      1410        0.00          0.46
  SQL*Net message from client                     6        0.03          0.17 

Oracle Database 10.2.0.5 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6      0.20       9.66        966      12017          0        4629
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.23       9.71        966      12017          0        4629

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 

Rows     Row Source Operation
-------  ---------------------------------------------------
   4629  NESTED LOOPS  (cr=12017 pr=966 pw=0 time=10450963 us)
   4629   NESTED LOOPS  (cr=2753 pr=822 pw=0 time=9710630 us)
    387    NESTED LOOPS  (cr=620 pr=12 pw=0 time=71052 us)
     33     TABLE ACCESS BY INDEX ROWID VENDORS (cr=40 pr=5 pw=0 time=25381 us)
     33      INDEX RANGE SCAN SYS_C004577 (cr=7 pr=5 pw=0 time=25183 us)(object id 47893)
    387     TABLE ACCESS BY INDEX ROWID PO_HEADER (cr=580 pr=7 pw=0 time=68799 us)
    497      INDEX RANGE SCAN IND_PO_HEADER_2 (cr=83 pr=7 pw=0 time=42360 us)(object id 47908)
   4629    TABLE ACCESS BY INDEX ROWID PO_LINE (cr=2133 pr=810 pw=0 time=7763613 us)
  10634     INDEX RANGE SCAN SYS_C004706 (cr=1363 pr=810 pw=0 time=10702791 us)(object id 47913)
   4629   TABLE ACCESS BY INDEX ROWID PARTS (cr=9264 pr=144 pw=0 time=737759 us)
   4629    INDEX UNIQUE SCAN SYS_C004617 (cr=4635 pr=144 pw=0 time=705778 us)(object id 47896)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net more data to client                   519        0.00          0.01
  db file sequential read                       730        0.03          8.94
  db file scattered read                         46        0.02          0.52
  SQL*Net message from client                     6        0.04          0.19

Oracle Database 10.2.0.5 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6      0.28       7.26       1191      11989          0        4629
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.34       7.32       1191      11989          0        4629

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164 

Rows     Row Source Operation
-------  ---------------------------------------------------
   4629  NESTED LOOPS  (cr=11989 pr=1191 pw=0 time=5359344 us)
   4629   NESTED LOOPS  (cr=2725 pr=1058 pw=0 time=4838370 us)
    387    NESTED LOOPS  (cr=620 pr=12 pw=0 time=53788 us)
     33     TABLE ACCESS BY INDEX ROWID VENDORS (cr=40 pr=5 pw=0 time=18936 us)
     33      INDEX RANGE SCAN SYS_C0012084 (cr=7 pr=5 pw=0 time=18834 us)(object id 51698)
    387     TABLE ACCESS BY INDEX ROWID PO_HEADER (cr=580 pr=7 pw=0 time=48231 us)
    497      INDEX RANGE SCAN IND_PO_HEADER_1 (cr=83 pr=7 pw=0 time=31669 us)(object id 51712)
   4629    TABLE ACCESS BY INDEX ROWID PO_LINE (cr=2105 pr=1046 pw=0 time=5891086 us)
  10634     INDEX RANGE SCAN SYS_C0012213 (cr=1333 pr=760 pw=0 time=5874269 us)(object id 51718)
   4629   TABLE ACCESS BY INDEX ROWID PARTS (cr=9264 pr=133 pw=0 time=406748 us)
   4629    INDEX UNIQUE SCAN SYS_C0012124 (cr=4635 pr=133 pw=0 time=373924 us)(object id 51701)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net more data to client                   517        0.00          0.01
  db file sequential read                       895        0.04          6.78
  db file scattered read                         56        0.01          0.27
  SQL*Net message from client                     6        0.04          0.19

Oracle Database 11.2.0.1 Standard Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6      0.17      10.28       2000      11985          0        4629
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.23      10.34       2000      11985          0        4629

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
   4629  NESTED LOOPS  (cr=11985 pr=2000 pw=0 time=16662600 us)
   4629   NESTED LOOPS  (cr=7356 pr=2000 pw=0 time=17419042 us cost=13 size=2964 card=3)
   4629    NESTED LOOPS  (cr=2721 pr=1845 pw=0 time=15284658 us cost=10 size=1875 card=3)
    387     NESTED LOOPS  (cr=587 pr=12 pw=0 time=3088 us cost=6 size=505 card=1)
     33      TABLE ACCESS BY INDEX ROWID VENDORS (cr=40 pr=5 pw=0 time=608 us cost=3 size=342 card=1)
     33       INDEX RANGE SCAN SYS_C009210 (cr=7 pr=5 pw=0 time=512 us cost=2 size=0 card=1)(object id 20305)
    387      TABLE ACCESS BY INDEX ROWID PO_HEADER (cr=547 pr=7 pw=0 time=3040 us cost=3 size=163 card=1)
    497       INDEX RANGE SCAN IND_PO_HEADER_1 (cr=50 pr=7 pw=0 time=976 us cost=2 size=0 card=1)(object id 20319)
   4629     TABLE ACCESS BY INDEX ROWID PO_LINE (cr=2134 pr=1833 pw=0 time=550076 us cost=4 size=360 card=3)
  10634      INDEX RANGE SCAN SYS_C009339 (cr=1353 pr=750 pw=0 time=11113 us cost=2 size=0 card=25)(object id 20325)
   4629    INDEX UNIQUE SCAN SYS_C009250 (cr=4635 pr=155 pw=0 time=0 us cost=0 size=0 card=1)(object id 20308)
   4629   TABLE ACCESS BY INDEX ROWID PARTS (cr=4629 pr=0 pw=0 time=0 us cost=1 size=363 card=1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net more data to client                   125        0.00          0.00
  db file sequential read                      1179        0.04          8.25
  db file scattered read                        121        0.03          0.55
  SQL*Net message from client                     6        0.04          0.19
  db file parallel read                         120        0.04          1.25

Oracle Database 11.2.0.1 Enterprise Edition:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        6      0.35      14.98       1999      11963          0        4629
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.39      15.03       1999      11963          0        4629

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
   4629  NESTED LOOPS  (cr=11963 pr=1999 pw=0 time=19636346 us)
   4629   NESTED LOOPS  (cr=7334 pr=1999 pw=0 time=20963088 us cost=13 size=2964 card=3)
   4629    NESTED LOOPS  (cr=2699 pr=1869 pw=0 time=18515502 us cost=10 size=1875 card=3)
    387     NESTED LOOPS  (cr=589 pr=13 pw=0 time=3474 us cost=6 size=505 card=1)
     33      TABLE ACCESS BY INDEX ROWID VENDORS (cr=40 pr=5 pw=0 time=1248 us cost=3 size=342 card=1)
     33       INDEX RANGE SCAN SYS_C0024659 (cr=7 pr=5 pw=0 time=1056 us cost=2 size=0 card=1)(object id 83200)
    387      TABLE ACCESS BY INDEX ROWID PO_HEADER (cr=549 pr=8 pw=0 time=3761 us cost=3 size=163 card=1)
    497       INDEX RANGE SCAN IND_PO_HEADER_1 (cr=52 pr=8 pw=0 time=1342 us cost=2 size=0 card=1)(object id 83214)
   4629     TABLE ACCESS BY INDEX ROWID PO_LINE (cr=2110 pr=1856 pw=0 time=321223 us cost=4 size=360 card=3)
  10634      INDEX RANGE SCAN SYS_C0024788 (cr=1342 pr=781 pw=0 time=3534 us cost=2 size=0 card=25)(object id 83220)
   4629    INDEX UNIQUE SCAN SYS_C0024699 (cr=4635 pr=130 pw=0 time=0 us cost=0 size=0 card=1)(object id 83203)
   4629   TABLE ACCESS BY INDEX ROWID PARTS (cr=4629 pr=0 pw=0 time=0 us cost=1 size=363 card=1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net more data to client                   125        0.00          0.00
  db file sequential read                      1177        0.03         12.48
  db file scattered read                        129        0.02          0.69
  SQL*Net message from client                     6        0.04          0.19
  db file parallel read                         110        0.02          1.59   

—————–

While the above output only shows a small number of execution plan changes, there are in fact a couple of notable changes.  In some cases, the number of physical block reads for one release version and/or edition was double, possibly even 1000 times as large as one of the other release versions – the differences in the types of wait events and the use or avoidance of the buffer cache for previously executed SQL statements in the database, as well as changes in the execution plans help explain many of the odd differences in the statistics.  One of the questions that remains to be answered is how would an execution that required an additional 3 seconds to execute in the above output, due to a significant increase in the number of physical block reads, behave once it is released into production where it might be simultaneously executed by a dozen or more users.  What about an execution that required significantly more CPU time – could that cause a similar problem once concurrency exists in production?

We still have not experimented with bind variables, an insufficient number of hints in complex SQL statements, rapid fire execution of SQL statements – each of which return only a small number of rows, and probably a couple other problem areas.


Actions

Information

One response

25 11 2010
Charles Hooper

For those who were blurry eyed before reaching the bottom of the page, take a very close look at the TKPROF output for the second to the last SQL statement (identified as “For all parts, determine the number of purchase order line exceptions where a part was purchase from a vendor other than the preferred vendor”). The number of rows returned by each database is different! That is possibly the most scary part of this blog article.

Also, take a look at the execution plan for the query identified as “Comparing the most recent vendor for each part with the expected vendor for each purchased part that has either an OFFICE or SHOP product code” When comparing the number of rows returned by the WINDOW SORT PUSHED RANK operation, notice that a significantly greater number of rows are returned by 11.2.0.1 – might this explain the significant increase in the number of physical block reads?

There are some other interesting results in the output, but I will let someone else identify those.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: