Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Parse Vs Execute
Jared, Sorry for the confusion.
I did not use 'cached_cursors' as a hint. It is just to identify SQL
statements in tkrpof output.
I should have mentioned /*cached cursors 0 */ instead of /*+ cached cursors
0*/
Thanks
Sami
-----Original Message-----
Jared Still
Sent: Sunday, November 30, 2003 12:24 AM
To: Multiple recipients of list ORACLE-L
Sami,
'cached_cursors' is not a valid hint, at least not in 9i.
Or at least, I can find no reference to it.
And 'cached cursors' as it appears in the SQL is not a valid hint syntax.
You need to set the session_cached_cursors value in the init.ora, and bounce the database. This parameter cannot be set dynamically, at least as of 9i.
Jared
On Sat, 2003-11-29 at 14:44, Sami wrote:
> 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
>
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: jkstill_at_cybcon.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: 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).Received on Sun Nov 30 2003 - 08:09:26 CST