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...)
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...)
I’ve just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in 18.104.22.168 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...)
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...)
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...)
I applied some of the 22.214.171.124 plugin patches to our Cloud Control installation today. We already have Cloud Control 126.96.36.199 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...)
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...)
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...)
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 188.8.131.52, there are now three distinct versions of a (more...)
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...)
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...)
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...)
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...)
sqlplus / as sysdba <<EOF
whenever sqlerror exit sql. (more...)
I’ve been making some tests recently with the new Oracle 184.108.40.206 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
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 220.127.116.11 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...)
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...)
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...)