Search

Top 60 Oracle Blogs

Recent comments

Uncategorized

Plans and Trees

Prompted by a question on the ODC database forum – and also because I failed to get to the “Bonus slides” on my presentation on basic execution plans at both the DOAG and UKOUG conferences, here’s a small of slides demonstrating how to convert a text execution plan into a tree that you can read using the mechanism described in Oracle’s white paper by the phrase: “start from the bottom left and work across and then up”.

The file is a Microsoft Powerpoint file (early version).

 

Misdirection

A recent post on the ODC database forum prompted me to write a short note about a trap that catches everyone from time to time. The trap is following the obvious; and it’s a trap because it’s only previous experience that lets you decide what’s obvious and the similarity between what you’re looking and your previous experience may be purely coincidental.

The question on OTN (paraphrased) was as follows:

When I run the first query below Oracle doesn’t use the index on column AF and is slow, but when I run the second query the Oracle uses the index and it’s fast. So when the input starts with ‘\\’ the indexes are not used. What’s going on ?

Automatic sequences not being dropped

One of the nice new things in 12c was the concept of identity columns. In terms of the functionality they provide (an automatic number default) it is really no different from anything we’ve had for years in the database via sequences, but native support for the declarative syntax makes migration from other database platforms a lot easier.

Under the covers, identity columns are implemented as sequences. This makes a lot of sense – why invent a new piece of functionality when you can exploit something that already has been tried and tested exhaustively for 20 years? So when you create a table with an identity column, you’ll see the appearance of a system named sequence to support it.

The phantom tablespace

(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL?

OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick Smile

I’ve created a partitioned table called “T” (I’ll pause here for your applause at my incredible imagination skills for table naming Smile) and to show you the complete DDL, I’ll extract it using the familiar DBMS_METADATA package.

IRR function in PLSQL

We had an AskTOM question recently about how to calculate the IRR, ie, the Internal Rate of Return. To be honest, I had not really heard of the function, but readers were quick to point out to me that it was a commonly used function in the most “popular” database on earth, namely Microsoft Excel Smile

You need 10 minutes! Not 5, not 15, not 7

What follow is just my opinion….Well duh, it’s a blog, of course it’s just my opinion, but I’ll try back up my rantings and pontificating with some reasoned arguments. Whether at the end of it, you still call my claims total BS is of course entirely within your rights and your opinion </p />
</p></div>

    	  	<div class=

Happy Thanksgiving!

Just a quick blog post from Wellington New Zealand where we have just wrapped up the 2018 APAC Groundbreakers tour. It was a great way to finish the event with a small but enthusiastic crowd here in New Zealand.

image

18c and the ignoring of hints

 

One of the new features in 18c is the ability to ignore any optimizer hints in a session or across the entire database. A motivation for this feature is obviously our own Autonomous Data Warehouse, where we want to optimize queries without the potential “baggage” of user nominated hints strewn throughout the code.

This would seem a fairly easy function to implement, namely, as we parse the SQL, simply rip out anything that is a comment structured as a hint. At the Perth Oracle User Group conference yesterday, I had an interesting question from an attendee – namely, if all optimizer hints are being ignored, then does this mean that every hint will be ignored. In particular, what about the (very useful) QB_NAME hint? If we are just stripping out anything that is in a hint text format, we will lose those as well?

So it’s time for a test!

Add ORDER BY to make ANY query faster

Yes it’s SCBT day here in Perth!

SCBT = Silly Click Bait Title Smile

This post is just a cautionary tale that it is easy to get caught up judging SQL performance solely on a few metrics rather than taking a more common sense approach of assessing performance based on the true requirements of the relevant component of the application.  I say “true requirements” because it may vary depending on what is important to the application for a particular component.

Interval versus Range partitions

One of the nice things about partitioning in the database is that partition pruning can quickly eliminate the requirement to read large amounts of data when the partitioning key(s) are appropriately provided in your queries (and DML). This also extends to queries where the values provided do not map to any partitions.

Here’s a simple example of a range partitioned table which only cover the values from (less than) 1 to a ceiling of 6.