This is a new series on Performance and Tuning which talks about Best Practises for Oracle 12c Release 2 Database. This this the first instalment to it hence not very comprehensive but i thought of starting something which i can start detailing in my upcoming blogs:
The document is divided into following sections:
- Tuning Analogy for the optimizer
- Tuning Recommendations for Standalone/RAC Databases
- Tuning Recommendations for Sharded Databases
- Tuning Recommendations for Oracle Database on Cloud
- Tuning Recommendation on Oracle for Big Data Platforms
- Tuning Recommendations for Database Listeners
- Tuning Recommendations for Oracle Golden Gate
- Tuning Recommendations for Application Developers
Tuning Analogy for the optimizer
Since every Developer has their own coding style and experience based on which they struggle to write efficient
SQL Code and end up having a bad execution plans or very complex code. But is not up to Oracle
but an application developer to decide what should be the expected behaviour of
the query.
For instance:
Google Maps can be
one of the analogies:
Say a driver wants
to know the most efficient route from A Location to B Location. A query is
similar to directive where based on joins used in table "it need the most
efficient route from point A to point B" or “from point A to point B by
way of point C." The Map uses an internal algorithm, which relies on
factors such as speed and feasibility/difficulty, to determine the most
efficient route. The driver can influence the Maps decision by using directives
such as "I want to arrive as fast as possible" or "I want the
easiest ride possible."
In this analogy, Maps
can generate multiple execution plans as possible with route details.
Internally, if you get into more details the map would have divided the overall
route into several sub routes (sub plans), and calculate the efficiency for
each sub route separately. For example, Maps may estimate one sub route at 5
minutes with bad conditions, an alternative sub route at 10 minutes with good
conditions, and so on.
The maps picks the
most efficient (lowest cost) overall route based on user-specified goals and
the available statistics about roads and traffic conditions. More accurate statistics
leads to better advice. For example, if the Map is not frequently notified / updated
of traffic jams, road closures, and poor road conditions, then the recommended
route may turn out to be inefficient (high cost).
Having said that it all depends on what developer wants and
how they want the code be optimized for oracle to select best execution plan
with an exception of how Oracle DBA have configured the database to be best
optimized for Developers.
Tuning Recommendations for Standalone/RAC Databases
There has been various tuning enhancements with relation to Oracle 12.2 Optimizer , The best Source to start is Oracle Documentation and I have used it extensively to write this article with few examples I could think of.
OPTIMIZER_ADAPTIVE_PLANS:
The New initialization Parameter "OPTIMIZER_ADAPTIVE_PLANS" should be set to "TRUE".
This enables the Optimizer to choose the correct sub plan based on latest statistics and histogram information statistics collector have generated. In Most cases I have see is the best option to SWITCH ON this parameter as it gives optimal and optimized query plan.
More details can be found on:
There have been instances where DBA's have preferred switching off this parameter for a simple reason "If Enabled This Feature Switches The Plan With Every Execution".
But that's not correct as mentioned earlier this may happen due to several reasons and if controlled correctly by DBA and DEVELOPER these issue can be avoided.
DBA:
1) Needs to ensure Shared pool is optimized for the application and configured correctly with 100% hit Ratio so that.
2) Decide if Statistics feedback is required and is behaving as required if not, its better to disable it.
Developer:
1) Needs the ensure Queries are optimized based on
Data Distribution and are using Bind Variables.
2) Now that we know how Adaptive Query
optimization can be useful and should be used for the best of its benefits,
another parameter OPTIMIZER_ADAPTIVE_STATISTICS is by default set to False and
shouldn't be switched on unless until tested appropriately.
Cursor-Duration Temp Table:
As explained in my article about the new feature "Cursor-Duration Temp Table" mentioned in the below link. This feature gives definite enhancement by materializing the intermediate results in PGA Memory for Complex sub-query operation which refers the same intermediate results multiple times. which leads to better and faster execution of query but all good comes few issues like :
As per Oracle documentation it, loads data into
memory until no memory remains, in which case it creates temporary segments on
disk leading to 2 situations like Out of PGA memory which also means other
sessions are impacted.
The bottleneck may be due to the spills of the pending processing to
Disk due to unavailability of PGA Memory leading to more I/O calls i.e. Swapping.
DBA
Adequately Sized PGA Memory will ensure no memory exhaustion and I/o bottleneck due to this enhancement.
Tuning Recommendations for Sharded Database
The evaluation is still going on and with more testing we might be able to figure out the latency. As of now the major performance bottleneck would be with network when cross-shard operations are seen.
Tuning Recommendations for Oracle Cloud
You tune the performance of Oracle Database on Oracle Database Cloud Service using the same tools you would use for an Oracle database running on any system in your data center. The fact that the database is housed in the Oracle Cloud does not place any restrictions on performance tuning.
Although based on the findings there might be a need to scale up the databases environment in cloud.
Tuning Recommendations for Big Data Platforms
1) Load Data from Oracle into Hadoop using SQOOP:
- Ensure enough Mappers are set to execute the tasks.
- Try use LZO compressions where possible.
- Make use of -direct option.
- Set appropriate JVM Heap using mapred.child.java.opts.
- If query is complex and using WITH operator will help gain better performance.
- If on 12c Disable Adaptive Features and test the Sqoop Jobs for better performance.
2) Load Data from Oracle into Hadoop/Kafka using Golden Gate Kafka Handler:
- Use Kafka Handler to operate in Transaction(TX) Mode. (gg.handler.<name>.Mode = tx)
- Use NonBlocking Async Calls. (gg.handler.<name>.BlackingSend = false).
- Enable Compression.(Compression.Type=snappy)
- Setting batch.size controls maximum number of bytes to buffer before sending it to Kafka.
- Setting linger.ms controls maximum milliseconds to wait before sending data to Kafka. Tuning batch.size and linger.ms parameter may lead to big performance gains.
Tuning Recommendations for Database Listener
Like Shared Server and Dedicated Server Architecture, DRCP is yet another connection management framework which enables less utilization of resources and giving performance benefits. It majorly:
- Enables resource sharing among multiple middle-tier client applications.
- Improves scalability of databases and applications by reducing resource usage.
For More information on How DRCP works please Refer to my article :
or Oracle Documentation to identify the difference between Dedicated, shared and DRCP connections.
Tuning Recommendations for Golden Gate
2) Compress the data before it is sent to Replicat
3) Set TCPBUFSIZE based on Avg Round Trip Time and amount of data which can be transmitted over network. This will increase the TCP/IP packet Size and avoid unnecessary round trips which may delay the process.
4) Use GROUPTRANSOPS to control the SQL operations that are contained in a Replicat transaction.
For More information on GG Tuning , please refer to Oracle GG Tuning guide:
Tuning Recommendations for Application Developers
I would like to highlight few of important changes in 12cR2 which can bring in significant performance improvement when used
Approximate Query Processing:
There might be a need to get approximate data and not the exact figures for some of the BI Application's for Instance a Super Market owner wants to depict the item which is most sold weekly or monthly based on which new orders can be placed. Having said that the Super Market owner will be least bothered about the exact number which anyhow he can avail from different other sources but to ensure the item is not "Out of Stock" he needs approximate sale details about the product.
Now that you have understood the scenario, lets discuss how oracle works internally. For large data sets, exact aggregation queries consume extensive memory and often spill to temp space which may lead to unexpected performance issue.
Oracle has bought this very interesting feature through approximate query processing and the accuracy of the approximate aggregation is over 97% but the processing time and the performance of the query is much faster than getting result using exact aggregation.
Now that you have understood the scenario, lets discuss how oracle works internally. For large data sets, exact aggregation queries consume extensive memory and often spill to temp space which may lead to unexpected performance issue.
Oracle has bought this very interesting feature through approximate query processing and the accuracy of the approximate aggregation is over 97% but the processing time and the performance of the query is much faster than getting result using exact aggregation.
Partitioning Enhancements in 12cR2
12cR2 further enhances the Partitioning for developers and reduces the management of the same.
Automatic List Partitioning : with addition to this feature new partitions can automatically be created on addition of new List Value. This reduces the partition maintenance and optimizes query for better performance.
In-Memory Join Group:
Optimise table joins by using In-Memory Join Groups.
*** I hope you enjoyed reading the Blog, more to come in upcoming parts of this Blog. ***
