Search

Top 60 Oracle Blogs

Recent comments

July 2015

Little things worth knowing: Data Guard Broker Setup changes in 12c

One of the problems I have seen when deploying Data Guard for systems such as RAC One Node and policy managed databases was the static listener configuration you needed in 11.2. This has changed with 12c for the better if you are using Grid Infrastructure.

http://docs.oracle.com/database/121/DGBKR/install.htm

In the section about static listener registration a little addendum can be found (thanks to Patrick Hurley/@phurley for pointing this out to me!):

“A static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used.”

This is good news, let’s put it to the test; I’m a great fan of Oracle Restart. If I ever find the time I’d like to repeat this test with clustered Grid Infrastructure. I think the quote mentioned earlier still stands true but I would like to see it with my own eyes.

Subquery Factoring (10)

What prompted me to write my previous note about subquerying was an upgrade to 12c, and a check that a few critical queries would not do something nasty on the upgrade. As ever it’s always interesting how many little oddities you can discover while looking closely as some little detail of how the optimizer works. Here’s an oddity that came up in the course of my playing around investigation in 12.1.0.2 – first some sample data:

Password Manager Woes

I read a post this morning and it hit a raw nerve or two.

As followers of the blog will know, I use KeePass for all my work and personal passwords. I’ve come across a number of sites that prevent pasting passwords for “security reasons” and it drives me nuts. Fortunately, most of the them can’t prevent the auto-type feature, so at least that’s something…

Learn it or don’t. The choice is yours.

glasses-272399_1280-smallTechnology is scary for a lot of people, but the biggest problem I see out there is denial (It’s not just a river in Africa! :) ).

Newbies

For people who are new to technology, the biggest problem I see is they refuse to actually read what is on the screen. I’m not talking about those stupid End User License Agreement (EULA) screens that nobody reads. I’m talking about basic instructions. If a screen says,

“Enter your username and password, then click the Login button.”

Things that are there but you cannot use

I did a "desc" command on the STANDARD package today, the package that helps define PL/SQL, and saw the XOR function!

SQL> declare
  2    x boolean;
  3  begin
  4    x := XOR(true,true);
  5    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
  6
  7    x := XOR(true,false);
  8    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
  9
 10    x := XOR(false,false);
 11    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
 12
 13  end;
 14  /
FALSE
TRUE
FALSE

PL/SQL procedure successfully completed.

I cant find any reference to it in the Oracle PL/SQL documentation anywhere, so its definitely not supported so using it is probably off limits until you see officially in the documentation.

SQL Text / View Expansion

One of the common issues faced when debugging or investigating SQL performance is that tools often do not not show the underlying objects at play when a query involves views.

Oracle 12c now provides a capability to display the underlying objects; DBMS_UTILITY.EXPAND_SQL_TEXT will provide the entire syntax of the”exploded” query as shown below:

Example EXPAND_SQL_TEXT – View Definition:

create view dept_emps
 as
 select dname,loc,d.deptno,
     empno,ename,job,hiredate,mgr,sal,comm
   from emp e join dept d
     on e.deptno = d.deptno
   order by dname,ename;

Example EXPAND_SQL_TEXT Use

Here’s a query using the view, unless you know the schema you might not realize multiple database objects were in use.

select * from dept_emps;

Here’s the PL/SQL you might use to “expand” the SQL text to show the objects really involved:

12c Parallel Execution New Features: Parallel FILTER Subquery Evaluation - Part 1: Introduction

12c introduces another interesting new Parallel Execution feature - the parallel evaluation of FILTER subqueries. In pre-12c FILTER subqueries always had to be evaluated in the Query Coordinator. This had several consequences, in particular the data driving the FILTER subquery always had to flow through the Query Coordinator, and hence represented a forced serial execution part of a parallel execution plan. This limitation also meant that depending on the overall plan shape the parallel plan was possibly decomposed into multiple DFO trees, leading to other side effects I've outlined in some of my other publications already.In 12c now the FILTER subquery can be evaluated in the Parallel Slaves, and the driving data no longer needs to be processed in the Query Coordinator. However, the resulting plan shape can be a little bit confusing.

OTN Tour of Latin America 2015 (Southern Leg)

ace-directorI put out a brief video a few days ago (re-uploaded today to fix typos) about my participation in the OTN Tour of Latin America (2015). I’ll be on the southern leg this year. Sorry to those countries who make up the northern leg. I will be back soon I hope.

Anyway, the southern leg of the tour shapes up like this.

  • 3/4 August Uruguay UYOUG
  • 5/6 August Argentina AROUG
  • 8 August Brazil GUOB
  • 10 August Chile CLOUG
  • 12 August Peru PEOUG

I’m looking forward to seeing everyone. See you soon!

After the Peru leg, the wife and I will be going off to see Machu Picchu.

Cheers

Karen Morton In Memoriam

 

Karen Morton

 

Photo

I got to know Karen a couple of years ago, while staying in the same 'house' at Lalandia, together with Cary, Tanel and Carel-Jan. A strong personality. Knowledgeable, present (in a good way), always willing to share.
And, like Lex, she was tall, with a lot of good stuff at the top-floor.
My deepest sympathies to her family.
Goodbye Karen, you will be missed.

Toon

In Memoriam

 In Memory of

 

Lex De Haan

 

Karen Morton