In this post, some important points of PL/SQL performance metrics has been explained. Oracle 19c has been used in all examples.
Use Bind Variable
Using bind variables can enhance your query performance. It prevents unnecessary parsing and also makes your code more secure (prevent SQL injection). The first step of SQL process is checking its validity (syntax, semantic, permissions etc.) and determine the execution plan. Oracle sends a statement to the optimizer in order to do this step. It is called hard parse. However, If a plan already exists for a query, Oracle reuse the existing plan. This is referred to soft parse.
Let’s prepare a basic demo.
DROP TABLE T_TAB;
CREATE TABLE T_TAB
AS
SELECT LEVEL ID, LPAD('X', MOD(LEVEL, 10)+1, 'X') TXT FROM DUAL CONNECT BY LEVEL <= 10000;
CREATE INDEX IND_TEMP1 ON T_TAB(ID);
DECLARE
V_TXT VARCHAR2(10);
V_START NUMBER;
PROCEDURE SHOW_ELAPSED_TIME(P_NAME VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(P_NAME||' elapsed time: '||TO_CHAR((DBMS_UTILITY.GET_TIME - V_START)/100));
V_START := DBMS_UTILITY.GET_TIME;
END;
BEGIN
V_START := DBMS_UTILITY.GET_TIME;
FOR REC_ID IN 1..10000
LOOP
EXECUTE IMMEDIATE 'SELECT TXT FROM T_TAB WHERE ID = '||REC_ID INTO V_TXT;
END LOOP;
SHOW_ELAPSED_TIME('Without bind variable');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
FOR REC_ID IN 1..10000
LOOP
EXECUTE IMMEDIATE 'SELECT TXT FROM T_TAB WHERE ID = :B1' INTO V_TXT USING REC_ID;
END LOOP;
SHOW_ELAPSED_TIME('Using bind variable');
END;
Without bind variable elapsed time: 11,55
-----------------------------------------
Using bind variable elapsed time: ,13
As it can be seen from the above outcome, not using bind variable slows the total execution time more than 88 times for 10 thousand calls. The query, in the first loop, is reparsed for each ID (hard parse). If you look at V$SQLSTATS view, It is obvious to see that SQL_ID‘s are different. However, PLAN_HASH_VALUE is the same for all statements. In other words, 10 thousand different cursors have been created instead of creating once. Moreover, they resides in library cache. Thus, using bind variable prevents this repetition.
So, we are expected to see 10.001 records in V$SQLSTATS. 10.000 records belong to the first loop and 1 row belongs to the second loop. Because, the all executions in the second loop share the same cursor. They have the same SQL_ID.
SQL> SELECT
2 COUNT(*) TOTAL_CNT,
3 COUNT(DISTINCT SQL_ID) CNT_SQL_ID,
4 COUNT(DISTINCT PLAN_HASH_VALUE) CNT_PLAN_HASH_VALUE
5 FROM V$SQLSTATS WHERE SQL_TEXT LIKE 'SELECT TXT FROM T_TAB WHERE ID =%';
TOTAL_CNT CNT_SQL_ID CNT_PLAN_HASH_VALUE
---------- ---------- -------------------
10001 10001 1
SQL> SELECT
2 SQL_ID,
3 PARSE_CALLS,
4 FETCHES,
5 EXECUTIONS
6 FROM V$SQLSTATS WHERE SQL_TEXT LIKE 'SELECT TXT FROM T_TAB WHERE ID = :B1';
SQL_ID PARSE_CALLS FETCHES EXECUTIONS
-------------------- ----------- --------- ----------
5hwz3y5z9jcpt 1 10000 10000
If you look at the result that uses bind variable, is parsed only once and executed 10 thousand times. So, it is clear to see how soft parsing reduces the overhead.
FORALL vs FOR
If you have to use DML statements inside PL/SQL loop. Consider to use FORALL, instead of other conventional loops.
Always use SQL instead of PL/SQL, if possible. Especially, in data manipulations. Every time when you call any SQL statement in PL/SQL, context switching occurs. FORALL only decreases the number of context switching by sending SQL statements in bulk to the PL/SQL engine.
DECLARE
V_LIMIT NUMBER := 1000;
V_COMMIT_FREQ NUMBER := 10000;
TYPE REC IS RECORD
(
C1 NUMBER,
C2 VARCHAR2(10)
);
TYPE T_ARR IS TABLE OF REC;
ARR T_ARR := T_ARR();
V_START NUMBER := DBMS_UTILITY.GET_TIME;
PROCEDURE SHOW_ELAPSED_TIME(P_NAME VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(P_NAME||' elapsed time: '||TO_CHAR((DBMS_UTILITY.GET_TIME - V_START)/100));
V_START := DBMS_UTILITY.GET_TIME;
END;
BEGIN
FOR IND IN 1..5
LOOP
ARR.DELETE;
EXECUTE IMMEDIATE 'TRUNCATE TABLE T_TAB';
--Populate the collection densely
FOR I IN 1..V_LIMIT
LOOP
ARR.EXTEND;
ARR(ARR.LAST).C1 := I;
ARR(ARR.LAST).C2 := LPAD('X', MOD(I, 10)+1, 'X');
END LOOP;
--Start timing
V_START := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('-------------------------------');
DBMS_OUTPUT.PUT_LINE('V_LIMIT: '||V_LIMIT);
--For loop with commit
FOR I IN ARR.FIRST..ARR.LAST
LOOP
INSERT INTO T_TAB(ID, TXT) VALUES(ARR(I).C1, ARR(I).C2);
COMMIT;
END LOOP;
SHOW_ELAPSED_TIME('For Loop commit');
--For loop with reasonable commit
FOR I IN ARR.FIRST..ARR.LAST
LOOP
INSERT INTO T_TAB(ID, TXT) VALUES(ARR(I).C1, ARR(I).C2);
IF MOD(I, V_COMMIT_FREQ) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
SHOW_ELAPSED_TIME('For Loop');
--Forall DML
FORALL I IN ARR.FIRST..ARR.LAST
INSERT INTO T_TAB(ID, TXT) VALUES(ARR(I).C1, ARR(I).C2);
COMMIT;
SHOW_ELAPSED_TIME('FORALL');
V_LIMIT := V_LIMIT * 10;
END LOOP;
END;
-------------------------------
V_LIMIT: 1000
For Loop commit elapsed time: ,03
For Loop elapsed time: ,02
FORALL elapsed time: 0
-------------------------------
V_LIMIT: 10000
For Loop commit elapsed time: ,37
For Loop elapsed time: ,14
FORALL elapsed time: ,01
-------------------------------
V_LIMIT: 100000
For Loop commit elapsed time: 3,61
For Loop elapsed time: 1,44
FORALL elapsed time: ,16
-------------------------------
V_LIMIT: 1000000
For Loop commit elapsed time: 40,48
For Loop elapsed time: 17,18
FORALL elapsed time: 1,88
-------------------------------
V_LIMIT: 10000000
For Loop commit elapsed time: 676,03
For Loop elapsed time: 204,8
FORALL elapsed time: 30,13
According to the chart above, as the number of rows increases, elapsed time differences are getting bigger. Especially, if you look at the change between 1M and 10M, you can see how wide the difference increases.
Implicit Cursor vs Explicit Cursor
Choosing the right cursor is one of the another important performance factor in PL/SQL. Let’s test this with our table T_TAB which has 30 million row count.
DECLARE
CURSOR C1 IS
SELECT * FROM T_TAB;
REC T_TAB%ROWTYPE;
V_START NUMBER;
PROCEDURE SHOW_ELAPSED_TIME(P_NAME VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(P_NAME||' elapsed time: '||TO_CHAR((DBMS_UTILITY.GET_TIME - V_START)/100));
V_START := DBMS_UTILITY.GET_TIME;
END;
BEGIN
V_START := DBMS_UTILITY.GET_TIME;
OPEN C1;
LOOP
FETCH C1 INTO REC;
IF C1%NOTFOUND THEN
EXIT;
END IF;
END LOOP;
CLOSE C1;
SHOW_ELAPSED_TIME('Explicit Cursor');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
FOR R_ROW IN C1
LOOP
REC := R_ROW;
END LOOP;
SHOW_ELAPSED_TIME('Implicit Cursor');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
END;
Explicit Cursor elapsed time: 184,5
-----------------------------------------
Implicit Cursor elapsed time: 13,67
-----------------------------------------
Implicit cursor is almost 15 times faster then explicit cursor. What is the reason behind this? Array fetch size is the key point for this performance difference. Let’s purge the cursor from the shared pool and execute the FOR loop again.
SQL> SELECT SQL_ID, ADDRESS, HASH_VALUE FROM V$SQL WHERE SQL_TEXT = 'SELECT * FROM T_TAB';
SQL_ID ADDRESS HASH_VALUE
------------- ---------------- ----------
0tjf6f2cvucgc 000000031AF87018 2579313132
SQL>
SQL> exec sys.DBMS_SHARED_POOL.purge('000000031AF87018,2579313132', 'C');
PL/SQL yordam² ba■ar²yla tamamland².
SQL> DECLARE
2
3 CURSOR C1 IS
4 SELECT * FROM T_TAB;
5
6 REC T_TAB%ROWTYPE;
7
8 BEGIN
9
10 FOR R_ROW IN C1
11 LOOP
12 REC := R_ROW;
13 END LOOP;
14
15 END;
16 /
PL/SQL yordam² ba■ar²yla tamamland².
SQL> SELECT FETCHES, EXECUTIONS FROM V$SQLSTATS WHERE SQL_TEXT = 'SELECT * FROM T_TAB';
FETCHES EXECUTIONS
---------- ----------
300001 1
If you look at the cursor statistics from V$SQLSTATS view, the FETCHES data corresponds to 300.001. This shows that array fetch size in FOR loop is 100 by default. For this reason, it reduces the network round-trips. On the other hand, array fetch size is 1 for the explicit cursor which causes too many network round-trips.
Conclusion
- Use bind variables for cursor sharing and security. If your data are not evenly distributed, you can also take a look at topics like Adaptive Cursor Sharing, Histograms and Bind Peeking. (https://www.oracleplsqltr.com/2020/05/24/adaptive-cursor-sharing/)
- Do not use COMMIT statement inside loop unwisely. Otherwise, you excessively give workload on LGWR background process.
- If you have to use DML in PL/SQL, use FORALL.
- Unlike other programming languages, If you develop with PL/SQL, rather than the structure of the language itself, it is always useful to know Oracle architecture.