Databases Reference
In-Depth Information
How to do it...
The following steps will show how we can minimize latches by using bind variables:
1.
Connect to the database as
SYSDBA
:
CONNECT / AS SYSDBA
2.
Query the
V$SYSTEM_EVENT
dynamic performance view to monitor
latch-related events:
COL EVENT FOR A37
SELECT EVENT, TOTAL_WAITS, TIME_WAITED, AVERAGE_WAIT,
TOTAL_TIMEOUTS
FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'latch:%' ORDER BY EVENT;
3.
Connect to the
SH
schema and create the package
CHAPTER4
containing a
test workload:
-- FROM CHAPTER 4 EXAMPLE...
CONNECT sh@TESTDB/sh
CREATE OR REPLACE PACKAGE sh.CHAPTER4 AS
PROCEDURE WORKLOAD_NOBIND;
PROCEDURE WORKLOAD_BIND;
PROCEDURE WORKLOAD_BIND_STATIC;
PROCEDURE TEST_INJECTION(
NAME IN sh.customers.cust_last_name%TYPE);
PROCEDURE TEST_INJECTION2(
NAME IN sh.customers.cust_last_name%TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY sh.CHAPTER4 AS
PROCEDURE TEST_NOBIND(CUSTID IN sh.customers.cust_id%TYPE) IS
BEGIN
DECLARE aRow sh.customers%ROWTYPE;
l_stmt VARCHAR2(2000);
BEGIN
l_stmt :=
'SELECT * FROM sh.customers s WHERE s.cust_id = ' ||
TO_CHAR (CUSTID);
EXECUTE IMMEDIATE l_stmt INTO aRow;
END;
