Note: The following is based on testing with 126.96.36.199 (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...)
Oracle Flashback database and restore points enables us to rewind the database back in time to correct any problems caused by logical data corruption or user errors and it doesn’t require any restoration of backup. There are 2 types of restoration points – 1. Normal Restore Point –> assigns a restore point name to an
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 188.8.131.52 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...)
Recently, i was troubleshooting a datapump export duration problem. Over the period of 18 months, the duration of a multi-table export increased dramatically. A quick analysis showed that the export duration was mainly dependent on one big table. This 50 GB table with no BLOB/CLOB/LONG datatypes took more than 4 hours on a modern system wheras it should not take more than 10 – 15 minutes. The system was performing ONLY single-block I/O requests (db file sequential read). I found this strange and started investigating.
Beginning with 11g, you can enable sql_trace (10046) for datapump with the new syntax:
Original Post can be viewed at Limiting I/O and CPU resources using 11g Oracle Resource Manager
Recently I was working on using Oracle DBMS_RESOURCE_MANAGER to limit resources usage by read-only queries. This was required to prevent long running ad-hoc and poorly written queries.Instead of writing custom sql /script to kill long running session, we decided to utilize Oracle Database Resource Manager. We decided using elapsed_time as criteria for cancelling sql.But during [...]