Unusable unique constraint

Another annoying thing about unusable indexes

I’m surprised that I can’t remember coming across this before before.

I want to archive some data from a bunch of partitions.

Unfortunately, I can’t follow the virtuous circle of data lifecycling and partition the table in such a way that I can archive all the data from a particular partition (or subpartition) via exchange partition.

Without going into too much detail and skipping a bunch of other steps, (more...)

ORA-00600 [kkzlpllg:5] When Dropping MView Log

This week a co-worker and I have been doing some rapid-fire testing to improve fast-refresh performance on an old materialized view, which does some summary aggregations on a 1.9 billion row (and growing) master table. One of the things we tested was using the new-in-11gR2 COMMIT SCN feature. There is a great blog post describing the benefits of this feature by Alberto Dell'Era. To quickly summarize and over-simplify, it provides a much faster way (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...)

Remote Surprise

Here is an example of surprising behaviour from a remote DB from an OTN forum thread

Setup a link to a remote DB (I’ve used an actual remote DB and not tested a loopback)

Remote DB:

create table t1
(col1  varchar2(1));

Local DB:

create or replace view v1 
as 
select count(*) c1 from t1@l1;

Then alternate variations on this sequence of events:

1. On local DB execute SELECT:

SELECT * FROM v1;

2. On remote (more...)

Battling Bigfile Backup Bottlenecks

Last Friday I kicked off a database backup to an NFS destination, using the standard "backup as compressed backupset database" syntax. Loyal readers of this blog may recall that I'm the proud custodian of a 25 Tb database, so this backup normally takes a few days, with an expected completion on Monday morning. However it was still running on Wednesday, and reviewing the logs I saw that there was just 1 channel (of the original (more...)

Why Is My MView Log Not Purging?

A few weeks ago we saw one of our tablespaces growing at a rate much higher than the others. Taking a look we saw that the biggest users of space were two materialized view logs, one being 110 Gb and the other 60 Gb. These logs were in place to facilitate the fast refresh of two materialized views, one for each log/table. These materialized views did some aggregations (sum) throughout the day on some important (more...)

An example of where the optimizer should ignore a hint

Here’s an example of where the optimizer really should ignore a hint.

I’m testing an implementation of deliberately unusable index partitions – i.e. some empty index partitions, some usable.

This is 11.2.0.3 so whilst there are enhancements in 12c, they’re no use here.

SQL> create table t1
  2  (col1 number)
  3  partition by list(col1)
  4  (partition p0 values(0),
  5   partition pdef values(default));

Table created.

SQL> create index i1 on t1 (col1)  (more...)

What to Expect When You’re Changing the DB_UNIQUE_NAME

I recently had to change the db_unique_name of a database to make it jive with our typical database/DataGuard naming policy of appending the datacenter location. For the sake of this post let's say it was changed from ORCL to ORCL_NYC, since this database is in our fictional New York City datacenter.

I did a quick set of tests and thought I'd share the findings to save anyone any unpleasant surprises. Here are the things to (more...)

Oracle Direct NFS and Infiniband: A Less-Than-Perfect Match

Readers of an earlier post on this blog will know about my latest forays into the world of Direct NFS. Part of that means stumbling over configuration hiccups or slamming into brick walls when you find new bugs.

To quickly re-set the table, my organization purchased the Oracle ZFS Storage Appliance (ZFSSA) 7420. Oracle sold us on the Infiniband connectivity as a way to make a possible future transition to Exadata easier. However the pre-sales (more...)

The Danger of Moving Incrementally Updated Datafile Copies

When I sat down at my desk yesterday morning I was greeted with some disturbing email alerts notifying me that one of the NFS mounts on my standby database host was full. This was the NFS mount that held an image copy of my database that is updated daily from an incremental backup. The concept and an example can be found in the documentation. With a 25Tb database, waiting to restore from backups is not (more...)

Simple Issue with ORA-00108

My friend asked me to check about error in alert log file - "dispatcher 'D000' encountered error getting listening address". After checked, I found.
Wed Feb 12 09:46:27 2014
dispatcher 'D000' encountered error getting listening address
Wed Feb 12 09:46:27 2014
found dead dispatcher 'D000', pid = (17, 154)
I checked trace file about d000 processed.
Trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_d000_31988.trc
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
ORACLE_HOME (more...)

Extension Bypassed Because of Missing Histogram

Today, while tuning a fairly complex query experiencing wrong cardinality estimates, I noticed something I was not aware of. Hence, I thought to write this short post to illustrate how to reproduce the problem I experienced…

  • Create the test table (notice the correlation between the data of the two columns):
SQL> CREATE TABLE t
  2  AS
  3  SELECT mod(rownum,50) AS n1, mod(rownum,50) AS n2
  4  FROM dual
  5  CONNECT BY level < = 1000;

AWR: Was a baselined plan used?

Sometimes a simple question turns out to be harder than expected.

“Can we see if a particular SQL execution in AWR used a baselined plan?”

Initial thoughts might be:

Q: Does DBMS_XPLAN.DISPLAY_AWR tell us this?
A: Apparently not. See below. This question could also be rephrased as two other possible questions:

Q:Isn’t there a column on DBA_HIST_SQLSTAT which tell us this?
A: No. You’d think there should be. There is a SQL_PROFILE column. (more...)

Learn a bit Oracle Scheduler with BROKEN state

On Oracle Database, DBAs can check broken job for Oracle Job (dbms_job) at *_JOBS.BROKEN column. Anyway, DBAs have changed from DBMS_JOB to DBMS_SCHEDULER. So, I was curious How to check broken job for Oracle Scheduler (DBMS_SCHEDULER)? After found out... DBAs can check on *_SCHEDULER_JOBS.STATE column.

STATEVARCHAR2(15)Current state of the job:
  • DISABLED
  • RETRY SCHEDULED
  • SCHEDULED
  • RUNNING
  • COMPLETED
  • BROKEN
  • FAILED
  • REMOTE
  • SUCCEEDED
  • CHAIN_STALLED

When does Oracle Scheduler change STATE to be BROKEN?
Then, (more...)

Plan change using load_plans_from_cursor_cache

This post is more of a note for myself and might be helpful to few other. Assuming db is 11gR2 and baselines/spm is used. When a new query is introduced in db, it might be that it runs with the good plan, but sometimes it picks up wrong plan. It could be that Index Range [&hellip

My Latin American Tour in March

At March I will be presenting my very successful seminar “Mastering Backup and Recovery” in some countries of Latin America for the very first time. Thank you Panama, Chile and Brazil OUGs for inviting me to your amazing countries! Please, use the following links for registration and also to find more information about the seminar: […]

Join me at the next OTN Virtual Developer Day

    Hi All, On February 4, 2014 at 9:30 am PT I will be talking on the next OTN Virtual Developer Day about Oracle VM and Oracle Database. Come and discover the answers for the following questions: Does an Oracle Database perform well on a virtualized environment? What virtualization technology is more stable and […]

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

Oracle Direct NFS (and Linux Routing) … for Dummies!

When I started my current role a few months ago, I was very interested to learn that direction had been set to migrate away from ASM and onto NFS storage that had some read flash cache in front of it. I'm not the world's biggest fan of ASM and the (more...)

View Data Volume Estimates

When the optimizer has to estimate the data volume (the BYTES column in the plan output), it usually bases this information on the column statistics, if applicable and available (think of complex expressions).However, whenever there is a VIEW operator in an execution plan, that represents an unmerged view, the optimizer obviously "loses" this information and starts applying defaults that are based on the column definition.Depending on the actual content of the columns (more...)