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)

Cloud Allergy – Clouds Security and Changing Notion

| Jun 29, 2016

With my recent role as CTO/Advisor with www.analytos.com, during most of my conversation with Analytics leaders within the company, all are concern over security. At a recent conversation with another entrepreneur friend, one of his solution was stalled due to SQL injection issue on the cloud ( a valid concern , but is it valid ?) .

During my recent startup sting, cloud Allergy word was coined, and it did make sense, because allergies do exist and (more...)

Why in Oracle BPM/SOA Suite Attaching a Fault Policy to a Synchronous Service Is Not a Good Idea

In this posting I will explain why in the Oracle BPM/SOA Suite you should not attach fault policies to synchronous services.

The other day I investigated some BPM process instance that had an unrecoverable error. It was calling a synchronous service, that on its turn was calling another synchronous service, that on its turn was calling a external, synchronous service exposed through the Oracle Service Bus. That latter call failed (due to a timeout). As (more...)

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

Using Adaptive Cursors Sharing with SQL Plan Baselines

We have several databases where automatic capturing of sql plan baselines is enabled for a few schemas.

Execution of some queries deeply depend on variables where is not always the best to reuse same execution plan for all executions. For those queries I want to avoid using literals and inefficient execution plans. Also, I want to use SQL plan baselines as I have automatic capturing enabled.

Question is, can I make Adaptive Cursor Sharing to (more...)

ORA-27102: out of memory while starting DB instance

While starting DB instance below error coming because of less memory allocated to oracle user at OS level.

ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 6291456000

 

If Oracle DB version is 12.1.0.2 or later use below workaround to resolve the eorror.

1. Upgrade the OS to at least Oracle Solaris 11.1.19.6.0 release (or greater).

OR

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

Programming for Style and Beautiful Black Boxes – is Fast Performance an Afterthought?

Uncategorized
| Jun 24, 2016
June 24, 2016 I have been working with an Enterprise Resource Planning (ERP) system platform for a bit over 16 years.  Through various methods of observation, including the use of 10046 extended Oracle SQL traces, Process Monitor traces, Wireshark traces, and just general observation of the various components of the ERP system, I noticed a […]

Kill DB sessions the easy way with SQLcl

Seems I can not tweet these animated GIFs anymore. So this is another very short blog post to show real case for adding a new command into SQLcl which can be downloaded here. It's hard annoying tedious to find SID and SESSION then alter to kill that when needed.  What if there was a simple kill command. Here a sample one that takes in the 2 options:   kill sid serial#   Example :   kill (more...)

Query tuning by waste minimization: a real-life example

Today I’d like to share another tuning example from a recent case at work, which in my opinion is good for illustrating typical steps involved in SQL optimization process.

I was handed a poorly performing query with a relatively verbose text, so I will only give the general structure here (it will also prevent me from accidentally disclosing some sensitive information from that application):

SELECT 
       /* long list of columns here */
       ,
	   (select *  (more...)

With a Modern Storage Infrastructure, Companies Must Find an Excellent Data Management Tool

One of the more “weighty” questions within the IT world is in reference to the value of each company’s particular data.  Many wonder what the true value of protected data is in the long-run, eventually view it as a cost center where money continuously gets used up.  In order to make data work in the favor of a business and to help generate some income, companies must get smarter with their approaches to (more...)

HOWTO solve any problem recursively, PL/SQL edition…

PROCEDURE solve (my_problem IN varchar2) IS
BEGIN
  my_idea := have_great_idea (my_problem) ;
  my_code := start_coding (my_idea) ;
  IF i_hit_complications (my_idea)
  THEN 
    new_problem := the_complications (my_idea);
    solve (new_problem);
  ELSE
    NULL; --we will never get here
  END IF;
END solve;

This abuse of recursion was inspired by @ThePracticalDev !

What’s in a name? – “Brittany” edition

How do you spell “Brittany”? The picture above has four well-known women with four different spellings of the name. It turns out there are nearly 100 different ways that Americans have spelled it. The US Social Security Administration names data lets us tease out all the spellings and find out which ones are most popular – and when.

Here’s how Americans have spelled “Brittany” each year. This is a graph of SSA applications for each (more...)

APEX UT – Display Image in a List

If using universal theme, we can switch from a classic navigation bar to a list and display it where usually a navigation bar is displayes - right top of the page. The problem with the list is that it will escape any html code in the list name. If we want to display an image, we will get the html code. To go arround that problem, we can use the "user defined attributes" for the (more...)

Blog retrospective

Not long ago I suddenly realized I had published my 100th blog post, so I figure it's time to look back and write down some thoughts about the blog.

I started this blog in 2008, so it has taken me all of 8 years to produce one hundred posts. Not exactly a huge output when compared to many other bloggers, but at least the pace has been reasonably consistent over the years, and hopefully most (more...)

SQLcl and Query Change Notification

The database has had Query Change Notification for a while but to use it required a custom program. Such as Tim outlines on his blog https://oracle-base.com/articles/10g/dbms_change_notification_10gR2 Since SQLcl has Nashorn, now it can be integrated with a few lines of javascript to get notified when changes happen.  The script is below and posted.  The catch is QCN only works on Varchars and