A comparison of sucking data into a table over a db link using DBMS_PARALLEL_EXECUTE.
This particular example is based on something I needed to do in the real world, copying data from one database into another over a db link. Datapump is not available to me. Tables in question happen to be partitioned by a date-like number (boo!) hence some of the specific actions in the detail.
I think it’s a good example of (more...)
There seems to me to be a relatively simple choice.
Either you except that the Oracle Optimizer has a wealth of complicated strategies and, in this complex effort to get the best executions it can, will inevitably either get it wrong sometimes (or take some extra executions to realise it’s wrong).
Or you stick your head in the sand and raise a bug for every unexpected poor execution or plan flip.
But let’s say that (more...)
A quick fly through the options for conditional uniqueness.
Requirement #1: I want uniqueness on a column but only under certain conditions.
For example, I have an active flag and I want to make sure there is only one active record for a particular attribute but there can be many inactive rows.
create table t1
(col1 number not null
,col2 varchar2(24) not null
,is_active number(1) not null
,constraint pk_t1 primary key (col1)
According to http://en.wikipedia.org/wiki/Perfect_storm:
A “perfect storm” is an expression that describes an event where a rare combination of circumstances will aggravate a situation drastically.
The other day we had a bit of a panic when AUD$ space grew by 45GB in 15 minutes and set off some alerts.
The audit_trail parameter was set to DB, EXTENDED.
Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text (more...)
Is this an appropriate response?
Others have raised this before – in fact I think I’ve raised the question before – but my initial search on Oracle Support did not turn up an answer so I thought I would raise a question again.
Question only: Null predicates in DBA_HIST_SQL_PLAN
Everyone knows that the FILTER_PREDICATES and ACCESS_PREDICATES columns in DBA_HIST_SQL_PLAN are not populated.
If I remember correctly, this was originally (more...)
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...)
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)
create table t1
create or replace view v1
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...)
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 22.214.171.124 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));
SQL> create index i1 on t1 (col1) (more...)
Just a quick note to highlight that the Oracle pre-built Developer VMs have been updated for Oracle 12c.
Oracle pre-built Developer VMs
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...)