Version Counts

By now, every man and his dog is probably familiar with the various bugs that have surfaced from 8.1.5.x to 8.1.6.x when activating the two parameters

• timed_statistics
• sql_trace

The main annoyance is that most of the "workarounds" tend to be setting one or both of the parameters to 'false'. In particular, turning off timed_statistics robs an administrator of huge amounts of quality information to tune the database.

Various posts on Metalink and other forums have suggested an alternative, using the hidden parameter

_sqlexec_progression_cost=0

Much of the information on Metalink is non-committal about the effectiveness of the parameter (see Note 68955.1), so if you do want to use it, make sure you do some monitoring to see if you're getting any joy.

Obviously before you even consider any of this, first check to see if your version counts are growing abnormally. The following Steve Adams script can be used to assist in this area

select
  to_char(min(v)) ||
  decode(
    max(v) - min(v),
    0, null,
    ' to ' || to_char(max(v))
  )  version_count,
  count(*)  cursors
from
  (
    select
      count(*)  v
    from
      sys.x_$kglcursor
    where
      inst_id = userenv('Instance') and
      kglhdadr != kglhdpar
    group by
      kglhdpar,
      kglnahsh
  )
group by
  trunc(round(log(2, v), 37))
/