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

Join cardinality

In my previous post I showed an example of how a query’s performance can be improved using the waste minimization technique. My focus was primarily on identifying and enforcing the correct plan, but I received some questions regarding the root cause of the problem: why the optimizer came up with a wrong join order? It’s a very interesting question, and it deserves a separate post so that it could be explored in detail.

Let’s take (more...)

SELinux blocked my .Xauthority

I was attempting to install an OEM management server on a new host in the lab using runInstaller.  Of course the installer is an X-windows app so I need to configure port forwarding to get the display back to MacBook.

I added the new host and its bastion to my ~/.ssh/config file to set up port forwarding:

Host 10.123.45.678
ConnectTimeout 60
StrictHostKeyChecking ask
ProxyCommand none
UserKnownHostsFile ~/.ssh/known_Hosts
User oracle

Host newlaboms. (more...)

Subtleties – Part 3 (more workarounds for COLLECT DISTINCT in PL/SQL)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL, and saw two workarounds – using dynamic SQL and using the SET function.
In Part 2 we saw that the SET function can operate on Nested Table but not on Varray.
In this post we’ll see two more workarounds.

Inline View

We can first remove the duplicates in an inline view, and then use the (more...)

AWR analysis: another case study

A few weeks ago, I received a request to review an AWR report for a database suffering from instance-level performance issues. Here are the the key parts of that report (with some masking):

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
XXXX           XXXXX     XXXXX               1 10.2.0.5.0  NO  XXXX

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
 (more...)

Moving to Seattle! Our beautiful rental available in San Francisco

27552681126_8d52a09180_z

More news coming, but for now, we (family and I) are moving to Seattle! We will be giving up our gorgeous rental house in San Francisco thus it will be available to the next lucky family.

Elegant mediterranean style family home on a quiet tree lined street in a sweet residential neighborhood just minutes from downtown and the Peninsula. 

Photos of house

.

Perfect location for commuting to Peninsula on 280  or downtown SF (more...)

OEM Preferred Connect Strings

You can specify a connection string for database targets in two ways:  fill in the blanks and let OEM create the connection string (my preference) or paste a Preferred Connect String on the bottom of the page.

dedicatedserver_connectstring01

Our default setting in our shop is to use multi-threaded servers. On the rare occasion when we need to change if, we paste the connect string from tnsnames.ora in place and add the SERVER=DEDICATED parameter.

Some things to (more...)

Dropping constraint… what about the index?

In a recent release that I’ve been working on, we had to drop and recreate some of the unique/primary key constraints (due to columns being added into the constraint) on some of our tables.

Prior to working in this team, I had only ever come across explicitly creating the indexes for a table, similar to:

create table test_explicit_index (col1 number,
                                  col2 varchar2(30),
                                  col3 date);

create unique index col1_exp_pk on test_explicit_index (col1);

We can see the (more...)

Moving from PeopleSoft to the Oracle Cloud

Those of you who know me will know that I’m working at Cedar and we’re moving from PeopleSoft to the Oracle Cloud. I won’t talk about the company’s journey, but more how I’ve found the transition personally.

In brief, there’s a lot to learn.

Data Model knowledge

In the PeopleSoft world, once you know the table structures you could work significantly faster. Whether you were writing an SQR, an App Engine, an XMLP report, a (more...)

Oracle Audit Control part 3 – OS files

Just realized that I wrote a follow-up to Part 2 2 years ago and never posted it! So here’s the stripped-out key facts to round off auditing control a bit more:

Deleting O/S audit files is, like, totally necessary. In Oracle 12C (12.1), they are created by default as AUDIT_SYS_OPERATIONS defaults to TRUE now, so you are going to be filling up your AUDIT_FILE_DEST whether you realized it or not.

[NOTE: In theory, this (more...)

Hints

Oracle cost-based optimizer (CBO) is great, but sometimes it’s making wrong choices even when correct inputs are fed to it. In such cases, you need a tool to override CBOs choices, and one of the most popular tools is optimizer hints. The main reason they’re so popular is that they allow “quick-and-dirty” kind of fixes for performance issues (provided that query text can be altered). Other ways may be more reliable, but generally require more (more...)

Oracle Forms Extreme Mobile Makeover Contest

 

WebSite_main_image

In today’s world, where everything is at the tip of your fingers, why are your enterprise systems glued to your office desk?  Imagine if you could access and operate your Oracle Forms/EBS systems from any location.  What if your Oracle Forms system was so agile, so easy to adapt, that any business function you do in Oracle Forms could be included in a mobile application? What would you include in your mobile (more...)

Evolution of Masking Vendors

Screen Shot 2016-04-22 at 12.47.52 PM

Masking with Delphix (where duplicate blocks are shared making a new copy almost free storage wise and almost instantaneous) has 4 big advantages

  1. Instant data, no copying
  2. Ease of Use
  3. Consistent across data centers and databases vendors
  4. Master/Slave

Virtual Data Masking

Delphix masking and virtualization is the most advanced solution in the market place, because Delphix doesn’t provision data. Instead of provisioning data, Delphix sets up pointers back to existing data for a new clone. When that (more...)

Workarounds

It’s been forever since I last shared any of my performance troubleshooting experiences at work. This week, I got a case that I think is worth publishing, and I decided to write about it in my blog. So, here we go…

A few days ago, I received a complaint about unstable performance of one of frequently running SQL reports on a 11gR2 database. Most of the time it completed within a couple of minutes, however, on (more...)

Delphix Replication

Someone just asked on the Delphix Forums whether they could test Delphix replication with the free version of Delphix called Delphix Express.

I’d never tried, so I sat down to try and was amazed at how easy it was.

I created twoDelphix Express installations.

On one engine, the source engine, (http://172.16.103.16/)  I linked to an Oracle 11.1.0.7 database on Solaris Sparc called “yesky”.

On that same engine I (more...)

Partially Applied Functions in JavaScript

First of all, what are partially applied functions? There’s a little bit of theory here. The concept of partially applying functions stems from the functional way of programming. There it’s tightly linked with a concept called currying. Currying says that every function can be written as a function with only one parameter. By currying and un-currying you can achieve this goal. If this sounds very hard and complex, do not fear, in essence it’s quite (more...)

Inflexible Security (MailChimp fail)

Maybe I shouldn’t have written about flexible security, because I immediately starting hitting inflexible security, locking me out.

Today’s fail is courtesy of MailChimp.com, which I use for my newsletters. It’s OK that they decided they want a confirmation when I log on to my account from India, but it is not OK that they require a text message passcode with no other option.

I have my phone in flight mode, because I don’t (more...)

Force OEM to Discover HA Systems

You know there’s a cluster and that means that an HA system exists, but OEM won’t discover it.  Of course that means that your RAC databases can’t be discovered either and all of this is making you cranky.  Take a deep breath.  We can fix this.

The agent can only recognize an HA system when it knows of the clusterware home.  For some reason the agents weren’t aware of the CRS_HOME on some of our (more...)

New APEX training in Vienna – APEX Debugging and Performance

Just a quick note: I’ll be holding a 1-day APEX training class on April 21st in Vienna, Austria.

In this training I’ll teach you how to debug your APEX applications, how to find problems and bugs and how to speed up your application.

Interested? Go here and sign up: http://www.aoug.at/Event/413

Resolve ‘Device or resource busy’ problems

Upgrading opatch utility consists of two steps

  1. Remove the old copy of opatch by deleting $OH/OPatch directory
  2. Unzip the replacement file into your Oracle home directory

But when you try to remove the contents of OPatch directory you get slammed with errors like this:

rm: cannot remove `jlib/.nfs0000000005213282002649ad’: Device or resource busy
rm: cannot remove `jlib/.nfs000000000523b2e4002649ae’: Device or resource busy


rm: cannot remove `ocm/lib/.nfs0000000005226a87002649b3′: Device or resource busy
rm: cannot remove (more...)