This is a case i was working on a few days ago.I was asked to tune a specific query on our production server, so here is a quick test case to model the problem (i only kept the interesting part of the original query which was much more longer)
Tunning
Run-time side effect of dropping a composite unused index
Unused Index although they seem not needed , dropping them must be carefully tested as it can have some side effect. As explained by Jonathan Lewis, due to index sanity check cardinality miss estimate can happen after dropping the unused index.
“The reason for this is the “index sanity check”. When the optimizer is doing its cardinality estimates, if it see equality conditions on the set of columns that make up an index it can use the distinct_keys statistic from the index in the calculation rather than using the standard calculation of multiplying together the num_distinct of the separate columns. ”
There is also a good example of the impact this can have on the query execution plan(switching join method from hash join to nested loop due to the drop of the estimated number of rows returned).Link
In this post i will show another interesting case when this can have an impact on query performance.As Franck Pachot Said :
Playing with adaptive-ticks CPUs [CONFIG_NO_HZ_FULL] in UEK 4 and ORACLE database
The introduction of adaptive-ticks CPUs (Dynamic ticks) in UEK R4 represent a significant step forward on getting rid of the timer tick/interrupt (Full tickless operation).
“That interrupt is the CPU’s cue to reconsider which process should be running, catch up with read-copy-update (RCU) callbacks, and generally handle any necessary housekeeping.” Ref
This periodic timer interpret “interference” (based on CONFIG_HZ) have an effect on performance and power consumption which makes developers wish to abolish it. In previous version, a partial solution was used that consist on disabling timer tick for idle CPU which is controlled by the configuration option CONFIG_NO_HZ.This mode reduced considerably the power usage as it allowed idle CPU to stay in deeper c-state. What adaptive ticks feature bring us is the possibility to reduce the timer interrupt to 1 tick/second (1HZ) for non idle CPU having only one runnable task. This feature minimizes kernel overhead (up to 1%) and the potential latency problem , it was primarily targeted for High-performance computing (HPC) and real time application so it’s not necessary for every one.
For more info on Nearly full tickless operation please check this articles as most of the information here come from there :
- http://lwn.net/Articles/549580/
- http://lwn.net/Articles/549592/
- https://www.kernel.org/doc/Documentation/timers/NO_HZ.txt
- http://www.breakage.org/2013/11/15/nohz_fullgodmode/
- https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/Performance_Tuning_Guide/sect-Red_Hat_Enterprise_Linux-Performance_Tuning_Guide-CPU-Configuration_suggestions.html
TIME TO TEST :
- ORACLE 12.1.0.2.6
- OEL : 4.1.12-32.1.2.el6uek.x86_64
- 4 CPU : 2 sockets / 2 cores per socket Continue reading
Semi-join to Inner-join enhacement in 12C
I will be using a slightly modified version of the example used by Jonathan Lewis here to demonstrate the enhancement introduced in 12c to the semi-join to inner-join transformation.Let’s begin !
New training available : Troubleshooting Oracle performance using trace data (Event 10046)
I am pleased to announce that classroom (Only in Tunisia) and online training are now available 😀
Information on training schedule and registration details are available on the Training Page.
Slow INDEX FULL SCAN (MIN/MAX) after rollback
Last week i was called by multiple users signaling a total slowdown of the the “system”. After a few check i decided to trace one of the affected user action.There was only a single SQL statement consuming mostly of the time and it was as simple as this :
Adpative cursor sharing and row source statistics anomaly
Recently when playing with adaptive cursor sharing i observed a strange behavior when activating the row source statistics.Here is a quick test case to demonstrate the finding : (Tested database 11.2.0.3.9 and 12.1.0.2)
Effect of wrong cardinality estimate on Hash join run time execution (Fanout)
Finding the optimum fanout (represent the number of partition the build input will be split into) is one of the keys to optimum performance of a hash join. “High fan-out can produce a large number of small partitions, resulting in inefficient I/O. At the other extreme, small fan-out can produce a small number of large partitions, which will not fit in hash memory.”(as quoted by hash-joins-implementation-and-tuning)
In this blog post i will not discuss how the hash join algorithm work in fact it’s well covered on other location like “Jonathan lewis book oracle cost based optimizer” and http://docslide.us/documents/hash-joins-implementation-and-tuning.html (old but still helpful) and Steve Adams http://www.scribd.com/doc/16883906/Adams-Hash-Joins-Oracle .
The calculation of the optimum fanout is based on the Build input estimated size which is Dependant on the column sizes and row counts.
Here is a quick example to demonstrate how sufficiently inaccurate cardinality can affect hash join runtime performance :
ORACLE 12C : Distributed Queries and bind variable peeking
In ORACLE 12C a new bug fix was introduced :
| 12534597 | 12.1.0.0 | Bind Peeking is disabled for remote queries |
select * from V$SYSTEM_FIX_CONTROL where bugno = ‘12534597’;
So from now on bind variable peeking will kick in also for distributed query.
Let’s run some test :
Async direct path read : PART2 (playing with db_file_multiblock_read_count and _direct_io_slots)
In the previous part of this series i showed how we can set the minimum number of I/O slots (_direct_io_slots) used by Async direct path read and how this can improve the throughput.In this second part i will play with different setting for the maximum I/O size “db_file_multiblock_read_count” and minimum number of outstanding I/O “_direct_io_slots” values and check there impact on throughput and CPU consumption.
