Join performance in MongoDB 3.2 using $lookup

One of the key tenants of MongoDB schema design is to account for the absence of server-side joins.  Data is joined all the time inside of application code of course, but traditionally there’s been no way to perform joins within the server itself. 

This changed in 3.2 with the introduction of the $lookup operator within the aggregation framework.  $lookup performs the equivalent of a left outer join – eg: it retrieves (more...)

LAG / LEAD quick tip

As most of us know, with LAG and LEAD or more generally, any analytic function that may extend “past” the boundary of window it is operating on, you can get null as a result.

Here’s a trivial example


SQL> create table t as
  2  select rownum x
  3  from dual
  4  connect by level <= 10;

Table created.

SQL>
SQL> select x, lag(x) over ( order by x ) as lag_Test
  2  from t;

         X    (more...)

Direct mode operations on IOT’s

An AskTom contributor brought to my attention, that direct mode insert on index organized tables now appears possible in 12c.  We can see the difference by running a simple script in both v11 and v12


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for  (more...)

Spark SQL and Oracle Database Integration

I’ve been meaning to write about Apache Spark for quite some time now – I’ve been working with a few of my customers and I find this framework powerful, practical, and useful for a lot of big data usages. For those of you who don’t know about Apache Spark, here is a short introduction.

Apache Spark is a framework for distributed calculation and handling of big data. Like Hadoop, it uses a clustered environment in (more...)

Happy birthday to me!

birthday-cake-clipartIt’s that time again. Another year older and unfortunately not another year wiser. I still keep reliving those same mistakes again and again. I guess the old adage, “You can’t teach an old dog new tricks”, is true. 🙂

I’m sure I’ve said this before, but the plans for this next year are “to do me” a bit more. That might sound funny to people who know me, as I am incredibly self-centred, but I keep (more...)

Oracle Tidbits – June 2016

“Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in June 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use… TidBit #oratidbit The Oracle #APEX […]

Friday Philosophy – Brexit & the Misplaced Blame Culture.

This is not going to be a rant about Brexit and how the selfish, stupid and simply fearful were led by a jingoistic & deceitful bunch of career politicians to show the worst side of the UK. Well, maybe a bit… It’s more about something that struck me about Brexit in respect of who is to “blame”. And there are aspects of this that are echoed in our own industry.

I feel that there is (more...)

Oracle Public Cloud User Experience Issues

For some time I’ve been openly critical of the user experience (UX) of Oracle Public Cloud. Just to be clear what I mean by this…

  • I am not talking about the quality of the services that are delivered, or the underlying technologies being used. I’m talking about the day-to-day usage of the Oracle Public Cloud (OPC) interface. The web pages you use to administer this stuff.
  • I’m not talking about the SaaS offerings, like Oracle (more...)

Understanding CPU on AIX Power SMT Systems

This month I worked with a chicagoland company to improve performance for eBusiness Suite on AIX. I’ve worked with databases running on AIX a number of times over the years now. Nevertheless, I got thrown for a loop this week.

TLDR: In the end, it came down to a fundamental change in resource accounting that IBM introduced with the POWER7 processor in 2010. The bottom line is twofold:

  1. if SMT is enabled then the meaning (more...)

Believe it or Not: Converting an Inner Join to an Outer Join to improve performance

The cost-based optimizer tries to merge views whenever possible but sometimes we ma y want to override this behavior; that is, we may want the optimizer to push predicates into the view instead of merging the view into the main query. If the main query performs an inner join to the view, it becomes necessary to convert the inner join to an outer join if the database version is less than 12.1.0.2.(read more)

Turn OEM Job Output into Lists

Context

We have an OEM job that we run after each system-wide Unix password change to verify success on all hosts.

The OEM Job is run against Dynamic Groups of hosts.  In this example I’ve limited it to the group DBHosts.

check_pwd_job2

The job consists of a very simple call for id

check_pwd_job01

The Credentials for the job contain the new password, of course.

The job quickly tries to connect to each host with that named named (more...)

Happy Hinting: Undocumented PUSH_PRED hint variation

There isn’t a documented method of controlling which specific predicate to push.; it appears that the decision is left to the cost-based optimizer. However there is an undocumented method. Both the PUSH_PRED and NO_PUSH_PRED hints accept an optional second parameter(read more)

The Oracle Database professional I admire most

Carlos Sierra spent 17 years in the SQL performance group of Oracle Support. He couldn’t move a mountain nor pull down a big oak tree. But Carlos Sierra became a mighty big man by putting all the knowledge he gained during that time into a wonderful tool called “SQLT.”(read more)

Point to Ponder: The most important insight about Oracle Database upgrades?

Someone recently gave me an insight with which you would probably agree with: If query performance is satisfactory before and after the upgrade, then business users are unlikely to complain if new optimizer features are not being used after the upgrade.(read more)

Intro to Database Security – Part 5

In the previous parts I talked about security of the application and infrastructure layer of database security. This will be the last post in the series and I want to dedicated it to a very important topic that is not a database only issue – passwords. Links to the previous posts: Part 1 – general […]

Join predicate pushdown

When a query contains a regular or inline view, there are 3 basic strategies for the optimizer to choose from:

1) merge the view (no “VIEW” operation in the plan)
2) instantiate the view as the whole and join it to the rest of the query (the plan shows a VIEW “operation”)
3) push join predicates inside the view (the plan shows “VIEW PUSHED PREDICATE”).

The last strategy in that list is similar to a (more...)

Good bye Quest!

You may have read that Francisco Partners and Elliott Management have entered into an agreement to  Acquire the Dell Software Group – largely composed of the Quest software company bought from Dell in 2012 .   I’ve worked at Quest since 1998, but alas I will not be participating in this next stage of the Quest journey.
Although the timing of the announcement was influenced by the logistics of this sale, it is actually a (more...)

Tales from the field: potential reasons for PDB plug-in violations part 1

Container Databases have been an area that I have researched intensively over the past years. With this post (and hopefully some others that follow) I would like to demonstrate some of the new situations the DBA might be confronted with. Please don’t use this post to give the new 12c architecture a hard time: standardised deployments (which I love) help you a lot. Not only do your DBA scripts work reliably everywhere, but the error condition (more...)

Expecting Sum-Of-Parts Performance From Shared Solid State Storage? I Didn’t Think So. Neither Should Exadata Customers. Here’s Why.

 

sum-of-parts

Last month I had the privilege of delivering the key note session to the quarterly gathering of Northern California Oracle User Group. My session was a set of vignettes in a theme regarding modern storage advancements. I was mistaken on how much time I had for the session so I skipped over a section about how we sometimes still expect systems performance to add up to a sum of its parts. This blog post (more...)

New Version Of XPLAN_ASH Utility

A new version 4.23 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version comes only with minor changes, see the change log below.

Here are the notes from the change log:

- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" (more...)