The Hitchhiker’s Guide to the EXPLAIN PLAN Part 31: How to Avoid a Salted Banana

In the February 2015 issue of the NoCOUG Journal, Lothar Flatz introduces the term “salted banana” for a certain kind of problem in SQL tuning.(read more)

Deploying Application Express with Delphix

VDBs

Seamless cloning of an application stack is an outstanding goal. Seamless cloning of an application stack including the full production database, application server, and webserver in a few minutes with next to zero disk space used or configuration required is the best goal since Alexander Graham Bell decided he wanted a better way tell Mr. Watson to “come here.”

So in the spirit of discovery, I’ve installed Oracle REST Data Services (ORDS) 2.0 (more...)

SQLCl – LDAP anyone?

since  we released our first preview of SDSQL, we've made  a lot of changes to it and enhanced a lot of things too in there so it would be more useable.  One specific one was the use of LDAP which some customers on SQLDeveloper are using in their organisations as a standard and our first release precluded them from working with this.

Well, to add this, we wanted a way that we could specify the (more...)

Oracle on GitHub

There has been a lot of activity on https://github.com/oracle lately. Apparently a place to keep…

New Year, New Name, Same Mission

Happy New Year! Looks like it will be another busy one. So, let’s get going! Yes, I just changed the name of my blog from “Oracle Data Warrior” to “The Data Warrior.” Why? Well, for one, my company name is Data Warrior LLC, so it seemed to make sense. Plus over the last few years […]

Patch Now!

So two guys walk into a bar.  They're both talking like pirates and dressed like pirates.  The bartender asks...




No, wait, that's not the kind of patch we're talking about today.  Sorry, folks.  We're talking about a patch for your software...a fix or an update for software you already have in place.  Did you know Oracle puts out Critical Patch Updates ("CPU"s) for all their project on a regular basis?  You can read about (more...)

Partition gotchas

Two minor partition gotchas on partition extent sizing and on the number of partitions in an interval partitioned.

First – old news – note that initial extent size for partitioned tables changed in 11.2.0.2.
Somehow this passed me by and I was late to the large extent party – duh!
Influenced by parameter _partition_large_extents, the default was changed to 8MB rather than 64K for autoallocated ASSM extent sizing.

Pre 11.2. (more...)

LOB Space

Following on from a recent “check the space” posting, here’s another case of the code not reporting what you thought it would, prompted by a question on the OTN database forum about a huge space discrepancy in LOBs.

There’s a fairly well-known package called dbms_space that can give you a fairly good idea of the space used by a segment stored in a tablespace that’s using automatic segment space management. But what can you think when a (more...)

Cluster Cache Coherency in EM12c

January is winding down and RMOUG Training Days 2015 is just around the corner, taking up much of my after work hours.

With that, we are going to discuss a great performance console in the EM12c cloud control-  Cluster Cache Coherency.

Cluster Cache Coherency

Optimization for an Oracle Real Application Cluster, (RAC) can be a daunting tasks for those that aren’t familiar with some of the most common issues to look for.  Although many (more...)

How to Set Axis Max Value of AnyChart in APEX Dynamically

I just created a one-page APEX application to show performance chart of our main production server based on Active Session History data. We publish it on a big monitor so we can see the performance chart during the day. It’s not a big deal but it’s really useful. I used 2D stacked column chart and make the chart simple (contains only 3 colors: green for CPU, blue for user IO and orange for all other (more...)

Oracle Database 12c Patching: DBMS_QOPATCH, OPATCH_XML_INV, and datapatch

Background

Oracle Database 12c brings us many new features including: the long needed ability to run OPatch and query the patch software installed in the Oracle Home programatically through SQL using the new DBMS_QOPATCH package.

If you’re a DBA working in an environment where patching consistently among databases is important, this is a welcomed enhancement. Sometimes one-off critical bug fix patches are important in the environment. Other times regular and consistent application of the quarterly (more...)

Enterprise Apps on SaaS

It's really, really early on a Monday morning as I write this:  "zero dark-thirty".  Some ideas just refuse to allow sleep until you share them.  So, while shivering in the pre-dawn cold, I offer the following for your consideration...

Picture if you will, just for a moment, the idea that your enterprise applications system is like an iceberg.  There are the system costs that you can see.  Then there are the costs below the water (more...)

Bitmap Counts

In an earlier post (not very serious) post about count(*) I pointed out how the optimizer sometimes does a redundant bitmap conversion to rowid when counting. In the basic count(*) example I showed this wasn’t a realistic issue unless you had set cursor_sharing to force (or the now-deprecated similar). There are, however, some cases where the optimizer can do this in more realistic circumstances and this posting models a scenario I came across a few (more...)

PL/SQL Fall Through?

Somebody wants to know how you can write a PL/SQL solution that mimics the fall through of a switch statement because PL/SQL doesn’t support a switch statement. It’s a question that I found interesting because there wasn’t a need for it when I figured out what he wanted to accomplish. Essentially, he wanted to know how to implement a nested loop where the first loop runs in ascending order and the nested loop runs in (more...)

Spatial space

One thing you (ought to) learn very early on in an Oracle career is that there are always cases you haven’t previously considered. It’s a feature that is frequently the downfall of “I found it on the internet” SQL.  Here’s one (heavily paraphrased) example that appeared on the OTN database forum a few days ago:

select table_name,round((blocks*8),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';

select table_name,round((num_rows*avg_row_len/1024),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';

The result from (more...)

Pulling Large Database tables in R

As the volume of the data in your tables grows, particularly in the big data world, you may run into some memory issues or package restrictions with pulling down the tables to your R environment.

Some of the R packages and drivers have some recommended numbers or limits for the number of records that can be fetched.

In the following example I'm looking at downloading a table with 300K records from an Oracle Database. I've (more...)

runInstaller fails at CreateOUIProcess with permission denied

Just a short post on a problem I encountered recently.

I had to install 11.2 GI and right after running the installer I got a message saying permission denied. Below is the exact error:

[oracle@testdb grid]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /u01/software/grid/response/grid_install_20140114.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 7507 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 8191 MB     (more...)

Convert to SQL Server?

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table (more...)

Primary on FileSystem and Standby on ASM

For one of the client, standby server went down. We had another standby server which was kept down for more than a month. Decision was taken to start the server and apply incremental SCN based backup on the standby database.

The standby was on ASM and the Primary on filesystem.Incremental backup was started from the SCN reported by below query

select min(fhscn) from x$kcvfh;

Once the backup completed, it was transferred to standby, standby (more...)

Bind Effects

A couple of days ago I highlighted an optimizer anomaly caused by the presence of an index with a descending column. This was a minor (unrelated) detail that appeared in a problem on OTN where the optimizer was using an index FULL scan when someone was expecting to see an index RANGE scan. My earlier posting supplies the SQL to create the table and indexes I used to model the problem – and in this posting I’ll explain the problem and answer the (more...)