You may have previously seen a short post I did on a SQL statement to identify which statements are using dynamic sampling.
If not, quick recap:
SELECT p.sql_id, t.val
FROM v$sql_plan p
, xmltable('for $i in /other_xml/info
where $i/@type eq "dynamic_sampling"
columns attr (more...)
A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post
includes links to video tutorials explaining what the tool is about.As usual the latest version can be downloaded here.
The new version comes with numerous improvements and (more...)
Yesterday I was creating a new Oracle 22.214.171.124 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...)
The values provided by the “parse count (total)” and “session cursor cache hits” statistics are subject to several bugs. And, what’s worse, for years Oracle didn’t care to fix it. This is my impression, at least.
Then, when few weeks ago I read in the Oracle Support note 13837105.8 (more...)
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...)
This is a short post about a strange behavior I discovered today…
The fact is that it is not really possible to use the named notation with the SEM_MATCH table function. In fact, even though the parameter’s names can be specified, the order of the parameters overrides the specification done (more...)
Oracle 12c is around the corner and due that I have received many questions from fellow DBAs about what was new about Backup and Recovery at 11g I have decided to write a small white paper about it.
Hope you will enjoy reading it as much I enjoyed writing it.
A reader of this blog, VijayS, asked me to share the script I use to demo ITL deadlocks that I mentioned in this comment. Since other readers might be interested, here is the script.
SET TERMOUT ON FEEDBACK ON VERIFY OFF SCAN ON ECHO ON
SELECT * FROM v$version (more...)
I’ve previously written about manually rewriting an OR condition into a UNION ALL using LNNVL.
This is a description of a performance issue observed in the real world from the optimizer coming up with a CONCATENATION operation against many child operations including an INLIST operator and other children which then (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...)
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...)
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...)
Three items within:
Part 1 – Subquery materialisation not possible
Part 2 – Materialised views
Part 3 – Lock scripts
Part 1 – Subquery materialisation not possible
Yesterday I was reminder of a familiar issue, officially listed as a bug, that can occur when combining subquery materialisation with distributed transactions.
I had previously tuned a query for a colleague, the best approach for which was using implicit materialised subquery factoring (implicit because it wasn’t hinted as /*+ materalize */ but was referenced multiple times in the main query) and I had provided such a solution, without even a thought (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
While trying to import using impdp got the below error Table "ANAND"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate Processing object type TABLE_EXPORT/TABLE/TABLE_DATA ORA-31693: Table data object "ANAND"."TEST":"Y2012_Q2_M06" failed to load/unload and is being skipped due to error: ORA-38500: Unsupported operation:
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...)