Little-Known Factors That Could Affect the Performance of AutoInvoice Process

AutoInvoice is one of those processes whose performance is really impacted by the way you setup the application. A small change in the System Options screen, can be the difference between this process completing in a few seconds or not completing at all.

Arguable, three of the most important settings you need to review and make sure they are correctly set, are the following:

  • System option: Log File Message Level
  • System option: Max Memory (in (more...)

Why oh Why Do Will Still Not Have a Fast Bulk “SQL*Unloader” Facility?

Way back in 2004 I was working at the UK side of the Human Genome project. We were creating a massive store of DNA sequences in an Oracle database (this was one of two world-wide available stores of this information, for free & open use by anyone {* see note!}). The database was, for back then, enormous at 5-6TB. And we knew it would approx double every 12 months (and it did, it was (more...)

Do you suffer from Storage Stockholm Syndrome?

The last year at DSSD (now a part of Dell EMC) has been an extremely interesting one for me, and I’ve learned a great deal, which is always good. Some of the lessons have been surprising, though… One of them is what I will rather dramatically refer to as Storage Stockholm Syndrome. Stockholm Syndrome is … Continue reading "Do you suffer from Storage Stockholm Syndrome?"

11 Tips to Improve Focus and Concentration at Work

Do you often find yourself not focusing at work? Or, you try to focus, but something else comes along and distracts you? I know how this feels. I’ve listed eleven ways to remove distractions and increase focus in this article. I’ve been able to improve my focus by removing some of the distractions I have […]

Delete/Insert

Many of the questions that appear on OTN are deceptively simple until you start thinking carefully about the implications; one such showed up a little while ago:

What i want to do is to delete rows from table where it matches condition upper(CATEGORY_DESCRIPTION) like ‘%BOOK%’.

At the same time i want these rows to be inserted into other table.

The first problem is this: how carefully does the requirement need to be stated before (more...)

cost vs. gets

Last week I hit an interesting performance issue:
A Table (T) has 2 index (of interest). One (IX_1) is only on column S, the other (IX_2) on (C, S).
The optimizer calculates the plan with IX_1 more expensive than the plan with IX_2 - as this should be more selective.
But the gets for the plan with IX_1 were less than those the optimizer preferred.

Here the information about the statement, index and plans.
As (more...)

Friday Philosophy – Your Experience can Keep You Ignorant

This week I was in an excellent presentation by Kerry Osborne about Outlines, SQL profiles, SQL patches and SQL Baselines. I’ve used three of those features in anger but when I looked at SQL Patches I just could not understand why you would use them – they looked to me like a very limited version of SQL Profiles.

There is a prize for spotting Kerry without a baseball cap

There is a prize for spotting Kerry without a baseball cap

So I asked Kerry about it (more...)

#DOAG2016 wrap up

doag_wegweiser

Another DOAG is over and here are my summary.

Day 1

Unfortunately the early train from duesseldorf arrives not before 9:30, so I missed the first morning slot. But on the other hand I this was the slot where I had to decide between 8 presentations, which I’m interested in.

The first soft skill presentation was about creative techniques beyond brain storming. The mentioned 3 techniques was all unknown for me, maybe I give it (more...)

Common OBIEE Implementation Mistakes That Impact Performance

I've recently performed several assessments to help our clients diagnose and fix their OBIEE performance problems. They often share similar design or implementation issues, and I thought it would be useful to present them here as a handy reference:

Mistake 1: No Software Monitoring

The key to happy users is to know there’s a problem (more...)

“If You Change This, Right Here …”


"changes are so frightening."
-- Norton Juster, The Phantom Tollbooth

It isn’t unusual for Oracle to expand functionality with each new release; a short list includes AWR, ADDM, the SYSAUX tablespace, Flashback Query, Flashback Database and Edition-based Redefinition. Oracle 12.1 is no different as it provides an interesting performance enhancement, the in-memory column store, that can increase performance by reducing physical I/O load by making tables memory-resident (to simplify the description somewhat). Inn (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 7

This part of the series is supposed to cover the results of I/O related tests performed on the Amazon RDS Oracle cloud instance.
As mentioned in the previous part of this series I've only used the "General Purpose SSD" storage type since the "Provisioned IOPS" storage was simply to expensive to me and it wasn't possible to get a trial license for that storage type.

Now one key aspect of the "General Purpose SSD" storage (more...)

no peek in PL/SQL

Connor McDonald wrote a blog about differences between bind peeking and SYS_CONTEXT in SQL queries. This even led to an proposal in Database Ideas: CBO should peek at SYS_CONTEXT values just like bind values (feel free to vote and comment, if you like it)
As I have a friend who really loves SYS_CONTEXT; I showed him the blog.

In the following discussion he stated (more/less
)
I should not have this problem at all, as (more...)

HotSpot JVM garbage collection options cheat sheet (v4)

After three years, I have decided to update my GC cheat sheet.

New version finally includes G1 options, thankfully there are not very many of them. There are also few useful options introduced to CMS including parallel inital mark and initiating concurrent cycles by timer.

Finally, I made separate cheat sheet versions for Java 7 and Java 8.

Below are links to PDF versions

Anniversary OICA

Happy anniversary to me!

On this day 10 years ago I published the first article in my blog. It was about the parameter optimizer_index_cost_adj (hence OICA), a parameter that has been a  source of many performance problems and baffled DBAs over the years and, if you read my first blog posting, a parameter that should be

It seems appropriate to mention it today because I recently found a blog posting (dated 3rd May 2013 (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 6

In the next parts of this series I'll have a look at the results of similar performance consistency tests performed on a comparable Amazon RDS Oracle cloud database instance.

Let's start with the configuration used, which was an Amazon RDS "db.m4.2xlarge" instance - the latest RDS generation available at time of testing. This is an 8 VCPUs resp. Hyper Threads / 4 cores configuration with 32GB of RAM.
As storage I've used (more...)

Transactions and SCNs

It’s general knowledge that the Oracle database is ACID compliant, and that SCNs or ‘system change numbers’ are at the heart of this mechanism. This blogpost dives into the details of how the Oracle engine uses these numbers.

Oracle database version 12.1.0.2.161018
Operating system version: OL 7.2, kernel: 4.1.12-61.1.14.el7uek.x86_64 (UEK4)

Redo generation
Whenever DML is executed, redo is generated in the form of ‘change (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 5

This is the fifth part of this installment, and before coming to comparisons to other cloud providers, in this part I show the results of the read-only test that I've already described in part three of this series, but repeated at a later point in time. The test setup was identical and can be checked in the mentioned previous part.

The reason for running the test again was the fact that I was informed during (more...)

Visualizing System Statistics in SQL Developer – OTN Appreciation Day

Tim Hall inspired me with his #ThanksOTN (or “OTN Appreciation Day“) campaign to continue my mini-series on leveraging SQL Developer Reports for DBA tasks. Today with visualizing historical System Statistics from Statspack for performance analyses. #ThanksOTN I have three favourite Oracle features to rave about today: Analytic SQL Statspack SQL Developer (particularly the reports)! System Statistics As the […]

OTN Appreciation Day – Instrument Your Damned Code. Please!

Today is OTN Appreciation Day.

This day is the idea of Tim Hall, Mr OracleBase, and you can See his post here. The idea is that as a sign of appreciation to OTN we do a technical (or not so technical) post on a feature of Oracle we like. I’m going to visit an area I have mentioned before…

DBMS_APPLICATION_INFO.

In my opinion, one of the key differences between good code and poor code is (more...)

Decommissioning triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.


Well, it's one thing to say 'create all new tables like this', (more...)