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

Partition gotchas

Two minor partition gotchas on partition extent sizing and on the number of partitions in an interval partitioned.

First – old news – note that initial extent size for partitioned tables changed in 11.2.0.2.
Somehow this passed me by and I was late to the large extent party – duh!
Influenced by parameter _partition_large_extents, the default was changed to 8MB rather than 64K for autoallocated ASSM extent sizing.

Pre 11.2. (more...)

Primary on FileSystem and Standby on ASM

For one of the client, standby server went down. We had another standby server which was kept down for more than a month. Decision was taken to start the server and apply incremental SCN based backup on the standby database.

The standby was on ASM and the Primary on filesystem.Incremental backup was started from the SCN reported by below query

select min(fhscn) from x$kcvfh;

Once the backup completed, it was transferred to standby, standby (more...)

Video Tutorial: XPLAN_ASH Active Session History – Introduction

I finally got around preparing another part of the XPLAN_ASH video tutorial.

This part is about the main funcationality of XPLAN_ASH: SQL statement execution analysis using Active Session History and Real-Time SQL Monitoring.

In this video tutorial I'll explain what the output of XPLAN_ASH is supposed to mean when using the Active Session History functionality of the script. Before diving into the details of the script output using sample reports I provide some overview and (more...)

"SELECT * FROM TABLE" Runs Out Of TEMP Space

Now that I've shown in the previous post in general that sometimes Parallel Execution plans might end up with unnecessary BUFFER SORT operations, let's have a look what particular side effects are possible due to this.

What would you say if someone tells you that (s)he just did a simple, straightforward "SELECT * FROM TABLE" that took several minutes to execute without returning, only to then error out with "ORA-01652 unable to extend temp segment", (more...)

“log file sync” and the MTTR Advisor

I recently investigated a performance problem on an Oracle 11.2 OLTP trading system and although we still don’t fully understand the issue (and which versions of Oracle it effects), I thought I would share what we found (and how we found it). We had a hardware failure on the database server, within 30 seconds the database had automatically been restarted on an idle identical member of the cluster and the application continued on the (more...)

Returning Error Codes from sqlplus to Shell Scripts

When I have to run SQL scripts, I prefer to do it via executable ksh scripts (I use ksh because it's usually an Oracle pre-requisite so I can rely on it being installed). One thing that I've just recently started adding into my scripts is exiting sqlplus on error and returning the ORA error code. This is done via the WHENEVER SQLERROR feature of sqlplus:

sqlplus / as sysdba <<EOF

        whenever sqlerror exit sql. (more...)

Unnecessary BUFFER SORT Operations – Parallel Concatenation Transformation

When using Parallel Execution, depending on the plan shape and the operations used, Oracle sometimes needs to turn non-blocking operations into blocking operations, which means in this case that the row source no longer passes its output data directly to the parent operation but buffers some data temporarily in PGA memory / TEMP. This is either accomplished via the special HASH JOIN BUFFERED operation, or simply by adding BUFFER SORT operations to the plan. The (more...)

ORA-00600: [ktbdchk1: bad dscn]

Last week I had performed switchover activity of database on version 11.2.0.3 The switchover was performed using dgmgrl “swicthover to standby” command. After sometime we started receiving “ORA-00600: [ktbdchk1: bad dscn]” on the primary database.

Tue Dec 16 10:33:26 2014
Errors in file /ora_software/diag/rdbms/db02_dbv/dbv/trace/db02_ora_16271.trc  (incident=434103):
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /ora_software/diag/rdbms/db02_dbv/dbv/incident/incdir_434103/db02_ora_16271_i434103.trc

The trace (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...)

RELY trumps DISABLE

Learning, relearning or unforgetting…

What value is there in a DISABLEd constraint?

This was a question on the OTN forums this week and a) my first reply was wrong and b) I couldn’t find a clear demonstration elsewhere.

The documentation is clear.

RELY Constraints

The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that (more...)

The mess that is fast-refresh join-only Materialized Views

Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.

This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.

This is quite a long article so I will do a top-level TOC first

  1. Why am I looking at a fast-refresh, on-commit, join-only (more...)

Upgrading to 11.2.0.4 – Dictionary View Performing Poor

Just a quick blog post on things you might see after upgrading to 11.2.0.4. We recently upgraded database from 11.2.0.3 to 11.2.0.4 and query on some data dictionary views ran too slow.

1. Performace of query on dba_free_space degraded
2. Performance of query involving dba_segments is slow

DEV01> select ceil(sum(b.bytes)/1024/1024) b from sys.dba_free_space b;

Elapsed: 01:31:45.78

Searching MOS pointed to these Doc Ids (more...)

Making Copies of Copies with Oracle RMAN

I recently had need to make a copy of an image copy in Oracle rman. Since it wasn't immediately obvious to me, I thought it was worth sharing once I had it sorted out. I was familiar with making a backup of a backup, but had never thought about making a copy of a copy.

First you need to create an image copy of your database or tablespace. For the sake of example, I'll make (more...)

OLTP Compression, Drop Column, Partition Exchange

With Basic Compression, you cannot drop a column.

create table t1
(col1 number
,col2 number)
compress;
 
table T1 created.

alter table t1 drop column col2;

SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 -  "unsupported add/drop column operation on compressed tables"
*Cause:    An unsupported add/drop column operation for compressed table
           was attemped.
*Action:   When adding a column, do not specify a default value.
           DROP column is only supported in the form  (more...)

Heuristic TEMP Table Transformation

There are at least three different ways how the Oracle optimizer can come up with a so called TEMP table transformation, that is materializing an intermediate result set:- As part of a Star Schema transformation the repeated access to dimensions can be materialized- As part of evaluating GROUPING SETs intermediate result sets can be materialized- Common Subquery/Table Expressions (CTE, WITH clause)Probably the most common usage of the materialization is in (more...)

ORA-16534 When Converting to/from Snapshot Standby with DataGuard Broker

We here at Seilerwerks Industries (not really) have been using snapshot standby databases to refresh an array of unit test databases from a common primary. During the business day, these would be converted to snapshot standby databases for testing, then overnight they are converted back to physical standby and recovered up to the master again.

However we ran into one problem the other week. I noticed that the test3 database was still in physical standby (more...)

Advanced Queue Quickie: Errors and Privileges

File this one under the misleading-errors department. One of my developers was working with a new queue. He pinged me when he got this error trying to create a job that used the queue:

ERROR at line 1:
ORA-27373: unknown or illegal event source queue
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 314
ORA-06512: at line 2

The CREATE_JOB statement was:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'foo.bar_q_job',
job_type => 'PLSQL_BLOCK',
(more...)

Purging SYSAUX

In continuation to my previous post “SYSAUX Growing Rapidly” , here i wanted to present the second method of purging sysaux. Basically i tried to perform the steps as mentioned in previous post and drop_snapshot_range was taking too long (> 24hrs) and still running on test db.Again WRH$_ACTIVE_SESSION_HISTORY was in top of the list occupying…

SYSAUX Growing rapidly!!! What can be done

Recently i have been working on cleaning up SYSAUX tablespace for few of clients, so thought to put down my steps which might be helpful to some of you out there. Why does SYSAUX tablespace grows much larger than expected? There could be number of potential reasons: 1. ASH data has grown too large (SM/AWR)…