Search

Top 60 Oracle Blogs

Recent comments

June 2016

GPIOZero on Non-Zero Raspberry Pi

So there are some cool features that are built into the GPIOZero library/module.  One of the challenges I think many of us that have a lot of projects that we want to work on, is that we end up having to translate it to the version of RPI that we just happen to be working on or have available.

Combining Features - Wrong Results With Scalar Subquery Caching

Quite often you can get into trouble with Oracle when you start combining different features.In this case of one my clients it is the combination of user-defined PL/SQL functions that can raise exceptions (think of currency conversion and a non-existent currency code gets passed into the function), DML error logging and attempting to improve performance by wrapping the PL/SQL function call into a scalar subquery to benefit from the built-in scalar subquery caching feature of the SQL runtime engine.As long as the scalar subquery didn't get used everything worked as expected, but after adding the scalar subquery after some while it became obvious that wrong results occurred - in that particular case here it meant rows that should have been rejected and written to the error logging table due to the exception raised in the user-defined PL/SQL function suddenly showed up in the target table, and what was even more worrying - they included a co

12c FETCH PERCENT

A nice little feature in 12c is the FETCH FIRST n ROWS syntax, which is a simple shorthand to avoid using inline views and the like to get a subset of the rows from what would normally be a larger resultset.

Here’s a simple example showing the syntax


SQL> select *
  2  from t
  3  order by 1
  4  fetch first 8 rows only;

         R
----------
         1
         2
         3
         4
         5
         6
         7
         8

8 rows selected.

You can also use the keyword “PERCENT” to retrieve a percentage of the rows, as show below

Accessing HCC compressed objects using an index

Problem

I came across another strange SQL performance issue: Problem was that a SQL statement was running for about 3+ hours in an User Acceptance (UA) database, compared to 1 hour in a development database. I ruled out usual culprits such as statistics, degree of parallelism etc. Reviewing the SQL Monitor output posted below, you can see that the SQL statement has already done 6 Billion buffer gets and steps 21 through 27 were executed 3 Billion times so far.

Statistics and execution plan

Quiz Night

Here’s an execution plan from a recent OTN database forum posting:

Buckle Up, Baby!

So after over two years at Oracle, I’m moving on.  Yes, for those of you who haven’t seen the tweets and the posts, you heard right.

EM13c, Configuration Management and Comparing Targets

How many times have you had maintenance or a release complete and everyone is sure that everything’s been put back the way it should have been, all t’s crossed, all i’s dotted and then you release it to the customers only to find out that NOPE, something was forgotten in the moving parts of technology?

Merge Precision

This note is about a little detail I hadn’t noticed about the merge command until a question came up on the OTN database forum a few days ago. The question was about the impact of the clustering_factor on the optimizer’s choice of execution plan – but the example supplied in the question displayed an oddity I couldn’t explain. Here’s the code and execution plan as originally supplied:

5 Days Expert Oracle Security Training In Paris - 20th June 2016

I will be teaching 5 days on my Oracle security classes in Paris from 20th June to 24th June with Oracle University at their offices and training suite. Details of the Oracle Security Event and how to register on Oracles....[Read More]

Posted by Pete On 06/06/16 At 09:59 AM

Oracle Database Cloud (DBaaS) Performance Consistency - Part 1

As Oracle ACE Director I got an extended trial license for Oracle's Cloud offerings, in particular the "Database as a Service" offering. As part of the (ongoing) evaluation I try to get an idea how consistent the performance of such an service is, which might be one of the concerns one might have when considering cloud offerings in general.

For my tests I've set up a 11.2.0.4 single instance database using "4 OCPUs" (Oracle CPUs) which ends up as an Oracle Linux 6 system showing 8 CPUs *and* 8 cores of type "Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz".

As edition for the database I've chosen the "Extreme Performance" Enterprise Edition which also shows up at the version banner (note the difference to regular database installations, not sure this might break some applications that don't know this banner):