High Version Count with usage of Parallel Query
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 06 Jul 2010 23:27:33 +0800
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
Other related posts:
