New Version Of XPLAN_ASH Utility – In-Memory Support

A new version 4.21 of the XPLAN_ASH utility is available for download. I publish this version because it will be used in the recent video tutorials explaining the Active Session History functionality of the script.

As usual the latest version can be downloaded here.

This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and 11.2.0.1).

As an extra however, (more...)

Bitmap Counts

In an earlier post (not very serious) post about count(*) I pointed out how the optimizer sometimes does a redundant bitmap conversion to rowid when counting. In the basic count(*) example I showed this wasn’t a realistic issue unless you had set cursor_sharing to force (or the now-deprecated similar). There are, however, some cases where the optimizer can do this in more realistic circumstances and this posting models a scenario I came across a few (more...)

Spatial space

One thing you (ought to) learn very early on in an Oracle career is that there are always cases you haven’t previously considered. It’s a feature that is frequently the downfall of “I found it on the internet” SQL.  Here’s one (heavily paraphrased) example that appeared on the OTN database forum a few days ago:

select table_name,round((blocks*8),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';

select table_name,round((num_rows*avg_row_len/1024),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';

The result from (more...)

Bind Effects

A couple of days ago I highlighted an optimizer anomaly caused by the presence of an index with a descending column. This was a minor (unrelated) detail that appeared in a problem on OTN where the optimizer was using an index FULL scan when someone was expecting to see an index RANGE scan. My earlier posting supplies the SQL to create the table and indexes I used to model the problem – and in this posting I’ll explain the problem and answer the (more...)

FBI Bug reprise

I’ve just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in 9.2.0.8 wasfixed in 10.2.0.3 – and this is true for the simple example in the posting; but a recent question on the OTN database forum has shown that the bug still appears in more complex cases.  Here’s some code to create a table and two indexes:


create table t1
 (more...)

count(*) – again !

Because you can never have enough of a good thing.

Here’s a thought – The optimizer doesn’t treat all constants equally.  No explanations, just read the code – execution plans at the end:


SQL> drop table t1 purge;
SQL> create table t1 nologging as select * from all_objects;
SQL> create bitmap index t1_b1 on t1(owner);

SQL> alter session set statistics_level = all;

SQL> set serveroutput off
SQL> select count(*) from t1;
SQL> select * from table(dbms_xplan. (more...)

ORA-19599 block corruption when filesystemio_options=SETALL on ext4 file system using Linux

Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.

This was virtual machine running oldish 10.2.0.5.0 Oracle database.

I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)

New Version Of XPLAN_ASH Utility

A new version 4.2 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

There were no too significant changes in this release, mainly some new sections related to I/O figures were added.

One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings (more...)

Data Pump and AQ_TM_PROCESSES

First things first: Thou shalt not explicitly set AQ_TM_PROCESSES=0 ! Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process. However, if you don’t reset this parameter afterwards, you might run […]

Datapump Bug: Scheduler Jobs Not Imported in 11.2

Just the other day I hit the unpublished Bug 16445132: DBMS_SCHEDULER PROBLEM WITH DATABASE MIGRATION VIA EXPDP/IMPDP. It affects Scheduler Jobs that were created to end with a corresponding Scheduler Window. Here’s an excerpt of a Data Pump Logfile: ORA-39083: Object type PROCOBJ failed to create with error: ORA-27469: STOP_ON_WINDOW_EXIT is not a valid job […]

PeopleTools 8.54 Feature: Application Engine Trace File Enhancements

In this blog, we have been reviewing the new features of PeopleTools 8.54. Today is the turn of Application Engine, particularly on its troubleshooting. This release of PeopleTools include several enhancements on Application Engine tracing, which are outlined below:


  • The .AET trace file can now include the PeopleCode trace. This removes the need of checking the .AET file for the the non-PeopleCode steps and the .TRC file for the PeopleCode steps. Surely, .TRC files (more...)

Being Right and Proving It

One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.

In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)

Debugging Faulty Assumptions

I wanted to install Oracle JDevelper 12.1.3 – a version that I had been eagerly awaiting. Since my primary machine is a MacBook, I wanted to install it on OS X 10.9.3.

I downloaded the generic installer and found that the install didn’t run. Since OS X had disappeared from the documentation, I assumed that I would have to fiddle around until I found a combination of JDK and OS (more...)

New Version Of XPLAN_ASH Utility

A new version 4.1 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.

Here are the notes from the change log:

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in (more...)

Database Shutdown With crsctl

I noticed something for the first time tonight when I was playing around in the Enkitec lab – something that I have been doing wrong for a while.  When working in the lab, I often rely on the crsctl command to shut down the entire cluster stack for me.  It’s really easy to use “crsctl stop cluster -all” followed by “dcli -l root -g ~/dbs_group /u01/app/11.2.0.4/grid/bin/crsctl stop crs” to get everything down (more...)

New Version Of XPLAN_ASH Utility

A minor update 4.01 to the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

These are the notes from the change log:

- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data

- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part (more...)

SCAN VIP Troubleshooting

We had a client that was running into a strange issue on their Exadata where new connections coming in through the SCAN were failing.  After doing some troubleshooting, it was discovered that it was related to one of the SCAN listeners not properly accepting requests from new sessions.  The VIP and listener were running, and everything looked normal.

We had the following SCAN setup:

SCAN VIP # VIP IP
1 172.25.2.70
2 (more...)

Wrong Result Bug using group-by placement optimization

Last week I’ve mentioned on Twitter that we ran into wrong result bug. We found workaround quickly but I’ve decided to spend some time to reproduce error and write blog post to warn you about this optimizer behavior.
Special thanks to my colleague who spotted odd results which led us to this finding.

My test (virtual) environment is:
OS: Oracle Enterprise Linux 5.8
DB: Oracle EE 11.1.0.7.12

In test I (more...)

November/December Highlights

In the Oracle technical universe, it seems that the end of the calendar year is always eventful. First there’s OpenWorld: obviously significant for official announcements and insight into Oracle’s strategy. It’s also the week when many top engineers around the world meet up in San Francisco to catch up over (more...)

New Version Of XPLAN_ASH Utility

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...)