Fwd: Re : High Version Count with usage of Parallel Query
As a follow up to my earlier email : <http://www.freelists.org/post/oracle-l/High-Version-Count-with-usage-of-Parallel-Query>http://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




--
http://www.freelists.org/webpage/oracle-l

Other related posts:

  • » Fwd: Re : High Version Count with usage of Parallel Query - Hemant K Chitale