Oracle Magazine useful Links

The following two links contain all the Oracle Magazine articles that have been published by Tom Kyte and Steven Feurstein. The third one, leads to all technical articles (check out Arup Nanda's Performance Tuning Series) and the last link points to all  issues archive.

• Oracle Magazine Ask Tom Columns
• Oracle Magazine PL/SQL Columns by Steven Feuerstein
• Oracle Magazine Technical (Technology and Developer) articles
• Oracle Magazine Issue Archive

enjoy
Oracle Learner

Oracle Internals Presentations

Check out this great set of Oracle Internals presentations by Julian Dyke. The presentations are all very comprehensive and I really like the animations that help you easily understand the sequence of events going on in the database.

Enjoy
Oracle Learner.

Hints from an SQL Profile

A SQL profile is a set of auxiliary information specific to a SQL statement. It is an object containing information that helps the query optimizer find an efficient execution plan for a specific SQL statement. It provides information about the execution environment, object statistics, and corrections related to the estimations performed by the query optimizer.

One of its main advantages is the ability to influence the query optimizer without modifying the SQL statement or the execution environment of the session executing it. In other words, it is transparent to the application connected to the database engine.

Conceptually, a SQL profile is to a SQL statement what statistics are to a table or
index. The database can use the auxiliary information to improve execution plans.

Exadata Storage Indexes

Storage Indexes is one of the most significant features of Exadata that run at the storage cells. However, is also the feature which is the least (if at all) controllable by the user (developer/dba). You cannot control when they will be build, for which columns they will be build for, and  for which columns the storage indexes will remain as the database workload evolves. So it is a true "black box" and totally different to what we have been used from good old database indexes.

The following links from Richard Foote's Oracle Blog are very insightfull on how storage indexes really work:
First goes the introductory series of posts:

• Exadata Strorage Indexes - part I
• Exadata Strorage Indexes - part II
• Exadata Strorage Indexes - part III
• Exadata Strorage Indexes - part IV
• Exadata Strorage Indexes - part V

And then is the comparison series between SIs and database indexes (check out the last one on the "magic" 8-column limit):

• Storage Indexes vs Database Indexes - part I
• Storage Indexes vs Database Indexes - part II
• Storage Indexes vs Database Indexes - part III
• Storage Indexes vs Database Indexes - part IV

Enjoy,
Oracle Learner.

Oracle Trace Files

In this post I will give an overview of Oracle trace files. The following are a collection of information from several books (especially [1]). Check out the References section at the end of this post.

Oracle trace files are text files that contain session information for the process that created
them. Trace files can be generated by the Oracle background processes, through the use of
trace events, or by user server processes. These trace files can contain useful information
for performance tuning and system troubleshooting.

Big Data Intro

Here is a great post from Arup Nanda with introductory material on Big Data and all the relevant buzzwords headed at Oracle professionals!

Enjoy!
Oracle Learner

parallel hint with no degree specified

In this post we try to investigate what degree of parallelism (DOP) Oracle assigns to DML operations when a parallel hint is used.

Happy Life KPI

If you want to measure how happy is your life, then ...you can use the following KPI! Of course you can modify the script to fit your own life priorities ;-)
I have created two versions of the query. The first one uses the analytic function ratio_to_report to generate the percentage needed for the KPI and thus makes the query a bit more "advanced" ;-)

select result, round(ratio_to_report(count(result)) over() * 100) ||'%' happy_life_KPI
from (
select  decode(time_spent, 'FAMILY', 'enjoy', 'FRIENDS', 'enjoy', 'ORACLE', 'enjoy', 'deadly dull') result
from mylife.days
start with yesterday is null and status = 'BORN'
connect by prior day = yesterday and status <> 'DEAD'
)
group by result
order by 2 desc
/

select round(count(decode(result,'enjoy',1,null))/count(*) *100) ||'%' happy_life_KPI
from (
select  decode(time_spent, 'FAMILY', 'enjoy', 'FRIENDS', 'enjoy', 'ORACLE', 'enjoy', 'deadly dull') result
from mylife.days
start with yesterday is null and status = 'BORN'
connect by prior day = yesterday and status <> 'DEAD'
)
/

Enjoy
OL

Update your stale statistics

Object statistics are very important for your data warehouse. Tables and indexes must have updated statistics in order for the query optimizer to choose the best execution plan for each query. So you must continously gather objects statistics (especially after ETL jobs have modified significantly the data).

The easiest way to do this is to utilize Oracle's automatic statistics gathering task. This can be easily configured to run at a specific maintenance window (that you will define, unless you want to use the default) from Enterprise Manager (Administration --> Automated Maintenance Tasks --> Optimizer Statistics Gathering).

Note that you should use an appropriate degree of parallelism so that the stats gathering process runs fast enough to fit in the maintenance window. You may have to experiment a bit with this, in order to find what is appropriate for your case.

But how can you monitor if the automated task has succesfully managed to gather statistics in the defined maintenance window?