| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Parse Vs Execute
Dear Jonathan Lewis,
Many thanks for your response.
Using session_cached_cursor parameter I am not getting better response time.
I did run this testcases multiple times but always session_cached_cursor=0
gives better response time.
But the same time w.r.t latch, session_cached_cursor=100 is giving positive
impact.
Version :8.1.7.3
OS: Sun Solaris
tkprof output
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 165 (recursive depth: 1)
Rows Row Source Operation
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 165 (recursive depth: 1)
Rows Row Source Operation
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>
Name Run1 Run2 Diff LATCH.KCL lock element parent 1 2 1 LATCH.KCL name table latch 1 2 1 LATCH.cache buffers lru chain 1 2 1 STAT...calls to kcmgas 2 1 -1 STAT...redo ordering marks 2 1 -1 STAT...free buffer requested 2 1 -1 LATCH.checkpoint queue latch 113 114 1 LATCH.list of block allocation 0 1 1 LATCH.dlm domain lock table la 0 2 2 LATCH.name-service namespace b 17 19 2 LATCH.name-service request que 17 19 2 LATCH.redo writing 4 6 2 STAT...redo entries 26 28 2 LATCH.dlm group lock table lat 0 2 2 STAT...calls to kcmgcs 17 20 3 LATCH.dlm lock table freelist 12,000 12,004 4 LATCH.session allocation 15 19 4 LATCH.enqueue hash chains 0 4 4 LATCH.enqueues 0 4 4 LATCH.dlm resource hash list 24,000 24,005 5 LATCH.process parent latch 30,000 30,005 5 STAT...consistent gets 34 39 5 LATCH.redo allocation 30 25 -5 STAT...db block gets 64 70 6 STAT...consistent changes 60 68 8 LATCH.undo global data 23 14 -9 STAT...db block changes 88 97 9 LATCH.dlm resource table freel 6,026 6,037 11 STAT...session logical reads 98 109 11 STAT...parse time cpu 57 83 26 STAT...parse time elapsed 58 85 27 LATCH.messages 200 236 36 STAT...recursive cpu usage 220 256 36 LATCH.cache buffers chains 404 327 -77 STAT...redo size 4,304 4,500 196 STAT...session cursor cache co -99 100 199 LATCH.shared pool 14,002 8,002 -6,000 LATCH.library cache 94,232 79,824 -14,408 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sami INET: saminathans_at_myrealbox.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Sat Nov 29 2003 - 16:44:25 CST
---------------------------------------------------------------------
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).
 |
 |