mwidlake's picture


I think I’ve found an (admittedly obscure) bug with PL/SQL and/or SQL.

Have a look and see if you also think this is odd – or have I missed the totally obvious?

(This is all on

Without going into the details (we would be here for hours if I did) I’m looking into the overhead of context switching between PL/SQL and SQL. It it fairly common knowledge that when you call a PL/SQL function from SQL there is a context switch when the SQL engine hands over control to the PL/SQL engine. I’ve been doing some work into how much the overhead is and that it is incurred for each distinct PL/SQL function (plus loads of other considerations around it).

In doing so I saw something unexpected (to me, anyway) which I have simplified down to this:

oraclebase's picture

SQL for Beginners : Videos and Articles

love-sqlI’ve been saying for some time I should do some more entry level content, but it’s been kind-of hard to motivate myself. I mostly write about things I’m learning or actively using, so going back and writing entry level content is not something that usually springs to mind.

Recently I’ve got involved in a number of “grumpy old man” conversations about the lack of SQL knowledge out there. That, combined with a few people at work getting re-skilled, prompted me to get off my ass and give it a go. It’s actually quite difficult trying to get yourself into the head-space of someone who is coming fresh to the subject. You don’t want to pitch it too low and sound patronizing, but then pitching it too high makes you sounds like an elitist dick.

mwidlake's picture

STANDARD date considerations in Oracle SQL and PL/SQL

Most of us know that the Oracle DATE datatype has upper and lower limits. From the Oracle 11g Database Concepts manual:

Oracle Database can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or ‘AD’). Unless BCE (‘BC’ in the format mask) is specifically used, CE date entries are the default.

I never believe 100% anything I read, so I’ll try that. I’ll set my session to show dates with the AD/BC indicator and step back in time:

Charles Hooper's picture

To N or Not to N, is SYS_OP_C2C the Question; Oracle NVARCHAR Slow?

February 8, 2015 I was recently contacted about an odd performance issue with a software product named TDM, where certain SQL statements submitted to an Oracle 9.2.x database required roughly 30 seconds to execute when a nearly instantaneous response was expected.  The person who reported the issue to me provided a sample SQL statement that […]

mwidlake's picture

Audio semi-Visual Presentation on Clustering Data in Oracle

I suppose it had to happen eventually but one of my presentations has ended up on YouTube. It’s a recent presentation I did for the Oracle Midlands user group in September.

The topic is (as the title of this blog post hints at!)Boosting select performance by clustering data. The video consists of the slides I presented, changing as the presentation progresses, with my audio over the top. It goes on for a bit, close to an hour, but you could watch a section and then go and do something else before watching a bit more.

I have to say, it is very odd hearing my voice (and the slight touch of the “brummie” {Birmingham} accent coming through) and I do wince at the places where I blather or say something slightly wrong or make a joke that involved a visual element that is lost. Oh well, at least you don’t see me wandering around and jumping up,literally, to point out bits on the slides.

mwidlake's picture

What Day Is It If You Only Specify The Time?

What is the date if you only specify the time when you populate an Oracle date column (or variable)?

That was the question that came up a few days ago in the little DBA team I am currently working in. Of course, the question was posed by the “junior” (who is damned smart at this stuff as he keeps asking questions like that) and the answer from us two old hands was… “Ohh!… hang on… errr….”

A little discussion then occurred. One of us suggested it would be “today”. One of us suggested it would be the Julian 1 date (4712BC). Both of us then stated it was an easy thing to test and the PROPER answer was “just try it and then you will know for sure”. We were right {and, of course, wrong} – as in with a simple question like this you don’t need to google the question (so “hello” to anyone googling the question and finding this page!) you just try it:

The junior tried it and…

oraclebase's picture

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c

I’ve spent the last couple of evenings playing with the new SQL pattern matching feature in Oracle 12c.

I’m doing some sessions on analytic functions in some upcoming conferences and I thought I should look at this stuff. I’m not really going to include much, if anything, about it as my sessions are focussed on beginners and I don’t really want to scare people off. The idea is to ease people in gently, then let them scare themselves once they are hooked on analytics. :) I’m thinking about Hooked on Monkey Fonics now…

khailey's picture

Full table scan runs way slower today!

#555555;">Every so often a DSS query that usually takes 10 minutes ends up taking over an hour.  (or one that takes an hour never seems to finish)

#555555;">Why would this happen?

#555555;">When investigating the DSS query, perhaps with wait event tracing,  one finds that the query which is doing full table scans and should be doing large multi-block reads and waiting for “db file scattered read” is instead waiting for single block reads, ie “db file sequential read”.  What the heck is going on?

#555555;">Sequential reads during a  full table scan scattered read query is a classic sign of reading rollback and reading rollback can make that minute(s) full table scan take hours.

khailey's picture

Right Deep, Left Deep and Bushy Joins in SQL

#555555;">What is right deep verses left deep? Good question. In join trees (not VST) the object on the left is acted upon first then the object on the right.  Below are left deep and right deep examples of the same query, showing

  • query text
  • join tree
  • join tree  modified to more clearly show actions
  • VST showing the same actions

#555555;">left_right_deep copy

jeremy.schneider's picture

Largest Tables Including Indexes and LOBs

Just a quick code snippit. I do a lot of data pumps to move schemas between different databases; for example taking a copy of a schema to an internal database to try to reproduce a problem. Some of these schemas have some very large tables. The large tables aren’t always needed to research a particular problem.

Here’s a quick bit of SQL to list the 20 largest tables by total size – including space used by indexes and LOBs. A quick search on google didn’t reveal anything similar so I just wrote something up myself. I’m pretty sure this is somewhat efficient; if there’s a better way to do it then let me know! I’m posting here so I can reference it in the future. :)

Syndicate content