Databases Reference
In-Depth Information
The newly created report presents a significant change:
Now the Soft Parse is 97.84 percent.
We can recode the procedure as well; let's rollback the change in
CURSOR_SHARING
:
ALTER SYSTEM SET CURSOR_SHARING=EXACT SCOPE = MEMORY;
And let's alter the
Foo
procedure:
CREATE OR REPLACE PACKAGE BODY Chapter1 AS
PROCEDURE Workload IS
BEGIN
FOR i in 1 .. 50000
LOOP
Foo(i);
END LOOP;
END Workload;
PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
BEGIN
DECLARE
l_stmt VARCHAR2(2000);
BEGIN
l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = :p_cust_id';
EXECUTE IMMEDIATE l_stmt USING CUSTID;
END;
END Foo;
END;
/
