Uniquely parallel

Here’s a surprising (to me) execution plan from 12.1.0.2 – parallel execution to find one row in a table using a unique scan of a unique index – produced by running the following script (data creation SQL to follow):


set serveroutput off
set linesize 180
set trimspool on
set pagesize 60

alter session set statistics_level = all;

variable b1 number
exec :b1 := 50000

select /*+ parallel (3) */ id, v1 from  (more...)

Oracle USERENV Function with Examples

The Oracle USERENV function allows you to get information about the current session. Learn how to use the USERENV function in this article. Purpose of the Oracle USERENV Function The purpose of the USERENV function is to get information about your current session. It’s a simple function but can take a few different values for […]

OGh Oracle DBA and SQL Celebration Day 2016

oghThe OGh Oracle DBA and SQL Celebration Day was the last stop on my trip and it went super-quick! The morning started with a keynote by Dad. He managed to resist the urge to talk about me for 60 minutes, which was good. It gets really embarrassing when he keeps telling everyone I’ve eclipsed his accomplishments in every way possible!

After the keynote is was speaking about efficient PL/SQL calls from SQL. I lost the (more...)

Quiz Night

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

 
------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT          |                    |     1 |   231 |   160   (6)| 00:00:02 |
|   1 |  UPDATE                   | GS_TABLE           |       |       |            |          |
|*  2 |   HASH JOIN SEMI          |                    |     1 |   231 |   130   (0)| 00:00:02 |
|*  3 |    TABLE  (more...)

Intro to Database Security – Part 2

In part 1 I talked about introduction to the security world and important terms. In this post I will introduce database security, what it means and what we can do about it. Link to the previous post: part 1 – general introduction to security The database is the heart of the organization’s data. People want […]

Examining predicted Clusters and Cluster details using SQL

In a previous blog post I gave some details of how you can examine some of the details behind a prediction made using a classification model. This seemed to spark a lot of interest. But before I come back to looking at classification prediction details and other information, this blog post is the first in a 4 part blog post on examining the details of Clusters, as identified by a cluster model created using Oracle (more...)

BGOUG Spring Conference 2016 & Twitter Statistics

As I already mentioned in my previous blog post, I attended the BGOUG (Bulgarian Oracle User Group) Spring Conference last week. It was my second time to attend BGOUG conferences, and as usual, it was a great organization. I’m really impressed how Milena Gerova and BGOUG Conference Team can organize such a successful event.

presenting

At the conference, I gave two speeches, one was “Linux Essential Commands for DBAs”, and the other one was “Oracle Enterprise (more...)

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?  As the Database Administrator, you can do a bit of CYA and not be (more...)

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:


MERGE INTO gtt_ord t1
    USING X t2 ON  (more...)

What Are Oracle SQL Analytic Functions?

In this article, I explain what Oracle SQL analytic functions are, how they are different from other functions, and show you some examples. What Are Oracle SQL Analytic Functions? Analytic functions are functions that calculate an aggregate value based on a group of rows. A recent article I wrote on SQL aggregate functions mention that […]

Reading Data From Oracle Data Files (without Connecting Database)

I attended the BGOUG Spring Conference last week as a speaker. While in BGOUG Conference, I had a chance to listen Kamil Stawiarski’s session about hacking Oracle. Kamil spoke about potential security issues of high level privileges such as create any index and create directory. At the end of the session, he also showed a tool written by himself. The tool (I do not remember its name right now), reads a datafile, searches for object (more...)

Oracle PSU Pain and Predicament

Here is a list of download options for APR2016 (11.2.0.4.160419) PSU.

OJVM PSU (Linux/Unix) 22674697
Combo OJVM + DB PSU 22738777
Combo OJVM + DB SPU 22738732
Combo OJVM + GI PSU 22738793

That’s not enough  and there should be more (sarcasm).

FYI: GI PSU includes DB and DB PSU does not include GI.

There is a thread from MOS – OJVM PSU and RAC: What happened to high availability? and I (more...)

Interesting difference in foreign key locking behavior between heap and index organized tables

I hit this issue by accident, developers wanted to disable inserts to child table so they could perform some one-time maintenance operation, and this maintenance only affected one rown from the parent table (and all it’s children). I started wondering if there is any smaller level impact solution possible than taking a shared read lock on child table.

Database version: 12.1.0.2, but the same behaviour was also present in 11.2.0. (more...)

AMIS 25 – Beyond the Horizon – Day 2

amisDay 2 of AMIS 25 – Beyond the Horizon started with me playing catch-up on some blogging, then the conference proper started.

The first session I went to was Jeremy Ashley presenting “General Session – Oracle Applications User Experience: Trends and Strategy”. I wrote loads of notes on this, because I find it really interesting. I’m not going to post them here because I will make some mistakes and look like a fool. I’ve seen (more...)

Oracle Licensing Costs on AWS, Self Managed vs RDS

When you move your Oracle database to AWS there are a number of ways to handle licensing. You can either:

  1. Bring your own licenses for a self managed Oracle installation on an EC2 instance 
  2. Bring your own licenses for an Oracle Relational Databases Service instance
  3. Use pay as you go licensing for an Oracle Relational Database Service instance
Of course you need to be sure that you’re getting best value from your licenses - in (more...)

AMIS 25 – Beyond the Horizon – Day 1

amisWe had already been at the venue the day before for the beginning of the OTN Cloud Developer Challenge, but today was the first day of AMIS 25 – Beyond the Horizon. The day started at about 07:00, with a short minibus trip to the venue.

The first session I attended was “Get your money’s worth out of your Database” by Patrick Barel. He presented a whole bunch of database features that developers need to be using (more...)

Yes, You Must Use CALIBRATE_IO. No, You Mustn’t Use It To Test Storage Performance.

I occasionally get questions from customers and colleagues about performance expectations for the Oracle Database procedure called calibrate_io on XtremIO storage. This procedure must be executed in order to update the data dictionary. I assert, however, that it shouldn’t be used to measure platform suitability for Oracle Database physical I/O. The main reason I say this is because calibrate_io is a black box, as it were.

The procedure is, indeed, documented so it can’t (more...)

Do, There Is No Try

I know Werner DeGruyter will like the title of this post, so here’s a post dedicated to him as my last week at Oracle is off to a busy start…. 🙂

yoda

As I attempt to wrap up any open tasks at Oracle, I’m still Training Days 2017 Conference Director for RMOUG, have a planning meeting for the 800 member Girl Geek Dinner Boulder/Denver Meetup group that I’m the owner of, (more...)

Oracle NEXT_DAY Function with Examples

The NEXT_DAY function is a handy date manipulation function provided by Oracle. In this article, I’ll explain how to use it and show you some examples. Purpose of the Oracle NEXT_DAY Function The Oracle NEXT_DAY function returns the date of the first weekday that comes after the specified date value. It’s good for working with […]

Intro to Databse Security – Part 1

One of our main lines of business at DBAces is security and database security. Besides that, security is a very interesting topic and I love it. So I decided to start a series of posts about security in general (as an overview) and database security specifically. This is the first part, and this one will […]