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.
Recent Comments