Re: Multiple SQL version count with cusror_sharing=similar
From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Wed, 01 Jul 2009 15:42:15 +0200
Message-Id: <694423209_at_web.de>
> I just checked that after parsing first version of the query, sql
> trace of second version (i.e. where deptno=20) is resulting in
> library cache miss. Need to re-run the case, and if result is
> consistent, will definitely share.
Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/
Date: Wed, 01 Jul 2009 15:42:15 +0200
Message-Id: <694423209_at_web.de>
> I just checked that after parsing first version of the query, sql
> trace of second version (i.e. where deptno=20) is resulting in
> library cache miss. Need to re-run the case, and if result is
> consistent, will definitely share.
Neeraj,
just beware of the effect of enabling SQL trace: By enabling SQL Trace you force a library cache miss if the version of the statement *with tracing enabled* is not yet in the library cache, because this setting is part of environment that is checked when looking for existing child cursors. You might want to double-check V$SQL_SHARED_CURSOR to make sure that the miss wasn't caused by simply enabling the trace.
Depending on the order of execution you need to take that into account for your analysis.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 01 2009 - 08:42:15 CDT