Re: High Version Count with usage of Parallel Query
- From: Taral Desai <taral.desai@xxxxxxxxx>
- To: hkchital@xxxxxxxxxxxxxx
- Date: Tue, 6 Jul 2010 14:08:25 -0500
You can check
V$SQL_OPTIMIZER_ENV
V$SES_OPTIMIZER_ENV
to find mismatch for version count
On Tue, Jul 6, 2010 at 10:27 AM, Hemant K Chitale
<hkchital@xxxxxxxxxxxxxx>wrote:
>
> I have a few queries that are wrongly using PQ (by having a Parallel DEGREE
> set on the Tables). Unfortunately, they run very frequently, from multiple
> sessions.
> Not always do they get the requisite number of PQ slaves.
> I will be getting these change to No-Parallel operations.
>
> These queries use Binds and not Literals. Thestatements are exactly the
> same. These are not Bind - mismatches (whether being mismatched datatype or
> varying lengths of actual bind values) and are not different accounts and
> schemas. These are not created by the usage of Explain Plan.
>
> However, I notice the V$SQLAREA shows a high VERSION_COUNT while V$SQL
> shows very many CHILD cursors for these queries.
> Oracle Note#296377.1 has one reference to "TOP_LEVEL_RPI_CURSOR" being
> a cause.
>
> Attempting to produce a test case in a small test environment by running a
> SELECT 200+ times (but with DEGREE forced at the table) does show upto 4
> VERSIONs and CHILDs being created and RELOADs also occurring.
> However, I am unable to create theVERSIONs and CHILDs in 10s (tens) from
> my test while I see 100s (hundreds) in production.. (Not that I have tried
> running the SELECT thousands of times from concurrent sessions either !)
>
> Apparently, session_cached_cursors might be coming in to play -- but I
> can't be sure. What else are the "heuristics" that Oracle uses about
> creating a new VERSION (in V$SQLAREA) and CHILD (in V$SQL) for a SELECT that
> is querying on one or more tables where DEGREE is forcing ParallelQuery ?
>
> (As for production, the tables will be reset to NoParallel, with DEGREE 1
> soon, -- their sizes don't justify PQ and the high frequency and concurrency
> of execution of the queries don't make me happy about why DEGREE 4 was set
> on the tables at all).
>
>
> Hemant K Chitale
>
> http://hemantoracledba.blogspot.com
> http://hemantscribbles.blogspot.com
> http://web.singnet.com.sg/~hkchital
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Thanks & Regards,
Taral Desai
Other related posts:
