Search

Top 60 Oracle Blogs

Recent comments

oracle database

Video : NTH_VALUE : Problem Solving using Analytic Functions

Today’s video is a demonstration of the NTH_VALUE analytic function.

This is based on this article.

You might find these useful also.

Imitation is the Sincerest Form of Flattery

Compilers and Interpreters Compatible with PL/SQL The PL/SQL programming language, created by Oracle, is the most well-known database-oriented procedural language in existence. While everyone who knows about PL/SQL knows it’s available in the Oracle Database, what most people don’t know is that a number of companies and individuals have implemented their own compilers and interpreters […]

The post Imitation is the Sincerest Form of Flattery appeared first on Oracle Internals.

Imitation is the Sincerest Form of Flattery

Compilers and Interpreters Compatible with PL/SQL The PL/SQL programming language, created by Oracle, is the most well-known database-oriented procedural language in existence. While everyone who knows about PL/SQL knows it’s available in the Oracle Database, what most people don’t know is that a number of companies and individuals have implemented their own compilers and interpreters […]

The post Imitation is the Sincerest Form of Flattery appeared first on Oracle Internals.

Demystifying PL/SQL: The Life of a Compilation Unit

With a solid history of use, Oracle’s PL/SQL is one of the top twenty programming languages in the world. OverviewWhile many people tend to think of it strictly as a database procedural language, PL/SQL is far more powerful than most give it credit for. PL/SQL is so popular, in fact, that at least fifteen independent […]

The post Demystifying PL/SQL: The Life of a Compilation Unit appeared first on Oracle Internals.

Demystifying PL/SQL: The Life of a Compilation Unit

With a solid history of use, Oracle’s PL/SQL is one of the top twenty programming languages in the world. OverviewWhile many people tend to think of it strictly as a database procedural language, PL/SQL is far more powerful than most give it credit for. PL/SQL is so popular, in fact, that at least fifteen independent […]

The post Demystifying PL/SQL: The Life of a Compilation Unit appeared first on Oracle Internals.

DBMS_CLOUD Package – A Reference Guide

The Appendix A of the Using Oracle Autonomous Data Warehouse Cloud guide describes the DBMS_CLOUD package. Unfortunately, it documents only a subset of the subroutines. And, for some of them, the description could also be enhanced. Therefore, while I was testing all the subroutines the DBMS_CLOUD package provides, I took a number of notes. By the end of my tests, I got what I can call my personal reference guide to the package. Since it might help others, here it is…

How Well a Query Optimizer Handles Subqueries?

At the beginning of December, at the UKOUG Tech17 conference in Birmingham (GB), I presented a comparison of the query optimizers of MySQL 8.0.3 and PostgreSQL 10.1. One of the things I talked about is their ability to handle subqueries. I summarized my findings with the following sentence:

Simple sub-queries that are not correctly optimized were observed.

It goes without saying that such a sentence leaves a lot of questions open. After all, it is just a summary. The aim of this post is to show you which subqueries I tested, and to compare my expectations with the execution plans generated by the query optimizers. In addition, since I’m not limited in time and scope as during a 50-minute presentation, I also discuss how the Oracle Database 12.2 query optimizer handles the same queries.

Announcing Trivadis Performance Days 2017

Trivadis Performance Days 2017

It is a great pleasure to announce the next Performance Days! This year the event will take place the 13-14 September in Zurich.

Given that detailed information about the event as well as online subscription are available at https://trivadis.com/performance-days-2017, in this short post I limit myself to thanking and pointing out who the speakers that accepted my invitation are:

What’s the Difference between Row Migration and Row Chaining?

In Oracle Database migrated and chained rows are often confused. In my opinion, this is for two main reasons. First, they share some characteristics, so it’s easy to confuse them. Second, Oracle, in its documentation and in the implementation of its software, has never been very consistent in distinguishing them. So, it’s essential to understand the differences between the two.

When rows are inserted into a block, the database engine reserves some free space for future updates. You define the amount of free space reserved for updates by using the PCTFREE parameter. To illustrate, I inserted six rows in the block depicted in the next figure. Since the limit set through PCTFREE has been reached, this block is no longer available for inserts.

Inserts leave some free space for future updates

Understanding CPU on AIX Power SMT Systems

This month I worked with a chicagoland company to improve performance for eBusiness Suite on AIX. I’ve worked with databases running on AIX a number of times over the years now. Nevertheless, I got thrown for a loop this week.

TLDR: In the end, it came down to a fundamental change in resource accounting that IBM introduced with the POWER7 processor in 2010. The bottom line is twofold: