Script to check for Various example of Performance Tuning

View All Public Scripts Login to Run Script

Script Name Script to check for Various example of Performance Tuning
Visibility Unlisted - anyone with the share link can access
Description Script contains 3 different PLSQL codes all of which try to perform the same validation logic in different manners. Need some guidance how to achieve maximum performance
Contributor diagnostic_messages_trace_lines
Created Wednesday July 25, 2018

Statement 1

DROP TABLE MTL_MANUFACTURERS;


CREATE TABLE MTL_MANUFACTURERS ( MANUFACTURER_ID NUMBER NOT NULL, MANUFACTURER_NAME VARCHAR2(30 BYTE) NOT NULL, LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER NOT NULL, LAST_UPDATE_LOGIN NUMBER, DESCRIPTION VARCHAR2(240 BYTE), ATTRIBUTE_CATEGORY VARCHAR2(30 BYTE), ATTRIBUTE1 VARCHAR2(150 BYTE), ATTRIBUTE2 VARCHAR2(150 BYTE), ATTRIBUTE3 VARCHAR2(150 BYTE), ATTRIBUTE4 VARCHAR2(150 BYTE), ATTRIBUTE5 VARCHAR2(150 BYTE), ATTRIBUTE6 VARCHAR2(150 BYTE), ATTRIBUTE7 VARCHAR2(150 BYTE), ATTRIBUTE8 VARCHAR2(150 BYTE), ATTRIBUTE9 VARCHAR2(150 BYTE), ATTRIBUTE10 VARCHAR2(150 BYTE), ATTRIBUTE11 VARCHAR2(150 BYTE), ATTRIBUTE12 VARCHAR2(150 BYTE), ATTRIBUTE13 VARCHAR2(150 BYTE), ATTRIBUTE14 VARCHAR2(150 BYTE), ATTRIBUTE15 VARCHAR2(150 BYTE), REQUEST_ID NUMBER, PROGRAM_APPLICATION_ID NUMBER, PROGRAM_ID NUMBER, PROGRAM_UPDATE_DATE DATE ) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;

Statement 2

BEGIN 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (69, '3M Healthcare', TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, 652437, '3M Healthcare'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (71, 'Eastman Kodak', TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, 652437, 'Kodak'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (72, 'Baxter', TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, 652437, 'Baxter'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (73, 'GE Medical', TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, 652437, 'GE Medical Systems'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (75, 'J&J', TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, 652437, 'Johnson & Johnson Mecical'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (78, 'Standard Textile', TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, TO_DATE('01/25/2001 10:16:53', 'MM/DD/YYYY HH24:MI:SS'), 1068, 652437, 'Standard Textile, Inc.'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (109, 'Allegiance', TO_DATE('02/01/2001 09:44:41', 'MM/DD/YYYY HH24:MI:SS'), 1318, TO_DATE('02/01/2001 09:44:41', 'MM/DD/YYYY HH24:MI:SS'), 1318, 677887, 'Allegiance'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (1128, 'Flexible Manufacturing', TO_DATE('09/14/2004 20:32:49', 'MM/DD/YYYY HH24:MI:SS'), 1006535, TO_DATE('09/14/2004 20:32:49', 'MM/DD/YYYY HH24:MI:SS'), 1006535, 2837760, 'Flexible Manufacturing'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (1129, 'BoardTec Corp', TO_DATE('09/14/2004 20:34:01', 'MM/DD/YYYY HH24:MI:SS'), 1006535, TO_DATE('09/14/2004 20:34:01', 'MM/DD/YYYY HH24:MI:SS'), 1006535, 2837760, 'BoardTec Corp'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (1131, 'Vision Operations', TO_DATE('11/10/2004 01:33:17', 'MM/DD/YYYY HH24:MI:SS'), 1009468, TO_DATE('11/10/2004 01:33:17', 'MM/DD/YYYY HH24:MI:SS'), 1009468, 2961571, 'Vision Operations'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (3132, 'Cisco Corporation', TO_DATE('08/22/2005 09:52:15', 'MM/DD/YYYY HH24:MI:SS'), 1068, TO_DATE('08/22/2005 09:52:15', 'MM/DD/YYYY HH24:MI:SS'), 1068, 3423599, 'Cisco Corporation'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (5132, 'CompressIt', TO_DATE('04/26/2007 11:26:11', 'MM/DD/YYYY HH24:MI:SS'), 1004510, TO_DATE('04/26/2007 11:26:11', 'MM/DD/YYYY HH24:MI:SS'), 1004510, 4751634, 'CompressIt'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (7132, 'Michelin', TO_DATE('01/29/2008 11:07:08', 'MM/DD/YYYY HH24:MI:SS'), 1004510, TO_DATE('01/29/2008 11:07:08', 'MM/DD/YYYY HH24:MI:SS'), 1004510, 5176852, 'Michelin'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (7133, 'Goodyear', TO_DATE('01/29/2008 11:07:08', 'MM/DD/YYYY HH24:MI:SS'), 1004510, TO_DATE('01/29/2008 11:07:08', 'MM/DD/YYYY HH24:MI:SS'), 1004510, 5176852, 'Goodyear'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (6132, 'Coolfan', TO_DATE('01/23/2008 10:26:06', 'MM/DD/YYYY HH24:MI:SS'), 1004510, TO_DATE('01/23/2008 10:26:06', 'MM/DD/YYYY HH24:MI:SS'), 1004510, 5152878, 'Coolfan'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8143, 'SF Static Data', TO_DATE('04/04/2018 06:30:32', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('04/04/2018 06:30:32', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5530685, 'SF Static Data'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8141, 'SK', TO_DATE('03/06/2018 04:56:40', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('03/06/2018 04:56:40', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5497440, 'SK'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8164, 'SFDK', TO_DATE('04/12/2018 07:22:53', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('04/12/2018 07:22:53', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5540311, 'SparesFinder DK'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8151, '2H PLASTICS', TO_DATE('04/09/2018 10:53:22', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('04/09/2018 10:53:22', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5536765, '2H PLASTICS'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8168, 'SD Testing', TO_DATE('04/25/2018 04:56:58', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('04/25/2018 04:56:58', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5555929, 'SparesFinder  Testing - 25-April'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8169, 'PR', TO_DATE('04/25/2018 05:03:35', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('04/25/2018 05:03:35', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5555929, 'SF Static Data'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8155, 'KPIT', TO_DATE('04/12/2018 04:46:10', 'MM/DD/YYYY HH24:MI:SS'), 1013420, TO_DATE('04/12/2018 04:46:10', 'MM/DD/YYYY HH24:MI:SS'), 1013420, 5540350, 'KPIT'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8146, 'DK', TO_DATE('04/05/2018 04:42:40', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('04/05/2018 04:42:40', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5530685, 'DK'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8174, 'BJ', TO_DATE('05/08/2018 06:47:52', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('05/08/2018 06:47:52', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5569769, 'BJ'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8175, 'PR1', TO_DATE('05/08/2018 06:48:44', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('05/08/2018 06:48:44', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5569769, 'PR1'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8132, 'UpDown Parts', TO_DATE('07/17/2008 15:22:23', 'MM/DD/YYYY HH24:MI:SS'), 1004510, TO_DATE('07/17/2008 10:49:28', 'MM/DD/YYYY HH24:MI:SS'), 1318, 5452715, 'UpDown Elevator Parts'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8135, 'Manufacturer 001', TO_DATE('07/17/2008 15:22:23', 'MM/DD/YYYY HH24:MI:SS'), 1004510, TO_DATE('07/17/2008 15:22:23', 'MM/DD/YYYY HH24:MI:SS'), 1004510, 5452715, 'Manufacturer 001'); 
INSERT INTO MTL_MANUFACTURERS 
   (MANUFACTURER_ID, MANUFACTURER_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION) 
 VALUES 
   (8177, 'SS', TO_DATE('05/10/2018 03:21:12', 'MM/DD/YYYY HH24:MI:SS'), 1013415, TO_DATE('05/10/2018 03:21:12', 'MM/DD/YYYY HH24:MI:SS'), 1013415, 5571732, 'SS the Great'); 
COMMIT; 
END;

Statement 3

DECLARE
P_MANUFACTURER_ID NUMBER:=NULL;
P_MANUFACTURER_NAME VARCHAR2(4000):='3M Healthcare';
P_RECORD_CNT NUMBER;
P_START_TIMESTAMP TIMESTAMP;
BEGIN
P_START_TIMESTAMP:=CURRENT_TIMESTAMP;
IF (P_MANUFACTURER_ID IS NOT NULL AND P_MANUFACTURER_NAME IS NULL)
THEN
SELECT COUNT (*) INTO P_RECORD_CNT
FROM
MTL_MANUFACTURERS WHERE MANUFACTURER_ID = P_MANUFACTURER_ID; 
ELSIF (P_MANUFACTURER_ID IS NULL AND P_MANUFACTURER_NAME IS NOT NULL)
THEN
SELECT COUNT (*) INTO P_RECORD_CNT
FROM
MTL_MANUFACTURERS WHERE MANUFACTURER_NAME = P_MANUFACTURER_NAME;
ELSIF P_MANUFACTURER_ID IS NOT NULL AND P_MANUFACTURER_NAME IS NOT NULL  THEN
SELECT COUNT (*) INTO P_RECORD_CNT
FROM MTL_MANUFACTURERS WHERE MANUFACTURER_NAME = P_MANUFACTURER_NAME AND MANUFACTURER_ID = P_MANUFACTURER_ID;
END IF;
DBMS_OUTPUT.PUT_LINE('PLSQL_CONDITIONAL_CLAUSE Time : '||(CURRENT_TIMESTAMP-P_START_TIMESTAMP));
END;

Statement 4

DECLARE
P_MANUFACTURER_ID NUMBER:=NULL;
P_MANUFACTURER_NAME VARCHAR2(4000):='3M Healthcare';
P_START_TIMESTAMP TIMESTAMP;
P_RECORD_CNT NUMBER;
CURSOR CUR IS 
SELECT COUNT (*)
FROM
MTL_MANUFACTURERS WHERE ( P_MANUFACTURER_ID IS NOT NULL AND P_MANUFACTURER_NAME IS NULL AND
MANUFACTURER_ID = P_MANUFACTURER_ID ) OR ( P_MANUFACTURER_ID IS NULL AND P_MANUFACTURER_NAME IS NOT NULL AND
MANUFACTURER_NAME = P_MANUFACTURER_NAME ) OR ( P_MANUFACTURER_ID IS NOT NULL AND P_MANUFACTURER_NAME IS NOT NULL AND
MANUFACTURER_NAME = P_MANUFACTURER_NAME AND MANUFACTURER_ID = P_MANUFACTURER_ID );
BEGIN
P_START_TIMESTAMP:=CURRENT_TIMESTAMP;
OPEN CUR;
FETCH CUR INTO P_RECORD_CNT;
DBMS_OUTPUT.PUT_LINE('CURSOR_SQL_CONDITIONAL_CLAUSE Time : '||(CURRENT_TIMESTAMP-P_START_TIMESTAMP));
P_START_TIMESTAMP:=CURRENT_TIMESTAMP;
END;

Statement 5

DECLARE
P_MANUFACTURER_ID NUMBER:=NULL;
P_MANUFACTURER_NAME VARCHAR2(4000):='3M Healthcare';
P_START_TIMESTAMP TIMESTAMP;
P_RECORD_CNT NUMBER;
BEGIN
P_START_TIMESTAMP:=CURRENT_TIMESTAMP;
SELECT COUNT (*) into P_RECORD_CNT  
FROM
MTL_MANUFACTURERS WHERE ( P_MANUFACTURER_ID IS NOT NULL AND P_MANUFACTURER_NAME IS NULL AND
MANUFACTURER_ID = P_MANUFACTURER_ID ) OR ( P_MANUFACTURER_ID IS NULL AND P_MANUFACTURER_NAME IS NOT NULL AND
MANUFACTURER_NAME = P_MANUFACTURER_NAME ) OR ( P_MANUFACTURER_ID IS NOT NULL AND P_MANUFACTURER_NAME IS NOT NULL AND
MANUFACTURER_NAME = P_MANUFACTURER_NAME AND MANUFACTURER_ID = P_MANUFACTURER_ID );
DBMS_OUTPUT.PUT_LINE('DIRECT_SQL_CONDITIONAL_CLAUSE Time : '||(CURRENT_TIMESTAMP-P_START_TIMESTAMP));
END;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database