Search

Top 60 Oracle Blogs

Recent comments

June 2016

Plan Shapes

There are a number of articles, webinars, and blogs online about how to read execution plans, but many of them seem to stop after the the minimum description of the simplest type of plan, so I thought I’d throw out a brief comment on a couple of the slightly more complicated things that are likely to appear fairly commonly because you sometimes find plans with very similar shapes but extremely different interpretation.

First: select with scalar subqueries in the select list (there’s no need to worry about what the table definitions look like):

Automatic Deployment of CDBs with non-default database options in 12.1.0.2

I have written about the importance of automation in previous posts, and this one is following the same line. This time I wanted to test and document my experience with “golden database templates”. Wy? Because most DBAs will appreciate that there are more interesting things to do than to run “create database” scripts all day, so why not automate the process? For quite some time DBCA, or the Database Creation Assistant offers you the option to create templates. One approach you could use would be to

  • Create a “golden master” database
  • Use DBCA to create a template based on this
  • And enjoy the fact that every database is exactly the same

All of this is done in hope to make life for the administrators a lot easier.

Motivation

Next Round Of ANZ “Let’s Talk Database” Events (July/August 2016)

I’ll be presenting the next round of “Let’s Talk Database” events around Australia and NZ this winter in July/August 2016. These are free events but due to limited places have often “sold out” in the past, so booking early is recommended to avoid disappointment. All events run between 9:00am – 12:30pm and are followed by a networking lunch. We always have […]

JWT - JSON Web Tokens

After having worked on a couple of SAML issues, trying to debug some encryption errors, I was getting really tired of really big SAML messages.

So I started looking around for alternatives, and found the JWT website. A lot more straight forward and plenty secure for what most people are looking to do with security tokens.

To quote from the website:

JSON Web Token (JWT) is an open standard (RFC 7519) that defines a compact and self-contained way for securely transmitting information between parties as a JSON object. This information can be verified and trusted because it is digitally signed. JWTs can be signed using a secret (with the HMAC algorithm) or a public/private key pair using RSA.

Private Life – When the Pond Came Inside

It's not deep - but it's still unwanted!

It’s not deep – but it’s still unwanted!

This week my plans were thrown into chaos when our house flooded. Before I go any further I should state that everyone is fine, no structural damage was done and we were about as lightly flooded as you can be whilst still being, well, in the situation of having dirty, contaminated water in your home. Let’s just say our garden pond partially relocated into our house but brought with it a strong hint of Septic Tank.

Cardinality trick

In the absence of a virtual column or function-based index, the optimizer uses a basic selectivity guess of 1% for a predicate of the form: “function(column) = constant”; but there is (at least) one special case where it gets clever; simple type conversion:

CPU_COUNT

When you have less CPU threads than the number of processes that has something to run in CPU, the OS will schedule them to share the CPU resource. Increasing the workload at that point will not increase the throughput because you have reached the capacity of your system, and response time will increase because of queuing. Actually, performance will even decrease because of the overhead of context switching when trying to share the processors.
When you don’t want the OS scheduler to do the resource sharing job, you can, and should, use Instance Caging. For sure, the database instance can do resource sharing more intelligently than the OS as it knows the kind of workload and the performance requirement of each process.

LOGGING and temporary space

We had an interesting question on AskTom this week.  The poster had been told by their DBA that the reason their large INSERT-AS_SELECT statement was consuming lots of temporary segment space, was because the database had been recently altered to enable FORCE LOGGING, presumably to ensure easier consistency in a physical standby node.

So … here’s a simple test case to demonstrate that this assertion is wrong.

First we build up table, and then sort ~2million rows both with the database in FORCE LOGGING and not in FORCE LOGGING modes.  We’ll see SQL monitoring to compare.

FORCE LOGGING

Diagnosing Non-Intuitive Errors with Errorstack

This is a story about how to diagnose a problem where the error message did not seem to make sense, nor have any apparent relation to what I thought was happening.

The Problem 

During a PeopleSoft on Exadata Proof-of-concept test, running on Oracle 11.2.0.4, we got this error in a COBOL process in PeopleSoft North American Payroll.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Application Program Failed
Action Type : SQL SELECT
In Pgm Section : SQLRT: EXECUTE-STMT
With Return Code: 31011
Error Message : ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00210: expected '<' instead of '�' Error at line 1
Stored Stmt : PSPEBUPD_S_BENF_NO
SQL Statement : SELECT A.BENEFIT_RCD_NBR FROM PS_PAY_EARNINGS A WHERE A.COMP