Being Right and Proving It

One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.

In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)

Oracle Database 12.1.0.2.0 – Native JSON Support (1)

Oracle Database 12.1.0.2 has now native support build-in for handling JSON (Javascript Object Notation) data. Oracle Database supports JSON natively with relational database features, including...

Read More

That’s … Huge!

Recently I’ve noticed the occasional thread in Oracle newsgroups and lists asking about hugepages support in Linux, including ‘best practices’ for hugepages configuration. This information is out on that ‘world-wide web’ in various places; I’d rather put a lot of that information in this article to provide an easier way to get to it. I’ll cover what hugepages are, what they do, what they can’t do and how best to allocate them for your particular (more...)

Oracle Database 12.1.0.2.0 – New Features

The world around us is changing and new stuff in the Oracle database arena is nowadays released on patch level. Although not many new features...

Read More

Exadata storage indexes and DML

Last week I’ve gotten a question on how storage indexes (SI) behave when the table for which the SI is holding data is changed. Based on logical reasoning, it can be two things: the SI is invalidated because the data it’s holding is changed, or the SI is updated to reflect the change. Think about this for yourself, and pick a choice. I would love to hear if you did choose the correct one.

First (more...)

New Version Of XPLAN_ASH Utility

A new version 4.1 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.

Here are the notes from the change log:

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in (more...)

B-tree and nulls

Today while I was scrolling my “WordPress reader” page I saw a post which reminds me to something I had fun before /and find it useful in many situations/: So if we have an index based on null column followed by not null column we can use it to filter the rows with null value. […]

Delete Costs

One of the quirky little anomalies of the optimizer is that it’s not allowed to select rows from a table after doing an index fast full scan (index_ffs) even if it is obviously the most efficient (or, perhaps, least inefficient) strategy. For example:


create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	rownum			id,
	mod(rownum,100)		n1,
	rpad('x',100)		padding
from
	generator	 (more...)

FIRST_ROWS_n Optimizer Mode – What is Wrong with this Statement?

June 8, 2014 It has been nearly two years since I last wrote a review of an Oracle Database related book, although I have recently written reviews of two Microsoft Exchange Server 2013 books and a handful of security cameras in the last two years.  My copy of the second edition of the “Troubleshooting Oracle […]

Tab Navigation Performance in UI Shell and Multi Task Flow

I was investigating performance impact of navigating between tabs in UI Shell template. To compare, as alternative solution for UI Shell, I was using dynamic tabs with Multi Task Flow binding (Building Custom UI Shell with ADF 11g R2). When you switch between tabs in UI Shell, you could notice slight delay. This is normal delay (differently to browser tabs, where no delay), as there should be generated response from the server and (more...)

Unindexed foreign keys

Unindexed foreign keys can lead to bad database performance due to lock contention and full table scans performed on the child table. Here is a diagram which illustrate the situation: In this exemple Oracle needs to lock the entire employees table when the primary key of the departments table is modified, in addition to that [...]

The post Unindexed foreign keys appeared first on Oracle DBA Scripts and Articles (Montreal).

Simora: Alpha Testers Confirmed

It's been a while since I provided any public updates regarding Simora, our Oracle workload simulation product. It's finally time to unveil the status of Simora and our steps moving forwards. We have been working extensively on the Simora engine and infrastructure over the  last several months, with a view to transforming it into a […]

SQL Activity for the last hour

This script can be used to show the top 10 SQL activity for the last hour. It uses the v$active_session_history view to search top SQL by resource consumption. SQL Activity [crayon-5377b1876b186473467355/] Here is the result you can obtain: and the active sessions history graph for the same period:

The post SQL Activity for the last hour appeared first on Oracle DBA Scripts and Articles (Montreal).

Comparing ADF View Object and Row Fetch Execution Times

I bet one of the most common doubts ADF developer, or may be DB admin, has - why VO SQL executes slower than the one identical from SQL Plus? This is often the case, but there is nothing VO SQL to blame about. Usually VO SQL executes in almost same time, as it would execute in SQL Plus - but there is extra added time of row fetch. When ADF UI page is rendering, data (more...)

Flame Graphs for Oracle

Topic: This post is a hands-on introduction to using on-CPU Flame Graphs for investigating Oracle workloads. This technique is about collecting and analyzing sampled stack trace data to analyze and troubleshoot Oracle processes at the OS level (in particular applied to Linux).

Motivations: The techniques and tools described here can be used for performance investigations to complement wait-event based information gathered from the Oracle engine, such as information available with ASH and sql monitoring. They (more...)

Oracle can now use function-based indexes in queries without functions!

There’s a neat optimization in Oracle I found while tinkering around (look closely at the predicate section):

select oracle_can_now_use,
       a_function_based_index
from   queries_with_no_functions
where  a_date_col_with_an_fbi = :a_date
and    oracle_version >= '11.2.0.2';

-------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |     1 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| QUERIES_WITH_NO_FUNCTIONS |     1 |
|*  2 |   INDEX RANGE SCAN          | QUWF_DATE_FBI             |      (more...)

Systemtap revisited

Some time back, I investigated the options to do profiling of processes in Linux. One of the things I investigated was systemtap. After careful investigation I came to the conclusion that systemtap was not really useful for my investigations, because it only worked in kernelspace, only very limited in userspace. The limitation of working in userspace was that you had to define your own markers in the source code of the program you wanted to (more...)

A Closer Look at CALIBRATE_IO

Topic: This blog entry is about investigating Oracle's DBMS_RESOURCE_MANAGER.CALIBRATE_IO

Spoiler: If you have reached this article in search of a tool for quantitative analysis of storage performance and in particular for measuring random read I/O in Oracle, I'd rather advise you to use tools that allow generating test workloads in a controlled manner, in a way that can be understood and measured and in particular with latency details together with IOPS measurements. For example (more...)

Information Lifecycle Management

Presentation of Information Lifecycle Management (ILM) With data volume growing, finding adapted storage solutions to storage costs and performance objectives is a real challenge for IT department in large companies. Information Lifecycle management is about managing the data all along its useful life while offering the best performance and storage cost as low as possible. The [...]

The post Information Lifecycle Management appeared first on Oracle DBA Scripts and Articles (Montreal).

Result Cache concept and benefits

This feature was first introduced in Oracle 11g and was meant to increase performance of repetitive queries returning the same data. This feature is interesting if your application always look for static data, or data that is rarely updated, for these reasons, it is firstly destinated to Data Warehouses databases (OLAP) as many users will [...]

The post Result Cache concept and benefits appeared first on Oracle DBA Scripts and Articles (Montreal).