Hi List,
In our DB we see huge number of softparses during the load test. So I thought of
setting SESSION_CACHED_CURSORS parameter to positive number(100) to take
advantage of SOFTER SOFT PARSE. But I am getting negative impact.
Kindly advice me what is going on here.
Version :8.1.7.3
OS: Sun Solaris
tkprof output
=============
SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV
FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID =
C.COUNTRYABBREV
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2000 1.76 1.77 0 0 0
0
Execute 2000 0.84 0.74 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4000 2.60 2.51 0 0 0
0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 165 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 HASH JOIN
0 INDEX FAST FULL SCAN (object id 76648)
0 HASH JOIN
0 TABLE ACCESS FULL T2
0 TABLE ACCESS FULL T1
SELECT /*+ cached cursors 100 */FIRST_NAME,LAST_NAME,CUSTOMERID,
COUNTRYABBREV
FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID =
C.COUNTRYABBREV
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2000 2.05 1.99 0 0 0
0
Execute 2000 0.82 0.74 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4000 2.87 2.73 0 0 0
0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 165 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 HASH JOIN
0 INDEX FAST FULL SCAN (object id 76648)
0 HASH JOIN
0 TABLE ACCESS FULL T2
0 TABLE ACCESS FULL T1
********************************************************************************
Program used to generate the above trace file.
==============================================
alter session set SQL_TRACE=true;
alter session set session_cached_cursors=0;
declare
type rc is ref cursor;
C rc;
n number :=0;
begin
n := dbms_utility.get_time;
for i in 1 .. 2000 loop
open C for select /*+ cached cursors 0 */
first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where
p.t1id=e.t1id and p.business_country_id=c.countryabbrev;
close C;
end loop;
dbms_output.put_line( dbms_utility.get_time - n );
end;
/
alter session set session_cached_cursors=100;
declare
type rc is ref cursor;
C rc;
n number :=0;
begin
n := dbms_utility.get_time;
for i in 1 .. 2000 loop
--open C for select /*+ cached_cursors 100 */ * from dual;
open C for select /*+ cached cursors 100 */
first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where
p.t1id=e.t1id and p.business_country_id=c.countryabbrev;
close C;
end loop;
dbms_output.put_line( dbms_utility.get_time - n );
end;
/
SQL> @x
Session altered.
Session altered.
394
PL/SQL procedure successfully completed.
Session altered.
413
PL/SQL procedure successfully completed.
SQL>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
