Stats collection time

Yesterday I posted a note about querying dba_optstat_operations to get a quick report of how long calls to dbms_stats had been taking but said I had another script that helped to fill some of the gaps it left. One of my readers points out fairly promptely that 12c enhances the feature considerably, with a view dba_optstat_operation_tasks that (for example) lists all the tables processed during a single call to gather_schema_stats.

Well, I wrote my script (more...)

Stats time

I don’t really remember how long it’s been since Oracle created an automatic log of how long a call to the dbms_stats package took, though it was probably some time in the 10g time-line. It wasn’t until it had been around for several years, though before I wrote little script (possibly prompted by a comment from Martin Widlake) that I’ve used occasionally since to see what’s been going on in the past, how variable (more...)

Securefile space

A script hacked together a couple of years ago from a clone of a script I’d been using for checking space usage in the older types of segments. Oracle Corp. eventually put together a routine to peer inside securefile LOBs:

rem     Script:         dbms_space_use_sf.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2013
rem     Purpose:
rem     Last tested
rem     Not tested
rem             11.1. (more...)

Space Usage

Here’s a simple script that I’ve used for many years to check space usage inside segments.  The comment about freelist groups may be out of date  – I’ve not had to worry about that for a very long time. There is a separate script for securefile lobs.

rem     Script:         dbms_space_use.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2002
rem     Purpose:
rem     Last tested 
rem             11.2. (more...)

Hit by the ‘Tech Support’ Scammers

I got a call earlier today from the Tech Support Scammers. You’ve probably heard of this horribly unethical practice already, but the premise is that they cold-call seemingly randomly and try to convince you that there is a problem with your PC/router, and then attempt to get you to allow them remote access to your PC to ‘fix it’. Some then claim problems are due to expired warranties on the computer and demand payment, others (more...)

The Problem with autotrace

There are few ways to see the execution plan of a SQL statement. One of these ways is the autotrace option in sqlplus. It is a very easy-to-use feature and people use it quite often. But there is a risk here. The autotrace option doesn’t always show you the correct execution plan. Lately I prepared […]

New Version Of XPLAN_ASH Utility

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

As usual the latest version can be downloaded here.

This version comes only with minor changes, see the change log below.

Here are the notes from the change log:

- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" (more...)

Tracefile Automation – Simplify Your Troubleshooting Tasks

Here’s a common Oracle troubleshooting scenario when a SQL statement needs tuning and/or troubleshooting:

  • log on to dev server
  • connect to database (on a different server)
  • run the SQL statement with 10046 tracing enabled
  • ssh to the database server
  • copy the trace file back to work environment
  • process the trace file.


All of this takes time. Granted, not a great deal of time, but tuning is an iterative process and so these steps will (more...)

Slow full table scan due to row chaining

Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.

This was the statement:

select count(1)
from CLIENT k
where k.expires is null;

I've used fake names for table name and columns.
Database version:

Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (more...)

DML Operations On Partitioned Tables Can Restart On Invalidation

It's probably not that well known that Oracle can actually rollback / re-start the execution of a DML statement should the cursor become invalidated. By rollback / re-start I mean that Oracle actually performs a statement level rollback (so any modification already performed by that statement until that point gets rolled back), performs another optimization phase of the statement on re-start (due to the invalidation) and begins the execution of the statement from scratch. Note (more...)

Extended Stack Profiling – Ideas, Tools and Comments

Topic: This post provides a short summary and pointers to previous work on Extended Stack Profiling for troubleshooting and performance investigations.

Understanding the workload is an important part of troubleshooting activities. We seek answers to questions like: what is the system doing, where is the time spent, which code paths are most used, what are the wait events, etc. Sometimes the relevant diagnostic data is easy to find, other times we need to dig (more...)

New Version Of XPLAN_ASH Utility

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

As usual the latest version can be downloaded here.

This version primarily addresses an issue with 12c - if the HIST mode got used to pull ASH information from AWR in 12c it turned out that Oracle forgot to add the new "DELTA_READ_MEM_BYTES" columns to DBA_HIST_ACTIVE_SESS_HISTORY - although it got officially added to V$ACTIVE_SESSION_HISTORY in 12c. So now I had to implement (more...)

Analyze database activity using v$log_history

The v$log_history view contains important information on how application’s users use the database , this view can help you define periods with the most activity in the database. v$log_history queries You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the … Continue reading Analyze database activity using v$log_history

The post Analyze database activity using v$log_history appeared first on Oracle (more...)

MRP process on standby stops with ORA-00600

A rather not so great post about an ORA-00600 error i faced on a standby database. Environement was on Sun Super Cluster machine. MRP process was hitting ORA-00600 while trying to apply a specific archive log.

The error message was something like this

MRP0: Background Media Recovery terminated with error 600
Errors in file /u01/app/oracle/product/
ORA-00600: internal error code, arguments: [2619], [539], [], [], [], [], [],  (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 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...)


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