Oracle-L: Negative impact of SESSION_CACHED

From: <jaysingh1_at_optonline.net>
Date: Fri, 28 Nov 2003 07:44:32 -0800
Message-ID: <F001.005D8165.20031128074432@fatcity.com>

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.

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: jaysingh1_at_optonline.net

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: ListGuru_at_fatcity.com (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).

Received on Fri Nov 28 2003 - 09:44:32 CST

Negative impact of SESSION_CACHED_CURSORS parameter