I may have said it before but I consider presenting and teaching a great way to expand one’s knowledge: first of all it requires me to really understand a subject. Secondly, when presenting, you get lots of interesting questions that can turn into blog posts like this one.
Lately I have been asked about the impact of synchronous log shipping to a physical standby database. I was sure there was an effect to be observed, depending most likely on the network latency between systems but I didn’t have any evidence I could pull out of the hat to back up my thoughts. So what better than trying! I also read that some of the events have changed in 12c, and wanted to make them visible. My environment is based on the 2 node RAC primary/2 node RAC standby configuration I wrote about in my previous posts.
Since their initial setup I upgraded the cluster to 18.104.22.168.170117 for Clusterware and RDBMS.
This question came in on AskTom, yielding a very interesting result when it comes to DDL triggers. To set the scene, I’ll first create a table called T which is just a copy of SCOTT.EMP
SQL> create table scott.t as select * from scott.emp; Table created. SQL> desc scott.t Name Null?
Wow, has it really been fourteen years since I started PeteFinnigan.com Limited? - Time has gone so fast and business is getting better and better. We have great customers, great Oracle Security trainings and consulting projects meeting new people and....[Read More]
Posted by Pete On 23/02/17 At 06:33 PM
As a heterogenous company, I get a lot of questions on what database platforms are important, why and if we should invest in them. When looking at some of the lists and some of the marketing, it can be a bit overwhelming.
We’ve been working hard to create an incredible new trial version of Delphix that uses AWS, which is built with the open source product Terraform. Terraform is a tool that anyone can use to build, version and manage a product effectively and seamlessly in a number of clouds.
Recently I’ve seen not so smart optimizer behavior: one query took long time to parse, and ended with an error hitting PGA_AGGREGATE_LIMIT in few minutes; another query was just parsed for ages while using reasonable (under 2G :)) amount of PGA and still could hit PGA_AGGREGATE_LIMIT but after way more time – up to an hour.
Both cases were similar and involved queries which were accessing views; and those views’ code is generated by an application using lots of IN LISTs and other OR conditions. They both are really ugly SQLs with text length ~100K. When Oracle tried to parse them it took a lot of time and parse attempt had either failed with ORA-4036 soon or hanged for a long time and then failed. Strangely incident trace file generated for ORA-4036 doesn’t include PGA heaps breakdown and you have to manually enable PGA heapdump on error to get an idea what is taking up memory. Here’s what I’ve found in there:
Here’s a very simple example of a table called PARENT being a (surprise surprise) parent in a referential integrity relationship to a (drum roll for my choice of name) CHILD table
SQL> create table parent ( p int, constraint PAR_PK primary key (p) ); Table created. SQL> create table child ( c int, 2 p int 3 ); Table created. SQL> alter table child add constraint fk1 foreign key ( p ) references parent ( p ); Table altered.
That is all as we would expect, and similarly, if I inadvertently try to add the same foreign key constraint, I’ll get an error
A couple of people have requested that I explain how to install the entire random_ninja and testdata_ninja packages manually. I have created a gist here with the full and complete order of the files: Full install list. So download random_ninja zipfile and testdata_ninja zipfile and follow the order of the gist to install all the code.
Colleague Jeff Smith published an interesting post the other day about his “rules and regulations” for blogging, but the overriding theme (Ed: – this is my opinion, I’m not speaking for Jeff) was that the “what” he blogs about was – anything he’s passionate about, and the “when” was – whenever felt inspired to do so.
That got me thinking about blogging in general. I think it is safe to say
(Because not to do so means you’re in the wrong career)