Today was the official launch of UKOUG 2012. We started with keynotes including one from Tom Kytes giving information about Oracle 12c features.
Then I had some very interesting sessions on database performance.

Oracle 12c

When we talk about performance issues on SQL statement, it’s really often due to bad/missing statistics and Oracle using the wrong execution plan. In next Oracle database two features may (or not) help us to deal with this topic:

  • Enhanced statistics
First, Oracle will automatically gather statistics on direct insert into an empty segment, when using a “create a select” statement for example there will be no need to gather statistics after the load.
Currently, the maximum number of buckets for a histogram is 254. So when the number of values is less than 254, Oracle gather a frequency histogram and store the count for each value. When the number is bigger, Oracle gather a height balanced histogram which is not efficient is there is a very popular value masking other enough important values.
Enhanced statistics will introduce a new type of histogram to deal with that type of data distribution.
A new level for dynamic sampling will also be introduced, it will be automatic for parallel queries and persitent.
  • Adaptive execution plan
After the introduction of cardinality feedback in 11g, Oracle will introduce adaptive execution plans.
The database will be able to choose another plan in runtime of the cardinality does not correspond to the real number of rows fetched.
The optimizer will not require anymore running the statement once to see that the plan is bad and will adapt it in runtime.

The importance of being well connected

The session was presented by Graham Wood, a member of Real World Performance group.
With a few slides and a live demo he was able to convince me that with a smaller connection pool the database throughput is better.
In summary, with a big number of connections when the load increases the system might experience unstable performance due to contention in database and resource usage.
The throughput (number of transaction per second) can decrease when the load is too high.
Too many connections:
  • increase the contention in the database
  • increase the resource usage
  • increase the user response time
Reducing the number of connection to a smaller value:
  • increase the throughput
  • decrease the resource usage
  • reduce the user response time
Reducing the number of connection will force the middle tier to enqueue the queries but the database will work more efficiently than if there are many contention events.
See you in next days for more news from UKOUG 2012.
Post Views: 1,238
Thumbnail [60x60]
by
Nicolas Jardot