When the optimizer has to estimate the data volume (the BYTES
column in the plan output), it usually bases this information on the column statistics
, if applicable and available (think of complex expressions).However, whenever there is a VIEW operator
in an execution plan, that represents an unmerged view, the optimizer obviously "loses" this information and starts applying defaults that are based on the column definition
.Depending on the actual content of the columns (more...)
Yesterday I was creating a new Oracle 18.104.22.168 database from a copy of datafiles and archivelogs taken from our standby. I was sure to include archivelogs from just prior to well after the span of the datafile backup time. I had created a new controlfile, gotten everything (more...)
The aim of this post isn’t to explain what the “exadata mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only think I would like to add is that the “exadata mode” is available (more...)
If you tired from the android emulator speed you may have read Chris Muir’s article ADF Mobile: Avoiding the Android Emulator with AndroVM.
In the meantime the successor of AndroVM is GENYMOTION.
If you have configured the port forwarding section from the AndroVM or use GENYMOTION, then you have to (more...)
Note: The following is based on testing with 22.214.171.124 (I believe same issue exists within other Oracle versions).
I recently worked on an interesting problem relating to the “enq: TX – contention” wait event. There are a number of reasons for the wait but the most common (more...)
The TIMESTAMP WITH TIME ZONE data type that got introduced a long time ago is known for some oddities, for example Tony Hasler has a nice summary of some of them here
.Here is another oddity that shows up when trying to aggregate on such a data type. Have a look at the following simple example:
create table t
rownum as id
, date '2000-01-01' + rownum - 1 as some_date
, cast(date (more...)
A new major release (version 3.0) of my XPLAN_ASH tool is available for download.You can download the latest version here.
In addition to many changes to the way the information is presented and many other smaller changes to functionality there is one major new feature: XPLAN_ASH now also supports S-ASH
, the free ASH implementation.
If you run XPLAN_ASH in a S-ASH repository owner schema, it will automatically detect that and adjust accordingly. (more...)
When using Locally Managed Tablespaces
(LMT) with variable, system managed extent sizes (AUTOALLOCATE
) and data files residing in ASM
the Allocation Unit (AU)
size can make a significant difference to the algorithm that searches for free extents.The corresponding free extent search algorithm when searching for free extents >= the AU size
seems to only search for free extents on AU boundaries
in order to avoid I/O splitting
.Furthermore the algorithm seems to (more...)
My site uses a 3rd party SQL monitoring tool which collects data based on the Oracle view v$sqlstats. The tool collects data for all sql statements which have been executed since the previous collection using the last_active_time column. A few months ago we noticed (after an upgrade to 11g) we (more...)
In the previous post
I've demonstrated an unexpected Nested Loop Join caused by an extreme data distribution. Although unexpected at first sight, the performance of the execution plan selected by the optimizer is decent - provided the estimates are in the right ballpark.Here is another case of an unexpected execution plan, this time about Merge Joins
In order to appreciate why the execution plan encountered is unexpected, first a quick summary about (more...)
Recently I came across some interesting edge cases regarding the costing of joins. They all have in common that they result in (at first sight) unexpected execution plans, but only some of them are actual threats to performance.
The first one is about outer joins
with an extreme data distribution. Consider the following data setup:
create table t1
rownum as id
, rpad('x', 100) as filler
, case when rownum > (more...)
I just stumbled upon this bug reference on My Oracle Support:
Bug 13262857 Enh: provide some control over DBMS_STATS index clustering factor computation
This enhancement was long due. Previously, when computing the clustering factor during gathering statistics, the value was incremented, whenever the row was not found in the same block as the previous row. Now, it is finally possible to determine how many blocks should be considered when computing clustering factor. The patch delivers an improved DBMS_STATS package body that can be used to set preferences with value TABLE_CACHED_BLOCKS.
The flaw in the over-simplistic and pessimistic original computation was (more...)
Oracle 10g introduced the QB_NAME
hint that can come handy in case hints need to be applied to more complex statements, in particular when possibly multiple layers of views / subqueries are involved.Jonathan Lewis has a older blog post
that describes more details.Just in case you wonder why sometimes apparently the QB_NAME
hint - along with all other hints that refer to the assigned query block name - seems to be ignored: One (more...)
Here is an interesting limitation to Exadata Smart Scans
- if more than 254 columns
from a table (not HCC compressed, more on that in moment) need to be projected, Smart Scans for that particular segment will be disabled
and Exadata will fall back to conventional I/O. This means that the number of columns in the projection clause can make a significant difference
to performance, since only Smart Scans allow taking advantage of offloading
I’ve worked on 24×7 systems for more than a decade, and I have a real dislike of downtime. For one, it can be a real pain to agree any downtime with the business, and while RAC can and does help when you do work in a rolling fashion, there is still risk.
The promise of online patching has been a long one, and it is only recently that I dipped my toe in the water with them. Unfortunately, they are not a panacea, and in this blog posting I’m going to share some of the downsides.
Of course not all (more...)
When performing aggregate GROUP BY
operations an additional filter on the aggregates can be applied using the HAVING
clause.Usually aggregates are one of the last steps
executed before the final result set is returned to the client.However there are various reasons, why a GROUP BY operation might be somewhere in the middle of the execution plan operation, for example it might be part of a view that cannot be merged
(or was hinted (more...)
I recently investigated an IO performance “spike” on a large 126.96.36.199 transactional system and I thought I would cover some interesting issues found. I am going to take the approach of detailing the observations made from our production and test systems and avoid attempting to cover how other versions of Oracle behave. The investigation also uncovers a confusing database statistic which we are currently discussing with Oracle Development so they can decide if this is an Oracle coding bug or a documentation issue.
The initial IO issue
We run a simple home grown database monitor which watches database wait events and sends an email alert if it detects either a (more...)
After a successful migration from Oracle 10gR2 to Oracle 11gR2, I observed a very odd behavior executing a query; On first execution the query run fast - means 0.3 s; On second not faster but with tremendous reduced speed - approximate 20 s;
Now this behavior is the opposite I experienced with complex queries (lot of joins, lot of predicates) , the first time of execution needs the extra cost of hard parsing and disk reads if the data is not in cache. the second time even the query run initial several seconds it run in a fraction of (more...)