Re: Parse Vs Execute

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 30 Nov 2003 06:54:25 -0800
Message-ID: <F001.005D83EB.20031130065425@fatcity.com>

How very irritating.

But I don't think you mentioned in earlier posts (or at any rate I missed it) that you are running OPS/RAC, and there could be all sorts of less well-known side effects coming in there.

Could you also take a snapshot of the v$dlm_misc figures, and the DLM-related session stats for the two different tests. Possibly the time difference is related to library cache coherence between instances rather than the usual local latch problems.

There is also an oddity in your figures that I'm going to have to think about. I would have expected your test case to cache the cursors in the pl/sql cursor cache, and not use the session cursor cache anyway. I may be wrong, and perhaps the explicit open/close is changing things in ways I didn't expect; but if I'm right, then perhaps the test with session_cursor_cache is giving slower results because you are disabling a special pl/sql optimisation.

I'll try to find some time to test around the problem over the next few days.

BTW - it's a little unsafe to rely on any timing results that are produced whilst running sql_trace - I've found some VERY strange things happening to reported CPU usage when sql_trace = true.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, November 29, 2003 10:44 PM

> 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.
>
> 1) session_cached_cursor=0 -> more latches but good response time(2.60)
> 2) session_cached_cursor=100 -> less # of latches but higher response
> time(2.87)
>
> 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>
>
>
> 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
> 181,088 160,677 -20,411 112.70%
>
>
> --
> 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 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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 Sun Nov 30 2003 - 08:54:25 CST