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...)
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.
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...)
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...)
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 [...]
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:
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...)
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 >= '22.214.171.124'; ------------------------------------------------------------------------- | 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...)
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...)
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...)
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 [...]
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 [...]