Fwd: Re : High Version Count with usage of Parallel Query
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 07 Jul 2010 22:39:39 +0800
As a follow up to my earlier email
:
<//www.freelists.org/post/oracle-l/High-Version-Count-with-usage-of-Parallel-Query>//www.freelists.org/post/oracle-l/High-Version-Count-with-usage-of-Parallel-Query
Greg : Yes, mixing PQ with Binds is a bad idea. And I am "taking
out" the PQ (the table sizes and queries don't justify PQ at all)
Tanel : 10.2.0.3 V$SQL_SHARED_CURSOR shows these mismatches :
For 648 "child"s of a particular simple SQL statement :
TOP_LEVEL_RPI_CURSOR : 208 (all the same CHILDs as BIND_PEEKED_PQ_MISMATCH)
BIND_UACS_DIFF : 198
PQ_SLAVE_MISMATCH : 434 (which means that the statements do not
really need PX OR is Bug 4367986 in 10.2.0.3)
BIND_PEEKED_PQ_MISMATCH : 208
As I had noted "Oracle Note#296377.1 has one reference to
"TOP_LEVEL_RPI_CURSOR" being a cause."
So, I do know the root cause for the high VERSION_COUNTs and will be
addressing it by resetting DEGREE on those tables.
From my test with an SQL running in a PLSQL loop I find that only
4 Invalidations and Versions are created. :
SQL> select sql_id, sql_text, loads, invalidations,version_count from
v$sqlarea where sql_text like 'SELECT OBJECT_ID%';
SQL_ID SQL_TEXT
LOADS INVALIDATIONS VERSION_COUNT
-------------
------------------------------------------------------------------
---------- ------------- -------------
amw3sv8t5nc9n SELECT OBJECT_ID FROM OBJ_LIST_PQ WHERE OBJECT_NAME =
:B1 4 0 4
SQL> select sql_id, sql_text, count(*) from v$sql where sql_text like
'SELECT OBJECT_ID%' group by sql_id,sql_text;
SQL_ID SQL_TEXT
COUNT(*)
-------------
------------------------------------------------------------------ ----------
amw3sv8t5nc9n SELECT OBJECT_ID FROM OBJ_LIST_PQ WHERE OBJECT_NAME =
:B1 4
SQL> select sql_id, sql_Text, executions, px_servers_executions from
v$sqlstats where sql_text like 'SELECT OBJECT_ID%';
SQL_ID SQL_TEXT
EXECUTIONS PX_SERVERS_EXECUTIONS
-------------
------------------------------------------------------------------
---------- ---------------------
amw3sv8t5nc9n SELECT OBJECT_ID FROM OBJ_LIST_PQ WHERE OBJECT_NAME =
:B1 1245 4978
SQL>
(table OBJ_LIST_PQ has DEGREE 4)
So my question is really : is there a way to determine how many
VERSIONs get created ? Why does my Test create only 4 VERSIONs but I
see 600+ versions in Production. (The table and SQL statement are
different, unfortunately).
Hemant K Chitale
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Fwd: Re : High Version Count with usage of Parallel Query - Hemant K Chitale
