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

Using Database In-Memory Column Store with Complex Datatypes

From those who are interested, hereby my slide deck I used during UKOUG Tech14, regarding…

Upgrade OVM 3.2 to 3.3

Lately several people asked me how they should go about with upgrading their OVM 3.2.x environment to 3.3.x
They have the backend repository running on an Oracle Database, which is no longer supported. In release 3.3, only MySQL is supported as a backend repository database.
They were wondering how the migration process will take place.
Furthermore the documentation states that there is only one way to upgrade the OVM Server (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...)

2014 in review

Happy New Year!

So many things happened in the past six months, I really can’t tell how quickly the time passed. As people say the life is what happens to you while you are making plans for the future.

I wish I had the time to blog more in the past year and I plan to change this in the New Year!

2014 was really successful for me. I worked on some really interesting projects, (more...)

CRS Timezone

Interesting problem – database was running normally without any issues and added to OCR.  But the log file started reporting a different time for all activities – for example.

Database and server is showing a time 4:25 while alertlog is ahead of 5 hours @9:25!

<pre>
oracle@prod # tail alert_xxxxxxx.log

Recovery of Online Redo Log: Thread 2 Group 16 Seq 129057 Reading mem 0

  Mem# 0: +ORAINDEX1/prod/onlinelog/group_16.282.864560747

Mon Jan 12  (more...)

Resilience

re·sil·ience
rəˈzilyəns/
noun
noun: resilience; plural noun: resiliences
  1. 1
    the ability of a substance or object to spring back into shape; elasticity.
    "nylon is excellent in wearability and resilience"
  2. 2
    the capacity to recover quickly from difficulties; toughness.
    "the often remarkable resilience of so many British institutions"

My parents were children of the Great Depression here in the U.S.  The experience influenced how they approached their lives - stability over all (more...)

Cloud Control : 12.1.0.7 Plugin Patch

I applied some of the 12.1.0.7 plugin patches to our Cloud Control installation today. We already have Cloud Control 12.1.0.4 installed, so this was just a case of updating the plugins.

Here’s a quick overview of what I did.

  • Backup up everything! Database and file system.
  • Setup > Extensibility > Self Update
  • Download the latest versions of the plugins. The minimum you can really get away with is “Oracle Database (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...)

enq: SQ – contention

Yesterday, I have seen huge waits “enq SQ – contention’”  – in every snapshot there were thousands of waits.  But the fix was so simple!  Here is the root cause of the issue –

When you select from a sequence, the NEXTVAL generated from a the seq$ table if it is not cached.  If it is cached, it will be available in a memory structure and no need to generate the value (more...)

Oracle GoldenGate Processes – Part 4 – Replicat

The replicat process is the apply process within the Oracle GoldenGate environment.  The replicat is responsible for reading the remote trail files and applying the data found in cronilogical order.  This ensures that the data is applied in the same order it was captured.  

Until recently there was only one version of a replicat, that version was the classic version.  As of 12.1.2.0, there are now three distinct versions of a (more...)

Oracle GoldenGate Processes – Part 2 – Extract

The extract process of Oracle GoldenGate is used to perform change data capture from the source database.  The extract can be used to read the online transaction log (in Oracle the online redo logs) or the associated archive logs.  The data that is extracted from the source database is then placed into an trail file (another topic for a post) for shipping to the apply sided. 

To configure an extract process there needs to (more...)

Recommended Reading: Oracle Database 12c NUMA-Related Topics

This is a short post to recommend some recent blog posts by Nikolay Manchev and Bertrand Drouvot on the topic of Oracle Database 12c NUMA awareness.

Nikolay provides a very helpful overview on Linux Control Groups and how they are leveraged by Oracle Database 12c. Bertand Drouvot carried the topic a bit further by leveraging SLOB to assess the impact of NUMA remote memory on a cached Oracle Database workload. Yes, SLOB is very useful (more...)

Flashback Data Archive Enhancements in Oracle Database 12c

Having played around with Flashback Data Archive in 11g, I figured I would get through the 12c enhancements pretty quickly. I didn’t account for the fact I’m a donkey and can’t see the wood for the trees. Luckily, I know some people who aren’t stupid and they gave me a nudge in the right direction, allowing me to spot my silly mistake. Thanks Bjoern and Connor:)

So after lots of wasted time, here is the (more...)

Most Recent

There’s a thread on the OTN database forum at present asking for advice on optimising a query that’s trying to find “the most recent price” for a transaction given that each transaction is for a stock item on a given date, and each item has a history of prices where each historic price has an effective start date. This means the price for a transaction is the price as at the most recent date prior to (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...)

Performance Problems with Dynamic Statistics in Oracle 12c

I’ve been making some tests recently with the new Oracle 12.1.0.2 In-Memory option and have been faced with an unexpected  performance problem.  Here is a test case:

create table tst_1 as
with q as (select 1 from dual connect by level <= 100000)
select rownum id, 12345 val, mod(rownum,1000) ref_id  from q,q
where rownum <= 200000000;

Table created.

create table tst_2 as select rownum ref_id, lpad(rownum,10, 'a') name, rownum || 'a'  (more...)

Stopping one ASM listener in Flex ASM environment takes down ASM instance

Stopping one ASM listener in Flex ASM environment takes down ASM instance

This is a heads-up about behaviour we are seeing during OAT testing on a 12c environment. We are running OAT tests on a new 12.1.0.2 Grid Infrastructure environment using Flex ASM (OEL 5.8) and when taking down one of the two ASMNETLSNR listeners on one of the nodes, the ASM instance running on that node is taken down. We (more...)

Access Oracle GoldenGate JAgent XML from browser

There are many different ways of monitoirng Oracle GoldenGate; I have posted about many of these in earlier blog posts.  Additionally, I have talked about the different ways of monitoring Oracle GoldenGate at a few conferences as well.  (The slides can be found on my slideshare site if wanted).  In both my blog and presentations I highlight many different approaches; yet I forgot one that I think is really cool!  This one was shown to (more...)

Checking the alert log – the easy way

Do you check the alert log of your databases every day? In the morning when you get in? But what about the alerts which happen during the day? How do you spot them – especially if you don’t have Grid Control or Cloud Control configured. Even if you do have a full monitoring solution, this can be useful for a belt-and-braces approach.

Here’s a short bash shell script to use adrci to read through each ORACLE_HOME (more...)